CREATE TABLE IF NOT EXISTS supplier_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    owner_user_id INT NOT NULL,
    supplier_invoice_number VARCHAR(80) NOT NULL,
    supplier_name VARCHAR(190) NOT NULL,
    purchase_order_id INT NULL,
    purchase_order_number VARCHAR(80) NULL,
    job_id INT NULL,
    contract_id INT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'draft',
    match_status VARCHAR(50) NOT NULL DEFAULT 'unmatched',
    invoice_date DATE NULL,
    due_date DATE NULL,
    posted_at DATE NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'GBP',
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS supplier_invoice_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    supplier_invoice_id INT NOT NULL,
    purchase_order_line_id INT NULL,
    inventory_item_id INT NULL,
    description VARCHAR(190) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    line_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE INDEX idx_supplier_invoices_tenant_po_invoice ON supplier_invoices (tenant_id, purchase_order_id, invoice_date);
CREATE INDEX idx_supplier_invoices_tenant_job_invoice ON supplier_invoices (tenant_id, job_id, invoice_date);
CREATE INDEX idx_supplier_invoices_tenant_contract_invoice ON supplier_invoices (tenant_id, contract_id, invoice_date);
CREATE INDEX idx_supplier_invoices_tenant_status_invoice ON supplier_invoices (tenant_id, status, invoice_date);
CREATE INDEX idx_supplier_invoice_lines_tenant_invoice ON supplier_invoice_lines (tenant_id, supplier_invoice_id);