#!/usr/bin/env python3
"""
Migration script: auth_db.users → syndicos.auth_users
- Finds all PHP files using getAuthConnection()
- Identifies the auth PDO variable name
- Replaces SQL table 'users' → 'auth_users' ONLY in auth queries
- Also updates Database.php so getAuthConnection() returns syndicos connection
"""
import re
import os
import subprocess

BETA_DIR = os.path.expanduser('~/beta')
SKIP_DIRS = {'vendor', 'node_modules', 'Controllers/Controllers'}

def find_auth_files():
    result = subprocess.run(
        ['grep', '-rln', 'getAuthConnection', BETA_DIR, '--include=*.php'],
        capture_output=True, text=True
    )
    files = []
    for f in result.stdout.strip().split('\n'):
        if not f or any(skip in f for skip in SKIP_DIRS):
            continue
        files.append(f)
    return sorted(files)

def migrate_file(filepath):
    with open(filepath, 'r') as f:
        content = f.read()

    auth_vars = set(re.findall(
        r'\$(\w+)\s*=\s*\\?(?:\\?Database)?::getAuthConnection\(\)', content
    ))
    if not auth_vars:
        auth_vars = set(re.findall(
            r'\$(\w+)\s*=\s*(?:\\\\)?Database::getAuthConnection\(\)', content
        ))
    if not auth_vars:
        return False, 0

    lines = content.split('\n')
    changes = 0

    for i, line in enumerate(lines):
        for var in auth_vars:
            pattern = r'\$' + re.escape(var) + r'\s*->\s*(prepare|query|exec)\s*\('
            if re.search(pattern, line):
                new_line = line
                new_line = re.sub(r'\bFROM\s+users\b', 'FROM auth_users', new_line)
                new_line = re.sub(r'\bINTO\s+users\b', 'INTO auth_users', new_line)
                new_line = re.sub(r'\bUPDATE\s+users\b', 'UPDATE auth_users', new_line)
                new_line = re.sub(r'\bJOIN\s+users\b', 'JOIN auth_users', new_line)
                new_line = re.sub(r'\bTABLE\s+users\b', 'TABLE auth_users', new_line)
                if new_line != line:
                    lines[i] = new_line
                    changes += 1

    if changes > 0:
        with open(filepath, 'w') as f:
            f.write('\n'.join(lines))
    return changes > 0, changes

def migrate_database_php():
    dbfile = os.path.join(BETA_DIR, 'core', 'Database.php')
    if not os.path.exists(dbfile):
        print(f'  WARNING: {dbfile} not found')
        return False
    with open(dbfile, 'r') as f:
        content = f.read()
    old = content
    content = content.replace(
        "return self::$authPdo;",
        "return self::getSyndicosConnection(); /* migration: auth_db -> syndicos.auth_users */",
        1
    )
    if content != old:
        with open(dbfile, 'w') as f:
            f.write(content)
        return True
    return False

def main():
    print("=" * 60)
    print("Migration: auth_db.users -> syndicos.auth_users")
    print("=" * 60)

    print("\n[1] Updating Database.php...")
    if migrate_database_php():
        print("  OK getAuthConnection() now returns syndicos connection")
    else:
        print("  WARNING Could not update Database.php")

    print("\n[2] Finding files with getAuthConnection()...")
    files = find_auth_files()
    print(f"  Found {len(files)} files")

    total_changes = 0
    changed_files = 0

    print("\n[3] Migrating SQL table names (users -> auth_users)...")
    for filepath in files:
        if filepath.endswith('Database.php'):
            print(f"  SKIP {filepath}")
            continue
        changed, count = migrate_file(filepath)
        rel = filepath.replace(BETA_DIR + '/', '')
        if changed:
            print(f"  OK {rel} ({count} changes)")
            changed_files += 1
            total_changes += count
        else:
            print(f"     {rel} (no SQL changes)")

    print(f"\nDone: {changed_files} files modified, {total_changes} SQL replacements")

    print("\n[4] Checking for remaining auth 'FROM users'...")
    warnings = 0
    for filepath in files:
        if filepath.endswith('Database.php'):
            continue
        with open(filepath, 'r') as f:
            content = f.read()
        auth_vars = set(re.findall(
            r'\$(\w+)\s*=\s*\\?(?:\\?Database)?::getAuthConnection\(\)', content
        ))
        if not auth_vars:
            auth_vars = set(re.findall(
                r'\$(\w+)\s*=\s*(?:\\\\)?Database::getAuthConnection\(\)', content
            ))
        lines = content.split('\n')
        for j, line in enumerate(lines):
            for var in auth_vars:
                if re.search(r'\$' + re.escape(var) + r'.*\bFROM\s+users\b', line):
                    rel = filepath.replace(BETA_DIR + '/', '')
                    print(f"  WARNING {rel}:{j+1}: {line.strip()}")
                    warnings += 1
    if warnings == 0:
        print("  All clear!")

if __name__ == '__main__':
    main()