-- Step 23: Add item-level payment tracking tables
-- Enables split-bill functionality — payments can be linked to specific items and quantities

-- 1. New junction table: links session payments to the specific items they cover
CREATE TABLE IF NOT EXISTS `sessionItemPayments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sessionId` INT UNSIGNED NOT NULL,
  `vendorId` VARCHAR(50) NOT NULL,
  `paymentId` INT UNSIGNED NOT NULL,
  `sessionItemId` INT UNSIGNED NOT NULL,
  `quantity` SMALLINT UNSIGNED NOT NULL,

  KEY `idx_sipay_session` (`sessionId`),
  KEY `idx_sipay_payment` (`paymentId`),
  KEY `idx_sipay_item` (`sessionItemId`),
  KEY `idx_sipay_vendor` (`vendorId`),
  CONSTRAINT `fk_sipay_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 2. Fix receiptPayments PK to support multiple payments per receipt
--    Currently PK is (vendorId, receiptCode) which only allows 1 payment per receipt
--    Uses a procedure to be idempotent (safe to re-run if partially applied)
DROP PROCEDURE IF EXISTS _migrate_receiptPayments_pk;
DELIMITER //
CREATE PROCEDURE _migrate_receiptPayments_pk()
BEGIN
  -- receiptPayments
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'receiptPayments' AND COLUMN_NAME = 'id'
  ) THEN
    ALTER TABLE `receiptPayments` ADD INDEX `idx_rp_vendor` (`vendorId`);
    ALTER TABLE `receiptPayments` DROP PRIMARY KEY, ADD COLUMN `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
  END IF;

  -- _archive_receiptPayments
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '_archive_receiptPayments' AND COLUMN_NAME = 'id'
  ) THEN
    ALTER TABLE `_archive_receiptPayments` ADD INDEX `idx_arp_vendor` (`vendorId`);
    ALTER TABLE `_archive_receiptPayments` DROP PRIMARY KEY, ADD COLUMN `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY FIRST;
  END IF;
END//
DELIMITER ;
CALL _migrate_receiptPayments_pk();
DROP PROCEDURE IF EXISTS _migrate_receiptPayments_pk;

-- 3. New table: finalized copy of sessionItemPayments for receipts
CREATE TABLE IF NOT EXISTS `receiptItemPayments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vendorId` VARCHAR(50) NOT NULL,
  `receiptCode` VARCHAR(25) NOT NULL,
  `receiptPaymentId` INT UNSIGNED NOT NULL,
  `receiptItemId` INT UNSIGNED NOT NULL,
  `quantity` SMALLINT UNSIGNED NOT NULL,

  KEY `idx_ripay_receipt` (`receiptCode`),
  KEY `idx_ripay_payment` (`receiptPaymentId`),
  KEY `idx_ripay_vendor` (`vendorId`),
  CONSTRAINT `fk_ripay_vendor` FOREIGN KEY (`vendorId`) REFERENCES `vendors`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 4. Archive version of receiptItemPayments
CREATE TABLE IF NOT EXISTS `_archive_receiptItemPayments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vendorId` VARCHAR(50) NOT NULL,
  `receiptCode` VARCHAR(25) NOT NULL,
  `receiptPaymentId` INT UNSIGNED NOT NULL,
  `receiptItemId` INT UNSIGNED NOT NULL,
  `quantity` SMALLINT UNSIGNED NOT NULL,

  KEY `idx_aripay_receipt` (`receiptCode`),
  KEY `idx_aripay_vendor` (`vendorId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 5. Add selectedItems JSON column to stripePayments for split-bill Stripe payments
--    The webhook handler reads this to know which items the Stripe payment covers
ALTER TABLE `stripePayments`
  ADD COLUMN `selectedItems` TEXT DEFAULT NULL AFTER `receiptCode`;
