-- ============================================================
-- Hospital Management System - Database Schema
-- Engine : MySQL 5.7+ / MariaDB 10+
-- Charset: utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS hms_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE hms_db;

-- ------------------------------------------------------------
-- USERS  (login accounts for every role)
-- ------------------------------------------------------------
CREATE TABLE users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(120)        NOT NULL,
    email           VARCHAR(150)        NOT NULL UNIQUE,
    password        VARCHAR(255)        NOT NULL,
    role            ENUM('admin','doctor','receptionist','patient') NOT NULL DEFAULT 'patient',
    phone           VARCHAR(20)         DEFAULT NULL,
    photo           VARCHAR(255)        DEFAULT NULL,
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at      DATETIME            DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME            DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- DOCTORS  (extends users where role = doctor)
-- ------------------------------------------------------------
CREATE TABLE doctors (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    specialization  VARCHAR(120) NOT NULL,
    qualification   VARCHAR(150) DEFAULT NULL,
    experience_years TINYINT UNSIGNED DEFAULT 0,
    consultation_fee DECIMAL(10,2) DEFAULT 0.00,
    available_days  VARCHAR(100) DEFAULT NULL,   -- e.g. "Mon,Tue,Wed"
    available_time  VARCHAR(100) DEFAULT NULL,   -- e.g. "09:00-14:00"
    room_no         VARCHAR(20)  DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PATIENTS  (extends users where role = patient, may also be
-- walk-in patients registered by receptionist with no login)
-- ------------------------------------------------------------
CREATE TABLE patients (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED DEFAULT NULL,
    name            VARCHAR(120) NOT NULL,
    gender          ENUM('male','female','other') DEFAULT 'male',
    dob             DATE DEFAULT NULL,
    blood_group     VARCHAR(5)  DEFAULT NULL,
    phone           VARCHAR(20) DEFAULT NULL,
    email           VARCHAR(150) DEFAULT NULL,
    address         VARCHAR(255) DEFAULT NULL,
    guardian_name   VARCHAR(120) DEFAULT NULL,
    blood_pressure  VARCHAR(20) DEFAULT NULL,
    allergies       VARCHAR(255) DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- APPOINTMENTS
-- ------------------------------------------------------------
CREATE TABLE appointments (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    patient_id      INT UNSIGNED NOT NULL,
    doctor_id       INT UNSIGNED NOT NULL,
    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,
    reason          VARCHAR(255) DEFAULT NULL,
    status          ENUM('pending','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending',
    created_by      INT UNSIGNED DEFAULT NULL,   -- user id who booked it
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id)  REFERENCES doctors(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PRESCRIPTIONS
-- ------------------------------------------------------------
CREATE TABLE prescriptions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    appointment_id  INT UNSIGNED DEFAULT NULL,
    patient_id      INT UNSIGNED NOT NULL,
    doctor_id       INT UNSIGNED NOT NULL,
    diagnosis       VARCHAR(255) DEFAULT NULL,
    notes           TEXT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id)  REFERENCES doctors(id)  ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE prescription_items (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prescription_id INT UNSIGNED NOT NULL,
    medicine_id     INT UNSIGNED DEFAULT NULL,
    medicine_name   VARCHAR(150) NOT NULL,
    dosage          VARCHAR(100) DEFAULT NULL,
    duration        VARCHAR(100) DEFAULT NULL,
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PHARMACY / MEDICINE INVENTORY
-- ------------------------------------------------------------
CREATE TABLE medicines (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    category        VARCHAR(100) DEFAULT NULL,
    manufacturer    VARCHAR(150) DEFAULT NULL,
    unit_price      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    quantity        INT UNSIGNED NOT NULL DEFAULT 0,
    reorder_level   INT UNSIGNED NOT NULL DEFAULT 10,
    expiry_date     DATE DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- WARDS & BEDS
-- ------------------------------------------------------------
CREATE TABLE wards (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    type            ENUM('general','private','icu','emergency') NOT NULL DEFAULT 'general',
    charge_per_day  DECIMAL(10,2) DEFAULT 0.00,
    total_beds      INT UNSIGNED DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE beds (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ward_id         INT UNSIGNED NOT NULL,
    bed_number      VARCHAR(20) NOT NULL,
    status          ENUM('available','occupied','maintenance') NOT NULL DEFAULT 'available',
    FOREIGN KEY (ward_id) REFERENCES wards(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE admissions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    patient_id      INT UNSIGNED NOT NULL,
    bed_id          INT UNSIGNED NOT NULL,
    doctor_id       INT UNSIGNED DEFAULT NULL,
    admit_date      DATETIME NOT NULL,
    discharge_date  DATETIME DEFAULT NULL,
    status          ENUM('admitted','discharged') NOT NULL DEFAULT 'admitted',
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (bed_id) REFERENCES beds(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- LAB TESTS & REPORTS
-- ------------------------------------------------------------
CREATE TABLE lab_tests (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    price           DECIMAL(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;

CREATE TABLE lab_reports (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    patient_id      INT UNSIGNED NOT NULL,
    doctor_id       INT UNSIGNED DEFAULT NULL,
    test_id         INT UNSIGNED DEFAULT NULL,
    test_name       VARCHAR(150) DEFAULT NULL,
    result          TEXT DEFAULT NULL,
    report_file     VARCHAR(255) DEFAULT NULL,
    status          ENUM('pending','completed') NOT NULL DEFAULT 'pending',
    report_date     DATE DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE SET NULL,
    FOREIGN KEY (test_id) REFERENCES lab_tests(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- BILLING
-- ------------------------------------------------------------
CREATE TABLE bills (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    patient_id      INT UNSIGNED NOT NULL,
    appointment_id  INT UNSIGNED DEFAULT NULL,
    total_amount    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    paid_amount     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status          ENUM('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid',
    payment_method  ENUM('cash','card','online') DEFAULT 'cash',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE bill_items (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bill_id         INT UNSIGNED NOT NULL,
    description     VARCHAR(255) NOT NULL,
    amount          DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ACTIVITY LOG (nice-to-have for an "advanced" feel)
-- ------------------------------------------------------------
CREATE TABLE activity_logs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED DEFAULT NULL,
    action          VARCHAR(255) NOT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- SEED DATA
-- ============================================================

-- Default admin  (password: Admin@123)
INSERT INTO users (name, email, password, role, phone) VALUES
('System Admin', 'admin@hms.com', '$2y$10$OUHIaVrzVKF1YxgtWOibJutfKcLFB89yh2unr2RQTIYWzf7Z5TUy2', 'admin', '03000000000');

-- Sample doctor login (password: Doctor@123)
INSERT INTO users (name, email, password, role, phone) VALUES
('Dr. Ahmed Khan', 'doctor@hms.com', '$2y$10$sA6s1kHGU7cZlbLD7sQhEedWh2C1mOS1tlz3oHMKjKSbQpYBdtIwm', 'doctor', '03001112222');
INSERT INTO doctors (user_id, specialization, qualification, experience_years, consultation_fee, available_days, available_time, room_no)
VALUES (LAST_INSERT_ID(), 'Cardiologist', 'MBBS, FCPS (Cardiology)', 10, 1500.00, 'Mon,Tue,Wed,Thu', '09:00-14:00', '101');

-- Sample receptionist (password: Reception@123)
INSERT INTO users (name, email, password, role, phone) VALUES
('Sara Malik', 'reception@hms.com', '$2y$10$c/Q0a3TX/bBjB77xt4fFMefB2vmDeJnWCQ3apFjby5yhPQNfn/U3W', 'receptionist', '03003334444');

-- Sample ward + beds
INSERT INTO wards (name, type, charge_per_day, total_beds) VALUES
('General Ward A', 'general', 1500.00, 10),
('ICU', 'icu', 8000.00, 5),
('Private Room', 'private', 5000.00, 6);

INSERT INTO beds (ward_id, bed_number, status) VALUES
(1,'A-01','available'),(1,'A-02','available'),(1,'A-03','available'),
(2,'ICU-01','available'),(2,'ICU-02','available'),
(3,'P-01','available'),(3,'P-02','available');

-- Sample lab tests
INSERT INTO lab_tests (name, price) VALUES
('Complete Blood Count (CBC)', 800.00),
('Blood Sugar (Fasting)', 300.00),
('Liver Function Test', 1200.00),
('X-Ray Chest', 1000.00),
('Urine Routine Examination', 400.00);

-- Sample medicines
INSERT INTO medicines (name, category, manufacturer, unit_price, quantity, reorder_level, expiry_date) VALUES
('Panadol 500mg', 'Analgesic', 'GSK', 5.00, 500, 50, '2027-12-31'),
('Augmentin 625mg', 'Antibiotic', 'GSK', 45.00, 200, 30, '2026-10-31'),
('Omeprazole 20mg', 'Antacid', 'Getz Pharma', 12.00, 300, 40, '2027-06-30'),
('Cetirizine 10mg', 'Antihistamine', 'Searle', 8.00, 400, 40, '2027-03-31');