"""
Seed permissions and system roles. Safe to run on every startup (idempotent).
Ensures all permission keys and system roles exist, migrates User.role -> User.role_id,
and copies role permissions to user_permissions for any user who has no direct permissions yet.
"""
from sqlalchemy import text, inspect
from app.models import Base, Permission, Role, User, role_permissions, user_permissions
from app.database import engine, SessionLocal

# All granular permissions: key, name, category
PERMISSIONS = [
    # Admin
    ("admin.access", "Access Admin page", "Admin"),
    ("admin.revenue_targets", "Revenue targets", "Admin"),
    ("admin.gp_thresholds", "GP thresholds", "Admin"),
    ("admin.kpi_targets", "KPI targets", "Admin"),
    ("admin.connections", "Connections", "Admin"),
    ("admin.users", "Users list & create", "Admin"),
    ("admin.manage_roles", "Manage roles & permissions", "Admin"),
    ("admin.custom_pages", "Custom page builder", "Admin"),
    # Directors
    ("directors.access", "Access Directors page", "Directors"),
    # Branches (top-level tabs)
    ("branch.overview", "Overview (company)", "Branches"),
    ("branch.branch1", "Busselton branch", "Branches"),
    ("branch.branch2", "Bunbury branch", "Branches"),
    ("branch.branch3", "Mandurah branch", "Branches"),
    # Departments (per-branch departments) - sorted by branch, then department
    ("department.branch1_electrical", "Busselton Electrical", "Departments"),
    ("department.branch1_air", "Busselton Air", "Departments"),
    ("department.branch2_residential", "Bunbury Residential", "Departments"),
    ("department.branch2_air", "Bunbury Air", "Departments"),
    ("department.branch2_solar", "Bunbury Solar", "Departments"),
    ("department.branch2_commercial", "Bunbury Commercial", "Departments"),
    ("department.branch3_electrical", "Mandurah Electrical", "Departments"),
    ("department.branch3_air", "Mandurah Air", "Departments"),
    # KPIs (branch-only, no revenue)
    ("kpi.branch1", "Busselton KPIs only", "KPIs"),
    ("kpi.branch2", "Bunbury KPIs only", "KPIs"),
    ("kpi.branch3", "Mandurah KPIs only", "KPIs"),
    # Performance monitoring
    ("performance.access", "Access Performance Monitor", "Performance"),
]

# System role slug -> list of permission keys (director = all; admin = all except manage_roles; user = branches + departments only)
SYSTEM_ROLE_PERMISSIONS = {
    "director": [p[0] for p in PERMISSIONS],
    "admin": [k for k in (p[0] for p in PERMISSIONS) if k != "admin.manage_roles"],
    "user": [
        "branch.overview", "branch.branch1", "branch.branch2", "branch.branch3",
        "department.branch1_electrical", "department.branch1_air",
        "department.branch2_residential", "department.branch2_air", "department.branch2_solar", "department.branch2_commercial",
        "department.branch3_electrical", "department.branch3_air",
    ],
}


def ensure_role_id_column():
    """Add users.role_id if missing (one-off migration)."""
    from sqlalchemy import inspect
    insp = inspect(engine)
    if "users" not in insp.get_table_names():
        return
    cols = [c["name"] for c in insp.get_columns("users")]
    if "role_id" in cols:
        return
    with engine.connect() as conn:
        conn.execute(text("ALTER TABLE users ADD COLUMN role_id INTEGER"))
        conn.commit()


def seed_roles_permissions():
    db = SessionLocal()
    try:
        ensure_role_id_column()

        # Ensure all permissions exist
        key_to_id = {}
        for key, name, category in PERMISSIONS:
            perm = db.query(Permission).filter(Permission.key == key).first()
            if not perm:
                perm = Permission(key=key, name=name, category=category)
                db.add(perm)
                db.flush()
            key_to_id[key] = perm.id

        # Ensure system roles exist and have correct permissions
        slug_to_role = {}
        for slug, role_name in [("user", "User"), ("admin", "Admin"), ("director", "Director")]:
            role = db.query(Role).filter(Role.slug == slug).first()
            if not role:
                role = Role(name=role_name, slug=slug, is_system=True)
                db.add(role)
                db.flush()
            slug_to_role[slug] = role
            wanted = set(SYSTEM_ROLE_PERMISSIONS[slug])
            current = {p.key for p in role.permissions}
            for k in wanted - current:
                perm = db.query(Permission).filter(Permission.key == k).first()
                if perm and perm not in role.permissions:
                    role.permissions.append(perm)
            for p in list(role.permissions):
                if p.key not in wanted:
                    role.permissions.remove(p)

        db.commit()
        db.expire_all()  # reload relationships

        # Migrate users with role_id IS NULL: set role_id from role string
        for user in db.query(User).filter(User.role_id == None).all():
            slug = (user.role or "user").lower()
            if slug in slug_to_role:
                user.role_id = slug_to_role[slug].id
        db.commit()

        # Migrate: users with role but no direct permissions get role's permissions copied to user_permissions
        insp = inspect(engine)
        if "user_permissions" in insp.get_table_names():
            for u in db.query(User).all():
                has_direct = db.execute(
                    text("SELECT 1 FROM user_permissions WHERE user_id = :uid LIMIT 1"),
                    {"uid": u.id},
                ).first()
                if has_direct:
                    continue
                role = db.query(Role).filter(Role.id == u.role_id).first() if u.role_id else db.query(Role).filter(Role.slug == (u.role or "user").lower()).first()
                if not role:
                    continue
                for p in role.permissions:
                    db.execute(user_permissions.insert().values(user_id=u.id, permission_id=p.id))
            db.commit()

        return slug_to_role
    finally:
        db.close()