-- HealthLink: Migration for new features
-- Run this if any columns are missing

-- Password reset tokens for patients
ALTER TABLE patients 
    ADD COLUMN IF NOT EXISTS reset_token VARCHAR(64) NULL AFTER password,
    ADD COLUMN IF NOT EXISTS reset_expires DATETIME NULL AFTER reset_token;

-- Additional profile columns (may already exist)
ALTER TABLE patients 
    ADD COLUMN IF NOT EXISTS city VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS state VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS blood_group VARCHAR(5) NULL,
    ADD COLUMN IF NOT EXISTS height DECIMAL(5,2) NULL,
    ADD COLUMN IF NOT EXISTS weight DECIMAL(5,2) NULL,
    ADD COLUMN IF NOT EXISTS allergies TEXT NULL,
    ADD COLUMN IF NOT EXISTS chronic_diseases TEXT NULL,
    ADD COLUMN IF NOT EXISTS current_medications TEXT NULL,
    ADD COLUMN IF NOT EXISTS medical_history TEXT NULL,
    ADD COLUMN IF NOT EXISTS family_history TEXT NULL,
    ADD COLUMN IF NOT EXISTS smoking ENUM('no','yes','former') DEFAULT 'no',
    ADD COLUMN IF NOT EXISTS alcohol ENUM('no','occasional','regular') DEFAULT 'no',
    ADD COLUMN IF NOT EXISTS emergency_contact_relation VARCHAR(50) NULL,
    ADD COLUMN IF NOT EXISTS profile_photo VARCHAR(255) NULL;

-- Support tickets (created by contact.php)
CREATE TABLE IF NOT EXISTS support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    category VARCHAR(50) DEFAULT 'general',
    status ENUM('open','in_progress','closed','resolved') DEFAULT 'open',
    priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
    resolution_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    assigned_to INT
);

-- doctor_name column on medical_records (for patient-uploaded records)
ALTER TABLE medical_records ADD COLUMN IF NOT EXISTS doctor_name VARCHAR(150) NULL;

-- is_visible_to_patient column (may already exist)
ALTER TABLE medical_records ADD COLUMN IF NOT EXISTS is_visible_to_patient TINYINT(1) DEFAULT 1;
