-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack R — Day close / till reconciliation v1.0
--
--  Apply AFTER schema-phase0-core-v1.sql.
--
--  =================================================================
--  WHY THIS EXISTS
--  =================================================================
--
--  Every shop counts the till at close. It is the single control that
--  catches a biller pocketing cash, a mis-keyed payment mode, and a
--  refund that never happened — and none of those are visible in any
--  report, because each one leaves the books internally consistent.
--
--  The core schema's event catalogue lists `day_close`,
--  `counter_opened` and `counter_closed`. None of the three was ever
--  built. The design anticipated this and the build did not reach it.
--
--  WHAT IT IS NOT: a "shift" system with logins and handovers. A single
--  chemist counting one drawer at 9pm is the case that matters. Anything
--  more elaborate goes unused and the control is lost.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE day_close (
  id                CHAR(26)     NOT NULL,
  tenant_id         CHAR(26)     NOT NULL,
  store_id          CHAR(26)     NOT NULL,
  /* NOT NULL, with a sentinel for "whole shop".
   *
   * This was NULL-able, and MySQL treats NULL as distinct from NULL in a
   * unique index — so uk_close_day allowed the same shop and date to be
   * closed twice, which is exactly what it existed to prevent. The test
   * closed a day, closed it again, and got a second row.
   *
   * A sentinel makes the constraint real. '-' is not a valid ULID, so it
   * cannot collide with an actual counter. */
  counter_id        CHAR(26)     NOT NULL DEFAULT '-'
                    COMMENT '"-" = whole shop, not one till',
  close_date        DATE         NOT NULL,

  -- What the system believes, captured at the moment of close.
  expected_cash     DECIMAL(14,2) NOT NULL DEFAULT 0,
  expected_card     DECIMAL(14,2) NOT NULL DEFAULT 0,
  expected_upi      DECIMAL(14,2) NOT NULL DEFAULT 0,
  expected_credit   DECIMAL(14,2) NOT NULL DEFAULT 0,

  -- What was actually in the drawer. Cash only: card and UPI settle
  -- through the bank and are reconciled there, not counted by hand.
  counted_cash      DECIMAL(14,2) NOT NULL DEFAULT 0,

  -- counted_cash - expected_cash. Stored rather than derived so a later
  -- correction to a bill cannot silently rewrite last week's variance.
  variance          DECIMAL(14,2) NOT NULL DEFAULT 0,

  bills_count       INT UNSIGNED NOT NULL DEFAULT 0,
  reason            VARCHAR(40)      NULL COMMENT 'required when variance is material',
  note              VARCHAR(500)     NULL,

  closed_by         CHAR(26)         NULL,
  closed_at         DATETIME(3)  NOT NULL,
  event_id          CHAR(26)         NULL,
  created_at        DATETIME(3)  NOT NULL,

  PRIMARY KEY (id),
  -- One close per counter per day, and it WORKS because counter_id is
  -- NOT NULL. A second attempt must correct the first, not sit beside
  -- it: two closes for one evening is two answers to "what was in the
  -- drawer".
  UNIQUE KEY uk_close_day (store_id, counter_id, close_date),
  KEY ix_close_date (store_id, close_date),
  KEY ix_close_variance (store_id, variance)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. A day cannot be closed twice — the unique key, not a check in
--      application code.
--   2. variance = counted_cash - expected_cash, always.
--   3. A material variance without a reason is refused.
--   4. Closing does not alter a single bill. It records what was found;
--      it never adjusts the books to match the drawer.
-- =====================================================================