"""Month-end book-close calendar + period lifecycle (PR1). Creates the operational book-close orchestration foundation — distinct from ``finance_period_close`` (which freezes the spend-ledger numbers): * finance_close_calendar_config — singleton org-wide calendar settings (target workday + weekend-work rule). * finance_close_holidays — public holidays excluded from workday counting (one per day+country). * finance_close_makeup_days — 补班日, weekends counted as workdays. * finance_close_runs — one per closing period: editable Day±1 anchors, optional manual close-date override (audited), close lifecycle. Seeds the singleton config + the bundled PingCAP/CN defaults (China public holidays 2024–2027 + official makeup workdays) from ``app.domains.finance.close_calendar_defaults`` (a pure-data module, safe to import here). No FKs — person ids are plain CHAR(36) columns, matching the finance-domain TiDB CHAR/VARCHAR FK-avoidance convention. Idempotent. Revision ID: 355 Revises: 354 """ from __future__ import annotations import sqlalchemy as sa from alembic import op from app.domains.finance.close_calendar_defaults import ( DEFAULT_HOLIDAY_COUNTRY, DEFAULT_HOLIDAYS, DEFAULT_MAKEUP_WORKDAYS, DEFAULT_TARGET_WORKDAY, DEFAULT_WORK_WEEKEND_DAY, ) revision = "355" down_revision = "354" 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 upgrade() -> None: bind = op.get_bind() if not _table_exists(bind, "finance_close_calendar_config"): op.create_table( "finance_close_calendar_config", sa.Column("id", sa.Integer(), autoincrement=False, nullable=False), sa.Column("target_workday", sa.Integer(), nullable=False, server_default="5"), sa.Column("work_weekend_day", sa.String(length=16), nullable=False, server_default="saturday"), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.Column("updated_by_id", sa.String(length=36), nullable=True), sa.PrimaryKeyConstraint("id"), ) if not _table_exists(bind, "finance_close_holidays"): op.create_table( "finance_close_holidays", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("day", sa.Date(), nullable=False), sa.Column("country", sa.String(length=8), nullable=False, server_default="CN"), sa.Column("name", sa.String(length=255), nullable=True), sa.Column("source", sa.String(length=16), nullable=False, server_default="manual"), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("day", "country", name="uq_finance_close_holiday_day_country"), ) op.create_index("ix_finance_close_holidays_day", "finance_close_holidays", ["day"]) if not _table_exists(bind, "finance_close_makeup_days"): op.create_table( "finance_close_makeup_days", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("day", sa.Date(), nullable=False), sa.Column("note", sa.String(length=255), nullable=True), sa.Column("source", sa.String(length=16), nullable=False, server_default="manual"), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("day", name="uq_finance_close_makeup_day"), ) op.create_index("ix_finance_close_makeup_days_day", "finance_close_makeup_days", ["day"]) if not _table_exists(bind, "finance_close_runs"): op.create_table( "finance_close_runs", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("period", sa.String(length=7), nullable=False), sa.Column("label", sa.String(length=32), nullable=False), sa.Column("anchor_minus_1", sa.Date(), nullable=False), sa.Column("anchor_plus_1", sa.Date(), nullable=False), sa.Column("override_close_date", sa.Date(), nullable=True), sa.Column("override_reason", sa.Text(), nullable=True), sa.Column("override_by_id", sa.String(length=36), nullable=True), sa.Column("override_at", sa.DateTime(timezone=True), nullable=True), sa.Column("frozen_close_date", sa.Date(), nullable=True), sa.Column("frozen_target_workday", sa.Integer(), nullable=True), sa.Column("frozen_work_weekend_day", sa.String(length=16), nullable=True), sa.Column("status", sa.String(length=16), nullable=False, server_default="open"), sa.Column("soft_closed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("soft_closed_by_id", sa.String(length=36), nullable=True), sa.Column("closed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("closed_by_id", sa.String(length=36), nullable=True), sa.Column("reopened_at", sa.DateTime(timezone=True), nullable=True), sa.Column("reopened_by_id", sa.String(length=36), nullable=True), sa.Column("note", sa.Text(), 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", name="uq_finance_close_run_period"), ) # ── seed singleton config + default calendar (idempotent) ── cfg = sa.table( "finance_close_calendar_config", sa.column("id", sa.Integer), sa.column("target_workday", sa.Integer), sa.column("work_weekend_day", sa.String), ) existing_cfg = bind.execute( sa.text("SELECT 1 FROM finance_close_calendar_config WHERE id = 1") ).fetchone() if existing_cfg is None: op.bulk_insert(cfg, [{ "id": 1, "target_workday": DEFAULT_TARGET_WORKDAY, "work_weekend_day": DEFAULT_WORK_WEEKEND_DAY, }]) hol = sa.table( "finance_close_holidays", sa.column("day", sa.Date), sa.column("country", sa.String), sa.column("name", sa.String), sa.column("source", sa.String), ) have_h = { (r[0].isoformat() if hasattr(r[0], "isoformat") else str(r[0]), r[1]) for r in bind.execute( sa.text("SELECT day, country FROM finance_close_holidays") ).fetchall() } rows_h = [ {"day": d, "country": DEFAULT_HOLIDAY_COUNTRY, "name": None, "source": "default"} for d in DEFAULT_HOLIDAYS if (d, DEFAULT_HOLIDAY_COUNTRY) not in have_h ] if rows_h: op.bulk_insert(hol, rows_h) mk = sa.table( "finance_close_makeup_days", sa.column("day", sa.Date), sa.column("note", sa.String), sa.column("source", sa.String), ) have_m = { (r[0].isoformat() if hasattr(r[0], "isoformat") else str(r[0])) for r in bind.execute( sa.text("SELECT day FROM finance_close_makeup_days") ).fetchall() } rows_m = [ {"day": d, "note": None, "source": "default"} for d in DEFAULT_MAKEUP_WORKDAYS if d not in have_m ] if rows_m: op.bulk_insert(mk, rows_m) def downgrade() -> None: bind = op.get_bind() for table in ( "finance_close_runs", "finance_close_makeup_days", "finance_close_holidays", "finance_close_calendar_config", ): if _table_exists(bind, table): op.drop_table(table)