23 lines
1005 B
SQL
23 lines
1005 B
SQL
-- 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)
|