import requests
import re

URL = "https://raw.githubusercontent.com/smogon/pokemon-showdown/master/data/pokedex.ts"
OUTPUT_FILE = r"c:\Users\lftam\.gemini\antigravity\scratch\Liga Tsuru\ligatsuru\anatomia\seed_pokedex_full.sql"

def get_region_id(num):
    if 1 <= num <= 151: return 1 # Kanto
    if 152 <= num <= 251: return 2 # Johto
    if 252 <= num <= 386: return 3 # Hoenn
    if 387 <= num <= 493: return 4 # Sinnoh
    if 494 <= num <= 649: return 5 # Teselia (Unova)
    if 650 <= num <= 721: return 6 # Kalos
    if 722 <= num <= 809: return 7 # Alola
    if 810 <= num <= 905: return 8 # Galar
    if num >= 906: return 9 # Paldea (and beyond)
    return 1

def main():
    print(f"Downloading {URL}...")
    try:
        response = requests.get(URL)
        response.raise_for_status()
        content = response.text
    except Exception as e:
        print(f"Error downloading: {e}")
        return

    print("Parsing content...")
    
    # Regex to capture content between braces for each entry
    # Keys like 'bulbasaur: { ... }'
    # We look for 'name: "..."' and 'num: ...' and 'types: [...]'
    # and 'baseSpecies' to filter out variants
    
    # Strategy: Split by "}," to get rough chunks, then regex each chunk
    # This is rough but effective for this specific file structure
    
    entries = {} # num -> {name, type1, type2, region}
    
    # Rough split - valid because standard formatting in file
    chunks = content.split("},\n\t")
    
    for chunk in chunks:
        # Extract Num
        num_match = re.search(r'num:\s*(\d+)', chunk)
        if not num_match: continue
        num = int(num_match.group(1))
        
        if num <= 0: continue # Skip placeholders like MissingNo if any
        
        # Check for Base Species (Variant filter)
        # If 'baseSpecies:' is present, it's usually a variant (Mega, Gmax, Regional)
        # EXCEPT for some edge cases, but for this massive list, this filter is safest 
        # to get the 1025 unique species.
        if "baseSpecies:" in chunk:
            continue
            
        # Extract Name
        name_match = re.search(r'name:\s*"([^"]+)"', chunk)
        if not name_match: continue
        name = name_match.group(1)
        
        # Extract Types
        types_match = re.search(r'types:\s*\[([^\]]+)\]', chunk)
        if not types_match: continue
        types_str = types_match.group(1)
        # Clean quotes
        types = [t.strip().strip('"').strip("'") for t in types_str.split(",")]
        
        type1 = types[0]
        type2 = types[1] if len(types) > 1 else "NULL"
        
        # Region
        region_id = get_region_id(num)
        
        # Store (First write wins is NOT needed if we filter baseSpecies, 
        # but just in case, we check if num exists)
        if num not in entries:
            entries[num] = {
                "name": name,
                "type1": type1,
                "type2": type2,
                "region": region_id
            }

    print(f"Found {len(entries)} unique Pokemon.")
    
    # Generate SQL
    print("Generating SQL...")
    sql_lines = []
    sql_lines.append("-- Script de Poblado Completo de Pokedex (Gen 1-9)")
    sql_lines.append("SET FOREIGN_KEY_CHECKS = 0;")
    sql_lines.append("TRUNCATE TABLE ltic_pokedex;")
    sql_lines.append("INSERT INTO ltic_pokedex (id, nombre, tipo1, tipo2, region_origen_id) VALUES")
    
    values = []
    sorted_nums = sorted(entries.keys())
    
    for num in sorted_nums:
        e = entries[num]
        t2 = f"'{e['type2']}'" if e['type2'] != "NULL" else "NULL"
        # Escape name single quotes if any (Farfetch'd)
        safe_name = e['name'].replace("'", "''")
        
        val = f"({num}, '{safe_name}', '{e['type1']}', {t2}, {e['region']})"
        values.append(val)
    
    # Join values with commas
    sql_lines.append(",\n".join(values) + ";")
    sql_lines.append("SET FOREIGN_KEY_CHECKS = 1;")
    sql_lines.append(f"SELECT 'Se han insertado {len(values)} Pokemon.' AS Status;")
    
    with open(OUTPUT_FILE, "w", encoding="utf-8") as f:
        f.write("\n".join(sql_lines))
        
    print(f"SQL script written to {OUTPUT_FILE}")

if __name__ == "__main__":
    main()