-- 0001_foundation.sql
-- Pharmacy POS, Wave 0: the data foundation.
--
-- Design rules enforced here (see docs/DECISIONS.md):
--   * Stock is an append-only ledger. On-hand quantity is derived, never edited.
--   * Money is stored as whole integers (cents, 1/100 KES). No floats, no NUMERIC.
--   * Every sellable unit belongs to a batch. FEFO is a query, not a convention.
--   * The audit log is append-only and hash-chained, so tampering is detectable.
--   * Sensitive stock changes need an approval by a different person.
--   * Every branch-scoped row carries branch_id.
--
-- The application sets these per transaction so the audit trigger can attribute changes:
--   SELECT set_config('app.user_id',   '<id>', true);
--   SELECT set_config('app.branch_id', '<id>', true);
--   SELECT set_config('app.ip',        '<ip>', true);
--   SELECT set_config('app.device',    '<text>', true);
--   SELECT set_config('app.reason',    '<text>', true);

-- No extensions are needed: hashing uses PostgreSQL's built-in sha256() (version 11 and newer),
-- because shared hosts often do not install contrib extensions such as pgcrypto.

-- ---------------------------------------------------------------- types ----

CREATE TYPE rx_status       AS ENUM ('otc', 'pharmacy_only', 'prescription_only', 'controlled');
CREATE TYPE batch_status    AS ENUM ('active', 'quarantined', 'recalled', 'expired', 'closed');
CREATE TYPE movement_type   AS ENUM (
  'opening', 'receipt', 'sale', 'sale_return', 'supplier_return',
  'adjustment', 'expiry_writeoff', 'damage_writeoff', 'disposal',
  'transfer_out', 'transfer_in'
);
CREATE TYPE approval_kind   AS ENUM (
  'price_override', 'large_discount', 'refund', 'stock_adjustment',
  'purchase_order', 'purchase_cancellation', 'controlled_adjustment',
  'credit_sale', 'transaction_cancellation', 'backdated_transaction'
);
CREATE TYPE approval_status AS ENUM ('pending', 'approved', 'rejected');

-- ------------------------------------------------------------- helpers -----

-- Reads a per-transaction application setting such as app.user_id; NULL when unset.
CREATE FUNCTION app_ctx(p_key text) RETURNS text
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.' || p_key, true), '')
$$;

CREATE FUNCTION forbid_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION '% is append-only: % is not allowed', TG_TABLE_NAME, TG_OP
    USING ERRCODE = 'PS010';
END $$;

-- ------------------------------------------------------ branches & users ---

CREATE TABLE branches (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code        text        NOT NULL UNIQUE,
  name        text        NOT NULL,
  active      boolean     NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username        text        NOT NULL,
  display_name    text        NOT NULL,
  password_hash   text        NOT NULL,            -- argon2 hash produced by the app
  totp_secret     text,                            -- encrypted by the app
  totp_required   boolean     NOT NULL DEFAULT false,
  active          boolean     NOT NULL DEFAULT true,
  failed_logins   integer     NOT NULL DEFAULT 0,
  locked_until    timestamptz,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_username_lower ON users (lower(username));

CREATE TABLE roles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code  text NOT NULL UNIQUE,
  name  text NOT NULL
);

CREATE TABLE permissions (
  code         text PRIMARY KEY,
  description  text NOT NULL
);

CREATE TABLE role_permissions (
  role_id         bigint NOT NULL REFERENCES roles (id) ON DELETE CASCADE,
  permission_code text   NOT NULL REFERENCES permissions (code) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_code)
);

-- branch_id NULL means the role applies in every branch.
CREATE TABLE user_roles (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   bigint NOT NULL REFERENCES users (id),
  role_id   bigint NOT NULL REFERENCES roles (id),
  branch_id bigint REFERENCES branches (id)
);
CREATE UNIQUE INDEX user_roles_unique ON user_roles (user_id, role_id, coalesce(branch_id, 0));

CREATE FUNCTION user_has_permission(p_user bigint, p_branch bigint, p_permission text)
RETURNS boolean LANGUAGE sql STABLE AS $$
  SELECT EXISTS (
    SELECT 1
    FROM users u
    JOIN user_roles ur       ON ur.user_id = u.id
    JOIN role_permissions rp ON rp.role_id = ur.role_id
    WHERE u.id = p_user
      AND u.active
      AND rp.permission_code = p_permission
      AND (ur.branch_id IS NULL OR ur.branch_id = p_branch)
  )
$$;

CREATE TABLE settings (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_id bigint REFERENCES branches (id),       -- NULL = business-wide
  key       text  NOT NULL,
  value     jsonb NOT NULL
);
CREATE UNIQUE INDEX settings_unique ON settings (coalesce(branch_id, 0), key);

-- ------------------------------------------------- suppliers & catalogue ---

CREATE TABLE suppliers (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name          text        NOT NULL,
  kra_pin       text,
  phone         text,
  email         text,
  payment_terms text,
  active        boolean     NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id                      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku                     text        NOT NULL UNIQUE,
  name                    text        NOT NULL,
  generic_name            text,
  brand_name              text,
  active_ingredient       text,
  strength                text,
  dosage_form             text,
  pack_size               integer     CHECK (pack_size IS NULL OR pack_size > 0),
  manufacturer            text,
  default_supplier_id     bigint      REFERENCES suppliers (id),
  category                text,
  therapeutic_category    text,
  rx_status               rx_status   NOT NULL DEFAULT 'otc',
  controlled_class        text,                    -- e.g. narcotic, psychotropic, precursor
  ppb_info                text,
  purchase_price_cents    bigint      NOT NULL DEFAULT 0 CHECK (purchase_price_cents >= 0),
  selling_price_cents     bigint      NOT NULL DEFAULT 0 CHECK (selling_price_cents >= 0),
  wholesale_price_cents   bigint      CHECK (wholesale_price_cents >= 0),
  min_selling_price_cents bigint      CHECK (min_selling_price_cents >= 0),
  vat_category            text        NOT NULL DEFAULT 'standard'
                                      CHECK (vat_category IN ('standard', 'zero_rated', 'exempt')),
  reorder_level           integer     CHECK (reorder_level >= 0),
  max_stock_level         integer     CHECK (max_stock_level >= 0),
  shelf_location          text,
  tracks_expiry           boolean     NOT NULL DEFAULT true,
  active                  boolean     NOT NULL DEFAULT true,
  created_at              timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT controlled_needs_class
    CHECK (rx_status <> 'controlled' OR controlled_class IS NOT NULL),
  CONSTRAINT min_price_not_above_selling
    CHECK (min_selling_price_cents IS NULL OR min_selling_price_cents <= selling_price_cents)
);

CREATE TABLE product_barcodes (
  barcode    text   PRIMARY KEY,
  product_id bigint NOT NULL REFERENCES products (id),
  pack_level text   NOT NULL DEFAULT 'unit'
);
CREATE INDEX product_barcodes_product ON product_barcodes (product_id);

-- Every stocked unit belongs to a batch. Products without expiry use a single
-- batch with expiry_date NULL (convention: batch_no 'NOBATCH').
CREATE TABLE batches (
  id                   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id           bigint       NOT NULL REFERENCES products (id),
  batch_no             text         NOT NULL,
  manufacture_date     date,
  expiry_date          date,                        -- last day on which the batch may be sold
  supplier_id          bigint       REFERENCES suppliers (id),
  grn_ref              text,
  purchase_price_cents bigint       CHECK (purchase_price_cents >= 0),
  selling_price_cents  bigint       CHECK (selling_price_cents >= 0),
  status               batch_status NOT NULL DEFAULT 'active',
  status_reason        text,
  created_at           timestamptz  NOT NULL DEFAULT now(),
  UNIQUE (product_id, batch_no),
  UNIQUE (id, product_id)
);
CREATE INDEX batches_product_expiry ON batches (product_id, expiry_date);

-- ---------------------------------------------------------- approvals ------

CREATE TABLE approvals (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_id     bigint          NOT NULL REFERENCES branches (id),
  kind          approval_kind   NOT NULL,
  status        approval_status NOT NULL DEFAULT 'pending',
  requested_by  bigint          NOT NULL REFERENCES users (id),
  decided_by    bigint          REFERENCES users (id),
  reason        text            NOT NULL CHECK (btrim(reason) <> ''),
  payload       jsonb           NOT NULL DEFAULT '{}'::jsonb,
  requested_at  timestamptz     NOT NULL DEFAULT now(),
  decided_at    timestamptz,
  CONSTRAINT approvals_decision_complete
    CHECK (status = 'pending' OR (decided_by IS NOT NULL AND decided_at IS NOT NULL)),
  CONSTRAINT approvals_separation_of_duties
    CHECK (decided_by IS NULL OR decided_by <> requested_by)
);

CREATE FUNCTION approvals_freeze_decided() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.status <> 'pending' THEN
    RAISE EXCEPTION 'decided approvals are immutable (approval %)', OLD.id
      USING ERRCODE = 'PS011';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER approvals_freeze_decided
  BEFORE UPDATE ON approvals
  FOR EACH ROW EXECUTE FUNCTION approvals_freeze_decided();

CREATE FUNCTION approvals_no_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'approvals cannot be deleted' USING ERRCODE = 'PS011';
END $$;

CREATE TRIGGER approvals_no_delete
  BEFORE DELETE ON approvals
  FOR EACH ROW EXECUTE FUNCTION approvals_no_delete();

-- ------------------------------------------------------- stock ledger ------

CREATE TABLE stock_movements (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_id       bigint        NOT NULL REFERENCES branches (id),
  product_id      bigint        NOT NULL,
  batch_id        bigint        NOT NULL,
  movement_type   movement_type NOT NULL,
  quantity        integer       NOT NULL,           -- signed: positive in, negative out
  reason          text,
  reference_type  text,                             -- 'grn', 'sale', 'transfer', ...
  reference_id    text,
  user_id         bigint        REFERENCES users (id),
  approval_id     bigint        REFERENCES approvals (id),
  idempotency_key text,
  occurred_at     timestamptz   NOT NULL DEFAULT now(),
  created_at      timestamptz   NOT NULL DEFAULT now(),
  FOREIGN KEY (batch_id, product_id) REFERENCES batches (id, product_id),
  CONSTRAINT movement_quantity_nonzero CHECK (quantity <> 0),
  CONSTRAINT movement_sign CHECK (
    (movement_type IN ('opening', 'receipt', 'sale_return', 'transfer_in') AND quantity > 0)
    OR (movement_type IN ('sale', 'supplier_return', 'expiry_writeoff', 'damage_writeoff',
                          'disposal', 'transfer_out') AND quantity < 0)
    OR movement_type = 'adjustment'
  ),
  CONSTRAINT adjustment_needs_reason CHECK (
    movement_type NOT IN ('adjustment', 'expiry_writeoff', 'damage_writeoff', 'disposal')
    OR btrim(coalesce(reason, '')) <> ''
  ),
  CONSTRAINT adjustment_needs_approval CHECK (
    movement_type <> 'adjustment' OR approval_id IS NOT NULL
  )
);

-- A retry of the same request must never create a second movement.
CREATE UNIQUE INDEX stock_movements_idem
  ON stock_movements (branch_id, idempotency_key) WHERE idempotency_key IS NOT NULL;
-- One approval covers exactly one adjustment movement.
CREATE UNIQUE INDEX stock_movements_one_adjustment_per_approval
  ON stock_movements (approval_id) WHERE movement_type = 'adjustment';
CREATE INDEX stock_movements_branch_batch   ON stock_movements (branch_id, batch_id);
CREATE INDEX stock_movements_branch_product ON stock_movements (branch_id, product_id);
CREATE INDEX stock_movements_reference      ON stock_movements (reference_type, reference_id);

CREATE FUNCTION stock_movements_before_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_balance bigint;
  v_batch   batches%ROWTYPE;
  v_rx      rx_status;
  v_appr    approvals%ROWTYPE;
BEGIN
  -- Serialise all movements for one branch+batch so two tills cannot both
  -- sell the last unit.
  PERFORM pg_advisory_xact_lock(
    hashtextextended('stock:' || NEW.branch_id || ':' || NEW.batch_id, 0));

  SELECT coalesce(sum(quantity), 0) INTO v_balance
  FROM stock_movements
  WHERE branch_id = NEW.branch_id AND batch_id = NEW.batch_id;

  IF v_balance + NEW.quantity < 0 THEN
    RAISE EXCEPTION 'insufficient_stock: batch % has % but movement is %',
      NEW.batch_id, v_balance, NEW.quantity USING ERRCODE = 'PS001';
  END IF;

  SELECT * INTO v_batch FROM batches WHERE id = NEW.batch_id;

  -- Selling is only allowed from an active, unexpired batch.
  IF NEW.movement_type = 'sale' THEN
    IF v_batch.status <> 'active'
       OR (v_batch.expiry_date IS NOT NULL AND v_batch.expiry_date < current_date) THEN
      RAISE EXCEPTION 'batch_not_sellable: batch % is % (expiry %)',
        v_batch.batch_no, v_batch.status, v_batch.expiry_date USING ERRCODE = 'PS003';
    END IF;
  END IF;

  -- Adjustments need an approved request from a different person.
  IF NEW.movement_type = 'adjustment' AND NEW.approval_id IS NOT NULL THEN
    SELECT * INTO v_appr FROM approvals WHERE id = NEW.approval_id;
    SELECT rx_status INTO v_rx FROM products WHERE id = NEW.product_id;

    IF v_appr.status IS DISTINCT FROM 'approved' THEN
      RAISE EXCEPTION 'approval_not_granted: approval % is %', NEW.approval_id, v_appr.status
        USING ERRCODE = 'PS004';
    END IF;
    IF v_appr.branch_id <> NEW.branch_id THEN
      RAISE EXCEPTION 'approval_wrong_branch: approval % belongs to another branch', NEW.approval_id
        USING ERRCODE = 'PS004';
    END IF;
    IF v_rx = 'controlled' AND v_appr.kind <> 'controlled_adjustment' THEN
      RAISE EXCEPTION 'controlled_adjustment approval required for controlled medicines'
        USING ERRCODE = 'PS004';
    END IF;
    IF v_rx <> 'controlled' AND v_appr.kind NOT IN ('stock_adjustment', 'controlled_adjustment') THEN
      RAISE EXCEPTION 'stock_adjustment approval required, got %', v_appr.kind
        USING ERRCODE = 'PS004';
    END IF;
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER stock_movements_before_insert
  BEFORE INSERT ON stock_movements
  FOR EACH ROW EXECUTE FUNCTION stock_movements_before_insert();

CREATE TRIGGER stock_movements_immutable
  BEFORE UPDATE OR DELETE ON stock_movements
  FOR EACH ROW EXECUTE FUNCTION forbid_change();
CREATE TRIGGER stock_movements_no_truncate
  BEFORE TRUNCATE ON stock_movements
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_change();

-- Current balance per branch and batch (zero balances are omitted).
CREATE VIEW stock_on_hand AS
SELECT branch_id, product_id, batch_id, sum(quantity)::integer AS quantity
FROM stock_movements
GROUP BY branch_id, product_id, batch_id
HAVING sum(quantity) <> 0;

-- FEFO: allocate a quantity across the earliest-expiring sellable batches.
-- Advisory only: the stock_movements insert re-checks the balance under a lock.
CREATE FUNCTION fefo_pick(p_branch bigint, p_product bigint, p_qty integer)
RETURNS TABLE (batch_id bigint, batch_no text, expiry_date date, qty integer)
LANGUAGE plpgsql STABLE AS $$
#variable_conflict use_column
DECLARE
  v_remaining integer := p_qty;
  v_take      integer;
  r           record;
BEGIN
  IF p_qty IS NULL OR p_qty <= 0 THEN
    RAISE EXCEPTION 'quantity must be positive' USING ERRCODE = 'PS002';
  END IF;

  FOR r IN
    SELECT b.id AS bid, b.batch_no AS bno, b.expiry_date AS bexp, s.q AS q
    FROM batches b
    JOIN (
      SELECT sm.batch_id AS sbid, sum(sm.quantity)::integer AS q
      FROM stock_movements sm
      WHERE sm.branch_id = p_branch AND sm.product_id = p_product
      GROUP BY sm.batch_id
    ) s ON s.sbid = b.id
    WHERE b.product_id = p_product
      AND b.status = 'active'
      AND (b.expiry_date IS NULL OR b.expiry_date >= current_date)
      AND s.q > 0
    ORDER BY b.expiry_date NULLS LAST, b.id
  LOOP
    v_take := least(r.q, v_remaining);
    batch_id := r.bid; batch_no := r.bno; expiry_date := r.bexp; qty := v_take;
    RETURN NEXT;
    v_remaining := v_remaining - v_take;
    EXIT WHEN v_remaining = 0;
  END LOOP;

  IF v_remaining > 0 THEN
    RAISE EXCEPTION 'insufficient_sellable_stock: product % is short by %', p_product, v_remaining
      USING ERRCODE = 'PS002';
  END IF;
END $$;

-- Controlled-drug register: a view over the same ledger, with a running balance.
-- Patient and prescriber details join in through reference_id in Wave 2.
CREATE VIEW controlled_drug_register AS
SELECT sm.id,
       sm.branch_id,
       sm.product_id,
       p.name  AS product_name,
       sm.batch_id,
       b.batch_no,
       sm.movement_type,
       sm.quantity,
       sm.reason,
       sm.reference_type,
       sm.reference_id,
       sm.user_id,
       sm.approval_id,
       sm.occurred_at,
       sum(sm.quantity) OVER (PARTITION BY sm.branch_id, sm.product_id ORDER BY sm.id)
         AS running_balance
FROM stock_movements sm
JOIN products p ON p.id = sm.product_id
JOIN batches  b ON b.id = sm.batch_id
WHERE p.rx_status = 'controlled';

-- ---------------------------------------------------------- audit log ------

CREATE SEQUENCE audit_log_id_seq;

CREATE TABLE audit_log (
  id              bigint      PRIMARY KEY,
  occurred_at     timestamptz NOT NULL,
  user_id         bigint,                      -- no foreign keys: the log must outlive its subjects
  branch_id       bigint,
  ip              inet,
  device          text,
  action          text        NOT NULL,
  entity_type     text,
  entity_id       text,
  old_value       jsonb,
  new_value       jsonb,
  reason          text,
  approval_id     bigint,
  transaction_ref text,
  prev_hash       text,
  row_hash        text        NOT NULL
);
CREATE INDEX audit_log_entity ON audit_log (entity_type, entity_id);
CREATE INDEX audit_log_user   ON audit_log (user_id, occurred_at);

CREATE FUNCTION audit_row_hash(r audit_log) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT encode(sha256(convert_to(
    coalesce(r.prev_hash, '') || '|' || r.id || '|' ||
    to_char(r.occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US') || '|' ||
    coalesce(r.user_id::text, '') || '|' || coalesce(r.branch_id::text, '') || '|' ||
    coalesce(r.ip::text, '') || '|' || coalesce(r.device, '') || '|' ||
    r.action || '|' || coalesce(r.entity_type, '') || '|' || coalesce(r.entity_id, '') || '|' ||
    coalesce(r.old_value::text, '') || '|' || coalesce(r.new_value::text, '') || '|' ||
    coalesce(r.reason, '') || '|' || coalesce(r.approval_id::text, '') || '|' ||
    coalesce(r.transaction_ref, ''),
    'UTF8')), 'hex')
$$;

CREATE FUNCTION audit_log_before_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_prev text;
BEGIN
  -- One writer at a time keeps the chain strictly ordered.
  PERFORM pg_advisory_xact_lock(hashtextextended('audit_chain', 0));
  NEW.id          := nextval('audit_log_id_seq');
  NEW.occurred_at := clock_timestamp();
  SELECT row_hash INTO v_prev FROM audit_log ORDER BY id DESC LIMIT 1;
  NEW.prev_hash   := v_prev;
  NEW.row_hash    := audit_row_hash(NEW);
  RETURN NEW;
END $$;

CREATE TRIGGER audit_log_before_insert
  BEFORE INSERT ON audit_log
  FOR EACH ROW EXECUTE FUNCTION audit_log_before_insert();

CREATE TRIGGER audit_log_immutable
  BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION forbid_change();
CREATE TRIGGER audit_log_no_truncate
  BEFORE TRUNCATE ON audit_log
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_change();

-- Walks the chain in order. Returns ok = false and the first bad id if any row
-- was altered, removed or reordered.
CREATE FUNCTION verify_audit_chain()
RETURNS TABLE (ok boolean, first_bad_id bigint, rows_checked bigint)
LANGUAGE plpgsql STABLE AS $$
DECLARE
  r        audit_log;
  v_prev   text := NULL;
  v_count  bigint := 0;
BEGIN
  FOR r IN SELECT * FROM audit_log ORDER BY id LOOP
    v_count := v_count + 1;
    IF r.prev_hash IS DISTINCT FROM v_prev OR r.row_hash <> audit_row_hash(r) THEN
      ok := false; first_bad_id := r.id; rows_checked := v_count;
      RETURN NEXT; RETURN;
    END IF;
    v_prev := r.row_hash;
  END LOOP;
  ok := true; first_bad_id := NULL; rows_checked := v_count;
  RETURN NEXT;
END $$;

-- Generic row-change audit. Attach with: EXECUTE FUNCTION audit_row_change('<entity>').
-- Secrets are never written to the log; a changed secret is recorded as a flag only.
CREATE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  o     jsonb := CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END;
  n     jsonb := CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END;
  v_old jsonb;
  v_new jsonb;
BEGIN
  IF TG_OP = 'UPDATE' AND o = n THEN
    RETURN NULL;
  END IF;

  v_old := o - ARRAY['password_hash', 'totp_secret'];
  v_new := n - ARRAY['password_hash', 'totp_secret'];

  IF TG_OP = 'UPDATE' THEN
    IF (o ->> 'password_hash') IS DISTINCT FROM (n ->> 'password_hash') THEN
      v_new := v_new || '{"password_changed": true}'::jsonb;
    END IF;
    IF (o ->> 'totp_secret') IS DISTINCT FROM (n ->> 'totp_secret') THEN
      v_new := v_new || '{"totp_changed": true}'::jsonb;
    END IF;
  END IF;

  INSERT INTO audit_log (user_id, branch_id, ip, device, action, entity_type, entity_id,
                         old_value, new_value, reason, transaction_ref, row_hash)
  VALUES (app_ctx('user_id')::bigint, app_ctx('branch_id')::bigint, app_ctx('ip')::inet,
          app_ctx('device'), lower(TG_OP), TG_ARGV[0],
          coalesce(n ->> 'id', o ->> 'id'), v_old, v_new,
          app_ctx('reason'), app_ctx('transaction_ref'), '');
  RETURN NULL;
END $$;