
-- Database Creation
CREATE DATABASE IF NOT EXISTS ecommerce_db;
USE ecommerce_db;

-- Users Table
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NULL,
    
    -- Billing Address
    address_line1 VARCHAR(255) NULL,
    address_line2 VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    country VARCHAR(100) NULL,

    -- Saved Shipping Address
    shipping_address_line1 VARCHAR(255) NULL,
    shipping_address_line2 VARCHAR(255) NULL,
    shipping_city VARCHAR(100) NULL,
    shipping_state VARCHAR(100) NULL,
    shipping_postal_code VARCHAR(20) NULL,
    shipping_country VARCHAR(100) NULL,

    -- Payment Information
    stripe_payment_token VARCHAR(255) NULL,
    saved_payment TINYINT(1) DEFAULT 0,
    is_subscriber TINYINT(1) DEFAULT 0,

    -- Security & Authentication
    is_admin TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    registered_ip VARCHAR(45) NOT NULL,
    login_ip VARCHAR(45) NULL,
    email_verified TINYINT(1) DEFAULT 0,
    failed_login_attempts INT DEFAULT 0,
    account_locked_until TIMESTAMP NULL DEFAULT NULL,
    fraud_suspensions INT DEFAULT 0,
    requires_review TINYINT(1) DEFAULT 0,

    -- MFA
    mfa_enabled TINYINT(1) DEFAULT 0,
    mfa_secret VARCHAR(255) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- User Shipping Addresses Table
CREATE TABLE user_shipping_addresses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    label VARCHAR(50) NOT NULL DEFAULT 'Home',
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255) NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Products Table
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    stock_quantity INT UNSIGNED NOT NULL DEFAULT 0,
    category VARCHAR(100) NULL,
    image_url VARCHAR(500) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
    order_status ENUM('processing', 'shipped', 'delivered', 'canceled', 'returned') NOT NULL DEFAULT 'processing',
    shipping_address_id INT UNSIGNED NOT NULL,
    tracking_number VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (shipping_address_id) REFERENCES user_shipping_addresses(id) ON DELETE CASCADE
);

-- Order Items Table
CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Discounts Table
CREATE TABLE discounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    discount_type ENUM('percentage', 'fixed') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    min_order_amount DECIMAL(10,2) NULL,
    max_uses INT UNSIGNED NULL,
    uses_remaining INT UNSIGNED NULL,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_date TIMESTAMP NULL,
    is_active TINYINT(1) DEFAULT 1
);

-- Order Discounts Table
CREATE TABLE order_discounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    discount_id INT UNSIGNED NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (discount_id) REFERENCES discounts(id) ON DELETE CASCADE
);

-- BNPL Transactions Table
CREATE TABLE bnpl_transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NOT NULL,
    bnpl_provider ENUM('Afterpay', 'Klarna', 'Affirm', 'PayPal Pay Later') NOT NULL,
    bnpl_status ENUM('approved', 'pending', 'declined', 'completed') NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    installment_amount DECIMAL(10,2) NOT NULL,
    installment_count INT UNSIGNED NOT NULL,
    due_next TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Upsell Recommendations Table
CREATE TABLE upsell_recommendations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    base_product_id INT UNSIGNED NOT NULL,
    upsell_product_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (base_product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (upsell_product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Cross-Sell Recommendations Table
CREATE TABLE cross_sell_recommendations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    base_product_id INT UNSIGNED NOT NULL,
    cross_sell_product_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (base_product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (cross_sell_product_id) REFERENCES products(id) ON DELETE CASCADE
);
