-- TeleHealth Hub Botswana - Database Initialization Script
-- Create database
CREATE DATABASE IF NOT EXISTS telehealth_hub_botswana;
USE telehealth_hub_botswana;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    user_type ENUM('patient', 'doctor', 'nurse', 'admin') DEFAULT 'patient',
    location VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Doctors/Healthcare Professionals table
CREATE TABLE IF NOT EXISTS healthcare_professionals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    specialization VARCHAR(100),
    license_number VARCHAR(100) UNIQUE,
    clinic_name VARCHAR(200),
    clinic_address VARCHAR(255),
    availability_status ENUM('available', 'busy', 'offline') DEFAULT 'offline',
    consultation_fee DECIMAL(10, 2),
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Consultations table
CREATE TABLE IF NOT EXISTS consultations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    consultation_type ENUM('video', 'audio', 'chat') DEFAULT 'chat',
    status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',
    scheduled_date DATETIME,
    started_at DATETIME,
    ended_at DATETIME,
    symptoms TEXT,
    diagnosis TEXT,
    prescription TEXT,
    notes TEXT,
    cost DECIMAL(10, 2),
    payment_status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Appointments table
CREATE TABLE IF NOT EXISTS appointments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    clinic_id INT,
    appointment_date DATETIME NOT NULL,
    status ENUM('scheduled', 'completed', 'cancelled', 'no-show') DEFAULT 'scheduled',
    reason TEXT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Clinics table
CREATE TABLE IF NOT EXISTS clinics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(150),
    location_lat DECIMAL(10, 8),
    location_lng DECIMAL(11, 8),
    opening_hours VARCHAR(100),
    services TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Health Library Content table
CREATE TABLE IF NOT EXISTS health_articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    content LONGTEXT,
    language ENUM('en', 'tn') DEFAULT 'en',
    author VARCHAR(100),
    is_published BOOLEAN DEFAULT TRUE,
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- SMS Notifications table
CREATE TABLE IF NOT EXISTS sms_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    phone_number VARCHAR(20),
    message TEXT NOT NULL,
    notification_type ENUM('appointment_reminder', 'medication_alert', 'health_tip', 'emergency') DEFAULT 'health_tip',
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    sent_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- USSD Sessions table
CREATE TABLE IF NOT EXISTS ussd_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(100) UNIQUE,
    phone_number VARCHAR(20),
    current_menu VARCHAR(50),
    user_input VARCHAR(255),
    response_text TEXT,
    session_status ENUM('active', 'completed', 'timeout') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    consultation_id INT,
    amount DECIMAL(10, 2),
    currency VARCHAR(3) DEFAULT 'BWP',
    payment_method ENUM('orange_money', 'card', 'bank_transfer') DEFAULT 'orange_money',
    transaction_id VARCHAR(100),
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE SET NULL
);

-- Symptom Checker History table
CREATE TABLE IF NOT EXISTS symptom_checks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    phone_number VARCHAR(20),
    symptoms TEXT,
    suggested_action TEXT,
    source ENUM('web', 'ussd') DEFAULT 'web',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Create indexes for better query performance
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_phone ON users(phone);
CREATE INDEX idx_consultation_patient ON consultations(patient_id);
CREATE INDEX idx_consultation_doctor ON consultations(doctor_id);
CREATE INDEX idx_consultation_status ON consultations(status);
CREATE INDEX idx_appointment_patient ON appointments(patient_id);
CREATE INDEX idx_appointment_date ON appointments(appointment_date);
CREATE INDEX idx_sms_user ON sms_notifications(user_id);
CREATE INDEX idx_sms_status ON sms_notifications(status);
CREATE INDEX idx_ussd_phone ON ussd_sessions(phone_number);
CREATE INDEX idx_payment_user ON payments(user_id);
CREATE INDEX idx_payment_status ON payments(status);

-- Insert sample data
INSERT INTO users (first_name, last_name, email, phone, password_hash, user_type, location) VALUES
('Dr.', 'Motswana', 'dr.motswana@telehealth.bw', '+267-71-234567', SHA2('password123', 256), 'doctor', 'Gaborone'),
('Nurse', 'Keabetswe', 'nurse.keabetswe@telehealth.bw', '+267-71-345678', SHA2('password123', 256), 'nurse', 'Francistown'),
('Admin', 'System', 'admin@telehealth.bw', '+267-71-456789', SHA2('admin123', 256), 'admin', 'Gaborone');

INSERT INTO clinics (name, address, phone, email, location_lat, location_lng, opening_hours, services) VALUES
('Gaborone Central Clinic', 'Plot 1234, Gaborone', '+267-3-123456', 'central@clinic.bw', -24.6282, 25.9241, '08:00-17:00', 'General Consultation, Maternal Health, Chronic Disease Management'),
('Francistown Medical Center', 'Main Street, Francistown', '+267-4-567890', 'francistown@clinic.bw', -21.1667, 27.5000, '08:00-17:00', 'Emergency Services, Surgery, Pediatrics');

INSERT INTO health_articles (title, category, content, language, author) VALUES
('HIV Prevention and Management', 'HIV/AIDS', 'Comprehensive guide on HIV prevention, testing, and treatment options available in Botswana...', 'en', 'Dr. Motswana'),
('Maternal Health: Pregnancy Care', 'Maternal Health', 'Essential information for pregnant women including prenatal care, nutrition, and delivery options...', 'en', 'Nurse Keabetswe'),
('Managing Tuberculosis', 'Infectious Diseases', 'Understanding TB symptoms, treatment regimens, and importance of medication adherence...', 'en', 'Dr. Motswana');