"""Onboarding acknowledgement items — the 入职须知 primitive.

Two tables backing the ``training_ack`` Day-1 readiness dimension:

* ``onboarding_ack_items``        admin-managed checklist of notices a
                                  new hire must confirm (IT 安全须知,
                                  设备使用规范, …) with an optional doc
                                  link (Feishu doc / help-center page)
* ``onboarding_acknowledgements`` one row per (item, person) confirm —
                                  the readiness scanner treats the
                                  dimension as satisfied when every
                                  active+required item has a row

Tables are created with the parent's charset/collation so the inline
FKs to ``identity_persons`` can't trip error 3780 on prod TiDB.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "278"
down_revision = "277"
branch_labels = None
depends_on = None

ITEMS = "onboarding_ack_items"
ACKS = "onboarding_acknowledgements"


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


def upgrade() -> None:
    bind = op.get_bind()
    persons_id = id_type_for("identity_persons")
    # FK columns must mirror the PARENT column's exact type/collation —
    # onboarding_processes.id may differ from identity_persons.id.
    procs_id = id_type_for("onboarding_processes")
    kw = table_kw_for("identity_persons")

    if not _table_exists(bind, ITEMS):
        op.create_table(
            ITEMS,
            sa.Column("id", persons_id, primary_key=True),
            sa.Column("title", sa.String(255), nullable=False),
            sa.Column("description", sa.Text(), nullable=True),
            sa.Column("doc_url", sa.String(512), nullable=True),
            sa.Column(
                "required", sa.Boolean(), nullable=False,
                server_default=sa.text("1"),
            ),
            sa.Column(
                "is_active", sa.Boolean(), nullable=False,
                server_default=sa.text("1"),
            ),
            sa.Column(
                "sort_order", sa.Integer(), nullable=False,
                server_default="0",
            ),
            sa.Column("created_by_person_id", persons_id, nullable=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(),
            ),
            sa.ForeignKeyConstraint(
                ["created_by_person_id"], ["identity_persons.id"],
                name="fk_onboarding_ack_items_creator", ondelete="SET NULL",
            ),
            **kw,
        )
        op.create_index(
            "idx_onboarding_ack_items_active", ITEMS, ["is_active"],
        )

    if not _table_exists(bind, ACKS):
        op.create_table(
            ACKS,
            sa.Column("id", persons_id, primary_key=True),
            sa.Column("item_id", persons_id, nullable=False),
            sa.Column("person_id", persons_id, nullable=False),
            sa.Column("process_id", procs_id, nullable=True),
            sa.Column(
                "source", sa.String(16), nullable=False,
                server_default="web",
            ),
            sa.Column(
                "acknowledged_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.ForeignKeyConstraint(
                ["item_id"], [f"{ITEMS}.id"],
                name="fk_onboarding_acks_item", ondelete="CASCADE",
            ),
            sa.ForeignKeyConstraint(
                ["person_id"], ["identity_persons.id"],
                name="fk_onboarding_acks_person", ondelete="CASCADE",
            ),
            sa.ForeignKeyConstraint(
                ["process_id"], ["onboarding_processes.id"],
                name="fk_onboarding_acks_process", ondelete="SET NULL",
            ),
            sa.UniqueConstraint(
                "item_id", "person_id",
                name="uq_onboarding_acks_item_person",
            ),
            **kw,
        )
        op.create_index(
            "idx_onboarding_acks_person", ACKS, ["person_id"],
        )


def downgrade() -> None:
    op.drop_table(ACKS)
    op.drop_table(ITEMS)