shop.tonybtw.com

-- Shared schema for both PHP and Go versions

CREATE TABLE IF NOT EXISTS products (

    id SERIAL PRIMARY KEY,

    slug VARCHAR(255) UNIQUE NOT NULL,

    name VARCHAR(255) NOT NULL,

    description TEXT,

    price INTEGER NOT NULL,  -- cents

    image_url VARCHAR(500),

    active BOOLEAN DEFAULT true,

    printful_product_id VARCHAR(100),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE IF NOT EXISTS variants (

    id SERIAL PRIMARY KEY,

    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,

    size VARCHAR(50) NOT NULL,

    printful_variant_id VARCHAR(100),

    stock INTEGER DEFAULT 0,

    sort_order INTEGER DEFAULT 0

);

CREATE TABLE IF NOT EXISTS orders (

    id SERIAL PRIMARY KEY,

    stripe_session_id VARCHAR(255) UNIQUE,

    stripe_payment_intent VARCHAR(255),

    email VARCHAR(255),

    status VARCHAR(50) DEFAULT 'pending',  -- pending, paid, fulfilled, shipped

    total INTEGER NOT NULL,  -- cents

    shipping_name VARCHAR(255),

    shipping_address TEXT,

    printful_order_id VARCHAR(100),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE IF NOT EXISTS order_items (

    id SERIAL PRIMARY KEY,

    order_id INTEGER 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  -- cents at time of purchase

);

CREATE TABLE IF NOT EXISTS product_images (

    id SERIAL PRIMARY KEY,

    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,

    image_url VARCHAR(500) NOT NULL,

    sort_order INTEGER DEFAULT 0

);

-- Sample data for testing

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)

ON CONFLICT DO NOTHING;

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);