-- 0005 — Phase 5: customers, accounts, credit & funding at the counter
-- (doc 13 §Phase-5, doc 03). Accounts and authorized persons are master data
-- pulled to devices (contract 1.4.0, cached-balance provisional model);
-- order_charges and account_payments are the immutable AR ledger the balances
-- derive from — never a stored running total (doc 03 §3).

CREATE TABLE accounts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    code VARCHAR(20) NOT NULL,
    type VARCHAR(12) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NULL,
    home_branch_id CHAR(36) NOT NULL,
    allow_cross_branch TINYINT NOT NULL DEFAULT 0,
    office_ref VARCHAR(40) NULL,
    parent_account_id CHAR(36) NULL,
    employee_funding_policy VARCHAR(20) NOT NULL DEFAULT 'NONE_COVERED',
    is_restricted TINYINT NOT NULL DEFAULT 0,
    credit_limit_iqd INT NULL,
    billing_mode VARCHAR(15) NOT NULL DEFAULT 'MONTHLY',
    interval_days INT NULL,
    anchor_day VARCHAR(3) NULL,
    cycle_start_date DATE NULL,
    due_days INT NOT NULL DEFAULT 0,
    grace_days INT NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    auto_hold_when_overdue TINYINT NOT NULL DEFAULT 1,
    invoice_delivery VARCHAR(20) NOT NULL DEFAULT 'print',
    status VARCHAR(10) NOT NULL DEFAULT 'active',
    on_hold_reason VARCHAR(20) NULL,
    notes TEXT NULL,
    opened_at_utc DATETIME NOT NULL,
    closed_at_utc DATETIME NULL,
    created_by_user_id CHAR(36) NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_accounts_code UNIQUE (code),
    CONSTRAINT fk_accounts_branch FOREIGN KEY (home_branch_id) REFERENCES branches (id),
    CONSTRAINT ck_accounts_type CHECK (type IN ('company', 'individual')),
    CONSTRAINT ck_accounts_policy CHECK (employee_funding_policy IN ('ALL_COVERED', 'NONE_COVERED', 'CAPPED_PER_EMPLOYEE')),
    CONSTRAINT ck_accounts_mode CHECK (billing_mode IN ('IMMEDIATE', 'DAILY', 'EVERY_N_DAYS', 'MONTHLY')),
    CONSTRAINT ck_accounts_status CHECK (status IN ('active', 'on_hold', 'closed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_accounts_branch ON accounts (home_branch_id, status);

CREATE TABLE account_contacts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    account_id CHAR(36) NOT NULL,
    name VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NULL,
    role VARCHAR(30) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_contacts_account FOREIGN KEY (account_id) REFERENCES accounts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE authorized_persons (
    id CHAR(36) NOT NULL PRIMARY KEY,
    account_id CHAR(36) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NULL,
    office_ref VARCHAR(40) NULL,
    funding_override VARCHAR(15) NOT NULL DEFAULT 'INHERIT',
    cap_amount_iqd INT NULL,
    cap_period VARCHAR(20) NOT NULL DEFAULT 'BILLING_CYCLE',
    cap_carryover TINYINT NOT NULL DEFAULT 0,
    on_cap_exceeded VARCHAR(25) NOT NULL DEFAULT 'SPLIT_TO_PERSONAL',
    per_order_limit_iqd INT NULL,
    daily_limit_iqd INT NULL,
    requires_signature TINYINT NOT NULL DEFAULT 0,
    valid_from DATE NULL,
    valid_to DATE NULL,
    status VARCHAR(10) NOT NULL DEFAULT 'active',
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_persons_account FOREIGN KEY (account_id) REFERENCES accounts (id),
    CONSTRAINT ck_persons_override CHECK (funding_override IN ('INHERIT', 'COVERED', 'NOT_COVERED', 'CAP')),
    CONSTRAINT ck_persons_period CHECK (cap_period IN ('BILLING_CYCLE', 'CALENDAR_MONTH')),
    CONSTRAINT ck_persons_exceeded CHECK (on_cap_exceeded IN ('BLOCK', 'SPLIT_TO_PERSONAL', 'ALLOW_WITH_APPROVAL'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_persons_account ON authorized_persons (account_id, status);

-- One order may carry several charge legs (doc 03 §6.4, doc 04 §2 charges[]).
-- Amounts are signed: refund credit notes arrive as negative legs. Balances and
-- caps DERIVE from these rows joined to non-voided orders (doc 03 §3/§6.3).
CREATE TABLE order_charges (
    id CHAR(36) NOT NULL PRIMARY KEY,
    order_id CHAR(36) NOT NULL,
    account_id CHAR(36) NOT NULL,
    authorized_person_id CHAR(36) NULL,
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    amount_iqd INT NOT NULL,
    funding VARCHAR(10) NOT NULL,
    credit_check VARCHAR(12) NOT NULL DEFAULT 'provisional',
    over_limit TINYINT NOT NULL DEFAULT 0,
    approved_by_user_id CHAR(36) NULL,
    source_event_id VARCHAR(64) NOT NULL,
    CONSTRAINT fk_charges_order FOREIGN KEY (order_id) REFERENCES orders (id),
    CONSTRAINT fk_charges_account FOREIGN KEY (account_id) REFERENCES accounts (id),
    CONSTRAINT ck_charges_funding CHECK (funding IN ('covered', 'personal')),
    CONSTRAINT ck_charges_check CHECK (credit_check IN ('passed', 'provisional', 'override'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_charges_account ON order_charges (account_id, business_day);
CREATE INDEX ix_charges_person ON order_charges (authorized_person_id, business_day);
CREATE INDEX ix_charges_order ON order_charges (order_id);

-- Account payments received at the POS (doc 05 §5; payment.received events).
-- Immutable — corrections are reversals (Phase 6), never edits.
CREATE TABLE account_payments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    receipt_number VARCHAR(30) NOT NULL,
    account_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    shift_id CHAR(36) NULL,
    received_by_user_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    usd_minor INT NULL,
    fx_rate VARCHAR(20) NULL,
    method VARCHAR(15) NOT NULL,
    reference VARCHAR(60) NULL,
    received_at_utc DATETIME NOT NULL,
    business_day DATE NOT NULL,
    note TEXT NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_payments_receipt UNIQUE (branch_id, receipt_number),
    CONSTRAINT fk_apayments_account FOREIGN KEY (account_id) REFERENCES accounts (id),
    CONSTRAINT ck_apayments_method CHECK (method IN ('cash', 'card', 'wallet', 'bank_transfer', 'cheque'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_apayments_account ON account_payments (account_id, business_day);
CREATE INDEX ix_apayments_day ON account_payments (branch_id, business_day);

ALTER TABLE rollup_daily_sales ADD COLUMN on_account_iqd INT NOT NULL DEFAULT 0;
ALTER TABLE rollup_daily_sales ADD COLUMN account_payments_iqd INT NOT NULL DEFAULT 0;
