"""Add whatsapp_account, whatsapp_template tables and conversation.last_customer_message_at.

Revision ID: 0021_whatsapp_tables_and_session_window
Revises: 0020_add_message_history_json
Create Date: 2026-03-12
"""

from collections.abc import Sequence

import sqlalchemy as sa
import sqlmodel
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "0021_whatsapp_tables_and_session_window"
down_revision: str | None = "0020_add_message_history_json"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # --- whatsapp_account table ---
    op.create_table(
        "whatsapp_account",
        sa.Column("restaurant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("waba_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("phone_number_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("phone_number", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("display_name", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("access_token_encrypted", sa.LargeBinary(), nullable=False),
        sa.Column("token_encryption_key_version", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("quality_rating", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("messaging_limit", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("is_verified", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("connected_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_whatsapp_account_restaurant_id"),
        "whatsapp_account",
        ["restaurant_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_whatsapp_account_phone_number_id"),
        "whatsapp_account",
        ["phone_number_id"],
        unique=True,
    )

    # --- whatsapp_template table ---
    op.create_table(
        "whatsapp_template",
        sa.Column("restaurant_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=True),
        sa.Column("id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("whatsapp_account_id", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("meta_template_id", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("name", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("category", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column("language", sqlmodel.sql.sqltypes.AutoString(), nullable=False),
        sa.Column(
            "status",
            sqlmodel.sql.sqltypes.AutoString(),
            nullable=False,
            server_default="PENDING",
        ),
        sa.Column(
            "components",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
        ),
        sa.Column("rejection_reason", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.Column("quality_score", sqlmodel.sql.sqltypes.AutoString(), nullable=True),
        sa.ForeignKeyConstraint(["restaurant_id"], ["restaurant.id"]),
        sa.ForeignKeyConstraint(["whatsapp_account_id"], ["whatsapp_account.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "whatsapp_account_id",
            "name",
            "language",
            name="uq_wa_template_account_name_lang",
        ),
    )
    op.create_index(
        op.f("ix_whatsapp_template_restaurant_id"),
        "whatsapp_template",
        ["restaurant_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_whatsapp_template_whatsapp_account_id"),
        "whatsapp_template",
        ["whatsapp_account_id"],
        unique=False,
    )

    # --- Add last_customer_message_at to conversation ---
    op.add_column(
        "conversation",
        sa.Column("last_customer_message_at", sa.DateTime(), nullable=True),
    )


def downgrade() -> None:
    op.drop_column("conversation", "last_customer_message_at")
    op.drop_table("whatsapp_template")
    op.drop_table("whatsapp_account")