CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    price INTEGER NOT NULL,
    image_url TEXT,
    active INTEGER DEFAULT 1,
    printful_product_id TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS variants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    size TEXT NOT NULL,
    printful_variant_id TEXT,
    stock INTEGER DEFAULT 0,
    sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stripe_session_id TEXT UNIQUE,
    stripe_payment_intent TEXT,
    email TEXT,
    status TEXT DEFAULT 'pending',
    total INTEGER NOT NULL,
    shipping_name TEXT,
    shipping_address TEXT,
    printful_order_id TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    variant_id INTEGER REFERENCES variants(id),
    quantity INTEGER NOT NULL,
    price INTEGER NOT NULL
);

INSERT INTO products (slug, name, description, price, image_url) VALUES
    ('linux-tux-tee', 'Linux Tux Tee', 'Classic penguin on a comfy tee. 100% cotton.', 2500, 'https://placehold.co/600x600/1a1a1a/3b82f6?text=Linux+Tux+Tee'),
    ('btw-i-use-arch-hoodie', 'BTW I Use Arch Hoodie', 'Let everyone know. Heavyweight fleece.', 4500, 'https://placehold.co/600x600/1a1a1a/3b82f6?text=Arch+Hoodie'),
    ('foss-freedom-cap', 'FOSS Freedom Cap', 'Embroidered dad hat for free software enjoyers.', 2000, 'https://placehold.co/600x600/1a1a1a/3b82f6?text=FOSS+Cap')
ON CONFLICT(slug) DO UPDATE SET image_url = excluded.image_url;

INSERT INTO variants (product_id, size, sort_order) VALUES
    (1, 'S', 1), (1, 'M', 2), (1, 'L', 3), (1, 'XL', 4),
    (2, 'S', 1), (2, 'M', 2), (2, 'L', 3), (2, 'XL', 4), (2, 'XXL', 5),
    (3, 'One Size', 1);

CREATE INDEX IF NOT EXISTS idx_products_slug ON products(slug);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);
CREATE INDEX IF NOT EXISTS idx_variants_product ON variants(product_id);
CREATE INDEX IF NOT EXISTS idx_orders_stripe ON orders(stripe_session_id);