-- Migration 002: item catalog / surgery packages / OT store orders / billing
-- integration / per-resource user scoping / emergency admission fields.
-- Safe to run on a fresh DB (schema.sql) or on top of the original v1 backend.

ALTER TABLE users DROP CONSTRAINT IF EXISTS users_department_check;
ALTER TABLE users ADD CONSTRAINT users_department_check
  CHECK (department IN ('all','ward','preop','ot','recovery','otstore'));
ALTER TABLE users ADD COLUMN IF NOT EXISTS assigned_ot_id TEXT REFERENCES ots(id) ON DELETE SET NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS assigned_ward_id TEXT REFERENCES wards(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS item_catalog (
  id          TEXT PRIMARY KEY,
  s_no        TEXT,
  item_code   TEXT NOT NULL,
  description TEXT NOT NULL,
  uom         TEXT,
  item_group  TEXT,
  item_type   TEXT,
  UNIQUE(item_code)
);

CREATE TABLE IF NOT EXISTS surgery_packages (
  id   TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS surgery_package_items (
  id          TEXT PRIMARY KEY,
  package_id  TEXT NOT NULL REFERENCES surgery_packages(id) ON DELETE CASCADE,
  catalog_id  TEXT NOT NULL REFERENCES item_catalog(id) ON DELETE CASCADE,
  qty         INTEGER NOT NULL DEFAULT 1,
  UNIQUE(package_id, catalog_id)
);

CREATE TABLE IF NOT EXISTS patient_item_orders (
  id           TEXT PRIMARY KEY,
  patient_id   TEXT NOT NULL REFERENCES patients(id) ON DELETE CASCADE,
  catalog_id   TEXT REFERENCES item_catalog(id) ON DELETE SET NULL,
  item_code    TEXT,
  description  TEXT NOT NULL,
  uom          TEXT,
  item_group   TEXT,
  item_type    TEXT,
  qty          INTEGER NOT NULL DEFAULT 1,
  used_qty     INTEGER,
  status       TEXT NOT NULL DEFAULT 'ordered' CHECK (status IN
                 ('ordered','issued','received','used','returned','billed','billing_failed')),
  ordered_by   TEXT, ordered_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  issued_by    TEXT, issued_at    TIMESTAMPTZ,
  received_by  TEXT, received_at  TIMESTAMPTZ,
  billed_at    TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_item_orders_patient ON patient_item_orders(patient_id);
CREATE INDEX IF NOT EXISTS idx_item_orders_status ON patient_item_orders(status);

CREATE TABLE IF NOT EXISTS integration_config (
  id              INTEGER PRIMARY KEY DEFAULT 1,
  hms_api_url     TEXT DEFAULT '',
  hms_api_key     TEXT DEFAULT '',
  hms_auth_header TEXT DEFAULT 'Authorization',
  hms_auth_scheme TEXT DEFAULT 'Bearer',
  CONSTRAINT single_row CHECK (id = 1)
);
INSERT INTO integration_config (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

ALTER TABLE patients ADD COLUMN IF NOT EXISTS emergency_admission BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE patients ADD COLUMN IF NOT EXISTS emergency_reason TEXT;
ALTER TABLE patients ADD COLUMN IF NOT EXISTS emergency_admitted_by TEXT;
ALTER TABLE patients ADD COLUMN IF NOT EXISTS emergency_admitted_at TIMESTAMPTZ;