"""crm schema (accounts, contacts, leads, pipelines, opportunities, activities)

Revision ID: f1a2b3c4d5e6
Revises: g2h3i4j5k6l7
Create Date: 2026-07-14 00:00:00.000000

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "f1a2b3c4d5e6"
down_revision: Union[str, None] = "g2h3i4j5k6l7"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

SCHEMA = "crm"


def _scoped_columns() -> list[sa.Column]:
    """Columnas comunes de TenantScopedMixin + TimestampMixin."""
    return [
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(), nullable=False, server_default=sa.text("now()")),
        sa.Column("deleted_at", sa.DateTime(), nullable=True),
    ]


def _scoped_indexes(table: str) -> None:
    op.create_index(f"ix_{SCHEMA}_{table}_id", table, ["id"], schema=SCHEMA)
    op.create_index(f"ix_{SCHEMA}_{table}_tenant_id", table, ["tenant_id"], schema=SCHEMA)
    op.create_index(f"ix_{SCHEMA}_{table}_company_id", table, ["company_id"], schema=SCHEMA)


def upgrade() -> None:
    op.execute(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")

    # ----- pipelines -----
    op.create_table(
        "pipelines",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=120), nullable=False),
        sa.Column("is_default", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("pipelines")

    # ----- pipeline_stages -----
    op.create_table(
        "pipeline_stages",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("pipeline_id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=120), nullable=False),
        sa.Column("position", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("probability", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("is_won", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("is_lost", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["pipeline_id"], [f"{SCHEMA}.pipelines.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("pipeline_stages")
    op.create_index("ix_crm_pipeline_stages_pipeline_id", "pipeline_stages", ["pipeline_id"], schema=SCHEMA)

    # ----- accounts -----
    op.create_table(
        "accounts",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("trade_name", sa.String(length=255), nullable=True),
        sa.Column("rfc", sa.String(length=13), nullable=True),
        sa.Column("account_type", sa.String(length=40), nullable=True),
        sa.Column("industry", sa.String(length=120), nullable=True),
        sa.Column("email", sa.String(length=255), nullable=True),
        sa.Column("phone", sa.String(length=40), nullable=True),
        sa.Column("website", sa.String(length=255), nullable=True),
        sa.Column("address", sa.Text(), nullable=True),
        sa.Column("city", sa.String(length=120), nullable=True),
        sa.Column("state", sa.String(length=120), nullable=True),
        sa.Column("country", sa.String(length=2), nullable=True, server_default=sa.text("'MX'")),
        sa.Column("patente_aduanal", sa.String(length=20), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'active'")),
        sa.Column("owner_user_id", sa.String(length=64), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("accounts")
    op.create_index("ix_crm_accounts_rfc", "accounts", ["rfc"], schema=SCHEMA)
    op.create_index("ix_crm_accounts_owner_user_id", "accounts", ["owner_user_id"], schema=SCHEMA)

    # ----- contacts -----
    op.create_table(
        "contacts",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("first_name", sa.String(length=120), nullable=False),
        sa.Column("last_name", sa.String(length=120), nullable=True),
        sa.Column("email", sa.String(length=255), nullable=True),
        sa.Column("phone", sa.String(length=40), nullable=True),
        sa.Column("mobile", sa.String(length=40), nullable=True),
        sa.Column("job_title", sa.String(length=120), nullable=True),
        sa.Column("department", sa.String(length=120), nullable=True),
        sa.Column("is_primary", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("owner_user_id", sa.String(length=64), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["account_id"], [f"{SCHEMA}.accounts.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("contacts")
    op.create_index("ix_crm_contacts_account_id", "contacts", ["account_id"], schema=SCHEMA)
    op.create_index("ix_crm_contacts_email", "contacts", ["email"], schema=SCHEMA)
    op.create_index("ix_crm_contacts_owner_user_id", "contacts", ["owner_user_id"], schema=SCHEMA)

    # ----- opportunities -----
    op.create_table(
        "opportunities",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("contact_id", sa.Integer(), nullable=True),
        sa.Column("pipeline_id", sa.Integer(), nullable=True),
        sa.Column("stage_id", sa.Integer(), nullable=True),
        sa.Column("amount", sa.Numeric(precision=14, scale=2), nullable=True),
        sa.Column("currency", sa.String(length=3), nullable=False, server_default=sa.text("'MXN'")),
        sa.Column("probability", sa.Integer(), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'open'")),
        sa.Column("expected_close_date", sa.Date(), nullable=True),
        sa.Column("closed_at", sa.DateTime(), nullable=True),
        sa.Column("lost_reason", sa.String(length=255), nullable=True),
        sa.Column("source", sa.String(length=60), nullable=True),
        sa.Column("owner_user_id", sa.String(length=64), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["account_id"], [f"{SCHEMA}.accounts.id"]),
        sa.ForeignKeyConstraint(["contact_id"], [f"{SCHEMA}.contacts.id"]),
        sa.ForeignKeyConstraint(["pipeline_id"], [f"{SCHEMA}.pipelines.id"]),
        sa.ForeignKeyConstraint(["stage_id"], [f"{SCHEMA}.pipeline_stages.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("opportunities")
    op.create_index("ix_crm_opportunities_account_id", "opportunities", ["account_id"], schema=SCHEMA)
    op.create_index("ix_crm_opportunities_contact_id", "opportunities", ["contact_id"], schema=SCHEMA)
    op.create_index("ix_crm_opportunities_pipeline_id", "opportunities", ["pipeline_id"], schema=SCHEMA)
    op.create_index("ix_crm_opportunities_stage_id", "opportunities", ["stage_id"], schema=SCHEMA)
    op.create_index("ix_crm_opportunities_status", "opportunities", ["status"], schema=SCHEMA)
    op.create_index("ix_crm_opportunities_owner_user_id", "opportunities", ["owner_user_id"], schema=SCHEMA)

    # ----- leads -----
    op.create_table(
        "leads",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("contact_name", sa.String(length=160), nullable=True),
        sa.Column("email", sa.String(length=255), nullable=True),
        sa.Column("phone", sa.String(length=40), nullable=True),
        sa.Column("company_name", sa.String(length=255), nullable=True),
        sa.Column("source", sa.String(length=60), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'new'")),
        sa.Column("estimated_value", sa.Numeric(precision=14, scale=2), nullable=True),
        sa.Column("owner_user_id", sa.String(length=64), nullable=True),
        sa.Column("converted_account_id", sa.Integer(), nullable=True),
        sa.Column("converted_contact_id", sa.Integer(), nullable=True),
        sa.Column("converted_opportunity_id", sa.Integer(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["converted_account_id"], [f"{SCHEMA}.accounts.id"]),
        sa.ForeignKeyConstraint(["converted_contact_id"], [f"{SCHEMA}.contacts.id"]),
        sa.ForeignKeyConstraint(["converted_opportunity_id"], [f"{SCHEMA}.opportunities.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("leads")
    op.create_index("ix_crm_leads_email", "leads", ["email"], schema=SCHEMA)
    op.create_index("ix_crm_leads_status", "leads", ["status"], schema=SCHEMA)
    op.create_index("ix_crm_leads_owner_user_id", "leads", ["owner_user_id"], schema=SCHEMA)

    # ----- activities -----
    op.create_table(
        "activities",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("activity_type", sa.String(length=20), nullable=False),
        sa.Column("subject", sa.String(length=255), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'pending'")),
        sa.Column("due_date", sa.DateTime(), nullable=True),
        sa.Column("completed_at", sa.DateTime(), nullable=True),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("contact_id", sa.Integer(), nullable=True),
        sa.Column("lead_id", sa.Integer(), nullable=True),
        sa.Column("opportunity_id", sa.Integer(), nullable=True),
        sa.Column("owner_user_id", sa.String(length=64), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["account_id"], [f"{SCHEMA}.accounts.id"]),
        sa.ForeignKeyConstraint(["contact_id"], [f"{SCHEMA}.contacts.id"]),
        sa.ForeignKeyConstraint(["lead_id"], [f"{SCHEMA}.leads.id"]),
        sa.ForeignKeyConstraint(["opportunity_id"], [f"{SCHEMA}.opportunities.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("activities")
    op.create_index("ix_crm_activities_status", "activities", ["status"], schema=SCHEMA)
    op.create_index("ix_crm_activities_account_id", "activities", ["account_id"], schema=SCHEMA)
    op.create_index("ix_crm_activities_contact_id", "activities", ["contact_id"], schema=SCHEMA)
    op.create_index("ix_crm_activities_lead_id", "activities", ["lead_id"], schema=SCHEMA)
    op.create_index("ix_crm_activities_opportunity_id", "activities", ["opportunity_id"], schema=SCHEMA)
    op.create_index("ix_crm_activities_owner_user_id", "activities", ["owner_user_id"], schema=SCHEMA)


def downgrade() -> None:
    # Orden inverso al de creación para respetar las FK.
    # DROP TABLE elimina automáticamente los índices asociados en PostgreSQL.
    op.drop_table("activities", schema=SCHEMA)
    op.drop_table("leads", schema=SCHEMA)
    op.drop_table("opportunities", schema=SCHEMA)
    op.drop_table("contacts", schema=SCHEMA)
    op.drop_table("accounts", schema=SCHEMA)
    op.drop_table("pipeline_stages", schema=SCHEMA)
    op.drop_table("pipelines", schema=SCHEMA)
    op.execute(f"DROP SCHEMA IF EXISTS {SCHEMA}")
