#!/usr/bin/env python3
"""
Remove duplicate/inferior skill entries now that we have clean Core Rulebook versions.
Also recategorize clearly non-skill sanitized entries.
"""
import os, re, mysql.connector
from pathlib import Path

REPO = Path(__file__).resolve().parent.parent

def load_env_file(path):
    if not path.exists():
        return
    for line in path.read_text().splitlines():
        line = line.strip()
        if not line or line.startswith('#') or '=' not in line:
            continue
        key, value = line.split('=', 1)
        os.environ.setdefault(key.strip(), value.strip().strip('"').strip("'"))

load_env_file(REPO / 'database' / '.env')
load_env_file(REPO / '.env')
if not os.environ.get('DB_PASSWORD'):
    raise RuntimeError('DB_PASSWORD must be set in database/.env or .env')

DB = dict(
    host=os.environ.get('DB_HOST', '192.168.1.113'),
    port=int(os.environ.get('DB_PORT', '3307')),
    user=os.environ.get('DB_USER', 'deathwatch'),
    password=os.environ['DB_PASSWORD'],
    database=os.environ.get('DB_NAME', 'deathwatch'),
)
db = mysql.connector.connect(**DB)
c = db.cursor()

# 1. Get all clean skill names (Core Rulebook source)
c.execute("SELECT title FROM rules WHERE source='Core Rulebook' AND category='skills'")
clean_skill_names = {row[0].lower() for row in c.fetchall()}
print(f"Clean skills: {len(clean_skill_names)}")

# 2. Delete old csv-import entries for skills we now have clean versions of
c.execute("SELECT id, title FROM rules WHERE source='csv-import' AND category IN ('skills','Skills/Rules')")
csv_skills = c.fetchall()
deleted_csv = 0
for db_id, title in csv_skills:
    if title.lower() in clean_skill_names:
        c.execute("DELETE FROM rules WHERE id=%s", (db_id,))
        deleted_csv += 1
print(f"Deleted {deleted_csv} old csv-import skill duplicates")

# 3. Delete old fandom entries for skills we now have clean versions of
c.execute("SELECT id, title FROM rules WHERE source IN ('fandom','https://40k-rpg-ffg.fandom.com') AND category IN ('skills','Skills/Rules')")
fandom_skills = c.fetchall()
deleted_fandom = 0
for db_id, title in fandom_skills:
    if title.lower() in clean_skill_names:
        c.execute("DELETE FROM rules WHERE id=%s", (db_id,))
        deleted_fandom += 1
print(f"Deleted {deleted_fandom} old fandom skill duplicates")

# 4. Delete sanitized duplicate skill entries (where we have an exact title match in Core Rulebook)
c.execute("SELECT id, title FROM rules WHERE source='sanitized' AND category='skills'")
sanitized_skills = c.fetchall()
deleted_san = 0
for db_id, title in sanitized_skills:
    if title.lower() in clean_skill_names:
        c.execute("DELETE FROM rules WHERE id=%s", (db_id,))
        deleted_san += 1
print(f"Deleted {deleted_san} sanitized skill duplicates")

db.commit()

# 5. Recategorize sanitized "skills" that are clearly lore/background
# Rules whose titles suggest they're lore, not skills
lore_patterns = [
    'Chapter Recruitment', 'Dark Heresy', 'Watch Fortress', 'Initiations',
    'Combat Doctrine', 'THE AGES', 'Hive Fleet', 'Blood Trinity',
    'Military Machine', 'Recruitment', 'Castobel', 'Warp', 'Erioch',
    'Jericho', 'Tau', 'Tyranid', 'Ork', 'Eldar', 'Necron', 'Chaos',
]
c.execute("SELECT id, title FROM rules WHERE source='sanitized' AND category='skills'")
sanitized_remaining = c.fetchall()
recatd = 0
for db_id, title in sanitized_remaining:
    for pat in lore_patterns:
        if pat.lower() in title.lower():
            c.execute("UPDATE rules SET category='lore' WHERE id=%s", (db_id,))
            recatd += 1
            break
print(f"Recategorized {recatd} lore entries from 'skills' to 'lore'")

# 6. Rules-category cleanup: remove duplicate numeric/fragment titles
c.execute("DELETE FROM rules WHERE source='sanitized' AND category='skills' AND title REGEXP '^[0-9]+$'")
print(f"Deleted {c.rowcount} numeric-title entries")

db.commit()

# Final stats
c.execute("SELECT category, COUNT(*) FROM rules GROUP BY category ORDER BY COUNT(*) DESC")
print("\n=== Rules by category ===")
for cat, cnt in c.fetchall():
    print(f"  {cat}: {cnt}")

c.execute("SELECT source, COUNT(*) FROM rules GROUP BY source ORDER BY COUNT(*) DESC")
print("\n=== Rules by source ===")
total = 0
for src, cnt in c.fetchall():
    print(f"  {src}: {cnt}")
    total += cnt
print(f"  TOTAL: {total}")

c.close()
db.close()