-- Comparison List Table
CREATE TABLE comparison_list (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_id INT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Stock Alerts Table
CREATE TABLE stock_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notified BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Rate Limiting Table
CREATE TABLE rate_limits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Promotions Table
CREATE TABLE promotions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vendor_id INT,
    name VARCHAR(100),
    discount_type ENUM('percentage', 'fixed'),
    discount_value DECIMAL(10,2),
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES vendors(id)
);

-- Add new columns to existing tables
ALTER TABLE products
    ADD COLUMN views INT DEFAULT 0,
    ADD COLUMN last_stock_update TIMESTAMP,
    ADD COLUMN meta_title VARCHAR(255),
    ADD COLUMN meta_description TEXT;

ALTER TABLE users
    ADD COLUMN last_login TIMESTAMP,
    ADD COLUMN login_attempts INT DEFAULT 0,
    ADD COLUMN account_locked BOOLEAN DEFAULT FALSE;

ALTER TABLE vendors
    ADD COLUMN rating DECIMAL(3,2),
    ADD COLUMN total_ratings INT DEFAULT 0,
    ADD COLUMN commission_rate DECIMAL(5,2);