"""Initial database schema with all models - consolidated migration.

Revision ID: init_full_schema
Revises:
Create Date: 2026-02-14 15:00:00.000000+00:00

This migration creates the complete OpenScientist database schema including:
- User authentication (users, oauth_accounts, sessions, api_keys)
- Jobs and knowledge state
- Skills and skill sources
- MCP servers
- Administrators
- Row-level security policies
- Full-text search indexes

This is a consolidated migration that replaces the previous incremental migrations.
"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = "init_full_schema"
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Create the complete OpenScientist database schema."""

    # =========================================================================
    # EXTENSIONS
    # =========================================================================
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")

    # =========================================================================
    # USERS TABLE
    # =========================================================================
    op.create_table(
        "users",
        sa.Column(
            "email",
            sa.String(length=255),
            nullable=False,
            comment="User's primary email address",
        ),
        sa.Column("name", sa.String(length=255), nullable=False, comment="User's display name"),
        sa.Column(
            "is_active",
            sa.Boolean(),
            server_default="true",
            nullable=False,
            comment="Whether user account is active",
        ),
        sa.Column(
            "is_approved",
            sa.Boolean(),
            server_default="false",
            nullable=False,
            comment="Whether user is approved by an administrator to start jobs",
        ),
        sa.Column(
            "ntfy_enabled",
            sa.Boolean(),
            server_default="true",
            nullable=False,
            comment="Whether push notifications via ntfy.sh are enabled",
        ),
        sa.Column(
            "ntfy_topic",
            sa.String(length=64),
            nullable=True,
            unique=True,
            comment="Unique ntfy.sh topic for this user's notifications",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_users_email"), "users", ["email"], unique=True)

    # Trigram indexes for fuzzy search
    op.execute(
        """
        CREATE INDEX IF NOT EXISTS ix_users_email_trgm
        ON users USING gin (email gin_trgm_ops)
        """
    )
    op.execute(
        """
        CREATE INDEX IF NOT EXISTS ix_users_name_trgm
        ON users USING gin (name gin_trgm_ops)
        """
    )

    # =========================================================================
    # API KEYS TABLE
    # =========================================================================
    op.create_table(
        "api_keys",
        sa.Column("user_id", sa.UUID(), nullable=False, comment="User who owns this API key"),
        sa.Column(
            "name",
            sa.String(length=100),
            nullable=False,
            comment="User-chosen descriptive name for the key",
        ),
        sa.Column(
            "key_hash",
            sa.Text(),
            nullable=False,
            comment="Hashed secret portion of the API key",
        ),
        sa.Column(
            "last_used_at",
            sa.DateTime(timezone=True),
            nullable=True,
            comment="Timestamp when key was last used (UTC)",
        ),
        sa.Column(
            "is_active",
            sa.Boolean(),
            server_default="true",
            nullable=False,
            comment="Whether the key is active (for revocation)",
        ),
        sa.Column(
            "usage_count",
            sa.Integer(),
            server_default="0",
            nullable=False,
            comment="Number of times this API key has been used",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("key_hash"),
        sa.UniqueConstraint("user_id", "name", name="uq_api_keys_user_name"),
    )
    op.create_index(op.f("ix_api_keys_user_id"), "api_keys", ["user_id"], unique=False)

    # =========================================================================
    # OAUTH ACCOUNTS TABLE
    # =========================================================================
    op.create_table(
        "oauth_accounts",
        sa.Column(
            "user_id",
            sa.UUID(),
            nullable=False,
            comment="User who owns this OAuth account",
        ),
        sa.Column(
            "provider",
            sa.String(length=50),
            nullable=False,
            comment="OAuth provider name (github, orcid)",
        ),
        sa.Column(
            "provider_user_id",
            sa.String(length=255),
            nullable=False,
            comment="User's unique ID on the OAuth provider",
        ),
        sa.Column(
            "email",
            sa.String(length=255),
            nullable=False,
            comment="Email address from OAuth provider",
        ),
        sa.Column(
            "name",
            sa.String(length=255),
            nullable=False,
            comment="Display name from OAuth provider",
        ),
        sa.Column(
            "access_token",
            sa.Text(),
            nullable=True,
            comment="OAuth access token (encrypted in production)",
        ),
        sa.Column(
            "refresh_token",
            sa.Text(),
            nullable=True,
            comment="OAuth refresh token (encrypted in production)",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("provider", "provider_user_id", name="uq_oauth_provider_user"),
    )
    op.create_index(
        op.f("ix_oauth_accounts_provider"), "oauth_accounts", ["provider"], unique=False
    )
    op.create_index(op.f("ix_oauth_accounts_user_id"), "oauth_accounts", ["user_id"], unique=False)

    # =========================================================================
    # SESSIONS TABLE
    # =========================================================================
    op.create_table(
        "sessions",
        sa.Column("user_id", sa.UUID(), nullable=False, comment="User who owns this session"),
        sa.Column(
            "expires_at",
            sa.DateTime(timezone=True),
            nullable=False,
            comment="Timestamp when session expires (UTC)",
        ),
        sa.Column(
            "ip_address",
            sa.String(length=45),
            nullable=True,
            comment="IP address where session was created",
        ),
        sa.Column(
            "user_agent",
            sa.Text(),
            nullable=True,
            comment="Browser user agent string",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_sessions_expires_at"), "sessions", ["expires_at"], unique=False)
    op.create_index(op.f("ix_sessions_user_id"), "sessions", ["user_id"], unique=False)

    # =========================================================================
    # ADMINISTRATORS TABLE
    # =========================================================================
    op.create_table(
        "administrators",
        sa.Column(
            "user_id",
            sa.UUID(),
            nullable=False,
            comment="User who has admin privileges",
        ),
        sa.Column(
            "granted_by",
            sa.UUID(),
            nullable=True,
            comment="User who granted admin privileges (NULL for initial setup)",
        ),
        sa.Column(
            "granted_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when admin was granted (UTC)",
        ),
        sa.Column(
            "notes",
            sa.Text(),
            nullable=True,
            comment="Optional notes about why admin was granted",
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["users.id"],
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["granted_by"],
            ["users.id"],
            ondelete="SET NULL",
        ),
        sa.PrimaryKeyConstraint("user_id"),
    )

    # =========================================================================
    # JOBS TABLE
    # =========================================================================
    op.create_table(
        "jobs",
        sa.Column(
            "owner_id",
            sa.UUID(),
            nullable=True,
            comment="User who owns this job (NULL for orphaned legacy jobs)",
        ),
        sa.Column(
            "title",
            sa.Text(),
            nullable=False,
            comment="Research question / job title",
        ),
        sa.Column(
            "short_title",
            sa.String(100),
            nullable=True,
            comment="Brief model-generated title (e.g., 'Kinase inhibitor analysis')",
        ),
        sa.Column(
            "description",
            sa.Text(),
            nullable=True,
            comment="User-provided job description",
        ),
        sa.Column(
            "use_hypotheses",
            sa.Boolean(),
            server_default="false",
            nullable=False,
            comment="Whether hypothesis tracking tools are enabled",
        ),
        sa.Column(
            "investigation_mode",
            sa.String(length=20),
            server_default="autonomous",
            nullable=False,
            comment="Investigation mode (autonomous or coinvestigate)",
        ),
        sa.Column(
            "status",
            sa.String(length=50),
            server_default="pending",
            nullable=False,
            comment="Current job status (pending/running/completed/failed/cancelled)",
        ),
        sa.Column(
            "max_iterations",
            sa.Integer(),
            server_default="5",
            nullable=False,
            comment="Maximum number of analysis iterations allowed",
        ),
        sa.Column(
            "current_iteration",
            sa.Integer(),
            server_default="0",
            nullable=False,
            comment="Current iteration number (0-based)",
        ),
        sa.Column(
            "pdb_code",
            sa.String(length=10),
            nullable=True,
            comment="PDB code if analyzing existing structure",
        ),
        sa.Column(
            "space_group",
            sa.String(length=50),
            nullable=True,
            comment="Crystal space group",
        ),
        sa.Column(
            "resume_iteration",
            sa.Integer(),
            nullable=True,
            comment="Iteration to resume from (NULL for new jobs)",
        ),
        sa.Column(
            "llm_provider",
            sa.String(length=50),
            server_default="vertex",
            nullable=False,
            comment="LLM provider being used (vertex/bedrock/cborg)",
        ),
        sa.Column(
            "llm_config",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="LLM configuration (model, temperature, etc.)",
        ),
        sa.Column(
            "error_message",
            sa.Text(),
            nullable=True,
            comment="Error message if job failed",
        ),
        sa.Column(
            "cancellation_reason",
            sa.Text(),
            nullable=True,
            comment="Reason why the job was cancelled",
        ),
        sa.Column("result_summary", sa.Text(), nullable=True, comment="Final analysis summary"),
        sa.Column(
            "consensus_answer",
            sa.Text(),
            nullable=True,
            comment="Consensus answer to the research question if one was reached",
        ),
        sa.Column(
            "data_summary",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Structured data summary used for prompting and UI",
        ),
        sa.Column(
            "agent_status",
            sa.Text(),
            nullable=True,
            comment="Current agent status message for live UI updates",
        ),
        sa.Column(
            "agent_status_updated_at",
            sa.DateTime(timezone=True),
            nullable=True,
            comment="Timestamp when agent_status was last updated",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["owner_id"], ["users.id"], ondelete="SET NULL"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_jobs_owner_id"), "jobs", ["owner_id"], unique=False)
    op.create_index(op.f("ix_jobs_status"), "jobs", ["status"], unique=False)

    # =========================================================================
    # JOB-RELATED TABLES
    # =========================================================================

    # Job shares
    op.create_table(
        "job_shares",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job being shared"),
        sa.Column(
            "shared_with_user_id",
            sa.UUID(),
            nullable=False,
            comment="User being granted access",
        ),
        sa.Column(
            "permission_level",
            sa.String(length=10),
            nullable=False,
            comment="Permission level (view/edit)",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.CheckConstraint("permission_level IN ('view', 'edit')", name="ck_job_share_permission"),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["shared_with_user_id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("job_id", "shared_with_user_id", name="uq_job_share_user"),
    )
    op.create_index(op.f("ix_job_shares_job_id"), "job_shares", ["job_id"], unique=False)
    op.create_index(
        op.f("ix_job_shares_shared_with_user_id"),
        "job_shares",
        ["shared_with_user_id"],
        unique=False,
    )

    # Job data files
    op.create_table(
        "job_data_files",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this file belongs to"),
        sa.Column(
            "filename",
            sa.String(length=255),
            nullable=False,
            comment="Original filename as uploaded by user",
        ),
        sa.Column(
            "file_path",
            sa.String(length=500),
            nullable=False,
            comment="Relative path to file on filesystem",
        ),
        sa.Column(
            "file_type",
            sa.String(length=50),
            nullable=False,
            comment="Type of file (mtz/cif/pdb/other)",
        ),
        sa.Column("file_size", sa.BigInteger(), nullable=False, comment="File size in bytes"),
        sa.Column(
            "mime_type",
            sa.String(length=100),
            nullable=True,
            comment="MIME type of the file",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_job_data_files_job_id"), "job_data_files", ["job_id"], unique=False)

    # Job chat messages
    op.create_table(
        "job_chat_messages",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this message belongs to"),
        sa.Column(
            "role",
            sa.String(length=20),
            nullable=False,
            comment="Message role (user/assistant)",
        ),
        sa.Column("content", sa.Text(), nullable=False, comment="Message text content"),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_job_chat_messages_job_id"),
        "job_chat_messages",
        ["job_id"],
        unique=False,
    )

    # Analysis log
    op.create_table(
        "analysis_log",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this log entry belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number when entry was created",
        ),
        sa.Column(
            "step_number",
            sa.Integer(),
            nullable=False,
            comment="Sequential step number within iteration",
        ),
        sa.Column(
            "action_type",
            sa.String(length=50),
            nullable=False,
            comment="Type of action (reasoning/code_execution/literature_search/etc)",
        ),
        sa.Column(
            "description",
            sa.Text(),
            nullable=False,
            comment="Human-readable description of the action",
        ),
        sa.Column(
            "input_data",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Input data for this step",
        ),
        sa.Column(
            "output_data",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Output/result data from this step",
        ),
        sa.Column(
            "duration_seconds",
            sa.Float(),
            nullable=True,
            comment="Time taken for this step",
        ),
        sa.Column(
            "success",
            sa.Boolean(),
            server_default="true",
            nullable=False,
            comment="Whether the step completed successfully",
        ),
        sa.Column(
            "error_message",
            sa.Text(),
            nullable=True,
            comment="Error message if step failed",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_analysis_log_job_id"), "analysis_log", ["job_id"], unique=False)

    # Cost records
    op.create_table(
        "cost_records",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this cost belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=True,
            comment="Iteration number when cost was incurred (NULL for chat)",
        ),
        sa.Column(
            "operation_type",
            sa.String(length=50),
            nullable=False,
            comment="Type of operation (analysis/code_gen/chat/etc)",
        ),
        sa.Column(
            "provider",
            sa.String(length=50),
            nullable=False,
            comment="LLM provider (vertex/bedrock/cborg)",
        ),
        sa.Column("model", sa.String(length=100), nullable=False, comment="Model name/ID used"),
        sa.Column(
            "input_tokens",
            sa.Integer(),
            nullable=False,
            comment="Number of input tokens",
        ),
        sa.Column(
            "output_tokens",
            sa.Integer(),
            nullable=False,
            comment="Number of output tokens",
        ),
        sa.Column("cost_usd", sa.Float(), nullable=False, comment="Cost in USD"),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_cost_records_job_id"), "cost_records", ["job_id"], unique=False)

    # Feedback history
    op.create_table(
        "feedback_history",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this feedback belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number when feedback was provided",
        ),
        sa.Column(
            "feedback_type",
            sa.String(length=50),
            nullable=False,
            comment="Type of feedback (correction/suggestion/question/approval)",
        ),
        sa.Column("feedback_text", sa.Text(), nullable=False, comment="User's feedback text"),
        sa.Column(
            "response_text",
            sa.Text(),
            nullable=True,
            comment="LLM's response to the feedback",
        ),
        sa.Column(
            "applied",
            sa.Boolean(),
            server_default="false",
            nullable=False,
            comment="Whether feedback has been incorporated",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_feedback_history_job_id"), "feedback_history", ["job_id"], unique=False
    )

    # Hypotheses
    op.create_table(
        "hypotheses",
        sa.Column(
            "job_id",
            sa.UUID(),
            nullable=False,
            comment="Job this hypothesis belongs to",
        ),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number when hypothesis was generated",
        ),
        sa.Column("text", sa.Text(), nullable=False, comment="Hypothesis statement"),
        sa.Column(
            "status",
            sa.String(length=50),
            server_default="active",
            nullable=False,
            comment="Current status (active/tested/disproved/merged/spawned)",
        ),
        sa.Column(
            "confidence",
            sa.Float(),
            nullable=True,
            comment="Confidence score (0.0-1.0)",
        ),
        sa.Column(
            "priority",
            sa.Integer(),
            nullable=True,
            comment="Priority for testing (higher = more important)",
        ),
        sa.Column(
            "rationale",
            sa.Text(),
            nullable=True,
            comment="Reasoning behind the hypothesis",
        ),
        sa.Column(
            "test_strategy",
            sa.Text(),
            nullable=True,
            comment="Proposed method for testing",
        ),
        sa.Column(
            "supporting_evidence",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Evidence supporting this hypothesis",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_hypotheses_job_id"), "hypotheses", ["job_id"], unique=False)

    # Findings
    op.create_table(
        "findings",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this finding belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number when finding was generated",
        ),
        sa.Column("text", sa.Text(), nullable=False, comment="Finding description"),
        sa.Column(
            "finding_type",
            sa.String(length=50),
            nullable=False,
            comment="Type of finding (observation/measurement/analysis/error)",
        ),
        sa.Column(
            "source",
            sa.String(length=50),
            nullable=False,
            comment="Source of finding (code_execution/literature/reasoning)",
        ),
        sa.Column(
            "data",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Structured data associated with finding",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_findings_job_id"), "findings", ["job_id"], unique=False)

    # Iteration summaries
    op.create_table(
        "iteration_summaries",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this summary belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number being summarized",
        ),
        sa.Column(
            "summary_text",
            sa.Text(),
            nullable=False,
            comment="Natural language summary of iteration",
        ),
        sa.Column(
            "key_findings",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="List of important findings from this iteration",
        ),
        sa.Column(
            "hypotheses_generated",
            sa.Integer(),
            nullable=True,
            comment="Number of new hypotheses generated",
        ),
        sa.Column(
            "hypotheses_tested",
            sa.Integer(),
            nullable=True,
            comment="Number of hypotheses tested",
        ),
        sa.Column(
            "code_executed",
            sa.Integer(),
            nullable=True,
            comment="Number of code blocks executed",
        ),
        sa.Column(
            "new_insights",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="List of new insights gained",
        ),
        sa.Column(
            "next_steps",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Planned next steps",
        ),
        sa.Column(
            "metrics",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Structured metrics (e.g., R-factors, completeness)",
        ),
        sa.Column(
            "strapline",
            sa.String(length=200),
            nullable=True,
            comment="Optional one-line headline for this iteration",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "job_id",
            "iteration",
            name="uq_iteration_summaries_job_iteration",
        ),
    )
    op.create_index(
        op.f("ix_iteration_summaries_job_id"),
        "iteration_summaries",
        ["job_id"],
        unique=False,
    )

    # Literature
    op.create_table(
        "literature",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this reference belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number when reference was retrieved",
        ),
        sa.Column(
            "title",
            sa.Text(),
            nullable=False,
            comment="Title of the paper/document",
        ),
        sa.Column("authors", sa.Text(), nullable=True, comment="Author list"),
        sa.Column(
            "journal",
            sa.Text(),
            nullable=True,
            comment="Journal or publication venue",
        ),
        sa.Column("year", sa.Integer(), nullable=True, comment="Publication year"),
        sa.Column(
            "doi",
            sa.String(length=255),
            nullable=True,
            comment="Digital Object Identifier",
        ),
        sa.Column("url", sa.Text(), nullable=True, comment="URL to the resource"),
        sa.Column("abstract", sa.Text(), nullable=True, comment="Abstract or summary"),
        sa.Column(
            "relevance_score",
            sa.Float(),
            nullable=True,
            comment="Relevance to current analysis (0.0-1.0)",
        ),
        sa.Column(
            "citation_key",
            sa.String(length=100),
            nullable=True,
            comment="BibTeX citation key",
        ),
        sa.Column(
            "extra_metadata",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=True,
            comment="Additional structured metadata",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_literature_doi"), "literature", ["doi"], unique=False)
    op.create_index(op.f("ix_literature_job_id"), "literature", ["job_id"], unique=False)

    # Plots
    op.create_table(
        "plots",
        sa.Column("job_id", sa.UUID(), nullable=False, comment="Job this plot belongs to"),
        sa.Column(
            "iteration",
            sa.Integer(),
            nullable=False,
            comment="Iteration number when plot was generated",
        ),
        sa.Column("title", sa.Text(), nullable=False, comment="Plot title"),
        sa.Column(
            "description",
            sa.Text(),
            nullable=True,
            comment="Description of what the plot shows",
        ),
        sa.Column(
            "file_path",
            sa.String(length=500),
            nullable=False,
            comment="Relative path to plot image file",
        ),
        sa.Column(
            "plot_type",
            sa.String(length=50),
            nullable=True,
            comment="Type of plot (histogram/scatter/heatmap/etc)",
        ),
        sa.Column(
            "code_used",
            sa.Text(),
            nullable=True,
            comment="Python code that generated the plot",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["job_id"], ["jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_plots_job_id"), "plots", ["job_id"], unique=False)

    # =========================================================================
    # JUNCTION TABLES
    # =========================================================================

    # Finding-hypothesis junction
    op.create_table(
        "finding_hypotheses",
        sa.Column(
            "finding_id",
            sa.UUID(),
            nullable=False,
            comment="Finding in this relationship",
        ),
        sa.Column(
            "hypothesis_id",
            sa.UUID(),
            nullable=False,
            comment="Hypothesis in this relationship",
        ),
        sa.ForeignKeyConstraint(["finding_id"], ["findings.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["hypothesis_id"], ["hypotheses.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("finding_id", "hypothesis_id"),
        comment="Many-to-many relationship between findings and hypotheses",
    )

    # Finding-literature junction
    op.create_table(
        "finding_literature",
        sa.Column(
            "finding_id",
            sa.UUID(),
            nullable=False,
            comment="Finding in this relationship",
        ),
        sa.Column(
            "literature_id",
            sa.UUID(),
            nullable=False,
            comment="Literature reference in this relationship",
        ),
        sa.ForeignKeyConstraint(["finding_id"], ["findings.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["literature_id"], ["literature.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("finding_id", "literature_id"),
        comment="Many-to-many relationship between findings and literature",
    )

    # Hypothesis spawns
    op.create_table(
        "hypothesis_spawns",
        sa.Column(
            "parent_id",
            sa.UUID(),
            nullable=False,
            comment="Parent hypothesis that spawned the child",
        ),
        sa.Column(
            "child_id",
            sa.UUID(),
            nullable=False,
            comment="Child hypothesis that was spawned",
        ),
        sa.Column(
            "spawn_reason",
            sa.Text(),
            nullable=True,
            comment="Explanation for why child was spawned",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(["child_id"], ["hypotheses.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["parent_id"], ["hypotheses.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_hypothesis_spawns_child_id"),
        "hypothesis_spawns",
        ["child_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_hypothesis_spawns_parent_id"),
        "hypothesis_spawns",
        ["parent_id"],
        unique=False,
    )

    # =========================================================================
    # SKILLS TABLES
    # =========================================================================

    # Skill sources
    op.create_table(
        "skill_sources",
        sa.Column(
            "source_type",
            sa.String(length=50),
            nullable=False,
            comment="Type of source (github/local)",
        ),
        sa.Column(
            "name",
            sa.String(length=255),
            nullable=False,
            comment="Human-readable name for the source",
        ),
        sa.Column(
            "url",
            sa.Text(),
            nullable=True,
            comment="URL for remote sources (e.g., GitHub repo URL)",
        ),
        sa.Column(
            "path",
            sa.Text(),
            nullable=True,
            comment="Local filesystem path for local sources",
        ),
        sa.Column(
            "branch",
            sa.String(length=255),
            server_default="main",
            nullable=False,
            comment="Git branch to sync from (for GitHub sources)",
        ),
        sa.Column(
            "skills_path",
            sa.String(length=255),
            server_default="",
            nullable=False,
            comment="Subdirectory within repo containing skills",
        ),
        sa.Column(
            "last_synced_at",
            sa.DateTime(timezone=True),
            nullable=True,
            comment="Timestamp of last successful sync",
        ),
        sa.Column(
            "last_commit_sha",
            sa.String(length=64),
            nullable=True,
            comment="Last synced Git commit SHA",
        ),
        sa.Column(
            "is_enabled",
            sa.Boolean(),
            server_default="true",
            nullable=False,
            comment="Whether this source is enabled for syncing",
        ),
        sa.Column(
            "sync_error",
            sa.Text(),
            nullable=True,
            comment="Error message from last failed sync attempt",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_skill_sources_source_type"),
        "skill_sources",
        ["source_type"],
        unique=False,
    )

    # Skills
    op.create_table(
        "skills",
        sa.Column(
            "name",
            sa.String(length=255),
            nullable=False,
            comment="Human-readable skill name",
        ),
        sa.Column(
            "slug",
            sa.String(length=255),
            nullable=False,
            comment="URL-friendly identifier (unique within category)",
        ),
        sa.Column(
            "category",
            sa.String(length=100),
            nullable=False,
            comment="Skill category (e.g., metabolomics, genomics, data-science)",
        ),
        sa.Column(
            "description",
            sa.Text(),
            nullable=True,
            comment="Brief description of what the skill does",
        ),
        sa.Column(
            "content",
            sa.Text(),
            nullable=False,
            comment="Full markdown content of the skill",
        ),
        sa.Column(
            "tags",
            postgresql.JSONB(astext_type=sa.Text()),
            server_default="[]",
            nullable=False,
            comment="Array of tags for filtering and search",
        ),
        sa.Column(
            "search_vector",
            postgresql.TSVECTOR(),
            nullable=True,
            comment="Full-text search vector (auto-generated)",
        ),
        sa.Column(
            "source_id",
            sa.UUID(),
            nullable=True,
            comment="Skill source this skill was synced from (NULL for built-in)",
        ),
        sa.Column(
            "source_path",
            sa.Text(),
            nullable=True,
            comment="Path within source where skill was found",
        ),
        sa.Column(
            "content_hash",
            sa.String(length=64),
            nullable=False,
            comment="SHA256 hash of content for change detection",
        ),
        sa.Column(
            "commit_sha",
            sa.String(length=64),
            nullable=True,
            comment="Git commit SHA when skill was synced",
        ),
        sa.Column(
            "is_enabled",
            sa.Boolean(),
            server_default="true",
            nullable=False,
            comment="Whether this skill is available for use",
        ),
        sa.Column(
            "version",
            sa.Integer(),
            server_default="1",
            nullable=False,
            comment="Version number for tracking updates",
        ),
        sa.Column(
            "id",
            sa.UUID(),
            server_default=sa.text("uuidv7()"),
            nullable=False,
            comment="UUIDv7 primary key generated by PostgreSQL",
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was created (UTC)",
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("CURRENT_TIMESTAMP"),
            nullable=False,
            comment="Timestamp when record was last updated (UTC)",
        ),
        sa.ForeignKeyConstraint(
            ["source_id"],
            ["skill_sources.id"],
            ondelete="SET NULL",
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("category", "slug", name="uq_skill_category_slug"),
    )
    op.create_index(op.f("ix_skills_category"), "skills", ["category"], unique=False)
    op.create_index(op.f("ix_skills_slug"), "skills", ["slug"], unique=False)
    op.create_index(op.f("ix_skills_source_id"), "skills", ["source_id"], unique=False)
    op.create_index(op.f("ix_skills_is_enabled"), "skills", ["is_enabled"], unique=False)

    # GIN index for full-text search on search_vector
    op.create_index(
        "ix_skills_search_vector",
        "skills",
        ["search_vector"],
        unique=False,
        postgresql_using="gin",
    )

    # GIN index for JSONB tags array
    op.create_index(
        "ix_skills_tags",
        "skills",
        ["tags"],
        unique=False,
        postgresql_using="gin",
    )

    # Create trigger function to auto-update search_vector
    op.execute(
        """
        CREATE OR REPLACE FUNCTION skills_search_vector_update() RETURNS TRIGGER AS $$
        BEGIN
            NEW.search_vector :=
                setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
                setweight(to_tsvector('english', COALESCE(NEW.category, '')), 'B') ||
                setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
                setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'C');
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql
    """
    )

    # Create trigger to call the function
    op.execute(
        """
        CREATE TRIGGER skills_search_vector_trigger
            BEFORE INSERT OR UPDATE ON skills
            FOR EACH ROW
            EXECUTE FUNCTION skills_search_vector_update()
    """
    )

    # =========================================================================
    # HELPER FUNCTIONS FOR RLS
    # =========================================================================

    # Check job ownership (breaks circular RLS dependency between jobs/job_shares)
    op.execute(
        """
        CREATE OR REPLACE FUNCTION check_job_owner(p_job_id UUID, p_user_id TEXT)
        RETURNS BOOLEAN AS $$
            SELECT EXISTS (
                SELECT 1 FROM jobs
                WHERE id = p_job_id AND owner_id::text = p_user_id
            )
        $$ LANGUAGE SQL SECURITY DEFINER STABLE
    """
    )

    # Check admin status (SECURITY DEFINER to avoid RLS recursion)
    op.execute(
        """
        CREATE OR REPLACE FUNCTION is_admin(check_user_id UUID)
        RETURNS BOOLEAN AS $$
        BEGIN
            RETURN EXISTS (
                SELECT 1 FROM administrators WHERE user_id = check_user_id
            );
        END;
        $$ LANGUAGE plpgsql SECURITY DEFINER
    """
    )

    # Check if current session user is admin
    op.execute(
        """
        CREATE OR REPLACE FUNCTION current_user_is_admin()
        RETURNS BOOLEAN AS $$
        DECLARE
            uid UUID;
        BEGIN
            BEGIN
                uid := current_setting('app.current_user_id', TRUE)::UUID;
            EXCEPTION WHEN OTHERS THEN
                RETURN FALSE;
            END;
            IF uid IS NULL THEN
                RETURN FALSE;
            END IF;
            RETURN is_admin(uid);
        END;
        $$ LANGUAGE plpgsql SECURITY DEFINER
    """
    )

    # =========================================================================
    # CREATE OPENSCIENTIST_APP ROLE (subject to RLS)
    # =========================================================================
    # get_session() does SET ROLE openscientist_app to drop superuser privileges.
    # Without this role, RLS policies are silently bypassed.
    op.execute(
        """
        DO $$
        BEGIN
            IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'openscientist_app') THEN
                CREATE ROLE openscientist_app NOLOGIN;
            END IF;
        END
        $$
        """
    )
    op.execute("GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO openscientist_app")
    op.execute("GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO openscientist_app")
    op.execute("GRANT USAGE ON SCHEMA public TO openscientist_app")
    op.execute(
        """
        ALTER DEFAULT PRIVILEGES IN SCHEMA public
            GRANT ALL PRIVILEGES ON TABLES TO openscientist_app
        """
    )
    op.execute(
        """
        ALTER DEFAULT PRIVILEGES IN SCHEMA public
            GRANT ALL PRIVILEGES ON SEQUENCES TO openscientist_app
        """
    )

    # =========================================================================
    # CREATE OPENSCIENTIST_ADMIN ROLE WITH BYPASSRLS
    # =========================================================================
    op.execute(
        """
        DO $$
        BEGIN
            IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'openscientist_admin') THEN
                CREATE ROLE openscientist_admin WITH LOGIN PASSWORD 'openscientist_dev_password' BYPASSRLS;
            END IF;
        END
        $$
        """
    )
    op.execute("GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO openscientist_admin")
    op.execute("GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO openscientist_admin")
    op.execute("GRANT USAGE ON SCHEMA public TO openscientist_admin")
    op.execute(
        """
        ALTER DEFAULT PRIVILEGES IN SCHEMA public
            GRANT ALL PRIVILEGES ON TABLES TO openscientist_admin
        """
    )
    op.execute(
        """
        ALTER DEFAULT PRIVILEGES IN SCHEMA public
            GRANT ALL PRIVILEGES ON SEQUENCES TO openscientist_admin
        """
    )

    # =========================================================================
    # ROW-LEVEL SECURITY POLICIES
    # =========================================================================

    # --- Users ---
    op.execute("ALTER TABLE users ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE users FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY users_select_own ON users FOR SELECT
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND id::text = current_setting('app.current_user_id', TRUE)
            )
    """
    )

    # --- API Keys ---
    op.execute("ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE api_keys FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY api_keys_all_own ON api_keys FOR ALL
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND user_id::text = current_setting('app.current_user_id', TRUE)
            )
    """
    )
    # Admins can view all API keys (read-only for security)
    op.execute(
        """
        CREATE POLICY api_keys_select_admin ON api_keys FOR SELECT
            USING (current_user_is_admin())
    """
    )

    # --- Sessions ---
    op.execute("ALTER TABLE sessions ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE sessions FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY sessions_all_own ON sessions FOR ALL
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND user_id::text = current_setting('app.current_user_id', TRUE)
            )
    """
    )

    # --- OAuth Accounts ---
    op.execute("ALTER TABLE oauth_accounts ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE oauth_accounts FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY oauth_accounts_all_own ON oauth_accounts FOR ALL
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND user_id::text = current_setting('app.current_user_id', TRUE)
            )
    """
    )

    # --- Administrators ---
    op.execute("ALTER TABLE administrators ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE administrators FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY administrators_select_admin ON administrators FOR SELECT
            USING (current_user_is_admin())
    """
    )

    # --- Jobs ---
    op.execute("ALTER TABLE jobs ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE jobs FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY jobs_all_owner ON jobs FOR ALL
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND owner_id::text = current_setting('app.current_user_id', TRUE)
            )
    """
    )
    op.execute(
        """
        CREATE POLICY jobs_select_shared ON jobs FOR SELECT
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM job_shares
                    WHERE job_shares.job_id = jobs.id
                    AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                )
            )
    """
    )
    op.execute(
        """
        CREATE POLICY jobs_update_shared_edit ON jobs FOR UPDATE
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM job_shares
                    WHERE job_shares.job_id = jobs.id
                    AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                    AND job_shares.permission_level = 'edit'
                )
            )
    """
    )
    op.execute(
        """
        CREATE POLICY jobs_select_orphaned ON jobs FOR SELECT
            USING (owner_id IS NULL)
    """
    )

    # --- Job Shares ---
    op.execute("ALTER TABLE job_shares ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE job_shares FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY job_shares_all_owner ON job_shares FOR ALL
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND check_job_owner(job_id, current_setting('app.current_user_id', TRUE))
            )
    """
    )
    op.execute(
        """
        CREATE POLICY job_shares_select_recipient ON job_shares FOR SELECT
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
            )
    """
    )

    # --- Job-related tables (use helper function) ---
    _create_job_related_policies("job_data_files")
    _create_job_related_policies("job_chat_messages")
    _create_job_related_policies("hypotheses")
    _create_job_related_policies("findings")
    _create_job_related_policies("literature")
    _create_job_related_policies("analysis_log")
    _create_job_related_policies("iteration_summaries")
    _create_job_related_policies("feedback_history")
    _create_job_related_policies("plots")
    _create_job_related_policies("cost_records")

    # --- Junction tables ---
    op.execute("ALTER TABLE finding_hypotheses ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE finding_hypotheses FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY finding_hypotheses_all ON finding_hypotheses FOR ALL
            USING (
                EXISTS (
                    SELECT 1 FROM findings
                    JOIN jobs ON jobs.id = findings.job_id
                    WHERE findings.id = finding_hypotheses.finding_id
                    AND (
                        jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                        OR EXISTS (
                            SELECT 1 FROM job_shares
                            WHERE job_shares.job_id = jobs.id
                            AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                        )
                    )
                )
                OR
                EXISTS (
                    SELECT 1 FROM hypotheses
                    JOIN jobs ON jobs.id = hypotheses.job_id
                    WHERE hypotheses.id = finding_hypotheses.hypothesis_id
                    AND (
                        jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                        OR EXISTS (
                            SELECT 1 FROM job_shares
                            WHERE job_shares.job_id = jobs.id
                            AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                        )
                    )
                )
            )
    """
    )

    op.execute("ALTER TABLE finding_literature ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE finding_literature FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY finding_literature_all ON finding_literature FOR ALL
            USING (
                EXISTS (
                    SELECT 1 FROM findings
                    JOIN jobs ON jobs.id = findings.job_id
                    WHERE findings.id = finding_literature.finding_id
                    AND (
                        jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                        OR EXISTS (
                            SELECT 1 FROM job_shares
                            WHERE job_shares.job_id = jobs.id
                            AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                        )
                    )
                )
                OR
                EXISTS (
                    SELECT 1 FROM literature
                    JOIN jobs ON jobs.id = literature.job_id
                    WHERE literature.id = finding_literature.literature_id
                    AND (
                        jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                        OR EXISTS (
                            SELECT 1 FROM job_shares
                            WHERE job_shares.job_id = jobs.id
                            AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                        )
                    )
                )
            )
    """
    )

    op.execute("ALTER TABLE hypothesis_spawns ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE hypothesis_spawns FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY hypothesis_spawns_all ON hypothesis_spawns FOR ALL
            USING (
                EXISTS (
                    SELECT 1 FROM hypotheses
                    JOIN jobs ON jobs.id = hypotheses.job_id
                    WHERE hypotheses.id = hypothesis_spawns.parent_id
                    AND (
                        jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                        OR EXISTS (
                            SELECT 1 FROM job_shares
                            WHERE job_shares.job_id = jobs.id
                            AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                        )
                    )
                )
                OR
                EXISTS (
                    SELECT 1 FROM hypotheses
                    JOIN jobs ON jobs.id = hypotheses.job_id
                    WHERE hypotheses.id = hypothesis_spawns.child_id
                    AND (
                        jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                        OR EXISTS (
                            SELECT 1 FROM job_shares
                            WHERE job_shares.job_id = jobs.id
                            AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                        )
                    )
                )
            )
    """
    )

    # --- Skills tables ---
    op.execute("ALTER TABLE skill_sources ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE skill_sources FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY skill_sources_admin ON skill_sources FOR ALL
            USING (current_user_is_admin())
    """
    )

    op.execute("ALTER TABLE skills ENABLE ROW LEVEL SECURITY")
    op.execute("ALTER TABLE skills FORCE ROW LEVEL SECURITY")
    op.execute(
        """
        CREATE POLICY skills_select_all ON skills FOR SELECT
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND is_enabled = true
            )
    """
    )


def _create_job_related_policies(table_name: str) -> None:
    """Create RLS policies for tables with job_id foreign key."""
    op.execute(f"ALTER TABLE {table_name} ENABLE ROW LEVEL SECURITY")
    op.execute(f"ALTER TABLE {table_name} FORCE ROW LEVEL SECURITY")

    # Job owner has full access
    op.execute(
        f"""
        CREATE POLICY {table_name}_all_owner ON {table_name} FOR ALL
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM jobs
                    WHERE jobs.id = {table_name}.job_id
                    AND jobs.owner_id::text = current_setting('app.current_user_id', TRUE)
                )
            )
    """
    )

    # Users with any job share can read
    op.execute(
        f"""
        CREATE POLICY {table_name}_select_shared ON {table_name} FOR SELECT
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM jobs
                    JOIN job_shares ON job_shares.job_id = jobs.id
                    WHERE jobs.id = {table_name}.job_id
                    AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                )
            )
    """
    )

    # Users with 'edit' share can insert/update/delete
    op.execute(
        f"""
        CREATE POLICY {table_name}_modify_shared_edit ON {table_name} FOR INSERT
            WITH CHECK (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM jobs
                    JOIN job_shares ON job_shares.job_id = jobs.id
                    WHERE jobs.id = {table_name}.job_id
                    AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                    AND job_shares.permission_level = 'edit'
                )
            )
    """
    )

    op.execute(
        f"""
        CREATE POLICY {table_name}_update_shared_edit ON {table_name} FOR UPDATE
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM jobs
                    JOIN job_shares ON job_shares.job_id = jobs.id
                    WHERE jobs.id = {table_name}.job_id
                    AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                    AND job_shares.permission_level = 'edit'
                )
            )
    """
    )

    op.execute(
        f"""
        CREATE POLICY {table_name}_delete_shared_edit ON {table_name} FOR DELETE
            USING (
                current_setting('app.current_user_id', TRUE) IS NOT NULL
                AND EXISTS (
                    SELECT 1 FROM jobs
                    JOIN job_shares ON job_shares.job_id = jobs.id
                    WHERE jobs.id = {table_name}.job_id
                    AND job_shares.shared_with_user_id::text = current_setting('app.current_user_id', TRUE)
                    AND job_shares.permission_level = 'edit'
                )
            )
    """
    )


def downgrade() -> None:
    """Drop all tables and RLS policies."""
    # Drop all tables in reverse order of creation (respecting FKs)
    tables_to_drop = [
        # Junction tables first
        "hypothesis_spawns",
        "finding_literature",
        "finding_hypotheses",
        # Job-related tables
        "plots",
        "literature",
        "iteration_summaries",
        "hypotheses",
        "findings",
        "feedback_history",
        "cost_records",
        "analysis_log",
        "job_chat_messages",
        "job_data_files",
        "job_shares",
        # Skills
        "skills",
        "skill_sources",
        # Core tables
        "jobs",
        "administrators",
        "sessions",
        "oauth_accounts",
        "api_keys",
        "users",
    ]

    # Drop RLS and tables
    for table in tables_to_drop:
        op.execute(f"ALTER TABLE IF EXISTS {table} NO FORCE ROW LEVEL SECURITY")
        op.execute(f"ALTER TABLE IF EXISTS {table} DISABLE ROW LEVEL SECURITY")

    # Drop trigger and function for skills
    op.execute("DROP TRIGGER IF EXISTS skills_search_vector_trigger ON skills")
    op.execute("DROP FUNCTION IF EXISTS skills_search_vector_update()")

    # Drop helper functions
    op.execute("DROP FUNCTION IF EXISTS current_user_is_admin()")
    op.execute("DROP FUNCTION IF EXISTS is_admin(UUID)")
    op.execute("DROP FUNCTION IF EXISTS check_job_owner(UUID, TEXT)")

    # Drop indexes with GIN
    op.execute("DROP INDEX IF EXISTS ix_skills_search_vector")
    op.execute("DROP INDEX IF EXISTS ix_skills_tags")
    op.execute("DROP INDEX IF EXISTS ix_users_email_trgm")
    op.execute("DROP INDEX IF EXISTS ix_users_name_trgm")

    # Drop tables
    for table in tables_to_drop:
        op.execute(f"DROP TABLE IF EXISTS {table} CASCADE")
