-- ============================================
-- Hospital Management System - MySQL Schema
-- ============================================

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

-- ----------------
-- roles
-- ----------------
CREATE TABLE IF NOT EXISTS roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name ENUM('admin','doctor','receptionist','pharmacy') NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ----------------
-- users
-- ----------------
CREATE TABLE IF NOT EXISTS users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  role_id INT NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  is_active BOOLEAN DEFAULT TRUE,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- ----------------
-- departments
-- ----------------
CREATE TABLE IF NOT EXISTS departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ----------------
-- doctors
-- ----------------
CREATE TABLE IF NOT EXISTS doctors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL UNIQUE,
  department_id INT NOT NULL,
  specialization VARCHAR(100),
  qualification VARCHAR(150),
  experience_years INT DEFAULT 0,
  consultation_fee DECIMAL(10,2) DEFAULT 0.00,
  available_days VARCHAR(100) DEFAULT 'Mon,Tue,Wed,Thu,Fri',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- ----------------
-- receptionists
-- ----------------
CREATE TABLE IF NOT EXISTS receptionists (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL UNIQUE,
  shift ENUM('morning','evening','night') DEFAULT 'morning',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ----------------
-- patients
-- ----------------
CREATE TABLE IF NOT EXISTS patients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  patient_code VARCHAR(20) UNIQUE NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  father_name VARCHAR(100),
  cnic VARCHAR(15),
  mobile VARCHAR(15) NOT NULL,
  age INT,
  gender ENUM('male','female','other') NOT NULL,
  address TEXT,
  blood_group VARCHAR(5),
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_cnic (cnic),
  INDEX idx_mobile (mobile),
  INDEX idx_patient_code (patient_code)
);

-- ----------------
-- appointments
-- ----------------
CREATE TABLE IF NOT EXISTS appointments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  appointment_number VARCHAR(30) UNIQUE NOT NULL,
  patient_id INT NOT NULL,
  doctor_id INT NOT NULL,
  receptionist_id INT NOT NULL,
  department_id INT NOT NULL,
  appointment_date DATE NOT NULL,
  appointment_time TIME,
  disease_description TEXT,
  fees DECIMAL(10,2) DEFAULT 0.00,
  status ENUM('scheduled','in_progress','completed','cancelled','no_show') DEFAULT 'scheduled',
  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),
  FOREIGN KEY (receptionist_id) REFERENCES receptionists(id),
  FOREIGN KEY (department_id) REFERENCES departments(id),
  INDEX idx_doctor_date (doctor_id, appointment_date),
  INDEX idx_patient (patient_id),
  INDEX idx_date (appointment_date)
);

-- ----------------
-- diagnoses
-- ----------------
CREATE TABLE IF NOT EXISTS diagnoses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id INT NOT NULL UNIQUE,
  doctor_id INT NOT NULL,
  patient_id INT NOT NULL,
  diagnosis_text TEXT NOT NULL,
  follow_up_date DATE,
  visit_status ENUM('pending','completed') DEFAULT 'pending',
  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)
);

-- ----------------
-- prescriptions
-- ----------------
CREATE TABLE IF NOT EXISTS prescriptions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id INT NOT NULL,
  doctor_id INT NOT NULL,
  patient_id INT NOT NULL,
  notes TEXT,
  status ENUM('pending','dispensed') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (appointment_id) REFERENCES appointments(id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id)
);

-- ----------------
-- prescription_items
-- ----------------
CREATE TABLE IF NOT EXISTS prescription_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  prescription_id INT NOT NULL,
  medicine_name VARCHAR(150) NOT NULL,
  dosage VARCHAR(100),
  duration VARCHAR(50),
  instructions TEXT,
  FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE
);

-- ----------------
-- medicines (catalog)
-- ----------------
CREATE TABLE IF NOT EXISTS medicines (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  generic_name VARCHAR(150),
  category VARCHAR(100),
  unit VARCHAR(30),
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stock_quantity INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_name (name)
);

-- ----------------
-- pharmacy_sales
-- ----------------
CREATE TABLE IF NOT EXISTS pharmacy_sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  bill_number VARCHAR(30) UNIQUE NOT NULL,
  patient_id INT NOT NULL,
  appointment_id INT DEFAULT NULL,
  prescription_id INT DEFAULT NULL,
  doctor_id INT DEFAULT NULL,
  pharmacy_user_id INT NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(10,2) DEFAULT 0.00,
  net_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  payment_status ENUM('paid','pending','partial') DEFAULT 'paid',
  sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
  FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE SET NULL,
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE SET NULL,
  FOREIGN KEY (pharmacy_user_id) REFERENCES users(id),
  INDEX idx_doctor (doctor_id),
  INDEX idx_patient (patient_id),
  INDEX idx_date (sale_date)
);

-- ----------------
-- pharmacy_sale_items
-- ----------------
CREATE TABLE IF NOT EXISTS pharmacy_sale_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sale_id INT NOT NULL,
  medicine_name VARCHAR(150) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (sale_id) REFERENCES pharmacy_sales(id) ON DELETE CASCADE
);

-- ----------------
-- audit_logs
-- ----------------
CREATE TABLE IF NOT EXISTS audit_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT DEFAULT NULL,
  user_role VARCHAR(50),
  action VARCHAR(100) NOT NULL,
  module VARCHAR(50) NOT NULL,
  record_id INT DEFAULT NULL,
  description TEXT,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_user (user_id),
  INDEX idx_module (module),
  INDEX idx_created (created_at)
);

-- ----------------
-- notifications
-- ----------------
CREATE TABLE IF NOT EXISTS notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  recipient_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  message TEXT,
  type ENUM('appointment','prescription','bill','system') DEFAULT 'system',
  is_read BOOLEAN DEFAULT FALSE,
  reference_id INT DEFAULT NULL,
  reference_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_recipient (recipient_id, is_read)
);

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

INSERT IGNORE INTO roles (name) VALUES ('admin'),('doctor'),('receptionist'),('pharmacy');

INSERT IGNORE INTO departments (name, description) VALUES
('ENT','Ear, Nose and Throat'),
('Cardiology','Heart and Cardiovascular'),
('General Surgery','General Surgical Procedures'),
('Neurology','Brain and Nervous System'),
('Dentistry','Dental Care'),
('Orthopedics','Bones and Joints'),
('Gynecology','Women Health'),
('Pediatrics','Children Health'),
('Dermatology','Skin Care'),
('General Medicine','General OPD');

-- Admin password: Admin@123 (bcrypt hash)
INSERT IGNORE INTO users (role_id, full_name, email, password, phone)
VALUES (1, 'Super Admin', 'admin@hospital.com', '$2b$10$rQnSEm6XDj8L/RwAHr7x7.t8DzM0nN6tGjJ0VkK1Ym5oZBvPqCkUa', '03001234567');