-- Add API key column to users table
ALTER TABLE `users` 
ADD COLUMN `api_key` VARCHAR(64) NULL UNIQUE AFTER `password_hash`,
ADD COLUMN `last_sync` DATETIME NULL AFTER `last_login`;

-- Generate API keys for existing doctors
UPDATE `users` 
SET `api_key` = CONCAT('hom_', MD5(CONCAT(id, username, RAND()))) 
WHERE `role` = 'doctor' AND `api_key` IS NULL;

-- Create reminder logs table
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`)
);

-- Create AI history tables if not exists
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`)
);

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`)
);

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`)
);

-- Create user_ai_settings table
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
);