"""finance-flux line sub-account details (drill-down)

Stores the GL/sub-account breakdown beneath each finance line (the detail rows
the parser previously discarded), so a flux line can expand to show what makes
it up — parity with the legacy portal's account → sub-account drill-down.

Revision ID: 349
Revises: 348
"""
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 = "349"
down_revision = "348"
branch_labels = None
depends_on = None

DETAILS = "finance_flux_line_details"
PERIODS = "finance_flux_periods"


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()
    # Same id type as finance_flux_periods.id (which 347 typed via
    # identity_persons) so the FK is type-compatible on TiDB (avoids 3780).
    pid = id_type_for("identity_persons")
    tkw = table_kw_for("identity_persons")

    if not _table_exists(bind, DETAILS):
        op.create_table(
            DETAILS,
            sa.Column("id", pid, nullable=False),
            sa.Column("period_id", pid, nullable=False),
            sa.Column("statement", sa.String(2), nullable=False),
            sa.Column("parent_line_key", sa.String(64), nullable=False),
            sa.Column("label", sa.String(255), nullable=False),
            sa.Column("value", sa.Numeric(20, 2), nullable=False, server_default="0"),
            sa.Column("sort_order", sa.Integer, nullable=False, server_default="0"),
            sa.Column("created_at", sa.DateTime(timezone=True), nullable=False,
                      server_default=sa.func.now()),
            sa.PrimaryKeyConstraint("id"),
            sa.ForeignKeyConstraint(
                ["period_id"], [f"{PERIODS}.id"],
                name="fk_finance_flux_line_details_period", ondelete="CASCADE"),
            **tkw,
        )
        op.create_index(
            "ix_finance_flux_line_details_parent", DETAILS,
            ["period_id", "statement", "parent_line_key"])


def downgrade() -> None:
    op.drop_index("ix_finance_flux_line_details_parent", table_name=DETAILS)
    op.drop_table(DETAILS)