-- Use your existing database
USE quantu41_needatee;

-- Product ideas table
CREATE TABLE product_ideas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    theme VARCHAR(255) NOT NULL,
    keywords TEXT,
    trending_source VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    admin_id INT,
    INDEX idx_theme (theme),
    INDEX idx_created (created_at)
);

-- Designs table
CREATE TABLE designs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    idea_id INT,
    image_path VARCHAR(500) NOT NULL,
    prompt_text TEXT,
    iteration_number INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    admin_id INT,
    INDEX idx_idea (idea_id),
    INDEX idx_created (created_at)
);

-- T-shirt templates table
CREATE TABLE tshirt_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    color VARCHAR(50) NOT NULL,
    style VARCHAR(100) NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    printful_variant_id VARCHAR(100),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_color (color),
    INDEX idx_style (style)
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    design_id INT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    base_price DECIMAL(10,2) NOT NULL,
    retail_price DECIMAL(10,2) NOT NULL,
    status ENUM('draft', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_price (retail_price),
    INDEX idx_created (created_at)
);

-- Product mockups table
CREATE TABLE product_mockups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    template_id INT NOT NULL,
    mockup_image_path VARCHAR(500) NOT NULL,
    background_type ENUM('uniform', 'ai_generated') DEFAULT 'uniform',
    INDEX idx_product (product_id)
);

-- Product variants table
CREATE TABLE product_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    size VARCHAR(10) NOT NULL,
    color VARCHAR(50) NOT NULL,
    sku VARCHAR(100) UNIQUE,
    printful_variant_id VARCHAR(100),
    stock_status ENUM('in_stock', 'out_of_stock', 'discontinued') DEFAULT 'in_stock',
    INDEX idx_product (product_id),
    INDEX idx_sku (sku)
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending',
    stripe_payment_id VARCHAR(255),
    printful_order_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_order_number (order_number)
);

-- Order items table
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT,
    variant_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    printful_item_id VARCHAR(100),
    INDEX idx_order (order_id)
);

-- Wishlist table
CREATE TABLE wishlist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_wishlist (customer_id, product_id),
    INDEX idx_customer (customer_id)
);

-- Discount codes table
CREATE TABLE discount_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    percentage DECIMAL(5,2) NOT NULL,
    expiry_date DATE,
    max_uses INT DEFAULT NULL,
    current_uses INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_expiry (expiry_date)
);

-- Returns table
CREATE TABLE returns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    reason TEXT,
    status ENUM('requested', 'under_review', 'approved', 'rejected', 'resolved') DEFAULT 'requested',
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    admin_notes TEXT,
    INDEX idx_order (order_id),
    INDEX idx_status (status)
);

-- Messages table
CREATE TABLE messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('new', 'in_progress', 'resolved') DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    response TEXT,
    responded_at TIMESTAMP NULL,
    admin_id INT,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- System settings table
CREATE TABLE system_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Product shares table
CREATE TABLE product_shares (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    sender_email VARCHAR(255),
    recipient_email VARCHAR(255) NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product (product_id),
    INDEX idx_sent (sent_at)
);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value) VALUES
('default_markup_percentage', '50'),
('birthday_discount_percentage', '15'),
('stripe_public_key', ''),
('stripe_secret_key', ''),
('printful_api_key', ''),
('openai_api_key', ''),
('smtp_host', ''),
('smtp_port', '587'),
('smtp_username', ''),
('smtp_password', ''),
('return_window_days', '7'),
('site_name', 'NeedATee'),
('site_email', 'admin@needatee.com');

-- Insert sample product data
INSERT INTO products (name, description, base_price, retail_price, status) VALUES
('AI Cat Design Tee', 'Unique AI-generated cat design on premium cotton t-shirt', 15.00, 29.99, 'published'),
('Futuristic Robot Shirt', 'Cool robot design created by artificial intelligence', 15.00, 32.99, 'published'),
('Abstract Art Tee', 'Beautiful abstract patterns generated by AI', 15.00, 27.99, 'published');

-- Insert sample variants
INSERT INTO product_variants (product_id, size, color) VALUES
(1, 'S', 'black'), (1, 'M', 'black'), (1, 'L', 'black'), (1, 'XL', 'black'),
(1, 'S', 'white'), (1, 'M', 'white'), (1, 'L', 'white'), (1, 'XL', 'white'),
(2, 'S', 'navy'), (2, 'M', 'navy'), (2, 'L', 'navy'), (2, 'XL', 'navy'),
(3, 'S', 'gray'), (3, 'M', 'gray'), (3, 'L', 'gray'), (3, 'XL', 'gray');