repair_database.py

"""
Database Repair Script - Fix Missing Connections
Shows exact entity names and repairs relationships
"""

from neo4j import GraphDatabase
from loguru import logger


class DatabaseRepair:
    """Repair missing relationships in the graph"""
    
    def __init__(self, uri="bolt://localhost:7687", user="neo4j", password="password123"):
        self.driver = GraphDatabase.driver(uri, auth=(user, password))
        logger.info(f"Connected to Neo4j")
    
    def close(self):
        self.driver.close()
    
    def show_exact_names(self):
        """Show entity names with invisible characters visible"""
        print("\n" + "="*80)
        print("EXACT ENTITY NAMES (with spaces/special chars visible)")
        print("="*80)
        print()
        
        query = """
        MATCH (n)
        WHERE n.name IS NOT NULL
        RETURN n.name as name, 
               labels(n)[0] as type,
               length(n.name) as name_length,
               id(n) as id
        ORDER BY type, name
        LIMIT 30
        """
        
        with self.driver.session() as session:
            result = session.run(query)
            
            current_type = None
            for record in result:
                node_type = record["type"]
                name = record["name"]
                length = record["name_length"]
                node_id = record["id"]
                
                if node_type != current_type:
                    print(f"\n{node_type}:")
                    print("-" * 80)
                    current_type = node_type
                
                # Show name with length to spot extra spaces
                name_repr = repr(name)  # Shows \n, \t, extra spaces, etc.
                print(f"  [{node_id:3d}] {name_repr:<40} (len={length})")
    
    def fix_helsing_duplicate(self):
        """Fix Helsing duplicate - keep Organization, delete Person"""
        print("\n" + "="*80)
        print("FIXING HELSING DUPLICATE")
        print("="*80)
        print()
        
        with self.driver.session() as session:
            # Check current state
            result = session.run("""
                MATCH (n {name: 'Helsing'})
                RETURN labels(n)[0] as type, id(n) as id, count(*) as count
            """)
            
            helsing_nodes = list(result)
            
            if len(helsing_nodes) <= 1:
                print("✓ No Helsing duplicate (only 1 node found)")
                return
            
            print(f"Found {len(helsing_nodes)} Helsing nodes:")
            for node in helsing_nodes:
                print(f"  - {node['type']} (ID: {node['id']})")
            
            # Delete Person node
            print("\nDeleting Helsing (Person) node...")
            result = session.run("""
                MATCH (p:Person {name: 'Helsing'})
                DETACH DELETE p
                RETURN count(p) as deleted
            """)
            deleted = result.single()["deleted"]
            
            if deleted > 0:
                print(f"✓ Deleted {deleted} Person node(s)")
            else:
                print("⚠ No Person node found to delete")
            
            # Verify
            result = session.run("""
                MATCH (n {name: 'Helsing'})
                RETURN labels(n)[0] as type, id(n) as id
            """)
            
            remaining = list(result)
            print(f"\nRemaining Helsing nodes: {len(remaining)}")
            for node in remaining:
                print(f"  ✓ {node['type']} (ID: {node['id']})")
    
    def find_orphan_relationships(self):
        """Find relationships that don't connect to any nodes"""
        print("\n" + "="*80)
        print("CHECKING FOR ORPHAN RELATIONSHIPS")
        print("="*80)
        print()
        
        # This is actually not possible in Neo4j - relationships must have start and end nodes
        # But we can check if relationship names match actual entity names
        
        query = """
        MATCH ()-[r]->()
        RETURN DISTINCT type(r) as rel_type, count(*) as count
        ORDER BY count DESC
        """
        
        with self.driver.session() as session:
            result = session.run(query)
            
            print("Existing Relationships:")
            for record in result:
                print(f"  {record['rel_type']}: {record['count']}")
    
    def create_missing_relationships(self):
        """Create relationships that should exist based on document analysis"""
        print("\n" + "="*80)
        print("CREATING MISSING RELATIONSHIPS")
        print("="*80)
        print()
        
        # Define relationships that should exist based on common knowledge
        relationships_to_create = [
            {
                "source": "Maria Weber",
                "source_type": "Person",
                "target": "Helsing",
                "target_type": "Organization",
                "rel_type": "WORKS_FOR"
            },
            {
                "source": "Helsing",
                "source_type": "Organization",
                "target": "Munich",
                "target_type": "Location",
                "rel_type": "LOCATED_IN"
            },
            {
                "source": "ARX Robotics",
                "source_type": "Organization",
                "target": "Munich",
                "target_type": "Location",
                "rel_type": "LOCATED_IN"
            },
        ]
        
        created = 0
        failed = 0
        
        with self.driver.session() as session:
            for rel in relationships_to_create:
                try:
                    # Check if relationship already exists
                    check_query = f"""
                    MATCH (source:{rel['source_type']} {{name: $source_name}})
                    MATCH (target:{rel['target_type']} {{name: $target_name}})
                    MATCH (source)-[r:{rel['rel_type']}]->(target)
                    RETURN count(r) as exists
                    """
                    
                    result = session.run(
                        check_query,
                        source_name=rel['source'],
                        target_name=rel['target']
                    )
                    
                    exists = result.single()["exists"]
                    
                    if exists > 0:
                        print(f"  ✓ Already exists: {rel['source']} --[{rel['rel_type']}]--> {rel['target']}")
                        continue
                    
                    # Create relationship
                    create_query = f"""
                    MATCH (source:{rel['source_type']} {{name: $source_name}})
                    MATCH (target:{rel['target_type']} {{name: $target_name}})
                    MERGE (source)-[r:{rel['rel_type']}]->(target)
                    RETURN r
                    """
                    
                    result = session.run(
                        create_query,
                        source_name=rel['source'],
                        target_name=rel['target']
                    )
                    
                    if result.single():
                        print(f"  ✓ Created: {rel['source']} --[{rel['rel_type']}]--> {rel['target']}")
                        created += 1
                    else:
                        print(f"  ✗ Failed: {rel['source']} --[{rel['rel_type']}]--> {rel['target']} (nodes not found?)")
                        failed += 1
                        
                except Exception as e:
                    print(f"  ✗ Error: {rel['source']} -> {rel['target']}: {e}")
                    failed += 1
        
        print(f"\nSummary: {created} created, {failed} failed")
    
    def verify_connections(self):
        """Verify key entities now have connections"""
        print("\n" + "="*80)
        print("VERIFYING CONNECTIONS")
        print("="*80)
        print()
        
        key_entities = ["Helsing", "Maria Weber", "ARX Robotics", "Munich"]
        
        with self.driver.session() as session:
            for entity_name in key_entities:
                query = """
                MATCH (e {name: $name})
                OPTIONAL MATCH (e)-[r]-()
                RETURN labels(e)[0] as type,
                       count(DISTINCT r) as connections
                """
                
                result = session.run(query, name=entity_name)
                record = result.single()
                
                if record:
                    node_type = record["type"]
                    connections = record["connections"]
                    
                    if connections > 0:
                        print(f"  ✓ {entity_name} ({node_type}): {connections} connections")
                    else:
                        print(f"  ⚠ {entity_name} ({node_type}): 0 connections")
                else:
                    print(f"  ✗ {entity_name}: NOT FOUND")


def main():
    print("\n" + "="*80)
    print("DATABASE REPAIR TOOL")
    print("="*80)
    print()
    print("This will:")
    print("  1. Show exact entity names (to spot invisible characters)")
    print("  2. Fix Helsing duplicate (keep Organization, delete Person)")
    print("  3. Create missing relationships (Maria Weber -> Helsing, etc.)")
    print("  4. Verify connections")
    print()
    
    response = input("Continue? (yes/no): ").strip().lower()
    if response != "yes":
        print("Aborted.")
        return
    
    repair = DatabaseRepair()
    
    # Step 1: Show exact names
    repair.show_exact_names()
    
    # Step 2: Fix Helsing duplicate
    repair.fix_helsing_duplicate()
    
    # Step 3: Check relationships
    repair.find_orphan_relationships()
    
    # Step 4: Create missing relationships
    repair.create_missing_relationships()
    
    # Step 5: Verify
    repair.verify_connections()
    
    repair.close()
    
    print("\n" + "="*80)
    print("REPAIR COMPLETE!")
    print("="*80)
    print()
    print("Next steps:")
    print("  1. Test: python -m backend.database.graph_query_engine")
    print("  2. Verify: python graph_diagnostic.py")
    print()


if __name__ == "__main__":
    main()