CREATE TABLE finance_accounts_payable (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    loan_proposal_id INT UNSIGNED NULL,
    reference_type VARCHAR(40) NOT NULL DEFAULT 'manual',
    reference_id INT UNSIGNED NULL,
    description VARCHAR(255) NOT NULL,
    counterparty_name VARCHAR(120) NULL,
    category VARCHAR(40) NULL,
    payment_method VARCHAR(40) NULL,
    due_date DATE NOT NULL,
    expected_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(14,2) NULL,
    paid_at DATETIME NULL,
    status ENUM('expected','approved','paid','cancelled') NOT NULL DEFAULT 'expected',
    tags JSON NULL,
    metadata JSON NULL,
    created_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    CONSTRAINT fk_finance_ap_proposal FOREIGN KEY (loan_proposal_id) REFERENCES loan_proposals (id) ON DELETE SET NULL,
    CONSTRAINT fk_finance_ap_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_finance_ap_approved_by FOREIGN KEY (approved_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_finance_ap_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_finance_ap_due_date (due_date),
    INDEX idx_finance_ap_status (status),
    INDEX idx_finance_ap_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;