#!/usr/bin/env python3
"""
Link Users to Persons based on name matching.
Adds person_id column to users table and links users to their Person records.
"""

import os
import sys
from dotenv import load_dotenv

# Load .env first
load_dotenv()

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import text
from database import SessionLocal, User, Person

def main():
    db = SessionLocal()

    # Step 1: Add person_id column if not exists
    try:
        db.execute(text("""
            ALTER TABLE users
            ADD COLUMN IF NOT EXISTS person_id INTEGER REFERENCES people(id)
        """))
        db.commit()
        print("✓ Kolumna person_id dodana (lub już istnieje)")
    except Exception as e:
        print(f"Uwaga przy dodawaniu kolumny: {e}")
        db.rollback()

    # Step 2: Get all users with names
    users = db.query(User).filter(User.name.isnot(None)).all()
    print(f"\nZnaleziono {len(users)} użytkowników z nazwami")

    # Step 3: Get all persons
    persons = db.query(Person).all()
    print(f"Znaleziono {len(persons)} osób w bazie KRS")

    # Build a lookup dict for persons by full name (lowercase)
    person_lookup = {}
    for p in persons:
        full_name = f"{p.imiona} {p.nazwisko}".lower().strip()
        person_lookup[full_name] = p
        # Also try just first name + last name (first word of imiona)
        first_name = p.imiona.split()[0] if p.imiona else ""
        short_name = f"{first_name} {p.nazwisko}".lower().strip()
        if short_name not in person_lookup:
            person_lookup[short_name] = p

    # Step 4: Match users to persons
    matched = 0
    for user in users:
        if user.person_id:
            print(f"  {user.name} - już powiązany z person_id={user.person_id}")
            continue

        user_name = user.name.lower().strip()

        if user_name in person_lookup:
            person = person_lookup[user_name]
            user.person_id = person.id
            matched += 1
            print(f"  ✓ {user.name} → Person #{person.id} ({person.full_name()})")
        else:
            print(f"  ✗ {user.name} - brak dopasowania")

    if matched > 0:
        db.commit()
        print(f"\n✓ Powiązano {matched} użytkowników z osobami")
    else:
        print("\nBrak nowych powiązań do zapisania")

    db.close()
    print("Gotowe!")

if __name__ == "__main__":
    main()