-- ============================================
-- Homoeopathy System - Database Schema
-- Kent's Repertory | Classical Homeopathy
-- Version: 1.0.0
-- ============================================

-- Create database
CREATE DATABASE IF NOT EXISTS `homoeopathy_kent` 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `homoeopathy_kent`;

-- ============================================
-- USERS & AUTHENTICATION
-- ============================================

-- Users table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `full_name` VARCHAR(100) NOT NULL,
    `role` ENUM('admin', 'doctor') DEFAULT 'doctor',
    `api_key` VARCHAR(64) UNIQUE NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `last_login` DATETIME NULL,
    `last_sync` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX(`role`, `is_active`),
    INDEX(`email`),
    INDEX(`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User sessions table
CREATE TABLE IF NOT EXISTS `user_sessions` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `session_token` VARCHAR(128) NOT NULL UNIQUE,
    `expires_at` DATETIME NOT NULL,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX(`session_token`),
    INDEX(`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SUBSCRIPTIONS
-- ============================================

CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `plan_type` ENUM('1month', '6month', '1year', 'unlimited') NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NULL,
    `amount` DECIMAL(10,2) DEFAULT 0,
    `payment_id` VARCHAR(100) NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `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,
    INDEX(`user_id`, `is_active`),
    INDEX(`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- REPERTORY (KENT'S REPERTORY)
-- ============================================

-- Rubrics table (hierarchical structure)
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`),
    INDEX(`chapter_name`)
) 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 (with grades)
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`),
    INDEX(`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- CASE MANAGEMENT
-- ============================================

-- 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`),
    INDEX(`created_at`)
) 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;

-- Case attachments
CREATE TABLE IF NOT EXISTS `case_attachments` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `case_id` INT NOT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `original_name` VARCHAR(255) NOT NULL,
    `file_type` ENUM('document', 'prescription', 'report', 'image') DEFAULT 'document',
    `mime_type` VARCHAR(100),
    `file_size` INT,
    `uploaded_at` DATETIME NOT NULL,
    FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON DELETE CASCADE,
    INDEX(`case_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- REMINDERS & NOTIFICATIONS
-- ============================================

-- Reminder logs
CREATE TABLE IF NOT EXISTS `reminder_logs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `case_id` INT NOT NULL,
    `type` ENUM('sms', 'email') NOT NULL,
    `recipient` VARCHAR(100) NOT NULL,
    `message` TEXT,
    `status` ENUM('sent', 'failed', 'pending') DEFAULT 'sent',
    `sent_at` DATETIME NOT NULL,
    FOREIGN KEY (`case_id`) REFERENCES `cases`(`id`) ON DELETE CASCADE,
    INDEX(`case_id`),
    INDEX(`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- AI FEATURES
-- ============================================

-- AI analysis history
CREATE TABLE IF NOT EXISTS `ai_analysis_history` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `case_text` TEXT NOT NULL,
    `analysis_result` TEXT NOT NULL,
    `remedy_name` VARCHAR(100) NULL,
    `created_at` DATETIME NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX(`user_id`),
    INDEX(`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI remedy suggestions
CREATE TABLE IF NOT EXISTS `ai_remedy_suggestions` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `symptoms` TEXT NOT NULL,
    `suggestion_result` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI rubric extractions
CREATE TABLE IF NOT EXISTS `ai_rubric_extractions` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `case_text` TEXT NOT NULL,
    `extracted_rubrics` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI analysis cache
CREATE TABLE IF NOT EXISTS `ai_analysis_cache` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `case_text_hash` VARCHAR(64) UNIQUE NOT NULL,
    `remedy_id` INT NULL,
    `analysis_result` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(`case_text_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- USER PREFERENCES
-- ============================================

-- User AI settings
CREATE TABLE IF NOT EXISTS `user_ai_settings` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL UNIQUE,
    `enable_ai` BOOLEAN DEFAULT TRUE,
    `auto_analyze` BOOLEAN DEFAULT FALSE,
    `include_repertorization` BOOLEAN DEFAULT TRUE,
    `analysis_depth` ENUM('basic', 'standard', 'detailed') DEFAULT 'standard',
    `preferred_model` ENUM('gemini', 'deepseek') DEFAULT 'gemini',
    `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Remedy bookmarks
CREATE TABLE IF NOT EXISTS `remedy_bookmarks` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `remedy_id` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`remedy_id`) REFERENCES `remedies`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_bookmark` (`user_id`, `remedy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SYSTEM SETTINGS
-- ============================================

-- Settings table
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `setting_key` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` TEXT,
    `setting_type` ENUM('text', 'number', 'boolean', 'json') DEFAULT 'text',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX(`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `setting_type`) VALUES
('app_name', 'Homoeopathy System', 'text'),
('app_version', '1.0.0', 'text'),
('timezone', 'Asia/Kolkata', 'text'),
('items_per_page', '20', 'number'),
('maintenance_mode', '0', 'boolean'),
('registration_enabled', '1', 'boolean'),
('email_notifications', '1', 'boolean'),
('smtp_host', '', 'text'),
('smtp_port', '587', 'number'),
('smtp_user', '', 'text'),
('smtp_pass', '', 'text'),
('date_format', 'd-m-Y', 'text'),
('time_format', 'H:i:s', 'text'),
('currency_symbol', '₹', 'text'),
('currency_code', 'INR', 'text'),
('gemini_api_key', '', 'text'),
('deepseek_api_key', '', 'text'),
('use_ai', '0', 'boolean'),
('ai_model', 'gemini', 'text'),
('ai_temperature', '0.7', 'number'),
('ai_max_tokens', '2048', 'number');

-- ============================================
-- MIGRATIONS TRACKING
-- ============================================

CREATE TABLE IF NOT EXISTS `migrations` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `migration` VARCHAR(255) NOT NULL,
    `executed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(`migration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INDEXES FOR PERFORMANCE
-- ============================================

-- Additional indexes for common queries
CREATE INDEX idx_cases_patient ON cases(patient_name, patient_mobile);
CREATE INDEX idx_cases_status_date ON cases(status, created_at);
CREATE INDEX idx_follow_ups_date ON follow_ups(follow_up_date);
CREATE INDEX idx_rubric_remedy_grade ON rubric_remedy(grade);
CREATE INDEX idx_subscriptions_active ON subscriptions(is_active, end_date);

-- ============================================
-- STORED PROCEDURES
-- ============================================

DELIMITER //

-- Get dashboard statistics for doctor
CREATE PROCEDURE GetDoctorStats(IN p_user_id INT)
BEGIN
    SELECT 
        COUNT(*) as total_cases,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_cases,
        SUM(CASE WHEN status = 'follow-up' THEN 1 ELSE 0 END) as follow_up_cases,
        SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) as closed_cases,
        COUNT(DISTINCT patient_mobile) as unique_patients
    FROM cases WHERE user_id = p_user_id;
END//

-- Clean expired sessions
CREATE PROCEDURE CleanExpiredSessions()
BEGIN
    DELETE FROM user_sessions WHERE expires_at < NOW();
END//

DELIMITER ;

-- ============================================
-- TRIGGERS
-- ============================================

DELIMITER //

-- Update updated_at timestamp
CREATE TRIGGER update_users_timestamp 
BEFORE UPDATE ON users
FOR EACH ROW
SET NEW.updated_at = CURRENT_TIMESTAMP//

CREATE TRIGGER update_cases_timestamp 
BEFORE UPDATE ON cases
FOR EACH ROW
SET NEW.updated_at = CURRENT_TIMESTAMP//

DELIMITER ;

-- ============================================
-- INITIAL DATA
-- ============================================

-- Insert default admin user (password: admin123)
INSERT INTO `users` (`username`, `password_hash`, `email`, `full_name`, `role`, `is_active`) 
SELECT 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@homoeopathy.com', 'System Administrator', 'admin', 1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'admin');

-- Insert sample chapters
INSERT IGNORE INTO `rubrics` (`chapter_name`, `rubric_name`, `full_path`, `level`) VALUES
('MIND', 'MIND', 'MIND', 1),
('VERTIGO', 'VERTIGO', 'VERTIGO', 1),
('HEAD', 'HEAD', 'HEAD', 1),
('EYE', 'EYE', 'EYE', 1),
('EAR', 'EAR', 'EAR', 1),
('NOSE', 'NOSE', 'NOSE', 1),
('FACE', 'FACE', 'FACE', 1),
('MOUTH', 'MOUTH', 'MOUTH', 1),
('STOMACH', 'STOMACH', 'STOMACH', 1),
('ABDOMEN', 'ABDOMEN', 'ABDOMEN', 1);

-- Insert sample remedies
INSERT IGNORE INTO `remedies` (`name`, `abbreviation`) VALUES
('Sulphur', 'Sulph'),
('Lycopodium', 'Lyc'),
('Arsenicum Album', 'Ars'),
('Pulsatilla', 'Puls'),
('Nux Vomica', 'Nux'),
('Belladonna', 'Bell'),
('Calcarea Carbonica', 'Calc'),
('Ignatia', 'Ign'),
('Natrum Muriaticum', 'Nat-m'),
('Sepia', 'Sep');

-- ============================================
-- VIEWS
-- ============================================

-- Active subscriptions view
CREATE OR REPLACE VIEW `active_subscriptions` AS
SELECT s.*, u.full_name, u.email
FROM subscriptions s
JOIN users u ON s.user_id = u.id
WHERE s.is_active = 1 
AND (s.plan_type = 'unlimited' OR s.end_date >= CURDATE());

-- Doctor performance view
CREATE OR REPLACE VIEW `doctor_performance` AS
SELECT 
    u.id as doctor_id,
    u.full_name as doctor_name,
    COUNT(DISTINCT c.id) as total_cases,
    COUNT(DISTINCT CASE WHEN c.status = 'closed' THEN c.id END) as closed_cases,
    COUNT(DISTINCT f.id) as total_followups,
    COUNT(DISTINCT c.patient_mobile) as unique_patients
FROM users u
LEFT JOIN cases c ON u.id = c.user_id
LEFT JOIN follow_ups f ON c.id = f.case_id
WHERE u.role = 'doctor'
GROUP BY u.id;

-- Monthly case summary view
CREATE OR REPLACE VIEW `monthly_case_summary` AS
SELECT 
    YEAR(created_at) as year,
    MONTH(created_at) as month,
    COUNT(*) as total_cases,
    SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) as closed_cases
FROM cases
GROUP BY YEAR(created_at), MONTH(created_at);

-- ============================================
-- DATABASE COMPLETED
-- ============================================

SELECT 'Database schema created successfully!' as message;