CREATE TABLE IF NOT EXISTS `{{prefix}}payment_item_allocations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` BIGINT UNSIGNED NOT NULL,
  `invoice_id` BIGINT UNSIGNED NOT NULL,
  `invoice_item_id` BIGINT UNSIGNED NULL,
  `sequence_no` INT UNSIGNED NOT NULL DEFAULT 1,
  `item_code_snapshot` VARCHAR(80) NULL,
  `description_snapshot` VARCHAR(255) NOT NULL,
  `quantity_snapshot` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit_price_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_net_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payable_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `previous_paid_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `amount_applied` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `remaining_after_snapshot` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_item_allocation_sequence` (`payment_id`,`sequence_no`),
  KEY `idx_payment_item_allocation_invoice` (`invoice_id`,`invoice_item_id`),
  KEY `idx_payment_item_allocation_payment` (`payment_id`,`id`),
  CONSTRAINT `{{prefix}}fk_payment_item_allocation_payment` FOREIGN KEY (`payment_id`) REFERENCES `{{prefix}}payments` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_payment_item_allocation_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `{{prefix}}invoices` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_payment_item_allocation_item` FOREIGN KEY (`invoice_item_id`) REFERENCES `{{prefix}}invoice_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
