-- RaseedNow: seven customer ranks + manual override support.
-- Run once on the selected phpMyAdmin database. No CREATE DATABASE statement.

ALTER TABLE `users`
    ADD COLUMN `customer_tier_locked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `customer_tier`;

CREATE INDEX `users_customer_tier_locked_index`
    ON `users` (`customer_tier_locked`);

-- Preserve ranks that were already assigned before installing the automatic system.
UPDATE `users`
SET `customer_tier_locked` = 1
WHERE `role` <> 'admin' AND `customer_tier` <> 'general';

-- Recalculate spending and automatic rank from confirmed paid orders only.
UPDATE `users` AS `u`
LEFT JOIN (
    SELECT `user_id`, SUM(`total_minor`) AS `paid_spend`, COUNT(*) AS `paid_orders`
    FROM `orders`
    WHERE `payment_status` = 'paid'
    GROUP BY `user_id`
) AS `p` ON `p`.`user_id` = `u`.`id`
SET
    `u`.`total_spent_minor` = COALESCE(`p`.`paid_spend`, 0),
    `u`.`orders_count` = COALESCE(`p`.`paid_orders`, 0),
    `u`.`customer_tier` = CASE
        WHEN `u`.`customer_tier_locked` = 1 THEN `u`.`customer_tier`
        WHEN COALESCE(`p`.`paid_spend`, 0) >= 3000000 THEN 'merchant_gold'
        WHEN COALESCE(`p`.`paid_spend`, 0) >= 1500000 THEN 'diamond'
        WHEN COALESCE(`p`.`paid_spend`, 0) >= 750000 THEN 'vip_silver'
        WHEN COALESCE(`p`.`paid_spend`, 0) >= 350000 THEN 'gold'
        WHEN COALESCE(`p`.`paid_spend`, 0) >= 150000 THEN 'silver'
        WHEN COALESCE(`p`.`paid_spend`, 0) >= 50000 THEN 'bronze'
        ELSE 'general'
    END
WHERE `u`.`role` <> 'admin';