-- Add loyalty points to customers table
ALTER TABLE customers ADD COLUMN IF NOT EXISTS loyalty_points DECIMAL(10, 2) DEFAULT 0.00;

-- Create loyalty transactions table
CREATE TABLE IF NOT EXISTS loyalty_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_id INT NULL,
    points DECIMAL(10, 2) NOT NULL,
    type ENUM('earned', 'redeemed', 'adjusted') NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (customer_id),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial settings for loyalty system
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES 
('loyalty_enabled', '1'),
('loyalty_points_per_currency', '1'), -- 1 point per 1 unit spent
('loyalty_currency_per_point', '0.05'); -- 1 point = 0.05 unit discount (20 points = 1 unit)