-- Migration: Clean up slugs and create redirects
-- Run with: docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks < migrate-slugs.sql

BEGIN;

-- 1. Create slug_redirects table
CREATE TABLE IF NOT EXISTS slug_redirects (
  old_slug TEXT PRIMARY KEY,
  new_slug TEXT NOT NULL,
  product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_slug_redirects_new ON slug_redirects(new_slug);

-- 2. Compute new slugs in a temp table
CREATE TEMP TABLE slug_mapping AS
WITH base_slugs AS (
  SELECT 
    id,
    slug AS old_slug,
    -- Build new base slug: brand-model-colorway (cleaned)
    trim(both '-' from 
      regexp_replace(
        regexp_replace(
          lower(
            COALESCE(brand, '') || '-' || 
            regexp_replace(
              regexp_replace(
                regexp_replace(
                  regexp_replace(
                    COALESCE(NULLIF(model, name), name, ''),
                    '\d{13,}', '', 'g'  -- remove EAN
                  ),
                  '[0-9a-f]{8,}', '', 'gi'  -- remove hex hashes
                ),
                '(US|EU|UK)[WM]?\d[\d.]*/?', '', 'gi'  -- remove sizes
              ),
              '\|.*$', ''  -- remove pipe junk
            ) || 
            CASE 
              WHEN COALESCE(colorway, '') != '' AND colorway != model AND colorway != name
              THEN '-' || regexp_replace(COALESCE(colorway, ''), '[0-9a-f]{8,}', '', 'gi')
              ELSE ''
            END
          ),
          '[^a-z0-9]+', '-', 'g'  -- slugify
        ),
        '-{2,}', '-', 'g'  -- dedupe dashes
      )
    ) AS base_slug,
    created_at
  FROM products
),
-- Add row number per base_slug to handle duplicates
numbered AS (
  SELECT 
    id,
    old_slug,
    base_slug,
    ROW_NUMBER() OVER (PARTITION BY base_slug ORDER BY created_at ASC, id ASC) AS rn
  FROM base_slugs
  WHERE length(base_slug) >= 5
)
SELECT 
  id,
  old_slug,
  CASE WHEN rn = 1 THEN base_slug ELSE base_slug || '-' || rn END AS new_slug
FROM numbered;

-- Check stats before applying
SELECT 'Products to update' as info, COUNT(*)::text as val FROM slug_mapping WHERE old_slug != new_slug
UNION ALL
SELECT 'Products unchanged', COUNT(*)::text FROM slug_mapping WHERE old_slug = new_slug
UNION ALL
SELECT 'Total mapped', COUNT(*)::text FROM slug_mapping;

-- 3. Insert redirects for changed slugs
INSERT INTO slug_redirects (old_slug, new_slug, product_id)
SELECT old_slug, new_slug, id 
FROM slug_mapping 
WHERE old_slug != new_slug
ON CONFLICT (old_slug) DO UPDATE SET new_slug = EXCLUDED.new_slug, product_id = EXCLUDED.product_id;

-- 4. Update product slugs
UPDATE products p
SET slug = sm.new_slug, updated_at = NOW()
FROM slug_mapping sm
WHERE p.id = sm.id AND p.slug != sm.new_slug;

-- 5. Fix blog links: /sneaker/ → /sneakers/ 
UPDATE blog_posts 
SET content = regexp_replace(content, '/sneaker/', '/sneakers/', 'g'),
    updated_at = NOW()
WHERE content LIKE '%/sneaker/%';

-- 6. Fix blog links: update old slugs to new slugs in blog content
-- Do this iteratively for each blog post that contains old slugs
DO $$
DECLARE
  bp RECORD;
  sr RECORD;
  new_content TEXT;
  changed BOOLEAN;
BEGIN
  FOR bp IN SELECT id, content FROM blog_posts LOOP
    new_content := bp.content;
    changed := false;
    FOR sr IN 
      SELECT old_slug, new_slug FROM slug_redirects 
      WHERE bp.content LIKE '%' || old_slug || '%'
    LOOP
      new_content := replace(new_content, sr.old_slug, sr.new_slug);
      changed := true;
    END LOOP;
    IF changed THEN
      UPDATE blog_posts SET content = new_content, updated_at = NOW() WHERE id = bp.id;
    END IF;
  END LOOP;
END $$;

-- 7. Stats
SELECT 'Total products' as metric, COUNT(*)::text as value FROM products
UNION ALL
SELECT 'Slug redirects created', COUNT(*)::text FROM slug_redirects
UNION ALL
SELECT 'Products still with hash suffix', COUNT(*)::text FROM products WHERE slug ~ '-[0-9a-f]{6,8}$'
UNION ALL
SELECT 'Sample new slugs', string_agg(slug, ', ') FROM (SELECT slug FROM products ORDER BY random() LIMIT 5) t;

COMMIT;
