-- ============================================================
--  Meridian Clinic — MySQL structure (MySQL 5.7+ / MariaDB 10+)
--  Import once:  mysql -u root -p < db.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS meridian_clinic
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE meridian_clinic;

-- ------------------------------------------------------------
-- USERS (staff + role based access)
-- ------------------------------------------------------------
CREATE TABLE users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(50)  NOT NULL UNIQUE,
  password    VARCHAR(255) NOT NULL,
  full_name   VARCHAR(100) NOT NULL,
  role        ENUM('admin','doctor','nurse','reception','pharmacist','cashier') NOT NULL DEFAULT 'reception',
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PATIENTS (master index + QR binding + consent flags)
-- ------------------------------------------------------------
CREATE TABLE patients (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  patient_code      VARCHAR(20)  NOT NULL UNIQUE,
  qr_token          VARCHAR(64)  NOT NULL UNIQUE,
  full_name         VARCHAR(100) NOT NULL,
  dob               DATE         NULL,
  gender            ENUM('Male','Female','Other') NOT NULL DEFAULT 'Male',
  national_id       VARCHAR(30)  NULL,
  phone             VARCHAR(20)  NULL,
  address           VARCHAR(255) NULL,
  allergies         TEXT         NULL,
  chronic_conditions TEXT        NULL,
  consent_treatment TINYINT(1)   NOT NULL DEFAULT 1,
  consent_sms       TINYINT(1)   NOT NULL DEFAULT 1,
  consent_voice     TINYINT(1)   NOT NULL DEFAULT 0,
  created_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- ENCOUNTERS (SOAP notes)
-- ------------------------------------------------------------
CREATE TABLE encounters (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  encounter_no  VARCHAR(20)  NOT NULL UNIQUE,
  patient_id    INT          NOT NULL,
  doctor_id     INT          NOT NULL,
  encounter_date DATE        NOT NULL,
  soap_s        TEXT         NULL,
  soap_o        TEXT         NULL,
  soap_a        TEXT         NULL,
  soap_p        TEXT         NULL,
  diagnosis     VARCHAR(255) NULL,
  icd_code      VARCHAR(20)  NULL,
  status        ENUM('draft','signed') NOT NULL DEFAULT 'draft',
  signed_by     INT          NULL,
  signed_at     DATETIME     NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (doctor_id)  REFERENCES users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PRESCRIPTIONS (one row per drug line)
-- ------------------------------------------------------------
CREATE TABLE prescriptions (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  encounter_id INT          NOT NULL,
  patient_id   INT          NOT NULL,
  doctor_id    INT          NOT NULL,
  medicine_name VARCHAR(100) NOT NULL,
  strength     VARCHAR(50)  NULL,
  dose         VARCHAR(50)  NULL,
  frequency    VARCHAR(50)  NULL,
  duration     VARCHAR(50)  NULL,
  notes        VARCHAR(255) NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (encounter_id) REFERENCES encounters(id) ON DELETE CASCADE,
  FOREIGN KEY (patient_id)   REFERENCES patients(id),
  FOREIGN KEY (doctor_id)    REFERENCES users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- LAB / DIAGNOSTIC ORDERS
-- ------------------------------------------------------------
CREATE TABLE lab_orders (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  encounter_id INT          NOT NULL,
  patient_id   INT          NOT NULL,
  doctor_id    INT          NOT NULL,
  test_name    VARCHAR(150) NOT NULL,
  test_type    ENUM('Lab','Imaging') NOT NULL DEFAULT 'Lab',
  result_text  TEXT         NULL,
  file_path    VARCHAR(255) NULL,
  status       ENUM('Ordered','In Progress','Completed') NOT NULL DEFAULT 'Ordered',
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (encounter_id) REFERENCES encounters(id) ON DELETE CASCADE,
  FOREIGN KEY (patient_id)   REFERENCES patients(id),
  FOREIGN KEY (doctor_id)    REFERENCES users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- APPOINTMENTS + QUEUE TOKENS
-- ------------------------------------------------------------
CREATE TABLE appointments (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  patient_id  INT         NOT NULL,
  doctor_id   INT         NOT NULL,
  appt_date   DATE        NOT NULL,
  appt_time   TIME        NOT NULL,
  token_no    INT         NOT NULL DEFAULT 0,
  appt_type   ENUM('Walk-in','Scheduled') NOT NULL DEFAULT 'Scheduled',
  status      ENUM('Booked','Arrived','In-Consultation','Completed','Cancelled')
              NOT NULL DEFAULT 'Booked',
  remarks     VARCHAR(255) NULL,
  created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (doctor_id)  REFERENCES users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- PHARMACY: MEDICINES + BATCHES + SALES
-- ------------------------------------------------------------
CREATE TABLE medicines (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  generic_name  VARCHAR(100) NULL,
  form          VARCHAR(30)  NULL,          -- tablet / syrup / capsule
  strength      VARCHAR(30)  NULL,
  reorder_level INT          NOT NULL DEFAULT 20,
  sale_price    DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE stock_batches (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  medicine_id INT           NOT NULL,
  batch_no    VARCHAR(40)   NOT NULL,
  expiry_date DATE          NOT NULL,
  qty         INT           NOT NULL DEFAULT 0,
  cost_price  DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (medicine_id) REFERENCES medicines(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sales (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  patient_id INT           NOT NULL,
  sold_by    INT           NOT NULL,
  total      DECIMAL(10,2) NOT NULL DEFAULT 0,
  sold_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (sold_by)    REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE sale_items (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  sale_id    INT           NOT NULL,
  medicine_id INT          NOT NULL,
  batch_id   INT           NOT NULL,
  qty        INT           NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (sale_id)    REFERENCES sales(id) ON DELETE CASCADE,
  FOREIGN KEY (medicine_id) REFERENCES medicines(id),
  FOREIGN KEY (batch_id)   REFERENCES stock_batches(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- BILLING: INVOICES + ITEMS + PAYMENTS
-- ------------------------------------------------------------
CREATE TABLE invoices (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no  VARCHAR(20)  NOT NULL UNIQUE,
  patient_id  INT          NOT NULL,
  encounter_id INT         NULL,
  total       DECIMAL(10,2) NOT NULL DEFAULT 0,
  discount    DECIMAL(10,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status      ENUM('Unpaid','Partial','Paid') NOT NULL DEFAULT 'Unpaid',
  created_by  INT          NOT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (patient_id)  REFERENCES patients(id),
  FOREIGN KEY (encounter_id) REFERENCES encounters(id),
  FOREIGN KEY (created_by)  REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE invoice_items (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT           NOT NULL,
  item_type   ENUM('Consultation','Medicine','Lab','Procedure','Other') NOT NULL DEFAULT 'Other',
  description VARCHAR(150)  NOT NULL,
  qty         INT           NOT NULL DEFAULT 1,
  unit_price  DECIMAL(10,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id  INT           NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  method      ENUM('Cash','Card','QR','Insurance') NOT NULL DEFAULT 'Cash',
  received_by INT           NOT NULL,
  paid_at     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invoice_id)  REFERENCES invoices(id),
  FOREIGN KEY (received_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- AUDIT TRAIL (immutable access / change log)
-- ------------------------------------------------------------
CREATE TABLE audit_log (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT          NOT NULL,
  action     VARCHAR(50)  NOT NULL,
  table_name VARCHAR(50)  NULL,
  record_id  INT          NULL,
  details    VARCHAR(255) NULL,
  ip_address VARCHAR(45)  NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- SAMPLE DATA
-- ------------------------------------------------------------
INSERT INTO medicines (name, generic_name, form, strength, reorder_level, sale_price) VALUES
('Amoxicillin', 'Amoxicillin', 'Capsule', '500mg', 50, 2.50),
('Paracetamol', 'Acetaminophen', 'Tablet', '500mg', 100, 0.50),
('Metformin', 'Metformin HCl', 'Tablet', '500mg', 80, 1.20),
('Amlodipine', 'Amlodipine Besylate', 'Tablet', '5mg', 60, 1.00),
('Panadeine', 'Paracetamol + Codeine', 'Tablet', '500/30mg', 40, 3.00),
('Omeprazole', 'Omeprazole', 'Capsule', '20mg', 50, 1.80),
('Cetirizine', 'Cetirizine HCl', 'Tablet', '10mg', 60, 0.80),
('ORS Sachet', 'Oral Rehydration Salts', 'Sachet', '20g', 30, 1.00);

INSERT INTO stock_batches (medicine_id, batch_no, expiry_date, qty, cost_price) VALUES
(1, 'AMX-2401', '2027-03-31', 200, 1.20),
(2, 'PCM-2412', '2026-12-31', 500, 0.20),
(3, 'MET-2408', '2026-08-31', 300, 0.60),
(4, 'AML-2410', '2026-10-31', 250, 0.45),
(5, 'PAN-2406', '2026-06-30', 150, 1.50),
(6, 'OME-2411', '2026-11-30', 180, 0.90);