"""Phase 4 scale: tenant extraction scoping, commission rules, search indexes

Revision ID: 008_phase4_scale
Revises: 007_whatsapp_cloud
Create Date: 2026-06-26

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy import inspect

revision: str = "008_phase4_scale"
down_revision: Union[str, None] = "007_whatsapp_cloud"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _has_column(table: str, column: str) -> bool:
    bind = op.get_bind()
    cols = {c["name"] for c in inspect(bind).get_columns(table)}
    return column in cols


def _has_index(table: str, index_name: str) -> bool:
    bind = op.get_bind()
    indexes = {idx["name"] for idx in inspect(bind).get_indexes(table)}
    return index_name in indexes


def _has_table(table: str) -> bool:
    bind = op.get_bind()
    return table in inspect(bind).get_table_names()


def upgrade() -> None:
    if not _has_column("extraction_results", "agency_id"):
        op.add_column("extraction_results", sa.Column("agency_id", sa.Integer(), nullable=True))
    if not _has_column("extraction_results", "uploaded_by"):
        op.add_column("extraction_results", sa.Column("uploaded_by", sa.Integer(), nullable=True))
    if not _has_index("extraction_results", "ix_extraction_results_agency_id"):
        op.create_index("ix_extraction_results_agency_id", "extraction_results", ["agency_id"])

    if not _has_table("commission_rules"):
        op.create_table(
            "commission_rules",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("agency_id", sa.Integer(), nullable=False),
            sa.Column("insurance_company_id", sa.Integer(), nullable=True),
            sa.Column("vehicle_type", sa.String(length=50), nullable=True),
            sa.Column("coverage_type", sa.String(length=50), nullable=True),
            sa.Column("rule_type", sa.String(length=20), nullable=False),
            sa.Column("value", sa.Numeric(12, 4), nullable=False),
            sa.Column("priority", sa.Integer(), nullable=False, server_default="100"),
            sa.Column("active", sa.Boolean(), nullable=False, server_default=sa.text("1")),
            sa.Column("notes", sa.Text(), nullable=True),
            sa.Column("created_at", sa.DateTime(), nullable=False),
            sa.Column("updated_at", sa.DateTime(), nullable=False),
            sa.ForeignKeyConstraint(["agency_id"], ["agencies.id"]),
            sa.ForeignKeyConstraint(["insurance_company_id"], ["insurance_companies.id"]),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_commission_rules_agency_id", "commission_rules", ["agency_id"])

    if not _has_index("customers", "ix_customers_agency_mobile"):
        op.create_index("ix_customers_agency_mobile", "customers", ["agency_id", "mobile"])
    if not _has_index("customers", "ix_customers_agency_name"):
        op.create_index("ix_customers_agency_name", "customers", ["agency_id", "name"])
    if not _has_index("policies", "ix_policies_agency_status_end"):
        op.create_index(
            "ix_policies_agency_status_end",
            "policies",
            ["agency_id", "status", "policy_end_date"],
        )


def downgrade() -> None:
    if _has_index("policies", "ix_policies_agency_status_end"):
        op.drop_index("ix_policies_agency_status_end", table_name="policies")
    if _has_index("customers", "ix_customers_agency_name"):
        op.drop_index("ix_customers_agency_name", table_name="customers")
    if _has_index("customers", "ix_customers_agency_mobile"):
        op.drop_index("ix_customers_agency_mobile", table_name="customers")
    if _has_table("commission_rules"):
        op.drop_table("commission_rules")
    if _has_index("extraction_results", "ix_extraction_results_agency_id"):
        op.drop_index("ix_extraction_results_agency_id", table_name="extraction_results")
    if _has_column("extraction_results", "uploaded_by"):
        op.drop_column("extraction_results", "uploaded_by")
    if _has_column("extraction_results", "agency_id"):
        op.drop_column("extraction_results", "agency_id")