-- Migration 003: Categorization & Smart Collections
-- Run: psql -d sneakerpicks -f db/migrations/003_categorization_tables.sql

BEGIN;

-- ============================================================
-- CATEGORIES
-- ============================================================

CREATE TABLE IF NOT EXISTS categories (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          TEXT UNIQUE NOT NULL,
  name_en       TEXT NOT NULL,
  name_nl       TEXT NOT NULL,
  dimension     TEXT NOT NULL CHECK (dimension IN ('activity', 'style', 'brand_family', 'gender')),
  parent_id     UUID REFERENCES categories(id),
  depth         INT NOT NULL DEFAULT 0,
  sort_order    INT NOT NULL DEFAULT 0,
  icon_url      TEXT,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories(parent_id);
CREATE INDEX IF NOT EXISTS idx_categories_dimension ON categories(dimension);
CREATE INDEX IF NOT EXISTS idx_categories_slug ON categories(slug);

-- ============================================================
-- PRODUCT <-> CATEGORY (many-to-many)
-- ============================================================

CREATE TABLE IF NOT EXISTS product_categories (
  product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id   UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  is_primary    BOOLEAN NOT NULL DEFAULT FALSE,
  confidence    REAL NOT NULL DEFAULT 1.0,
  source        TEXT NOT NULL DEFAULT 'rule' CHECK (source IN ('rule', 'ml', 'manual')),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (product_id, category_id)
);

CREATE INDEX IF NOT EXISTS idx_pc_product ON product_categories(product_id);
CREATE INDEX IF NOT EXISTS idx_pc_category ON product_categories(category_id);

-- ============================================================
-- TAGS
-- ============================================================

CREATE TABLE IF NOT EXISTS tags (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          TEXT UNIQUE NOT NULL,
  name_en       TEXT NOT NULL,
  name_nl       TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS product_tags (
  product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  tag_id        UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  source        TEXT NOT NULL DEFAULT 'rule',
  PRIMARY KEY (product_id, tag_id)
);

-- ============================================================
-- COLLECTIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS collections (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          TEXT UNIQUE NOT NULL,
  name_en       TEXT NOT NULL,
  name_nl       TEXT NOT NULL,
  description   TEXT,
  type          TEXT NOT NULL CHECK (type IN ('smart', 'manual', 'hybrid')),
  rules_json    JSONB,
  sort_by       TEXT NOT NULL DEFAULT 'relevance',
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  is_featured   BOOLEAN NOT NULL DEFAULT FALSE,
  homepage_sort INT,
  image_url     TEXT,
  max_products  INT,
  cache_ttl_sec INT NOT NULL DEFAULT 300,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS collection_products (
  collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
  product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sort_score    REAL NOT NULL DEFAULT 0,
  added_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (collection_id, product_id)
);

CREATE INDEX IF NOT EXISTS idx_cp_collection_sort ON collection_products(collection_id, sort_score DESC);

CREATE TABLE IF NOT EXISTS collection_overrides (
  collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
  product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  action        TEXT NOT NULL CHECK (action IN ('include', 'exclude', 'pin')),
  pin_position  INT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (collection_id, product_id)
);

-- ============================================================
-- FEED CATEGORY MAPPINGS
-- ============================================================

CREATE TABLE IF NOT EXISTS feed_category_map (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  shop            TEXT NOT NULL,
  feed_category   TEXT NOT NULL,
  our_category_id UUID REFERENCES categories(id),
  confidence      REAL NOT NULL DEFAULT 1.0,
  UNIQUE(shop, feed_category)
);

-- ============================================================
-- CATEGORIZATION RULES
-- ============================================================

CREATE TABLE IF NOT EXISTS categorization_rules (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  priority      INT NOT NULL DEFAULT 100,
  conditions    JSONB NOT NULL,
  actions       JSONB NOT NULL,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  hit_count     BIGINT NOT NULL DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_rules_priority ON categorization_rules(priority) WHERE is_active;

-- ============================================================
-- HOMEPAGE SECTIONS
-- ============================================================

CREATE TABLE IF NOT EXISTS homepage_sections (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type          TEXT NOT NULL CHECK (type IN ('collection', 'editors_picks', 'banner', 'brands', 'categories')),
  collection_id UUID REFERENCES collections(id),
  title_en      TEXT,
  title_nl      TEXT,
  sort_order    INT NOT NULL DEFAULT 0,
  max_items     INT NOT NULL DEFAULT 12,
  layout        TEXT NOT NULL DEFAULT 'carousel',
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  config_json   JSONB,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS featured_products (
  section_id    UUID NOT NULL REFERENCES homepage_sections(id) ON DELETE CASCADE,
  product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sort_order    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (section_id, product_id)
);

-- ============================================================
-- ADD COLUMNS TO PRODUCTS TABLE (if needed)
-- ============================================================

DO $$ BEGIN
  ALTER TABLE products ADD COLUMN IF NOT EXISTS brand TEXT;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS model_name TEXT;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS gender TEXT;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS original_price NUMERIC;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS clicks_48h INT DEFAULT 0;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS clicks_7d INT DEFAULT 0;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS conversions_30d INT DEFAULT 0;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS first_seen_at TIMESTAMPTZ;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS categorized_at TIMESTAMPTZ;
  ALTER TABLE products ADD COLUMN IF NOT EXISTS categorization_version INT DEFAULT 0;
EXCEPTION WHEN undefined_table THEN
  -- products table doesn't exist yet, skip
  NULL;
END $$;

-- ============================================================
-- SEED: Default Taxonomy
-- ============================================================

-- Activity categories
INSERT INTO categories (slug, name_en, name_nl, dimension, depth, sort_order) VALUES
  ('lifestyle', 'Lifestyle', 'Lifestyle', 'activity', 0, 1),
  ('running', 'Running', 'Hardlopen', 'activity', 0, 2),
  ('basketball', 'Basketball', 'Basketbal', 'activity', 0, 3),
  ('skateboarding', 'Skateboarding', 'Skateboarden', 'activity', 0, 4),
  ('training', 'Training & Gym', 'Training & Gym', 'activity', 0, 5),
  ('football', 'Football / Soccer', 'Voetbal', 'activity', 0, 6),
  ('tennis', 'Tennis / Padel', 'Tennis / Padel', 'activity', 0, 7),
  ('walking', 'Walking / Outdoor', 'Wandelen / Outdoor', 'activity', 0, 8)
ON CONFLICT (slug) DO NOTHING;

-- Activity subcategories
INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('lifestyle/casual', 'Casual', 'Casual', 'activity', (SELECT id FROM categories WHERE slug = 'lifestyle'), 1, 1),
  ('lifestyle/streetwear', 'Fashion / Streetwear', 'Fashion / Streetwear', 'activity', (SELECT id FROM categories WHERE slug = 'lifestyle'), 1, 2),
  ('running/road', 'Road Running', 'Wegrennen', 'activity', (SELECT id FROM categories WHERE slug = 'running'), 1, 1),
  ('running/trail', 'Trail Running', 'Trail Running', 'activity', (SELECT id FROM categories WHERE slug = 'running'), 1, 2),
  ('running/racing', 'Racing / Competition', 'Wedstrijd', 'activity', (SELECT id FROM categories WHERE slug = 'running'), 1, 3),
  ('basketball/performance', 'Performance', 'Performance', 'activity', (SELECT id FROM categories WHERE slug = 'basketball'), 1, 1),
  ('basketball/lifestyle', 'Lifestyle / Off-court', 'Lifestyle / Off-court', 'activity', (SELECT id FROM categories WHERE slug = 'basketball'), 1, 2),
  ('skateboarding/low', 'Low-top', 'Low-top', 'activity', (SELECT id FROM categories WHERE slug = 'skateboarding'), 1, 1),
  ('skateboarding/high', 'High-top', 'High-top', 'activity', (SELECT id FROM categories WHERE slug = 'skateboarding'), 1, 2),
  ('training/cross', 'Cross-training', 'Cross-training', 'activity', (SELECT id FROM categories WHERE slug = 'training'), 1, 1),
  ('training/weightlifting', 'Weightlifting', 'Gewichtheffen', 'activity', (SELECT id FROM categories WHERE slug = 'training'), 1, 2),
  ('football/indoor', 'Indoor', 'Indoor', 'activity', (SELECT id FROM categories WHERE slug = 'football'), 1, 1),
  ('football/turf', 'Turf / Street', 'Turf / Straat', 'activity', (SELECT id FROM categories WHERE slug = 'football'), 1, 2),
  ('walking/hiking', 'Hiking', 'Wandelen', 'activity', (SELECT id FROM categories WHERE slug = 'walking'), 1, 1),
  ('walking/sandals', 'Sandals', 'Sandalen', 'activity', (SELECT id FROM categories WHERE slug = 'walking'), 1, 2)
ON CONFLICT (slug) DO NOTHING;

-- Style categories
INSERT INTO categories (slug, name_en, name_nl, dimension, depth, sort_order) VALUES
  ('retro', 'Retro / Heritage', 'Retro / Heritage', 'style', 0, 1),
  ('limited', 'Limited / Collab', 'Limited / Collab', 'style', 0, 2),
  ('high-top', 'High-top', 'High-top', 'style', 0, 3),
  ('low-top', 'Low-top', 'Low-top', 'style', 0, 4),
  ('mid-top', 'Mid-top', 'Mid-top', 'style', 0, 5),
  ('platform', 'Platform / Chunky', 'Platform / Chunky', 'style', 0, 6)
ON CONFLICT (slug) DO NOTHING;

INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('retro/70s-80s', '70s & 80s Classics', '70s & 80s Classics', 'style', (SELECT id FROM categories WHERE slug = 'retro'), 1, 1),
  ('retro/90s', '90s Retro', '90s Retro', 'style', (SELECT id FROM categories WHERE slug = 'retro'), 1, 2),
  ('retro/2000s', '2000s Retro', '2000s Retro', 'style', (SELECT id FROM categories WHERE slug = 'retro'), 1, 3),
  ('limited/designer', 'Designer Collabs', 'Designer Collabs', 'style', (SELECT id FROM categories WHERE slug = 'limited'), 1, 1),
  ('limited/artist', 'Artist Collabs', 'Artist Collabs', 'style', (SELECT id FROM categories WHERE slug = 'limited'), 1, 2),
  ('limited/editions', 'Limited Editions', 'Limited Editions', 'style', (SELECT id FROM categories WHERE slug = 'limited'), 1, 3)
ON CONFLICT (slug) DO NOTHING;

-- Gender categories
INSERT INTO categories (slug, name_en, name_nl, dimension, depth, sort_order) VALUES
  ('men', 'Men', 'Heren', 'gender', 0, 1),
  ('women', 'Women', 'Dames', 'gender', 0, 2),
  ('unisex', 'Unisex', 'Unisex', 'gender', 0, 3),
  ('kids', 'Kids', 'Kinderen', 'gender', 0, 4)
ON CONFLICT (slug) DO NOTHING;

INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('kids/boys', 'Boys', 'Jongens', 'gender', (SELECT id FROM categories WHERE slug = 'kids'), 1, 1),
  ('kids/girls', 'Girls', 'Meisjes', 'gender', (SELECT id FROM categories WHERE slug = 'kids'), 1, 2),
  ('kids/toddler', 'Toddler', 'Peuter', 'gender', (SELECT id FROM categories WHERE slug = 'kids'), 1, 3),
  ('kids/infant', 'Infant', 'Baby', 'gender', (SELECT id FROM categories WHERE slug = 'kids'), 1, 4)
ON CONFLICT (slug) DO NOTHING;

-- Brand/Model Family categories
INSERT INTO categories (slug, name_en, name_nl, dimension, depth, sort_order) VALUES
  ('nike', 'Nike', 'Nike', 'brand_family', 0, 1),
  ('adidas', 'Adidas', 'Adidas', 'brand_family', 0, 2),
  ('new-balance', 'New Balance', 'New Balance', 'brand_family', 0, 3),
  ('puma', 'Puma', 'Puma', 'brand_family', 0, 4),
  ('converse', 'Converse', 'Converse', 'brand_family', 0, 5),
  ('vans', 'Vans', 'Vans', 'brand_family', 0, 6),
  ('asics', 'ASICS', 'ASICS', 'brand_family', 0, 7),
  ('reebok', 'Reebok', 'Reebok', 'brand_family', 0, 8),
  ('on-running', 'On Running', 'On Running', 'brand_family', 0, 9),
  ('salomon', 'Salomon', 'Salomon', 'brand_family', 0, 10)
ON CONFLICT (slug) DO NOTHING;

-- Nike model families
INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('nike/air-max', 'Air Max', 'Air Max', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 1),
  ('nike/air-force-1', 'Air Force 1', 'Air Force 1', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 2),
  ('nike/dunk', 'Dunk', 'Dunk', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 3),
  ('nike/air-jordan', 'Air Jordan', 'Air Jordan', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 4),
  ('nike/blazer', 'Blazer', 'Blazer', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 5),
  ('nike/cortez', 'Cortez', 'Cortez', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 6),
  ('nike/pegasus', 'Vomero / Pegasus', 'Vomero / Pegasus', 'brand_family', (SELECT id FROM categories WHERE slug = 'nike'), 1, 7)
ON CONFLICT (slug) DO NOTHING;

-- Adidas model families
INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('adidas/superstar', 'Superstar', 'Superstar', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 1),
  ('adidas/stan-smith', 'Stan Smith', 'Stan Smith', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 2),
  ('adidas/forum', 'Forum', 'Forum', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 3),
  ('adidas/samba', 'Gazelle / Samba / Spezial', 'Gazelle / Samba / Spezial', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 4),
  ('adidas/ultraboost', 'Ultraboost', 'Ultraboost', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 5),
  ('adidas/nmd', 'NMD', 'NMD', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 6),
  ('adidas/yeezy', 'Yeezy', 'Yeezy', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 7),
  ('adidas/campus', 'Campus', 'Campus', 'brand_family', (SELECT id FROM categories WHERE slug = 'adidas'), 1, 8)
ON CONFLICT (slug) DO NOTHING;

-- New Balance model families
INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('new-balance/550', '550', '550', 'brand_family', (SELECT id FROM categories WHERE slug = 'new-balance'), 1, 1),
  ('new-balance/574', '574', '574', 'brand_family', (SELECT id FROM categories WHERE slug = 'new-balance'), 1, 2),
  ('new-balance/990', '990 Series', '990 Series', 'brand_family', (SELECT id FROM categories WHERE slug = 'new-balance'), 1, 3),
  ('new-balance/2002r', '2002R', '2002R', 'brand_family', (SELECT id FROM categories WHERE slug = 'new-balance'), 1, 4),
  ('new-balance/1906r', '1906R', '1906R', 'brand_family', (SELECT id FROM categories WHERE slug = 'new-balance'), 1, 5)
ON CONFLICT (slug) DO NOTHING;

-- Other brand model families
INSERT INTO categories (slug, name_en, name_nl, dimension, parent_id, depth, sort_order) VALUES
  ('puma/suede', 'Suede', 'Suede', 'brand_family', (SELECT id FROM categories WHERE slug = 'puma'), 1, 1),
  ('puma/rs-x', 'RS-X', 'RS-X', 'brand_family', (SELECT id FROM categories WHERE slug = 'puma'), 1, 2),
  ('converse/chuck-taylor', 'Chuck Taylor / All Star', 'Chuck Taylor / All Star', 'brand_family', (SELECT id FROM categories WHERE slug = 'converse'), 1, 1),
  ('converse/one-star', 'One Star', 'One Star', 'brand_family', (SELECT id FROM categories WHERE slug = 'converse'), 1, 2),
  ('vans/old-skool', 'Old Skool', 'Old Skool', 'brand_family', (SELECT id FROM categories WHERE slug = 'vans'), 1, 1),
  ('vans/sk8-hi', 'Sk8-Hi', 'Sk8-Hi', 'brand_family', (SELECT id FROM categories WHERE slug = 'vans'), 1, 2),
  ('vans/authentic', 'Authentic / Era', 'Authentic / Era', 'brand_family', (SELECT id FROM categories WHERE slug = 'vans'), 1, 3),
  ('asics/gel-lyte', 'Gel-Lyte III / V', 'Gel-Lyte III / V', 'brand_family', (SELECT id FROM categories WHERE slug = 'asics'), 1, 1),
  ('asics/gel-kayano', 'Gel-Kayano', 'Gel-Kayano', 'brand_family', (SELECT id FROM categories WHERE slug = 'asics'), 1, 2),
  ('asics/gel-1130', 'Gel-1130 / Gel-NYC', 'Gel-1130 / Gel-NYC', 'brand_family', (SELECT id FROM categories WHERE slug = 'asics'), 1, 3),
  ('reebok/classic-leather', 'Classic Leather', 'Classic Leather', 'brand_family', (SELECT id FROM categories WHERE slug = 'reebok'), 1, 1),
  ('reebok/club-c', 'Club C', 'Club C', 'brand_family', (SELECT id FROM categories WHERE slug = 'reebok'), 1, 2),
  ('on-running/cloud-5', 'Cloud 5', 'Cloud 5', 'brand_family', (SELECT id FROM categories WHERE slug = 'on-running'), 1, 1),
  ('on-running/cloudmonster', 'Cloudmonster', 'Cloudmonster', 'brand_family', (SELECT id FROM categories WHERE slug = 'on-running'), 1, 2),
  ('on-running/cloudnova', 'Cloudnova', 'Cloudnova', 'brand_family', (SELECT id FROM categories WHERE slug = 'on-running'), 1, 3),
  ('salomon/xt-6', 'XT-6', 'XT-6', 'brand_family', (SELECT id FROM categories WHERE slug = 'salomon'), 1, 1),
  ('salomon/speedcross', 'Speedcross', 'Speedcross', 'brand_family', (SELECT id FROM categories WHERE slug = 'salomon'), 1, 2),
  ('salomon/acs-pro', 'ACS Pro', 'ACS Pro', 'brand_family', (SELECT id FROM categories WHERE slug = 'salomon'), 1, 3)
ON CONFLICT (slug) DO NOTHING;

-- Default tags
INSERT INTO tags (slug, name_en, name_nl) VALUES
  ('retro', 'Retro', 'Retro'),
  ('sustainable', 'Sustainable / Eco', 'Duurzaam / Eco'),
  ('waterproof', 'Waterproof', 'Waterdicht'),
  ('gore-tex', 'Gore-Tex', 'Gore-Tex'),
  ('collab', 'Collab', 'Collab'),
  ('limited-edition', 'Limited Edition', 'Limited Edition'),
  ('vegan', 'Vegan', 'Vegan'),
  ('wide-fit', 'Wide Fit', 'Brede Pasvorm')
ON CONFLICT (slug) DO NOTHING;

-- ============================================================
-- SEED: Default Collections
-- ============================================================

INSERT INTO collections (slug, name_en, name_nl, description, type, rules_json, sort_by, is_active, is_featured, homepage_sort, max_products, cache_ttl_sec) VALUES
  ('sale', 'Sale', 'Sale', 'Products with 15%+ discount', 'smart',
    '{"operator":"AND","rules":[{"type":"field","field":"discount_pct","op":"gte","value":15},{"type":"field","field":"price","op":"gt","value":0}]}',
    'discount_desc', TRUE, TRUE, 6, NULL, 300),
  ('bestsellers', 'Bestsellers', 'Bestsellers', 'Top performing products', 'smart',
    '{"operator":"AND","rules":[{"type":"field","field":"conversions_30d","op":"gte","value":1}]}',
    'conversions_desc', TRUE, TRUE, 2, 50, 3600),
  ('trending', 'Trending', 'Trending', 'Most clicked in last 48h', 'smart',
    '{"operator":"AND","rules":[{"type":"field","field":"clicks_48h","op":"gte","value":5}]}',
    'clicks_48h_desc', TRUE, TRUE, 1, 40, 300),
  ('price-drops', 'Price Drops', 'Prijsdalingen', 'Price decreased vs 7 days ago', 'smart',
    '{"operator":"AND","rules":[{"type":"price_history","op":"dropped","period_days":7,"min_drop_pct":10}]}',
    'drop_pct_desc', TRUE, TRUE, 7, NULL, 600),
  ('new-arrivals', 'New Arrivals', 'Nieuw Binnen', 'Added in last 7 days', 'smart',
    '{"operator":"AND","rules":[{"type":"time","field":"first_seen_at","op":"within_days","value":7}]}',
    'newest', TRUE, TRUE, 3, NULL, 600),
  ('under-100', 'Under €100', 'Onder €100', 'Affordable sneakers under €100', 'smart',
    '{"operator":"AND","rules":[{"type":"field","field":"price","op":"lt","value":100},{"type":"field","field":"price","op":"gt","value":0}]}',
    'price_asc', TRUE, TRUE, 9, NULL, 600)
ON CONFLICT (slug) DO NOTHING;

COMMIT;
