-- 0006 — Phase 6: billing engine & collections (doc 13 §Phase-6, doc 05).
-- The document chain: charges → invoices → payments/allocations → credit
-- notes / write-offs, plus reminders and the collections log. Every document
-- immutable once issued; balances derived from allocations, never stored as
-- a lone mutable number (doc 10 §5).

-- Charge lifecycle per doc 05 §2: unbilled → billed (invoice_id set) / voided.
ALTER TABLE order_charges ADD COLUMN status VARCHAR(10) NOT NULL DEFAULT 'unbilled';
ALTER TABLE order_charges ADD COLUMN invoice_id CHAR(36) NULL;
CREATE INDEX ix_charges_billing ON order_charges (account_id, status, business_day);

-- Per-account billing presentation + zero-activity behaviour (doc 05 §3/§4),
-- and the pending-terms slot: a terms change completes the open period under
-- the OLD terms and applies from the next (doc 03 §4 rule 2, doc 05 §3).
ALTER TABLE accounts ADD COLUMN issue_zero_invoices TINYINT NOT NULL DEFAULT 0;
ALTER TABLE accounts ADD COLUMN statement_grouping VARCHAR(10) NOT NULL DEFAULT 'full';
ALTER TABLE accounts ADD COLUMN pending_terms_json TEXT NULL;

-- Central payment entry + immutable reversal documents (doc 05 §5 rule 5).
ALTER TABLE account_payments ADD COLUMN source VARCHAR(15) NOT NULL DEFAULT 'pos';
ALTER TABLE account_payments ADD COLUMN reverses_payment_id CHAR(36) NULL;

-- Gapless sequences (doc 05 §4 rule 1): the number is taken with a row lock in
-- the SAME transaction that inserts the document — no success, no increment.
CREATE TABLE number_sequences (
    name VARCHAR(20) NOT NULL PRIMARY KEY,
    next_value BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO number_sequences (name, next_value) VALUES ('invoice', 1), ('credit_note', 1);

CREATE TABLE invoices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    invoice_number VARCHAR(20) NOT NULL,
    account_id CHAR(36) NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    grace_until DATE NOT NULL,
    subtotal_iqd INT NOT NULL,
    total_iqd INT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    status VARCHAR(15) NOT NULL DEFAULT 'issued',
    late_arrival_count INT NOT NULL DEFAULT 0,
    statement_grouping VARCHAR(10) NOT NULL DEFAULT 'full',
    terms_snapshot_json TEXT NOT NULL,
    cap_summary_json TEXT NULL,
    branch_breakdown_json TEXT NULL,
    delivery VARCHAR(20) NOT NULL DEFAULT 'print',
    delivered_at_utc DATETIME NULL,
    cancelled_at_utc DATETIME NULL,
    cancelled_by_user_id CHAR(36) NULL,
    cancel_reason VARCHAR(200) NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_invoice_number UNIQUE (invoice_number),
    CONSTRAINT fk_invoices_account FOREIGN KEY (account_id) REFERENCES accounts (id),
    CONSTRAINT ck_invoice_status CHECK (status IN ('issued', 'partially_paid', 'paid', 'overdue', 'written_off', 'cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_invoices_account ON invoices (account_id, period_start, period_end);
CREATE INDEX ix_invoices_due ON invoices (status, due_date);

-- One line per charge; grouping is presentation-time (doc 05 §4). Lines of a
-- cancelled invoice remain as history — the charge's own status/invoice_id
-- pointer says where it currently lives.
CREATE TABLE invoice_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    invoice_id CHAR(36) NOT NULL,
    order_charge_id CHAR(36) NOT NULL,
    order_id CHAR(36) NOT NULL,
    order_number VARCHAR(30) NOT NULL,
    business_day DATE NOT NULL,
    branch_id CHAR(36) NOT NULL,
    authorized_person_id CHAR(36) NULL,
    person_name_ar VARCHAR(200) NULL,
    amount_iqd INT NOT NULL,
    late_arrival TINYINT NOT NULL DEFAULT 0,
    CONSTRAINT fk_ilines_invoice FOREIGN KEY (invoice_id) REFERENCES invoices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_ilines_invoice ON invoice_lines (invoice_id);
CREATE INDEX ix_ilines_charge ON invoice_lines (order_charge_id);

-- One allocator for payments AND credit notes (doc 05 §5 rule 2, §6):
-- oldest-invoice-first by default; unapplied remainder = source amount − Σ here.
CREATE TABLE ar_allocations (
    id CHAR(36) NOT NULL PRIMARY KEY,
    source_type VARCHAR(12) NOT NULL,
    source_id CHAR(36) NOT NULL,
    invoice_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    allocated_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_alloc_invoice FOREIGN KEY (invoice_id) REFERENCES invoices (id),
    CONSTRAINT ck_alloc_source CHECK (source_type IN ('payment', 'credit_note', 'write_off'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_alloc_invoice ON ar_allocations (invoice_id);
CREATE INDEX ix_alloc_source ON ar_allocations (source_type, source_id);

CREATE TABLE credit_notes (
    id CHAR(36) NOT NULL PRIMARY KEY,
    note_number VARCHAR(20) NOT NULL,
    account_id CHAR(36) NOT NULL,
    invoice_id CHAR(36) NULL,
    amount_iqd INT NOT NULL,
    reason_code VARCHAR(40) NOT NULL,
    note TEXT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_credit_note_number UNIQUE (note_number),
    CONSTRAINT fk_cn_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE write_offs (
    id CHAR(36) NOT NULL PRIMARY KEY,
    account_id CHAR(36) NOT NULL,
    invoice_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    reason_code VARCHAR(40) NOT NULL,
    note TEXT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NOT NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_wo_account FOREIGN KEY (account_id) REFERENCES accounts (id),
    CONSTRAINT fk_wo_invoice FOREIGN KEY (invoice_id) REFERENCES invoices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The reminder queue + collections activity log (doc 05 §7). Reminders are
-- queued by the aging step; sending stays manual until the Phase 7 digest
-- transport exists.
CREATE TABLE reminders (
    id CHAR(36) NOT NULL PRIMARY KEY,
    invoice_id CHAR(36) NOT NULL,
    account_id CHAR(36) NOT NULL,
    stage TINYINT NOT NULL,
    queued_on DATE NOT NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'pending',
    sent_at_utc DATETIME NULL,
    sent_by_user_id CHAR(36) NULL,
    CONSTRAINT uq_reminder UNIQUE (invoice_id, stage),
    CONSTRAINT ck_reminder_status CHECK (status IN ('pending', 'sent', 'dismissed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE collections_log (
    id CHAR(36) NOT NULL PRIMARY KEY,
    account_id CHAR(36) NOT NULL,
    invoice_id CHAR(36) NULL,
    type VARCHAR(30) NOT NULL,
    note TEXT NULL,
    actor_user_id CHAR(36) NULL,
    at_utc DATETIME NOT NULL,
    CONSTRAINT fk_clog_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_clog_account ON collections_log (account_id, at_utc);
