#!/usr/bin/env python3
"""
Generate llms.txt + llms-full.txt for SneakerPicks.nl
Industry-emerging standard for exposing site content to AI engines.

Output: /var/www/sneakerpicks/dist/client/llms.txt + llms-full.txt

Run: python3 scripts/generate-llms-txt.py
Cron: daily 05:45 (alongside sitemap generation)
Postbuild: called from deploy.sh after build wipes dist/client/
"""
import os, sys, io, time, re
from datetime import datetime, timezone

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace', line_buffering=True)

import psycopg2
import psycopg2.extras

DB_URL = os.environ.get("DATABASE_URL", "")
if not DB_URL:
    env_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '.env')
    if os.path.exists(env_path):
        with open(env_path) as f:
            for line in f:
                if line.strip().startswith('DATABASE_URL='):
                    DB_URL = line.strip().split('=', 1)[1]
    if not DB_URL:
        print("ERROR: DATABASE_URL not set"); sys.exit(1)

SITE_URL = "https://sneakerpicks.nl"
OUTPUT_DIR = "/var/www/sneakerpicks/dist/client"
TOP_BRANDS = 30
TOP_MODELS = 80


def strip_html(text):
    if not text:
        return ""
    return re.sub(r'<[^>]+>', '', text).strip()


def first_sentence(text, max_len=220):
    if not text:
        return ""
    text = text.strip().replace("\n", " ")
    text = re.sub(r'\s+', ' ', text)
    # Pick the first sentence, fall back to a hard cut
    match = re.match(r'^(.{20,' + str(max_len) + r'}?[.!?])\s', text + ' ')
    if match:
        return match.group(1)
    return (text[:max_len - 1] + '…') if len(text) > max_len else text


def main():
    t0 = time.time()
    print("=== SneakerPicks llms.txt Generator ===")
    print(f"  Output: {OUTPUT_DIR}")
    os.makedirs(OUTPUT_DIR, exist_ok=True)

    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    today = datetime.now(timezone.utc).strftime('%Y-%m-%d')

    # --- Pull metrics for the intro block ---
    cur.execute("SELECT COUNT(*) as c FROM products WHERE is_sneaker = true")
    total_products = cur.fetchone()['c']
    cur.execute("SELECT COUNT(DISTINCT feed_id) as c FROM product_offers WHERE in_stock = true")
    total_shops = cur.fetchone()['c']
    cur.execute("SELECT COUNT(*) as c FROM brands")
    total_brands = cur.fetchone()['c']

    # --- Top brands (by product count) ---
    cur.execute("""
        SELECT b.slug, b.name, COUNT(DISTINCT p.id) as cnt
        FROM brands b
        JOIN products p ON p.brand_id = b.id
        WHERE p.is_sneaker = true
        GROUP BY b.slug, b.name
        HAVING COUNT(DISTINCT p.id) >= 3
        ORDER BY cnt DESC
        LIMIT %s
    """, (TOP_BRANDS,))
    top_brands = list(cur.fetchall())

    # --- Top models (by in-stock product count) ---
    cur.execute("""
        SELECT b.slug as brand_slug,
               b.name as brand_name,
               TRIM(p.model) as model_name,
               LOWER(REGEXP_REPLACE(TRIM(p.model), '[^a-zA-Z0-9]+', '-', 'g')) as model_slug,
               COUNT(DISTINCT p.id) as cnt
        FROM products p
        JOIN brands b ON b.id = p.brand_id
        JOIN product_offers po ON po.product_id = p.id AND po.in_stock = true
        WHERE p.is_sneaker = true
          AND p.model IS NOT NULL AND TRIM(p.model) != ''
        GROUP BY b.slug, b.name, TRIM(p.model), LOWER(REGEXP_REPLACE(TRIM(p.model), '[^a-zA-Z0-9]+', '-', 'g'))
        HAVING COUNT(DISTINCT p.id) >= 2
        ORDER BY cnt DESC
        LIMIT %s
    """, (TOP_MODELS,))
    top_models = list(cur.fetchall())

    # --- Model content (for llms-full.txt long-form) ---
    cur.execute("""
        SELECT brand_slug, model_slug, pdp_body, intro, description
        FROM model_content
        WHERE pdp_body IS NOT NULL AND LENGTH(pdp_body) > 50
    """)
    model_content_rows = {(r['brand_slug'], r['model_slug']): r for r in cur.fetchall()}

    # --- Recent blog posts ---
    cur.execute("""
        SELECT slug, title, COALESCE(excerpt, meta_description) as summary,
               COALESCE(published_at, created_at)::date as date
        FROM blog_posts
        WHERE is_published = true AND slug IS NOT NULL
        ORDER BY published_at DESC NULLS LAST
        LIMIT 30
    """)
    blog_posts = list(cur.fetchall())

    # --- Write llms.txt (short, scannable) ---
    llms_path = os.path.join(OUTPUT_DIR, "llms.txt")
    with open(llms_path, 'w', encoding='utf-8') as f:
        f.write(f"# SneakerPicks\n\n")
        f.write(
            f"> SneakerPicks is een Nederlandse sneaker-prijsvergelijker. "
            f"We vergelijken live prijzen van {total_products:,} sneakers bij "
            f"{total_shops}+ Nederlandse webshops van {total_brands} merken, zodat bezoekers "
            f"per maat, kleur en model de laagste prijs vinden. Prijzen worden meerdere keren "
            f"per dag via affiliate-feeds (Awin, Webgains, TradeTracker, Daisycon, Bol direct) "
            f"ververst; raadpleeg altijd de live pagina voor de actuele prijs.\n\n"
        )
        f.write(f"Laatst bijgewerkt: {today}\n\n")

        f.write("## Belangrijk om te weten\n\n")
        f.write(
            "- Taal: Nederlands (nl-NL). Alle prijzen zijn inclusief BTW in EUR.\n"
            "- Verzending: verzendkosten staan op de winkel, niet op SneakerPicks.\n"
            "- Retour: Nederlandse winkels hanteren doorgaans 14 dagen EU-bedenktijd.\n"
            "- Data-actualiteit: offer-timestamps op productpagina's (schema.org `dateModified`) geven de meest recente feed-update weer.\n"
            "- Methodologie: zie /methodologie voor feed-bronnen en update-frequentie.\n\n"
        )

        f.write("## Hoofdsecties\n\n")
        f.write(f"- [Homepage]({SITE_URL}/): zoeken, trending, deals.\n")
        f.write(f"- [Alle sneakers]({SITE_URL}/sneakers): volledig doorzoekbare catalogus met filters (merk, maat, prijs, geslacht).\n")
        f.write(f"- [Merken]({SITE_URL}/merken): overzicht van alle {total_brands} merken.\n")
        f.write(f"- [Deals]({SITE_URL}/deals): sneakers die recent in prijs zijn gedaald.\n")
        f.write(f"- [Releases]({SITE_URL}/releases): binnenkort verwachte en nieuwe sneakers.\n")
        f.write(f"- [Blog]({SITE_URL}/blog): koopgidsen, vergelijkingen, trends en deals.\n")
        f.write(f"- [Methodologie]({SITE_URL}/methodologie): hoe prijzen worden verzameld en welke bronnen we gebruiken.\n")
        f.write(f"- [Over ons]({SITE_URL}/over-ons): missie, team, affiliate-disclosure.\n")
        f.write(f"- [Contact]({SITE_URL}/contact): vragen, webshop toevoegen, prijscorrecties.\n\n")

        f.write("## Populaire merken\n\n")
        for b in top_brands:
            f.write(f"- [{b['name']}]({SITE_URL}/merken/{b['slug']}): {b['cnt']} sneakers in de catalogus.\n")
        f.write("\n")

        f.write("## Populaire modellen\n\n")
        for m in top_models:
            if not m['model_slug'] or m['model_slug'] == '-':
                continue
            f.write(
                f"- [{m['brand_name']} {m['model_name']}]({SITE_URL}/merken/{m['brand_slug']}/{m['model_slug']}): "
                f"{m['cnt']} varianten beschikbaar.\n"
            )
        f.write("\n")

        if blog_posts:
            f.write("## Recente artikelen\n\n")
            for p in blog_posts[:15]:
                summary = first_sentence(strip_html(p['summary'] or ''), 160)
                date = p['date'].isoformat() if p['date'] else ''
                extra = f" ({date})" if date else ''
                f.write(f"- [{p['title']}]({SITE_URL}/blog/{p['slug']}){extra}: {summary}\n")
            f.write("\n")

        f.write("## Aanvullende bronnen\n\n")
        f.write(f"- Sitemap: {SITE_URL}/sitemap.xml\n")
        f.write(f"- Uitgebreide content: {SITE_URL}/llms-full.txt\n")
        f.write(f"- RSS: {SITE_URL}/blog/rss.xml\n")

    llms_size = os.path.getsize(llms_path)
    print(f"  llms.txt: {llms_size:,} bytes")

    # --- Write llms-full.txt (long-form for deep citation) ---
    full_path = os.path.join(OUTPUT_DIR, "llms-full.txt")
    with open(full_path, 'w', encoding='utf-8') as f:
        f.write(f"# SneakerPicks — Uitgebreide contentgids\n\n")
        f.write(f"Laatst bijgewerkt: {today}\n\n")
        f.write(
            f"SneakerPicks vergelijkt prijzen van {total_products:,} sneakers bij "
            f"{total_shops}+ Nederlandse webshops. Deze gids bevat de brand- en modelintroducties "
            f"zodat AI-engines SneakerPicks correct kunnen citeren bij vragen over sneakers in Nederland. "
            f"Prijzen zijn dynamisch; raadpleeg altijd de live productpagina voor de actuele prijs.\n\n"
        )

        f.write("## Merken\n\n")
        for b in top_brands:
            f.write(f"### {b['name']}\n\n")
            f.write(f"URL: {SITE_URL}/merken/{b['slug']}\n\n")
            f.write(f"Sneakers in catalogus: {b['cnt']}\n\n")
            # Model-level content for this brand
            models_for_brand = [m for m in top_models if m['brand_slug'] == b['slug']]
            if models_for_brand:
                model_names = ', '.join(m['model_name'] for m in models_for_brand[:8])
                f.write(f"Populaire modellen: {model_names}.\n\n")

        f.write("## Modellen\n\n")
        for m in top_models:
            if not m['model_slug'] or m['model_slug'] == '-':
                continue
            content = model_content_rows.get((m['brand_slug'], m['model_slug']))
            title = f"{m['brand_name']} {m['model_name']}"
            url = f"{SITE_URL}/merken/{m['brand_slug']}/{m['model_slug']}"
            f.write(f"### {title}\n\n")
            f.write(f"URL: {url}\n\n")
            f.write(f"Varianten beschikbaar: {m['cnt']}\n\n")
            if content:
                intro = strip_html(content['intro'] or '')
                body = strip_html(content['pdp_body'] or content['description'] or '')
                if intro:
                    f.write(f"{intro}\n\n")
                if body and body != intro:
                    f.write(f"{body}\n\n")
            else:
                f.write(
                    f"Vergelijk prijzen van de {title} bij Nederlandse webshops op SneakerPicks. "
                    f"Zie de live pagina voor actuele prijzen, maten en kleuren.\n\n"
                )

    full_size = os.path.getsize(full_path)
    print(f"  llms-full.txt: {full_size:,} bytes")

    cur.close()
    conn.close()

    elapsed = time.time() - t0
    print(
        f"\nDone in {elapsed:.1f}s. "
        f"Brands: {len(top_brands)}, Models: {len(top_models)}, Blog: {len(blog_posts)}"
    )


if __name__ == "__main__":
    main()