USE `kiosk_db`;

-- Milestone 4D.0B: POS/table payment-queue pricing shadow storage.
--
-- This additive migration stores the immutable unified-pricing comparison
-- made when a POS/NETS payment is initiated. It does not change what the
-- terminal charges, rewrite historical payment_queue rows, or modify any
-- existing bill, order, payment, loyalty, receipt, or report value.
--
-- IMPORTANT
-- MySQL commits DDL statements implicitly. Export a database backup first.
-- Run PRE-FLIGHT by itself and confirm every result is 0 before MIGRATION.

-- =========================================================================
-- PRE-FLIGHT -- every result 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 COUNT(*) AS `existing_payment_queue_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `COLUMN_NAME` IN (
      'pricing_version',
      'items_subtotal',
      'item_discount_amount',
      'promotion_discount_amount',
      'reward_discount_amount',
      'flexible_points_amount',
      'service_charge_amount',
      'gst_amount',
      'grand_total',
      'amount_payable',
      'pricing_snapshot_json',
      'pricing_snapshot_sha256',
      'legacy_amount',
      'pricing_difference'
  );

SELECT COUNT(*) AS `existing_payment_queue_pricing_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` = 'idx_payment_queue_pricing_snapshot_sha256';

-- Informational only: record the number of historical rows that must remain
-- present and receive NULL in every new nullable pricing column.
SELECT COUNT(*) AS `historical_payment_queue_row_count`
FROM `kiosk_db`.`payment_queue`;

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

ALTER TABLE `kiosk_db`.`payment_queue`
    ADD COLUMN `pricing_version` varchar(32) DEFAULT NULL,
    ADD COLUMN `items_subtotal` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `item_discount_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `promotion_discount_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `reward_discount_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `flexible_points_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `service_charge_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `gst_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `grand_total` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `amount_payable` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `pricing_snapshot_json` json DEFAULT NULL,
    ADD COLUMN `pricing_snapshot_sha256` char(64)
        CHARACTER SET ascii
        COLLATE ascii_bin
        DEFAULT NULL,
    ADD COLUMN `legacy_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `pricing_difference` decimal(12,2) DEFAULT NULL,
    ADD KEY `idx_payment_queue_pricing_snapshot_sha256`
        (`pricing_snapshot_sha256`);

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

SELECT 14 - COUNT(*) AS `missing_payment_queue_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `COLUMN_NAME` IN (
      'pricing_version',
      'items_subtotal',
      'item_discount_amount',
      'promotion_discount_amount',
      'reward_discount_amount',
      'flexible_points_amount',
      'service_charge_amount',
      'gst_amount',
      'grand_total',
      'amount_payable',
      'pricing_snapshot_json',
      'pricing_snapshot_sha256',
      'legacy_amount',
      'pricing_difference'
  );

SELECT 1 - COUNT(*) AS `missing_payment_queue_pricing_index_problem_count`
FROM information_schema.`STATISTICS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'payment_queue'
  AND `INDEX_NAME` = 'idx_payment_queue_pricing_snapshot_sha256'
  AND `COLUMN_NAME` = 'pricing_snapshot_sha256';

-- Existing rows must remain unversioned and without a pricing comparison.
-- New shadow-mode rows are expected to populate these fields later.
SELECT COUNT(*) AS `historical_payment_queue_pricing_rewrite_problem_count`
FROM `kiosk_db`.`payment_queue`
WHERE `pricing_version` IS NOT NULL
   OR `items_subtotal` IS NOT NULL
   OR `item_discount_amount` IS NOT NULL
   OR `promotion_discount_amount` IS NOT NULL
   OR `reward_discount_amount` IS NOT NULL
   OR `flexible_points_amount` IS NOT NULL
   OR `service_charge_amount` IS NOT NULL
   OR `gst_amount` IS NOT NULL
   OR `grand_total` IS NOT NULL
   OR `amount_payable` IS NOT NULL
   OR `pricing_snapshot_json` IS NOT NULL
   OR `pricing_snapshot_sha256` IS NOT NULL
   OR `legacy_amount` IS NOT NULL
   OR `pricing_difference` IS NOT NULL;

-- Informational schema review.
SHOW COLUMNS FROM `kiosk_db`.`payment_queue`;
