-- Migration: 003_cases_tables
-- Date: 2024-01-01
-- Description: Create cases and follow-ups tables

-- Cases table
CREATE TABLE IF NOT EXISTS `cases` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `patient_name` VARCHAR(100) NOT NULL,
    `patient_mobile` VARCHAR(15) NOT NULL,
    `patient_age` INT NULL,
    `patient_gender` ENUM('M', 'F', 'Other') NULL,
    `chief_complaint` TEXT NOT NULL,
    `case_history` TEXT,
    `physical_generals` TEXT,
    `mental_emotional` TEXT,
    `modalities` TEXT,
    `selected_rubrics` JSON,
    `repertorization_result` JSON,
    `prescribed_remedy_id` INT NULL,
    `prescription_potency` VARCHAR(20) NULL,
    `follow_up_date` DATE NULL,
    `status` ENUM('active', 'follow-up', 'closed') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`prescribed_remedy_id`) REFERENCES `remedies`(`id`) ON DELETE SET NULL,
    INDEX(`user_id`),
    INDEX(`patient_mobile`),
    INDEX(`status`),
    INDEX(`follow_up_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Follow-ups table
CREATE TABLE IF NOT EXISTS `follow_ups` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `case_id` INT NOT NULL,
    `follow_up_date` DATE NOT NULL,
    `progress` TEXT,
    `new_symptoms` TEXT,
    `remedy_repeated` BOOLEAN DEFAULT FALSE,
    `changed_remedy_id` INT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`changed_remedy_id`) REFERENCES `remedies`(`id`) ON DELETE SET NULL,
    INDEX(`case_id`),
    INDEX(`follow_up_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;