"""Seed storage.s3.* into platform_settings — S3 upload backend.

Revision ID: 194
Revises: 193
Create Date: 2026-05-20

The asset inventory "记录有误，不属于我" branch (and future self-service
flows) needs durable file upload. We picked S3 (bucket
``helix-prod-uploads-ap-southeast-1`` in account 239023060546,
ap-southeast-1) over a self-hosted volume — prod EC2 already runs in
the same account/region and the bucket is policy-attached to
``helix-prod-ec2-role`` so boto3 picks creds up via the instance
profile (no access key in env).

This migration seeds three platform_settings rows so admins can tweak
the target without code changes:

* ``storage.s3.bucket`` — bucket name
* ``storage.s3.region``  — AWS region
* ``storage.s3.key_prefix`` — namespace prefix prepended to every
  generated object key (default empty for prod; runtime guard in
  ``app.core.storage`` force-overrides to ``"dev/"`` whenever
  ``APP_ENV != "production"`` so a local dev environment running
  against this DB row never writes into the prod top-level prefix).

Idempotent — re-run leaves existing rows untouched.
"""
# ruff: noqa: I001
from __future__ import annotations

import json

import sqlalchemy as sa
from alembic import op

revision = "194"
down_revision = "193"
branch_labels = None
depends_on = None


def _table_exists(bind, name: str) -> bool:
    insp = sa.inspect(bind)
    return name in insp.get_table_names()


def _seed_setting_if_absent(
    bind, *, key: str, value: object, description: str,
) -> None:
    bind.execute(
        sa.text(
            "INSERT INTO platform_settings (`key`, value, description, "
            "created_at, updated_at) "
            "SELECT :k, CAST(:v AS JSON), :d, NOW(), NOW() FROM DUAL "
            "WHERE NOT EXISTS "
            "(SELECT 1 FROM platform_settings WHERE `key` = :k)"
        ),
        {
            "k": key,
            "v": json.dumps(value, ensure_ascii=False),
            "d": description,
        },
    )


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "platform_settings"):
        return

    _seed_setting_if_absent(
        bind,
        key="storage.s3.bucket",
        value="helix-prod-uploads-ap-southeast-1",
        description=(
            "对象存储 bucket 名（用户上传附件用）。"
            "AWS 账号 239023060546，ap-southeast-1。"
            "Prod EC2 通过 instance profile 自动取凭证，无需 access key。"
        ),
    )
    _seed_setting_if_absent(
        bind,
        key="storage.s3.region",
        value="ap-southeast-1",
        description="对象存储 region，必须匹配 bucket 所在 region。",
    )
    _seed_setting_if_absent(
        bind,
        key="storage.s3.key_prefix",
        value="",
        description=(
            "对象 key 顶层前缀（空 = 不加前缀）。"
            "Runtime 守卫：APP_ENV!='production' 时强制覆盖为 'dev/'，"
            "保证本地环境不会写到 prod 同前缀下。"
        ),
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "platform_settings"):
        return
    bind.execute(
        sa.text(
            "DELETE FROM platform_settings WHERE `key` IN "
            "('storage.s3.bucket', 'storage.s3.region', "
            "'storage.s3.key_prefix')"
        )
    )