"""External admin installations domain - SQLAlchemy models.

Tracks WordPress and Shopify AI-assist project installations, external
credentials (versioned/rotated), action sessions requiring human approval,
per-project assistant settings, and data-sharing consents.
"""
from __future__ import annotations

from datetime import datetime
from typing import Optional
from uuid import UUID as PyUUID, uuid4

import sqlalchemy as sa
from sqlalchemy import BigInteger, Boolean, ForeignKey, Integer, String, Text
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func
from sqlalchemy.types import DateTime

from src.core.database import Base

# ── Installation ───────────────────────────────────────────────────────────────

INSTALLATION_TYPE_SHOPIFY = "shopify"
INSTALLATION_TYPE_WORDPRESS = "wordpress"
INSTALLATION_TYPES = (INSTALLATION_TYPE_SHOPIFY, INSTALLATION_TYPE_WORDPRESS)

INSTALLATION_STATUS_PENDING = "pending"
INSTALLATION_STATUS_CONNECTED = "connected"
INSTALLATION_STATUS_DISCONNECTED = "disconnected"
INSTALLATION_STATUS_TAMPERED = "tampered"
INSTALLATION_STATUS_ERROR = "error"
# Shopify shop/redact (GDPR): all shop data erased; the row is kept only as a tombstone.
INSTALLATION_STATUS_REDACTED = "redacted"
INSTALLATION_STATUSES = (
    INSTALLATION_STATUS_PENDING,
    INSTALLATION_STATUS_CONNECTED,
    INSTALLATION_STATUS_DISCONNECTED,
    INSTALLATION_STATUS_TAMPERED,
    INSTALLATION_STATUS_ERROR,
    INSTALLATION_STATUS_REDACTED,
)

# ── Credential ─────────────────────────────────────────────────────────────────

CREDENTIAL_TYPE_SHOPIFY_ACCESS_TOKEN = "shopify_access_token"
CREDENTIAL_TYPE_WP_CREDENTIAL_TOKEN = "wp_credential_token"
CREDENTIAL_TYPE_COMPANION_CLIENT_TOKEN = "companion_client_token"
CREDENTIAL_TYPES = (
    CREDENTIAL_TYPE_SHOPIFY_ACCESS_TOKEN,
    CREDENTIAL_TYPE_WP_CREDENTIAL_TOKEN,
    CREDENTIAL_TYPE_COMPANION_CLIENT_TOKEN,
)

CREDENTIAL_STATUS_ACTIVE = "active"
CREDENTIAL_STATUS_ROTATED = "rotated"
CREDENTIAL_STATUS_REVOKED = "revoked"
CREDENTIAL_STATUSES = (CREDENTIAL_STATUS_ACTIVE, CREDENTIAL_STATUS_ROTATED, CREDENTIAL_STATUS_REVOKED)

# ── Action session ─────────────────────────────────────────────────────────────

ACTION_SESSION_STATUS_PENDING = "pending"
ACTION_SESSION_STATUS_APPROVED = "approved"
ACTION_SESSION_STATUS_REJECTED = "rejected"
ACTION_SESSION_STATUS_EXECUTING = "executing"  # queued for companion pickup
ACTION_SESSION_STATUS_APPLIED = "applied"
ACTION_SESSION_STATUS_FAILED = "failed"
ACTION_SESSION_STATUS_EXPIRED = "expired"
ACTION_SESSION_STATUSES = (
    ACTION_SESSION_STATUS_PENDING,
    ACTION_SESSION_STATUS_APPROVED,
    ACTION_SESSION_STATUS_REJECTED,
    ACTION_SESSION_STATUS_APPLIED,
    ACTION_SESSION_STATUS_FAILED,
    ACTION_SESSION_STATUS_EXPIRED,
)

# ── Consent ────────────────────────────────────────────────────────────────────

CONSENT_TYPE_DATA_SYNC = "data_sync"
CONSENT_TYPE_ANALYTICS_READ = "analytics_read"
CONSENT_TYPE_CONTENT_INDEX = "content_index"
CONSENT_TYPES = (CONSENT_TYPE_DATA_SYNC, CONSENT_TYPE_ANALYTICS_READ, CONSENT_TYPE_CONTENT_INDEX)

CONSENT_STATUS_GRANTED = "granted"
CONSENT_STATUS_REVOKED = "revoked"
CONSENT_STATUSES = (CONSENT_STATUS_GRANTED, CONSENT_STATUS_REVOKED)


class ExternalAdminInstallation(Base):
    """One-to-one with a project; tracks the installed plugin/app on the merchant's store or site."""

    __tablename__ = "external_admin_installations"

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, unique=True, index=True
    )
    workspace_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False, index=True
    )
    installation_type: Mapped[str] = mapped_column(String(20), nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending")
    # Core-issued opaque installation token, sent to the plugin on connect.
    installation_token_enc: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    shop_domain: Mapped[Optional[str]] = mapped_column(String(255), nullable=True, index=True)
    site_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    plugin_version: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    companion_version: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    companion_file_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    plugin_file_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    last_heartbeat_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    connected_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    disconnected_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    tampered_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    last_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    metadata_json: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class ExternalCredential(Base):
    """Versioned, encrypted credential tied to an external admin installation.

    A new row is inserted on each rotation; the previous row is set to 'rotated'.
    The current active credential is the one with status='active' and the highest version.
    """

    __tablename__ = "external_credentials"
    __table_args__ = (
        sa.UniqueConstraint("installation_id", "credential_type", "version", name="uq_ext_cred_version"),
    )

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True
    )
    credential_type: Mapped[str] = mapped_column(String(40), nullable=False)
    version: Mapped[int] = mapped_column(Integer, nullable=False, server_default="1")
    value_enc: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="active")
    rotated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    rotated_by_user_id: Mapped[Optional[PyUUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class AssistantProjectSettings(Base):
    """Per-project configuration for the AI assistant experience."""

    __tablename__ = "assistant_project_settings"

    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), primary_key=True
    )
    ai_usage_mode: Mapped[str] = mapped_column(String(20), nullable=False, server_default="p2hs_credits")
    usage_budget: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True)
    chat_session_ttl_hours: Mapped[int] = mapped_column(Integer, nullable=False, server_default="8")
    action_approval_required: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=sa.true())
    allowed_capabilities: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]")
    notify_on_approval_required: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=sa.true())
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class AssistantActionSession(Base):
    """Tracks a single write-or-deploy action that required P2HS-hosted approval.

    Created by Core when the assistant requests an elevated action. The
    dashboard user approves or rejects here; external runtimes receive only
    scoped, signed execution grants derived from this row.
    """

    __tablename__ = "assistant_action_sessions"

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True
    )
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    correlation_id: Mapped[str] = mapped_column(String(64), nullable=False, unique=True, index=True)
    action: Mapped[str] = mapped_column(String(120), nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending", index=True)
    requested_by: Mapped[Optional[str]] = mapped_column(String(120), nullable=True)
    reviewed_by_user_id: Mapped[Optional[PyUUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    result: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    applied_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    reviewed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class AssistantDataConsent(Base):
    """Records merchant/site-owner consent for data-sharing capabilities."""

    __tablename__ = "assistant_data_consents"
    __table_args__ = (
        sa.UniqueConstraint("project_id", "consent_type", name="uq_consent_project_type"),
    )

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True
    )
    consent_type: Mapped[str] = mapped_column(String(40), nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="granted")
    granted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    granted_by_user_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False
    )
    notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


# ── Chat session ───────────────────────────────────────────────────────────────

CHAT_SESSION_STATUS_ACTIVE = "active"
CHAT_SESSION_STATUS_EXPIRED = "expired"
CHAT_SESSION_STATUS_CLOSED = "closed"

CHAT_JOB_STATUS_PENDING = "pending"
CHAT_JOB_STATUS_RUNNING = "running"
CHAT_JOB_STATUS_COMPLETED = "completed"
CHAT_JOB_STATUS_FAILED = "failed"

# Run classification mirrors the execution-lane taxonomy from the architecture.
CHAT_JOB_RUN_CLASS_WP_DATA_MUTATION = "wp_data_mutation"
CHAT_JOB_RUN_CLASS_COMPANION_UPDATE = "companion_update"
CHAT_JOB_RUN_CLASS_HYBRID = "hybrid"
CHAT_JOB_RUN_CLASS_READ_REPORT = "read_report"

# ── Site Understanding run ─────────────────────────────────────────────────────

SITE_UNDERSTANDING_STATUS_QUEUED = "queued"
SITE_UNDERSTANDING_STATUS_RUNNING = "running"
SITE_UNDERSTANDING_STATUS_COMPLETED = "completed"
SITE_UNDERSTANDING_STATUS_FAILED = "failed"
SITE_UNDERSTANDING_STATUS_CANCELLED = "cancelled"


class ExternalAdminChatSession(Base):
    """Persisted WP AI chat session. One active session per installation at a time."""

    __tablename__ = "external_admin_chat_sessions"

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True
    )
    site_url: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="active")
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class ExternalAdminChatMessage(Base):
    """Single message in a WP AI chat session (user or assistant)."""

    __tablename__ = "external_admin_chat_messages"

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    session_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_chat_sessions.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    role: Mapped[str] = mapped_column(String(20), nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class ExternalAdminChatJob(Base):
    """One assistant run triggered by a user message.

    Tracks the execution lifecycle from pending through completed/failed.
    run_class is set by Core when it classifies the user's intent:
    wp_data_mutation, companion_update, hybrid, or read_report.
    """

    __tablename__ = "external_admin_chat_jobs"
    __table_args__ = (
        sa.UniqueConstraint("installation_id", "idempotency_key", name="uq_chat_job_idempotency"),
    )

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    session_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_chat_sessions.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending", index=True)
    run_class: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    user_message_id: Mapped[Optional[PyUUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_chat_messages.id", ondelete="SET NULL"),
        nullable=True,
    )
    assistant_message_id: Mapped[Optional[PyUUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_chat_messages.id", ondelete="SET NULL"),
        nullable=True,
    )
    action_session_id: Mapped[Optional[PyUUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("assistant_action_sessions.id", ondelete="SET NULL"),
        nullable=True,
    )
    idempotency_key: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
    context_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    result_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class SiteUnderstandingRun(Base):
    """One Site Understanding pipeline execution for a site.

    platform discriminator is 'wordpress' now; 'shopify' will reuse the same table.
    Cloud D1 stores the collected site shape; this table tracks run lifecycle in Core.
    """

    __tablename__ = "site_understanding_runs"

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True
    )
    platform: Mapped[str] = mapped_column(String(20), nullable=False, server_default="wordpress")
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="queued", index=True)
    reason: Mapped[str] = mapped_column(String(40), nullable=False, server_default="manual")
    triggered_by: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    summary_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    payload_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    artifacts_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


# ── WP Mutation action ─────────────────────────────────────────────────────────

WP_MUTATION_ACTION_STATUS_PENDING = "pending"
WP_MUTATION_ACTION_STATUS_RUNNING = "running"
WP_MUTATION_ACTION_STATUS_COMPLETED = "completed"
WP_MUTATION_ACTION_STATUS_FAILED = "failed"
WP_MUTATION_ACTION_STATUS_SKIPPED = "skipped"


class WpMutationAction(Base):
    """One operation within an approved WP mutation plan.

    A single AssistantActionSession may contain many operations executed
    sequentially. Records per-operation state, result, and before/after
    object hashes for rollback.
    """

    __tablename__ = "wp_mutation_actions"

    id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    action_session_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("assistant_action_sessions.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    installation_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("external_admin_installations.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    project_id: Mapped[PyUUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True
    )
    sequence_number: Mapped[int] = mapped_column(Integer, nullable=False)
    action: Mapped[str] = mapped_column(String(80), nullable=False)
    description: Mapped[str] = mapped_column(Text, nullable=False)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}")
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending", index=True)
    before_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    after_hash: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    result_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    executed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )