-- StockFlow migration 004: opening balances and debt ledger entries.
--
-- The tables deliberately keep debit and credit as DECIMAL values.  A debt
-- entry is posted to one side only; reports derive the closing side in SQL.
-- Run after migrations 001, 002 and 003.

CREATE TABLE IF NOT EXISTS debt_opening_balances (
    opening_balance_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fiscal_year SMALLINT UNSIGNED NOT NULL,
    effective_date DATE NOT NULL,
    partner_type ENUM('CUSTOMER', 'SUPPLIER') NOT NULL,
    customer_id INT NULL,
    supplier_id INT NULL,
    category VARCHAR(32) NOT NULL,
    debit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    credit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    reference_code VARCHAR(100) NULL,
    note VARCHAR(500) NULL,
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    updated_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    cancelled_by INT NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    partner_key VARCHAR(80) GENERATED ALWAYS AS (
        CASE
            WHEN partner_type = 'CUSTOMER' THEN CONCAT('CUSTOMER:', customer_id)
            ELSE CONCAT('SUPPLIER:', supplier_id)
        END
    ) STORED,
    UNIQUE KEY uq_debt_opening_year_partner_category (fiscal_year, partner_key, category),
    KEY idx_debt_opening_partner_date (partner_type, customer_id, supplier_id, effective_date),
    KEY idx_debt_opening_status_date (status, effective_date),
    KEY idx_debt_opening_category (partner_type, category),
    CONSTRAINT fk_debt_opening_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_updated_by FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT fk_debt_opening_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT chk_debt_opening_partner CHECK (
        (partner_type = 'CUSTOMER' AND customer_id IS NOT NULL AND supplier_id IS NULL AND category IN ('TRADE_RECEIVABLE', 'RESERVE_CAPITAL', 'OTHER_RECEIVABLE', 'SHORTFALL')) OR
        (partner_type = 'SUPPLIER' AND supplier_id IS NOT NULL AND customer_id IS NULL AND category IN ('TRADE_PAYABLE', 'INTERNAL', 'EXTERNAL', 'OTHER_PAYABLE'))
    ),
    CONSTRAINT chk_debt_opening_year CHECK (fiscal_year BETWEEN 1900 AND 9999),
    CONSTRAINT chk_debt_opening_amounts CHECK (
        debit_amount >= 0 AND credit_amount >= 0 AND
        ((debit_amount > 0 AND credit_amount = 0) OR (credit_amount > 0 AND debit_amount = 0))
    ),
    CONSTRAINT chk_debt_opening_status_audit CHECK (
        (status <> 'CONFIRMED' OR (confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) AND
        (status <> 'CANCELLED' OR (cancelled_by IS NOT NULL AND cancelled_at IS NOT NULL))
    )
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS debt_ledger_entries (
    debt_entry_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_date DATE NOT NULL,
    partner_type ENUM('CUSTOMER', 'SUPPLIER') NOT NULL,
    customer_id INT NULL,
    supplier_id INT NULL,
    category VARCHAR(32) NOT NULL,
    debit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    credit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    entry_source ENUM('MANUAL', 'INVOICE', 'PAYMENT', 'ADJUSTMENT') NOT NULL DEFAULT 'MANUAL',
    reference_type VARCHAR(40) NULL,
    reference_id BIGINT UNSIGNED NULL,
    reference_code VARCHAR(100) NULL,
    description VARCHAR(500) NULL,
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    updated_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    cancelled_by INT NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    partner_key VARCHAR(80) GENERATED ALWAYS AS (
        CASE
            WHEN partner_type = 'CUSTOMER' THEN CONCAT('CUSTOMER:', customer_id)
            ELSE CONCAT('SUPPLIER:', supplier_id)
        END
    ) STORED,
    KEY idx_debt_ledger_partner_date (partner_type, customer_id, supplier_id, entry_date),
    KEY idx_debt_ledger_status_date (status, entry_date),
    KEY idx_debt_ledger_category (partner_type, category),
    KEY idx_debt_ledger_reference (reference_type, reference_id),
    CONSTRAINT fk_debt_ledger_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_updated_by FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT fk_debt_ledger_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT chk_debt_ledger_partner CHECK (
        (partner_type = 'CUSTOMER' AND customer_id IS NOT NULL AND supplier_id IS NULL AND category IN ('TRADE_RECEIVABLE', 'RESERVE_CAPITAL', 'OTHER_RECEIVABLE', 'SHORTFALL')) OR
        (partner_type = 'SUPPLIER' AND supplier_id IS NOT NULL AND customer_id IS NULL AND category IN ('TRADE_PAYABLE', 'INTERNAL', 'EXTERNAL', 'OTHER_PAYABLE'))
    ),
    CONSTRAINT chk_debt_ledger_amounts CHECK (
        debit_amount >= 0 AND credit_amount >= 0 AND
        ((debit_amount > 0 AND credit_amount = 0) OR (credit_amount > 0 AND debit_amount = 0))
    ),
    CONSTRAINT chk_debt_ledger_reference CHECK (reference_id IS NULL OR reference_type IS NOT NULL),
    CONSTRAINT chk_debt_ledger_status_audit CHECK (
        (status <> 'CONFIRMED' OR (confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) AND
        (status <> 'CANCELLED' OR (cancelled_by IS NOT NULL AND cancelled_at IS NOT NULL))
    )
) ENGINE=InnoDB;