USE `kiosk_db`;

-- POS loyalty identity snapshot and redemption idempotency.
--
-- This migration prepares terminal and manual POS payments to carry the exact
-- member, programme and redemption identity that existed before payment.
-- It does not backfill historical payment_queue rows, finalize a redemption,
-- insert a loyalty ledger row, or change any member balance.
--
-- IMPORTANT
-- MySQL commits DDL statements implicitly. Take a database backup first.
-- Run the PRE-FLIGHT section by itself and confirm every problem count is 0
-- before running the MIGRATION section.

-- =========================================================================
-- PRE-FLIGHT -- every result whose name ends in problem_count must be 0
-- =========================================================================

SELECT 1 - COUNT(*) AS `missing_payment_queue_table_problem_count`
FROM information_schema.`TABLES`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `TABLE_TYPE` = 'BASE TABLE';

SELECT 1 - COUNT(*) AS `missing_loyalty_ledger_table_problem_count`
FROM information_schema.`TABLES`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'loyalty_points_ledger'
  AND `TABLE_TYPE` = 'BASE TABLE';

SELECT COUNT(*) AS `existing_payment_queue_loyalty_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `COLUMN_NAME` IN (
      'phone',
      'member_id',
      'loyalty_program_id',
      'loyalty_program_member_id',
      'member_name_snapshot',
      'member_tier_id',
      'loyalty_redemption_id',
      'loyalty_attempt_guard',
      'loyalty_redeem_amount',
      'loyalty_redeem_points'
  );

SELECT COUNT(*) AS `existing_payment_queue_loyalty_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` IN (
      'idx_payment_queue_loyalty_program_member',
      'idx_payment_queue_loyalty_member',
      'uq_payment_queue_loyalty_attempt'
  );

SELECT COUNT(*) AS `existing_payment_queue_loyalty_constraint_problem_count`
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE `CONSTRAINT_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `CONSTRAINT_NAME` IN (
      'fk_payment_queue_loyalty_member',
      'fk_payment_queue_loyalty_program_member',
      'fk_payment_queue_loyalty_tier',
      'fk_payment_queue_loyalty_redemption'
  );

SELECT COUNT(*) AS `existing_redeemed_guard_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'loyalty_points_ledger'
  AND `COLUMN_NAME` = 'redeemed_invoice_guard';

SELECT COUNT(*) AS `existing_redeemed_guard_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'loyalty_points_ledger'
  AND `INDEX_NAME` = 'uq_loyalty_redeemed_invoice_member';

-- Existing duplicate rows would prevent the new unique guard from being
-- installed. A result above 0 must be investigated rather than deleted.
SELECT COUNT(*) AS `duplicate_redeemed_ledger_problem_count`
FROM (
    SELECT
        `program_id`,
        `program_member_id`,
        `invoice_no`
    FROM `kiosk_db`.`loyalty_points_ledger`
    WHERE `change_type` = 'Redeemed'
      AND `program_member_id` IS NOT NULL
      AND `invoice_no` IS NOT NULL
      AND TRIM(`invoice_no`) <> ''
    GROUP BY
        `program_id`,
        `program_member_id`,
        `invoice_no`
    HAVING COUNT(*) > 1
) AS `duplicates`;

SELECT COUNT(*) AS `orphaned_program_member_problem_count`
FROM `kiosk_db`.`loyalty_program_members` AS `lpm`
LEFT JOIN `kiosk_db`.`loyalty_members` AS `lm`
    ON `lm`.`id` = `lpm`.`member_id`
LEFT JOIN `kiosk_db`.`loyalty_programs` AS `lp`
    ON `lp`.`id` = `lpm`.`program_id`
WHERE `lm`.`id` IS NULL
   OR `lp`.`id` IS NULL;

SELECT COUNT(*) AS `orphaned_redemption_member_problem_count`
FROM `kiosk_db`.`table_redemptions` AS `tr`
LEFT JOIN `kiosk_db`.`loyalty_members` AS `lm`
    ON `lm`.`id` = `tr`.`member_id`
WHERE `lm`.`id` IS NULL;

-- Informational only. These rows must remain present with NULL in every new
-- nullable payment_queue loyalty snapshot column.
SELECT COUNT(*) AS `historical_payment_queue_row_count`
FROM `kiosk_db`.`payment_queue`;

-- =========================================================================
-- MIGRATION -- run only after every pre-flight problem count is 0
-- =========================================================================

ALTER TABLE `kiosk_db`.`payment_queue`
    ADD COLUMN `phone` varchar(32) DEFAULT NULL
        AFTER `pricing_difference`,
    ADD COLUMN `member_id` int DEFAULT NULL
        AFTER `phone`,
    ADD COLUMN `loyalty_program_id` int DEFAULT NULL
        AFTER `member_id`,
    ADD COLUMN `loyalty_program_member_id` bigint UNSIGNED DEFAULT NULL
        AFTER `loyalty_program_id`,
    ADD COLUMN `member_name_snapshot` varchar(255) DEFAULT NULL
        AFTER `loyalty_program_member_id`,
    ADD COLUMN `member_tier_id` int DEFAULT NULL
        AFTER `member_name_snapshot`,
    ADD COLUMN `loyalty_redemption_id` int DEFAULT NULL
        AFTER `member_tier_id`,
    ADD COLUMN `loyalty_attempt_guard` tinyint
        GENERATED ALWAYS AS (
            CASE
                WHEN `loyalty_redemption_id` IS NOT NULL
                 AND `status` IN (
                     'pending',
                     'android_pending',
                     'unknown',
                     'processed',
                     'finalized'
                 )
                THEN 1
                ELSE NULL
            END
        ) STORED
        AFTER `loyalty_redemption_id`,
    ADD COLUMN `loyalty_redeem_amount` decimal(12,2) DEFAULT NULL
        AFTER `loyalty_attempt_guard`,
    ADD COLUMN `loyalty_redeem_points` int DEFAULT NULL
        AFTER `loyalty_redeem_amount`,

    ADD KEY `idx_payment_queue_loyalty_program_member`
        (`loyalty_program_id`, `loyalty_program_member_id`),
    ADD KEY `idx_payment_queue_loyalty_member`
        (`member_id`),
    ADD KEY `idx_payment_queue_loyalty_redemption_fk`
        (`loyalty_redemption_id`),
    ADD UNIQUE KEY `uq_payment_queue_loyalty_attempt`
        (`loyalty_redemption_id`, `loyalty_attempt_guard`),

    ADD CONSTRAINT `fk_payment_queue_loyalty_member`
        FOREIGN KEY (`member_id`)
        REFERENCES `kiosk_db`.`loyalty_members` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_payment_queue_loyalty_program_member`
        FOREIGN KEY (`loyalty_program_id`, `loyalty_program_member_id`)
        REFERENCES `kiosk_db`.`loyalty_program_members`
            (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_payment_queue_loyalty_tier`
        FOREIGN KEY (`loyalty_program_id`, `member_tier_id`)
        REFERENCES `kiosk_db`.`loyalty_tiers`
            (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_payment_queue_loyalty_redemption`
        FOREIGN KEY (`loyalty_redemption_id`)
        REFERENCES `kiosk_db`.`table_redemptions` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

ALTER TABLE `kiosk_db`.`loyalty_points_ledger`
    ADD COLUMN `redeemed_invoice_guard` tinyint
        GENERATED ALWAYS AS (
            CASE
                WHEN `change_type` = 'Redeemed' THEN 1
                ELSE NULL
            END
        ) STORED
        AFTER `change_type`,
    ADD UNIQUE KEY `uq_loyalty_redeemed_invoice_member`
        (
            `program_id`,
            `program_member_id`,
            `invoice_no`,
            `redeemed_invoice_guard`
        );

-- =========================================================================
-- POST-MIGRATION VERIFICATION -- every result must be 0
-- =========================================================================

SELECT 10 - COUNT(*) AS `missing_payment_queue_loyalty_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `COLUMN_NAME` IN (
      'phone',
      'member_id',
      'loyalty_program_id',
      'loyalty_program_member_id',
      'member_name_snapshot',
      'member_tier_id',
      'loyalty_redemption_id',
      'loyalty_attempt_guard',
      'loyalty_redeem_amount',
      'loyalty_redeem_points'
  );

SELECT 4 - COUNT(DISTINCT `INDEX_NAME`)
    AS `missing_payment_queue_loyalty_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` IN (
      'idx_payment_queue_loyalty_program_member',
      'idx_payment_queue_loyalty_member',
      'idx_payment_queue_loyalty_redemption_fk',
      'uq_payment_queue_loyalty_attempt'
  );

SELECT 4 - COUNT(*) AS `missing_payment_queue_loyalty_constraint_problem_count`
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE `CONSTRAINT_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `CONSTRAINT_NAME` IN (
      'fk_payment_queue_loyalty_member',
      'fk_payment_queue_loyalty_program_member',
      'fk_payment_queue_loyalty_tier',
      'fk_payment_queue_loyalty_redemption'
  )
  AND `CONSTRAINT_TYPE` = 'FOREIGN KEY';

SELECT 1 - COUNT(*) AS `missing_redeemed_guard_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'loyalty_points_ledger'
  AND `COLUMN_NAME` = 'redeemed_invoice_guard'
  AND `EXTRA` LIKE '%STORED GENERATED%';

SELECT 1 - COUNT(DISTINCT `INDEX_NAME`)
    AS `missing_redeemed_guard_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'loyalty_points_ledger'
  AND `INDEX_NAME` = 'uq_loyalty_redeemed_invoice_member'
  AND `NON_UNIQUE` = 0;

-- The migration must not populate any historical payment identity snapshot.
SELECT COUNT(*) AS `historical_payment_queue_loyalty_rewrite_problem_count`
FROM `kiosk_db`.`payment_queue`
WHERE `phone` IS NOT NULL
   OR `member_id` IS NOT NULL
   OR `loyalty_program_id` IS NOT NULL
   OR `loyalty_program_member_id` IS NOT NULL
   OR `member_name_snapshot` IS NOT NULL
   OR `member_tier_id` IS NOT NULL
   OR `loyalty_redemption_id` IS NOT NULL
   OR `loyalty_redeem_amount` IS NOT NULL
   OR `loyalty_redeem_points` IS NOT NULL;

SELECT COUNT(*) AS `duplicate_redeemed_ledger_problem_count`
FROM (
    SELECT
        `program_id`,
        `program_member_id`,
        `invoice_no`
    FROM `kiosk_db`.`loyalty_points_ledger`
    WHERE `change_type` = 'Redeemed'
      AND `program_member_id` IS NOT NULL
      AND `invoice_no` IS NOT NULL
      AND TRIM(`invoice_no`) <> ''
    GROUP BY
        `program_id`,
        `program_member_id`,
        `invoice_no`
    HAVING COUNT(*) > 1
) AS `duplicates`;
