"""Add company_users dashboard fields (employee_code, last_login) and disabled status.

Revision ID: 031_company_users_dashboard
Revises: 030_users_roles
Create Date: 2026-08-15
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "031_company_users_dashboard"
down_revision: Union[str, None] = "030_users_roles"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    tables = set(inspector.get_table_names())
    if "company_users" not in tables:
        return

    columns = {c["name"] for c in inspector.get_columns("company_users")}
    if "employee_code" not in columns:
        op.add_column(
            "company_users",
            sa.Column("employee_code", sa.String(length=50), nullable=True),
        )
    if "last_login" not in columns:
        op.add_column(
            "company_users",
            sa.Column("last_login", sa.DateTime(), nullable=True),
        )

    indexes = {i["name"] for i in inspector.get_indexes("company_users")}
    if "ix_company_users_employee_code" not in indexes:
        op.create_index(
            "ix_company_users_employee_code",
            "company_users",
            ["company_id", "employee_code"],
        )
    if "uq_company_users_company_employee_code" not in {
        u["name"] for u in inspector.get_unique_constraints("company_users")
    }:
        # Unique only when employee_code is set — MySQL allows multiple NULLs.
        op.create_unique_constraint(
            "uq_company_users_company_employee_code",
            "company_users",
            ["company_id", "employee_code"],
        )

    op.execute(
        sa.text(
            "UPDATE company_users SET status = 'disabled' WHERE status = 'inactive'"
        )
    )


def downgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    tables = set(inspector.get_table_names())
    if "company_users" not in tables:
        return

    op.execute(
        sa.text(
            "UPDATE company_users SET status = 'inactive' WHERE status = 'disabled'"
        )
    )

    uniques = {u["name"] for u in inspector.get_unique_constraints("company_users")}
    if "uq_company_users_company_employee_code" in uniques:
        op.drop_constraint(
            "uq_company_users_company_employee_code",
            "company_users",
            type_="unique",
        )

    indexes = {i["name"] for i in inspector.get_indexes("company_users")}
    if "ix_company_users_employee_code" in indexes:
        op.drop_index("ix_company_users_employee_code", table_name="company_users")

    columns = {c["name"] for c in inspector.get_columns("company_users")}
    if "last_login" in columns:
        op.drop_column("company_users", "last_login")
    if "employee_code" in columns:
        op.drop_column("company_users", "employee_code")