USE `kiosk_db`;

-- Milestone 4D.0B: additive storage for a unified, immutable pricing result.
--
-- This migration does not change historical totals or activate the new
-- pricing engine. Existing checkout, HitPay, bill, receipt and report paths
-- remain untouched until a later, separately tested wiring step.
--
-- IMPORTANT
-- MySQL commits DDL implicitly. Export a backup before applying this file.
-- Run the PRE-FLIGHT section by itself first. Every result must be 0.

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

SELECT COUNT(*) AS `existing_hitpay_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'hitpay_payment_requests'
  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'
  );

SELECT COUNT(*) AS `existing_hitpay_item_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'hitpay_payment_request_items'
  AND `COLUMN_NAME` IN (
      'item_discount_amount',
      'promotion_discount_alloc',
      'reward_discount_alloc',
      'net_subtotal'
  );

SELECT COUNT(*) AS `existing_bill_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'counter_bills'
  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'
  );

SELECT COUNT(*) AS `existing_bill_item_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'counter_bills_items'
  AND `COLUMN_NAME` IN (
      'promotion_discount_alloc',
      'reward_discount_alloc'
  );

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

ALTER TABLE `hitpay_payment_requests`
    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 KEY `idx_hitpay_pricing_snapshot_sha256`
        (`pricing_snapshot_sha256`);

ALTER TABLE `hitpay_payment_request_items`
    ADD COLUMN `item_discount_amount` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `promotion_discount_alloc` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `reward_discount_alloc` decimal(12,2) DEFAULT NULL,
    ADD COLUMN `net_subtotal` decimal(12,2) DEFAULT NULL;

ALTER TABLE `counter_bills`
    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 KEY `idx_counter_bill_pricing_snapshot_sha256`
        (`pricing_snapshot_sha256`);

ALTER TABLE `counter_bills_items`
    ADD COLUMN `promotion_discount_alloc` decimal(10,2) DEFAULT NULL,
    ADD COLUMN `reward_discount_alloc` decimal(10,2) DEFAULT NULL;

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

SELECT 12 - COUNT(*) AS `missing_hitpay_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'hitpay_payment_requests'
  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'
  );

SELECT 4 - COUNT(*) AS `missing_hitpay_item_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'hitpay_payment_request_items'
  AND `COLUMN_NAME` IN (
      'item_discount_amount',
      'promotion_discount_alloc',
      'reward_discount_alloc',
      'net_subtotal'
  );

SELECT 12 - COUNT(*) AS `missing_bill_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'counter_bills'
  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'
  );

SELECT 2 - COUNT(*) AS `missing_bill_item_pricing_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'counter_bills_items'
  AND `COLUMN_NAME` IN (
      'promotion_discount_alloc',
      'reward_discount_alloc'
  );

-- Existing rows must remain unversioned and without a new snapshot. This
-- proves the migration did not rewrite historical financial records.
SELECT COUNT(*) AS `historical_hitpay_pricing_rewrite_problem_count`
FROM `kiosk_db`.`hitpay_payment_requests`
WHERE `pricing_version` IS NOT NULL
   OR `pricing_snapshot_json` IS NOT NULL
   OR `pricing_snapshot_sha256` IS NOT NULL;

SELECT COUNT(*) AS `historical_bill_pricing_rewrite_problem_count`
FROM `kiosk_db`.`counter_bills`
WHERE `pricing_version` IS NOT NULL
   OR `pricing_snapshot_json` IS NOT NULL
   OR `pricing_snapshot_sha256` IS NOT NULL;
