-- schema.sql
CREATE TABLE IF NOT EXISTS collections (
    id BIGINT PRIMARY KEY, -- Shopify Collection ID
    handle VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    parent_id BIGINT DEFAULT NULL, -- Stores parent collection ID if this is a child in custom.related_collection
    UNIQUE KEY (handle)
);

CREATE TABLE IF NOT EXISTS products (
    id BIGINT PRIMARY KEY, -- Shopify Product ID
    handle VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    vendor VARCHAR(255) DEFAULT NULL,
    min_price DECIMAL(10, 2) DEFAULT 0.00,
    max_price DECIMAL(10, 2) DEFAULT 0.00,
    status VARCHAR(50) DEFAULT 'active',
    UNIQUE KEY (handle),
    INDEX (vendor),
    INDEX (min_price),
    INDEX (status)
);

CREATE TABLE IF NOT EXISTS product_collections (
    product_id BIGINT,
    collection_id BIGINT,
    PRIMARY KEY (product_id, collection_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE
);