"""Merge profiles table into users, add counter columns.

Revision ID: 20260728_0003
Revises: 20260726_0002
Create Date: 2026-07-28
"""

from typing import Sequence

from alembic import op
import sqlalchemy as sa


revision: str = "20260728_0003"
down_revision: str | None = "20260726_0002"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column("users", sa.Column("username", sa.Text(), nullable=True))
    op.add_column("users", sa.Column("avatar_path", sa.Text(), nullable=True))
    op.add_column("users", sa.Column("role", sa.Text(), server_default="user", nullable=True))
    op.add_column("users", sa.Column("is_disabled", sa.Boolean(), server_default=sa.text("false"), nullable=True))
    op.add_column("users", sa.Column("cloud_count", sa.Integer(), server_default="0", nullable=True))
    op.add_column("users", sa.Column("public_cloud_count", sa.Integer(), server_default="0", nullable=True))
    op.add_column("users", sa.Column("collection_count", sa.Integer(), server_default="0", nullable=True))

    op.execute("""
        UPDATE users SET
            username = p.username,
            avatar_path = p.avatar_path,
            role = p.role,
            is_disabled = p.is_disabled
        FROM profiles p
        WHERE users.id = p.id
    """)

    op.alter_column("users", "username", nullable=False)
    op.alter_column("users", "role", nullable=False)
    op.alter_column("users", "is_disabled", nullable=False)
    op.alter_column("users", "cloud_count", nullable=False)
    op.alter_column("users", "public_cloud_count", nullable=False)
    op.alter_column("users", "collection_count", nullable=False)

    op.create_unique_constraint("uq_users_username", "users", ["username"])
    op.create_check_constraint("ck_users_username_length", "users", "length(trim(username)) >= 2")
    op.create_check_constraint("ck_users_role", "users", "role IN ('user', 'admin')")

    op.drop_table("profiles")

    op.add_column("clouds", sa.Column("favorite_count", sa.Integer(), server_default="0", nullable=True))
    op.execute("""
        UPDATE clouds SET favorite_count = (
            SELECT COUNT(*) FROM cloud_favorites WHERE cloud_favorites.cloud_id = clouds.id
        )
    """)
    op.alter_column("clouds", "favorite_count", nullable=False)


def downgrade() -> None:
    op.create_table(
        "profiles",
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("username", sa.Text(), nullable=False),
        sa.Column("avatar_path", sa.Text(), nullable=True),
        sa.Column("role", sa.Text(), server_default="user", nullable=False),
        sa.Column("is_disabled", sa.Boolean(), server_default=sa.text("false"), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.CheckConstraint("length(trim(username)) >= 2", name="ck_profiles_username_length"),
        sa.CheckConstraint("role IN ('user', 'admin')", name="ck_profiles_role"),
        sa.ForeignKeyConstraint(["id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("username"),
    )

    op.execute("""
        INSERT INTO profiles (id, username, avatar_path, role, is_disabled, created_at, updated_at)
        SELECT id, username, avatar_path, role, is_disabled, created_at, created_at
        FROM users
    """)

    op.drop_constraint("ck_users_role", "users", type_="check")
    op.drop_constraint("ck_users_username_length", "users", type_="check")
    op.drop_constraint("uq_users_username", "users", type_="unique")
    op.drop_column("users", "collection_count")
    op.drop_column("users", "public_cloud_count")
    op.drop_column("users", "cloud_count")
    op.drop_column("users", "is_disabled")
    op.drop_column("users", "role")
    op.drop_column("users", "avatar_path")
    op.drop_column("users", "username")

    op.drop_column("clouds", "favorite_count")