-- Slice 8i-DocNumbers · central document numbering (improved rewrite of legacy order_settings)
--
-- Improvements vs rookpmgt_hqdspera.order_settings:
--   · doc_key is UNIQUE + canonical (no free-text order_type typos causing silent dupes)
--   · prefix + separator + optional year component + zero-padded seq are all configurable
--   · reset_scope = NEVER / YEARLY / MONTHLY with auto-detection on allocate
--   · current_year + current_month tracked for rollover trigger
--   · atomic allocate via single UPDATE with WHERE current_year + optimistic retry

CREATE TABLE IF NOT EXISTS `doc_number_settings` (
    `c0d`             INT AUTO_INCREMENT PRIMARY KEY,
    `doc_key`         VARCHAR(60)   NOT NULL,                -- e.g. LPO, CR, IPC, SCRUB, CLIENT_PO
    `label`           VARCHAR(120)  NOT NULL DEFAULT '',
    `prefix`          VARCHAR(20)   NOT NULL DEFAULT '',     -- e.g. LPO
    `separator`       VARCHAR(4)    NOT NULL DEFAULT '-',
    `include_year`    TINYINT(1)    NOT NULL DEFAULT 1,
    `year_position`   ENUM('AFTER_PREFIX','BEFORE_SEQ','AFTER_SEQ') NOT NULL DEFAULT 'AFTER_PREFIX',
    `seq_width`       TINYINT       NOT NULL DEFAULT 4,      -- zero-pad width
    `start_no`        INT           NOT NULL DEFAULT 1,      -- reset target when scope trips
    `next_no`         INT           NOT NULL DEFAULT 1,      -- next seq to allocate
    `reset_scope`     ENUM('NEVER','YEARLY','MONTHLY') NOT NULL DEFAULT 'YEARLY',
    `current_year`    INT           NOT NULL DEFAULT 0,      -- year the current next_no belongs to
    `current_month`   TINYINT       NOT NULL DEFAULT 0,      -- 1..12; only used when reset_scope=MONTHLY
    `example`         VARCHAR(80)   NOT NULL DEFAULT '',     -- last allocated string, for preview
    `notes`           VARCHAR(500)  NOT NULL DEFAULT '',
    `created_by`      INT           NOT NULL DEFAULT 0,
    `created_on`      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `edited_by`       INT           NOT NULL DEFAULT 0,
    `edited_on`       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted`         TINYINT(1)    NOT NULL DEFAULT 0,
    UNIQUE KEY `uq_doc_key` (`doc_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* ── Seed defaults for every doc-key the app currently mints ─────────── */
INSERT INTO doc_number_settings (doc_key, label, prefix, seq_width, start_no, next_no, reset_scope, current_year, notes)
VALUES
    -- Cash-out chain
    ('LPO',        'Local Purchase Order',        'LPO', 4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Allocated only on chain terminal APPROVED (Slice 8d).'),
    ('PV',         'Payment Voucher',             'PV',  4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Allocated by PaymentVouchers::prepFromLpo.'),
    ('PAYMENT',    'Bank Payment',                'PAY', 4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Allocated by PaymentVouchers::recordPayment.'),
    -- Works project side
    ('CR',         'Consolidated Request',        'CR',  4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Works CR against a phase.'),
    ('IPC',        'Interim Payment Certificate', 'IPC', 4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'IPC per project — project component prepended separately in code.'),
    -- Goods project side
    ('SCRUB',      'PC 7-pillar Scrub',           'SCR', 4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Slice 8h scrub pack per Client PO.'),
    ('CLIENT_PO',  'Customer Client PO',          'CPO', 4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'CRM-authored per Slice 8g.'),
    ('SALE',       'Sales Invoice',               'SI',  4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Allocated by Sales::allocateAndAck on terminal.'),
    ('RECEIPT',    'Customer Receipt',            'RCP', 4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Allocated by Receipts::record.'),
    -- Project + tender
    ('PROJECT',    'Project number',              'P',   4, 1, 1, 'YEARLY', YEAR(CURDATE()), 'Allocated by Projects::create.')
ON DUPLICATE KEY UPDATE label = VALUES(label);  -- idempotent seed; existing rows untouched
