-- ============================================================
-- Shopify Custom Checkout - MySQL Schema
-- ============================================================

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

-- Admins
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Customers
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    address1 VARCHAR(255),
    address2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    zip VARCHAR(20),
    country VARCHAR(100) DEFAULT 'US',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Orders
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    shopify_order_id BIGINT DEFAULT NULL,
    shopify_order_number VARCHAR(50) DEFAULT NULL,
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    shipping_cost DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_method VARCHAR(100),
    transaction_id VARCHAR(255),
    notes TEXT,
    status ENUM('pending','verified','approved','rejected','fulfilled') DEFAULT 'pending',
    ai_verified TINYINT(1) DEFAULT 0,
    ai_result TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Order Items
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    shopify_variant_id BIGINT,
    shopify_product_id BIGINT,
    product_title VARCHAR(255),
    variant_title VARCHAR(255),
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    image_url TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Payment Proofs
CREATE TABLE IF NOT EXISTS payment_proofs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    image_path VARCHAR(500),
    image_url VARCHAR(500),
    transaction_id VARCHAR(255),
    payment_method VARCHAR(100),
    ai_verified TINYINT(1) DEFAULT 0,
    ai_confidence DECIMAL(5,2) DEFAULT 0.00,
    ai_response TEXT,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Activity Logs
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT DEFAULT NULL,
    customer_id INT DEFAULT NULL,
    admin_id INT DEFAULT NULL,
    action VARCHAR(255) NOT NULL,
    detail TEXT,
    ip_address VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Default admin (password: admin123 — change immediately)
INSERT INTO admins (username, password, email)
VALUES ('admin', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@yourdomain.com');