#!/usr/bin/env python3
"""
Generate XML sitemaps for SneakerPicks.nl
Produces: sitemap.xml (index), sitemap-products.xml, sitemap-brands.xml, sitemap-models.xml, sitemap-pages.xml, sitemap-blog.xml, sitemap-news.xml
Output: /var/www/sneakerpicks/dist/client/ (static files served by Astro SSR)

Run: python3 scripts/generate-sitemaps.py
Cron: daily at 05:45
"""
import os, sys, io, time
from datetime import datetime, timezone
from xml.etree.ElementTree import Element, SubElement, ElementTree

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"

XMLNS = "http://www.sitemaps.org/schemas/sitemap/0.9"


def write_xml(root, filename):
    path = os.path.join(OUTPUT_DIR, filename)
    tree = ElementTree(root)
    with open(path, 'wb') as f:
        tree.write(f, encoding='UTF-8', xml_declaration=True)
    size = os.path.getsize(path)
    print(f"  {filename}: {size:,} bytes")
    return path


def add_url(parent, loc, lastmod=None, changefreq=None, priority=None):
    url = SubElement(parent, "url")
    SubElement(url, "loc").text = loc
    if lastmod:
        SubElement(url, "lastmod").text = lastmod
    if changefreq:
        SubElement(url, "changefreq").text = changefreq
    if priority:
        SubElement(url, "priority").text = str(priority)


def main():
    t0 = time.time()
    print(f"=== SneakerPicks Sitemap 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)
    now = datetime.now(timezone.utc).strftime('%Y-%m-%d')

    # --- Static pages ---
    print("\n[1/8] Static pages...")
    root = Element("urlset", xmlns=XMLNS)
    static_pages = [
        ("/", "daily", "1.0"),
        ("/sneakers", "daily", "0.9"),
        ("/merken", "weekly", "0.8"),
        ("/deals", "daily", "0.9"),
        ("/releases", "daily", "0.8"),
        ("/blog", "weekly", "0.7"),
        ("/zoeken", "weekly", "0.5"),
        ("/over-ons", "monthly", "0.4"),
        ("/privacy", "monthly", "0.3"),
        ("/voorwaarden", "monthly", "0.3"),
        ("/contact", "monthly", "0.4"),
    ]
    for path, freq, prio in static_pages:
        add_url(root, f"{SITE_URL}{path}", now, freq, prio)
    write_xml(root, "sitemap-pages.xml")

    # --- Products (sneakers only, with offers) ---
    print("[2/8] Product pages...")
    root = Element("urlset", xmlns=XMLNS)
    cur.execute("""
        SELECT p.slug, MAX(po.updated_at)::date as lastmod
        FROM products p
        JOIN product_offers po ON po.product_id = p.id
        WHERE p.is_sneaker = true
          AND po.in_stock = true
          AND p.slug IS NOT NULL
        GROUP BY p.slug
        ORDER BY lastmod DESC NULLS LAST
    """)
    product_count = 0
    for row in cur.fetchall():
        lastmod = row['lastmod'].isoformat() if row['lastmod'] else now
        add_url(root, f"{SITE_URL}/sneakers/{row['slug']}", lastmod, "weekly", "0.7")
        product_count += 1
    write_xml(root, "sitemap-products.xml")
    print(f"    {product_count} products")

    # --- Brands ---
    print("[3/8] Brand pages...")
    root = Element("urlset", xmlns=XMLNS)
    cur.execute("""
        SELECT b.slug, MAX(po.updated_at)::date as lastmod
        FROM brands b
        JOIN products p ON p.brand_id = b.id
        LEFT JOIN product_offers po ON po.product_id = p.id
        WHERE p.is_sneaker = true
        GROUP BY b.slug
        HAVING COUNT(p.id) >= 3
        ORDER BY COUNT(p.id) DESC
    """)
    brand_count = 0
    for row in cur.fetchall():
        lastmod = row['lastmod'].isoformat() if row['lastmod'] else now
        add_url(root, f"{SITE_URL}/merken/{row['slug']}", lastmod, "weekly", "0.8")
        brand_count += 1
    write_xml(root, "sitemap-brands.xml")
    print(f"    {brand_count} brands")

    # --- Brand + Model pages ---
    print("[4/8] Brand model pages...")
    root = Element("urlset", xmlns=XMLNS)
    cur.execute("""
        SELECT b.slug as brand_slug,
               LOWER(REGEXP_REPLACE(TRIM(p.model), '[^a-zA-Z0-9]+', '-', 'g')) as model_slug,
               MAX(po.updated_at)::date as lastmod,
               COUNT(DISTINCT p.id) as cnt
        FROM products p
        JOIN brands b ON b.id = p.brand_id
        LEFT JOIN product_offers po ON po.product_id = p.id
        WHERE p.is_sneaker = true
          AND p.model IS NOT NULL AND TRIM(p.model) != ''
        GROUP BY b.slug, model_slug
        HAVING COUNT(DISTINCT p.id) >= 2
        ORDER BY cnt DESC
    """)
    model_count = 0
    for row in cur.fetchall():
        if not row['model_slug'] or row['model_slug'] == '-':
            continue
        lastmod = row['lastmod'].isoformat() if row['lastmod'] else now
        prio = "0.8" if row['cnt'] >= 5 else "0.7"
        add_url(root, f"{SITE_URL}/merken/{row['brand_slug']}/{row['model_slug']}", lastmod, "weekly", prio)
        model_count += 1
    write_xml(root, "sitemap-models.xml")
    print(f"    {model_count} model pages")

    # --- Color pages (brand/model/color) ---
    print("[5/8] Color pages...")
    root = Element("urlset", xmlns=XMLNS)
    cur.execute("""
        SELECT b.slug as brand_slug,
               LOWER(REGEXP_REPLACE(TRIM(p.model), '[^a-zA-Z0-9]+', '-', 'g')) as model_slug,
               LOWER(REGEXP_REPLACE(REGEXP_REPLACE(
                 COALESCE(NULLIF(TRIM(p.colorway_normalized), ''), NULLIF(TRIM(p.colorway), ''), 'overig'),
                 '[^a-zA-Z0-9]+', '-', 'g'), '(^-|-$)', '', 'g')) as color_slug,
               MAX(po.updated_at)::date as lastmod,
               COUNT(DISTINCT p.id) as cnt,
               MIN(po.price) as min_price
        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) != ''
          AND p.image_url IS NOT NULL AND p.image_url != ''
        GROUP BY b.slug, model_slug, color_slug
        HAVING COUNT(DISTINCT p.id) >= 1 AND MIN(po.price) > 0
        ORDER BY cnt DESC
    """)
    color_count = 0
    for row in cur.fetchall():
        if not row['model_slug'] or row['model_slug'] == '-':
            continue
        if not row['color_slug'] or row['color_slug'] == '-':
            continue
        lastmod = row['lastmod'].isoformat() if row['lastmod'] else now
        # Higher priority for pages with more products
        prio = "0.8" if row['cnt'] >= 3 else "0.6"
        add_url(root, f"{SITE_URL}/merken/{row['brand_slug']}/{row['model_slug']}/{row['color_slug']}", lastmod, "weekly", prio)
        color_count += 1
    write_xml(root, "sitemap-colors.xml")
    print(f"    {color_count} color pages")

    # --- Blog posts ---
    print("[6/8] Blog posts...")
    root = Element("urlset", xmlns=XMLNS)
    cur.execute("""
        SELECT slug, COALESCE(published_at, created_at)::date as lastmod
        FROM blog_posts
        WHERE is_published = true AND slug IS NOT NULL
        ORDER BY published_at DESC NULLS LAST
    """)
    blog_count = 0
    for row in cur.fetchall():
        lastmod = row['lastmod'].isoformat() if row['lastmod'] else now
        add_url(root, f"{SITE_URL}/blog/{row['slug']}", lastmod, "monthly", "0.6")
        blog_count += 1
    write_xml(root, "sitemap-blog.xml")
    print(f"    {blog_count} posts")

    # --- News Sitemap (blog posts < 48 hours, Google News protocol) ---
    print("[7/8] News sitemap...")
    XMLNS_NEWS = "http://www.google.com/schemas/sitemap-news/0.9"
    root = Element("urlset", xmlns=XMLNS)
    root.set("xmlns:news", XMLNS_NEWS)
    cur.execute("""
        SELECT slug, title, COALESCE(published_at, created_at) as pub_date
        FROM blog_posts
        WHERE is_published = true
          AND slug IS NOT NULL
          AND published_at >= NOW() - INTERVAL '48 hours'
        ORDER BY published_at DESC
    """)
    news_count = 0
    for row in cur.fetchall():
        url_el = SubElement(root, "url")
        SubElement(url_el, "loc").text = f"{SITE_URL}/blog/{row['slug']}"
        news_el = SubElement(url_el, "news:news")
        pub_el = SubElement(news_el, "news:publication")
        SubElement(pub_el, "news:name").text = "SneakerPicks"
        SubElement(pub_el, "news:language").text = "nl"
        pub_date = row['pub_date']
        if pub_date:
            SubElement(news_el, "news:publication_date").text = pub_date.strftime('%Y-%m-%dT%H:%M:%S+00:00')
        SubElement(news_el, "news:title").text = row['title']
        news_count += 1
    write_xml(root, "sitemap-news.xml")
    print(f"    {news_count} news articles (last 48h)")

    # --- Sitemap Index ---
    print("[8/8] Sitemap index...")
    root = Element("sitemapindex", xmlns=XMLNS)
    sitemap_files = [
        "sitemap-pages.xml", "sitemap-products.xml", "sitemap-brands.xml",
        "sitemap-models.xml", "sitemap-colors.xml", "sitemap-blog.xml", "sitemap-news.xml",
    ]
    for name in sitemap_files:
        sm = SubElement(root, "sitemap")
        SubElement(sm, "loc").text = f"{SITE_URL}/{name}"
        SubElement(sm, "lastmod").text = now
    write_xml(root, "sitemap.xml")

    cur.close()
    conn.close()

    elapsed = time.time() - t0
    print(f"\nDone in {elapsed:.1f}s. Products: {product_count}, Brands: {brand_count}, Models: {model_count}, Colors: {color_count}, Blog: {blog_count}, News: {news_count}")


if __name__ == "__main__":
    main()