-- HealthLink Complete Database Migration
-- Adds all missing tables for full feature support

-- Email Logs Table (for tracking all email sends)
CREATE TABLE IF NOT EXISTS email_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    recipient VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body LONGTEXT,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    error_message TEXT,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_recipient (recipient),
    INDEX idx_created (created_at)
);

-- Admin Users Table (for multi-admin support with roles)
CREATE TABLE IF NOT EXISTS admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin', 'moderator', 'support') DEFAULT 'admin',
    permissions LONGTEXT,
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_active (is_active)
);

-- System Settings Table (for configurable settings)
CREATE TABLE IF NOT EXISTS system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value LONGTEXT,
    description TEXT,
    data_type ENUM('string', 'number', 'boolean', 'array') DEFAULT 'string',
    is_editable BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key)
);

-- Appointment Reminders Queue (for automated reminders)
CREATE TABLE IF NOT EXISTS appointment_reminders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_id INT NOT NULL,
    reminder_type ENUM('email', 'sms', 'whatsapp', 'in_app') DEFAULT 'email',
    scheduled_time DATETIME NOT NULL,
    sent_time TIMESTAMP NULL,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id),
    INDEX idx_status (status),
    INDEX idx_scheduled (scheduled_time)
);

-- Two-Factor Authentication Table
CREATE TABLE IF NOT EXISTS user_2fa (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_type ENUM('patient', 'doctor', 'admin') NOT NULL,
    user_id INT NOT NULL,
    method ENUM('email', 'sms', 'authenticator') DEFAULT 'email',
    secret_key VARCHAR(255),
    is_verified BOOLEAN DEFAULT FALSE,
    backup_codes LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_type, user_id)
);

-- System Audit Log Table (for compliance & security)
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_type ENUM('patient', 'doctor', 'admin', 'system') NOT NULL,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    changes LONGTEXT JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    status ENUM('success', 'failed', 'attempted') DEFAULT 'success',
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_type, user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at),
    INDEX idx_entity (entity_type, entity_id)
);

-- Doctor Availability Slots
CREATE TABLE IF NOT EXISTS doctor_availability (
    id INT PRIMARY KEY AUTO_INCREMENT,
    doctor_id INT NOT NULL,
    day_of_week INT NOT NULL COMMENT '0=Sunday, 6=Saturday',
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    slot_duration INT DEFAULT 30 COMMENT 'in minutes',
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
    INDEX idx_doctor (doctor_id),
    UNIQUE KEY unique_doctor_day (doctor_id, day_of_week)
);

-- Consultation Notes (for follow-up care)
CREATE TABLE IF NOT EXISTS consultation_notes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    notes TEXT NOT NULL,
    prescription TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    INDEX idx_appointment (appointment_id)
);

-- Insurance/Payment Plans
CREATE TABLE IF NOT EXISTS payment_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    monthly_fee DECIMAL(10, 2) NOT NULL,
    annual_fee DECIMAL(10, 2),
    benefits LONGTEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default system settings
INSERT IGNORE INTO system_settings (setting_key, setting_value, description, data_type) VALUES
('site_name', 'HealthLink', 'Website name', 'string'),
('site_logo', '/assets/logo.png', 'Logo path', 'string'),
('email_from', 'noreply@healthlink.com', 'Email address for sending notifications', 'string'),
('email_from_name', 'HealthLink', 'Email sender name', 'string'),
('max_appointment_duration', '60', 'Maximum appointment duration in minutes', 'number'),
('appointment_reminder_hours', '24', 'Send reminder X hours before appointment', 'number'),
('require_doctor_verification', 'true', 'Require admin verification for doctors', 'boolean'),
('enable_2fa', 'false', 'Enable two-factor authentication', 'boolean'),
('gdpr_compliance', 'true', 'Enable GDPR data export features', 'boolean'),
('maintenance_mode', 'false', 'Maintenance mode enabled', 'boolean');
