USE `kiosk_db`;

-- Milestone 4D.1: programme-scoped, read-only loyalty rewards catalogue.
--
-- This migration defines rewards and their store eligibility. It does not
-- claim rewards, reserve points, deduct points, issue coupons, or alter any
-- existing member balance, tier, payment, order, bill, or ledger row.
--
-- IMPORTANT
-- MySQL commits DDL statements implicitly. Take a database backup first.
-- Run the PRE-FLIGHT section by itself and confirm every result is 0 before
-- running the MIGRATION section.

-- =========================================================================
-- PRE-FLIGHT -- every result must be 0
-- =========================================================================

SELECT COUNT(*) AS `existing_rewards_table_problem_count`
FROM information_schema.`TABLES`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` IN (
      'loyalty_rewards',
      'loyalty_reward_stores'
  );

SELECT COUNT(*) AS `existing_menu_identity_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'menu_items'
  AND `INDEX_NAME` = 'uq_menu_item_store_identity';

SELECT COUNT(*) AS `orphaned_program_store_problem_count`
FROM `kiosk_db`.`loyalty_program_stores` AS `lps`
LEFT JOIN `kiosk_db`.`loyalty_programs` AS `lp`
    ON `lp`.`id` = `lps`.`program_id`
LEFT JOIN `kiosk_db`.`stores` AS `s`
    ON `s`.`id` = `lps`.`store_id`
WHERE `lp`.`id` IS NULL
   OR `s`.`id` IS NULL;

SELECT COUNT(*) AS `orphaned_program_tier_problem_count`
FROM `kiosk_db`.`loyalty_tiers` AS `lt`
LEFT JOIN `kiosk_db`.`loyalty_programs` AS `lp`
    ON `lp`.`id` = `lt`.`program_id`
WHERE `lp`.`id` IS NULL;

SELECT COUNT(*) AS `orphaned_menu_item_store_problem_count`
FROM `kiosk_db`.`menu_items` AS `mi`
LEFT JOIN `kiosk_db`.`stores` AS `s`
    ON `s`.`id` = `mi`.`store_id`
WHERE `mi`.`store_id` IS NOT NULL
  AND `s`.`id` IS NULL;

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

-- The composite identity allows a reward-store row to prove that its free
-- menu item belongs to the same store. `menu_items.id` is already unique, so
-- this adds referential structure without changing existing menu data.
ALTER TABLE `kiosk_db`.`menu_items`
    ADD UNIQUE KEY `uq_menu_item_store_identity`
        (`store_id`, `id`);

CREATE TABLE `kiosk_db`.`loyalty_rewards` (
    `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,

    `program_id` int NOT NULL,

    `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,
    `description` text,
    `terms` text,

    `reward_type` enum(
        'amount_discount',
        'percent_discount',
        'free_item'
    ) NOT NULL,

    `points_cost` int UNSIGNED NOT NULL,
    `reward_value` decimal(10,2) DEFAULT NULL,
    `minimum_spend` decimal(10,2) NOT NULL DEFAULT '0.00',

    `minimum_tier_id` int DEFAULT NULL,

    `card_image_url` varchar(500) DEFAULT NULL,
    `is_featured` tinyint(1) NOT NULL DEFAULT '0',
    `is_active` tinyint(1) NOT NULL DEFAULT '1',

    `starts_at` datetime DEFAULT NULL,
    `ends_at` datetime DEFAULT NULL,
    `sort_order` int UNSIGNED NOT NULL DEFAULT 0,

    `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_reward_public_id`
        (`public_id`),

    UNIQUE KEY `uq_loyalty_reward_program_code`
        (`program_id`, `code`),

    UNIQUE KEY `uq_loyalty_reward_program_identity`
        (`program_id`, `id`),

    KEY `idx_loyalty_reward_catalog`
        (
            `program_id`,
            `is_active`,
            `starts_at`,
            `ends_at`,
            `sort_order`
        ),

    KEY `idx_loyalty_reward_featured`
        (`program_id`, `is_featured`, `is_active`, `sort_order`),

    KEY `idx_loyalty_reward_minimum_tier`
        (`program_id`, `minimum_tier_id`),

    CONSTRAINT `fk_loyalty_reward_program`
        FOREIGN KEY (`program_id`)
        REFERENCES `kiosk_db`.`loyalty_programs` (`id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT `fk_loyalty_reward_minimum_tier`
        FOREIGN KEY (`program_id`, `minimum_tier_id`)
        REFERENCES `kiosk_db`.`loyalty_tiers` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT `chk_loyalty_reward_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_reward_code`
        CHECK (`code` REGEXP '^[A-Z0-9][A-Z0-9_-]{1,63}$'),

    CONSTRAINT `chk_loyalty_reward_points_cost`
        CHECK (`points_cost` > 0),

    CONSTRAINT `chk_loyalty_reward_minimum_spend`
        CHECK (`minimum_spend` >= 0),

    CONSTRAINT `chk_loyalty_reward_dates`
        CHECK (
            `starts_at` IS NULL
            OR `ends_at` IS NULL
            OR `ends_at` > `starts_at`
        ),

    -- Store and menu-item eligibility is held in loyalty_reward_stores.
    -- Keeping foreign-key columns out of this CHECK avoids MySQL error #3823.
    CONSTRAINT `chk_loyalty_reward_value_shape`
        CHECK (
            (
                `reward_type` = 'amount_discount'
                AND `reward_value` IS NOT NULL
                AND `reward_value` > 0
            )
            OR
            (
                `reward_type` = 'percent_discount'
                AND `reward_value` IS NOT NULL
                AND `reward_value` > 0
                AND `reward_value` <= 100
            )
            OR
            (
                `reward_type` = 'free_item'
                AND `reward_value` IS NULL
            )
        )
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

-- Rewards use explicit programme-store rows. There is no ambiguous
-- "no rows means every store" behaviour. An all-store reward receives one
-- row for each store participating in that loyalty programme.
--
-- For discount rewards, menu_item_id is NULL.
-- For free-item rewards, menu_item_id identifies the item supplied by that
-- store. The service/admin layer will enforce this reward-type rule; the
-- composite foreign key below enforces that the item belongs to the store.
CREATE TABLE `kiosk_db`.`loyalty_reward_stores` (
    `reward_id` bigint UNSIGNED NOT NULL,
    `program_id` int NOT NULL,
    `store_id` int NOT NULL,
    `menu_item_id` int DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`reward_id`, `store_id`),

    KEY `idx_loyalty_reward_store_reward_identity`
        (`program_id`, `reward_id`),

    KEY `idx_loyalty_reward_store_program_store`
        (`program_id`, `store_id`),

    KEY `idx_loyalty_reward_store_menu_identity`
        (`store_id`, `menu_item_id`),

    CONSTRAINT `fk_loyalty_reward_store_reward`
        FOREIGN KEY (`program_id`, `reward_id`)
        REFERENCES `kiosk_db`.`loyalty_rewards` (`program_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT `fk_loyalty_reward_store_program_store`
        FOREIGN KEY (`program_id`, `store_id`)
        REFERENCES `kiosk_db`.`loyalty_program_stores` (`program_id`, `store_id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT `fk_loyalty_reward_store_menu_item`
        FOREIGN KEY (`store_id`, `menu_item_id`)
        REFERENCES `kiosk_db`.`menu_items` (`store_id`, `id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

-- =========================================================================
-- POST-MIGRATION VERIFICATION
-- =========================================================================

SHOW COLUMNS FROM `kiosk_db`.`loyalty_rewards`;
SHOW COLUMNS FROM `kiosk_db`.`loyalty_reward_stores`;

SELECT COUNT(*) AS `reward_row_count`
FROM `kiosk_db`.`loyalty_rewards`;

SELECT COUNT(*) AS `reward_store_row_count`
FROM `kiosk_db`.`loyalty_reward_stores`;

SELECT COUNT(*) AS `missing_required_constraint_problem_count`
FROM (
    SELECT 'fk_loyalty_reward_program' AS `constraint_name`
    UNION ALL
    SELECT 'fk_loyalty_reward_minimum_tier'
    UNION ALL
    SELECT 'fk_loyalty_reward_store_reward'
    UNION ALL
    SELECT 'fk_loyalty_reward_store_program_store'
    UNION ALL
    SELECT 'fk_loyalty_reward_store_menu_item'
) AS `required`
LEFT JOIN information_schema.`REFERENTIAL_CONSTRAINTS` AS `rc`
    ON `rc`.`CONSTRAINT_SCHEMA` = 'kiosk_db'
   AND `rc`.`CONSTRAINT_NAME` = `required`.`constraint_name`
WHERE `rc`.`CONSTRAINT_NAME` IS NULL;

SELECT COUNT(*) AS `missing_menu_identity_index_problem_count`
FROM (
    SELECT 1
) AS `guard`
WHERE NOT EXISTS (
    SELECT 1
    FROM information_schema.`STATISTICS`
    WHERE `TABLE_SCHEMA` = 'kiosk_db'
      AND `TABLE_NAME` = 'menu_items'
      AND `INDEX_NAME` = 'uq_menu_item_store_identity'
      AND `NON_UNIQUE` = 0
);

SELECT COUNT(*) AS `legacy_mirror_mismatch_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`);

SELECT COUNT(*) AS `ledger_rows_missing_program_snapshot_count`
FROM `kiosk_db`.`loyalty_points_ledger`
WHERE `program_id` IS NULL
   OR `program_member_id` IS NULL;

SELECT COUNT(*) AS `invalid_reward_tier_problem_count`
FROM `kiosk_db`.`loyalty_rewards` AS `lr`
LEFT JOIN `kiosk_db`.`loyalty_tiers` AS `lt`
    ON `lt`.`program_id` = `lr`.`program_id`
   AND `lt`.`id` = `lr`.`minimum_tier_id`
WHERE `lr`.`minimum_tier_id` IS NOT NULL
  AND `lt`.`id` IS NULL;

SELECT COUNT(*) AS `invalid_reward_store_problem_count`
FROM `kiosk_db`.`loyalty_reward_stores` AS `lrs`
LEFT JOIN `kiosk_db`.`loyalty_program_stores` AS `lps`
    ON `lps`.`program_id` = `lrs`.`program_id`
   AND `lps`.`store_id` = `lrs`.`store_id`
WHERE `lps`.`store_id` IS NULL;

SELECT COUNT(*) AS `invalid_reward_menu_item_problem_count`
FROM `kiosk_db`.`loyalty_reward_stores` AS `lrs`
LEFT JOIN `kiosk_db`.`menu_items` AS `mi`
    ON `mi`.`store_id` = `lrs`.`store_id`
   AND `mi`.`id` = `lrs`.`menu_item_id`
WHERE `lrs`.`menu_item_id` IS NOT NULL
  AND `mi`.`id` IS NULL;
