#!/usr/bin/env python3
"""
SneakerPicks Product Deduplication Script

Finds and merges duplicate products across feeds using:
  Pass 1: EAN/GTIN exact match (highest confidence)
  Pass 2: match_fingerprint exact match
  Pass 3: Fuzzy brand+model+colorway match (report-only unless --fuzzy)

Usage:
  python scripts/dedup-products.py                  # dry-run report
  python scripts/dedup-products.py --execute        # execute pass 1+2
  python scripts/dedup-products.py --execute --fuzzy # also execute pass 3
  python scripts/dedup-products.py --report         # detailed report with examples
"""

import argparse
import os
import sys
from collections import defaultdict
from datetime import datetime

import psycopg2
import psycopg2.extras
from dotenv import load_dotenv

load_dotenv(os.path.join(os.path.dirname(__file__), '..', '.env.local'))
load_dotenv(os.path.join(os.path.dirname(__file__), '..', '.env'))

DATABASE_URL = os.environ.get('DATABASE_URL')
if not DATABASE_URL:
    print("ERROR: DATABASE_URL not set")
    sys.exit(1)


def get_conn():
    return psycopg2.connect(DATABASE_URL)


def levenshtein(s1, s2):
    if len(s1) < len(s2):
        return levenshtein(s2, s1)
    if len(s2) == 0:
        return len(s1)
    prev = range(len(s2) + 1)
    for i, c1 in enumerate(s1):
        curr = [i + 1]
        for j, c2 in enumerate(s2):
            curr.append(min(prev[j + 1] + 1, curr[j] + 1, prev[j] + (c1 != c2)))
        prev = curr
    return prev[-1]


def pick_master(products):
    """Pick master: most offers, then oldest (lowest id), then most complete."""
    return max(products, key=lambda p: (p['offer_count'], -p['id'], len(p['name'] or '')))


def find_ean_duplicates(cur):
    """Pass 1: Group products by EAN/GTIN."""
    cur.execute("""
        SELECT p.id, p.name, p.brand, p.brand_normalized, p.model_normalized,
               p.colorway, p.ean, p.gtin, p.match_fingerprint,
               COUNT(po.id) as offer_count
        FROM products p
        LEFT JOIN product_offers po ON po.product_id = p.id
        WHERE p.is_sneaker = true
          AND (p.ean IS NOT NULL AND p.ean != '' OR p.gtin IS NOT NULL AND p.gtin != '')
        GROUP BY p.id
        ORDER BY p.id
    """)
    products = [dict(r) for r in cur.fetchall()]

    groups = defaultdict(list)
    for p in products:
        key = p['ean'] or p['gtin']
        if key and key.strip():
            groups[key.strip()].append(p)

    return {k: v for k, v in groups.items() if len(v) > 1}


def find_fingerprint_duplicates(cur, exclude_ids=None):
    """Pass 2: Group by match_fingerprint."""
    exclude_ids = exclude_ids or set()
    cur.execute("""
        SELECT p.id, p.name, p.brand, p.brand_normalized, p.model_normalized,
               p.colorway, p.colorway_normalized, p.ean, p.gtin, p.match_fingerprint,
               COUNT(po.id) as offer_count
        FROM products p
        LEFT JOIN product_offers po ON po.product_id = p.id
        WHERE p.is_sneaker = true
          AND p.match_fingerprint IS NOT NULL
          AND p.match_fingerprint != ''
        GROUP BY p.id
        ORDER BY p.id
    """)
    products = [dict(r) for r in cur.fetchall()]

    groups = defaultdict(list)
    for p in products:
        if p['id'] in exclude_ids:
            continue
        groups[p['match_fingerprint']].append(p)

    return {k: v for k, v in groups.items() if len(v) > 1}


def find_fuzzy_duplicates(cur, exclude_ids=None):
    """Pass 3: Fuzzy match on brand_normalized + model_normalized + colorway."""
    exclude_ids = exclude_ids or set()
    cur.execute("""
        SELECT p.id, p.name, p.brand, p.brand_normalized, p.model_normalized,
               p.colorway, p.colorway_normalized, p.ean, p.gtin, p.match_fingerprint,
               COUNT(po.id) as offer_count
        FROM products p
        LEFT JOIN product_offers po ON po.product_id = p.id
        WHERE p.is_sneaker = true
          AND p.brand_normalized IS NOT NULL
          AND p.model_normalized IS NOT NULL
          AND p.model_normalized != ''
        GROUP BY p.id
        ORDER BY p.brand_normalized, p.model_normalized
    """)
    products = [dict(r) for r in cur.fetchall() if dict(r)['id'] not in exclude_ids]

    # Group by brand first to reduce comparisons
    by_brand = defaultdict(list)
    for p in products:
        by_brand[p['brand_normalized']].append(p)

    merge_groups = []
    for brand, prods in by_brand.items():
        # Sort by model for efficient comparison
        prods.sort(key=lambda x: x['model_normalized'] or '')
        matched = set()
        for i, p1 in enumerate(prods):
            if p1['id'] in matched:
                continue
            group = [p1]
            m1 = p1['model_normalized'] or ''
            c1 = (p1['colorway_normalized'] or '').strip()
            for j in range(i + 1, len(prods)):
                p2 = prods[j]
                if p2['id'] in matched:
                    continue
                m2 = p2['model_normalized'] or ''
                c2 = (p2['colorway_normalized'] or '').strip()

                # Colorway must match (both empty or same)
                if c1 != c2:
                    continue

                # Check fuzzy model match
                if m1 == m2:
                    continue  # exact match handled by fingerprint pass
                dist = levenshtein(m1, m2)
                is_substring = m1 in m2 or m2 in m1
                if dist < 3 or is_substring:
                    group.append(p2)
                    matched.add(p2['id'])

            if len(group) > 1:
                matched.add(p1['id'])
                merge_groups.append(group)

    return merge_groups


def merge_group(cur, group, dry_run=True):
    """Merge a group of duplicate products. Returns (master_id, duplicate_ids)."""
    master = pick_master(group)
    duplicates = [p for p in group if p['id'] != master['id']]
    dup_ids = [p['id'] for p in duplicates]

    if not dup_ids:
        return None

    if not dry_run:
        # Move offers
        cur.execute(
            "UPDATE product_offers SET product_id = %s WHERE product_id = ANY(%s)",
            (master['id'], dup_ids)
        )

        # Move price_history if table exists
        try:
            cur.execute(
                "UPDATE price_history SET product_id = %s WHERE product_id = ANY(%s)",
                (master['id'], dup_ids)
            )
        except psycopg2.errors.UndefinedTable:
            cur.connection.rollback()
            # Re-do the offers update since we rolled back
            cur.execute(
                "UPDATE product_offers SET product_id = %s WHERE product_id = ANY(%s)",
                (master['id'], dup_ids)
            )

        # Deactivate duplicates
        cur.execute(
            "UPDATE products SET is_sneaker = false WHERE id = ANY(%s)",
            (dup_ids,)
        )

    return master, duplicates


def print_examples(groups, limit=20):
    """Print example duplicate groups."""
    shown = 0
    items = list(groups.values()) if isinstance(groups, dict) else groups
    for group in items[:limit]:
        if isinstance(group, list):
            prods = group
        else:
            prods = group
        master = pick_master(prods)
        print(f"\n  Master: [{master['id']}] {master['name']} ({master['offer_count']} offers)")
        for p in prods:
            if p['id'] != master['id']:
                print(f"    Dup:  [{p['id']}] {p['name']} ({p['offer_count']} offers)")
        shown += 1
    if len(items) > limit:
        print(f"\n  ... and {len(items) - limit} more groups")


def main():
    parser = argparse.ArgumentParser(description='SneakerPicks product deduplication')
    parser.add_argument('--execute', action='store_true', help='Execute merges (pass 1+2)')
    parser.add_argument('--fuzzy', action='store_true', help='Also execute pass 3 (fuzzy)')
    parser.add_argument('--report', action='store_true', help='Detailed report with examples')
    args = parser.parse_args()

    dry_run = not args.execute
    conn = get_conn()
    conn.autocommit = False
    cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

    print(f"SneakerPicks Dedup — {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print(f"Mode: {'EXECUTE' if args.execute else 'DRY-RUN'}\n")

    # Stats
    cur.execute("SELECT COUNT(*) as cnt FROM products WHERE is_sneaker = true")
    total = cur.fetchone()['cnt']
    print(f"Total active products: {total:,}")

    merged_ids = set()  # track already-merged duplicates
    total_merges = 0

    # === Pass 1: EAN/GTIN ===
    print("\n" + "=" * 60)
    print("PASS 1: EAN/GTIN exact match")
    print("=" * 60)

    ean_groups = find_ean_duplicates(cur)
    dup_count = sum(len(v) - 1 for v in ean_groups.values())
    print(f"Found {len(ean_groups)} duplicate groups ({dup_count} duplicates)")

    if args.report:
        print_examples(ean_groups)

    if args.execute:
        for key, group in ean_groups.items():
            result = merge_group(cur, group, dry_run=False)
            if result:
                master, dups = result
                for d in dups:
                    merged_ids.add(d['id'])
                total_merges += len(dups)
                if args.report:
                    print(f"  Merged [{master['id']}] <- {[d['id'] for d in dups]} (EAN: {key})")
        print(f"Merged: {total_merges} products")
    else:
        print("(dry-run, no changes)")

    # === Pass 2: Fingerprint ===
    print("\n" + "=" * 60)
    print("PASS 2: match_fingerprint exact match")
    print("=" * 60)

    fp_groups = find_fingerprint_duplicates(cur, exclude_ids=merged_ids)
    dup_count = sum(len(v) - 1 for v in fp_groups.values())
    print(f"Found {len(fp_groups)} duplicate groups ({dup_count} duplicates)")

    if args.report:
        print_examples(fp_groups)

    pass2_merges = 0
    if args.execute:
        for key, group in fp_groups.items():
            result = merge_group(cur, group, dry_run=False)
            if result:
                master, dups = result
                for d in dups:
                    merged_ids.add(d['id'])
                pass2_merges += len(dups)
                if args.report:
                    print(f"  Merged [{master['id']}] <- {[d['id'] for d in dups]}")
        total_merges += pass2_merges
        print(f"Merged: {pass2_merges} products")
    else:
        print("(dry-run, no changes)")

    # === Pass 3: Fuzzy ===
    print("\n" + "=" * 60)
    print("PASS 3: Fuzzy name match")
    print("=" * 60)

    fuzzy_groups = find_fuzzy_duplicates(cur, exclude_ids=merged_ids)
    dup_count = sum(len(g) - 1 for g in fuzzy_groups)
    print(f"Found {len(fuzzy_groups)} potential duplicate groups ({dup_count} duplicates)")

    if args.report:
        print_examples(fuzzy_groups)

    if args.execute and args.fuzzy:
        pass3_merges = 0
        for group in fuzzy_groups:
            result = merge_group(cur, group, dry_run=False)
            if result:
                master, dups = result
                pass3_merges += len(dups)
                if args.report:
                    print(f"  Merged [{master['id']}] <- {[d['id'] for d in dups]}")
        total_merges += pass3_merges
        print(f"Merged: {pass3_merges} products")
    else:
        print("(report-only — use --execute --fuzzy to merge)")

    # === Summary ===
    print("\n" + "=" * 60)
    print("SUMMARY")
    print("=" * 60)
    if args.execute:
        print(f"Total merged: {total_merges} duplicate products")
        conn.commit()
        print("Transaction committed.")
    else:
        print("Dry-run complete. Use --execute to apply changes.")
        conn.rollback()

    cur.close()
    conn.close()


if __name__ == '__main__':
    main()