-- Milestone 4D.0A: loyalty programme foundation
--
-- Permanent schema migration for the existing kiosk_db database.
-- This migration does not enable rewards, redemption, or new earning rules.
-- It preserves the legacy loyalty_members balance/tier columns while creating
-- programme-scoped records that later code can adopt safely.
--
-- Current production mapping:
--   Programme: ChuanXi Rewards
--   Store:     stores.id = 2
--
-- IMPORTANT:
-- 1. Back up/export kiosk_db before applying this file.
-- 2. Run the preflight queries below by themselves first.
-- 3. Continue only when every *_problem_count result is 0 and
--    loyalty_settings_row_count is 1.
-- 4. Do not apply 20260727_mobile_loyalty_rewards_catalog.sql yet.

USE `kiosk_db`;

-- -------------------------------------------------------------------------
-- Preflight checks (run and review before executing the migration section)
-- -------------------------------------------------------------------------

SELECT COUNT(*) AS `missing_store_problem_count`
FROM (SELECT 1) AS `guard`
WHERE NOT EXISTS (
    SELECT 1
    FROM `stores`
    WHERE `id` = 2
);

SELECT COUNT(*) AS `loyalty_settings_row_count`
FROM `loyalty_settings`;

SELECT COUNT(*) AS `orphaned_member_tier_problem_count`
FROM `loyalty_members` AS `lm`
LEFT JOIN `loyalty_tiers` AS `lt`
    ON `lt`.`id` = `lm`.`tier_id`
WHERE `lm`.`tier_id` IS NOT NULL
  AND `lt`.`id` IS NULL;

SELECT COUNT(*) AS `orphaned_ledger_member_problem_count`
FROM `loyalty_points_ledger` AS `lpl`
LEFT JOIN `loyalty_members` AS `lm`
    ON `lm`.`id` = `lpl`.`member_id`
WHERE `lpl`.`member_id` IS NOT NULL
  AND `lm`.`id` IS NULL;

SELECT COUNT(*) AS `orphaned_ledger_store_problem_count`
FROM `loyalty_points_ledger` AS `lpl`
LEFT JOIN `stores` AS `s`
    ON `s`.`id` = `lpl`.`store_id`
WHERE `lpl`.`store_id` IS NOT NULL
  AND `s`.`id` IS NULL;

SELECT COUNT(*) AS `orphaned_counter_bill_member_problem_count`
FROM `counter_bills` AS `cb`
LEFT JOIN `loyalty_members` AS `lm`
    ON `lm`.`id` = `cb`.`member_id`
WHERE `cb`.`member_id` IS NOT NULL
  AND `lm`.`id` IS NULL;

-- -------------------------------------------------------------------------
-- Migration
--
-- MySQL commits DDL statements implicitly. The preflight and backup above
-- therefore matter: this is intentionally not presented as one transaction.
-- -------------------------------------------------------------------------

CREATE TABLE `loyalty_programs` (
    `id` int NOT NULL AUTO_INCREMENT,
    `public_id` char(36)
        CHARACTER SET ascii
        COLLATE ascii_bin
        NOT NULL,
    `code` varchar(64)
        CHARACTER SET ascii
        COLLATE ascii_bin
        NOT NULL,
    `name` varchar(120) NOT NULL,
    `currency_code` char(3)
        CHARACTER SET ascii
        COLLATE ascii_bin
        NOT NULL DEFAULT 'SGD',
    `is_active` tinyint(1) NOT NULL DEFAULT '1',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_loyalty_program_public_id` (`public_id`),
    UNIQUE KEY `uq_loyalty_program_code` (`code`),
    CONSTRAINT `chk_loyalty_program_public_id`
        CHECK (
            `public_id` REGEXP
            '^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'
        ),
    CONSTRAINT `chk_loyalty_program_currency`
        CHECK (`currency_code` REGEXP '^[A-Z]{3}$')
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `loyalty_programs` (
    `public_id`,
    `code`,
    `name`,
    `currency_code`,
    `is_active`
)
SELECT
    '2f7a2be4-e879-4d9d-8db5-8af35aa1c697',
    'CHUANXI',
    'ChuanXi Rewards',
    `s`.`currency_code`,
    1
FROM `stores` AS `s`
WHERE `s`.`id` = 2;

SET @ssn_loyalty_program_id := LAST_INSERT_ID();

CREATE TABLE `loyalty_program_stores` (
    `program_id` int NOT NULL,
    `store_id` int NOT NULL,
    `is_primary` tinyint(1) NOT NULL DEFAULT '0',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`program_id`, `store_id`),
    UNIQUE KEY `uq_loyalty_program_store` (`store_id`),
    CONSTRAINT `fk_loyalty_program_store_program`
        FOREIGN KEY (`program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT `fk_loyalty_program_store_store`
        FOREIGN KEY (`store_id`)
        REFERENCES `stores` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `loyalty_program_stores` (
    `program_id`,
    `store_id`,
    `is_primary`
)
VALUES (
    @ssn_loyalty_program_id,
    2,
    1
);

ALTER TABLE `loyalty_settings`
    ADD COLUMN `program_id` int DEFAULT NULL AFTER `id`;

UPDATE `loyalty_settings`
SET `program_id` = @ssn_loyalty_program_id;

ALTER TABLE `loyalty_settings`
    MODIFY COLUMN `program_id` int NOT NULL,
    ADD UNIQUE KEY `uq_loyalty_settings_program` (`program_id`),
    ADD CONSTRAINT `fk_loyalty_settings_program`
        FOREIGN KEY (`program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

ALTER TABLE `loyalty_tiers`
    ADD COLUMN `program_id` int DEFAULT NULL AFTER `id`;

UPDATE `loyalty_tiers`
SET `program_id` = @ssn_loyalty_program_id;

ALTER TABLE `loyalty_tiers`
    MODIFY COLUMN `program_id` int NOT NULL,
    DROP INDEX `uq_loyalty_tiers_rank`,
    ADD UNIQUE KEY `uq_loyalty_tier_program_rank`
        (`program_id`, `tier_rank`),
    ADD UNIQUE KEY `uq_loyalty_tier_program_id`
        (`program_id`, `id`),
    ADD CONSTRAINT `fk_loyalty_tier_program`
        FOREIGN KEY (`program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

CREATE TABLE `loyalty_program_members` (
    `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    `program_id` int NOT NULL,
    `member_id` int NOT NULL,
    `points_balance` int NOT NULL DEFAULT '0',
    `tier_id` int DEFAULT NULL,
    `joined_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_loyalty_program_member`
        (`program_id`, `member_id`),
    UNIQUE KEY `uq_loyalty_program_member_identity`
        (`program_id`, `id`),
    KEY `idx_loyalty_program_member_tier`
        (`program_id`, `tier_id`),
    CONSTRAINT `fk_loyalty_program_member_program`
        FOREIGN KEY (`program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT `fk_loyalty_program_member_member`
        FOREIGN KEY (`member_id`)
        REFERENCES `loyalty_members` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT `fk_loyalty_program_member_tier`
        FOREIGN KEY (`program_id`, `tier_id`)
        REFERENCES `loyalty_tiers` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CONSTRAINT `chk_loyalty_program_member_balance`
        CHECK (`points_balance` >= 0)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `loyalty_program_members` (
    `program_id`,
    `member_id`,
    `points_balance`,
    `tier_id`,
    `joined_at`
)
SELECT
    @ssn_loyalty_program_id,
    `lm`.`id`,
    `lm`.`points_balance`,
    `lm`.`tier_id`,
    COALESCE(`lm`.`created_at`, CURRENT_TIMESTAMP)
FROM `loyalty_members` AS `lm`
ORDER BY `lm`.`id`;

ALTER TABLE `loyalty_points_ledger`
    ADD COLUMN `program_id` int DEFAULT NULL AFTER `id`,
    ADD COLUMN `program_member_id` bigint UNSIGNED DEFAULT NULL
        AFTER `program_id`;

UPDATE `loyalty_points_ledger` AS `lpl`
LEFT JOIN `loyalty_program_members` AS `lpm`
    ON `lpm`.`program_id` = @ssn_loyalty_program_id
   AND `lpm`.`member_id` = `lpl`.`member_id`
SET
    `lpl`.`program_id` = @ssn_loyalty_program_id,
    `lpl`.`program_member_id` = `lpm`.`id`;

ALTER TABLE `loyalty_points_ledger`
    MODIFY COLUMN `program_id` int NOT NULL,
    ADD KEY `idx_loyalty_ledger_program_member`
        (`program_id`, `program_member_id`),
    ADD CONSTRAINT `fk_loyalty_ledger_program`
        FOREIGN KEY (`program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_loyalty_ledger_program_member`
        FOREIGN KEY (`program_id`, `program_member_id`)
        REFERENCES `loyalty_program_members` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

-- Durable programme/member snapshots on financial and fulfilment records.
-- Guest rows receive a programme when their store is enrolled, while their
-- programme-member value remains NULL.

ALTER TABLE `hitpay_payment_requests`
    ADD COLUMN `loyalty_program_id` int DEFAULT NULL
        AFTER `loyalty_member_id`,
    ADD COLUMN `loyalty_program_member_id` bigint UNSIGNED DEFAULT NULL
        AFTER `loyalty_program_id`;

UPDATE `hitpay_payment_requests` AS `hpr`
LEFT JOIN `loyalty_program_stores` AS `lps`
    ON `lps`.`store_id` = `hpr`.`store_id`
LEFT JOIN `loyalty_program_members` AS `lpm`
    ON `lpm`.`program_id` = `lps`.`program_id`
   AND `lpm`.`member_id` = `hpr`.`loyalty_member_id`
SET
    `hpr`.`loyalty_program_id` = `lps`.`program_id`,
    `hpr`.`loyalty_program_member_id` = `lpm`.`id`;

ALTER TABLE `hitpay_payment_requests`
    ADD KEY `idx_hitpay_loyalty_program_member`
        (`loyalty_program_id`, `loyalty_program_member_id`),
    ADD CONSTRAINT `fk_hitpay_loyalty_program`
        FOREIGN KEY (`loyalty_program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_hitpay_loyalty_program_member`
        FOREIGN KEY (`loyalty_program_id`, `loyalty_program_member_id`)
        REFERENCES `loyalty_program_members` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

ALTER TABLE `counter_bills`
    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`;

UPDATE `counter_bills` AS `cb`
LEFT JOIN `loyalty_program_stores` AS `lps`
    ON `lps`.`store_id` = `cb`.`store_id`
LEFT JOIN `loyalty_program_members` AS `lpm`
    ON `lpm`.`program_id` = `lps`.`program_id`
   AND `lpm`.`member_id` = `cb`.`member_id`
SET
    `cb`.`loyalty_program_id` = `lps`.`program_id`,
    `cb`.`loyalty_program_member_id` = `lpm`.`id`;

ALTER TABLE `counter_bills`
    ADD KEY `idx_counter_bill_loyalty_program_member`
        (`loyalty_program_id`, `loyalty_program_member_id`),
    ADD CONSTRAINT `fk_counter_bill_loyalty_program`
        FOREIGN KEY (`loyalty_program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_counter_bill_loyalty_program_member`
        FOREIGN KEY (`loyalty_program_id`, `loyalty_program_member_id`)
        REFERENCES `loyalty_program_members` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

ALTER TABLE `kitchen_orders`
    ADD COLUMN `loyalty_program_id` int DEFAULT NULL
        AFTER `loyalty_member_id`,
    ADD COLUMN `loyalty_program_member_id` bigint UNSIGNED DEFAULT NULL
        AFTER `loyalty_program_id`;

UPDATE `kitchen_orders` AS `ko`
LEFT JOIN `loyalty_program_stores` AS `lps`
    ON `lps`.`store_id` = `ko`.`store_id`
LEFT JOIN `loyalty_program_members` AS `lpm`
    ON `lpm`.`program_id` = `lps`.`program_id`
   AND `lpm`.`member_id` = `ko`.`loyalty_member_id`
SET
    `ko`.`loyalty_program_id` = `lps`.`program_id`,
    `ko`.`loyalty_program_member_id` = `lpm`.`id`;

ALTER TABLE `kitchen_orders`
    ADD KEY `idx_kitchen_loyalty_program_member`
        (`loyalty_program_id`, `loyalty_program_member_id`),
    ADD CONSTRAINT `fk_kitchen_loyalty_program`
        FOREIGN KEY (`loyalty_program_id`)
        REFERENCES `loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    ADD CONSTRAINT `fk_kitchen_loyalty_program_member`
        FOREIGN KEY (`loyalty_program_id`, `loyalty_program_member_id`)
        REFERENCES `loyalty_program_members` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE;

-- -------------------------------------------------------------------------
-- Post-migration verification
--
-- Expected:
-- - every *_problem_count is 0
-- - legacy_member_count equals programme_member_count
-- - legacy_points_total equals programme_points_total
-- - legacy tier/balance mismatch counts are 0
-- - the ChuanXi programme has store_id 2
-- -------------------------------------------------------------------------

SELECT
    `lp`.`id` AS `program_id`,
    `lp`.`code`,
    `lp`.`name`,
    `lp`.`currency_code`,
    `lps`.`store_id`,
    `lps`.`is_primary`
FROM `loyalty_programs` AS `lp`
INNER JOIN `loyalty_program_stores` AS `lps`
    ON `lps`.`program_id` = `lp`.`id`
WHERE `lp`.`code` = 'CHUANXI';

SELECT
    (SELECT COUNT(*) FROM `loyalty_members`)
        AS `legacy_member_count`,
    (SELECT COUNT(*)
     FROM `loyalty_program_members`
     WHERE `program_id` = @ssn_loyalty_program_id)
        AS `programme_member_count`,
    (SELECT COALESCE(SUM(`points_balance`), 0)
     FROM `loyalty_members`)
        AS `legacy_points_total`,
    (SELECT COALESCE(SUM(`points_balance`), 0)
     FROM `loyalty_program_members`
     WHERE `program_id` = @ssn_loyalty_program_id)
        AS `programme_points_total`;

SELECT COUNT(*) AS `member_balance_mismatch_problem_count`
FROM `loyalty_members` AS `lm`
INNER JOIN `loyalty_program_members` AS `lpm`
    ON `lpm`.`program_id` = @ssn_loyalty_program_id
   AND `lpm`.`member_id` = `lm`.`id`
WHERE `lpm`.`points_balance` <> `lm`.`points_balance`;

SELECT COUNT(*) AS `member_tier_mismatch_problem_count`
FROM `loyalty_members` AS `lm`
INNER JOIN `loyalty_program_members` AS `lpm`
    ON `lpm`.`program_id` = @ssn_loyalty_program_id
   AND `lpm`.`member_id` = `lm`.`id`
WHERE NOT (`lpm`.`tier_id` <=> `lm`.`tier_id`);

SELECT COUNT(*) AS `ledger_program_backfill_problem_count`
FROM `loyalty_points_ledger`
WHERE `program_id` <> @ssn_loyalty_program_id;

SELECT COUNT(*) AS `ledger_member_backfill_problem_count`
FROM `loyalty_points_ledger`
WHERE `member_id` IS NOT NULL
  AND `program_member_id` IS NULL;

SELECT
    (SELECT COUNT(*)
     FROM `hitpay_payment_requests`
     WHERE `store_id` = 2
       AND `loyalty_program_id` IS NULL)
        AS `hitpay_program_snapshot_problem_count`,
    (SELECT COUNT(*)
     FROM `counter_bills`
     WHERE `store_id` = 2
       AND `loyalty_program_id` IS NULL)
        AS `counter_bill_program_snapshot_problem_count`,
    (SELECT COUNT(*)
     FROM `kitchen_orders`
     WHERE `store_id` = 2
       AND `loyalty_program_id` IS NULL)
        AS `kitchen_order_program_snapshot_problem_count`;

SELECT
    (SELECT COUNT(*)
     FROM `hitpay_payment_requests`
     WHERE `loyalty_member_id` IS NOT NULL
       AND `loyalty_program_member_id` IS NULL)
        AS `hitpay_member_snapshot_problem_count`,
    (SELECT COUNT(*)
     FROM `counter_bills`
     WHERE `member_id` IS NOT NULL
       AND `loyalty_program_member_id` IS NULL)
        AS `counter_bill_member_snapshot_problem_count`,
    (SELECT COUNT(*)
     FROM `kitchen_orders`
     WHERE `loyalty_member_id` IS NOT NULL
       AND `loyalty_program_member_id` IS NULL)
        AS `kitchen_order_member_snapshot_problem_count`;
