45 lines
1.9 KiB
SQL
45 lines
1.9 KiB
SQL
-- Held Carts Table
|
|
CREATE TABLE IF NOT EXISTS pos_held_carts (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
cart_name VARCHAR(100) NOT NULL,
|
|
items_json TEXT NOT NULL,
|
|
customer_id INT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Discount Codes Table
|
|
CREATE TABLE IF NOT EXISTS discount_codes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(50) UNIQUE NOT NULL,
|
|
type ENUM('percentage', 'fixed') NOT NULL DEFAULT 'percentage',
|
|
value DECIMAL(15, 3) NOT NULL,
|
|
min_purchase DECIMAL(15, 3) DEFAULT 0.000,
|
|
expiry_date DATE NULL,
|
|
status ENUM('active', 'inactive') DEFAULT 'active',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Update Customers for Loyalty Points
|
|
-- Check if column exists first (handled by IF NOT EXISTS in some dialects, but let's be safe for MySQL 5.7/MariaDB)
|
|
-- Using a procedure or just trying it. MySQL 8.0/MariaDB supports IF NOT EXISTS for ADD COLUMN but older versions don't always.
|
|
-- For this VM, we can just run it.
|
|
|
|
ALTER TABLE customers ADD COLUMN IF NOT EXISTS loyalty_points DECIMAL(15, 3) DEFAULT 0.000;
|
|
|
|
-- Update POS Transactions for Discounts and Loyalty
|
|
ALTER TABLE pos_transactions
|
|
ADD COLUMN IF NOT EXISTS discount_code_id INT NULL,
|
|
ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(15, 3) DEFAULT 0.000,
|
|
ADD COLUMN IF NOT EXISTS loyalty_points_earned DECIMAL(15, 3) DEFAULT 0.000,
|
|
ADD COLUMN IF NOT EXISTS loyalty_points_redeemed DECIMAL(15, 3) DEFAULT 0.000,
|
|
ADD COLUMN IF NOT EXISTS net_amount DECIMAL(15, 3) NOT NULL DEFAULT 0.000;
|
|
|
|
-- Adding foreign key separately to be safe
|
|
-- ALTER TABLE pos_transactions ADD FOREIGN KEY (discount_code_id) REFERENCES discount_codes(id) ON DELETE SET NULL;
|
|
|
|
-- Insert some dummy discount codes
|
|
INSERT IGNORE INTO discount_codes (code, type, value, min_purchase) VALUES
|
|
('WELCOME10', 'percentage', 10.000, 0.000),
|
|
('SAVE5', 'fixed', 5.000, 50.000);
|