"""init db

Revision ID: 4f1a83256323
Revises:
Create Date: 2024-05-22 10:06:43.461849

"""

import uuid
from collections.abc import Sequence
from datetime import date, datetime

import sqlalchemy as sa
from alembic import op
from dateutil.relativedelta import relativedelta

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


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "permissions",
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("description", sa.String(length=1024), nullable=True),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("name"),
    )
    op.create_index(op.f("ix_permissions_id"), "permissions", ["id"], unique=True)
    op.create_table(
        "roles",
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("description", sa.String(length=1024), nullable=True),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("name"),
    )
    op.create_index(op.f("ix_roles_id"), "roles", ["id"], unique=True)
    op.create_table(
        "users",
        sa.Column("login", sa.String(length=255), nullable=False),
        sa.Column("password", sa.String(length=255), nullable=False),
        sa.Column("first_name", sa.String(length=64), nullable=True),
        sa.Column("last_name", sa.String(length=64), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=True),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_users_id"), "users", ["id"], unique=True)
    op.create_index(op.f("ix_users_login"), "users", ["login"], unique=True)
    op.create_table(
        "provider_accounts",
        sa.Column("user_id", sa.Uuid(), nullable=False),
        sa.Column("id_social", sa.String(length=255), nullable=False),
        sa.Column(
            "provider_name",
            sa.Enum("YANDEX", "GOOGLE", name="provider"),
            nullable=False,
        ),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        op.f("ix_provider_accounts_id"), "provider_accounts", ["id"], unique=True
    )
    op.create_index(
        op.f("ix_provider_accounts_id_social"),
        "provider_accounts",
        ["id_social"],
        unique=True,
    )
    op.create_table(
        "role_permissions",
        sa.Column("role_id", sa.Uuid(), nullable=False),
        sa.Column("permission_id", sa.Uuid(), nullable=False),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["permission_id"], ["permissions.id"]),
        sa.ForeignKeyConstraint(["role_id"], ["roles.id"]),
        sa.PrimaryKeyConstraint("role_id", "permission_id", "id"),
    )
    op.create_index(
        op.f("ix_role_permissions_id"), "role_permissions", ["id"], unique=True
    )
    op.create_table(
        "user_roles",
        sa.Column("user_id", sa.Uuid(), nullable=False),
        sa.Column("role_id", sa.Uuid(), nullable=False),
        sa.Column("id", sa.Uuid(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["role_id"], ["roles.id"]),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"]),
        sa.PrimaryKeyConstraint("user_id", "role_id", "id"),
    )
    op.create_index(op.f("ix_user_roles_id"), "user_roles", ["id"], unique=True)

    # Создание партиционированной таблицы user_sessions
    op.execute("""
        CREATE TABLE user_sessions (
            id UUID NOT NULL,
            created_at TIMESTAMPTZ NOT NULL,
            updated_at TIMESTAMPTZ NOT NULL,
            refresh_token VARCHAR NOT NULL,
            user_agent VARCHAR(4096),
            user_id UUID,
            PRIMARY KEY (id, created_at)
        ) PARTITION BY RANGE (created_at);
    """)

    op.create_index(
        op.f("ix_user_sessions_id_created_at"),
        "user_sessions",
        ["id", "created_at"],
        unique=True,
    )
    op.create_index(
        op.f("ix_user_sessions_refresh_token_created_at"),
        "user_sessions",
        ["refresh_token", "created_at"],
        unique=True,
    )
    op.create_index(
        op.f("ix_user_sessions_user_id"), "user_sessions", ["user_id"], unique=False
    )

    # Создание партиций для таблицы user_sessions
    create_monthly_partitions(date(2023, 1, 1), date(2025, 1, 1))

    roles_table = sa.Table(
        "roles",
        sa.MetaData(),
        sa.Column("id", sa.Uuid(), primary_key=True),
        sa.Column("name", sa.String()),
        sa.Column(
            "created_at", sa.DateTime(), nullable=False, server_default=sa.func.now()
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.func.now(),
            onupdate=sa.func.now(),
        ),
    )

    op.bulk_insert(
        roles_table,
        [
            {
                "id": str(uuid.uuid4()),
                "name": "admin",
                "created_at": datetime.utcnow(),
                "updated_at": datetime.utcnow(),
            },
            {
                "id": str(uuid.uuid4()),
                "name": "guest",
                "created_at": datetime.utcnow(),
                "updated_at": datetime.utcnow(),
            },
            {
                "id": str(uuid.uuid4()),
                "name": "user",
                "created_at": datetime.utcnow(),
                "updated_at": datetime.utcnow(),
            },
        ],
    )


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f("ix_user_sessions_user_id"), table_name="user_sessions")
    op.drop_index(
        op.f("ix_user_sessions_refresh_token_created_at"), table_name="user_sessions"
    )
    op.drop_index(op.f("ix_user_sessions_id_created_at"), table_name="user_sessions")
    op.drop_table("user_sessions")
    op.drop_index(op.f("ix_user_roles_id"), table_name="user_roles")
    op.drop_table("user_roles")
    op.drop_index(op.f("ix_role_permissions_id"), table_name="role_permissions")
    op.drop_table("role_permissions")
    op.drop_index(
        op.f("ix_provider_accounts_id_social"), table_name="provider_accounts"
    )
    op.drop_index(op.f("ix_provider_accounts_id"), table_name="provider_accounts")
    op.drop_table("provider_accounts")
    op.drop_index(op.f("ix_users_login"), table_name="users")
    op.drop_index(op.f("ix_users_id"), table_name="users")
    op.drop_table("users")
    op.drop_index(op.f("ix_roles_id"), table_name="roles")
    op.drop_table("roles")
    op.drop_index(op.f("ix_permissions_id"), table_name="permissions")
    op.drop_table("permissions")
    # ### end Alembic commands ###


def create_monthly_partitions(start_date: date, end_date: date):
    current_date = start_date
    while current_date < end_date:
        partition_name = "user_sessions_" + current_date.strftime("%Y_%m")
        partition_range_start = current_date.strftime("%Y-%m-%d")
        current_date += relativedelta(months=1)
        partition_range_end = current_date.strftime("%Y-%m-%d")

        op.execute(f"""
            CREATE TABLE IF NOT EXISTS {partition_name} PARTITION OF user_sessions
            FOR VALUES FROM ('{partition_range_start}') TO ('{partition_range_end}');
        """)
