-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Phase 0 — Immutability Guards v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql, on BOTH cloud and store nodes.
--
--  WHY THIS EXISTS
--  Principle P4 (events are append-only) and P5 (no backdated edits)
--  are the reason this system has no merge logic. They are also the
--  kind of rule that erodes quietly: some evening in year two, someone
--  under pressure writes a one-line UPDATE to "just fix" a bill, and
--  the guarantee is gone with no error and no trace.
--
--  Code review cannot catch that reliably. The database can.
--
--  These are the ONLY triggers in the system. Master replication is
--  deliberately application-level (see MasterRepl) because a missing
--  trigger there produces a silent replication gap. Here the trigger
--  is the point: it must fire on every path, including a DBA at a
--  mysql prompt at 11pm.
-- =====================================================================

DELIMITER $$

-- ---------------------------------------------------------------------
-- event_log — the record of truth. Insert only.
-- Exception: server_ts may be stamped once on cloud ingestion, because
-- the origin node cannot know it. Nothing else may change, ever.
-- ---------------------------------------------------------------------
DROP TRIGGER IF EXISTS trg_event_log_no_update$$
CREATE TRIGGER trg_event_log_no_update
BEFORE UPDATE ON event_log
FOR EACH ROW
BEGIN
  IF NOT (
       NEW.id          <=> OLD.id
   AND NEW.tenant_id   <=> OLD.tenant_id
   AND NEW.store_id    <=> OLD.store_id
   AND NEW.counter_id  <=> OLD.counter_id
   AND NEW.local_seq   <=> OLD.local_seq
   AND NEW.event_type  <=> OLD.event_type
   AND NEW.entity_type <=> OLD.entity_type
   AND NEW.entity_id   <=> OLD.entity_id
   AND NEW.corrects_id <=> OLD.corrects_id
   AND NEW.payload     <=> OLD.payload
   AND NEW.schema_ver  <=> OLD.schema_ver
   AND NEW.origin      <=> OLD.origin
   AND NEW.local_ts    <=> OLD.local_ts
   AND NEW.user_id     <=> OLD.user_id
  ) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'event_log is append-only (P4). Emit a correcting event instead.';
  END IF;

  IF OLD.server_ts IS NOT NULL AND NOT (NEW.server_ts <=> OLD.server_ts) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'event_log.server_ts is write-once.';
  END IF;
END$$

DROP TRIGGER IF EXISTS trg_event_log_no_delete$$
CREATE TRIGGER trg_event_log_no_delete
BEFORE DELETE ON event_log
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'event_log rows are never deleted (P4).';
END$$

-- ---------------------------------------------------------------------
-- sale_bill — immutable except the two operational counters.
-- A cancellation sets is_cancelled and records the reversing event; it
-- does not rewrite the bill. Amounts, dates and numbers never change.
-- ---------------------------------------------------------------------
DROP TRIGGER IF EXISTS trg_sale_bill_immutable$$
CREATE TRIGGER trg_sale_bill_immutable
BEFORE UPDATE ON sale_bill
FOR EACH ROW
BEGIN
  IF NOT (
       NEW.bill_no        <=> OLD.bill_no
   AND NEW.bill_series    <=> OLD.bill_series
   AND NEW.fy_code        <=> OLD.fy_code
   AND NEW.bill_date      <=> OLD.bill_date
   AND NEW.bill_ts        <=> OLD.bill_ts
   AND NEW.store_id       <=> OLD.store_id
   AND NEW.counter_id     <=> OLD.counter_id
   AND NEW.channel        <=> OLD.channel
   AND NEW.customer_id    <=> OLD.customer_id
   AND NEW.gross_amount   <=> OLD.gross_amount
   AND NEW.disc_amount    <=> OLD.disc_amount
   AND NEW.taxable_amount <=> OLD.taxable_amount
   AND NEW.cgst_amount    <=> OLD.cgst_amount
   AND NEW.sgst_amount    <=> OLD.sgst_amount
   AND NEW.igst_amount    <=> OLD.igst_amount
   AND NEW.net_amount     <=> OLD.net_amount
  ) THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'sale_bill is immutable (P5). Raise a credit note instead of editing.';
  END IF;
END$$

DROP TRIGGER IF EXISTS trg_sale_bill_no_delete$$
CREATE TRIGGER trg_sale_bill_no_delete
BEFORE DELETE ON sale_bill
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'sale_bill rows are never deleted. Cancel via credit note.';
END$$

DROP TRIGGER IF EXISTS trg_sale_bill_line_immutable$$
CREATE TRIGGER trg_sale_bill_line_immutable
BEFORE UPDATE ON sale_bill_line
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'sale_bill_line is immutable (P5).';
END$$

-- ---------------------------------------------------------------------
-- stock_ledger — append-only. stock_balance is the derived cache and
-- remains freely updatable; if the two disagree, the ledger wins and
-- the balance is rebuilt.
-- ---------------------------------------------------------------------
DROP TRIGGER IF EXISTS trg_stock_ledger_no_update$$
CREATE TRIGGER trg_stock_ledger_no_update
BEFORE UPDATE ON stock_ledger
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'stock_ledger is append-only (P4). Post a reversing movement.';
END$$

DROP TRIGGER IF EXISTS trg_stock_ledger_no_delete$$
CREATE TRIGGER trg_stock_ledger_no_delete
BEFORE DELETE ON stock_ledger
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'stock_ledger rows are never deleted (P4).';
END$$

-- ---------------------------------------------------------------------
-- repl_change_log — the replication spine. If rows can vanish, a store
-- node silently misses a master update and nobody finds out until a
-- chemist bills at the wrong GST rate.
-- ---------------------------------------------------------------------
DROP TRIGGER IF EXISTS trg_repl_change_log_no_update$$
CREATE TRIGGER trg_repl_change_log_no_update
BEFORE UPDATE ON repl_change_log
FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'repl_change_log is append-only.';
END$$

DELIMITER ;

-- =====================================================================
--  OPERATIONAL NOTE
--
--  Archival, not deletion. When event_log or stock_ledger grow beyond
--  the retention window, move rows to an archive table with the trigger
--  temporarily disabled by a DBA under written change control — never
--  from application code, and never from a support session.
--
--  Statutory retention under the Drugs and Cosmetics Act and GST rules
--  is longer than most teams assume. Confirm with counsel before
--  archiving anything.
-- =====================================================================