#!/usr/bin/env python3
"""
SneakerPicks — Stale Product Cleanup

Deactivates products where ALL offers have last_seen_at > 7 days ago,
hard-deletes their stale offers, and removes orphan products.

Usage:
    python3 scripts/stale-cleanup.py              # dry-run
    python3 scripts/stale-cleanup.py --execute    # actually execute
"""

import argparse
import os
import sys
from datetime import datetime, timedelta

import psycopg2

STALE_DAYS = 7


def get_connection():
    url = os.environ.get("DATABASE_URL")
    if not url:
        print("ERROR: DATABASE_URL environment variable not set")
        sys.exit(1)
    return psycopg2.connect(url)


def run(execute: bool):
    conn = get_connection()
    cur = conn.cursor()
    cutoff = datetime.utcnow() - timedelta(days=STALE_DAYS)
    mode = "EXECUTE" if execute else "DRY-RUN"
    print(f"[{mode}] Stale cleanup — cutoff: {cutoff.isoformat()}")

    # 1. Find products where ALL offers are stale (last_seen_at < cutoff)
    #    Only consider products that are currently active (is_sneaker = true)
    cur.execute("""
        SELECT p.id, p.name
        FROM products p
        WHERE p.is_sneaker = true
          AND EXISTS (SELECT 1 FROM product_offers po WHERE po.product_id = p.id)
          AND NOT EXISTS (
              SELECT 1 FROM product_offers po
              WHERE po.product_id = p.id AND po.last_seen_at >= %s
          )
    """, (cutoff,))
    stale_products = cur.fetchall()
    stale_ids = [r[0] for r in stale_products]

    print(f"  Products to deactivate: {len(stale_ids)}")

    # 2. Count stale offers to delete for those products
    if stale_ids:
        cur.execute("""
            SELECT COUNT(*) FROM product_offers
            WHERE product_id = ANY(%s) AND last_seen_at < %s
        """, (stale_ids, cutoff))
        offers_to_delete = cur.fetchone()[0]
    else:
        offers_to_delete = 0

    print(f"  Stale offers to delete: {offers_to_delete}")

    # 3. Find orphan products (no offers at all)
    cur.execute("""
        SELECT p.id, p.name
        FROM products p
        WHERE p.is_sneaker = true
          AND NOT EXISTS (SELECT 1 FROM product_offers po WHERE po.product_id = p.id)
    """)
    orphans = cur.fetchall()
    orphan_ids = [r[0] for r in orphans]
    print(f"  Orphan products to deactivate: {len(orphan_ids)}")

    if not execute:
        print("\n  [DRY-RUN] No changes made. Use --execute to apply.")
        cur.close()
        conn.close()
        return

    # Execute changes
    if stale_ids:
        # Delete stale offers
        cur.execute("""
            DELETE FROM product_offers
            WHERE product_id = ANY(%s) AND last_seen_at < %s
        """, (stale_ids, cutoff))
        deleted_offers = cur.rowcount

        # Deactivate products
        cur.execute("""
            UPDATE products SET is_sneaker = false
            WHERE id = ANY(%s)
        """, (stale_ids,))
        deactivated = cur.rowcount
        print(f"  ✓ Deactivated {deactivated} products, deleted {deleted_offers} offers")

    # Deactivate orphans
    if orphan_ids:
        cur.execute("""
            UPDATE products SET is_sneaker = false
            WHERE id = ANY(%s)
        """, (orphan_ids,))
        print(f"  ✓ Deactivated {cur.rowcount} orphan products")

    conn.commit()
    cur.close()
    conn.close()
    print("  Done!")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="SneakerPicks stale product cleanup")
    parser.add_argument("--execute", action="store_true", help="Actually execute (default is dry-run)")
    args = parser.parse_args()
    run(execute=args.execute)