-- ============================================
-- EMC Extended Schema - New Modules
-- Run this AFTER existing schema.sql
-- ============================================

USE hospital_db;

-- ============================================
-- MULTI-TENANCY: Add hospital_id to core tables
-- ============================================

-- hospitals table (managed by superadmin)
CREATE TABLE IF NOT EXISTS hospitals (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  address TEXT,
  phone VARCHAR(20),
  email VARCHAR(100),
  license_number VARCHAR(100),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- subscription plans
CREATE TABLE IF NOT EXISTS subscription_plans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  monthly_price DECIMAL(10,2) DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- hospital subscriptions
CREATE TABLE IF NOT EXISTS hospital_subscriptions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hospital_id INT NOT NULL,
  plan_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  amount_paid DECIMAL(10,2) DEFAULT 0.00,
  payment_status ENUM('paid','pending','overdue') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

-- which features each hospital has enabled
CREATE TABLE IF NOT EXISTS hospital_features (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hospital_id INT NOT NULL,
  feature ENUM('appointments','pharmacy','laboratory','emergency','sehat_card','reports','ward') NOT NULL,
  is_enabled BOOLEAN DEFAULT TRUE,
  UNIQUE KEY unique_hospital_feature (hospital_id, feature),
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE
);

-- Add hospital_id to users (nullable for superadmin)
ALTER TABLE users ADD COLUMN IF NOT EXISTS hospital_id INT DEFAULT NULL AFTER id;
ALTER TABLE users ADD CONSTRAINT IF NOT EXISTS fk_users_hospital FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE;

-- Add hospital_id to patients
ALTER TABLE patients ADD COLUMN IF NOT EXISTS hospital_id INT DEFAULT NULL AFTER id;
ALTER TABLE patients ADD CONSTRAINT IF NOT EXISTS fk_patients_hospital FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE;

-- superadmin role
INSERT IGNORE INTO roles (name) VALUES ('superadmin'), ('laboratorian'), ('emergency_staff');

-- ============================================
-- MODULE 1: LABORATORY
-- ============================================

-- lab test catalog
CREATE TABLE IF NOT EXISTS lab_tests (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hospital_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50),
  category VARCHAR(100),
  price DECIMAL(10,2) DEFAULT 0.00,
  normal_range VARCHAR(200),
  unit VARCHAR(50),
  sample_type ENUM('blood','urine','stool','swab','other') DEFAULT 'blood',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  INDEX idx_hospital (hospital_id)
);

-- lab orders (doctor orders tests for patient)
CREATE TABLE IF NOT EXISTS lab_orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(30) UNIQUE NOT NULL,
  hospital_id INT NOT NULL,
  patient_id INT NOT NULL,
  doctor_id INT DEFAULT NULL,
  appointment_id INT DEFAULT NULL,
  ordered_by INT NOT NULL,
  status ENUM('pending','in_progress','completed','cancelled') DEFAULT 'pending',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE SET NULL,
  FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
  FOREIGN KEY (ordered_by) REFERENCES users(id),
  INDEX idx_hospital (hospital_id),
  INDEX idx_patient (patient_id),
  INDEX idx_status (status)
);

-- individual tests within an order
CREATE TABLE IF NOT EXISTS lab_order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  lab_test_id INT NOT NULL,
  result_value TEXT,
  result_status ENUM('pending','normal','abnormal') DEFAULT 'pending',
  result_entered_by INT DEFAULT NULL,
  result_entered_at TIMESTAMP NULL,
  notes TEXT,
  FOREIGN KEY (order_id) REFERENCES lab_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (lab_test_id) REFERENCES lab_tests(id),
  FOREIGN KEY (result_entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- lab bills (same pattern as pharmacy_sales)
CREATE TABLE IF NOT EXISTS lab_bills (
  id INT PRIMARY KEY AUTO_INCREMENT,
  bill_number VARCHAR(30) UNIQUE NOT NULL,
  hospital_id INT NOT NULL,
  order_id INT NOT NULL,
  patient_id INT NOT NULL,
  billed_by INT NOT NULL,
  total_amount DECIMAL(10,2) DEFAULT 0.00,
  discount DECIMAL(10,2) DEFAULT 0.00,
  net_amount DECIMAL(10,2) DEFAULT 0.00,
  payment_status ENUM('paid','pending','partial') DEFAULT 'paid',
  payment_method ENUM('cash','sehat_card','other') DEFAULT 'cash',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (order_id) REFERENCES lab_orders(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (billed_by) REFERENCES users(id),
  INDEX idx_hospital (hospital_id),
  INDEX idx_patient (patient_id)
);

-- ============================================
-- MODULE 2: EMERGENCY
-- ============================================

CREATE TABLE IF NOT EXISTS emergency_cases (
  id INT PRIMARY KEY AUTO_INCREMENT,
  case_number VARCHAR(30) UNIQUE NOT NULL,
  hospital_id INT NOT NULL,
  patient_id INT DEFAULT NULL,
  patient_name VARCHAR(100),
  patient_age INT,
  patient_gender ENUM('male','female','other'),
  patient_cnic VARCHAR(15),
  patient_mobile VARCHAR(15),
  case_type ENUM('accident','cardiac','respiratory','neurological','surgical','poisoning','other') DEFAULT 'other',
  severity ENUM('critical','serious','moderate','minor') DEFAULT 'moderate',
  chief_complaint TEXT NOT NULL,
  initial_assessment TEXT,
  assigned_doctor_id INT DEFAULT NULL,
  assigned_staff_id INT DEFAULT NULL,
  status ENUM('active','stable','admitted','discharged','expired','transferred') DEFAULT 'active',
  arrival_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  discharge_time TIMESTAMP NULL,
  notes TEXT,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_doctor_id) REFERENCES doctors(id) ON DELETE SET NULL,
  FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_hospital (hospital_id),
  INDEX idx_status (status),
  INDEX idx_severity (severity)
);

-- emergency staff duty schedule
CREATE TABLE IF NOT EXISTS emergency_schedules (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hospital_id INT NOT NULL,
  staff_id INT NOT NULL,
  shift_date DATE NOT NULL,
  shift ENUM('morning','evening','night') NOT NULL,
  shift_start TIME,
  shift_end TIME,
  is_on_duty BOOLEAN DEFAULT TRUE,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (staff_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_staff_shift (staff_id, shift_date, shift),
  INDEX idx_hospital_date (hospital_id, shift_date)
);

-- ============================================
-- MODULE 3: SEHAT CARD
-- ============================================

-- sehat card packages (what services are covered)
CREATE TABLE IF NOT EXISTS sehat_card_packages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hospital_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  category ENUM('opd','ipd','surgery','diagnostic','maternity','other') DEFAULT 'opd',
  covered_services TEXT,
  max_limit DECIMAL(10,2) DEFAULT 0.00,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE
);

-- sehat card registered patients
CREATE TABLE IF NOT EXISTS sehat_card_patients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  hospital_id INT NOT NULL,
  patient_id INT NOT NULL,
  card_number VARCHAR(50) NOT NULL,
  cnic VARCHAR(15) NOT NULL,
  family_head_name VARCHAR(100),
  is_eligible BOOLEAN DEFAULT TRUE,
  registration_date DATE,
  expiry_date DATE,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_card (hospital_id, card_number),
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  INDEX idx_cnic (cnic),
  INDEX idx_card (card_number)
);

-- sehat card claims per visit
CREATE TABLE IF NOT EXISTS sehat_card_claims (
  id INT PRIMARY KEY AUTO_INCREMENT,
  claim_number VARCHAR(30) UNIQUE NOT NULL,
  hospital_id INT NOT NULL,
  sehat_patient_id INT NOT NULL,
  patient_id INT NOT NULL,
  package_id INT DEFAULT NULL,
  appointment_id INT DEFAULT NULL,
  lab_order_id INT DEFAULT NULL,
  service_type ENUM('opd','lab','medicine','surgery','other') NOT NULL,
  service_description TEXT,
  claimed_amount DECIMAL(10,2) DEFAULT 0.00,
  approved_amount DECIMAL(10,2) DEFAULT 0.00,
  status ENUM('pending','approved','rejected','submitted') DEFAULT 'pending',
  rejection_reason TEXT,
  claim_date DATE DEFAULT (CURRENT_DATE),
  processed_by INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
  FOREIGN KEY (sehat_patient_id) REFERENCES sehat_card_patients(id),
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (package_id) REFERENCES sehat_card_packages(id) ON DELETE SET NULL,
  FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
  FOREIGN KEY (lab_order_id) REFERENCES lab_orders(id) ON DELETE SET NULL,
  FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_hospital (hospital_id),
  INDEX idx_status (status)
);

-- ============================================
-- SEED: Default subscription plans
-- ============================================

INSERT IGNORE INTO subscription_plans (name, description, monthly_price) VALUES
('Basic', 'Appointments + Doctor Dashboard only', 5000.00),
('Standard', 'Basic + Pharmacy + Laboratory', 10000.00),
('Premium', 'All modules including Emergency + Sehat Card', 18000.00);

-- ============================================
-- SEED: Default hospital (for existing data)
-- ============================================

INSERT IGNORE INTO hospitals (id, name, address, phone, email) VALUES
(1, 'Default Hospital', 'Pakistan', '03000000000', 'admin@hospital.com');

-- Insert default features for hospital 1
INSERT IGNORE INTO hospital_features (hospital_id, feature, is_enabled) VALUES
(1, 'appointments', TRUE),
(1, 'pharmacy', TRUE),
(1, 'laboratory', TRUE),
(1, 'emergency', TRUE),
(1, 'sehat_card', TRUE),
(1, 'reports', TRUE);

-- Update existing users to belong to hospital 1
UPDATE users SET hospital_id = 1 WHERE hospital_id IS NULL AND role_id != (SELECT id FROM roles WHERE name = 'superadmin' LIMIT 1);

-- Update existing patients to belong to hospital 1
UPDATE patients SET hospital_id = 1 WHERE hospital_id IS NULL;