"""ORM rows for the month-end **book-close** orchestration sub-domain. Distinct from :class:`app.domains.finance.models.FinancePeriodClose`, which freezes the *spend-ledger* numbers for a period. These tables drive the operational close *checklist* — the calendar, the per-period close run and its lifecycle, and (in PR2) the task templates + instances. Standalone tables, no DB-level foreign keys: person references are stored as plain ``String(36)`` id columns, matching the finance-domain convention that avoids TiDB's CHAR/VARCHAR + collation FK strictness against ``identity_persons`` (see :mod:`app.domains.finance.models` and memory ``table_kw_for_sibling_vs_person_fk_3780``). Tables ------ * ``finance_close_calendar_config`` — singleton org-wide calendar settings (target workday + weekend-work rule). * ``finance_close_holidays`` — public holidays excluded from workday counting. * ``finance_close_makeup_days`` — 补班日, weekends counted as workdays. * ``finance_close_runs`` — one per closing period: editable Day±1 anchors, optional manual close-date override (with audit note), and the close lifecycle (open → in_progress → soft_closed → closed, reopenable). """ from __future__ import annotations from datetime import date, datetime from sqlalchemy import ( JSON, BigInteger, Boolean, Date, DateTime, Index, Integer, Numeric, String, Text, UniqueConstraint, ) from sqlalchemy.orm import Mapped, mapped_column from sqlalchemy.sql import func from app.core.database import Base # ── calendar config ── WORK_WEEKEND_SATURDAY = "saturday" WORK_WEEKEND_SUNDAY = "sunday" WORK_WEEKEND_DAYS = (WORK_WEEKEND_SATURDAY, WORK_WEEKEND_SUNDAY) # ── close-run lifecycle ── RUN_OPEN = "open" # window scheduled, work not yet started RUN_IN_PROGRESS = "in_progress" # at least one task moved off "not started" RUN_SOFT_CLOSED = "soft_closed" # standalone book soft-closed (Day+6 announce) RUN_CLOSED = "closed" # hard close — frozen RUN_STATUSES = (RUN_OPEN, RUN_IN_PROGRESS, RUN_SOFT_CLOSED, RUN_CLOSED) # Statuses in which the computed close date is frozen onto the run. RUN_FROZEN_STATUSES = (RUN_SOFT_CLOSED, RUN_CLOSED) # Holiday / makeup provenance. SOURCE_DEFAULT = "default" # seeded from the built-in PingCAP/CN calendar SOURCE_MANUAL = "manual" # added by an admin class FinanceCloseCalendarConfig(Base): """Org-wide close-calendar settings. Single active row (``id=1``).""" __tablename__ = "finance_close_calendar_config" __mapper_args__ = {"eager_defaults": True} id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=False) # Nth business day of the following month on which the book closes. target_workday: Mapped[int] = mapped_column( Integer, nullable=False, default=5, server_default="5", ) # Which weekend day the team works when the close window overlaps a full # weekend: 'saturday' (PingCAP default) or 'sunday'. work_weekend_day: Mapped[str] = mapped_column( String(16), nullable=False, default=WORK_WEEKEND_SATURDAY, server_default=WORK_WEEKEND_SATURDAY, ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, ) updated_by_id: Mapped[str | None] = mapped_column(String(36)) class FinanceCloseHoliday(Base): """A public holiday excluded from workday counting (one per day+country).""" __tablename__ = "finance_close_holidays" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("day", "country", name="uq_finance_close_holiday_day_country"), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) day: Mapped[date] = mapped_column(Date, nullable=False, index=True) country: Mapped[str] = mapped_column( String(8), nullable=False, default="CN", server_default="CN", ) name: Mapped[str | None] = mapped_column(String(255)) source: Mapped[str] = mapped_column( String(16), nullable=False, default=SOURCE_MANUAL, server_default=SOURCE_MANUAL, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) class FinanceCloseMakeupDay(Base): """A 补班日 — a weekend day officially designated a workday.""" __tablename__ = "finance_close_makeup_days" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("day", name="uq_finance_close_makeup_day"), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) day: Mapped[date] = mapped_column(Date, nullable=False, index=True) note: Mapped[str | None] = mapped_column(String(255)) source: Mapped[str] = mapped_column( String(16), nullable=False, default=SOURCE_MANUAL, server_default=SOURCE_MANUAL, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) class FinanceCloseRun(Base): """One book-close run for a closing period (``period`` = YYYY-MM). The effective book-close date is, in precedence order: the manual ``override_close_date`` if set; else, once the run is soft/hard closed, the frozen ``frozen_close_date`` snapshot; else the live engine result computed from the editable Day±1 anchors and the current calendar config. """ __tablename__ = "finance_close_runs" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("period", name="uq_finance_close_run_period"), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) period: Mapped[str] = mapped_column(String(7), nullable=False) # YYYY-MM label: Mapped[str] = mapped_column(String(32), nullable=False) # "June 2026" # Editable deadline anchors. Day-N counts calendar days back from # anchor_minus_1; Day+N is the Nth business day from anchor_plus_1. anchor_minus_1: Mapped[date] = mapped_column(Date, nullable=False) anchor_plus_1: Mapped[date] = mapped_column(Date, nullable=False) # Manual override of the computed close date (with mandatory audit note). override_close_date: Mapped[date | None] = mapped_column(Date) override_reason: Mapped[str | None] = mapped_column(Text) override_by_id: Mapped[str | None] = mapped_column(String(36)) override_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # Frozen snapshot captured when the run is soft/hard closed, so a later # calendar-config change can never retroactively move a closed period. frozen_close_date: Mapped[date | None] = mapped_column(Date) frozen_target_workday: Mapped[int | None] = mapped_column(Integer) frozen_work_weekend_day: Mapped[str | None] = mapped_column(String(16)) status: Mapped[str] = mapped_column( String(16), nullable=False, default=RUN_OPEN, server_default=RUN_OPEN, ) soft_closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) soft_closed_by_id: Mapped[str | None] = mapped_column(String(36)) closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) closed_by_id: Mapped[str | None] = mapped_column(String(36)) reopened_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) reopened_by_id: Mapped[str | None] = mapped_column(String(36)) note: Mapped[str | None] = mapped_column(Text) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) created_by_id: Mapped[str | None] = mapped_column(String(36)) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, ) # ── task status (instances) ── TASK_NOT_STARTED = "not_started" TASK_IN_PROGRESS = "in_progress" TASK_COMPLETED = "completed" TASK_NA = "na" TASK_STATUSES = (TASK_NOT_STARTED, TASK_IN_PROGRESS, TASK_COMPLETED, TASK_NA) # A finished task is one no longer needing work (completed or explicitly N/A). TASK_DONE_STATUSES = (TASK_COMPLETED, TASK_NA) # ── frequency / ai-work canonical values (mirror close_seed_loader) ── FREQ_ONGOING = "ongoing" FREQUENCIES = ("ongoing", "monthly", "quarterly", "annual") AI_WORK_VALUES = ("assist", "perform", "no", "na", "none") class FinanceCloseEntity(Base): """Catalogue of the close checklist's legal-entity shorthands. Each row maps a checklist entity code (e.g. ``USP``, ``TEJ``, ``Nebula``) to an ``asset_legal_entities`` row when one can be resolved (stored as a plain id string, resolved via the close coordinator — finance never imports the asset domain). ``legal_entity_id`` is nullable so an unresolved code still renders by its label. """ __tablename__ = "finance_close_entities" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("code", name="uq_finance_close_entity_code"), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) code: Mapped[str] = mapped_column(String(32), nullable=False) label: Mapped[str | None] = mapped_column(String(128)) legal_entity_id: Mapped[str | None] = mapped_column(String(36)) legal_entity_name: Mapped[str | None] = mapped_column(String(255)) sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0") active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, server_default="1") created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, ) class _CloseTaskFields: """Columns shared by the template (definition) and the instance (per-period).""" process: Mapped[str] = mapped_column(String(64), nullable=False) sub_process: Mapped[str] = mapped_column(String(128), nullable=False, default="", server_default="") description: Mapped[str] = mapped_column(Text, nullable=False) # Owner = a real person when resolvable; the raw checklist label is always # kept so an unresolved owner still displays (and re-resolution can retry). owner_person_id: Mapped[str | None] = mapped_column(String(36)) owner_label: Mapped[str | None] = mapped_column(String(64)) frequency: Mapped[str] = mapped_column(String(16), nullable=False, default="monthly", server_default="monthly") calendar_day: Mapped[str] = mapped_column(String(16), nullable=False, default="On going", server_default="On going") estimated_hours: Mapped[float | None] = mapped_column(Numeric(6, 2)) submit_to_fpa: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, server_default="0") ai_work: Mapped[str] = mapped_column(String(16), nullable=False, default="none", server_default="none") link: Mapped[str | None] = mapped_column(String(512)) # Applicable legal-entity codes (subset of FinanceCloseEntity.code). entity_codes: Mapped[list | None] = mapped_column(JSON) sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0, server_default="0") # Optional binding to a Helix-native close signal (see close_signals.py): # when set, the console shows that signal's live green/amber/red state next # to the task as decision support (it never auto-completes the task). signal_key: Mapped[str | None] = mapped_column(String(64)) class FinanceCloseTaskTemplate(Base, _CloseTaskFields): """The canonical, reusable definition of one close task. The 129-row library seeded from the finance team's checklist. Roll-forward / initial population instantiate :class:`FinanceCloseTask` rows from these. ``source_key`` is the stable id from the seed (e.g. ``ap-01``) so re-seeding is an idempotent upsert. """ __tablename__ = "finance_close_task_templates" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("source_key", name="uq_finance_close_template_source_key"), Index("ix_finance_close_template_process", "process"), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) source_key: Mapped[str] = mapped_column(String(64), nullable=False) is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, server_default="1") created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, ) class FinanceCloseTask(Base, _CloseTaskFields): """A close task instance for one closing period. Snapshotted from a template (or another period's task) at roll-forward, then tracked independently: status, comment, owner reassignment, computed deadline. ``template_source_key`` links back to the template (NULL for ad-hoc tasks added directly to a period). """ __tablename__ = "finance_close_tasks" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("period", "instance_key", name="uq_finance_close_task_period_key"), Index("ix_finance_close_task_period", "period"), Index("ix_finance_close_task_period_owner", "period", "owner_person_id"), ) id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True) period: Mapped[str] = mapped_column(String(7), nullable=False) # Stable per-period key for idempotent roll-forward: the template # source_key, or a generated key for ad-hoc tasks. instance_key: Mapped[str] = mapped_column(String(64), nullable=False) template_source_key: Mapped[str | None] = mapped_column(String(64)) status: Mapped[str] = mapped_column( String(16), nullable=False, default=TASK_NOT_STARTED, server_default=TASK_NOT_STARTED, ) deadline: Mapped[date | None] = mapped_column(Date) comment: Mapped[str | None] = mapped_column(Text) commenter_person_id: Mapped[str | None] = mapped_column(String(36)) commenter_label: Mapped[str | None] = mapped_column(String(64)) completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) completed_by_id: Mapped[str | None] = mapped_column(String(36)) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, ) created_by_id: Mapped[str | None] = mapped_column(String(36)) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, )