import os
import json
from src.knowledge.repository import KnowledgeRepository

def migrate_v2_to_v3(ontology_dir: str = "data/ontology", db_path: str = "data/knowledge/build/knowledge.db"):
    print(f"Starting migration from {ontology_dir} to SQLite ({db_path})...")
    repo = KnowledgeRepository(db_path)
    repo.connect()
    repo.init_schema()
    
    # Normally we would load JSONs from data/ontology/ and parse them
    # For now, we will create a dummy run if data/ontology/ doesn't exist or is empty
    # to demonstrate the schema insertion.
    
    conn = repo._conn
    cursor = conn.cursor()
    
    # Just an example of migrating a hardcoded v2.4 record for demonstration
    # Since we don't have the exact ontology files in this workspace yet.
    dummy_data = [
        {"id": "amy_rose", "name": "Amy Rose", "franchise": "sonic_the_hedgehog", "traits": ["pink_quills", "red_dress", "piko_piko_hammer"]},
        {"id": "sonic_the_hedgehog_char", "name": "Sonic", "franchise": "sonic_the_hedgehog", "traits": ["blue_quills", "red_shoes"]}
    ]
    
    cursor.execute("INSERT OR REPLACE INTO franchises (franchise_id, canonical_name) VALUES (?, ?)", ("sonic_the_hedgehog", "Sonic The Hedgehog"))
    
    for item in dummy_data:
        repo.add_entity(item["id"], item["name"], item["franchise"])
        
        for trait in item["traits"]:
            # Insert canonical trait
            cursor.execute("INSERT OR IGNORE INTO canonical_traits (trait_id, trait_type) VALUES (?, ?)", (trait, "visual"))
            
            # Insert entity visual trait
            cursor.execute("""
                INSERT OR REPLACE INTO entity_visual_traits 
                (entity_id, trait_id, confidence, frequency, source_count) 
                VALUES (?, ?, ?, ?, ?)
            """, (item["id"], trait, 1.0, 100, 2))
            
    conn.commit()
    print("Migration complete.")

if __name__ == "__main__":
    migrate_v2_to_v3()