-- =====================================================================
-- ZUSahabat Operation Portal — PostgreSQL schema (MVP)
-- Target: PostgreSQL 15+
--
-- Principles enforced in the database (not only in the app):
--   * One master record per merchant (merchant_id ZS-00001 …) referenced by every module
--   * Permanent audit log: UPDATE / DELETE on audit_logs is blocked
--   * Locked months: financial rows in a locked month cannot change except
--     through an approved adjustment request
--   * Executor ≠ verifier (except Full Access users)
--   * Restock order total cannot exceed the merchant's available restock balance
--   * Allocation percentages are data, never hard-coded; each allocation row
--     keeps the percentage that applied when it was created
--
-- The application must set, per request/transaction:
--   SET LOCAL app.user_id = '<users.id uuid>';
--   (and, only when applying an approved adjustment)
--   SET LOCAL app.adjustment_id = '<adjustment_requests.id>';
-- =====================================================================

BEGIN;

CREATE EXTENSION IF NOT EXISTS citext;

-- ---------------------------------------------------------------------
-- Enums
-- ---------------------------------------------------------------------
CREATE TYPE user_role          AS ENUM ('full_access','management','operations','finance');
CREATE TYPE user_status        AS ENUM ('active','inactive');
CREATE TYPE merchant_status    AS ENUM ('applicant','active','inactive','suspended');
CREATE TYPE registration_status AS ENUM ('new_application','under_initial_review','ready_for_location_screening','withdrawn');
CREATE TYPE location_status    AS ENUM ('pending_review','submitted_to_zus','pending_zus_decision','approved','rejected','resubmission_required');
CREATE TYPE document_status    AS ENUM ('missing','submitted','under_verification','verified','rejected','resubmission_required');
CREATE TYPE verification_action AS ENUM ('verify','reject','request_resubmission');
CREATE TYPE onboarding_step    AS ENUM ('agreement','payment','fasspay','heydoc','allianz','training','starter_kit');
CREATE TYPE step_status        AS ENUM ('not_started','in_progress','completed');
CREATE TYPE task_status        AS ENUM ('not_started','in_progress','submitted','pending_verification','completed','rejected');
CREATE TYPE settlement_status  AS ENUM ('matched','partial','unmatched','missing','adjustment_required');
CREATE TYPE bank_txn_status    AS ENUM ('unmatched','investigation_required','matched');
CREATE TYPE restock_status     AS ENUM ('restock_requested','order_submitted','awaiting_delivery','received','completed','cancelled');
CREATE TYPE stock_movement_type AS ENUM ('opening','received','used','count_adjustment');
CREATE TYPE issue_status       AS ENUM ('open','assigned','in_progress','resolved','verified','closed');
CREATE TYPE issue_priority     AS ENUM ('low','medium','high','critical');
CREATE TYPE report_status      AS ENUM ('draft','verified','sent_to_finance');
CREATE TYPE period_status      AS ENUM ('open','locked');
CREATE TYPE adjustment_status  AS ENUM ('pending_approval','approved','rejected');
CREATE TYPE data_source        AS ENUM ('manual','csv_import','api');
CREATE TYPE import_kind        AS ENUM ('sales','settlement','bank_transactions','orders','stock','repayment','merchants');

-- ---------------------------------------------------------------------
-- Users & access
-- ---------------------------------------------------------------------
CREATE TABLE users (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name           text NOT NULL,
  email          citext NOT NULL UNIQUE CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$'),
  role           user_role NOT NULL,
  access_level   text NOT NULL,                 -- human label, e.g. 'FULL ACCESS'
  status         user_status NOT NULL DEFAULT 'active',
  last_login_at  timestamptz,
  created_at     timestamptz NOT NULL DEFAULT now(),
  created_by     uuid REFERENCES users(id)
);

-- Optional fine-grained overrides (e.g. give one Finance user an operational approval right)
CREATE TABLE user_permission_overrides (
  user_id     uuid NOT NULL REFERENCES users(id),
  permission  text NOT NULL,
  granted     boolean NOT NULL,
  granted_by  uuid NOT NULL REFERENCES users(id),
  granted_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, permission)
);

CREATE OR REPLACE FUNCTION current_app_user() RETURNS uuid LANGUAGE sql STABLE AS $$
  SELECT NULLIF(current_setting('app.user_id', true), '')::uuid
$$;

CREATE OR REPLACE FUNCTION is_full_access(uid uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
  SELECT EXISTS (SELECT 1 FROM users WHERE id = uid AND role = 'full_access' AND status = 'active')
$$;

-- ---------------------------------------------------------------------
-- Reference data
-- ---------------------------------------------------------------------
CREATE TABLE aim_branches (code text PRIMARY KEY, name text NOT NULL, state text NOT NULL);
CREATE TABLE zus_branches (code text PRIMARY KEY, name text NOT NULL, state text NOT NULL, address text);

CREATE TABLE bank_accounts (
  id          serial PRIMARY KEY,
  label       text NOT NULL UNIQUE,              -- 'Maybank Settlement'
  bank        text NOT NULL,
  account_no  text NOT NULL,                     -- store masked in UI; full number restricted
  purpose     text NOT NULL,                     -- settlement | restock | repayment | payout
  active      boolean NOT NULL DEFAULT true
);

CREATE TABLE document_types (
  code       text PRIMARY KEY,
  name       text NOT NULL,
  mandatory  boolean NOT NULL DEFAULT true,
  sort_order int NOT NULL
);

-- ---------------------------------------------------------------------
-- Master merchant record
-- ---------------------------------------------------------------------
CREATE SEQUENCE merchant_seq START 1;

CREATE TABLE merchants (
  merchant_id       text PRIMARY KEY DEFAULT ('ZS-' || lpad(nextval('merchant_seq')::text, 5, '0')),
  name              text NOT NULL,
  owner_name        text NOT NULL,
  ic_or_company_no  text NOT NULL UNIQUE,
  contact_no        text NOT NULL,
  email             citext NOT NULL,
  aim_branch        text REFERENCES aim_branches(code),
  zus_branch        text REFERENCES zus_branches(code),
  address           text,
  state             text NOT NULL,
  district          text,
  status            merchant_status NOT NULL DEFAULT 'applicant',
  assigned_pic_id   uuid REFERENCES users(id),
  activated_at      date,
  activated_by      uuid REFERENCES users(id),
  created_at        timestamptz NOT NULL DEFAULT now(),
  created_by        uuid REFERENCES users(id),
  CHECK (status <> 'active' OR activated_at IS NOT NULL)
);

CREATE TABLE applications (
  application_id    text PRIMARY KEY,                       -- APP-2026-0001
  merchant_id       text NOT NULL UNIQUE REFERENCES merchants(merchant_id),
  application_date  date NOT NULL DEFAULT current_date,
  proposed_location text NOT NULL,
  status            registration_status NOT NULL DEFAULT 'new_application',
  source            data_source NOT NULL DEFAULT 'manual',
  remarks           text,
  created_at        timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------
-- Location screening (each attempt is a row; history is never overwritten)
-- ---------------------------------------------------------------------
CREATE TABLE location_submissions (
  id                bigserial PRIMARY KEY,
  merchant_id       text NOT NULL REFERENCES merchants(merchant_id),
  attempt_no        int NOT NULL,
  proposed_location text NOT NULL,
  status            location_status NOT NULL DEFAULT 'pending_review',
  submitted_by      uuid REFERENCES users(id),
  submitted_at      timestamptz,
  decision_date     date,
  decision_by       uuid REFERENCES users(id),          -- who recorded ZUS's decision
  rejection_reason  text,
  zus_remarks       text,
  UNIQUE (merchant_id, attempt_no),
  CHECK (status <> 'rejected' OR rejection_reason IS NOT NULL)
);

-- ---------------------------------------------------------------------
-- Documents (versioned) + verification history
-- ---------------------------------------------------------------------
CREATE TABLE documents (
  id            bigserial PRIMARY KEY,
  merchant_id   text NOT NULL REFERENCES merchants(merchant_id),
  doc_type      text NOT NULL REFERENCES document_types(code),
  version       int NOT NULL DEFAULT 1,
  file_key      text NOT NULL,             -- object-storage key, never a public URL
  file_name     text NOT NULL,
  mime_type     text NOT NULL CHECK (mime_type IN ('application/pdf','image/jpeg','image/png')),
  size_bytes    bigint NOT NULL CHECK (size_bytes > 0 AND size_bytes <= 10485760),
  sha256        text NOT NULL,
  status        document_status NOT NULL DEFAULT 'under_verification',
  uploaded_by   uuid NOT NULL REFERENCES users(id),
  uploaded_at   timestamptz NOT NULL DEFAULT now(),
  remarks       text,
  UNIQUE (merchant_id, doc_type, version),
  UNIQUE (merchant_id, sha256)             -- blocks duplicate uploads of the same file
);

CREATE TABLE document_verifications (
  id           bigserial PRIMARY KEY,
  document_id  bigint NOT NULL REFERENCES documents(id),
  action       verification_action NOT NULL,
  reason       text,
  verified_by  uuid NOT NULL REFERENCES users(id),
  verified_at  timestamptz NOT NULL DEFAULT now(),
  CHECK (action = 'verify' OR (reason IS NOT NULL AND length(trim(reason)) > 0))
);

CREATE OR REPLACE FUNCTION trg_document_verification() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE up uuid;
BEGIN
  SELECT uploaded_by INTO up FROM documents WHERE id = NEW.document_id;
  IF NOT is_full_access(NEW.verified_by) THEN
    RAISE EXCEPTION 'Only Full Access users can verify or reject documents';
  END IF;
  IF up = NEW.verified_by AND NOT is_full_access(NEW.verified_by) THEN
    RAISE EXCEPTION 'A user cannot verify their own uploaded document';
  END IF;
  UPDATE documents SET status = CASE NEW.action WHEN 'verify' THEN 'verified'::document_status
                                                WHEN 'reject' THEN 'rejected'::document_status
                                                ELSE 'resubmission_required'::document_status END
   WHERE id = NEW.document_id;
  RETURN NEW;
END $$;
CREATE TRIGGER document_verification_rules BEFORE INSERT ON document_verifications
  FOR EACH ROW EXECUTE FUNCTION trg_document_verification();

-- Latest version per merchant/document type
CREATE VIEW v_document_checklist AS
SELECT m.merchant_id, dt.code AS doc_type, dt.name, dt.mandatory,
       d.id AS document_id, d.version, d.file_name, d.uploaded_by, d.uploaded_at,
       COALESCE(d.status, 'missing'::document_status) AS status
FROM merchants m
CROSS JOIN document_types dt
LEFT JOIN LATERAL (
  SELECT * FROM documents x WHERE x.merchant_id = m.merchant_id AND x.doc_type = dt.code
  ORDER BY x.version DESC LIMIT 1) d ON true;

CREATE VIEW v_document_completion AS
SELECT merchant_id,
       count(*) FILTER (WHERE mandatory)                          AS mandatory_total,
       count(*) FILTER (WHERE mandatory AND status = 'verified')  AS mandatory_verified,
       bool_and(status = 'verified') FILTER (WHERE mandatory)     AS complete
FROM v_document_checklist GROUP BY merchant_id;

-- ---------------------------------------------------------------------
-- Onboarding
-- ---------------------------------------------------------------------
CREATE TABLE onboarding_steps (
  merchant_id  text NOT NULL REFERENCES merchants(merchant_id),
  step         onboarding_step NOT NULL,
  status       step_status NOT NULL DEFAULT 'not_started',
  step_date    date,
  reference    text,                 -- payment ref, tracking no, policy no …
  file_key     text,
  details      jsonb NOT NULL DEFAULT '{}'::jsonb,  -- step-specific fields (see docs/03)
  updated_by   uuid REFERENCES users(id),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (merchant_id, step)
);

CREATE OR REPLACE FUNCTION trg_onboarding_requires_documents() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'not_started' AND NOT COALESCE((SELECT complete FROM v_document_completion WHERE merchant_id = NEW.merchant_id), false) THEN
    RAISE EXCEPTION 'Onboarding cannot start: mandatory documents for % are not all verified', NEW.merchant_id;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER onboarding_requires_documents BEFORE INSERT OR UPDATE ON onboarding_steps
  FOR EACH ROW EXECUTE FUNCTION trg_onboarding_requires_documents();

-- Activation guard: location approved + documents complete + all onboarding steps completed
CREATE OR REPLACE FUNCTION trg_merchant_activation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'active' AND OLD.status = 'applicant' THEN
    IF NOT EXISTS (SELECT 1 FROM location_submissions WHERE merchant_id = NEW.merchant_id AND status = 'approved') THEN
      RAISE EXCEPTION 'Cannot activate %: location not approved', NEW.merchant_id;
    END IF;
    IF NOT COALESCE((SELECT complete FROM v_document_completion WHERE merchant_id = NEW.merchant_id), false) THEN
      RAISE EXCEPTION 'Cannot activate %: documents incomplete', NEW.merchant_id;
    END IF;
    IF (SELECT count(*) FROM onboarding_steps WHERE merchant_id = NEW.merchant_id AND status = 'completed') < 7 THEN
      RAISE EXCEPTION 'Cannot activate %: onboarding steps incomplete', NEW.merchant_id;
    END IF;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER merchant_activation_rules BEFORE UPDATE OF status ON merchants
  FOR EACH ROW EXECUTE FUNCTION trg_merchant_activation();

-- ---------------------------------------------------------------------
-- Tasks & PIC
-- ---------------------------------------------------------------------
CREATE SEQUENCE task_seq START 1;
CREATE TABLE tasks (
  task_id              text PRIMARY KEY DEFAULT ('T-' || lpad(nextval('task_seq')::text, 5, '0')),
  merchant_id          text NOT NULL REFERENCES merchants(merchant_id),
  title                text NOT NULL,
  pic_id               uuid NOT NULL REFERENCES users(id),
  assigned_by          uuid REFERENCES users(id),
  assigned_at          timestamptz NOT NULL DEFAULT now(),
  due_date             date NOT NULL,
  status               task_status NOT NULL DEFAULT 'not_started',
  completed_at         timestamptz,
  completed_by         uuid REFERENCES users(id),
  verification_status  text CHECK (verification_status IN ('verified','rejected')),
  verified_by          uuid REFERENCES users(id),
  verified_at          timestamptz,
  remarks              text
);
CREATE INDEX ON tasks (pic_id, status);
CREATE INDEX ON tasks (merchant_id);

CREATE OR REPLACE FUNCTION trg_task_verification() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.verified_by IS NOT NULL AND NEW.verified_by IS DISTINCT FROM OLD.verified_by THEN
    IF NOT is_full_access(NEW.verified_by) THEN
      RAISE EXCEPTION 'Only Full Access users can verify tasks';
    END IF;
    IF NEW.verified_by = NEW.completed_by AND NOT is_full_access(NEW.verified_by) THEN
      RAISE EXCEPTION 'Executor cannot verify their own task';
    END IF;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER task_verification_rules BEFORE UPDATE ON tasks
  FOR EACH ROW EXECUTE FUNCTION trg_task_verification();

-- Overdue is derived, never stored
CREATE VIEW v_tasks AS
SELECT t.*, u.name AS pic_name, u.email AS pic_email,
       CASE WHEN t.status <> 'completed' AND t.due_date < current_date THEN 'overdue' ELSE t.status::text END AS display_status
FROM tasks t JOIN users u ON u.id = t.pic_id;

-- ---------------------------------------------------------------------
-- Period locking (monthly closing)
-- ---------------------------------------------------------------------
CREATE TABLE monthly_closings (
  period       date PRIMARY KEY CHECK (extract(day FROM period) = 1),   -- first day of month
  status       period_status NOT NULL DEFAULT 'open',
  verified_by  uuid REFERENCES users(id),
  locked_at    timestamptz,
  checklist    jsonb,                  -- snapshot of closing checks at lock time
  CHECK (status = 'open' OR (verified_by IS NOT NULL AND locked_at IS NOT NULL))
);

CREATE OR REPLACE FUNCTION period_locked(d date) RETURNS boolean LANGUAGE sql STABLE AS $$
  SELECT EXISTS (SELECT 1 FROM monthly_closings WHERE period = date_trunc('month', d)::date AND status = 'locked')
$$;

-- Attach to every financial table. Inside an approved adjustment the app sets app.adjustment_id.
CREATE OR REPLACE FUNCTION trg_block_locked_period() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE d date; adj text;
BEGIN
  adj := NULLIF(current_setting('app.adjustment_id', true), '');
  IF TG_OP = 'DELETE' THEN d := (to_jsonb(OLD) ->> TG_ARGV[0])::date;
  ELSE d := (to_jsonb(NEW) ->> TG_ARGV[0])::date; END IF;
  IF TG_OP = 'UPDATE' AND period_locked((to_jsonb(OLD) ->> TG_ARGV[0])::date) THEN d := (to_jsonb(OLD) ->> TG_ARGV[0])::date; END IF;
  IF period_locked(d) THEN
    IF adj IS NULL OR NOT EXISTS (SELECT 1 FROM adjustment_requests WHERE id = adj::bigint AND status = 'approved') THEN
      RAISE EXCEPTION '% is in a locked month (%). Submit an adjustment request.', TG_TABLE_NAME, to_char(d, 'Mon YYYY');
    END IF;
  END IF;
  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END $$;

CREATE TABLE adjustment_requests (
  id              bigserial PRIMARY KEY,
  table_name      text NOT NULL,
  record_id       text NOT NULL,
  field_name      text NOT NULL,
  original_value  text,
  correct_value   text NOT NULL,
  reason          text NOT NULL CHECK (length(trim(reason)) > 0),
  requested_by    uuid NOT NULL REFERENCES users(id),
  requested_at    timestamptz NOT NULL DEFAULT now(),
  status          adjustment_status NOT NULL DEFAULT 'pending_approval',
  approved_by     uuid REFERENCES users(id),
  approved_at     timestamptz,
  remarks         text,
  CHECK (approved_by IS NULL OR approved_by <> requested_by)
);

-- ---------------------------------------------------------------------
-- Imports (Excel/CSV) — one row per uploaded file
-- ---------------------------------------------------------------------
CREATE TABLE import_batches (
  id               bigserial PRIMARY KEY,
  kind             import_kind NOT NULL,
  file_name        text NOT NULL,
  file_sha256      text NOT NULL,
  total_rows       int NOT NULL,
  success_rows     int NOT NULL,
  failed_rows      int NOT NULL,
  duplicate_rows   int NOT NULL,
  invalid_rows     int NOT NULL,
  error_report_key text,
  uploaded_by      uuid NOT NULL REFERENCES users(id),
  uploaded_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (kind, file_sha256)            -- same file cannot be imported twice
);

-- ---------------------------------------------------------------------
-- Sales (manual / CSV now, ZUS POS API later)
-- ---------------------------------------------------------------------
CREATE TABLE sales (
  id               bigserial PRIMARY KEY,
  sales_date       date NOT NULL CHECK (sales_date <= current_date),
  merchant_id      text NOT NULL REFERENCES merchants(merchant_id),
  zus_branch       text REFERENCES zus_branches(code),
  sales_amount     numeric(12,2) NOT NULL CHECK (sales_amount > 0),
  item_count       int NOT NULL CHECK (item_count >= 0),
  pos_reference    text NOT NULL UNIQUE,
  source           data_source NOT NULL DEFAULT 'manual',
  external_id      text,                  -- ZUS POS id when the API exists
  import_batch_id  bigint REFERENCES import_batches(id),
  uploaded_by      uuid NOT NULL REFERENCES users(id),
  uploaded_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (merchant_id, sales_date)        -- one daily POS total per merchant (change if POS sends per-shift rows)
);
CREATE TRIGGER sales_period_lock BEFORE INSERT OR UPDATE OR DELETE ON sales
  FOR EACH ROW EXECUTE FUNCTION trg_block_locked_period('sales_date');

-- ---------------------------------------------------------------------
-- Bank transactions + daily settlement
-- ---------------------------------------------------------------------
CREATE TABLE settlement_batches (
  id               bigserial PRIMARY KEY,
  settlement_date  date NOT NULL UNIQUE,
  created_by       uuid REFERENCES users(id),
  created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE settlements (
  id                      bigserial PRIMARY KEY,
  batch_id                bigint NOT NULL REFERENCES settlement_batches(id),
  settlement_date         date NOT NULL,
  merchant_id             text NOT NULL REFERENCES merchants(merchant_id),
  sales_id                bigint REFERENCES sales(id),
  pos_sales               numeric(12,2) NOT NULL DEFAULT 0,
  expected_amount         numeric(12,2) NOT NULL DEFAULT 0,
  destination_account_id  int REFERENCES bank_accounts(id),
  actual_amount           numeric(12,2) CHECK (actual_amount >= 0),
  bank_reference          text,
  difference              numeric(12,2) GENERATED ALWAYS AS (COALESCE(actual_amount, 0) - expected_amount) STORED,
  status                  settlement_status NOT NULL DEFAULT 'missing',
  remarks                 text,
  verified_by             uuid REFERENCES users(id),
  verified_at             timestamptz,
  UNIQUE (merchant_id, settlement_date),
  CHECK (actual_amount IS NULL OR bank_reference IS NOT NULL)
);
CREATE TRIGGER settlements_period_lock BEFORE INSERT OR UPDATE OR DELETE ON settlements
  FOR EACH ROW EXECUTE FUNCTION trg_block_locked_period('settlement_date');

-- Status is derived from amounts unless a user flags 'adjustment_required'
CREATE OR REPLACE FUNCTION trg_settlement_status() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'adjustment_required' THEN RETURN NEW; END IF;
  NEW.status := CASE
    WHEN NEW.actual_amount IS NULL THEN 'missing'
    WHEN NEW.actual_amount = NEW.expected_amount THEN 'matched'
    WHEN NEW.actual_amount > 0 AND NEW.actual_amount < NEW.expected_amount THEN 'partial'
    ELSE 'unmatched' END;
  RETURN NEW;
END $$;
CREATE TRIGGER settlement_status BEFORE INSERT OR UPDATE ON settlements
  FOR EACH ROW EXECUTE FUNCTION trg_settlement_status();

CREATE TABLE bank_transactions (
  id                     bigserial PRIMARY KEY,
  txn_date               date NOT NULL,
  amount                 numeric(12,2) NOT NULL CHECK (amount > 0),
  reference              text NOT NULL,
  bank_account_id        int NOT NULL REFERENCES bank_accounts(id),
  narrative              text,
  source                 data_source NOT NULL DEFAULT 'manual',
  external_id            text,                -- bank API id later
  import_batch_id        bigint REFERENCES import_batches(id),
  matched_settlement_id  bigint REFERENCES settlements(id),
  matched_by             uuid REFERENCES users(id),
  matched_at             timestamptz,
  match_evidence         text,
  status                 bank_txn_status NOT NULL DEFAULT 'unmatched',
  UNIQUE (bank_account_id, reference, txn_date, amount),
  CHECK (status <> 'matched' OR (matched_settlement_id IS NOT NULL AND match_evidence IS NOT NULL))
);
CREATE TRIGGER bank_txn_period_lock BEFORE INSERT OR UPDATE OR DELETE ON bank_transactions
  FOR EACH ROW EXECUTE FUNCTION trg_block_locked_period('txn_date');

-- ---------------------------------------------------------------------
-- Fund allocation (configurable rule, snapshot per allocation)
-- ---------------------------------------------------------------------
CREATE TABLE allocation_rules (
  id              serial PRIMARY KEY,
  effective_from  date NOT NULL UNIQUE,
  created_by      uuid REFERENCES users(id),
  created_at      timestamptz NOT NULL DEFAULT now(),
  remarks         text
);
CREATE TABLE allocation_rule_buckets (
  rule_id          int NOT NULL REFERENCES allocation_rules(id),
  bucket_key       text NOT NULL,          -- 'restock', 'repayment', …
  bucket_name      text NOT NULL,
  percentage       numeric(5,2) NOT NULL CHECK (percentage >= 0 AND percentage <= 100),
  bank_account_id  int NOT NULL REFERENCES bank_accounts(id),
  PRIMARY KEY (rule_id, bucket_key)
);
-- Optional per-merchant override of the global rule
CREATE TABLE merchant_allocation_overrides (
  merchant_id  text NOT NULL REFERENCES merchants(merchant_id),
  rule_id      int NOT NULL REFERENCES allocation_rules(id),
  PRIMARY KEY (merchant_id, rule_id)
);

CREATE TABLE fund_allocations (
  id               bigserial PRIMARY KEY,
  settlement_id    bigint NOT NULL REFERENCES settlements(id),
  merchant_id      text NOT NULL REFERENCES merchants(merchant_id),
  allocation_date  date NOT NULL,
  rule_id          int NOT NULL REFERENCES allocation_rules(id),
  bucket_key       text NOT NULL,
  percentage       numeric(5,2) NOT NULL,      -- snapshot
  base_amount      numeric(12,2) NOT NULL,
  amount           numeric(12,2) NOT NULL CHECK (amount >= 0),
  bank_account_id  int NOT NULL REFERENCES bank_accounts(id),
  status           text NOT NULL DEFAULT 'allocated' CHECK (status IN ('allocated','reversed')),
  created_by       uuid REFERENCES users(id),
  created_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (settlement_id, bucket_key)
);
CREATE TRIGGER fund_alloc_period_lock BEFORE INSERT OR UPDATE OR DELETE ON fund_allocations
  FOR EACH ROW EXECUTE FUNCTION trg_block_locked_period('allocation_date');

CREATE VIEW v_merchant_fund_balance AS
SELECT merchant_id, bucket_key, sum(amount) FILTER (WHERE status = 'allocated') AS total_allocated
FROM fund_allocations GROUP BY merchant_id, bucket_key;

-- ---------------------------------------------------------------------
-- Restock fund, orders, stock
-- ---------------------------------------------------------------------
CREATE TABLE restock_opening_balances (
  merchant_id   text PRIMARY KEY REFERENCES merchants(merchant_id),
  amount        numeric(12,2) NOT NULL DEFAULT 0 CHECK (amount >= 0),
  as_of         date NOT NULL,
  set_by        uuid REFERENCES users(id)
);

CREATE TABLE stock_items (
  id            serial PRIMARY KEY,
  name          text NOT NULL UNIQUE,
  unit          text NOT NULL,
  unit_price    numeric(10,2) NOT NULL CHECK (unit_price >= 0),
  active        boolean NOT NULL DEFAULT true
);

CREATE SEQUENCE order_seq START 1;
CREATE TABLE restock_orders (
  order_id           text PRIMARY KEY DEFAULT ('RO-' || lpad(nextval('order_seq')::text, 5, '0')),
  merchant_id        text NOT NULL REFERENCES merchants(merchant_id),
  order_date         date NOT NULL DEFAULT current_date,
  zus_order_no       text UNIQUE,
  funding_source     text NOT NULL DEFAULT 'restock_fund',
  total_amount       numeric(12,2) NOT NULL DEFAULT 0 CHECK (total_amount >= 0),
  status             restock_status NOT NULL DEFAULT 'restock_requested',
  expected_delivery  date,
  actual_delivery    date,
  source             data_source NOT NULL DEFAULT 'manual',
  created_by         uuid REFERENCES users(id),
  completed_by       uuid REFERENCES users(id),
  remarks            text
);
CREATE TABLE restock_order_items (
  order_id      text NOT NULL REFERENCES restock_orders(order_id),
  item_id       int NOT NULL REFERENCES stock_items(id),
  quantity      int NOT NULL CHECK (quantity > 0),
  unit_price    numeric(10,2) NOT NULL CHECK (unit_price >= 0),
  line_total    numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
  received_qty  int CHECK (received_qty >= 0),
  PRIMARY KEY (order_id, item_id)
);

-- Available = opening + restock allocations − completed orders − open (reserved) orders
CREATE VIEW v_restock_balance AS
SELECT m.merchant_id,
       COALESCE(ob.amount, 0)                                                              AS opening_balance,
       COALESCE((SELECT sum(amount) FROM fund_allocations f WHERE f.merchant_id = m.merchant_id AND f.bucket_key = 'restock' AND f.status = 'allocated'), 0) AS new_funds,
       COALESCE((SELECT sum(total_amount) FROM restock_orders o WHERE o.merchant_id = m.merchant_id AND o.status = 'completed'), 0) AS used,
       COALESCE((SELECT sum(total_amount) FROM restock_orders o WHERE o.merchant_id = m.merchant_id AND o.status NOT IN ('completed','cancelled')), 0) AS reserved
FROM merchants m LEFT JOIN restock_opening_balances ob ON ob.merchant_id = m.merchant_id;

CREATE OR REPLACE FUNCTION restock_available(mid text, exclude_order text DEFAULT NULL) RETURNS numeric LANGUAGE sql STABLE AS $$
  SELECT COALESCE((SELECT amount FROM restock_opening_balances WHERE merchant_id = mid), 0)
       + COALESCE((SELECT sum(amount) FROM fund_allocations WHERE merchant_id = mid AND bucket_key = 'restock' AND status = 'allocated'), 0)
       - COALESCE((SELECT sum(total_amount) FROM restock_orders WHERE merchant_id = mid AND status <> 'cancelled' AND order_id IS DISTINCT FROM exclude_order), 0)
$$;

CREATE OR REPLACE FUNCTION trg_restock_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'cancelled' AND NEW.total_amount > restock_available(NEW.merchant_id, NEW.order_id) THEN
    RAISE EXCEPTION 'Restock order % (RM %) exceeds available restock balance (RM %)',
      NEW.order_id, NEW.total_amount, restock_available(NEW.merchant_id, NEW.order_id);
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER restock_guard BEFORE INSERT OR UPDATE OF total_amount, status ON restock_orders
  FOR EACH ROW EXECUTE FUNCTION trg_restock_guard();

-- Keep order total in sync with its lines (and re-run the guard)
CREATE OR REPLACE FUNCTION trg_order_total() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE oid text := COALESCE(NEW.order_id, OLD.order_id);
BEGIN
  UPDATE restock_orders SET total_amount = COALESCE((SELECT sum(line_total) FROM restock_order_items WHERE order_id = oid), 0)
   WHERE order_id = oid;
  RETURN NULL;
END $$;
CREATE TRIGGER order_total AFTER INSERT OR UPDATE OR DELETE ON restock_order_items
  FOR EACH ROW EXECUTE FUNCTION trg_order_total();

CREATE TABLE stock_levels (
  merchant_id  text NOT NULL REFERENCES merchants(merchant_id),
  item_id      int NOT NULL REFERENCES stock_items(id),
  min_qty      int NOT NULL DEFAULT 0 CHECK (min_qty >= 0),
  PRIMARY KEY (merchant_id, item_id)
);
CREATE TABLE stock_movements (
  id            bigserial PRIMARY KEY,
  merchant_id   text NOT NULL REFERENCES merchants(merchant_id),
  item_id       int NOT NULL REFERENCES stock_items(id),
  movement_date date NOT NULL DEFAULT current_date,
  type          stock_movement_type NOT NULL,
  quantity      int NOT NULL,          -- positive; sign comes from type (count_adjustment may be ±)
  order_id      text REFERENCES restock_orders(order_id),
  recorded_by   uuid REFERENCES users(id),
  recorded_at   timestamptz NOT NULL DEFAULT now(),
  remarks       text,
  CHECK (type = 'count_adjustment' OR quantity > 0),
  CHECK (type <> 'received' OR order_id IS NOT NULL)
);
CREATE VIEW v_stock AS
SELECT l.merchant_id, l.item_id, i.name AS item, l.min_qty,
       COALESCE(sum(m.quantity) FILTER (WHERE m.type = 'opening'), 0)          AS opening_qty,
       COALESCE(sum(m.quantity) FILTER (WHERE m.type = 'received'), 0)         AS received_qty,
       COALESCE(sum(m.quantity) FILTER (WHERE m.type = 'used'), 0)             AS used_qty,
       COALESCE(sum(CASE m.type WHEN 'used' THEN -m.quantity ELSE m.quantity END), 0) AS current_qty,
       CASE WHEN COALESCE(sum(CASE m.type WHEN 'used' THEN -m.quantity ELSE m.quantity END), 0) <= 0 THEN 'out_of_stock'
            WHEN COALESCE(sum(CASE m.type WHEN 'used' THEN -m.quantity ELSE m.quantity END), 0) < l.min_qty THEN 'low_stock'
            ELSE 'normal' END AS stock_status
FROM stock_levels l JOIN stock_items i ON i.id = l.item_id
LEFT JOIN stock_movements m ON m.merchant_id = l.merchant_id AND m.item_id = l.item_id
GROUP BY l.merchant_id, l.item_id, i.name, l.min_qty;

-- ---------------------------------------------------------------------
-- Repayment
-- ---------------------------------------------------------------------
CREATE TABLE repayment_plans (
  merchant_id        text PRIMARY KEY REFERENCES merchants(merchant_id),
  package_amount     numeric(12,2) NOT NULL DEFAULT 16200 CHECK (package_amount > 0),
  monthly_amount     numeric(12,2) NOT NULL DEFAULT 675 CHECK (monthly_amount > 0),
  term_months        int NOT NULL DEFAULT 24 CHECK (term_months > 0),
  first_due_date     date NOT NULL,
  created_by         uuid REFERENCES users(id),
  created_at         timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE repayments (
  id                bigserial PRIMARY KEY,
  merchant_id       text NOT NULL REFERENCES repayment_plans(merchant_id),
  payment_date      date NOT NULL,
  amount            numeric(12,2) NOT NULL CHECK (amount > 0),
  payment_reference text NOT NULL UNIQUE,
  source            data_source NOT NULL DEFAULT 'manual',
  import_batch_id   bigint REFERENCES import_batches(id),
  recorded_by       uuid NOT NULL REFERENCES users(id),
  recorded_at       timestamptz NOT NULL DEFAULT now()
);
CREATE TRIGGER repayment_period_lock BEFORE INSERT OR UPDATE OR DELETE ON repayments
  FOR EACH ROW EXECUTE FUNCTION trg_block_locked_period('payment_date');

CREATE VIEW v_repayment_status AS
WITH s AS (
  SELECT p.*,
         LEAST(p.term_months, GREATEST(0,
           (extract(year FROM age(current_date, p.first_due_date)) * 12 + extract(month FROM age(current_date, p.first_due_date)))::int
           + CASE WHEN current_date >= p.first_due_date THEN 1 ELSE 0 END)) AS instalments_due,
         COALESCE((SELECT sum(amount) FROM repayments r WHERE r.merchant_id = p.merchant_id), 0) AS paid,
         (SELECT max(payment_date) FROM repayments r WHERE r.merchant_id = p.merchant_id) AS last_payment
  FROM repayment_plans p)
SELECT s.*, instalments_due * monthly_amount AS expected_to_date,
       instalments_due * monthly_amount - paid AS arrears,
       package_amount - paid AS outstanding,
       CASE WHEN instalments_due * monthly_amount - paid <= 0 THEN 'paid'
            WHEN instalments_due * monthly_amount - paid >= monthly_amount THEN 'overdue'
            WHEN date_trunc('month', last_payment) = date_trunc('month', current_date) THEN 'partial'
            ELSE 'outstanding' END AS status
FROM s;

-- ---------------------------------------------------------------------
-- Issues
-- ---------------------------------------------------------------------
CREATE SEQUENCE issue_seq START 1;
CREATE TABLE issues (
  issue_id      text PRIMARY KEY DEFAULT ('ISS-' || lpad(nextval('issue_seq')::text, 5, '0')),
  merchant_id   text NOT NULL REFERENCES merchants(merchant_id),
  category      text NOT NULL,
  description   text NOT NULL,
  pic_id        uuid REFERENCES users(id),
  raised_by     uuid NOT NULL REFERENCES users(id),
  raised_at     timestamptz NOT NULL DEFAULT now(),
  due_date      date,
  priority      issue_priority NOT NULL DEFAULT 'medium',
  status        issue_status NOT NULL DEFAULT 'open',
  action_taken  text,
  resolution    text,
  resolved_at   timestamptz,
  verified_by   uuid REFERENCES users(id),
  closed_at     timestamptz,
  CHECK (status NOT IN ('resolved','verified','closed') OR resolution IS NOT NULL)
);
CREATE OR REPLACE FUNCTION trg_issue_verify() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status IN ('verified','closed') AND OLD.status NOT IN ('verified','closed') AND NOT is_full_access(current_app_user()) THEN
    RAISE EXCEPTION 'Only Full Access users can verify or close issues';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER issue_verify_rules BEFORE UPDATE OF status ON issues
  FOR EACH ROW EXECUTE FUNCTION trg_issue_verify();

-- ---------------------------------------------------------------------
-- Daily Finance report
-- ---------------------------------------------------------------------
CREATE TABLE finance_reports (
  report_id     text PRIMARY KEY,                 -- FR-20261112
  report_date   date NOT NULL UNIQUE,
  covers_date   date NOT NULL,                    -- settlement batch it reports on
  status        report_status NOT NULL DEFAULT 'draft',
  prepared_by   uuid NOT NULL REFERENCES users(id),
  prepared_at   timestamptz NOT NULL DEFAULT now(),
  verified_by   uuid REFERENCES users(id),
  verified_at   timestamptz,
  sent_at       timestamptz,
  snapshot      jsonb,                            -- frozen totals & breakdowns at verification
  CHECK (status = 'draft' OR verified_by IS NOT NULL),
  CHECK (status <> 'sent_to_finance' OR sent_at IS NOT NULL)
);
CREATE OR REPLACE FUNCTION trg_report_verify() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.verified_by IS NOT NULL AND NEW.verified_by IS DISTINCT FROM OLD.verified_by THEN
    IF NOT is_full_access(NEW.verified_by) THEN RAISE EXCEPTION 'Only Full Access users verify Finance reports'; END IF;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER report_verify_rules BEFORE UPDATE ON finance_reports
  FOR EACH ROW EXECUTE FUNCTION trg_report_verify();

-- Business days with no report sent (drives the red "Not Prepared" alert)
CREATE TABLE public_holidays (holiday_date date PRIMARY KEY, name text NOT NULL);
CREATE VIEW v_missing_finance_reports AS
SELECT d::date AS business_day
FROM generate_series(date_trunc('month', current_date), current_date, interval '1 day') d
WHERE extract(isodow FROM d) < 6
  AND d::date NOT IN (SELECT holiday_date FROM public_holidays)
  AND NOT EXISTS (SELECT 1 FROM finance_reports f WHERE f.report_date = d::date AND f.status = 'sent_to_finance');

-- Month cannot be locked while settlement or bank matching is unresolved
CREATE OR REPLACE FUNCTION trg_month_lock() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE p_end date := (NEW.period + interval '1 month - 1 day')::date;
BEGIN
  IF NEW.status = 'locked' AND (TG_OP = 'INSERT' OR OLD.status = 'open') THEN
    IF NOT is_full_access(NEW.verified_by) THEN RAISE EXCEPTION 'Only Full Access users can lock a month'; END IF;
    IF EXISTS (SELECT 1 FROM settlements WHERE settlement_date BETWEEN NEW.period AND p_end AND status <> 'matched') THEN
      RAISE EXCEPTION 'Cannot lock %: settlements not fully matched', to_char(NEW.period, 'Mon YYYY');
    END IF;
    IF EXISTS (SELECT 1 FROM bank_transactions WHERE txn_date BETWEEN NEW.period AND p_end AND status <> 'matched') THEN
      RAISE EXCEPTION 'Cannot lock %: bank transactions without an owner', to_char(NEW.period, 'Mon YYYY');
    END IF;
    IF EXISTS (SELECT 1 FROM adjustment_requests WHERE status = 'pending_approval') THEN
      RAISE EXCEPTION 'Cannot lock %: adjustment requests pending', to_char(NEW.period, 'Mon YYYY');
    END IF;
  END IF;
  IF TG_OP = 'UPDATE' AND OLD.status = 'locked' AND NEW.status = 'open' THEN
    RAISE EXCEPTION 'A locked month cannot be reopened; use adjustment requests';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER month_lock_rules BEFORE INSERT OR UPDATE ON monthly_closings
  FOR EACH ROW EXECUTE FUNCTION trg_month_lock();

-- ---------------------------------------------------------------------
-- Notifications (outbox; a worker sends the emails)
-- ---------------------------------------------------------------------
CREATE TABLE notifications (
  id           bigserial PRIMARY KEY,
  event_type   text NOT NULL,          -- task_assigned, document_rejected, settlement_mismatch …
  to_user_id   uuid NOT NULL REFERENCES users(id),
  merchant_id  text REFERENCES merchants(merchant_id),
  subject      text NOT NULL,
  body         text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  sent_at      timestamptz,
  attempts     int NOT NULL DEFAULT 0,
  last_error   text
);
CREATE INDEX ON notifications (sent_at) WHERE sent_at IS NULL;

-- ---------------------------------------------------------------------
-- Integrations (future ZUS POS / ZUS Order / Bank APIs)
-- ---------------------------------------------------------------------
CREATE TABLE integration_runs (
  id            bigserial PRIMARY KEY,
  provider      text NOT NULL CHECK (provider IN ('zus_pos','zus_order','bank')),
  started_at    timestamptz NOT NULL DEFAULT now(),
  finished_at   timestamptz,
  status        text NOT NULL DEFAULT 'running' CHECK (status IN ('running','success','failed')),
  records_in    int NOT NULL DEFAULT 0,
  error         text
);

-- ---------------------------------------------------------------------
-- Audit log — permanent
-- ---------------------------------------------------------------------
CREATE TABLE audit_logs (
  id           bigserial PRIMARY KEY,
  at           timestamptz NOT NULL DEFAULT now(),
  user_id      uuid REFERENCES users(id),
  user_name    text,
  user_email   text,
  module       text NOT NULL,
  action       text NOT NULL,
  merchant_id  text,
  entity       text,
  entity_id    text,
  previous     jsonb,
  new          jsonb,
  remarks      text,
  ip_address   inet
);
CREATE INDEX ON audit_logs (merchant_id, at DESC);
CREATE INDEX ON audit_logs (user_id, at DESC);

CREATE OR REPLACE FUNCTION trg_audit_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'Audit log entries are permanent and cannot be % ', lower(TG_OP);
END $$;
CREATE TRIGGER audit_no_update BEFORE UPDATE OR DELETE ON audit_logs
  FOR EACH ROW EXECUTE FUNCTION trg_audit_immutable();
CREATE TRIGGER audit_no_truncate BEFORE TRUNCATE ON audit_logs
  FOR EACH STATEMENT EXECUTE FUNCTION trg_audit_immutable();

-- Generic row-change audit (the app also writes business-level entries, e.g. "Rejected SSM – document expired")
CREATE OR REPLACE FUNCTION trg_row_audit() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE uid uuid := current_app_user(); u users%ROWTYPE; oldj jsonb; newj jsonb; mid text; eid text;
BEGIN
  IF uid IS NOT NULL THEN SELECT * INTO u FROM users WHERE id = uid; END IF;
  IF TG_OP <> 'INSERT' THEN oldj := to_jsonb(OLD); END IF;
  IF TG_OP <> 'DELETE' THEN newj := to_jsonb(NEW); END IF;
  mid := COALESCE(newj ->> 'merchant_id', oldj ->> 'merchant_id');
  eid := COALESCE(newj ->> TG_ARGV[0], oldj ->> TG_ARGV[0]);
  IF TG_OP = 'UPDATE' AND oldj = newj THEN RETURN NULL; END IF;
  INSERT INTO audit_logs (user_id, user_name, user_email, module, action, merchant_id, entity, entity_id, previous, new, remarks)
  VALUES (uid, u.name, u.email, TG_ARGV[1], lower(TG_OP), mid, TG_TABLE_NAME, eid, oldj, newj,
          NULLIF(current_setting('app.adjustment_id', true), ''));
  RETURN NULL;
END $$;

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT * FROM (VALUES
    ('users','id','Users & Roles'), ('merchants','merchant_id','Merchants'), ('applications','application_id','Registration'),
    ('location_submissions','id','Location Screening'), ('documents','id','Documents'), ('document_verifications','id','Documents'),
    ('onboarding_steps','merchant_id','Onboarding'), ('tasks','task_id','Tasks'), ('sales','id','Sales'),
    ('settlements','id','Settlement'), ('bank_transactions','id','Settlement'), ('allocation_rules','id','Fund Allocation'),
    ('allocation_rule_buckets','rule_id','Fund Allocation'), ('fund_allocations','id','Fund Allocation'),
    ('restock_orders','order_id','Restock'), ('restock_order_items','order_id','Restock'), ('stock_movements','id','Stock'),
    ('stock_levels','merchant_id','Stock'), ('repayment_plans','merchant_id','Repayment'), ('repayments','id','Repayment'),
    ('issues','issue_id','Issues'), ('finance_reports','report_id','Reports'), ('monthly_closings','period','Reports'),
    ('adjustment_requests','id','Reports'), ('import_batches','id','Imports')
  ) AS t(tbl, key, module) LOOP
    EXECUTE format('CREATE TRIGGER %I AFTER INSERT OR UPDATE OR DELETE ON %I FOR EACH ROW EXECUTE FUNCTION trg_row_audit(%L, %L)',
                   r.tbl || '_audit', r.tbl, r.key, r.module);
  END LOOP;
END $$;

-- ---------------------------------------------------------------------
-- Merchant master view (one screen = one query)
-- ---------------------------------------------------------------------
CREATE VIEW v_merchant_master AS
SELECT m.merchant_id, m.name, m.owner_name, m.contact_no, m.email, m.aim_branch, m.zus_branch, m.address, m.state, m.district,
       a.application_id, a.application_date, a.status AS registration_status,
       ls.status AS location_status, ls.attempt_no AS location_attempts,
       dc.mandatory_verified, dc.mandatory_total, COALESCE(dc.complete, false) AS documents_complete,
       (SELECT count(*) FROM onboarding_steps o WHERE o.merchant_id = m.merchant_id AND o.status = 'completed') AS onboarding_completed_steps,
       m.activated_at, m.status AS merchant_status,
       p.name AS pic_name, p.email AS pic_email,
       (SELECT count(*) FROM issues i WHERE i.merchant_id = m.merchant_id AND i.status <> 'closed') AS open_issues
FROM merchants m
LEFT JOIN applications a ON a.merchant_id = m.merchant_id
LEFT JOIN LATERAL (SELECT * FROM location_submissions x WHERE x.merchant_id = m.merchant_id ORDER BY attempt_no DESC LIMIT 1) ls ON true
LEFT JOIN v_document_completion dc ON dc.merchant_id = m.merchant_id
LEFT JOIN users p ON p.id = m.assigned_pic_id;

COMMIT;