"""Month-end book-close task model (PR2): templates, instances, entities.

Adds the operational task layer on top of PR1's calendar/period foundation:

  * finance_close_entities       — catalogue of the checklist's legal-entity
                                   shorthands (USP/TEJ/…), each best-effort
                                   mapped to an asset_legal_entities row.
  * finance_close_task_templates — the canonical 129-row task library
                                   (definition; seeded lazily from the
                                   captured checklist via ensure_seeded()).
  * finance_close_tasks          — per-period task instances (status, owner,
                                   comment, computed deadline).

Tables only — templates/entities are seeded idempotently at runtime by
`close_task_service.ensure_seeded` (it resolves owners + entity→legal-entity
links via the close coordinator, which a migration must not do). No FKs;
person/entity ids are plain CHAR(36) per the finance TiDB FK-avoidance
convention. Idempotent.

Revision ID: 356
Revises: 355
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "356"
down_revision = "355"
branch_labels = None
depends_on = None


def _table_exists(bind, table: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": table},
    ).fetchone()
    return row is not None


def _shared_cols() -> list[sa.Column]:
    return [
        sa.Column("process", sa.String(length=64), nullable=False),
        sa.Column("sub_process", sa.String(length=128), nullable=False, server_default=""),
        sa.Column("description", sa.Text(), nullable=False),
        sa.Column("owner_person_id", sa.String(length=36), nullable=True),
        sa.Column("owner_label", sa.String(length=64), nullable=True),
        sa.Column("frequency", sa.String(length=16), nullable=False, server_default="monthly"),
        sa.Column("calendar_day", sa.String(length=16), nullable=False, server_default="On going"),
        sa.Column("estimated_hours", sa.Numeric(6, 2), nullable=True),
        sa.Column("submit_to_fpa", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("ai_work", sa.String(length=16), nullable=False, server_default="none"),
        sa.Column("link", sa.String(length=512), nullable=True),
        sa.Column("entity_codes", sa.JSON(), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
    ]


def upgrade() -> None:
    bind = op.get_bind()

    if not _table_exists(bind, "finance_close_entities"):
        op.create_table(
            "finance_close_entities",
            sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
            sa.Column("code", sa.String(length=32), nullable=False),
            sa.Column("label", sa.String(length=128), nullable=True),
            sa.Column("legal_entity_id", sa.String(length=36), nullable=True),
            sa.Column("legal_entity_name", sa.String(length=255), nullable=True),
            sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
            sa.Column("active", sa.Boolean(), nullable=False, server_default="1"),
            sa.Column("created_at", sa.DateTime(timezone=True),
                      server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True),
                      server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("code", name="uq_finance_close_entity_code"),
        )

    if not _table_exists(bind, "finance_close_task_templates"):
        op.create_table(
            "finance_close_task_templates",
            sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
            sa.Column("source_key", sa.String(length=64), nullable=False),
            *(_shared_cols()),
            sa.Column("is_active", sa.Boolean(), nullable=False, server_default="1"),
            sa.Column("created_at", sa.DateTime(timezone=True),
                      server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True),
                      server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("source_key", name="uq_finance_close_template_source_key"),
        )
        op.create_index("ix_finance_close_template_process",
                        "finance_close_task_templates", ["process"])

    if not _table_exists(bind, "finance_close_tasks"):
        op.create_table(
            "finance_close_tasks",
            sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
            sa.Column("period", sa.String(length=7), nullable=False),
            sa.Column("instance_key", sa.String(length=64), nullable=False),
            sa.Column("template_source_key", sa.String(length=64), nullable=True),
            *(_shared_cols()),
            sa.Column("status", sa.String(length=16), nullable=False, server_default="not_started"),
            sa.Column("deadline", sa.Date(), nullable=True),
            sa.Column("comment", sa.Text(), nullable=True),
            sa.Column("commenter_person_id", sa.String(length=36), nullable=True),
            sa.Column("commenter_label", sa.String(length=64), nullable=True),
            sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("completed_by_id", sa.String(length=36), nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True),
                      server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
            sa.Column("created_by_id", sa.String(length=36), nullable=True),
            sa.Column("updated_at", sa.DateTime(timezone=True),
                      server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("period", "instance_key", name="uq_finance_close_task_period_key"),
        )
        op.create_index("ix_finance_close_task_period", "finance_close_tasks", ["period"])
        op.create_index("ix_finance_close_task_period_owner",
                        "finance_close_tasks", ["period", "owner_person_id"])


def downgrade() -> None:
    bind = op.get_bind()
    for table in (
        "finance_close_tasks",
        "finance_close_task_templates",
        "finance_close_entities",
    ):
        if _table_exists(bind, table):
            op.drop_table(table)