migration.sql

-- ============================================================
-- SUPLECOST MINIMAL PRODUCTION SCHEMA
-- ============================================================
-- Generated from exhaustive codebase analysis.
-- Contains ONLY database objects actually used by the code.
-- ============================================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ============================================================
-- 1. BRANDS
-- ============================================================
CREATE TABLE IF NOT EXISTS brands (
  id                TEXT PRIMARY KEY,        -- slug: "optimum-nutrition"
  name              TEXT NOT NULL,
  slug              TEXT NOT NULL UNIQUE,
  description       TEXT,
  logo_url          TEXT,
  website           TEXT,
  country_of_origin TEXT,
  is_featured       BOOLEAN NOT NULL DEFAULT false,
  meta_title        TEXT,
  meta_description  TEXT,
  product_count     INTEGER NOT NULL DEFAULT 0,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_brands_slug ON brands(slug);

-- ============================================================
-- 2. CATEGORIES
-- ============================================================
CREATE TABLE IF NOT EXISTS categories (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  slug            TEXT NOT NULL UNIQUE,
  description     TEXT,
  purpose_text    TEXT,
  icon            TEXT,
  image_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_slug ON categories(slug);

-- ============================================================
-- 3. RETAILERS
-- ============================================================
CREATE TABLE IF NOT EXISTS retailers (
  id                TEXT PRIMARY KEY,
  name              TEXT NOT NULL,
  slug              TEXT NOT NULL UNIQUE,
  logo_url          TEXT,
  website           TEXT,
  affiliate_network TEXT,
  commission_rate   DOUBLE PRECISION,
  cookie_days       INTEGER DEFAULT 30,
  is_active         BOOLEAN NOT NULL DEFAULT true,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 4. PRODUCTS
-- ============================================================
CREATE TABLE IF NOT EXISTS products (
  id                    TEXT PRIMARY KEY,
  name                  TEXT NOT NULL,
  slug                  TEXT NOT NULL UNIQUE,
  brand_id              TEXT NOT NULL REFERENCES brands(id) ON DELETE RESTRICT,
  category_id           TEXT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
  description           TEXT,
  short_description     TEXT,
  images                TEXT[] NOT NULL DEFAULT '{}',
  flavor                TEXT[] NOT NULL DEFAULT '{}',
  net_weight            TEXT,
  serving_size          TEXT,
  total_servings        INTEGER,
  cost_per_serving      DOUBLE PRECISION,
  protein_per_serving   DOUBLE PRECISION,
  creatine_per_serving  DOUBLE PRECISION,
  carbs_per_serving     DOUBLE PRECISION,
  fats_per_serving      DOUBLE PRECISION,
  calories_per_serving  INTEGER,
  key_ingredients       TEXT[] NOT NULL DEFAULT '{}',
  full_ingredients_list TEXT,
  ingredients           JSONB,
  nutrition             JSONB,
  benefits              TEXT[] NOT NULL DEFAULT '{}',
  usage_instructions    TEXT,
  best_time_to_use      TEXT,
  warnings              TEXT,
  is_vegetarian         BOOLEAN NOT NULL DEFAULT false,
  is_vegan              BOOLEAN NOT NULL DEFAULT false,
  certifications        TEXT[] NOT NULL DEFAULT '{}',
  lab_tested            BOOLEAN NOT NULL DEFAULT false,
  lab_test_details      TEXT,
  country_of_origin     TEXT,
  manufacturing_standard TEXT,
  upc_ean               TEXT,
  goals                 TEXT[] NOT NULL DEFAULT '{}',
  best_price            DOUBLE PRECISION,
  original_price        DOUBLE PRECISION,
  discount_percentage   DOUBLE PRECISION NOT NULL DEFAULT 0,
  is_active             BOOLEAN NOT NULL DEFAULT true,
  published_at          TIMESTAMPTZ,
  meta_title            TEXT,
  meta_description      TEXT,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Product search indexes
CREATE INDEX IF NOT EXISTS idx_products_slug ON products(slug);
CREATE INDEX IF NOT EXISTS idx_products_brand_id ON products(brand_id);
CREATE INDEX IF NOT EXISTS idx_products_category_id ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_products_best_price ON products(best_price);
CREATE INDEX IF NOT EXISTS idx_products_discount ON products(discount_percentage DESC);
CREATE INDEX IF NOT EXISTS idx_products_is_active ON products(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_products_goals ON products USING gin (goals);
CREATE INDEX IF NOT EXISTS idx_products_upc_ean ON products(upc_ean) WHERE upc_ean IS NOT NULL;

-- Full-text search vector
ALTER TABLE products ADD COLUMN IF NOT EXISTS search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english',
      coalesce(name, '') || ' ' ||
      coalesce(short_description, '') || ' ' ||
      coalesce(description, '')
    )
  ) STORED;

CREATE INDEX IF NOT EXISTS idx_products_search ON products USING gin (search_vector);

-- Trigram indexes for fuzzy search
CREATE INDEX IF NOT EXISTS idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- Composite indexes for common filtered queries
CREATE INDEX IF NOT EXISTS idx_products_cat_price ON products(category_id, best_price);
CREATE INDEX IF NOT EXISTS idx_products_brand_cat ON products(brand_id, category_id);

-- ============================================================
-- 5. PRODUCT PRICES
-- ============================================================
CREATE TABLE IF NOT EXISTS product_prices (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id          TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  retailer_id         TEXT NOT NULL REFERENCES retailers(id) ON DELETE RESTRICT,
  current_price       DOUBLE PRECISION NOT NULL,
  original_price      DOUBLE PRECISION,
  discount_percentage DOUBLE PRECISION NOT NULL DEFAULT 0,
  in_stock            BOOLEAN NOT NULL DEFAULT true,
  is_main_link        BOOLEAN NOT NULL DEFAULT false,
  url                 TEXT,
  last_checked        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(product_id, retailer_id)
);

CREATE INDEX IF NOT EXISTS idx_product_prices_product ON product_prices(product_id);
CREATE INDEX IF NOT EXISTS idx_product_prices_retailer ON product_prices(retailer_id);
CREATE INDEX IF NOT EXISTS idx_product_prices_retailer_product ON product_prices(retailer_id, product_id);
CREATE INDEX IF NOT EXISTS idx_product_prices_current_price ON product_prices(current_price);

-- ============================================================
-- 6. PRICE HISTORY
-- ============================================================
CREATE TABLE IF NOT EXISTS price_history (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id  TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  retailer_id TEXT NOT NULL REFERENCES retailers(id) ON DELETE RESTRICT,
  price       DOUBLE PRECISION NOT NULL,
  date        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_price_history_product ON price_history(product_id);
CREATE INDEX IF NOT EXISTS idx_price_history_product_date ON price_history(product_id, date DESC);

-- ============================================================
-- 7. AFFILIATE LINKS
-- ============================================================
CREATE TABLE IF NOT EXISTS affiliate_links (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id    TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  retailer_id   TEXT NOT NULL REFERENCES retailers(id) ON DELETE RESTRICT,
  raw_url       TEXT NOT NULL,
  cloaked_path  TEXT NOT NULL UNIQUE,
  click_count   INTEGER NOT NULL DEFAULT 0,
  is_active     BOOLEAN NOT NULL DEFAULT true,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(product_id, retailer_id)
);

CREATE INDEX IF NOT EXISTS idx_affiliate_links_cloaked ON affiliate_links(cloaked_path);
CREATE INDEX IF NOT EXISTS idx_affiliate_links_product ON affiliate_links(product_id);

-- ============================================================
-- 8. AFFILIATE CLICKS
-- ============================================================
CREATE TABLE IF NOT EXISTS affiliate_clicks (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  affiliate_link_id UUID NOT NULL REFERENCES affiliate_links(id) ON DELETE CASCADE,
  product_id        TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  retailer_id       TEXT NOT NULL REFERENCES retailers(id) ON DELETE RESTRICT,
  ip_address        TEXT,
  user_agent        TEXT,
  referrer          TEXT,
  country           TEXT,
  clicked_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_affiliate_clicks_link ON affiliate_clicks(affiliate_link_id);
CREATE INDEX IF NOT EXISTS idx_affiliate_clicks_product ON affiliate_clicks(product_id);
CREATE INDEX IF NOT EXISTS idx_affiliate_clicks_retailer ON affiliate_clicks(retailer_id);
CREATE INDEX IF NOT EXISTS idx_affiliate_clicks_clicked_at ON affiliate_clicks(clicked_at DESC);

-- ============================================================
-- 9. DYNAMIC FILTER SYSTEM
-- ============================================================
CREATE TABLE IF NOT EXISTS filter_groups (
  id              TEXT PRIMARY KEY,        -- "goals", "dietary", "certifications"
  name            TEXT NOT NULL,
  description     TEXT,
  icon            TEXT,
  display_order   INTEGER NOT NULL DEFAULT 0,
  is_active       BOOLEAN NOT NULL DEFAULT true,
  category_id     TEXT REFERENCES categories(id) ON DELETE SET NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS filter_options (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  group_id        TEXT NOT NULL REFERENCES filter_groups(id) ON DELETE CASCADE,
  value           TEXT NOT NULL,
  label           TEXT NOT NULL,
  display_order   INTEGER NOT NULL DEFAULT 0,
  is_active       BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(group_id, value)
);

CREATE TABLE IF NOT EXISTS product_filter_values (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id        TEXT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  filter_option_id  UUID NOT NULL REFERENCES filter_options(id) ON DELETE CASCADE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(product_id, filter_option_id)
);

CREATE INDEX IF NOT EXISTS idx_filter_options_group ON filter_options(group_id);
CREATE INDEX IF NOT EXISTS idx_filter_options_active ON filter_options(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_product_filter_values_product ON product_filter_values(product_id);
CREATE INDEX IF NOT EXISTS idx_product_filter_values_option ON product_filter_values(filter_option_id);

-- ============================================================
-- 10. FILTER PRESETS
-- ============================================================
CREATE TABLE IF NOT EXISTS filter_presets (
  id              TEXT PRIMARY KEY,         -- "budget", "rating", "sort", "brand"
  label           TEXT NOT NULL,
  description     TEXT,
  type            TEXT NOT NULL DEFAULT 'preset',
  options         JSONB NOT NULL DEFAULT '[]',
  is_active       BOOLEAN NOT NULL DEFAULT true,
  display_order   INTEGER NOT NULL DEFAULT 0,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 11. AI RECOMMENDATION LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS ai_recommendation_logs (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  query           TEXT NOT NULL DEFAULT '',
  goal            TEXT,
  budget          DOUBLE PRECISION,
  results_count   INTEGER NOT NULL DEFAULT 0,
  top_product_id  TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ai_logs_created ON ai_recommendation_logs(created_at DESC);

-- ============================================================
-- FUNCTIONS & TRIGGERS
-- ============================================================

-- Auto-update updated_at for any table
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_brands_updated_at
  BEFORE UPDATE ON brands FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_categories_updated_at
  BEFORE UPDATE ON categories FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_products_updated_at
  BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_product_prices_updated_at
  BEFORE UPDATE ON product_prices FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_affiliate_links_updated_at
  BEFORE UPDATE ON affiliate_links FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_retailers_updated_at
  BEFORE UPDATE ON retailers FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_filter_groups_updated_at
  BEFORE UPDATE ON filter_groups FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_filter_options_updated_at
  BEFORE UPDATE ON filter_options FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER trigger_filter_presets_updated_at
  BEFORE UPDATE ON filter_presets FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Increment click count function
CREATE OR REPLACE FUNCTION increment_click_count(link_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE affiliate_links SET click_count = click_count + 1, updated_at = NOW()
  WHERE id = link_id;
END;
$$ LANGUAGE plpgsql;

-- Auto-capture price history when a price changes
CREATE OR REPLACE FUNCTION capture_price_history()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.current_price IS DISTINCT FROM NEW.current_price THEN
    INSERT INTO price_history (product_id, retailer_id, price, date)
    VALUES (NEW.product_id, NEW.retailer_id, NEW.current_price, NOW());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_product_prices_capture_history
  AFTER INSERT OR UPDATE ON product_prices
  FOR EACH ROW
  EXECUTE FUNCTION capture_price_history();

-- Auto-update product best_price, original_price, discount_percentage, cost_per_serving
CREATE OR REPLACE FUNCTION update_product_aggregated_prices()
RETURNS TRIGGER AS $$
DECLARE
  min_price DOUBLE PRECISION;
  max_original DOUBLE PRECISION;
  calc_discount DOUBLE PRECISION;
  target_product_id TEXT;
  v_total_servings INTEGER;
BEGIN
  target_product_id := COALESCE(NEW.product_id, OLD.product_id);

  SELECT MIN(current_price), MAX(original_price)
  INTO min_price, max_original
  FROM product_prices
  WHERE product_id = target_product_id AND in_stock = true;

  IF min_price IS NOT NULL THEN
    calc_discount := CASE
      WHEN max_original > 0 AND max_original > min_price
      THEN ROUND(((max_original - min_price) / max_original) * 100)
      ELSE 0
    END;

    -- Get total_servings to calculate cost_per_serving
    SELECT total_servings INTO v_total_servings
    FROM products
    WHERE id = target_product_id;

    UPDATE products SET
      best_price = min_price,
      original_price = max_original,
      discount_percentage = calc_discount,
      cost_per_serving = CASE
        WHEN v_total_servings IS NOT NULL AND v_total_servings > 0 AND min_price > 0
        THEN ROUND((min_price / v_total_servings)::numeric, 2)
        ELSE cost_per_serving
      END,
      updated_at = NOW()
    WHERE id = target_product_id;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_product_prices_aggregate
  AFTER INSERT OR UPDATE OR DELETE ON product_prices
  FOR EACH ROW
  EXECUTE FUNCTION update_product_aggregated_prices();

-- Auto-update product_count on brands (used by FilterSidebar)
CREATE OR REPLACE FUNCTION update_product_counts()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE brands SET product_count = (
    SELECT COUNT(*) FROM products WHERE brand_id = COALESCE(NEW.brand_id, OLD.brand_id) AND is_active = true
  ) WHERE id = COALESCE(NEW.brand_id, OLD.brand_id);

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_products_update_counts
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW
  EXECUTE FUNCTION update_product_counts();

-- ============================================================
-- SEED DATA — RETAILERS
-- ============================================================
INSERT INTO retailers (id, name, slug, website, affiliate_network, commission_rate, cookie_days) VALUES
  ('amazon',       'Amazon India',     'amazon',       'https://www.amazon.in',    'amazon-pa-api',    0.03, 30),
  ('flipkart',     'Flipkart',         'flipkart',     'https://www.flipkart.com', 'flipkart-affiliate', 0.05, 30),
  ('healthkart',   'HealthKart',       'healthkart',   'https://www.healthkart.com', 'direct', 0.08, 30),
  ('myprotein',    'MyProtein India',  'myprotein',    'https://www.myprotein.co.in', 'direct', 0.10, 30),
  ('nutrabay',     'NutraBay',         'nutrabay',     'https://www.nutrabay.com',  'direct', 0.07, 30),
  ('supplementsexpert', 'Supplements Expert', 'supplementsexpert',
    'https://www.supplementsexpert.com', 'direct', 0.06, 30),
  ('fitbakes',     'Fitbakes',         'fitbakes',     'https://www.fitbakes.in',  'direct', 0.07, 30),
  ('healthysays',  'Healthy Says',     'healthysays',  'https://www.healthysays.com', 'direct', 0.05, 30)
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- SEED DATA — CATEGORIES
-- ============================================================
INSERT INTO categories (id, name, slug, icon, description) VALUES
  ('whey-protein',       'Whey Protein',       'whey-protein',       '💪', 'Fast-absorbing protein for muscle recovery'),
  ('isolate-protein',    'Isolate Protein',    'isolate-protein',    '🏋️', 'Pure protein with minimal carbs and fats'),
  ('casein-protein',     'Casein Protein',     'casein-protein',     '🌙', 'Slow-digesting protein for overnight recovery'),
  ('plant-protein',      'Plant Protein',      'plant-protein',      '🌱', 'Vegan-friendly plant-based protein'),
   ('mass-gainers-weight-gainers', 'Mass Gainers / Weight Gainers', 'mass-gainers-weight-gainers', 'MWG', 'High-calorie blends for weight and muscle gain'),
  ('creatine',           'Creatine',           'creatine',           '⚡', 'Strength and power supplements'),
  ('fish-oil',           'Fish Oil',           'fish-oil',           '🐟', 'Omega-3 fatty acids for joint and heart health'),
  ('pre-workout',        'Pre-Workout',        'pre-workout',        '🚀', 'Energy and focus boosters for training'),
  ('bcaa',               'BCAA',               'bcaa',               '🧬', 'Branched-chain amino acids'),
  ('multivitamins',      'Multivitamins',      'multivitamins',      '💊', 'Daily vitamin and mineral support'),
  ('fat-burners',        'Fat Burners',        'fat-burners',        '🔥', 'Thermogenic formulas for fat loss')
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- SEED DATA — FILTER GROUPS AND OPTIONS
-- ============================================================
INSERT INTO filter_groups (id, name, description, display_order) VALUES
  ('goals', 'Goal', 'Fitness and health goals', 1),
  ('dietary', 'Dietary', 'Dietary preferences', 2),
  ('certifications', 'Certifications', 'Quality certifications and seals', 3),
  ('ai-settings', 'AI Settings', 'AI recommendation engine configuration', 999)
ON CONFLICT (id) DO NOTHING;

INSERT INTO filter_options (group_id, value, label, display_order) VALUES
  -- Goals
  ('goals', 'muscle-building', 'Muscle Building', 1),
  ('goals', 'weight-gain', 'Weight Gain', 2),
  ('goals', 'fat-loss', 'Fat Loss', 3),
  ('goals', 'recovery', 'Recovery', 4),
  ('goals', 'strength', 'Strength', 5),
  ('goals', 'endurance', 'Endurance', 6),
  ('goals', 'energy', 'Energy', 7),
  ('goals', 'focus', 'Focus', 8),
  ('goals', 'immunity', 'Immunity', 9),
  -- Dietary
  ('dietary', 'vegetarian', 'Vegetarian', 1),
  ('dietary', 'vegan', 'Vegan', 2),
  ('dietary', 'gluten-free', 'Gluten Free', 3),
  ('dietary', 'sugar-free', 'Sugar Free', 4),
  ('dietary', 'keto-friendly', 'Keto Friendly', 5),
  -- Certifications
  ('certifications', 'informed-choice', 'Informed Choice', 1),
  ('certifications', 'banned-substance-tested', 'Banned Substance Tested', 2),
  ('certifications', 'labdoor-certified', 'Labdoor Certified', 3),
  ('certifications', 'trustified', 'Trustified', 4),
  ('certifications', 'heavy-metal-tested', 'Heavy Metal Tested', 5),
  ('certifications', 'usp-verified', 'USP Verified', 6),
  ('certifications', 'nsf-certified', 'NSF Certified', 7),
  ('certifications', 'gmp-certified', 'GMP Certified', 8),
  ('certifications', 'iso-certified', 'ISO Certified', 9),
  ('certifications', 'fssai-approved', 'Fssai Approved', 10),
  -- AI Settings (seed)
  ('ai-settings', 'ai-weights', '{"weights":{"goalMatch":33,"budgetFit":27,"dietaryMatch":20,"proteinScore":20},"maxRecommendations":10}', 1)
ON CONFLICT (group_id, value) DO NOTHING;

-- ============================================================
-- SEED DATA — FILTER PRESETS
-- ============================================================
INSERT INTO filter_presets (id, label, description, type, options, display_order) VALUES
  ('budget', 'Budget', 'Price range filters', 'budget', '[
    {"label":"Under ₹500","max":500},
    {"label":"₹500 - ₹1,000","min":500,"max":1000},
    {"label":"₹1,000 - ₹2,000","min":1000,"max":2000},
    {"label":"₹2,000 - ₹5,000","min":2000,"max":5000},
    {"label":"Above ₹5,000","min":5000,"max":null}
  ]'::jsonb, 1),
  ('brand', 'Brand', 'Brand filter (dynamically loaded from brands table)', 'brand', '[]'::jsonb, 3)
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- STORAGE BUCKET SETUP
-- ============================================================
-- Run this separately via the Supabase dashboard or script:
-- CREATE BUCKET product-images WITH (public = true);
-- Set allowed MIME types: image/jpeg, image/png, image/webp, image/gif
-- Set file size limit: 5242880 (5MB)

-- ============================================================
-- RLS POLICIES
-- ============================================================
-- The application uses supabaseAdmin (service role key) for ALL
-- server-side operations, which bypasses RLS entirely.
-- The anon key is exposed to the client but no client-side
-- queries go directly to the database.
--
-- For defense-in-depth, enable RLS and set restrictive defaults:

-- Enable RLS on all tables
ALTER TABLE brands ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE retailers ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_prices ENABLE ROW LEVEL SECURITY;
ALTER TABLE price_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE affiliate_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE affiliate_clicks ENABLE ROW LEVEL SECURITY;
ALTER TABLE filter_groups ENABLE ROW LEVEL SECURITY;
ALTER TABLE filter_options ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_filter_values ENABLE ROW LEVEL SECURITY;
ALTER TABLE filter_presets ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_recommendation_logs ENABLE ROW LEVEL SECURITY;

-- Public anon users can read published data
CREATE POLICY "anon_can_read_brands" ON brands FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_categories" ON categories FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_retailers" ON retailers FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_active_products" ON products FOR SELECT TO anon USING (is_active = true);
CREATE POLICY "anon_can_read_product_prices" ON product_prices FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_price_history" ON price_history FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_filter_groups" ON filter_groups FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_filter_options" ON filter_options FOR SELECT TO anon USING (true);
CREATE POLICY "anon_can_read_filter_presets" ON filter_presets FOR SELECT TO anon USING (true);

-- Affiliate and analytics tables are write-only for anon (click tracking)
CREATE POLICY "anon_can_insert_affiliate_clicks" ON affiliate_clicks FOR INSERT TO anon WITH CHECK (true);
CREATE POLICY "anon_can_select_affiliate_links" ON affiliate_links FOR SELECT TO anon USING (is_active = true);

-- ============================================================
-- HOME PROMO BANNERS (admin-managed hero carousel)
-- ============================================================
CREATE TABLE IF NOT EXISTS home_promo (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  image_url TEXT NOT NULL,
  link_url TEXT NOT NULL DEFAULT '',
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE home_promo ENABLE ROW LEVEL SECURITY;

-- Anon can read active banners
CREATE POLICY "anon_can_read_home_promo" ON home_promo
  FOR SELECT TO anon
  USING (is_active = true);

-- Trigger to auto-update updated_at
CREATE TRIGGER update_home_promo_updated_at
  BEFORE UPDATE ON home_promo
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- SCRAPER FINGERPRINT COLUMNS (on product_prices)
-- ============================================================
ALTER TABLE product_prices ADD COLUMN IF NOT EXISTS scraped_title TEXT;
ALTER TABLE product_prices ADD COLUMN IF NOT EXISTS scraped_weight TEXT;
ALTER TABLE product_prices ADD COLUMN IF NOT EXISTS product_fingerprint TEXT;
ALTER TABLE product_prices ADD COLUMN IF NOT EXISTS last_fetch_status TEXT NOT NULL DEFAULT 'ok';
ALTER TABLE price_history ADD COLUMN IF NOT EXISTS flags TEXT[] NOT NULL DEFAULT '{}';

-- ============================================================
-- VISITOR LOG & SITE STATS
-- ============================================================

CREATE TABLE IF NOT EXISTS visitor_log (
  ip_address TEXT PRIMARY KEY,
  first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS site_stats (
  id    TEXT PRIMARY KEY,   -- 'unique_visitors'
  value BIGINT NOT NULL DEFAULT 0
);

INSERT INTO site_stats (id, value) VALUES ('unique_visitors', 50000)
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- FUNCTION: increment_unique_visitor(ip TEXT)
-- ============================================================
CREATE OR REPLACE FUNCTION increment_unique_visitor(p_ip TEXT)
RETURNS BIGINT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_total BIGINT;
BEGIN
  INSERT INTO visitor_log (ip_address) VALUES (p_ip)
  ON CONFLICT (ip_address) DO NOTHING;

  IF FOUND THEN
    UPDATE site_stats SET value = value + 1 WHERE id = 'unique_visitors';
  END IF;

  SELECT value INTO v_total FROM site_stats WHERE id = 'unique_visitors';
  RETURN v_total;
END;
$$;

-- ============================================================
-- INCREMENT CLICK COUNT FUNCTION (TEXT overload for cloaked paths)
-- ============================================================
CREATE OR REPLACE FUNCTION increment_click_count(link_id TEXT)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE affiliate_links
  SET click_count = COALESCE(click_count, 0) + 1
  WHERE id = link_id;
END;
$$;

-- ============================================================
-- AUTO-CALCULATE COST PER SERVING
-- Formula: cost_per_serving = best_price / total_servings
-- Runs BEFORE insert/update when best_price or total_servings changes
-- ============================================================

CREATE OR REPLACE FUNCTION auto_calculate_cost_per_serving()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.total_servings IS NOT NULL AND NEW.total_servings > 0 THEN
    IF NEW.best_price IS NOT NULL AND NEW.best_price > 0 THEN
      NEW.cost_per_serving := ROUND((NEW.best_price / NEW.total_servings)::numeric, 2);
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_products_auto_cost_per_serving ON products;

CREATE TRIGGER trigger_products_auto_cost_per_serving
  BEFORE INSERT OR UPDATE OF best_price, total_servings ON products
  FOR EACH ROW
  EXECUTE FUNCTION auto_calculate_cost_per_serving();

-- Service role bypasses all RLS (handled by Supabase internally)