-- Cleanup script:
-- Remove old Quick Service default values that were previously written into
-- cars.vehicle_type, cars.brand and cars.model for Guest customers.
--
-- This script only targets:
-- 1. customers.customer_group = 'guest'
-- 2. cars that were used in at least one Quick Service order
-- 3. rows where the old default values are still present
--
-- Recommended:
-- - Backup the database first
-- - Run the preview queries before executing the UPDATE section

USE `CCW`;

-- =========================================================
-- PREVIEW 1: candidate rows that will be cleaned
-- =========================================================
SELECT
    car.car_id,
    cust.customer_id,
    cust.name AS customer_name,
    car.plate_number,
    car.province,
    car.vehicle_type,
    car.brand,
    car.model,
    (
        SELECT GROUP_CONCAT(DISTINCT o.branch_name ORDER BY o.branch_name SEPARATOR ', ')
        FROM orders o
        WHERE o.car_id = car.car_id
            AND o.order_source = 'quick_service'
            AND COALESCE(o.branch_name, '') <> ''
    ) AS quick_service_branches
FROM cars car
INNER JOIN customers cust ON cust.customer_id = car.customer_id
WHERE cust.customer_group = 'guest'
    AND EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.car_id = car.car_id
            AND o.order_source = 'quick_service'
    )
    AND (
        car.vehicle_type = 'Quick Service'
        OR car.brand = 'Quick Service'
        OR car.model IN ('Guest', 'Walk-in')
        OR EXISTS (
            SELECT 1
            FROM orders oq
            WHERE oq.car_id = car.car_id
                AND oq.order_source = 'quick_service'
                AND COALESCE(oq.branch_name, '') <> ''
                AND car.model = oq.branch_name
        )
    )
ORDER BY cust.customer_id, car.car_id;

-- =========================================================
-- PREVIEW 2: total candidate count
-- =========================================================
SELECT COUNT(*) AS candidate_rows
FROM cars car
INNER JOIN customers cust ON cust.customer_id = car.customer_id
WHERE cust.customer_group = 'guest'
    AND EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.car_id = car.car_id
            AND o.order_source = 'quick_service'
    )
    AND (
        car.vehicle_type = 'Quick Service'
        OR car.brand = 'Quick Service'
        OR car.model IN ('Guest', 'Walk-in')
        OR EXISTS (
            SELECT 1
            FROM orders oq
            WHERE oq.car_id = car.car_id
                AND oq.order_source = 'quick_service'
                AND COALESCE(oq.branch_name, '') <> ''
                AND car.model = oq.branch_name
        )
    );

-- =========================================================
-- EXECUTE: uncomment this block to clean the old values
-- =========================================================
/*
START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_quick_service_guest_cleanup;

CREATE TEMPORARY TABLE tmp_quick_service_guest_cleanup AS
SELECT DISTINCT
    car.car_id
FROM cars car
INNER JOIN customers cust ON cust.customer_id = car.customer_id
WHERE cust.customer_group = 'guest'
    AND EXISTS (
        SELECT 1
        FROM orders o
        WHERE o.car_id = car.car_id
            AND o.order_source = 'quick_service'
    )
    AND (
        car.vehicle_type = 'Quick Service'
        OR car.brand = 'Quick Service'
        OR car.model IN ('Guest', 'Walk-in')
        OR EXISTS (
            SELECT 1
            FROM orders oq
            WHERE oq.car_id = car.car_id
                AND oq.order_source = 'quick_service'
                AND COALESCE(oq.branch_name, '') <> ''
                AND car.model = oq.branch_name
        )
    );

UPDATE cars car
INNER JOIN tmp_quick_service_guest_cleanup t ON t.car_id = car.car_id
SET
    car.vehicle_type = CASE
        WHEN car.vehicle_type = 'Quick Service' THEN ''
        ELSE car.vehicle_type
    END,
    car.brand = CASE
        WHEN car.brand = 'Quick Service' THEN ''
        ELSE car.brand
    END,
    car.model = CASE
        WHEN car.model IN ('Guest', 'Walk-in') THEN ''
        WHEN EXISTS (
            SELECT 1
            FROM orders oq
            WHERE oq.car_id = car.car_id
                AND oq.order_source = 'quick_service'
                AND COALESCE(oq.branch_name, '') <> ''
                AND car.model = oq.branch_name
        ) THEN ''
        ELSE car.model
    END;

SELECT ROW_COUNT() AS updated_rows;

SELECT
    car.car_id,
    cust.customer_id,
    cust.name AS customer_name,
    car.plate_number,
    car.province,
    car.vehicle_type,
    car.brand,
    car.model
FROM cars car
INNER JOIN customers cust ON cust.customer_id = car.customer_id
INNER JOIN tmp_quick_service_guest_cleanup t ON t.car_id = car.car_id
ORDER BY cust.customer_id, car.car_id;

COMMIT;
*/
