-- 0009 — Phase 9: expenses, payroll & branch profit (doc 13 §Phase-9, doc 08).
-- Everything exists to make Sales − COGS − Rent − Payroll − Opex computable
-- per branch per month without a spreadsheet. Rent stores BOTH sides: cash
-- when money moved, accrual per reporting period (doc 08 §2). Every expense
-- belongs to exactly one branch (rule 1).

CREATE TABLE expense_categories (
    id CHAR(36) NOT NULL PRIMARY KEY,
    code VARCHAR(30) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NULL,
    type VARCHAR(10) NOT NULL DEFAULT 'variable',
    is_cogs TINYINT NOT NULL DEFAULT 0,
    parent_category_id CHAR(36) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT uq_expcat_code UNIQUE (code),
    CONSTRAINT ck_expcat_type CHECK (type IN ('fixed', 'variable'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The doc 08 §1 chart, with the Iraqi grid/generator electricity split.
INSERT INTO expense_categories (id, code, name_ar, name_en, type, is_cogs) VALUES
    ('ec000000-0000-4000-8000-000000000001', 'RENT', 'إيجار', 'Rent', 'fixed', 0),
    ('ec000000-0000-4000-8000-000000000002', 'PAYROLL', 'رواتب', 'Payroll', 'fixed', 0),
    ('ec000000-0000-4000-8000-000000000003', 'ELEC_GRID', 'كهرباء وطنية', 'Electricity (grid)', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000004', 'ELEC_GEN', 'مولدة (أمبير)', 'Electricity (generator)', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000005', 'WATER_NET', 'ماء وإنترنت', 'Water & internet', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000006', 'FUEL_GAS', 'غاز ووقود', 'Fuel & gas', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000007', 'MAINTENANCE', 'صيانة', 'Maintenance', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000008', 'SUPPLIES', 'مستلزمات', 'Supplies', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000009', 'TRANSPORT', 'نقل', 'Transport', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000010', 'FEES', 'رسوم وتراخيص', 'Fees & licences', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000011', 'MARKETING', 'تسويق', 'Marketing', 'variable', 0),
    ('ec000000-0000-4000-8000-000000000012', 'INVENTORY', 'مشتريات مخزون', 'Inventory purchases', 'variable', 1),
    ('ec000000-0000-4000-8000-000000000013', 'OTHER', 'أخرى', 'Other', 'variable', 0);

CREATE TABLE expenses (
    id CHAR(36) NOT NULL PRIMARY KEY,
    expense_number VARCHAR(30) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    category_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,
    expense_date DATE NOT NULL,
    period_start DATE NULL,
    period_end DATE NULL,
    payee_type VARCHAR(12) NOT NULL DEFAULT 'other',
    payee_ref VARCHAR(64) NULL,
    description VARCHAR(300) NOT NULL,
    payment_method VARCHAR(20) NOT NULL DEFAULT 'unpaid',
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    attachment_ref VARCHAR(200) NULL,
    till_movement_id CHAR(36) NULL,
    recurring_schedule_id CHAR(36) NULL,
    rent_contract_id CHAR(36) NULL,
    reverses_expense_id CHAR(36) NULL,
    recorded_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    paid_at_utc DATETIME NULL,
    paid_by_user_id CHAR(36) NULL,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_expense_number UNIQUE (expense_number),
    CONSTRAINT fk_exp_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT fk_exp_category FOREIGN KEY (category_id) REFERENCES expense_categories (id),
    CONSTRAINT ck_exp_payee CHECK (payee_type IN ('landlord', 'supplier', 'employee', 'utility', 'other')),
    CONSTRAINT ck_exp_method CHECK (payment_method IN ('cash_from_till', 'cash_from_safe', 'bank_transfer', 'card', 'unpaid')),
    CONSTRAINT ck_exp_status CHECK (status IN ('draft', 'pending_approval', 'approved', 'paid', 'rejected'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_exp_branch_date ON expenses (branch_id, expense_date);
CREATE INDEX ix_exp_status ON expenses (status);
CREATE INDEX ix_exp_till ON expenses (till_movement_id);

CREATE TABLE recurring_schedules (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    category_id CHAR(36) NOT NULL,
    payee_type VARCHAR(12) NOT NULL DEFAULT 'other',
    payee_ref VARCHAR(64) NULL,
    description VARCHAR(300) NOT NULL,
    amount_iqd INT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    frequency VARCHAR(12) NOT NULL,
    interval_n INT NULL,
    anchor_day TINYINT NOT NULL DEFAULT 1,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    next_due_date DATE NOT NULL,
    auto_generate_days_before INT NOT NULL DEFAULT 3,
    status VARCHAR(10) NOT NULL DEFAULT 'active',
    created_by_user_id CHAR(36) NOT NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_rs_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_rs_freq CHECK (frequency IN ('monthly', 'quarterly', 'semi_annual', 'annual', 'every_n_days')),
    CONSTRAINT ck_rs_status CHECK (status IN ('active', 'paused', 'ended'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rent_contracts (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    landlord_name VARCHAR(200) NOT NULL,
    landlord_contact VARCHAR(200) NULL,
    contract_ref VARCHAR(60) NULL,
    contract_start DATE NOT NULL,
    contract_end DATE NOT NULL,
    amount INT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    payment_frequency VARCHAR(12) NOT NULL DEFAULT 'monthly',
    payment_in_advance TINYINT NOT NULL DEFAULT 0,
    deposit_amount INT NULL,
    deposit_currency CHAR(3) NULL,
    deposit_returned TINYINT NOT NULL DEFAULT 0,
    escalation_pct INT NULL,
    escalation_date DATE NULL,
    notice_period_days INT NOT NULL DEFAULT 60,
    includes_utilities TINYINT NOT NULL DEFAULT 0,
    status VARCHAR(10) NOT NULL DEFAULT 'active',
    created_by_user_id CHAR(36) NOT NULL,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_rent_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_rent_freq CHECK (payment_frequency IN ('monthly', 'quarterly', 'semi_annual', 'annual')),
    CONSTRAINT ck_rent_status CHECK (status IN ('active', 'ended'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE employees (
    id CHAR(36) NOT NULL PRIMARY KEY,
    employee_number VARCHAR(20) NOT NULL,
    full_name_ar VARCHAR(200) NOT NULL,
    full_name_en VARCHAR(200) NULL,
    branch_id CHAR(36) NOT NULL,
    position VARCHAR(40) NOT NULL DEFAULT 'Barista',
    employment_type VARCHAR(12) NOT NULL DEFAULT 'full_time',
    hire_date DATE NOT NULL,
    termination_date DATE NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'active',
    base_salary_iqd INT NOT NULL DEFAULT 0,
    hourly_rate_iqd INT NULL,
    pays_in_cash TINYINT NOT NULL DEFAULT 1,
    bank_details VARCHAR(200) NULL,
    national_id_ref VARCHAR(60) NULL,
    phone VARCHAR(20) NULL,
    emergency_contact VARCHAR(200) NULL,
    user_id CHAR(36) NULL,
    deduct_staff_consumption TINYINT NOT NULL DEFAULT 0,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_employee_number UNIQUE (employee_number),
    CONSTRAINT fk_emp_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_emp_type CHECK (employment_type IN ('full_time', 'part_time', 'hourly', 'temporary')),
    CONSTRAINT ck_emp_status CHECK (status IN ('active', 'terminated'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE employee_allowances (
    id CHAR(36) NOT NULL PRIMARY KEY,
    employee_id CHAR(36) NOT NULL,
    name VARCHAR(60) NOT NULL,
    amount_iqd INT NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_ea_employee FOREIGN KEY (employee_id) REFERENCES employees (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional PIN time-clock on the branch terminal (doc 08 §4, contract 1.7.0).
CREATE TABLE time_entries (
    id CHAR(36) NOT NULL PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    employee_id CHAR(36) NULL,
    branch_id CHAR(36) NOT NULL,
    business_day DATE NOT NULL,
    direction VARCHAR(3) NOT NULL,
    at_utc DATETIME NOT NULL,
    source_event_id VARCHAR(64) NOT NULL,
    applied_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_te_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_te_dir CHECK (direction IN ('in', 'out'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_te_day ON time_entries (branch_id, business_day);
CREATE INDEX ix_te_user ON time_entries (user_id, business_day);

-- Advances (سلفة) — doc 08 §5: the most common payroll dispute, modelled properly.
CREATE TABLE advances (
    id CHAR(36) NOT NULL PRIMARY KEY,
    employee_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    granted_on DATE NOT NULL,
    reason VARCHAR(200) NULL,
    repayment_mode VARCHAR(20) NOT NULL DEFAULT 'single_deduction',
    instalments INT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'outstanding',
    granted_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    till_movement_id CHAR(36) NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_adv_employee FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT ck_adv_mode CHECK (repayment_mode IN ('single_deduction', 'instalments')),
    CONSTRAINT ck_adv_status CHECK (status IN ('outstanding', 'partially_repaid', 'repaid', 'written_off'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payroll_runs (
    id CHAR(36) NOT NULL PRIMARY KEY,
    run_number VARCHAR(30) NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    branch_id CHAR(36) NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'draft',
    created_by_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    calculated_at_utc DATETIME NULL,
    approved_at_utc DATETIME NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_payroll_number UNIQUE (run_number),
    CONSTRAINT ck_pr_status CHECK (status IN ('draft', 'calculated', 'approved', 'paid'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payroll_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    run_id CHAR(36) NOT NULL,
    employee_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    base_iqd INT NOT NULL DEFAULT 0,
    allowances_iqd INT NOT NULL DEFAULT 0,
    overtime_iqd INT NOT NULL DEFAULT 0,
    bonus_iqd INT NOT NULL DEFAULT 0,
    gross_iqd INT NOT NULL DEFAULT 0,
    advance_deduction_iqd INT NOT NULL DEFAULT 0,
    consumption_deduction_iqd INT NOT NULL DEFAULT 0,
    other_deductions_iqd INT NOT NULL DEFAULT 0,
    net_iqd INT NOT NULL DEFAULT 0,
    components_json TEXT NULL,
    payment_status VARCHAR(10) NOT NULL DEFAULT 'pending',
    paid_at_utc DATETIME NULL,
    paid_by_user_id CHAR(36) NULL,
    payment_ref VARCHAR(100) NULL,
    CONSTRAINT fk_pl_run FOREIGN KEY (run_id) REFERENCES payroll_runs (id),
    CONSTRAINT fk_pl_employee FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT ck_pl_pay CHECK (payment_status IN ('pending', 'paid'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_pl_run ON payroll_lines (run_id);

CREATE TABLE advance_repayments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    advance_id CHAR(36) NOT NULL,
    payroll_line_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    CONSTRAINT fk_ar_advance FOREIGN KEY (advance_id) REFERENCES advances (id),
    CONSTRAINT fk_ar_line FOREIGN KEY (payroll_line_id) REFERENCES payroll_lines (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Supplier payables (doc 08 §6): invoices from GRNs, three-way matched.
CREATE TABLE supplier_invoices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    supplier_invoice_ref VARCHAR(60) NOT NULL,
    supplier_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status VARCHAR(12) NOT NULL DEFAULT 'draft',
    match_ordered_iqd INT NULL,
    match_received_iqd INT NULL,
    match_note VARCHAR(200) 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 fk_si_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_si_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_si_status CHECK (status IN ('draft', 'matched', 'disputed', 'approved', 'paid'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supplier_invoice_grns (
    id CHAR(36) NOT NULL PRIMARY KEY,
    supplier_invoice_id CHAR(36) NOT NULL,
    grn_id CHAR(36) NOT NULL,
    CONSTRAINT uq_sig UNIQUE (supplier_invoice_id, grn_id),
    CONSTRAINT fk_sig_invoice FOREIGN KEY (supplier_invoice_id) REFERENCES supplier_invoices (id),
    CONSTRAINT fk_sig_grn FOREIGN KEY (grn_id) REFERENCES goods_receipts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supplier_payments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    supplier_invoice_id CHAR(36) NOT NULL,
    amount_iqd INT NOT NULL,
    method VARCHAR(15) NOT NULL DEFAULT 'cash_from_safe',
    reference VARCHAR(100) NULL,
    paid_on DATE NOT NULL,
    paid_by_user_id CHAR(36) NOT NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_sp_invoice FOREIGN KEY (supplier_invoice_id) REFERENCES supplier_invoices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
