-- Atrium Merchant Platform Database Schema
-- Run this file to initialize the database

CREATE DATABASE IF NOT EXISTS estore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE estore;

-- Plans table
CREATE TABLE IF NOT EXISTS plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    price_monthly DECIMAL(10,2) NOT NULL,
    price_annual DECIMAL(10,2) NOT NULL,
    transaction_fee DECIMAL(5,3) NOT NULL,
    staff_accounts INT NOT NULL DEFAULT 2,
    inventory_locations INT NOT NULL DEFAULT 4,
    shipping_discount INT NOT NULL DEFAULT 77,
    features JSON,
    is_popular BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Merchants table
CREATE TABLE IF NOT EXISTS merchants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    store_name VARCHAR(255),
    store_slug VARCHAR(255) UNIQUE,
    business_type ENUM('physical_goods', 'digital_services', 'both') DEFAULT 'physical_goods',
    experience_level ENUM('starting_out', 'moving_platform', 'established_offline') DEFAULT 'starting_out',
    plan_id INT DEFAULT 1,
    plan_billing_cycle ENUM('monthly', 'annual') DEFAULT 'monthly',
    status ENUM('active', 'suspended', 'pending') DEFAULT 'pending',
    onboarding_step INT DEFAULT 1,
    onboarding_complete BOOLEAN DEFAULT FALSE,
    avatar_url VARCHAR(500),
    domain VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
);

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    cost DECIMAL(10,2) DEFAULT 0.00,
    sku VARCHAR(100),
    stock INT DEFAULT 0,
    continue_selling_when_out BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'draft', 'archived') DEFAULT 'draft',
    seo_title VARCHAR(500),
    seo_description TEXT,
    url_handle VARCHAR(500),
    images JSON,
    variants JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Customers table
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    address_line1 VARCHAR(500),
    address_line2 VARCHAR(500),
    city VARCHAR(255),
    state VARCHAR(255),
    country VARCHAR(100),
    postal_code VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    customer_id INT,
    order_number VARCHAR(50) NOT NULL,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    payment_status ENUM('paid', 'pending', 'refunded', 'canceled') DEFAULT 'pending',
    fulfillment_status ENUM('unfulfilled', 'fulfilled', 'restocked', 'partial') DEFAULT 'unfulfilled',
    total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    subtotal DECIMAL(10,2) DEFAULT 0.00,
    tax DECIMAL(10,2) DEFAULT 0.00,
    shipping DECIMAL(10,2) DEFAULT 0.00,
    notes TEXT,
    shipping_address JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);

-- Order items table
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT,
    product_title VARCHAR(500) NOT NULL,
    variant_title VARCHAR(255),
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    sku VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- Campaigns / Marketing table
CREATE TABLE IF NOT EXISTS campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    channel ENUM('email', 'social', 'automation', 'discount') DEFAULT 'email',
    status ENUM('active', 'paused', 'completed', 'draft') DEFAULT 'draft',
    roi DECIMAL(10,2) DEFAULT 0.00,
    reach INT DEFAULT 0,
    budget DECIMAL(10,2) DEFAULT 0.00,
    start_date DATE,
    end_date DATE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Sessions table (managed by connect-session-sequelize, included for reference)
-- express-session will create this automatically

-- Analytics snapshots (daily summaries)
CREATE TABLE IF NOT EXISTS analytics_daily (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    date DATE NOT NULL,
    total_sales DECIMAL(12,2) DEFAULT 0.00,
    total_orders INT DEFAULT 0,
    sessions INT DEFAULT 0,
    avg_order_value DECIMAL(10,2) DEFAULT 0.00,
    conversion_rate DECIMAL(5,4) DEFAULT 0.0000,
    new_customers INT DEFAULT 0,
    UNIQUE KEY unique_merchant_date (merchant_id, date),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Storefront themes
CREATE TABLE IF NOT EXISTS themes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    version VARCHAR(20) DEFAULT '1.0.0',
    settings JSON,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- =============================================
-- Seed Data
-- =============================================

INSERT IGNORE INTO plans (name, slug, price_monthly, price_annual, transaction_fee, staff_accounts, inventory_locations, shipping_discount, features, is_popular) VALUES
('Basic', 'basic', 29.00, 23.20, 2.000, 2, 4, 77, '["Online store & Checkout", "Unlimited products", "2 staff accounts", "Basic reports", "24/7 Chat Support"]', FALSE),
('Pro', 'pro', 79.00, 63.20, 1.000, 5, 8, 88, '["Everything in Basic", "5 staff accounts", "Professional reports", "Lower transaction fees", "Priority Chat Support", "Custom shipping rates"]', TRUE),
('Enterprise', 'enterprise', 299.00, 239.20, 0.500, 15, 20, 88, '["Everything in Pro", "15 staff accounts", "Advanced report builder", "Calculated shipping rates", "Dedicated Account Manager", "Custom checkout"]', FALSE);
