USE `kiosk_db`;

-- POS payment-attempt retry lifecycle.
--
-- payment_queue rows are immutable audit evidence. A cancelled or explicitly
-- failed terminal attempt must remain in payment_queue, but it must not prevent
-- a new attempt from using the same still-pending table redemption.
--
-- The former unique key on loyalty_redemption_id treated failed and finalized
-- attempts identically. This migration replaces it with a generated lifecycle
-- guard:
--
--   pending/android_pending  -> blocks another attempt
--   unknown                  -> blocks retry (payment outcome is unresolved)
--   processed                -> blocks retry (provider approved)
--   finalized                -> blocks reuse (payment is complete)
--   failed                   -> does not block a new attempt
--
-- It does not delete a payment attempt, alter a bill, change a loyalty balance,
-- finalize a redemption, or insert/delete a loyalty ledger row.
--
-- IMPORTANT
-- MySQL commits DDL statements implicitly. Take a fresh backup first.
-- Run PRE-FLIGHT by itself. Every problem_count result must be 0.

-- =========================================================================
-- PRE-FLIGHT
-- =========================================================================

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

SELECT 2 - COUNT(*) AS `missing_retry_source_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `COLUMN_NAME` IN (
      'status',
      'loyalty_redemption_id'
  );

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

SELECT COUNT(*) AS `existing_loyalty_attempt_guard_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `COLUMN_NAME` = 'loyalty_attempt_guard';

SELECT COUNT(DISTINCT `INDEX_NAME`)
    AS `existing_loyalty_attempt_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` = 'uq_payment_queue_loyalty_attempt';

SELECT COUNT(DISTINCT `INDEX_NAME`)
    AS `existing_loyalty_redemption_fk_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` = 'idx_payment_queue_loyalty_redemption_fk';

-- More than one blocking attempt for a redemption would prevent installation
-- of the lifecycle-aware unique key. Investigate rather than deleting rows.
SELECT COUNT(*) AS `duplicate_blocking_attempt_problem_count`
FROM (
    SELECT `loyalty_redemption_id`
    FROM `kiosk_db`.`payment_queue`
    WHERE `loyalty_redemption_id` IS NOT NULL
      AND `status` IN (
          'pending',
          'android_pending',
          'unknown',
          'processed',
          'finalized'
      )
    GROUP BY `loyalty_redemption_id`
    HAVING COUNT(*) > 1
) AS `duplicates`;

-- Informational only. These failed attempts remain as audit evidence and will
-- receive NULL in the generated guard so a new attempt can be created.
SELECT COUNT(*) AS `failed_loyalty_attempt_row_count`
FROM `kiosk_db`.`payment_queue`
WHERE `loyalty_redemption_id` IS NOT NULL
  AND `status` = 'failed';

-- =========================================================================
-- MIGRATION
-- =========================================================================

-- The legacy unique key currently also supports the redemption foreign key.
-- Install a dedicated non-unique foreign-key index before replacing it.
ALTER TABLE `kiosk_db`.`payment_queue`
    ADD KEY `idx_payment_queue_loyalty_redemption_fk`
        (`loyalty_redemption_id`);

-- Some MySQL/InnoDB versions continue binding the existing foreign key to
-- the legacy unique index during an in-place table rebuild. Remove that
-- foreign key and replace the uniqueness rule in one atomic ALTER, then
-- immediately restore the same RESTRICT/CASCADE relationship using the
-- dedicated supporting index.
ALTER TABLE `kiosk_db`.`payment_queue`
    DROP FOREIGN KEY `fk_payment_queue_loyalty_redemption`,
    DROP INDEX `uq_payment_queue_loyalty_redemption`,
    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 UNIQUE KEY `uq_payment_queue_loyalty_attempt`
        (`loyalty_redemption_id`, `loyalty_attempt_guard`);

ALTER TABLE `kiosk_db`.`payment_queue`
    ADD CONSTRAINT `fk_payment_queue_loyalty_redemption`
        FOREIGN KEY (`loyalty_redemption_id`)
        REFERENCES `kiosk_db`.`table_redemptions` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

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

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

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

SELECT 1 - COUNT(DISTINCT `INDEX_NAME`)
    AS `missing_loyalty_redemption_fk_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` = 'idx_payment_queue_loyalty_redemption_fk'
  AND `NON_UNIQUE` = 1;

SELECT 1 - COUNT(*) AS `missing_loyalty_redemption_constraint_problem_count`
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE `CONSTRAINT_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `CONSTRAINT_NAME` = 'fk_payment_queue_loyalty_redemption'
  AND `CONSTRAINT_TYPE` = 'FOREIGN KEY';

SELECT COUNT(DISTINCT `INDEX_NAME`)
    AS `remaining_legacy_redemption_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` = 'uq_payment_queue_loyalty_redemption';

SELECT COUNT(*) AS `failed_attempt_blocking_retry_problem_count`
FROM `kiosk_db`.`payment_queue`
WHERE `loyalty_redemption_id` IS NOT NULL
  AND `status` = 'failed'
  AND `loyalty_attempt_guard` IS NOT NULL;

SELECT COUNT(*) AS `unresolved_attempt_not_blocking_problem_count`
FROM `kiosk_db`.`payment_queue`
WHERE `loyalty_redemption_id` IS NOT NULL
  AND `status` IN (
      'pending',
      'android_pending',
      'unknown',
      'processed',
      'finalized'
  )
  AND `loyalty_attempt_guard` <> 1;

SELECT COUNT(*) AS `duplicate_blocking_attempt_problem_count`
FROM (
    SELECT `loyalty_redemption_id`
    FROM `kiosk_db`.`payment_queue`
    WHERE `loyalty_attempt_guard` = 1
    GROUP BY `loyalty_redemption_id`
    HAVING COUNT(*) > 1
) AS `duplicates`;

SELECT COUNT(*) AS `loyalty_balance_mirror_problem_count`
FROM `kiosk_db`.`loyalty_program_members` AS `lpm`
INNER JOIN `kiosk_db`.`loyalty_members` AS `lm`
    ON `lm`.`id` = `lpm`.`member_id`
WHERE `lpm`.`points_balance` <> `lm`.`points_balance`
   OR NOT (`lpm`.`tier_id` <=> `lm`.`tier_id`);

-- Informational: the cancelled attempt remains, but its guard must be NULL.
SELECT
    `id`,
    `status`,
    `loyalty_redemption_id`,
    `loyalty_attempt_guard`,
    `payment_result`,
    `created_at`
FROM `kiosk_db`.`payment_queue`
WHERE `loyalty_redemption_id` IS NOT NULL
ORDER BY `id` DESC
LIMIT 10;
