"""Create roles, role_permissions, and company_users tables.

Revision ID: 030_users_roles
Revises: 029_custom_reports
Create Date: 2026-08-15
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "030_users_roles"
down_revision: Union[str, None] = "029_custom_reports"
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 "roles" not in tables:
        op.create_table(
            "roles",
            sa.Column("id", sa.String(length=36), primary_key=True),
            sa.Column("company_id", sa.String(length=36), nullable=False),
            sa.Column("name", sa.String(length=200), nullable=False),
            sa.Column("slug", sa.String(length=100), nullable=False),
            sa.Column("description", sa.String(length=500), nullable=True),
            sa.Column("created_by", sa.String(length=36), nullable=True),
            sa.Column("created_at", sa.DateTime(), nullable=False),
            sa.Column("updated_at", sa.DateTime(), nullable=False),
            sa.ForeignKeyConstraint(["company_id"], ["companies.id"], ondelete="CASCADE"),
            sa.UniqueConstraint("company_id", "slug", name="uq_roles_company_slug"),
            sa.UniqueConstraint("company_id", "name", name="uq_roles_company_name"),
        )
        op.create_index("ix_roles_company_id", "roles", ["company_id"])

    if "role_permissions" not in tables:
        op.create_table(
            "role_permissions",
            sa.Column("id", sa.String(length=36), primary_key=True),
            sa.Column("role_id", sa.String(length=36), nullable=False),
            sa.Column("module", sa.String(length=40), nullable=False),
            sa.Column("can_view", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("can_add", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("can_edit", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("can_delete", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("can_export", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.ForeignKeyConstraint(["role_id"], ["roles.id"], ondelete="CASCADE"),
            sa.UniqueConstraint("role_id", "module", name="uq_role_permissions_role_module"),
        )
        op.create_index("ix_role_permissions_role_id", "role_permissions", ["role_id"])

    if "company_users" not in tables:
        op.create_table(
            "company_users",
            sa.Column("id", sa.String(length=36), primary_key=True),
            sa.Column("company_id", sa.String(length=36), nullable=False),
            sa.Column("full_name", sa.String(length=200), nullable=False),
            sa.Column("username", sa.String(length=100), nullable=False),
            sa.Column("email", sa.String(length=255), nullable=False),
            sa.Column("phone", sa.String(length=50), nullable=True),
            sa.Column("password_hash", sa.String(length=255), nullable=False),
            sa.Column("role_id", sa.String(length=36), nullable=False),
            sa.Column("department_id", sa.String(length=36), nullable=True),
            sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
            sa.Column(
                "send_welcome_email",
                sa.Boolean(),
                nullable=False,
                server_default=sa.text("0"),
            ),
            sa.Column("address", sa.Text(), nullable=True),
            sa.Column("notes", sa.Text(), nullable=True),
            sa.Column("created_by", sa.String(length=36), nullable=True),
            sa.Column("created_at", sa.DateTime(), nullable=False),
            sa.Column("updated_at", sa.DateTime(), nullable=False),
            sa.ForeignKeyConstraint(["company_id"], ["companies.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["role_id"], ["roles.id"], ondelete="RESTRICT"),
            sa.ForeignKeyConstraint(["department_id"], ["departments.id"], ondelete="SET NULL"),
            sa.UniqueConstraint(
                "company_id", "username", name="uq_company_users_company_username"
            ),
            sa.UniqueConstraint(
                "company_id", "email", name="uq_company_users_company_email"
            ),
        )
        op.create_index("ix_company_users_company_id", "company_users", ["company_id"])
        op.create_index("ix_company_users_role_id", "company_users", ["role_id"])
        op.create_index(
            "ix_company_users_department_id", "company_users", ["department_id"]
        )
        op.create_index("ix_company_users_status", "company_users", ["status"])


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