"""NetSuite GL inbound — actuals + financial master tables + disabled cron. Creates the "book of record" leg of the finance spine, all pulled read-only via SuiteQL by ``finance_netsuite_coordinator``: * finance_gl_actuals — summarized GL movement, grain (period × subsidiary × account × dept). NEVER raw transaction lines (volume). * finance_ns_subsidiaries — subsidiary (legal-entity) master, with a human-set legal_entity_id mapping. * finance_ns_accounts — chart-of-accounts master. * finance_ns_accounting_periods — period master (closed flag + YYYY-MM). …and seeds ``finance.netsuite_gl_actuals_sync`` **DISABLED** (enabled=0): activation requires confirming the NetSuite role's GL permissions (REST Web Services + Log in using Access Tokens + SuiteAnalytics Workbook + Accounts/Subsidiary/Department/Find Transaction) and running one probe to validate the SuiteQL column shape live, then flipping it on. The handler refreshes the masters inline before each GL pull, so a single daily cron keeps both fresh. No FKs (TiDB CHAR/VARCHAR FK avoidance, same as the rest of the finance domain). Idempotent. Revision ID: 350 Revises: 349 """ from __future__ import annotations import uuid import sqlalchemy as sa from alembic import op revision = "350" down_revision = "349" 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_gl_actuals"): op.create_table( "finance_gl_actuals", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("gl_key", sa.String(length=64), nullable=False), sa.Column("source", sa.String(length=32), nullable=False, server_default="netsuite"), sa.Column("period", sa.String(length=7), nullable=False), sa.Column("subsidiary_id", sa.String(length=64), nullable=True), sa.Column("account_id", sa.String(length=64), nullable=False), sa.Column("account_number", sa.String(length=128), nullable=True), sa.Column("account_name", sa.String(length=255), nullable=True), sa.Column("account_type", sa.String(length=64), nullable=True), sa.Column("department_id", sa.String(length=64), nullable=True), sa.Column("department_name", sa.String(length=255), nullable=True), sa.Column("amount_native", sa.Numeric(20, 2), nullable=False), sa.Column("currency", sa.String(length=8), nullable=False), sa.Column("amount_usd", sa.Numeric(20, 2), nullable=False), sa.Column("fx_rate_used", sa.Numeric(20, 8), nullable=True), sa.Column("fx_basis", sa.String(length=16), nullable=False, server_default="month_frozen"), sa.Column("is_closed", sa.Boolean(), nullable=False, server_default="0"), sa.Column("raw", sa.JSON(), nullable=True), sa.Column("pulled_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("gl_key", name="uq_finance_gl_key"), ) op.create_index("ix_fin_gl_period_acct", "finance_gl_actuals", ["period", "account_number"]) op.create_index("ix_fin_gl_period_sub", "finance_gl_actuals", ["period", "subsidiary_id"]) op.create_index("ix_fin_gl_period_dept", "finance_gl_actuals", ["period", "department_id"]) op.create_index("ix_fin_gl_period_type", "finance_gl_actuals", ["period", "account_type"]) if not _table_exists(bind, "finance_ns_subsidiaries"): op.create_table( "finance_ns_subsidiaries", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("subsidiary_id", sa.String(length=64), nullable=False), sa.Column("name", sa.String(length=255), nullable=True), sa.Column("full_name", sa.String(length=512), nullable=True), sa.Column("currency", sa.String(length=16), nullable=True), sa.Column("parent_id", sa.String(length=64), nullable=True), sa.Column("is_elimination", sa.Boolean(), nullable=False, server_default="0"), sa.Column("is_inactive", sa.Boolean(), nullable=False, server_default="0"), sa.Column("legal_entity_id", sa.String(length=36), nullable=True), sa.Column("raw", sa.JSON(), nullable=True), sa.Column("synced_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("subsidiary_id", name="uq_finance_ns_subsidiary_id"), ) if not _table_exists(bind, "finance_ns_accounts"): op.create_table( "finance_ns_accounts", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("account_id", sa.String(length=64), nullable=False), sa.Column("account_number", sa.String(length=128), nullable=True), sa.Column("account_name", sa.String(length=255), nullable=True), sa.Column("account_type", sa.String(length=64), nullable=True), sa.Column("parent_id", sa.String(length=64), nullable=True), sa.Column("is_inactive", sa.Boolean(), nullable=False, server_default="0"), sa.Column("raw", sa.JSON(), nullable=True), sa.Column("synced_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("account_id", name="uq_finance_ns_account_id"), ) op.create_index("ix_finance_ns_account_number", "finance_ns_accounts", ["account_number"]) if not _table_exists(bind, "finance_ns_accounting_periods"): op.create_table( "finance_ns_accounting_periods", sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False), sa.Column("period_id", sa.String(length=64), nullable=False), sa.Column("period_name", sa.String(length=128), nullable=True), sa.Column("start_date", sa.String(length=32), nullable=True), sa.Column("end_date", sa.String(length=32), nullable=True), sa.Column("period", sa.String(length=7), nullable=True), sa.Column("closed", sa.Boolean(), nullable=False, server_default="0"), sa.Column("is_adjustment", sa.Boolean(), nullable=False, server_default="0"), sa.Column("is_quarter", sa.Boolean(), nullable=False, server_default="0"), sa.Column("is_year", sa.Boolean(), nullable=False, server_default="0"), sa.Column("raw", sa.JSON(), nullable=True), sa.Column("synced_at", sa.DateTime(timezone=True), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("period_id", name="uq_finance_ns_period_id"), ) op.create_index("ix_finance_ns_period_calendar", "finance_ns_accounting_periods", ["period"]) # Seed the pull cron DISABLED (enabled=0). Daily once enabled; the # handler refreshes masters inline before each GL pull. if _table_exists(bind, "scheduled_jobs"): bind.execute( sa.text( "INSERT INTO scheduled_jobs " "(id, name, display_name, description, handler_key, " "trigger_type, interval_seconds, timezone, enabled, config, " "run_count, failure_count, created_at, updated_at) " "SELECT :id, :name, :display, :desc, :handler, 'interval', " ":interval, 'Asia/Shanghai', 0, :config, 0, 0, NOW(), NOW() " "FROM DUAL WHERE NOT EXISTS " "(SELECT 1 FROM scheduled_jobs WHERE name = :name)" ), { "id": str(uuid.uuid4()), "name": "finance.netsuite_gl_actuals_sync", "display": "财务对账 · NetSuite GL 实际账拉取", "desc": ( "通过 SuiteQL 增量拉取 NetSuite 汇总 GL(期间×主体×科目×部门" "粒度,绝不拉原始分录)+ 科目/子公司/会计期间主数据,落" " finance_gl_actuals 等。默认禁用:需先确认 NetSuite 角色 GL " "权限并跑一次 gl-probe 校验列结构后启用。已关账期间拉取一次后" "冻结,当前期间每日幂等覆盖。无连接器/无权限优雅跳过。" ), "handler": "finance.netsuite_gl_actuals_sync", "interval": 86400, "config": "{}", }, ) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, "scheduled_jobs"): bind.execute( sa.text( "DELETE FROM scheduled_jobs " "WHERE handler_key = 'finance.netsuite_gl_actuals_sync'" ) ) for table in ( "finance_gl_actuals", "finance_ns_subsidiaries", "finance_ns_accounts", "finance_ns_accounting_periods", ): if _table_exists(bind, table): op.drop_table(table)