-- Main POS Transactions Table
CREATE TABLE IF NOT EXISTS pos_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_no VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT NULL,
    total_amount DECIMAL(15, 3) NOT NULL,
    tax_amount DECIMAL(15, 3) DEFAULT 0.000,
    discount_code_id INT NULL,
    discount_amount DECIMAL(15, 3) DEFAULT 0.000,
    loyalty_points_earned DECIMAL(15, 3) DEFAULT 0.000,
    loyalty_points_redeemed DECIMAL(15, 3) DEFAULT 0.000,
    net_amount DECIMAL(15, 3) NOT NULL DEFAULT 0.000,
    payment_method ENUM('cash', 'card', 'transfer') NOT NULL,
    status ENUM('completed', 'refunded', 'cancelled') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT NULL
);

-- POS Items Table
CREATE TABLE IF NOT EXISTS pos_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(15, 3) NOT NULL,
    unit_price DECIMAL(15, 3) NOT NULL,
    subtotal DECIMAL(15, 3) NOT NULL,
    FOREIGN KEY (transaction_id) REFERENCES pos_transactions(id) ON DELETE CASCADE
);

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

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

-- Add Loyalty Points to Customers if missing
ALTER TABLE customers ADD COLUMN IF NOT EXISTS loyalty_points DECIMAL(15, 3) DEFAULT 0.000;

-- Insert initial 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);