-- HealthLink Complete Database
-- Database Name: healthlink_db
-- Import this file in phpMyAdmin

DROP DATABASE IF EXISTS healthlink_db;
CREATE DATABASE healthlink_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE healthlink_db;

-- Specializations Table
CREATE TABLE specializations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    icon VARCHAR(50),
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert Specializations
INSERT INTO specializations (name, icon, description) VALUES
('General Physician', 'fas fa-user-md', 'General medical consultation and primary care'),
('Cardiologist', 'fas fa-heartbeat', 'Heart and cardiovascular system specialist'),
('Dermatologist', 'fas fa-hand-paper', 'Skin, hair, and nail specialist'),
('Pediatrician', 'fas fa-baby', 'Children and adolescent healthcare specialist'),
('Gynecologist', 'fas fa-female', 'Women reproductive health specialist'),
('Neurologist', 'fas fa-brain', 'Nervous system and brain specialist'),
('Orthopedic', 'fas fa-bone', 'Bone, joint, and muscle specialist'),
('ENT Specialist', 'fas fa-ear', 'Ear, Nose, and Throat specialist'),
('Psychiatrist', 'fas fa-head-side-virus', 'Mental health and behavioral specialist'),
('Dentist', 'fas fa-tooth', 'Dental and oral health specialist'),
('Ophthalmologist', 'fas fa-eye', 'Eye and vision specialist'),
('Urologist', 'fas fa-kidneys', 'Urinary system specialist');

-- Patients Table
CREATE TABLE patients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    health_id VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    gender ENUM('male','female'),
    date_of_birth DATE,
    cnic VARCHAR(15),
    address TEXT,
    city VARCHAR(50),
    emergency_contact_name VARCHAR(100),
    emergency_contact_phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,
    profile_photo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Doctors Table
CREATE TABLE doctors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    gender ENUM('male','female'),
    pmc_license_number VARCHAR(50) UNIQUE NOT NULL,
    specialization_id INT,
    qualification TEXT NOT NULL,
    experience_years INT DEFAULT 0,
    consultation_fee DECIMAL(10,2) NOT NULL,
    clinic_name VARCHAR(150),
    clinic_address TEXT,
    city VARCHAR(50),
    bio TEXT,
    profile_photo VARCHAR(255),
    doc_license VARCHAR(255),
    doc_qualification VARCHAR(255),
    doc_cnic VARCHAR(255),
    password VARCHAR(255) NOT NULL,
    verification_status ENUM('pending','approved','rejected') DEFAULT 'pending',
    is_active BOOLEAN DEFAULT TRUE,
    rating DECIMAL(3,2) DEFAULT 0,
    total_reviews INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (specialization_id) REFERENCES specializations(id)
);

-- Doctor Availability Table
CREATE TABLE doctor_availability (
    id INT PRIMARY KEY AUTO_INCREMENT,
    doctor_id INT NOT NULL,
    day_of_week ENUM('monday','tuesday','wednesday','thursday','friday','saturday','sunday'),
    start_time TIME,
    end_time TIME,
    consultation_duration INT DEFAULT 30,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE
);

-- Appointments Table
CREATE TABLE appointments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_no VARCHAR(20) UNIQUE NOT NULL,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,
    consultation_type ENUM('online','physical') DEFAULT 'online',
    status ENUM('pending','confirmed','completed','cancelled','rescheduled') DEFAULT 'pending',
    symptoms TEXT,
    consultation_fee DECIMAL(10,2),
    payment_status ENUM('pending','paid','refunded') DEFAULT 'pending',
    payment_method VARCHAR(50),
    transaction_id VARCHAR(100),
    meeting_link VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

-- Medical Records Table
CREATE TABLE medical_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT,
    appointment_id INT,
    record_type ENUM('consultation','prescription','lab_report','xray','mri','ct_scan','ultrasound','vaccination','other') NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    file_path VARCHAR(255),
    record_date DATE NOT NULL,
    created_by_type ENUM('doctor','patient','admin') DEFAULT 'doctor',
    is_visible_to_patient BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
);

-- Prescriptions Table
CREATE TABLE prescriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_id INT,
    medicines JSON,
    instructions TEXT,
    follow_up_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
);

-- Patient Health Info Table
CREATE TABLE patient_health_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    allergies TEXT,
    chronic_diseases TEXT,
    current_medications TEXT,
    blood_group VARCHAR(10),
    height DECIMAL(5,2),
    weight DECIMAL(5,2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    UNIQUE KEY unique_patient (patient_id)
);

-- Payments Table
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    appointment_id INT NOT NULL,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50),
    transaction_id VARCHAR(100),
    status ENUM('pending','completed','failed','refunded') DEFAULT 'pending',
    payment_gateway_response JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id),
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id)
);

-- Notifications Table
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_type ENUM('patient','doctor','admin') NOT NULL,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50),
    channel ENUM('email','sms','whatsapp','in_app') DEFAULT 'in_app',
    is_read BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reviews Table
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (appointment_id) REFERENCES appointments(id)
);

-- Admin Users Table
CREATE TABLE admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('super_admin','admin','moderator') DEFAULT 'admin',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Audit Logs Table
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_type ENUM('patient','doctor','admin') NOT NULL,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System Settings Table
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert Default Admin User
-- Password: "password" (hashed with bcrypt)
INSERT INTO admin_users (username, full_name, email, password, role) 
VALUES ('admin', 'System Administrator', 'admin@healthlink.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');

-- Insert System Settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'HealthLink', 'Website name'),
('site_email', 'info@healthlink.com', 'Contact email address'),
('site_phone', '+92-300-1234567', 'Contact phone number'),
('consultation_duration', '30', 'Default consultation duration in minutes'),
('payment_gateway', 'stripe', 'Payment gateway provider'),
('max_file_size', '5242880', 'Maximum file upload size in bytes (5MB)'),
('allowed_file_types', 'jpg,jpeg,png,pdf,doc,docx', 'Allowed file extensions'),
('smtp_host', 'smtp.gmail.com', 'SMTP server host'),
('smtp_port', '587', 'SMTP server port'),
('smtp_username', '', 'SMTP username'),
('smtp_password', '', 'SMTP password'),
('appointment_reminder_hours', '24', 'Send reminder X hours before appointment'),
('doctor_verification_required', '1', 'Require admin verification for doctors'),
('patient_registration_enabled', '1', 'Allow new patient registrations'),
('maintenance_mode', '0', 'System maintenance mode');

-- Insert Sample Doctors (Pre-approved for testing)
INSERT INTO doctors (
    full_name, email, phone, gender, pmc_license_number, specialization_id, 
    qualification, experience_years, consultation_fee, clinic_name, clinic_address, 
    city, bio, password, verification_status, created_at
) VALUES 
(
    'Dr. Ahmad Ali', 'ahmad.ali@healthlink.com', '+92-301-1234567', 'male', 
    'PMC-12345', 1, 'MBBS, FCPS (Medicine)', 8, 2000.00, 
    'City Hospital', 'Main Boulevard, Gulberg', 'Lahore', 
    'Experienced general physician with 8 years of practice in internal medicine and preventive healthcare.',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', '2024-01-15 09:00:00'
),
(
    'Dr. Fatima Khan', 'fatima.khan@healthlink.com', '+92-302-2345678', 'female', 
    'PMC-23456', 2, 'MBBS, FCPS (Cardiology)', 12, 3500.00, 
    'Heart Care Center', 'MM Alam Road, Gulberg III', 'Lahore', 
    'Specialized cardiologist with expertise in interventional cardiology and heart disease prevention.',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', '2024-01-16 10:30:00'
),
(
    'Dr. Hassan Raza', 'hassan.raza@healthlink.com', '+92-303-3456789', 'male', 
    'PMC-34567', 4, 'MBBS, FCPS (Pediatrics)', 6, 2500.00, 
    'Children Hospital', 'Ferozepur Road', 'Lahore', 
    'Dedicated pediatrician focused on child healthcare, vaccinations, and developmental assessments.',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', '2024-01-17 14:15:00'
),
(
    'Dr. Ayesha Ahmed', 'ayesha.ahmed@healthlink.com', '+92-304-4567890', 'female', 
    'PMC-45678', 3, 'MBBS, FCPS (Dermatology)', 5, 2800.00, 
    'Skin Care Clinic', 'DHA Phase 5', 'Lahore', 
    'Expert dermatologist specializing in skin disorders, cosmetic procedures, and aesthetic treatments.',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'approved', '2024-01-18 11:45:00'
);

-- Insert Sample Patients (For testing)
INSERT INTO patients (
    health_id, full_name, email, phone, gender, date_of_birth, 
    address, city, emergency_contact_name, emergency_contact_phone, 
    password, created_at
) VALUES 
(
    'HL-2024-00001', 'Muhammad Usman', 'usman@email.com', '+92-301-1111111', 'male', '1990-05-15',
    'House 123, Block A, Johar Town', 'Lahore', 'Fatima Usman', '+92-301-2222222',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '2024-01-20 09:30:00'
),
(
    'HL-2024-00002', 'Aisha Malik', 'aisha@email.com', '+92-302-3333333', 'female', '1985-08-22',
    'Flat 456, Gulberg II', 'Lahore', 'Ahmed Malik', '+92-302-4444444',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '2024-01-21 14:20:00'
);

-- Insert Sample Patient Health Info
INSERT INTO patient_health_info (patient_id, blood_group, height, weight, allergies, chronic_diseases) VALUES
(1, 'B+', 175.5, 70.2, 'Peanuts, Dust', 'Hypertension'),
(2, 'A+', 160.0, 55.8, 'None known', 'Diabetes Type 2');

-- Insert Sample Appointments
INSERT INTO appointments (
    appointment_no, patient_id, doctor_id, appointment_date, appointment_time, 
    consultation_type, symptoms, consultation_fee, status, created_at
) VALUES 
(
    'APT-2024-00001', 1, 1, '2024-12-30', '10:00:00', 'online', 
    'Fever and headache for 2 days', 2000.00, 'confirmed', '2024-12-28 15:30:00'
),
(
    'APT-2024-00002', 2, 2, '2024-12-31', '14:30:00', 'physical', 
    'Chest pain and shortness of breath', 3500.00, 'pending', '2024-12-29 09:15:00'
);

-- Insert Sample Medical Records
INSERT INTO medical_records (
    patient_id, doctor_id, appointment_id, record_type, title, 
    description, record_date, created_by_type
) VALUES 
(
    1, 1, 1, 'consultation', 'General Checkup - Fever', 
    'Patient presented with fever and headache. Physical examination normal. Prescribed paracetamol and rest.', 
    '2024-12-30', 'doctor'
),
(
    2, 2, 2, 'consultation', 'Cardiac Assessment', 
    'Initial consultation for chest pain. ECG ordered. Patient advised follow-up.', 
    '2024-12-31', 'doctor'
);

-- Insert Sample Reviews
INSERT INTO reviews (patient_id, doctor_id, appointment_id, rating, review_text) VALUES
(1, 1, 1, 5, 'Excellent doctor! Very professional and caring. Highly recommend.'),
(2, 2, 2, 4, 'Good consultation. Doctor explained everything clearly.');

-- Update Doctor Ratings (based on reviews)
UPDATE doctors SET rating = 5.0, total_reviews = 1 WHERE id = 1;
UPDATE doctors SET rating = 4.0, total_reviews = 1 WHERE id = 2;

-- Create Indexes for Better Performance
CREATE INDEX idx_patients_email ON patients(email);
CREATE INDEX idx_patients_health_id ON patients(health_id);
CREATE INDEX idx_doctors_email ON doctors(email);
CREATE INDEX idx_doctors_pmc ON doctors(pmc_license_number);
CREATE INDEX idx_doctors_specialization ON doctors(specialization_id);
CREATE INDEX idx_appointments_date ON appointments(appointment_date);
CREATE INDEX idx_appointments_patient ON appointments(patient_id);
CREATE INDEX idx_appointments_doctor ON appointments(doctor_id);
CREATE INDEX idx_medical_records_patient ON medical_records(patient_id);
CREATE INDEX idx_notifications_user ON notifications(user_type, user_id);

-- Display Success Message
SELECT 'HealthLink Database Created Successfully!' as Status,
       'Database Name: healthlink_db' as Info,
       'Admin Login: admin / password' as Login,
       'Ready to use!' as Message;