USE `kiosk_db`;

-- Milestone 4D.0B: service-charge policy by fulfilment mode.
--
-- The service-charge percentage remains in stores.service_tax. This migration
-- only records whether that percentage applies to each fulfilment mode.
--
-- Initial policy:
--   dine_in  -> service charge applies
--   takeaway -> service charge does not apply
--   any other/custom mode -> service charge does not apply
--
-- Administrators can change these values later from:
-- Manage Stores -> Edit details -> Mobile settings.
--
-- 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_service_charge_policy_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'store_fulfillment_modes'
  AND `COLUMN_NAME` = 'service_charge_applies';

SELECT COUNT(*) AS `existing_service_charge_policy_constraint_problem_count`
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE `CONSTRAINT_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'store_fulfillment_modes'
  AND `CONSTRAINT_NAME` = 'chk_store_fulfillment_service_charge';

SELECT COUNT(*) AS `duplicate_fulfillment_mode_problem_count`
FROM (
    SELECT `store_id`, `mode_code`
    FROM `kiosk_db`.`store_fulfillment_modes`
    GROUP BY `store_id`, `mode_code`
    HAVING COUNT(*) > 1
) AS `duplicates`;

SELECT COUNT(*) AS `orphaned_fulfillment_store_problem_count`
FROM `kiosk_db`.`store_fulfillment_modes` AS `sfm`
LEFT JOIN `kiosk_db`.`stores` AS `s`
    ON `s`.`id` = `sfm`.`store_id`
WHERE `s`.`id` IS NULL;

-- =========================================================================
-- MIGRATION
-- Run only after all four pre-flight results above are 0.
-- =========================================================================

ALTER TABLE `kiosk_db`.`store_fulfillment_modes`
    ADD COLUMN `service_charge_applies` tinyint(1)
        NOT NULL DEFAULT '0'
        AFTER `requires_table_number`,
    ADD CONSTRAINT `chk_store_fulfillment_service_charge`
        CHECK (`service_charge_applies` IN (0, 1));

-- Apply the safe initial policy. Custom modes remain at the default of 0.
UPDATE `kiosk_db`.`store_fulfillment_modes`
SET `service_charge_applies` =
    CASE
        WHEN `mode_code` = 'dine_in' THEN 1
        ELSE 0
    END;

-- =========================================================================
-- POST-MIGRATION VERIFICATION -- every problem count must be 0
-- =========================================================================

SELECT 1 - COUNT(*) AS `missing_service_charge_policy_column_problem_count`
FROM information_schema.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'store_fulfillment_modes'
  AND `COLUMN_NAME` = 'service_charge_applies'
  AND `IS_NULLABLE` = 'NO'
  AND `COLUMN_DEFAULT` = '0';

SELECT 1 - COUNT(*) AS `missing_service_charge_policy_constraint_problem_count`
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE `CONSTRAINT_SCHEMA` = 'kiosk_db'
  AND `TABLE_NAME` = 'store_fulfillment_modes'
  AND `CONSTRAINT_NAME` = 'chk_store_fulfillment_service_charge'
  AND `CONSTRAINT_TYPE` = 'CHECK';

SELECT COUNT(*) AS `invalid_service_charge_policy_value_problem_count`
FROM `kiosk_db`.`store_fulfillment_modes`
WHERE `service_charge_applies` NOT IN (0, 1);

SELECT COUNT(*) AS `takeaway_service_charge_policy_problem_count`
FROM `kiosk_db`.`store_fulfillment_modes`
WHERE `mode_code` = 'takeaway'
  AND `service_charge_applies` <> 0;

SELECT COUNT(*) AS `dine_in_service_charge_policy_problem_count`
FROM `kiosk_db`.`store_fulfillment_modes`
WHERE `mode_code` = 'dine_in'
  AND `service_charge_applies` <> 1;

-- Informational result: review the effective policy for every store/mode.
SELECT
    `s`.`id` AS `store_id`,
    `s`.`name` AS `store_name`,
    `sfm`.`mode_code`,
    `sfm`.`display_name`,
    `sfm`.`is_active`,
    `sfm`.`service_charge_applies`,
    `s`.`service_tax` AS `configured_service_charge_percent`,
    `s`.`gst` AS `configured_gst_percent`
FROM `kiosk_db`.`store_fulfillment_modes` AS `sfm`
INNER JOIN `kiosk_db`.`stores` AS `s`
    ON `s`.`id` = `sfm`.`store_id`
ORDER BY `s`.`id`, `sfm`.`display_order`, `sfm`.`mode_code`;
