"""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}")