"""add_identity_and_integration_tables

Revision ID: l6m7n8o9p0q1
Revises: h2c3d4e5f6g7
Create Date: 2025-12-29 12:00:00.000000

"""

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 = "l6m7n8o9p0q1"
down_revision: str | Sequence[str] | None = "h2c3d4e5f6g7"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

DEFAULT_WORKSPACE_ID = "00000000-0000-0000-0000-000000000001"
DEFAULT_USER_ID = "00000000-0000-0000-0000-000000000001"


def upgrade() -> None:
    """Add identity, organization, and integration tables + schema fixes."""
    op.execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")

    op.execute(
        """
        CREATE OR REPLACE FUNCTION noteflow.set_updated_at()
        RETURNS trigger AS $$
        BEGIN
            NEW.updated_at = now();
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
        """
    )

    op.create_table(
        "workspaces",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("slug", sa.Text(), nullable=True, unique=True),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "metadata",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        schema="noteflow",
    )

    op.create_table(
        "users",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("email", sa.Text(), nullable=True, unique=True),
        sa.Column("display_name", sa.Text(), nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "metadata",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        schema="noteflow",
    )

    op.create_table(
        "workspace_memberships",
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.workspaces.id",
                ondelete="CASCADE",
            ),
            primary_key=True,
        ),
        sa.Column(
            "user_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.users.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("role", sa.String(50), nullable=False, server_default=sa.text("'owner'")),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        schema="noteflow",
    )

    op.execute(
        """
        INSERT INTO noteflow.workspaces (id, slug, name, metadata)
        VALUES (
            '00000000-0000-0000-0000-000000000001',
            'default',
            'Default Workspace',
            '{"seed": true}'::jsonb
        )
        ON CONFLICT (id) DO NOTHING;
        """
    )

    op.execute(
        """
        INSERT INTO noteflow.users (id, email, display_name, metadata)
        VALUES (
            '00000000-0000-0000-0000-000000000001',
            'local@noteflow.local',
            'Local User',
            '{"seed": true}'::jsonb
        )
        ON CONFLICT (id) DO NOTHING;
        """
    )

    op.execute(
        """
        INSERT INTO noteflow.workspace_memberships (workspace_id, user_id, role)
        VALUES (
            '00000000-0000-0000-0000-000000000001',
            '00000000-0000-0000-0000-000000000001',
            'owner'
        )
        ON CONFLICT DO NOTHING;
        """
    )

    op.execute(
        """
        CREATE TRIGGER trg_workspaces_updated_at
        BEFORE UPDATE ON noteflow.workspaces
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.execute(
        """
        CREATE TRIGGER trg_users_updated_at
        BEFORE UPDATE ON noteflow.users
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    # Check and add columns idempotently
    conn = op.get_bind()

    # Add workspace_id if it doesn't exist
    result = conn.execute(
        sa.text(
            "SELECT EXISTS ("
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = 'noteflow' "
            "AND table_name = 'meetings' "
            "AND column_name = 'workspace_id'"
            ")"
        )
    )
    if not result.scalar():
        op.add_column(
            "meetings",
            sa.Column(
                "workspace_id",
                postgresql.UUID(as_uuid=True),
                nullable=True,
                server_default=sa.text(f"'{DEFAULT_WORKSPACE_ID}'"),
            ),
            schema="noteflow",
        )

    # Add created_by_id if it doesn't exist
    result = conn.execute(
        sa.text(
            "SELECT EXISTS ("
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = 'noteflow' "
            "AND table_name = 'meetings' "
            "AND column_name = 'created_by_id'"
            ")"
        )
    )
    if not result.scalar():
        op.add_column(
            "meetings",
            sa.Column(
                "created_by_id",
                postgresql.UUID(as_uuid=True),
                nullable=True,
                server_default=sa.text(f"'{DEFAULT_USER_ID}'"),
            ),
            schema="noteflow",
        )

    # Add deleted_at if it doesn't exist
    result = conn.execute(
        sa.text(
            "SELECT EXISTS ("
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = 'noteflow' "
            "AND table_name = 'meetings' "
            "AND column_name = 'deleted_at'"
            ")"
        )
    )
    if not result.scalar():
        op.add_column(
            "meetings",
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            schema="noteflow",
        )

    # Update existing rows and make workspace_id NOT NULL if column exists
    result = conn.execute(
        sa.text(
            "SELECT EXISTS ("
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = 'noteflow' "
            "AND table_name = 'meetings' "
            "AND column_name = 'workspace_id'"
            ")"
        )
    )
    if result.scalar():
        # Check if column is nullable
        result = conn.execute(
            sa.text(
                "SELECT is_nullable FROM information_schema.columns "
                "WHERE table_schema = 'noteflow' "
                "AND table_name = 'meetings' "
                "AND column_name = 'workspace_id'"
            )
        )
        is_nullable = result.scalar() == "YES"

        if is_nullable:
            op.execute(
                f"""
                UPDATE noteflow.meetings
                SET workspace_id = '{DEFAULT_WORKSPACE_ID}'
                WHERE workspace_id IS NULL;
                """
            )
            op.alter_column("meetings", "workspace_id", nullable=False, schema="noteflow")

    # Create foreign keys if they don't exist
    result = conn.execute(
        sa.text(
            "SELECT EXISTS ("
            "SELECT 1 FROM information_schema.table_constraints "
            "WHERE table_schema = 'noteflow' "
            "AND table_name = 'meetings' "
            "AND constraint_name = 'fk_meetings_workspace_id'"
            ")"
        )
    )
    if not result.scalar():
        op.create_foreign_key(
            "fk_meetings_workspace_id",
            "meetings",
            "workspaces",
            ["workspace_id"],
            ["id"],
            source_schema="noteflow",
            referent_schema="noteflow",
            ondelete="RESTRICT",
        )

    result = conn.execute(
        sa.text(
            "SELECT EXISTS ("
            "SELECT 1 FROM information_schema.table_constraints "
            "WHERE table_schema = 'noteflow' "
            "AND table_name = 'meetings' "
            "AND constraint_name = 'fk_meetings_created_by_id'"
            ")"
        )
    )
    if not result.scalar():
        op.create_foreign_key(
            "fk_meetings_created_by_id",
            "meetings",
            "users",
            ["created_by_id"],
            ["id"],
            source_schema="noteflow",
            referent_schema="noteflow",
            ondelete="SET NULL",
        )

    op.create_unique_constraint(
        "segments_unique_per_meeting",
        "segments",
        ["meeting_id", "segment_id"],
        schema="noteflow",
    )

    op.add_column(
        "word_timings",
        sa.Column("word_index", sa.Integer(), nullable=True),
        schema="noteflow",
    )
    op.execute(
        """
        WITH ranked AS (
            SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY segment_pk ORDER BY id) - 1 AS word_index
            FROM noteflow.word_timings
        )
        UPDATE noteflow.word_timings wt
        SET word_index = ranked.word_index
        FROM ranked
        WHERE wt.id = ranked.id;
        """
    )
    op.alter_column("word_timings", "word_index", nullable=False, schema="noteflow")
    op.create_unique_constraint(
        "word_timings_unique_per_segment",
        "word_timings",
        ["segment_pk", "word_index"],
        schema="noteflow",
    )

    op.add_column(
        "summaries",
        sa.Column(
            "provider_name",
            sa.Text(),
            nullable=False,
            server_default=sa.text("''"),
        ),
        schema="noteflow",
    )
    op.add_column(
        "summaries",
        sa.Column(
            "model_name",
            sa.Text(),
            nullable=False,
            server_default=sa.text("''"),
        ),
        schema="noteflow",
    )
    op.add_column(
        "summaries",
        sa.Column("tokens_used", sa.Integer(), nullable=True),
        schema="noteflow",
    )
    op.add_column(
        "summaries",
        sa.Column("latency_ms", sa.Float(), nullable=True),
        schema="noteflow",
    )
    op.add_column(
        "summaries",
        sa.Column(
            "verification",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        schema="noteflow",
    )
    op.execute(
        """
        UPDATE noteflow.summaries
        SET executive_summary = ''
        WHERE executive_summary IS NULL;
        """
    )
    op.alter_column(
        "summaries",
        "executive_summary",
        nullable=False,
        server_default=sa.text("''"),
        schema="noteflow",
    )

    op.add_column(
        "key_points",
        sa.Column("position", sa.Integer(), nullable=True),
        schema="noteflow",
    )
    op.execute(
        """
        WITH ranked AS (
            SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY summary_id ORDER BY id) - 1 AS position
            FROM noteflow.key_points
        )
        UPDATE noteflow.key_points kp
        SET position = ranked.position
        FROM ranked
        WHERE kp.id = ranked.id;
        """
    )
    op.alter_column("key_points", "position", nullable=False, schema="noteflow")
    op.create_unique_constraint(
        "key_points_unique_position",
        "key_points",
        ["summary_id", "position"],
        schema="noteflow",
    )

    op.add_column(
        "action_items",
        sa.Column("position", sa.Integer(), nullable=True),
        schema="noteflow",
    )
    op.execute(
        """
        WITH ranked AS (
            SELECT id,
                   ROW_NUMBER() OVER (PARTITION BY summary_id ORDER BY id) - 1 AS position
            FROM noteflow.action_items
        )
        UPDATE noteflow.action_items ai
        SET position = ranked.position
        FROM ranked
        WHERE ai.id = ranked.id;
        """
    )
    op.alter_column("action_items", "position", nullable=False, schema="noteflow")
    op.create_unique_constraint(
        "action_items_unique_position",
        "action_items",
        ["summary_id", "position"],
        schema="noteflow",
    )

    op.create_table(
        "settings",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column("scope", sa.Text(), nullable=False),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.workspaces.id",
                ondelete="CASCADE",
            ),
            nullable=True,
        ),
        sa.Column(
            "user_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.users.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column("key", sa.Text(), nullable=False),
        sa.Column(
            "value",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.UniqueConstraint(
            "scope",
            "workspace_id",
            "user_id",
            "key",
            name="settings_unique_scope_key",
        ),
        sa.CheckConstraint(
            "scope IN ('system', 'workspace', 'user')",
            name="settings_scope_chk",
        ),
        schema="noteflow",
    )

    op.execute(
        """
        CREATE TRIGGER trg_settings_updated_at
        BEFORE UPDATE ON noteflow.settings
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.create_table(
        "persons",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.workspaces.id",
                ondelete="CASCADE",
            ),
            nullable=False,
        ),
        sa.Column("display_name", sa.Text(), nullable=False),
        sa.Column("email", sa.Text(), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "metadata",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        sa.UniqueConstraint(
            "workspace_id",
            "email",
            name="persons_unique_email_per_workspace",
        ),
        schema="noteflow",
    )

    op.execute(
        """
        CREATE TRIGGER trg_persons_updated_at
        BEFORE UPDATE ON noteflow.persons
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.create_table(
        "meeting_speakers",
        sa.Column(
            "meeting_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.meetings.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("speaker_id", sa.String(50), primary_key=True),
        sa.Column("display_name", sa.Text(), nullable=True),
        sa.Column(
            "person_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.persons.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        schema="noteflow",
    )

    op.create_table(
        "tags",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.workspaces.id",
                ondelete="CASCADE",
            ),
            nullable=False,
        ),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("color", sa.Text(), nullable=False, server_default=sa.text("'#888888'")),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.UniqueConstraint("workspace_id", "name", name="tags_unique_name"),
        schema="noteflow",
    )

    op.create_table(
        "meeting_tags",
        sa.Column(
            "meeting_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.meetings.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "tag_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.tags.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        schema="noteflow",
    )

    op.create_table(
        "tasks",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.workspaces.id",
                ondelete="CASCADE",
            ),
            nullable=False,
        ),
        sa.Column(
            "meeting_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.meetings.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "action_item_id",
            sa.Integer(),
            sa.ForeignKey("noteflow.action_items.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("text", sa.Text(), nullable=False),
        sa.Column("status", sa.Text(), nullable=False, server_default=sa.text("'open'")),
        sa.Column(
            "assignee_person_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.persons.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("due_date", sa.DateTime(timezone=True), nullable=True),
        sa.Column("priority", sa.Integer(), nullable=False, server_default="0"),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "metadata",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        sa.CheckConstraint(
            "status IN ('open', 'done', 'dismissed')",
            name="tasks_status_chk",
        ),
        schema="noteflow",
    )

    op.create_index(
        "idx_tasks_workspace_id",
        "tasks",
        ["workspace_id"],
        schema="noteflow",
    )

    op.execute(
        """
        CREATE TRIGGER trg_tasks_updated_at
        BEFORE UPDATE ON noteflow.tasks
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.create_table(
        "integrations",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "workspace_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.workspaces.id",
                ondelete="CASCADE",
            ),
            nullable=False,
        ),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("type", sa.Text(), nullable=False),
        sa.Column(
            "status",
            sa.Text(),
            nullable=False,
            server_default=sa.text("'disconnected'"),
        ),
        sa.Column(
            "config",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        sa.Column("last_sync", sa.DateTime(timezone=True), nullable=True),
        sa.Column("error_message", sa.Text(), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.CheckConstraint(
            "type IN ('auth', 'email', 'calendar', 'pkm', 'custom')",
            name="integrations_type_chk",
        ),
        sa.CheckConstraint(
            "status IN ('disconnected', 'connected', 'error')",
            name="integrations_status_chk",
        ),
        schema="noteflow",
    )

    op.execute(
        """
        CREATE TRIGGER trg_integrations_updated_at
        BEFORE UPDATE ON noteflow.integrations
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.create_table(
        "integration_secrets",
        sa.Column(
            "integration_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.integrations.id",
                ondelete="CASCADE",
            ),
            primary_key=True,
        ),
        sa.Column("secret_key", sa.Text(), primary_key=True),
        sa.Column("secret_value", sa.LargeBinary(), nullable=False),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        schema="noteflow",
    )

    op.execute(
        """
        CREATE TRIGGER trg_integration_secrets_updated_at
        BEFORE UPDATE ON noteflow.integration_secrets
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.create_table(
        "calendar_events",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "integration_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.integrations.id",
                ondelete="CASCADE",
            ),
            nullable=False,
        ),
        sa.Column("external_id", sa.Text(), nullable=False),
        sa.Column("calendar_id", sa.Text(), nullable=False),
        sa.Column("calendar_name", sa.Text(), nullable=False),
        sa.Column("title", sa.Text(), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("start_time", sa.DateTime(timezone=True), nullable=False),
        sa.Column("end_time", sa.DateTime(timezone=True), nullable=False),
        sa.Column("location", sa.Text(), nullable=True),
        sa.Column("attendees", postgresql.ARRAY(sa.Text()), nullable=True),
        sa.Column("is_all_day", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column("meeting_link", sa.Text(), nullable=True),
        sa.Column(
            "raw",
            postgresql.JSONB(astext_type=sa.Text()),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.UniqueConstraint(
            "integration_id",
            "external_id",
            name="calendar_events_unique_external_id",
        ),
        schema="noteflow",
    )

    op.execute(
        """
        CREATE TRIGGER trg_calendar_events_updated_at
        BEFORE UPDATE ON noteflow.calendar_events
        FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();
        """
    )

    op.create_table(
        "meeting_calendar_links",
        sa.Column(
            "meeting_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.meetings.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column(
            "calendar_event_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("noteflow.calendar_events.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        schema="noteflow",
    )

    op.create_table(
        "external_refs",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "integration_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey(
                "noteflow.integrations.id",
                ondelete="CASCADE",
            ),
            nullable=False,
        ),
        sa.Column("entity_type", sa.Text(), nullable=False),
        sa.Column("entity_id", sa.Text(), nullable=False),
        sa.Column("external_id", sa.Text(), nullable=False),
        sa.Column("external_url", sa.Text(), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        sa.UniqueConstraint(
            "integration_id",
            "entity_type",
            "entity_id",
            name="external_refs_unique_entity",
        ),
        schema="noteflow",
    )


def downgrade() -> None:
    """Revert identity, organization, and integration additions."""
    op.drop_table("external_refs", schema="noteflow")
    op.drop_table("meeting_calendar_links", schema="noteflow")
    op.execute(
        "DROP TRIGGER IF EXISTS trg_calendar_events_updated_at ON noteflow.calendar_events;"
    )
    op.drop_table("calendar_events", schema="noteflow")
    op.execute(
        "DROP TRIGGER IF EXISTS trg_integration_secrets_updated_at ON noteflow.integration_secrets;"
    )
    op.drop_table("integration_secrets", schema="noteflow")
    op.execute(
        "DROP TRIGGER IF EXISTS trg_integrations_updated_at ON noteflow.integrations;"
    )
    op.drop_table("integrations", schema="noteflow")

    op.execute("DROP TRIGGER IF EXISTS trg_tasks_updated_at ON noteflow.tasks;")
    op.drop_index("idx_tasks_workspace_id", table_name="tasks", schema="noteflow")
    op.drop_table("tasks", schema="noteflow")
    op.drop_table("meeting_tags", schema="noteflow")
    op.drop_table("tags", schema="noteflow")
    op.drop_table("meeting_speakers", schema="noteflow")
    op.execute("DROP TRIGGER IF EXISTS trg_persons_updated_at ON noteflow.persons;")
    op.drop_table("persons", schema="noteflow")

    op.execute("DROP TRIGGER IF EXISTS trg_settings_updated_at ON noteflow.settings;")
    op.drop_table("settings", schema="noteflow")

    op.drop_constraint(
        "action_items_unique_position",
        "action_items",
        schema="noteflow",
        type_="unique",
    )
    op.drop_column("action_items", "position", schema="noteflow")

    op.drop_constraint(
        "key_points_unique_position",
        "key_points",
        schema="noteflow",
        type_="unique",
    )
    op.drop_column("key_points", "position", schema="noteflow")

    op.alter_column(
        "summaries",
        "executive_summary",
        nullable=True,
        server_default=None,
        schema="noteflow",
    )
    op.drop_column("summaries", "verification", schema="noteflow")
    op.drop_column("summaries", "latency_ms", schema="noteflow")
    op.drop_column("summaries", "tokens_used", schema="noteflow")
    op.drop_column("summaries", "model_name", schema="noteflow")
    op.drop_column("summaries", "provider_name", schema="noteflow")

    op.drop_constraint(
        "word_timings_unique_per_segment",
        "word_timings",
        schema="noteflow",
        type_="unique",
    )
    op.drop_column("word_timings", "word_index", schema="noteflow")

    op.drop_constraint(
        "segments_unique_per_meeting",
        "segments",
        schema="noteflow",
        type_="unique",
    )

    op.drop_constraint(
        "fk_meetings_created_by_id",
        "meetings",
        schema="noteflow",
        type_="foreignkey",
    )
    op.drop_constraint(
        "fk_meetings_workspace_id",
        "meetings",
        schema="noteflow",
        type_="foreignkey",
    )
    op.drop_column("meetings", "deleted_at", schema="noteflow")
    op.drop_column("meetings", "created_by_id", schema="noteflow")
    op.drop_column("meetings", "workspace_id", schema="noteflow")

    op.execute("DROP TRIGGER IF EXISTS trg_users_updated_at ON noteflow.users;")
    op.execute("DROP TRIGGER IF EXISTS trg_workspaces_updated_at ON noteflow.workspaces;")
    op.drop_table("workspace_memberships", schema="noteflow")
    op.drop_table("users", schema="noteflow")
    op.drop_table("workspaces", schema="noteflow")
