"""Drop legacy auth tables; migrate profile fields to musehub_identities.

- Drop muse_users, muse_access_tokens, musehub_profiles
- Add location, twitter_handle, is_verified, cc_license, pinned_repo_ids to musehub_identities
- Drop legacy_user_id from musehub_identities
- Drop FK constraints from musehub_collaborators and musehub_stash to muse_users

Revision ID: 0016
Revises: 0015
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "0016"
down_revision = "0015"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # 1. Drop FK constraints that reference muse_users
    op.drop_constraint("musehub_collaborators_user_id_fkey", "musehub_collaborators", type_="foreignkey")
    op.drop_constraint("musehub_collaborators_invited_by_fkey", "musehub_collaborators", type_="foreignkey")
    op.drop_constraint("musehub_stash_user_id_fkey", "musehub_stash", type_="foreignkey")

    # 2. Add profile fields to musehub_identities
    op.add_column("musehub_identities", sa.Column("location", sa.String(255), nullable=True))
    op.add_column("musehub_identities", sa.Column("twitter_handle", sa.String(64), nullable=True))
    op.add_column("musehub_identities", sa.Column(
        "is_verified", sa.Boolean, nullable=False, server_default="false"
    ))
    op.add_column("musehub_identities", sa.Column("cc_license", sa.String(50), nullable=True))
    op.add_column("musehub_identities", sa.Column(
        "pinned_repo_ids", sa.JSON, nullable=False, server_default="[]"
    ))
    op.create_index("ix_musehub_identities_is_verified", "musehub_identities", ["is_verified"])

    # 3. Drop legacy_user_id from musehub_identities
    op.drop_index("ix_musehub_identities_legacy_user_id", table_name="musehub_identities")
    op.drop_column("musehub_identities", "legacy_user_id")

    # 4. Drop legacy tables (order matters: tokens before users, profiles standalone)
    op.drop_table("muse_access_tokens")
    op.drop_table("musehub_profiles")
    op.drop_table("muse_users")


def downgrade() -> None:
    # Recreate muse_users
    op.create_table(
        "muse_users",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )

    # Recreate muse_access_tokens
    op.create_table(
        "muse_access_tokens",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("user_id", sa.String(36), sa.ForeignKey("muse_users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("token_hash", sa.String(64), nullable=False, unique=True),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("revoked", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    # Restore indexes that migration 0001 created on muse_access_tokens
    op.create_index("ix_muse_access_tokens_user_id", "muse_access_tokens", ["user_id"])
    op.create_index("ix_muse_access_tokens_token_hash", "muse_access_tokens", ["token_hash"], unique=True)

    # Recreate musehub_profiles
    op.create_table(
        "musehub_profiles",
        sa.Column("user_id", sa.String(36), primary_key=True),
        sa.Column("username", sa.String(64), nullable=False),
        sa.Column("display_name", sa.String(255), nullable=True),
        sa.Column("bio", sa.Text, nullable=True),
        sa.Column("avatar_url", sa.String(2048), nullable=True),
        sa.Column("location", sa.String(255), nullable=True),
        sa.Column("website_url", sa.String(2048), nullable=True),
        sa.Column("twitter_handle", sa.String(64), nullable=True),
        sa.Column("is_verified", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("cc_license", sa.String(50), nullable=True),
        sa.Column("pinned_repo_ids", sa.JSON, nullable=False, server_default="[]"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint("username", name="uq_musehub_profiles_username"),
    )
    # Restore indexes that migration 0001 created on musehub_profiles
    op.create_index("ix_musehub_profiles_username", "musehub_profiles", ["username"])
    op.create_index("ix_musehub_profiles_is_verified", "musehub_profiles", ["is_verified"])

    # Restore FK constraints
    op.create_foreign_key(
        "musehub_collaborators_user_id_fkey", "musehub_collaborators",
        "muse_users", ["user_id"], ["id"], ondelete="CASCADE"
    )
    op.create_foreign_key(
        "musehub_collaborators_invited_by_fkey", "musehub_collaborators",
        "muse_users", ["invited_by"], ["id"], ondelete="SET NULL"
    )
    op.create_foreign_key(
        "musehub_stash_user_id_fkey", "musehub_stash",
        "muse_users", ["user_id"], ["id"], ondelete="CASCADE"
    )

    # Restore legacy_user_id
    op.add_column("musehub_identities", sa.Column("legacy_user_id", sa.String(36), nullable=True))
    op.create_index("ix_musehub_identities_legacy_user_id", "musehub_identities", ["legacy_user_id"])

    # Remove profile fields from musehub_identities
    op.drop_index("ix_musehub_identities_is_verified", table_name="musehub_identities")
    op.drop_column("musehub_identities", "pinned_repo_ids")
    op.drop_column("musehub_identities", "cc_license")
    op.drop_column("musehub_identities", "is_verified")
    op.drop_column("musehub_identities", "twitter_handle")
    op.drop_column("musehub_identities", "location")