"""Add WP AI chat session/message/job tables and site understanding runs.

Replaces the in-memory wordpress_chat shim with persisted Core-owned tables.
SiteUnderstandingRun uses a platform discriminator so Shopify can reuse the
same table without schema duplication.

Revision ID: 076
Revises: 075
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import JSONB, UUID

revision: str = "076"
down_revision: Union[str, None] = "075"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "external_admin_chat_sessions",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "installation_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "project_id",
            UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("site_url", sa.Text, nullable=False),
        sa.Column("status", sa.String(20), nullable=False, server_default="active"),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_chat_sessions_installation_id", "external_admin_chat_sessions", ["installation_id"])
    op.create_index("ix_chat_sessions_project_id", "external_admin_chat_sessions", ["project_id"])
    op.create_index(
        "ix_chat_sessions_installation_status",
        "external_admin_chat_sessions",
        ["installation_id", "status"],
    )

    op.create_table(
        "external_admin_chat_messages",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "session_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_chat_sessions.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "installation_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("role", sa.String(20), nullable=False),
        sa.Column("content", sa.Text, nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_chat_messages_session_id", "external_admin_chat_messages", ["session_id"])
    op.create_index("ix_chat_messages_installation_id", "external_admin_chat_messages", ["installation_id"])
    op.create_index(
        "ix_chat_messages_session_created",
        "external_admin_chat_messages",
        ["session_id", "created_at"],
    )

    op.create_table(
        "external_admin_chat_jobs",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "session_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_chat_sessions.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "installation_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
        sa.Column("run_class", sa.String(40), nullable=True),
        sa.Column(
            "user_message_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_chat_messages.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "assistant_message_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_chat_messages.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "action_session_id",
            UUID(as_uuid=True),
            sa.ForeignKey("assistant_action_sessions.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("idempotency_key", sa.String(128), nullable=True),
        sa.Column("context_json", JSONB, nullable=True),
        sa.Column("result_json", JSONB, nullable=True),
        sa.Column("error", sa.Text, nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.UniqueConstraint("installation_id", "idempotency_key", name="uq_chat_job_idempotency"),
    )
    op.create_index("ix_chat_jobs_session_id", "external_admin_chat_jobs", ["session_id"])
    op.create_index("ix_chat_jobs_installation_id", "external_admin_chat_jobs", ["installation_id"])
    op.create_index("ix_chat_jobs_status", "external_admin_chat_jobs", ["status"])

    op.create_table(
        "site_understanding_runs",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "installation_id",
            UUID(as_uuid=True),
            sa.ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "project_id",
            UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("platform", sa.String(20), nullable=False, server_default="wordpress"),
        sa.Column("status", sa.String(20), nullable=False, server_default="queued"),
        sa.Column("reason", sa.String(40), nullable=False, server_default="manual"),
        sa.Column("triggered_by", sa.String(40), nullable=True),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("summary_json", JSONB, nullable=True),
        sa.Column("payload_json", JSONB, nullable=True),
        sa.Column("artifacts_json", JSONB, nullable=True),
        sa.Column("error", sa.Text, nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
    )
    op.create_index("ix_su_runs_installation_id", "site_understanding_runs", ["installation_id"])
    op.create_index("ix_su_runs_project_id", "site_understanding_runs", ["project_id"])
    op.create_index("ix_su_runs_status", "site_understanding_runs", ["status"])
    op.create_index(
        "ix_su_runs_installation_created",
        "site_understanding_runs",
        ["installation_id", "created_at"],
    )


def downgrade() -> None:
    op.drop_table("site_understanding_runs")
    op.drop_table("external_admin_chat_jobs")
    op.drop_table("external_admin_chat_messages")
    op.drop_table("external_admin_chat_sessions")