"""Seed the ``feishu.seat_sync`` scheduled job.

Revision ID: 109
Revises: 108
Create Date: 2026-05-02

(Originally authored as 105 against then-head 104; PR #149 landed
105-108 first, so this seed was renumbered to chain after 108.
Pure data seed, no schema dependency on the 105-108 tables, so the
move is mechanical.)

End-to-end seed for the new Feishu user-group seat back-fill job.
What lands:

* One ``scheduled_jobs`` row ``feishu.seat_sync`` on a daily 03:00 UTC
  cron (matches Jira / Slack / Zoom's off-peak window). Disabled on
  first boot so the admin can plug credentials + create the
  ``software_connectors`` binding before the first sync fires.

What deliberately does NOT land here (different from
``077_seed_jira_integration.py``):

* **No ``integrations`` row.** A ``feishu_group`` integration row
  already exists in every deploy from migration 028 / 034 (the
  generic standard-integrations seed) — re-seeding would either
  collide or no-op, and reusing whatever the admin already
  configured is the right behaviour.

* **No ``asset_software_catalog`` row.** Feishu user groups are
  whatever the company defined in their tenant (``oc_…`` chat ids
  with admin-chosen names like "飞书 - 财务系统"); there's no
  industry-standard catalog name to seed. The existing "飞书"
  catalog row created by the admin (verified 2026-05-02) is what
  ``feishu.seat_sync`` walks against.

* **No ``software_license_plans`` row, no ``software_connectors``
  row.** Same reason — these are tenant-specific and the admin
  creates them through the UI alongside the binding's
  ``group_chat_id``.

The handler is idempotent: re-running with no Feishu integration
configured / no connector binding returns ``plans_synced=0`` with a
descriptive error in the result, not an exception. The job stays
``enabled=0`` until the operator verifies the chain end-to-end.

Idempotent: safe to re-run. Existing job rows with the same name are
skipped (the admin may have customised cron / config since seed).
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "109"
down_revision = "108"
branch_labels = None
depends_on = None


_JOB_NAME = "feishu.seat_sync"


def upgrade() -> None:
    bind = op.get_bind()

    job_exists = bind.execute(
        sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"),
        {"n": _JOB_NAME},
    ).scalar()
    if job_exists:
        return

    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())
            """
        ),
        {
            # Pre-mint UUID — TiDB's UUID() is unreliable per prior
            # ops experience (see 077_seed_jira_integration.py for
            # the same precaution), so the migration controls the id.
            "id": str(uuid.uuid4()),
            "name": _JOB_NAME,
            "display_name": "飞书 · 用户组成员同步",
            "description": (
                "每天 03:00 UTC 拉每个 group_member 模式的飞书坐席 plan 对应"
                "的用户组成员,把匹配到 Helix 员工的成员 back-fill 到 "
                "software_seat_allocations。仅导入,不清理:飞书侧已不在组的"
                "成员对应坐席不会自动 revoke(这是与 Jira / Zoom / Slack 同步"
                "的有意差异,避免人工临时调组触发误关)。配好 feishu_group "
                "integration 凭据 + 在某个软件的 software_connectors 行里设 "
                "binding_config={operation:'group_member', group_chat_id:'oc_…'} "
                "之后,把该任务 enabled 置 1 即可生效。"
            ),
            "handler_key": _JOB_NAME,
        },
    )


def downgrade() -> None:
    """Conservative downgrade — drop only the scheduled job row.

    The ``software_seat_allocations`` rows the job back-filled are
    real seat records and stay; revoking them on rollback would
    obscure the actual access state.
    """
    bind = op.get_bind()
    bind.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE name = :n"),
        {"n": _JOB_NAME},
    )