#!/bin/bash
echo "=== SneakerPicks Nightly Sync started at $(date) ==="
cd /var/www/sneakerpicks

# Snapshot current prices BEFORE sync
echo "=== Snapshotting prices ==="
docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks << 'PRICE_SQL'
INSERT INTO price_history (product_id, price, feed_id, recorded_at)
SELECT po.product_id, MIN(po.price), po.feed_id, CURRENT_DATE
FROM product_offers po
WHERE po.in_stock = true
GROUP BY po.product_id, po.feed_id
ON CONFLICT DO NOTHING;
SELECT 'Price snapshots recorded: ' || COUNT(DISTINCT product_id) FROM price_history WHERE recorded_at::date = CURRENT_DATE;
PRICE_SQL

python3 scripts/feed-sync-v4.py 2>&1

# Bol.com feed (FTP download + v2 importer with EAN matching)
echo "=== Bol.com Feed Sync ==="
if bash scripts/download-bol-feed.sh 2>&1; then
    python3 scripts/import-bol-feed-v2.py --feed /var/www/sneakerpicks/data/bol-footwear-feed.csv.gz 2>&1
else
    echo "WARNING: Bol.com feed download failed, skipping import"
fi

# Re-apply is_sneaker filter + name cleanup for new products
docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks << 'SQL'
-- Count before cleanup
SELECT 'Sneakers BEFORE cleanup: ' || COUNT(*) FROM products WHERE is_sneaker = true;

UPDATE products SET is_sneaker = true WHERE is_sneaker IS NULL;

-- Re-evaluate false positives: products incorrectly marked false that have sneaker keywords
UPDATE products SET is_sneaker = true WHERE is_sneaker = false AND (
  LOWER(name) LIKE '%sneaker%' OR LOWER(name) LIKE '%trainer%'
  OR LOWER(name) LIKE '%air max%' OR LOWER(name) LIKE '%air force%'
  OR LOWER(name) LIKE '%dunk%' OR LOWER(name) LIKE '%jordan%'
  OR LOWER(name) LIKE '%ultraboost%' OR LOWER(name) LIKE '%superstar%'
  OR LOWER(name) LIKE '%stan smith%' OR LOWER(name) LIKE '%gazelle%'
  OR LOWER(name) LIKE '%samba%' OR LOWER(name) LIKE '%campus%'
  OR LOWER(name) LIKE '%spezial%' OR LOWER(name) LIKE '%old skool%'
  OR LOWER(name) LIKE '% uno %' OR LOWER(name) LIKE '%uno-%'
  OR LOWER(name) LIKE '%strada%' OR LOWER(name) LIKE '%574%'
  OR LOWER(name) LIKE '%990%' OR LOWER(name) LIKE '%fresh foam%'
  OR LOWER(name) LIKE '%blazer%' OR LOWER(name) LIKE '%cortez%'
  OR LOWER(name) LIKE '%pegasus%' OR LOWER(name) LIKE '%gel-%'
  OR LOWER(name) LIKE '%runner%' OR LOWER(name) LIKE '%running%'
  OR LOWER(name) LIKE '%chuck taylor%' OR LOWER(name) LIKE '%all star%'
  OR LOWER(name) LIKE '%slip-ins%' OR LOWER(name) LIKE '%skech-%'
) AND LOWER(name) NOT LIKE '%pantoffel%' AND LOWER(name) NOT LIKE '%slipper%'
  AND LOWER(name) NOT LIKE '%laars%' AND LOWER(name) NOT LIKE '%sandal%'
  AND LOWER(name) NOT LIKE '%regenlaar%';

UPDATE products SET is_sneaker = false WHERE is_sneaker = true AND google_product_category IS NOT NULL AND google_product_category != '187';
UPDATE products SET is_sneaker = false WHERE is_sneaker = true AND (
  -- Non-sneaker footwear
  LOWER(name) LIKE '%laars%' OR LOWER(name) LIKE '%boot%' OR LOWER(name) LIKE '%sandal%'
  OR LOWER(name) LIKE '%muiltje%' OR LOWER(name) LIKE '%pumps%' OR LOWER(name) LIKE '%hak%'
  OR LOWER(name) LIKE '%ballerina%' OR LOWER(name) LIKE '%mocassin%' OR LOWER(name) LIKE '%loafer%'
  OR LOWER(name) LIKE '%slipper%' OR LOWER(name) LIKE '%pantoffel%' OR LOWER(name) LIKE '%clog%'
  OR LOWER(name) LIKE '%espadrille%' OR LOWER(name) LIKE '%cowboy%'
  OR LOWER(name) LIKE '%instapper%' OR LOWER(name) LIKE '%laarzen%'
  -- Clothing
  OR LOWER(name) LIKE '%sportjack%' OR LOWER(name) LIKE '%regenjack%' OR LOWER(name) LIKE '%windjack%'
  OR LOWER(name) LIKE '%bodysuit%' OR LOWER(name) LIKE '%skort%' OR LOWER(name) LIKE '%track top%'
  OR LOWER(name) LIKE '%short%' OR LOWER(name) LIKE '%jurk%' OR LOWER(name) LIKE '%dress%'
  OR LOWER(name) LIKE '%jogging%' OR LOWER(name) LIKE '%pant %' OR LOWER(name) LIKE '%pants%'
  OR LOWER(name) LIKE '%tight%' OR LOWER(name) LIKE '%beha%' OR LOWER(name) LIKE '%tank %'
  OR LOWER(name) LIKE '%longsleeve%' OR LOWER(name) LIKE '%mouw%' OR LOWER(name) LIKE '%skirt%'
  OR LOWER(name) LIKE '%shirt%' OR LOWER(name) LIKE '%hoodie%' OR LOWER(name) LIKE '%sweater%'
  OR LOWER(name) LIKE '%trui%' OR LOWER(name) LIKE '%trainingspak%' OR LOWER(name) LIKE '%replica%'
  OR LOWER(name) LIKE '%jersey%' OR LOWER(name) LIKE '%jacket%'
  OR LOWER(name) LIKE '%voetbalschoen%' OR LOWER(name) LIKE '%handbalschoen%'
  OR LOWER(name) LIKE '%jack %'
  -- Accessories
  OR LOWER(name) LIKE '%rugzak%' OR LOWER(name) LIKE '%sokken%' OR LOWER(name) LIKE '%sokkken%'
  OR LOWER(name) LIKE '% tas %' OR LOWER(name) LIKE '% tas' OR LOWER(name) LIKE '%backpack%'
  OR LOWER(name) LIKE '%veters%' OR LOWER(name) LIKE '%veter %'
  OR LOWER(name) LIKE '%inlegzool%' OR LOWER(name) LIKE '%inlegzolen%' OR LOWER(name) LIKE '%insole%'
  OR LOWER(name) LIKE '%schoenlepel%' OR LOWER(name) LIKE '%shoehorn%'
  OR LOWER(name) LIKE '%spray%' OR LOWER(name) LIKE '%crème%' OR LOWER(name) LIKE '%creme%'
  OR LOWER(name) LIKE '%borstel%' OR LOWER(name) LIKE '%brush%'
  OR LOWER(name) LIKE '%pet %' OR LOWER(name) LIKE '%cap %' OR LOWER(name) LIKE '%muts%'
  OR LOWER(name) LIKE '%handschoen%' OR LOWER(name) LIKE '%riem%' OR LOWER(name) LIKE '%belt%'
  OR LOWER(name) LIKE '%portemonnee%' OR LOWER(name) LIKE '%wallet%'
  OR LOWER(name) LIKE '%accessoire%' OR LOWER(name) LIKE '%zonnebril%' OR LOWER(name) LIKE '%sunglasses%'
  OR LOWER(name) LIKE '%horloge%' OR LOWER(name) LIKE '%watch %'
  OR LOWER(name) LIKE '%parfum%' OR LOWER(name) LIKE '%fragrance%' OR LOWER(name) LIKE '%deodorant%'
  OR LOWER(name) LIKE '%sleutelhanger%' OR LOWER(name) LIKE '%keychain%'
  OR LOWER(name) LIKE '%telefoonhoesje%' OR LOWER(name) LIKE '%phone case%'
  -- Shoe care
  OR LOWER(name) LIKE '%schoenverzorging%' OR LOWER(name) LIKE '%shoe care%'
  OR LOWER(name) LIKE '%schoenpoets%' OR LOWER(name) LIKE '%cleaner%'
  OR LOWER(name) LIKE '%impregneer%' OR LOWER(name) LIKE '%protector%'
  OR LOWER(name) LIKE '%schoenenrek%' OR LOWER(name) LIKE '%schoenspanner%'
);
UPDATE products SET is_sneaker = false WHERE is_sneaker = true AND google_product_category IS NULL
  AND LOWER(name) NOT LIKE '%sneaker%' AND LOWER(name) NOT LIKE '%trainer%'
  AND LOWER(name) NOT LIKE '%schoen%' AND LOWER(name) NOT LIKE '%shoe%'
  AND LOWER(name) NOT LIKE '%running%' AND LOWER(name) NOT LIKE '%air max%'
  AND LOWER(name) NOT LIKE '%air force%' AND LOWER(name) NOT LIKE '%dunk%'
  AND LOWER(name) NOT LIKE '%jordan%' AND LOWER(name) NOT LIKE '%gel-%'
  AND LOWER(name) NOT LIKE '%ultraboost%' AND LOWER(name) NOT LIKE '%superstar%'
  AND LOWER(name) NOT LIKE '%stan smith%' AND LOWER(name) NOT LIKE '%gazelle%'
  AND LOWER(name) NOT LIKE '%samba%' AND LOWER(name) NOT LIKE '%campus%'
  AND LOWER(name) NOT LIKE '%spezial%' AND LOWER(name) NOT LIKE '%handball%'
  AND LOWER(name) NOT LIKE '%old skool%' AND LOWER(name) NOT LIKE '%chuck taylor%'
  AND LOWER(name) NOT LIKE '%all star%' AND LOWER(name) NOT LIKE '%574%'
  AND LOWER(name) NOT LIKE '%990%' AND LOWER(name) NOT LIKE '%fresh foam%'
  AND LOWER(name) NOT LIKE '%blazer%' AND LOWER(name) NOT LIKE '%cortez%'
  AND LOWER(name) NOT LIKE '%pegasus%' AND LOWER(name) NOT LIKE '%cloud%'
  AND LOWER(name) NOT LIKE '%hoka%' AND LOWER(name) NOT LIKE '%runner%'
  AND LOWER(name) NOT LIKE '%retro%' AND LOWER(name) NOT LIKE '%court %'
  AND LOWER(name) NOT LIKE '%boost%' AND LOWER(name) NOT LIKE '%slide%'
  AND LOWER(name) NOT LIKE '%skate%' AND LOWER(name) NOT LIKE '%hardloop%';
UPDATE products SET name = regexp_replace(name, ' - US[WM]?[0-9]+[.,][0-9],EU[0-9]+[.,][0-9],UK[0-9]+[.,][0-9]$', '')
  WHERE name ~ '- US[WM]?[0-9]+[.,][0-9],EU[0-9]';
SELECT 'Total sneakers: ' || COUNT(*) FROM products WHERE is_sneaker = true;
SQL

# Dedup: merge products with same EAN into one, keeping the one with more offers
echo "=== Deduplicating products ==="
docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks << 'DEDUP_SQL'
WITH dupes AS (
  SELECT ean, array_agg(id ORDER BY (SELECT COUNT(*) FROM product_offers WHERE product_id = products.id) DESC) as ids
  FROM products
  WHERE ean IS NOT NULL AND ean != '' AND is_sneaker = true
  GROUP BY ean HAVING COUNT(*) > 1
),
to_merge AS (
  SELECT ean, ids[1] as keep_id, unnest(ids[2:]) as remove_id
  FROM dupes
)
-- Move offers from duplicates to the keeper
UPDATE product_offers SET product_id = tm.keep_id
FROM to_merge tm
WHERE product_offers.product_id = tm.remove_id
  AND NOT EXISTS (SELECT 1 FROM product_offers po2 WHERE po2.product_id = tm.keep_id AND po2.feed_id = product_offers.feed_id AND po2.url = product_offers.url);

-- Delete orphaned offers (same feed+url already exists on keeper)
DELETE FROM product_offers WHERE product_id IN (SELECT remove_id FROM to_merge tm JOIN dupes d ON d.ean = tm.ean);

-- Soft-delete duplicate products
UPDATE products SET is_sneaker = false WHERE id IN (
  SELECT remove_id FROM (
    SELECT ean, array_agg(id ORDER BY (SELECT COUNT(*) FROM product_offers WHERE product_id = products.id) DESC) as ids
    FROM products WHERE ean IS NOT NULL AND ean != '' AND is_sneaker = true
    GROUP BY ean HAVING COUNT(*) > 1
  ) d, unnest(ids[2:]) as remove_id
);
SELECT 'Dedup complete. Remaining sneakers: ' || COUNT(*) FROM products WHERE is_sneaker = true;
DEDUP_SQL

# Feed monitoring: check that sync produced results
echo "=== Feed Monitoring ==="
docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks << 'MONITOR_SQL'
SELECT 'Active feeds: ' || COUNT(*) FROM feeds WHERE is_active = true;
SELECT 'Feeds with 0 offers: ' || COUNT(*)
FROM feeds f
WHERE f.is_active = true
  AND NOT EXISTS (SELECT 1 FROM product_offers po WHERE po.feed_id = f.id AND po.in_stock = true);
SELECT 'Total in-stock offers: ' || COUNT(*) FROM product_offers WHERE in_stock = true;
SELECT f.name, COUNT(po.id) as offers
FROM feeds f LEFT JOIN product_offers po ON po.feed_id = f.id AND po.in_stock = true
WHERE f.is_active = true
GROUP BY f.name ORDER BY offers ASC LIMIT 5;
MONITOR_SQL

# Process P1 cutouts as part of the pipeline (small batch)
echo "=== Starting P1 cutout processing ==="
python3 scripts/process-cutouts.py --batch 200 --priority p1 --model isnet-general-use 2>&1

# Update clicks_7d from click_events + page_views
echo "=== Updating clicks_7d ==="
docker exec -i supabase-db psql -U supabase_admin -d sneakerpicks << 'CLICKS_SQL'
UPDATE products p SET clicks_7d = COALESCE(stats.total, 0)
FROM (
  SELECT id, (
    COALESCE((SELECT COUNT(*) FROM click_events ce WHERE ce.product_id = id::text AND ce.created_at >= NOW() - INTERVAL '7 days'), 0) * 3
    + COALESCE((SELECT COUNT(*) FROM page_views pv WHERE pv.product_id = id AND pv.created_at >= NOW() - INTERVAL '7 days'), 0)
  ) AS total
  FROM products
) stats
WHERE p.id = stats.id;
SELECT 'Products with clicks_7d > 0: ' || COUNT(*) FROM products WHERE clicks_7d > 0;
CLICKS_SQL

# Sync Google Search Console data
echo "=== GSC Sync ==="
if [ -n "$GOOGLE_REFRESH_TOKEN" ]; then
  npx tsx scripts/gsc-sync.ts 2>&1
else
  echo "GOOGLE_REFRESH_TOKEN not set, skipping GSC sync"
fi

# Auto-publish scheduled blog posts
echo "=== Auto-publish blogs ==="
export $(grep -v '^#' .env | xargs)
npx tsx scripts/auto-publish-blogs.ts 2>&1

# Regenerate sitemaps
echo "=== Regenerating sitemaps ==="
python3 scripts/generate-sitemaps.py 2>&1

# Regenerate llms.txt
echo "=== Regenerating llms.txt ==="
python3 scripts/generate-llms-txt.py 2>&1

# Submit changed URLs to IndexNow (Bing/Yandex)
echo "=== IndexNow Submission ==="
if [ -n "$INDEXNOW_KEY" ]; then
  npx tsx scripts/submit-indexnow.ts 2>&1
else
  echo "INDEXNOW_KEY not set, skipping IndexNow"
fi

# Check price alerts and send notifications
echo "=== Checking price alerts ==="
npx tsx scripts/check-price-alerts.ts 2>&1

echo "=== Nightly Sync finished at $(date) ==="