-- Migration: 002_rubrics_tables
-- Date: 2024-01-01
-- Description: Create rubrics and remedies tables

-- Rubrics table
CREATE TABLE IF NOT EXISTS `rubrics` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `parent_id` INT NULL,
    `chapter_name` VARCHAR(100) NULL,
    `rubric_name` VARCHAR(255) NOT NULL,
    `full_path` VARCHAR(500) NOT NULL,
    `level` TINYINT DEFAULT 1,
    `sort_order` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`parent_id`) REFERENCES `rubrics`(`id`) ON DELETE CASCADE,
    INDEX(`parent_id`),
    INDEX(`full_path`(255)),
    INDEX(`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Remedies table
CREATE TABLE IF NOT EXISTS `remedies` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) UNIQUE NOT NULL,
    `abbreviation` VARCHAR(20) NULL,
    `materia_medica_text` LONGTEXT,
    `clinical_indications` TEXT,
    `modalities` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rubric-Remedy mapping
CREATE TABLE IF NOT EXISTS `rubric_remedy` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `rubric_id` INT NOT NULL,
    `remedy_id` INT NOT NULL,
    `grade` TINYINT NOT NULL CHECK (grade IN (1,2,3,4)),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`rubric_id`) REFERENCES `rubrics`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`remedy_id`) REFERENCES `remedies`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_rubric_remedy` (`rubric_id`, `remedy_id`),
    INDEX(`rubric_id`),
    INDEX(`remedy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;