from sqlmodel import text, SQLModel
# Import all models to register them in metadata
import app.models.security_models
from app.models.LifeInsuranceModel import LifeInsuranceModel
from app.models.AnnuityInsuranceModel import AnnuityInsuranceModel
from app.models.RentersInsuranceModel import RentersInsuranceModel
# Import others as well
from db import engine

with engine.begin() as conn:
    print("Dialect:", conn.dialect.name)
    print("Registered tables:", list(SQLModel.metadata.tables.keys()))
    for table_name in SQLModel.metadata.tables.keys():
        if table_name in ["users", "refresh_tokens", "verification_tokens", "audit_logs"]:
            continue
        res_user_id = conn.execute(text(
            "SELECT EXISTS (SELECT 1 FROM information_schema.columns "
            "WHERE table_name = :table_name AND column_name = 'user_id')"
        ), {"table_name": table_name}).scalar()
        print(f"Table: {table_name}, has user_id: {res_user_id}")
        
        if not res_user_id:
            print(f"Altering {table_name} to add user_id...")
            conn.execute(text(f"ALTER TABLE {table_name} ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE SET NULL"))
            print("Added user_id column.")
            
        res_deleted_at = conn.execute(text(
            "SELECT EXISTS (SELECT 1 FROM information_schema.columns "
            "WHERE table_name = :table_name AND column_name = 'deleted_at')"
        ), {"table_name": table_name}).scalar()
        print(f"Table: {table_name}, has deleted_at: {res_deleted_at}")
        
        if not res_deleted_at:
            print(f"Altering {table_name} to add deleted_at...")
            conn.execute(text(f"ALTER TABLE {table_name} ADD COLUMN deleted_at TIMESTAMP NULL"))
            print("Added deleted_at column.")
