-- v1.30: Supplier Payment Allocations (link payments to purchase invoices)
-- Run once on your DB after v1.23 (purchases) and v1.29 (supplier payments).

CREATE TABLE IF NOT EXISTS supplier_payment_allocations (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  payment_id BIGINT UNSIGNED NOT NULL,
  purchase_invoice_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_spa_payment (payment_id),
  KEY idx_spa_invoice (purchase_invoice_id),
  CONSTRAINT fk_spa_payment FOREIGN KEY (payment_id) REFERENCES supplier_payments(id) ON DELETE CASCADE,
  CONSTRAINT fk_spa_invoice FOREIGN KEY (purchase_invoice_id) REFERENCES purchase_invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;