"""Seed NexusCRM integration + software catalog + license plan + connector binding + scheduled sync job. Revision ID: 315 Revises: 314 Create Date: 2026-06-22 End-to-end seed so NexusCRM is ready the moment the admin supplies the IT Connected App credentials. NexusCRM is a Salesforce-compatible CRM (``nexuscrm-new-prod.pingcap.com``); a "seat" is an active ``User``. What lands: * One ``integrations`` row (``slug='nexuscrm-primary'``, ``provider='nexuscrm'``, ``kind='software_provisioning'``, ``enabled=False``, secrets empty). Admin plugs the Consumer Key / Consumer Secret / instance_url in via ``scripts/seed_nexuscrm_integration.py`` (reads ``HELIX_NEXUSCRM_*`` from env) or the ``/admin/integrations`` UI, then enables the row. * One ``asset_software_catalog`` row ``name='NexusCRM'`` with ``offboarding_policy='auto_revoke'`` and ``owner_person_id`` / ``it_contact_person_id`` set to jie.ni@pingcap.com (resolved at migration time). Pairing auto_revoke with the connector binding's ``execution_mode='manual_notify'`` (below) means: when a NexusCRM seat-holder leaves, Helix creates a manual-notify task **assigned to the software owner (jie.ni)** — the provisioning service DMs jie.ni to handle the seat by hand. No auto-API revoke fires (decision 2026-06-22 — notify only). If jie.ni isn't found, owner stays NULL and the DM falls back to the IT-admin group. * One ``software_license_plans`` row ``name='NexusCRM Seat'``, ``over_quota_policy='reject'``, ``max_seats=NULL``. * One ``software_connectors`` binding pointing the catalog at the integration with ``execution_mode='manual_notify'`` and ``action_on_offboard='deactivate'``. When the IT Connected App is later granted write scope, flip this to ``auto_api`` to deactivate leavers automatically — no migration needed. * One ``scheduled_jobs`` row ``nexuscrm.seat_sync`` on a daily 03:00 UTC cron, disabled on first boot (enable after plugging credentials in). The ``email_template.provisioning.manual_notify`` platform-setting used by the manual-notify DM was already seeded by migration 056 — reused. Idempotent: safe to re-run. Every seed step checks for the slug / software name / job name first. """ from __future__ import annotations import json import uuid import sqlalchemy as sa from alembic import op revision = "315" down_revision = "314" branch_labels = None depends_on = None _SOFTWARE_NAME = "NexusCRM" _PLAN_NAME = "NexusCRM Seat" _INTEGRATION_SLUG = "nexuscrm-primary" _JOB_NAME = "nexuscrm.seat_sync" _OWNER_EMAIL = "jie.ni@pingcap.com" def upgrade() -> None: bind = op.get_bind() # ─── Resolve owner (jie.ni) — NULL if not present in this env ── owner_id = bind.execute( sa.text( "SELECT id FROM identity_persons " "WHERE LOWER(work_email) = :e LIMIT 1" ), {"e": _OWNER_EMAIL}, ).scalar() # ─── Seed: integration row ─────────────────────────────── integration_id = bind.execute( sa.text("SELECT id FROM integrations WHERE slug = :s"), {"s": _INTEGRATION_SLUG}, ).scalar() if integration_id is None: integration_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO integrations " "(id, kind, provider, name, slug, description, " " enabled, is_primary, " " config_public, secrets_encrypted, secrets_nonce) " "VALUES " "(:id, 'software_provisioning', 'nexuscrm', :name, :slug, " " :desc, 0, 1, '{}', NULL, NULL)" ), { "id": integration_id, "name": "NexusCRM", "slug": _INTEGRATION_SLUG, "desc": ( "NexusCRM(Salesforce 兼容)。IT Connected App 的 OAuth " "client_credentials:Consumer Key + Consumer Secret + " "instance_url。坐席 = 活跃 User。列表只读即可;revoke " "需要 run-as 用户带 api(写)scope。" ), }, ) # ─── Seed: software catalog row ───────────────────────── software_id = bind.execute( sa.text( "SELECT id FROM asset_software_catalog WHERE name = :n LIMIT 1" ), {"n": _SOFTWARE_NAME}, ).scalar() if software_id is None: software_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO asset_software_catalog " "(id, name, asset_type, owner_person_id, it_contact_person_id, " " status, remark, login_url, " " offboarding_policy, offboarding_grace_days, category) " "VALUES " "(:id, :name, 'SaaS', :owner, :owner, 'active', :remark, " " :login_url, 'auto_revoke', 0, 'other')" ), { "id": software_id, "name": _SOFTWARE_NAME, "owner": owner_id, "login_url": "https://nexuscrm-new-prod.pingcap.com/", "remark": ( "NexusCRM(Salesforce 兼容 CRM)。离职时走 auto_revoke + " "binding manual_notify:系统建回收任务并飞书 DM 负责人 " f"({_OWNER_EMAIL}) 手动处理,不自动调用 CRM。" "nexuscrm.seat_sync 每天核对活跃 User 修正坐席漂移。" ), }, ) # ─── Seed: license plan row ───────────────────────────── plan_id = bind.execute( sa.text( "SELECT id FROM software_license_plans " "WHERE software_id = :sid AND name = :n LIMIT 1" ), {"sid": software_id, "n": _PLAN_NAME}, ).scalar() if plan_id is None: plan_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO software_license_plans " "(id, software_id, name, price, billing_cycle, currency, " " description, status, max_seats, over_quota_policy) " "VALUES " "(:id, :sid, :name, 0, 'monthly', 'USD', :desc, 'active', " " NULL, 'reject')" ), { "id": plan_id, "sid": software_id, "name": _PLAN_NAME, "desc": ( "NexusCRM 付费坐席(活跃 User)。max_seats 不做硬约束。" ), }, ) # ─── Seed: software connector binding ─────────────────── connector_exists = bind.execute( sa.text( "SELECT 1 FROM software_connectors WHERE software_id = :sid LIMIT 1" ), {"sid": software_id}, ).scalar() if not connector_exists: bind.execute( sa.text( "INSERT INTO software_connectors " "(id, software_id, integration_id, binding_config, is_active) " "VALUES (:id, :sid, :iid, :cfg, 1)" ), { "id": str(uuid.uuid4()), "sid": software_id, "iid": integration_id, "cfg": json.dumps( { "execution_mode": "manual_notify", "action_on_offboard": "deactivate", }, ensure_ascii=False, ), }, ) # ─── Seed: scheduled job (disabled until creds plugged in) ── job_exists = bind.execute( sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"), {"n": _JOB_NAME}, ).scalar() if not job_exists: bind.execute( sa.text( """ INSERT INTO scheduled_jobs (id, name, display_name, description, handler_key, trigger_type, interval_seconds, cron_expr, timezone, enabled, config, run_count, failure_count, created_at, updated_at) VALUES (:id, :name, :display_name, :description, :handler_key, 'cron', NULL, '0 3 * * *', 'UTC', 0, '{}', 0, 0, NOW(), NOW()) """ ), { "id": str(uuid.uuid4()), "name": _JOB_NAME, "display_name": "NexusCRM · 坐席同步", "description": ( "每天 03:00 UTC 拉 NexusCRM 活跃 User 列表,镜像到 " "software_seat_allocations,并把 CRM 上已停用的成员对应" "坐席标为 revoked。填好 Connected App 凭据并启用集成后," "把该任务 enabled 置 1。" ), "handler_key": _JOB_NAME, }, ) def downgrade() -> None: """Conservative — drop only the scheduled job. The integration / software / plan / connector rows may have been customised, so they stay.""" bind = op.get_bind() bind.execute( sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME}, )