seed_catalog.sql

-- Clube do Java catalog seed.
--
-- Source of truth: the retired Spring Boot backend's Flyway seed
-- (Clube-do-java/backend/src/main/resources/db/migration/V2__Initial_Data.sql),
-- ported to this API's model:
--   - prices reais -> price_cents (int64 minor units)
--   - weight kg -> weight_grams
--   - one sellable variant PER size x color combination (the storefront resolves
--     the selected size/color to its variant_id); products.attributes still lists
--     the available sizes/colors for the selectors. Products without size/color
--     get a single default variant.
--   - the product's total stock is split across its variants; each variant holds
--     its own variant_stock row against the default source (the single truth
--     Reserve/Claim/Release act on; products.stock is deprecated).
--
-- Idempotent: deterministic uuid_generate_v5 ids; variants/media are rebuilt per
-- run (DELETE + reinsert) so combos stay in sync. Run with:
--   psql "$DATABASE_URL" -f scripts/seed_catalog.sql

DO $$
DECLARE
    ns        constant uuid := '0b1e7a1a-4a2b-4c3d-8e9f-c0deba5e0001'::uuid;
    def_src   uuid;
    cat_cami  uuid := uuid_generate_v5(ns, 'cat:camisetas');
    cat_cane  uuid := uuid_generate_v5(ns, 'cat:canecas');
    cat_ades  uuid := uuid_generate_v5(ns, 'cat:adesivos');
    cat_jaqu  uuid := uuid_generate_v5(ns, 'cat:jaquetas');
    cat_aces  uuid := uuid_generate_v5(ns, 'cat:acessorios');
    rec       record;
    pid       uuid;
    vid       uuid;
    i         integer;
    sizes     text[];
    colors    text[];
    s         text;
    c         text;
    ncombos   integer;
    base      integer;
    rem       integer;
    idx       integer;
    vstock    integer;
    attrs     jsonb;
    vsku      text;
BEGIN
    SELECT id INTO def_src FROM sources WHERE is_default LIMIT 1;
    IF def_src IS NULL THEN
        RAISE EXCEPTION 'no default source found; run migrations first';
    END IF;

    INSERT INTO categories (id, name) VALUES
        (cat_cami, 'Camisetas'),
        (cat_cane, 'Canecas'),
        (cat_ades, 'Adesivos'),
        (cat_jaqu, 'Jaquetas'),
        (cat_aces, 'Acessórios')
    ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

    FOR rec IN
        SELECT * FROM (VALUES
            ('camiseta-java-developer',  'Camiseta Java Developer',    'Camiseta oversized com estampa Java Developer',   7990, 100, cat_cami, true,  300, '{"sizes":["P","M","G","GG"],"colors":["Preto","Branco","Azul"]}'::jsonb),
            ('camiseta-spring-framework','Camiseta Spring Framework',  'Camiseta com logo do Spring Framework',           7990,  80, cat_cami, false, 300, '{"sizes":["P","M","G","GG"],"colors":["Preto","Branco"]}'::jsonb),
            ('camiseta-sout',            'Camiseta Sout',              'Camiseta Sysout',                                 7990,  50, cat_cami, false, 300, '{"sizes":["P","M","G","GG"],"colors":["Preto","Cinza"]}'::jsonb),
            ('caneca-java',              'Caneca Java',                'Caneca preta com logo Java',                      4990, 200, cat_cane, true,  500, '{"colors":["Preto","Branco"]}'::jsonb),
            ('caneca-codigo-java',       'Caneca Código Java',         'Caneca com código Java impresso',                 4990, 150, cat_cane, false, 500, '{"colors":["Preto","Branco"]}'::jsonb),
            ('kit-adesivos-java',        'Kit Adesivos Java',          'Kit com 5 adesivos de Java para notebook',        1990, 300, cat_ades, true,  100, '{}'::jsonb),
            ('adesivo-spring-boot',      'Adesivo Spring Boot',        'Adesivo grande com logo do Spring Boot',           990, 250, cat_ades, false,  50, '{}'::jsonb),
            ('jaqueta-java-developer',   'Jaqueta Java Developer',     'Jaqueta com capuz e estampa Java Dev',           14990,  40, cat_jaqu, true,  800, '{"sizes":["P","M","G","GG"],"colors":["Preto","Azul"]}'::jsonb),
            ('moletom-jvm',              'Moletom JVM',                'Moletom com estampa da JVM',                     12990,  60, cat_jaqu, false, 700, '{"sizes":["P","M","G","GG"],"colors":["Preto","Cinza"]}'::jsonb),
            ('mousepad-java',            'Mousepad Java',              'Mousepad com logo Java',                          2990, 120, cat_aces, false, 200, '{"colors":["Preto"]}'::jsonb)
        ) AS t(slug, name, description, price_cents, stock, category_id, featured, weight_grams, attributes)
    LOOP
        pid := uuid_generate_v5(ns, 'prod:' || rec.slug);

        INSERT INTO products
            (id, name, description, price_cents, category_id, featured, weight_grams, attributes, slug, type, status, unit, currency)
        VALUES
            (pid, rec.name, rec.description, rec.price_cents, rec.category_id, rec.featured, rec.weight_grams, rec.attributes, rec.slug, 'simple', 'active', 'UN', 'BRL')
        ON CONFLICT (id) DO UPDATE SET
            name         = EXCLUDED.name,
            description  = EXCLUDED.description,
            price_cents  = EXCLUDED.price_cents,
            category_id  = EXCLUDED.category_id,
            featured     = EXCLUDED.featured,
            weight_grams = EXCLUDED.weight_grams,
            attributes   = EXCLUDED.attributes;

        -- Rebuild variants for this product (cascade drops their variant_stock).
        DELETE FROM cart_items ci USING product_variants pv
            WHERE ci.variant_id = pv.id AND pv.product_id = pid;
        DELETE FROM product_variants WHERE product_id = pid;

        -- Size/color axes; an absent axis becomes a single NULL element so the
        -- cartesian loop still runs once (e.g. color-only or no-variation products).
        SELECT coalesce(array_agg(value), ARRAY[NULL]::text[]) INTO sizes
            FROM jsonb_array_elements_text(rec.attributes -> 'sizes');
        SELECT coalesce(array_agg(value), ARRAY[NULL]::text[]) INTO colors
            FROM jsonb_array_elements_text(rec.attributes -> 'colors');

        ncombos := array_length(sizes, 1) * array_length(colors, 1);
        base := rec.stock / ncombos;
        rem := rec.stock % ncombos;
        idx := 0;

        FOREACH s IN ARRAY sizes LOOP
            FOREACH c IN ARRAY colors LOOP
                idx := idx + 1;
                vstock := base + (CASE WHEN idx <= rem THEN 1 ELSE 0 END);

                attrs := '{}'::jsonb;
                IF s IS NOT NULL THEN attrs := attrs || jsonb_build_object('tamanho', s); END IF;
                IF c IS NOT NULL THEN attrs := attrs || jsonb_build_object('cor', c); END IF;

                vsku := rec.slug
                    || coalesce('-' || lower(s), '')
                    || coalesce('-' || lower(c), '');
                vid := uuid_generate_v5(ns, 'var:' || rec.slug || ':' || coalesce(s, '_') || ':' || coalesce(c, '_'));

                INSERT INTO product_variants
                    (id, product_id, sku, attributes, price_cents, active, position, price_inherited)
                VALUES
                    (vid, pid, vsku, attrs, rec.price_cents, true, idx - 1, true);

                INSERT INTO variant_stock (variant_id, source_id, stock)
                VALUES (vid, def_src, vstock);
            END LOOP;
        END LOOP;

        -- Product gallery. On-brand labeled placeholders (3 distinct swatches)
        -- until real photos exist; swap these urls to publish real images.
        DELETE FROM product_media WHERE product_id = pid;
        FOR i IN 0..2 LOOP
            INSERT INTO product_media (id, product_id, variant_id, url, alt, kind, position)
            VALUES (
                uuid_generate_v5(ns, 'media:' || rec.slug || ':' || i),
                pid, NULL,
                'https://placehold.co/600x600/' ||
                    (ARRAY['0D1B2A/0da589', '1B263B/3AC2A9', '415A77/E0E1DD'])[i + 1] ||
                    '?text=' || replace(rec.name, ' ', '+') || '+' || (i + 1),
                rec.name, 'image', i
            );
        END LOOP;
    END LOOP;
END $$;

-- Storefront coupon advertised in the UI (footer/hero): 10% off, no minimum.
INSERT INTO coupons (code, discount_type, value, min_cart_cents, active)
VALUES ('JAVA10', 'percent', 10, 0, true)
ON CONFLICT (code) DO UPDATE SET
    discount_type = EXCLUDED.discount_type,
    value = EXCLUDED.value,
    min_cart_cents = EXCLUDED.min_cart_cents,
    active = EXCLUDED.active;

-- Demo markdowns ("preço de"): a few products carry a higher compare_at_price so
-- the storefront shows the struck-through price and the discount badge.
UPDATE products SET compare_at_price_cents = 9990  WHERE slug = 'camiseta-java-developer';
UPDATE products SET compare_at_price_cents = 17990 WHERE slug = 'jaqueta-java-developer';
UPDATE products SET compare_at_price_cents = 5990  WHERE slug = 'caneca-java';