"""Add ai_assist project types and external admin tables.

Adds 'ai_assist_shopify' and 'ai_assist_wordpress' to p2hs_project_type enum,
and creates tables for external admin installations, credentials, assistant
settings, action sessions, and data consents.

Revision ID: 075
Revises: 074
"""

from typing import Sequence, Union

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

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


def upgrade() -> None:
    # Extend the project type enum with two new AI-assist values.
    op.execute("ALTER TYPE p2hs_project_type ADD VALUE IF NOT EXISTS 'ai_assist_shopify'")
    op.execute("ALTER TYPE p2hs_project_type ADD VALUE IF NOT EXISTS 'ai_assist_wordpress'")

    op.create_table(
        "external_admin_installations",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
            unique=True,
        ),
        sa.Column(
            "workspace_id",
            UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("installation_type", sa.String(20), nullable=False),
        sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
        sa.Column("installation_token_enc", sa.Text, nullable=True),
        sa.Column("shop_domain", sa.String(255), nullable=True),
        sa.Column("site_url", sa.Text, nullable=True),
        sa.Column("companion_version", sa.String(40), nullable=True),
        sa.Column("companion_file_hash", sa.String(64), nullable=True),
        sa.Column("plugin_file_hash", sa.String(64), nullable=True),
        sa.Column("last_heartbeat_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("connected_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("disconnected_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("tampered_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_error", sa.Text, nullable=True),
        sa.Column("metadata_json", JSONB, nullable=False, server_default="{}"),
        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_ext_admin_inst_workspace_id", "external_admin_installations", ["workspace_id"])
    op.create_index("ix_ext_admin_inst_shop_domain", "external_admin_installations", ["shop_domain"])

    op.create_table(
        "external_credentials",
        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("credential_type", sa.String(40), nullable=False),
        sa.Column("version", sa.Integer, nullable=False, server_default="1"),
        sa.Column("value_enc", sa.Text, nullable=False),
        sa.Column("status", sa.String(20), nullable=False, server_default="active"),
        sa.Column("rotated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "rotated_by_user_id",
            UUID(as_uuid=True),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            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", "credential_type", "version", name="uq_ext_cred_version"),
    )
    op.create_index("ix_ext_cred_installation_id", "external_credentials", ["installation_id"])
    op.create_index("ix_ext_cred_project_id", "external_credentials", ["project_id"])

    op.create_table(
        "assistant_project_settings",
        sa.Column(
            "project_id",
            UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("ai_usage_mode", sa.String(20), nullable=False, server_default="p2hs_credits"),
        sa.Column("usage_budget", sa.BigInteger, nullable=True),
        sa.Column("chat_session_ttl_hours", sa.Integer, nullable=False, server_default="8"),
        sa.Column("action_approval_required", sa.Boolean, nullable=False, server_default=sa.true()),
        sa.Column("allowed_capabilities", JSONB, nullable=False, server_default="[]"),
        sa.Column("notify_on_approval_required", sa.Boolean, nullable=False, server_default=sa.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_table(
        "assistant_action_sessions",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            UUID(as_uuid=True),
            sa.ForeignKey("projects.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("correlation_id", sa.String(64), nullable=False, unique=True),
        sa.Column("action", sa.String(120), nullable=False),
        sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
        sa.Column("requested_by", sa.String(120), nullable=True),
        sa.Column(
            "reviewed_by_user_id",
            UUID(as_uuid=True),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("payload", JSONB, nullable=False, server_default="{}"),
        sa.Column("result", JSONB, nullable=True),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("applied_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("reviewed_at", sa.DateTime(timezone=True), 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_action_sessions_project_id", "assistant_action_sessions", ["project_id"])
    op.create_index("ix_action_sessions_installation_id", "assistant_action_sessions", ["installation_id"])
    op.create_index("ix_action_sessions_correlation_id", "assistant_action_sessions", ["correlation_id"])
    op.create_index("ix_action_sessions_status", "assistant_action_sessions", ["status"])

    op.create_table(
        "assistant_data_consents",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("consent_type", sa.String(40), nullable=False),
        sa.Column("status", sa.String(20), nullable=False, server_default="granted"),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "granted_by_user_id",
            UUID(as_uuid=True),
            sa.ForeignKey("users.id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column("notes", 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("project_id", "consent_type", name="uq_consent_project_type"),
    )
    op.create_index("ix_data_consents_project_id", "assistant_data_consents", ["project_id"])


def downgrade() -> None:
    op.drop_table("assistant_data_consents")
    op.drop_table("assistant_action_sessions")
    op.drop_table("assistant_project_settings")
    op.drop_table("external_credentials")
    op.drop_table("external_admin_installations")
    # PostgreSQL does not support removing enum values; downgrade leaves the enum values in place.