"""Create the initial MemRelay schema."""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "20260802_0001"
down_revision: str | Sequence[str] | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "administrators",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("username", sa.String(100), nullable=False, unique=True),
        sa.Column("password_hash", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_table(
        "web_sessions",
        sa.Column("id", sa.String(96), primary_key=True),
        sa.Column(
            "administrator_id",
            sa.Integer(),
            sa.ForeignKey("administrators.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("csrf_digest", sa.String(64), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("last_seen_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_web_sessions_administrator_id", "web_sessions", ["administrator_id"])
    op.create_index("ix_web_sessions_expires_at", "web_sessions", ["expires_at"])
    op.create_table(
        "mcp_tokens",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("name", sa.String(100), nullable=False),
        sa.Column("token_digest", sa.String(64), nullable=False, unique=True),
        sa.Column("encrypted_token", sa.Text(), nullable=False),
        sa.Column("access_mode", sa.String(16), nullable=False),
        sa.Column("revoked", sa.Boolean(), nullable=False),
        sa.Column("last_used_at", sa.DateTime(timezone=True)),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_mcp_tokens_token_digest", "mcp_tokens", ["token_digest"])
    op.create_index("ix_mcp_tokens_revoked", "mcp_tokens", ["revoked"])
    op.create_table(
        "projects",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("slug", sa.String(200), nullable=False, unique=True),
        sa.Column("git_remote", sa.Text(), unique=True),
        sa.Column("archived", sa.Boolean(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_projects_slug", "projects", ["slug"])
    op.create_index("ix_projects_archived", "projects", ["archived"])
    op.create_table(
        "project_aliases",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("project_id", sa.String(36), sa.ForeignKey("projects.id", ondelete="CASCADE")),
        sa.Column("kind", sa.String(32), nullable=False),
        sa.Column("value", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("kind", "value", name="uq_project_alias_kind_value"),
    )
    op.create_index("ix_project_aliases_project_id", "project_aliases", ["project_id"])
    op.create_table(
        "memory_references",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("project_id", sa.String(36), sa.ForeignKey("projects.id", ondelete="SET NULL")),
        sa.Column("scope", sa.String(16), nullable=False),
        sa.Column("memory_type", sa.String(32), nullable=False),
        sa.Column("path", sa.Text(), nullable=False, unique=True),
        sa.Column("title", sa.String(300), nullable=False),
        sa.Column("revision", sa.Integer(), nullable=False),
        sa.Column("status", sa.String(32), nullable=False),
        sa.Column("tags_json", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_memory_references_project_id", "memory_references", ["project_id"])
    op.create_index("ix_memory_references_scope", "memory_references", ["scope"])
    op.create_index("ix_memory_references_memory_type", "memory_references", ["memory_type"])
    op.create_index("ix_memory_references_status", "memory_references", ["status"])
    op.create_index(
        "ix_memory_scope_project_type",
        "memory_references",
        ["scope", "project_id", "memory_type"],
    )
    op.create_table(
        "idempotency_records",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("operation", sa.String(100), nullable=False),
        sa.Column("request_id", sa.String(100), nullable=False),
        sa.Column("response_json", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("operation", "request_id", name="uq_idempotency_request"),
    )
    op.create_table(
        "vault_connections",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("encrypted_server_url", sa.Text(), nullable=False),
        sa.Column("encrypted_account", sa.Text(), nullable=False),
        sa.Column("encrypted_password", sa.Text(), nullable=False),
        sa.Column("enabled", sa.Boolean(), nullable=False),
        sa.Column("status", sa.String(32), nullable=False),
        sa.Column("last_sync_at", sa.DateTime(timezone=True)),
        sa.Column("last_error", sa.Text()),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_table(
        "credential_mappings",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("lookup_key", sa.String(300), nullable=False),
        sa.Column("vault_item_id", sa.String(100), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.UniqueConstraint("lookup_key", name="uq_credential_mapping_lookup"),
    )
    op.create_index("ix_credential_mappings_lookup_key", "credential_mappings", ["lookup_key"])
    op.create_table(
        "file_records",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("project_id", sa.String(36), sa.ForeignKey("projects.id", ondelete="SET NULL")),
        sa.Column("storage_path", sa.Text(), nullable=False, unique=True),
        sa.Column("name", sa.String(300), nullable=False),
        sa.Column("description", sa.Text()),
        sa.Column("version", sa.String(100)),
        sa.Column("purpose", sa.String(200)),
        sa.Column("tags_json", sa.Text(), nullable=False),
        sa.Column("mime_type", sa.String(200), nullable=False),
        sa.Column("size", sa.Integer(), nullable=False),
        sa.Column("checksum_sha256", sa.String(64), nullable=False),
        sa.Column("status", sa.String(32), nullable=False),
        sa.Column("modified_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_file_records_project_id", "file_records", ["project_id"])
    op.create_index("ix_file_records_name", "file_records", ["name"])
    op.create_index("ix_file_records_version", "file_records", ["version"])
    op.create_index("ix_file_records_purpose", "file_records", ["purpose"])
    op.create_index("ix_file_records_checksum_sha256", "file_records", ["checksum_sha256"])
    op.create_index("ix_file_records_status", "file_records", ["status"])
    op.create_index("ix_file_project_status", "file_records", ["project_id", "status"])
    op.create_table(
        "upload_tasks",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("target_path", sa.Text(), nullable=False),
        sa.Column("expected_size", sa.Integer(), nullable=False),
        sa.Column("expected_sha256", sa.String(64)),
        sa.Column("token_digest", sa.String(64), nullable=False, unique=True),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("status", sa.String(32), nullable=False),
        sa.Column("error_message", sa.Text()),
        sa.Column("file_id", sa.String(36), sa.ForeignKey("file_records.id", ondelete="SET NULL")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )
    op.create_index("ix_upload_tasks_token_digest", "upload_tasks", ["token_digest"])
    op.create_index("ix_upload_tasks_expires_at", "upload_tasks", ["expires_at"])
    op.create_index("ix_upload_tasks_status", "upload_tasks", ["status"])
    op.create_table(
        "system_settings",
        sa.Column("key", sa.String(100), primary_key=True),
        sa.Column("value_json", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
    )


def downgrade() -> None:
    for table in (
        "system_settings",
        "upload_tasks",
        "file_records",
        "credential_mappings",
        "vault_connections",
        "idempotency_records",
        "memory_references",
        "project_aliases",
        "projects",
        "mcp_tokens",
        "web_sessions",
        "administrators",
    ):
        op.drop_table(table)