32 lines
1.0 KiB
SQL
32 lines
1.0 KiB
SQL
-- Modern Loyalty System Migration
|
|
ALTER TABLE customers
|
|
ADD COLUMN IF NOT EXISTS loyalty_tier ENUM('bronze', 'silver', 'gold') DEFAULT 'bronze',
|
|
ADD COLUMN IF NOT EXISTS total_spent DECIMAL(15, 3) DEFAULT 0.000;
|
|
|
|
CREATE TABLE IF NOT EXISTS loyalty_transactions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
customer_id INT NOT NULL,
|
|
transaction_id INT NULL,
|
|
points_change DECIMAL(15, 3) NOT NULL,
|
|
transaction_type ENUM('earned', 'redeemed', 'adjustment') NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Update existing total_spent based on previous transactions if possible
|
|
UPDATE customers c
|
|
SET c.total_spent = (
|
|
SELECT COALESCE(SUM(total_with_vat), 0)
|
|
FROM invoices
|
|
WHERE customer_id = c.id AND type = 'sale'
|
|
);
|
|
|
|
-- Initial tier update based on existing spent amount
|
|
UPDATE customers
|
|
SET loyalty_tier = CASE
|
|
WHEN total_spent >= 1500 THEN 'gold'
|
|
WHEN total_spent >= 500 THEN 'silver'
|
|
ELSE 'bronze'
|
|
END;
|