-- 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;