"""Intern attendance monthly-report domain. Tables: * attendance_cycles — one row per period (21→20 by default). * attendance_reports — one intern's versioned report per cycle, carrying the immutable snapshot_json + denormalised summary columns. * attendance_report_events — append-only state-transition audit. Convention (mirrors migration 324): person id columns (person_id / leader_person_id / created_by / actor_person_id / returned_by_person_id) are plain id columns with NO FK to identity_persons (TiDB-FK-safe), sized via ``id_type_for('identity_persons')``. The only real FKs are intra- feature (report → cycle, event → report); all three tables share the parent charset via ``table_kw_for`` so those FKs are type-compatible on TiDB. Revision ID: 339 Revises: 338 """ 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 = "339" down_revision = "338" branch_labels = None depends_on = None CYCLES = "attendance_cycles" REPORTS = "attendance_reports" EVENTS = "attendance_report_events" 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() pid = id_type_for("identity_persons") tkw = table_kw_for("identity_persons") if not _table_exists(bind, CYCLES): op.create_table( CYCLES, sa.Column("id", pid, nullable=False), sa.Column("cycle_key", sa.String(16), nullable=False), sa.Column("start_date", sa.Date, nullable=False), sa.Column("end_date", sa.Date, nullable=False), sa.Column("intern_department", sa.String(128), nullable=True), sa.Column("created_by", pid, 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.PrimaryKeyConstraint("id"), sa.UniqueConstraint("cycle_key", name="uq_attendance_cycles_key"), **tkw, ) op.create_index("ix_attendance_cycles_end_date", CYCLES, ["end_date"]) if not _table_exists(bind, REPORTS): op.create_table( REPORTS, sa.Column("id", pid, nullable=False), sa.Column("cycle_id", pid, nullable=False), sa.Column("person_id", pid, nullable=False), sa.Column("leader_person_id", pid, nullable=True), sa.Column("status", sa.String(24), nullable=False, server_default="generated"), sa.Column("version", sa.Integer, nullable=False, server_default="1"), sa.Column("snapshot_json", sa.JSON, nullable=True), sa.Column("work_days", sa.Integer, nullable=False, server_default="0"), sa.Column("abnormal_days", sa.Integer, nullable=False, server_default="0"), sa.Column("lack_count", sa.Integer, nullable=False, server_default="0"), sa.Column("late_count", sa.Integer, nullable=False, server_default="0"), sa.Column("early_count", sa.Integer, nullable=False, server_default="0"), sa.Column("remedied_count", sa.Integer, nullable=False, server_default="0"), sa.Column("data_quality_json", sa.JSON, nullable=True), sa.Column("return_reason", sa.Text, nullable=True), sa.Column("returned_by", sa.String(16), nullable=True), sa.Column("returned_by_person_id", pid, nullable=True), sa.Column("generated_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_sent_at", sa.DateTime(timezone=True), nullable=True), sa.Column("intern_confirmed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("leader_confirmed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("returned_at", sa.DateTime(timezone=True), 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.PrimaryKeyConstraint("id"), sa.UniqueConstraint( "cycle_id", "person_id", name="uq_attendance_reports_cycle_person", ), sa.ForeignKeyConstraint( ["cycle_id"], [f"{CYCLES}.id"], name="fk_attendance_reports_cycle", ondelete="CASCADE", ), **tkw, ) op.create_index("ix_attendance_reports_status", REPORTS, ["status"]) op.create_index("ix_attendance_reports_person", REPORTS, ["person_id"]) op.create_index("ix_attendance_reports_leader", REPORTS, ["leader_person_id"]) op.create_index("ix_attendance_reports_cycle_status", REPORTS, ["cycle_id", "status"]) if not _table_exists(bind, EVENTS): op.create_table( EVENTS, sa.Column("id", pid, nullable=False), sa.Column("report_id", pid, nullable=False), sa.Column("event_type", sa.String(32), nullable=False), sa.Column("actor_person_id", pid, nullable=True), sa.Column("from_status", sa.String(24), nullable=True), sa.Column("to_status", sa.String(24), nullable=True), sa.Column("reason", sa.Text, nullable=True), sa.Column("detail_json", sa.JSON, nullable=True), sa.Column("trace_id", sa.String(64), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint("id"), sa.ForeignKeyConstraint( ["report_id"], [f"{REPORTS}.id"], name="fk_attendance_report_events_report", ondelete="CASCADE", ), **tkw, ) op.create_index("ix_attendance_report_events_report", EVENTS, ["report_id"]) op.create_index("ix_attendance_report_events_type", EVENTS, ["event_type"]) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, EVENTS): op.drop_table(EVENTS) if _table_exists(bind, REPORTS): op.drop_table(REPORTS) if _table_exists(bind, CYCLES): op.drop_table(CYCLES)