#!/usr/bin/env python3
"""
Update malformed links in the database.
Fixes existing link entries with malformed paths.
"""

import sys
import re
from db_utils import MigrationDB

def update_malformed_links():
    """Update malformed link paths in the database."""
    db = MigrationDB()

    # Malformed to correct mappings
    link_fixes = {
        # Original malformed paths
        '/$baseurl/intranet/databases/crm/HiddenRemovedContacts.php': '/intranet/databases/crm/HiddenRemovedContacts.php',
        '/<?php print "$baseurl";?>/intranet/databases/crm/crmlinkeddocuments.php': '/intranet/databases/crm/crmlinkeddocuments.php',
        # Paths that were incorrectly normalized with /intranet/ prefix
        '/intranet/$baseurl/intranet/databases/crm/HiddenRemovedContacts.php': '/intranet/databases/crm/HiddenRemovedContacts.php',
        '/intranet/<?php print "$baseurl";?>/intranet/databases/crm/crmlinkeddocuments.php': '/intranet/databases/crm/crmlinkeddocuments.php'
    }

    print("🔧 Updating malformed links in database...")
    print("=" * 50)

    total_updated = 0

    with db.get_connection() as conn:
        for malformed, correct in link_fixes.items():
            # Find links with this malformed path
            links = conn.execute("SELECT id, source_file_id FROM links WHERE target_path = ?", (malformed,)).fetchall()

            if links:
                print(f"Found {len(links)} links with malformed path: {malformed}")
                print(f"  Updating to: {correct}")

                # Update each link
                for link in links:
                    conn.execute("UPDATE links SET target_path = ? WHERE id = ?", (correct, link['id']))
                    print(f"    Updated link ID {link['id']} from file ID {link['source_file_id']}")

                total_updated += len(links)
            else:
                print(f"No links found with path: {malformed}")

        # Now handle patterns for remaining malformed links
        # Get all links that still contain $baseurl or <?php
        malformed_links = conn.execute("SELECT id, target_path, source_file_id FROM links WHERE target_path LIKE '%$baseurl%' OR target_path LIKE '%<?php%'").fetchall()

        for link in malformed_links:
            original_path = link['target_path']
            corrected_path = original_path

            # Fix /intranet/$baseurl/intranet/ patterns
            corrected_path = re.sub(r'/intranet/\$baseurl/intranet/', '/intranet/', corrected_path)

            # Fix /intranet/<?php print "$baseurl/intranet/ patterns
            corrected_path = re.sub(r'/intranet/<\?php print "\$baseurl/intranet/', '/intranet/', corrected_path)

            # Fix /intranet/<?php print "/intranet/ patterns
            corrected_path = re.sub(r'/intranet/<\?php print "/intranet/', '/intranet/', corrected_path)

            if corrected_path != original_path:
                print(f"Correcting pattern: {original_path}")
                print(f"  To: {corrected_path}")
                conn.execute("UPDATE links SET target_path = ? WHERE id = ?", (corrected_path, link['id']))
                total_updated += 1

        conn.commit()

    print(f"\n✅ Update complete: {total_updated} links updated")

    # Show updated statistics
    stats = db.get_statistics()
    print("\n📊 Database Statistics:")
    print(f"   Total files: {stats['total_files']}")
    print(f"   Links scanned: {stats['links_scanned']}")
    print(f"   Total links: {stats['total_links']}")

if __name__ == '__main__':
    update_malformed_links()