CREATE TABLE IF NOT EXISTS invoice_payment_allocations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    payment_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NOT NULL,

    -- How much of this payment applies to this specific invoice.
    -- Sum of allocations for an invoice drives its amount_paid/status —
    -- this table is the source of truth, monthly_invoices.amount_paid
    -- is a derived/cached value kept in sync when allocations change.
    allocated_amount DECIMAL(12,2) NOT NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_allocations_payment (payment_id),
    KEY idx_allocations_invoice (invoice_id),

    CONSTRAINT fk_allocations_payment
        FOREIGN KEY (payment_id) REFERENCES payments (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_allocations_invoice
        FOREIGN KEY (invoice_id) REFERENCES monthly_invoices (id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;