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

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

-- Add last_login column if not exists
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `last_login` DATETIME NULL AFTER `updated_at`,
ADD COLUMN IF NOT EXISTS `api_key` VARCHAR(64) NULL UNIQUE AFTER `password_hash`;