-- 0008 — Phase 8: inventory, recipes & purchasing (doc 13 §Phase-8, doc 07).
-- Stock is strictly per branch and ALWAYS derived from the movement ledger —
-- quantity_on_hand is a sum, never a written field (doc 07 §2/§3). Costing is
-- weighted average per branch; every movement snapshots the unit cost it used
-- (unit_cost_milli = IQD × 1000 per base unit, because a gram of coffee costs
-- less than one dinar). Suppliers are global; purchasing is per branch.

CREATE TABLE inventory_items (
    id CHAR(36) NOT NULL PRIMARY KEY,
    sku VARCHAR(40) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NULL,
    category VARCHAR(20) NOT NULL DEFAULT 'Other',
    base_unit VARCHAR(6) NOT NULL,
    is_perishable TINYINT NOT NULL DEFAULT 0,
    shelf_life_days INT NULL,
    is_sold_directly TINYINT NOT NULL DEFAULT 0,
    menu_item_id CHAR(36) NULL,
    default_supplier_id CHAR(36) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_inv_sku UNIQUE (sku),
    CONSTRAINT ck_inv_unit CHECK (base_unit IN ('g', 'ml', 'piece'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Purchase units convert ON RECEIPT; the ledger is base-unit only (doc 07 §1).
CREATE TABLE inventory_purchase_units (
    id CHAR(36) NOT NULL PRIMARY KEY,
    inventory_item_id CHAR(36) NOT NULL,
    name VARCHAR(40) NOT NULL,
    factor INT NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_ipu_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id),
    CONSTRAINT ck_ipu_factor CHECK (factor > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Modifier recipe deltas (doc 02 §3): almond milk = −150 ml regular, +150 ml almond.
CREATE TABLE modifier_recipe_deltas (
    id CHAR(36) NOT NULL PRIMARY KEY,
    option_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    quantity_delta INT NOT NULL,
    effective_from DATE NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_mrd_option FOREIGN KEY (option_id) REFERENCES modifier_options (id),
    CONSTRAINT fk_mrd_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branch_stock_settings (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    reorder_point INT NULL,
    reorder_quantity INT NULL,
    storage_location VARCHAR(40) NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_bss UNIQUE (branch_id, inventory_item_id),
    CONSTRAINT fk_bss_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT fk_bss_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- THE ledger (doc 07 §3). Everything is a movement; on-hand is its sum.
CREATE TABLE stock_movements (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    type VARCHAR(20) NOT NULL,
    quantity INT NOT NULL,
    unit_cost_milli INT NOT NULL DEFAULT 0,
    reference VARCHAR(64) NULL,
    reason_code VARCHAR(40) NULL,
    note VARCHAR(200) NULL,
    user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    employee_ref CHAR(36) NULL,
    occurred_at_utc DATETIME NOT NULL,
    business_day DATE NOT NULL,
    local_seq BIGINT NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_sm_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT fk_sm_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id),
    CONSTRAINT ck_sm_type CHECK (type IN ('receipt', 'sale', 'void_return', 'transfer_out', 'transfer_in',
        'waste', 'staff_consumption', 'sample_marketing', 'count_adjustment', 'manual_adjustment'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_sm_branch_item ON stock_movements (branch_id, inventory_item_id, business_day);
CREATE INDEX ix_sm_type ON stock_movements (branch_id, type, business_day);
CREATE INDEX ix_sm_reference ON stock_movements (reference);

-- Suppliers: global master data (doc 07 §4).
CREATE TABLE suppliers (
    id CHAR(36) NOT NULL PRIMARY KEY,
    code VARCHAR(20) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NULL,
    payment_terms VARCHAR(20) NOT NULL DEFAULT 'cash_on_delivery',
    net_days INT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    lead_time_days INT NOT NULL DEFAULT 1,
    delivery_days VARCHAR(30) NULL,
    rating TINYINT NULL,
    notes TEXT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_supplier_code UNIQUE (code),
    CONSTRAINT ck_supplier_terms CHECK (payment_terms IN ('cash_on_delivery', 'net_days', 'end_of_month'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supplier_contacts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    supplier_id CHAR(36) NOT NULL,
    name VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_sc_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supplier_prices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    supplier_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    purchase_unit_name VARCHAR(40) NOT NULL,
    unit_factor INT NOT NULL,
    price_iqd INT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NULL,
    min_order_qty INT NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_sp_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_sp_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Purchasing (doc 07 §5). PO numbers are per branch, sequential.
CREATE TABLE purchase_orders (
    id CHAR(36) NOT NULL PRIMARY KEY,
    po_number VARCHAR(30) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    supplier_id CHAR(36) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    expected_date DATE NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    fx_rate VARCHAR(20) NULL,
    notes TEXT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_po_number UNIQUE (po_number),
    CONSTRAINT fk_po_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT ck_po_status CHECK (status IN ('draft', 'approved', 'sent', 'partially_received', 'received', 'cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_po_branch ON purchase_orders (branch_id, status);

CREATE TABLE purchase_order_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    po_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    purchase_unit_name VARCHAR(40) NOT NULL,
    unit_factor INT NOT NULL,
    quantity INT NOT NULL,
    unit_price_iqd INT NOT NULL,
    line_total_iqd INT NOT NULL,
    CONSTRAINT fk_pol_po FOREIGN KEY (po_id) REFERENCES purchase_orders (id),
    CONSTRAINT fk_pol_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Goods receipts (doc 07 §5): recorded AT the branch; short/over as-is.
CREATE TABLE goods_receipts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    grn_number VARCHAR(30) NOT NULL,
    po_id CHAR(36) NULL,
    branch_id CHAR(36) NOT NULL,
    supplier_id CHAR(36) NULL,
    received_by_user_id CHAR(36) NOT NULL,
    received_at_utc DATETIME NOT NULL,
    business_day DATE NOT NULL,
    notes VARCHAR(200) NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_grn_number UNIQUE (branch_id, grn_number),
    CONSTRAINT fk_grn_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE goods_receipt_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    grn_id CHAR(36) NOT NULL,
    po_line_id CHAR(36) NULL,
    inventory_item_id CHAR(36) NOT NULL,
    quantity_base INT NOT NULL,
    unit_cost_milli INT NOT NULL,
    expiry_date DATE NULL,
    CONSTRAINT fk_grl_grn FOREIGN KEY (grn_id) REFERENCES goods_receipts (id),
    CONSTRAINT fk_grl_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Two-sided transfers with in-transit visibility (doc 07 §6).
CREATE TABLE stock_transfers (
    id CHAR(36) NOT NULL PRIMARY KEY,
    transfer_number VARCHAR(30) NOT NULL,
    from_branch_id CHAR(36) NOT NULL,
    to_branch_id CHAR(36) NOT NULL,
    status VARCHAR(15) NOT NULL DEFAULT 'requested',
    notes VARCHAR(200) NULL,
    created_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    sent_at_utc DATETIME NULL,
    received_at_utc DATETIME NULL,
    resolved_by_user_id CHAR(36) NULL,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_transfer_number UNIQUE (transfer_number),
    CONSTRAINT fk_st_from FOREIGN KEY (from_branch_id) REFERENCES branches (id),
    CONSTRAINT fk_st_to FOREIGN KEY (to_branch_id) REFERENCES branches (id),
    CONSTRAINT ck_st_status CHECK (status IN ('requested', 'approved', 'sent', 'received', 'discrepancy', 'resolved', 'cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_transfer_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    transfer_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    quantity_sent INT NULL,
    quantity_received INT NULL,
    unit_cost_milli INT NULL,
    CONSTRAINT fk_stl_transfer FOREIGN KEY (transfer_id) REFERENCES stock_transfers (id),
    CONSTRAINT fk_stl_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Blind stock counts (doc 07 §8) — device-created, frozen once approved.
CREATE TABLE stock_counts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    count_number VARCHAR(30) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    type VARCHAR(10) NOT NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'approved',
    business_day DATE NOT NULL,
    opened_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    opened_at_utc DATETIME NOT NULL,
    approved_at_utc DATETIME NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_count_number UNIQUE (branch_id, count_number),
    CONSTRAINT fk_cnt_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_cnt_type CHECK (type IN ('spot', 'cycle', 'full'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_count_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    count_id CHAR(36) NOT NULL,
    inventory_item_id CHAR(36) NOT NULL,
    expected_qty INT NOT NULL,
    counted_qty INT NOT NULL,
    unit_cost_milli INT NOT NULL DEFAULT 0,
    variance_reason VARCHAR(200) NULL,
    CONSTRAINT fk_cl_count FOREIGN KEY (count_id) REFERENCES stock_counts (id),
    CONSTRAINT fk_cl_item FOREIGN KEY (inventory_item_id) REFERENCES inventory_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Manager PO approval threshold (doc 07 §5, matrix LIMITED).
ALTER TABLE branches ADD COLUMN po_approval_threshold_iqd INT NULL;

-- Inventory alerts key on composite "branch|item" identifiers (doc 07 §9).
ALTER TABLE alerts MODIFY entity_id VARCHAR(128) NOT NULL;
