-- OT Flow database schema

CREATE TABLE IF NOT EXISTS users (
  id            TEXT PRIMARY KEY,
  username      TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  full_name     TEXT NOT NULL,
  role          TEXT NOT NULL CHECK (role IN ('superadmin','admin','consultant','manager','user')),
  department    TEXT NOT NULL DEFAULT 'all' CHECK (department IN ('all','ward','preop','ot','recovery')),
  active        BOOLEAN NOT NULL DEFAULT true,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by    TEXT REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS wards (
  id    TEXT PRIMARY KEY,
  name  TEXT NOT NULL,
  floor TEXT
);

CREATE TABLE IF NOT EXISTS rooms (
  id      TEXT PRIMARY KEY,
  ward_id TEXT NOT NULL REFERENCES wards(id) ON DELETE CASCADE,
  name    TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS preop_bays (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  status     TEXT NOT NULL DEFAULT 'vacant' CHECK (status IN ('vacant','reserved','occupied')),
  patient_id TEXT
);

CREATE TABLE IF NOT EXISTS ots (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  floor      TEXT,
  status     TEXT NOT NULL DEFAULT 'vacant' CHECK (status IN ('vacant','reserved','occupied','cleaning')),
  patient_id TEXT
);

CREATE TABLE IF NOT EXISTS beds (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  status     TEXT NOT NULL DEFAULT 'vacant' CHECK (status IN ('vacant','occupied')),
  patient_id TEXT
);

CREATE TABLE IF NOT EXISTS masters (
  id    TEXT PRIMARY KEY,
  type  TEXT NOT NULL CHECK (type IN ('anesthesia_type','anesthetist','drug','ot_item')),
  value TEXT NOT NULL,
  UNIQUE(type, value)
);

CREATE TABLE IF NOT EXISTS patients (
  id                TEXT PRIMARY KEY,
  name              TEXT NOT NULL,
  uhid              TEXT,
  booking_no        TEXT,
  ward_id           TEXT REFERENCES wards(id) ON DELETE SET NULL,
  room_id           TEXT REFERENCES rooms(id) ON DELETE SET NULL,
  bed_label         TEXT,
  age               TEXT,
  sex               TEXT,
  surgery_type      TEXT,
  surgeon           TEXT,
  anesthesia_type   TEXT,
  anesthetist       TEXT,
  drugs_used        JSONB NOT NULL DEFAULT '[]',
  ot_items_used     JSONB NOT NULL DEFAULT '[]',
  recovery_notes    TEXT,
  pending_preop_id  TEXT REFERENCES preop_bays(id) ON DELETE SET NULL,
  assigned_preop_id TEXT REFERENCES preop_bays(id) ON DELETE SET NULL,
  pending_ot_id     TEXT REFERENCES ots(id) ON DELETE SET NULL,
  assigned_ot_id    TEXT REFERENCES ots(id) ON DELETE SET NULL,
  ot_name_snapshot  TEXT,
  booking_id        TEXT,
  status            TEXT NOT NULL DEFAULT 'ward' CHECK (status IN
                       ('ward','awaiting_preop_receive','preop','awaiting_ot_receive','ot',
                        'recovery','awaiting_ward_receive','returned')),
  case_cancelled    BOOLEAN NOT NULL DEFAULT false,
  cancel_reason     TEXT,
  cancelled_at      TIMESTAMPTZ,
  cancelled_by      TEXT,
  preop_vitals_log    JSONB NOT NULL DEFAULT '[]',
  recovery_vitals_log JSONB NOT NULL DEFAULT '[]',
  activity          JSONB NOT NULL DEFAULT '[]',
  ward_in           TIMESTAMPTZ,
  sent_to_preop_at  TIMESTAMPTZ,
  preop_in          TIMESTAMPTZ,
  sent_to_ot_at     TIMESTAMPTZ,
  ot_in             TIMESTAMPTZ,
  anes_start        TIMESTAMPTZ,
  surg_start        TIMESTAMPTZ,
  surg_end          TIMESTAMPTZ,
  anes_end          TIMESTAMPTZ,
  ot_out            TIMESTAMPTZ,
  recovery_in       TIMESTAMPTZ,
  sent_to_ward_at   TIMESTAMPTZ,
  ward_return       TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_patients_status ON patients(status);
CREATE INDEX IF NOT EXISTS idx_patients_name ON patients(name);

CREATE TABLE IF NOT EXISTS bookings (
  id                TEXT PRIMARY KEY,
  name              TEXT,
  reg_no            TEXT,
  ip_no             TEXT,
  age               TEXT,
  sex               TEXT,
  bed_no            TEXT,
  ward_text         TEXT,
  booking_no        TEXT,
  ot_text           TEXT,
  ot_id             TEXT REFERENCES ots(id) ON DELETE SET NULL,
  time              TEXT,
  surgeon           TEXT,
  procedure         TEXT,
  status            TEXT NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled','admitted','cancelled')),
  linked_patient_id TEXT REFERENCES patients(id) ON DELETE SET NULL,
  imported_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_log (
  id           TEXT PRIMARY KEY,
  at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  by_user      TEXT,
  by_user_id   TEXT,
  by_role      TEXT,
  patient_id   TEXT,
  patient_name TEXT,
  action       TEXT,
  reason       TEXT
);

ALTER TABLE preop_bays ADD CONSTRAINT fk_preop_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL;
ALTER TABLE ots ADD CONSTRAINT fk_ot_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL;
ALTER TABLE beds ADD CONSTRAINT fk_bed_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL;
ALTER TABLE patients ADD CONSTRAINT fk_patient_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE SET NULL;