USE `kiosk_db`;

-- POS redemption-attempt lifecycle repair.
--
-- Finalized table_redemptions rows are immutable payment history because
-- payment_queue.loyalty_redemption_id points to the exact row submitted to a
-- terminal. The former unique keys allowed only one row per store/table and
-- caused apply_redemption.php to revive a finalized row for a later bill.
--
-- This migration:
--   1. restores any finalized-queue redemption from its immutable snapshot;
--   2. permits unlimited finalized history rows; and
--   3. permits at most one pending redemption per store/table.
--
-- It does not change a loyalty balance or insert/delete a ledger row.
--
-- IMPORTANT
-- MySQL commits DDL statements implicitly. Take a fresh backup first.
-- Run PRE-FLIGHT by itself. All problem_count results must be 0.

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

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

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 COUNT(*) AS `existing_pending_guard_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'table_redemptions'
  AND `COLUMN_NAME` = 'pending_redemption_guard';

SELECT COUNT(*) AS `existing_pending_guard_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'table_redemptions'
  AND `INDEX_NAME` = 'uq_table_redemptions_pending';

SELECT 3 - COUNT(DISTINCT `INDEX_NAME`)
    AS `missing_legacy_redemption_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'table_redemptions'
  AND `INDEX_NAME` IN (
      'uniq_store_table',
      'uniq_table_redeem',
      'uniq_active'
  );

SELECT COUNT(*) AS `finalized_queue_missing_redemption_problem_count`
FROM `kiosk_db`.`payment_queue` AS `pq`
LEFT JOIN `kiosk_db`.`table_redemptions` AS `tr`
    ON `tr`.`id` = `pq`.`loyalty_redemption_id`
WHERE `pq`.`status` = 'finalized'
  AND `pq`.`loyalty_redemption_id` IS NOT NULL
  AND `tr`.`id` IS NULL;

SELECT COUNT(*) AS `finalized_queue_incomplete_snapshot_problem_count`
FROM `kiosk_db`.`payment_queue` AS `pq`
WHERE `pq`.`status` = 'finalized'
  AND `pq`.`loyalty_redemption_id` IS NOT NULL
  AND (
      `pq`.`loyalty_redeem_amount` IS NULL
      OR `pq`.`loyalty_redeem_points` IS NULL
      OR `pq`.`loyalty_redeem_amount` <= 0
      OR `pq`.`loyalty_redeem_points` <= 0
  );

SELECT COUNT(*) AS `finalized_queue_missing_invoice_problem_count`
FROM `kiosk_db`.`payment_queue` AS `pq`
LEFT JOIN `kiosk_db`.`counter_bills` AS `cb`
    ON `cb`.`payment_queue_id` = `pq`.`id`
WHERE `pq`.`status` = 'finalized'
  AND `pq`.`loyalty_redemption_id` IS NOT NULL
  AND (
      `cb`.`invoice_no` IS NULL
      OR TRIM(`cb`.`invoice_no`) = ''
  );

-- Informational: these rows will be restored from payment_queue/counter_bills.
-- The reported count may be above zero; on the reported test database it is 1.
SELECT COUNT(*) AS `finalized_redemption_rows_to_restore`
FROM `kiosk_db`.`payment_queue` AS `pq`
INNER JOIN `kiosk_db`.`table_redemptions` AS `tr`
    ON `tr`.`id` = `pq`.`loyalty_redemption_id`
INNER JOIN `kiosk_db`.`counter_bills` AS `cb`
    ON `cb`.`payment_queue_id` = `pq`.`id`
WHERE `pq`.`status` = 'finalized'
  AND (
      `tr`.`finalized` <> 1
      OR NOT (`tr`.`invoice_no` <=> `cb`.`invoice_no`)
      OR `tr`.`redeem_amount` <> `pq`.`loyalty_redeem_amount`
      OR `tr`.`points_used` <> `pq`.`loyalty_redeem_points`
  );

-- =========================================================================
-- DATA REPAIR
-- =========================================================================

-- payment_queue is the immutable pre-terminal snapshot; counter_bills supplies
-- the invoice produced by that finalized payment. This restores row 159 to
-- the exact 10-point redemption used by INV072990383.
UPDATE `kiosk_db`.`table_redemptions` AS `tr`
INNER JOIN `kiosk_db`.`payment_queue` AS `pq`
    ON `pq`.`loyalty_redemption_id` = `tr`.`id`
INNER JOIN `kiosk_db`.`counter_bills` AS `cb`
    ON `cb`.`payment_queue_id` = `pq`.`id`
SET
    `tr`.`redeem_amount` = `pq`.`loyalty_redeem_amount`,
    `tr`.`points_used` = `pq`.`loyalty_redeem_points`,
    `tr`.`finalized` = 1,
    `tr`.`invoice_no` = `cb`.`invoice_no`
WHERE `pq`.`status` = 'finalized';

-- =========================================================================
-- LIFECYCLE INDEX MIGRATION
-- =========================================================================

ALTER TABLE `kiosk_db`.`table_redemptions`
    DROP INDEX `uniq_store_table`,
    DROP INDEX `uniq_table_redeem`,
    DROP INDEX `uniq_active`,
    ADD COLUMN `pending_redemption_guard` tinyint
        GENERATED ALWAYS AS (
            CASE
                WHEN `finalized` = 0 THEN 1
                ELSE NULL
            END
        ) STORED
        AFTER `finalized`,
    ADD UNIQUE KEY `uq_table_redemptions_pending`
        (`store_id`, `table_name`, `pending_redemption_guard`);

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

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

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

SELECT COUNT(DISTINCT `INDEX_NAME`)
    AS `remaining_legacy_redemption_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'table_redemptions'
  AND `INDEX_NAME` IN (
      'uniq_store_table',
      'uniq_table_redeem',
      'uniq_active'
  );

SELECT COUNT(*) AS `finalized_queue_redemption_mismatch_problem_count`
FROM `kiosk_db`.`payment_queue` AS `pq`
INNER JOIN `kiosk_db`.`table_redemptions` AS `tr`
    ON `tr`.`id` = `pq`.`loyalty_redemption_id`
INNER JOIN `kiosk_db`.`counter_bills` AS `cb`
    ON `cb`.`payment_queue_id` = `pq`.`id`
WHERE `pq`.`status` = 'finalized'
  AND (
      `tr`.`finalized` <> 1
      OR NOT (`tr`.`invoice_no` <=> `cb`.`invoice_no`)
      OR `tr`.`redeem_amount` <> `pq`.`loyalty_redeem_amount`
      OR `tr`.`points_used` <> `pq`.`loyalty_redeem_points`
  );

SELECT COUNT(*) AS `multiple_pending_redemptions_problem_count`
FROM (
    SELECT `store_id`, `table_name`
    FROM `kiosk_db`.`table_redemptions`
    WHERE `finalized` = 0
    GROUP BY `store_id`, `table_name`
    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`);

