-- 0004 — Phase 4: sync pipeline & central reporting (doc 13 §Phase-4, doc 15 §1.3.0).
-- Staging table (doc 12 §5 fast-and-dumb push), central mirrors of the branch
-- POS documents (applied by sync:process, idempotent by natural id), rollup
-- tables REBUILDABLE FROM SOURCE (doc 09 §8 / doc 13), alerts, and push
-- observability on devices. Centre-only — no branch mirror; the PWA's local
-- store is IndexedDB (D17).

CREATE TABLE sync_staging (
    seq BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    event_id VARCHAR(64) NOT NULL,
    device_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    type VARCHAR(60) NOT NULL,
    occurred_at_utc VARCHAR(32) NOT NULL,
    business_day DATE NOT NULL,
    payload LONGTEXT NOT NULL,
    received_at_utc DATETIME NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    quarantine_reason VARCHAR(40) NULL,
    attempts INT NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    processed_at_utc DATETIME NULL,
    CONSTRAINT uq_staging_event UNIQUE (event_id),
    CONSTRAINT ck_staging_status CHECK (status IN ('pending', 'applied', 'quarantined'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_staging_pending ON sync_staging (status, seq);
CREATE INDEX ix_staging_device ON sync_staging (device_id, seq);

-- Central mirror of branch sale/refund documents. Financial figures are
-- immutable once applied (doc 10 §5); status/void_json are lifecycle fields
-- mirroring the branch truth (doc 04 §6 — the void is recorded AGAINST the
-- order). payload_json keeps the exact device document for fidelity.
CREATE TABLE orders (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    device_id VARCHAR(64) NULL,
    shift_id CHAR(36) NULL,
    cashier_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    type VARCHAR(10) NOT NULL DEFAULT 'sale',
    refund_of_order_id CHAR(36) NULL,
    order_number VARCHAR(30) NOT NULL,
    business_day DATE NOT NULL,
    created_at_utc DATETIME NOT NULL,
    created_at_local DATETIME NOT NULL,
    buyer_type VARCHAR(20) NOT NULL DEFAULT 'walk_in',
    fulfilment VARCHAR(20) NOT NULL DEFAULT 'counter_pickup',
    office_ref VARCHAR(40) NULL,
    subtotal INT NOT NULL,
    discount_total INT NOT NULL DEFAULT 0,
    tax_total INT NOT NULL DEFAULT 0,
    grand_total INT NOT NULL,
    cash_rounding_house_gain INT NOT NULL DEFAULT 0,
    settlement_type VARCHAR(20) NOT NULL DEFAULT 'paid',
    status VARCHAR(20) NOT NULL,
    prep_state VARCHAR(20) NULL,
    late_arrival TINYINT NOT NULL DEFAULT 0,
    reason_code VARCHAR(40) NULL,
    note TEXT NULL,
    void_json LONGTEXT NULL,
    payload_json LONGTEXT NOT NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_orders_number UNIQUE (branch_id, order_number),
    CONSTRAINT fk_orders_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_orders_type CHECK (type IN ('sale', 'refund'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_orders_day ON orders (branch_id, business_day);
CREATE INDEX ix_orders_cashier ON orders (cashier_user_id, business_day);
CREATE INDEX ix_orders_refund_of ON orders (refund_of_order_id);

CREATE TABLE order_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    order_id CHAR(36) NOT NULL,
    item_id CHAR(36) NOT NULL,
    item_name_ar VARCHAR(200) NOT NULL,
    item_name_en VARCHAR(200) NULL,
    sku VARCHAR(60) NULL,
    prep_station VARCHAR(20) NULL,
    quantity INT NOT NULL,
    unit_price INT NOT NULL,
    modifiers_json LONGTEXT NULL,
    line_discount INT NOT NULL DEFAULT 0,
    discount_reason VARCHAR(40) NULL,
    line_total INT NOT NULL,
    for_person VARCHAR(100) NULL,
    void_of_line_id CHAR(36) NULL,
    CONSTRAINT fk_lines_order FOREIGN KEY (order_id) REFERENCES orders (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_lines_order ON order_lines (order_id);
CREATE INDEX ix_lines_item ON order_lines (item_id);

CREATE TABLE order_payments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    order_id CHAR(36) NOT NULL,
    tender VARCHAR(20) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    direction VARCHAR(5) NOT NULL DEFAULT 'in',
    amount_iqd INT NOT NULL,
    cash_due_exact INT NULL,
    tendered_iqd INT NULL,
    change_iqd INT NULL,
    usd_minor INT NULL,
    fx_rate VARCHAR(20) NULL,
    iqd_equivalent INT NULL,
    cash_rounding_house_gain INT NOT NULL DEFAULT 0,
    ref VARCHAR(60) NULL,
    provider VARCHAR(30) NULL,
    CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_payments_order ON order_payments (order_id);

CREATE TABLE shifts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    device_id VARCHAR(64) NULL,
    opened_by_user_id CHAR(36) NOT NULL,
    opened_at_utc DATETIME NOT NULL,
    opened_at_local DATETIME NOT NULL,
    business_day DATE NOT NULL,
    opening_float INT NOT NULL,
    opening_denominations_json TEXT NULL,
    status VARCHAR(20) NOT NULL,
    closed_by_user_id CHAR(36) NULL,
    closed_at_utc DATETIME NULL,
    counted_cash INT NULL,
    counted_denominations_json TEXT NULL,
    counted_usd_minor INT NULL,
    expected_cash INT NULL,
    expected_usd_minor INT NULL,
    variance INT NULL,
    variance_reason TEXT NULL,
    approved_by_user_id CHAR(36) NULL,
    crossed_business_day TINYINT NOT NULL DEFAULT 0,
    escalated TINYINT NOT NULL DEFAULT 0,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_shifts_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_shifts_day ON shifts (branch_id, business_day);

CREATE TABLE cash_movements (
    id CHAR(36) NOT NULL PRIMARY KEY,
    shift_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    type VARCHAR(30) NOT NULL,
    direction VARCHAR(6) NOT NULL,
    amount_iqd INT NOT NULL DEFAULT 0,
    reason_code VARCHAR(40) NOT NULL,
    note TEXT NULL,
    user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    at_utc DATETIME NOT NULL,
    source_event_id VARCHAR(64) NOT NULL,
    CONSTRAINT fk_movements_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_movements_shift ON cash_movements (shift_id);
CREATE INDEX ix_movements_day ON cash_movements (branch_id, business_day);

CREATE TABLE day_closes (
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    closed_by_user_id CHAR(36) NOT NULL,
    closed_at_utc DATETIME NOT NULL,
    z_json LONGTEXT NOT NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    PRIMARY KEY (branch_id, business_day),
    CONSTRAINT fk_daycloses_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rollups: never scan raw tables for reports (doc 12 §11). Rebuildable from
-- source at any time; rollups:build drains rollup_dirty.
CREATE TABLE rollup_daily_sales (
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    gross_sales INT NOT NULL DEFAULT 0,
    discount_total INT NOT NULL DEFAULT 0,
    net_sales INT NOT NULL DEFAULT 0,
    order_count INT NOT NULL DEFAULT 0,
    void_count INT NOT NULL DEFAULT 0,
    void_value INT NOT NULL DEFAULT 0,
    refund_count INT NOT NULL DEFAULT 0,
    refund_value INT NOT NULL DEFAULT 0,
    cash_iqd INT NOT NULL DEFAULT 0,
    card_iqd INT NOT NULL DEFAULT 0,
    wallet_iqd INT NOT NULL DEFAULT 0,
    usd_cash_minor INT NOT NULL DEFAULT 0,
    usd_cash_iqd_equivalent INT NOT NULL DEFAULT 0,
    change_out_iqd INT NOT NULL DEFAULT 0,
    cash_rounding INT NOT NULL DEFAULT 0,
    late_arrival_count INT NOT NULL DEFAULT 0,
    cash_variance INT NOT NULL DEFAULT 0,
    built_at_utc DATETIME NOT NULL,
    PRIMARY KEY (branch_id, business_day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rollup_item_sales (
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    item_id CHAR(36) NOT NULL,
    item_name_ar VARCHAR(200) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    net_total INT NOT NULL DEFAULT 0,
    built_at_utc DATETIME NOT NULL,
    PRIMARY KEY (branch_id, business_day, item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rollup_cashier_actions (
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    user_id CHAR(36) NOT NULL,
    orders_count INT NOT NULL DEFAULT 0,
    net_sales INT NOT NULL DEFAULT 0,
    discount_count INT NOT NULL DEFAULT 0,
    discount_value INT NOT NULL DEFAULT 0,
    void_count INT NOT NULL DEFAULT 0,
    void_value INT NOT NULL DEFAULT 0,
    refund_count INT NOT NULL DEFAULT 0,
    refund_value INT NOT NULL DEFAULT 0,
    no_sale_opens INT NOT NULL DEFAULT 0,
    built_at_utc DATETIME NOT NULL,
    PRIMARY KEY (branch_id, business_day, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rollup_dirty (
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    marked_at_utc DATETIME NOT NULL,
    PRIMARY KEY (branch_id, business_day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Central alerts (doc 10 §4: a device offline beyond N hours raises an alert).
CREATE TABLE alerts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    type VARCHAR(40) NOT NULL,
    entity_id VARCHAR(64) NOT NULL,
    branch_id CHAR(36) NULL,
    message TEXT NOT NULL,
    opened_at_utc DATETIME NOT NULL,
    resolved_at_utc DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_alerts_open ON alerts (type, resolved_at_utc);

ALTER TABLE devices ADD COLUMN last_push_at_utc DATETIME NULL;
ALTER TABLE devices ADD COLUMN last_push_count INT NULL;
