"""system settings

Revision ID: 041_system_settings
Revises: 040_upstream_action_no_fks
Create Date: 2026-06-17
"""
import json

import sqlalchemy as sa
from alembic import op


revision = "041_system_settings"
down_revision = "040_upstream_action_no_fks"
branch_labels = None
depends_on = None


DEFAULT_SETTINGS = [
    (
        "refund.full_refund_days",
        3,
        "全额退订天数",
        "订单开通后多少天内可全额退订。",
    ),
    (
        "refund.full_refund_limit",
        3,
        "全额退订次数",
        "每个用户最多可使用的全额退订次数。",
    ),
    (
        "lifecycle.expire_suspend_after_days",
        0,
        "到期暂停天数",
        "实例到期后多少天自动暂停，0 表示到期立即暂停。",
    ),
    (
        "lifecycle.expire_delete_after_days",
        3,
        "到期删除天数",
        "实例到期后多少天自动强制删除。",
    ),
    (
        "upstream.task_sync_interval_seconds",
        120,
        "任务同步频率",
        "上游资源操作任务状态同步间隔，单位秒。",
    ),
]


def _index_names(bind, table_name: str) -> set[str]:
    return {index["name"] for index in sa.inspect(bind).get_indexes(table_name)}


def upgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())
    if "system_settings" not in tables:
        op.create_table(
            "system_settings",
            sa.Column("key", sa.String(80), nullable=False),
            sa.Column("value", sa.JSON(), nullable=False),
            sa.Column("label", sa.String(120), nullable=False),
            sa.Column("description", sa.Text(), nullable=True),
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("key", name="uq_system_settings_key"),
        )

    indexes = _index_names(bind, "system_settings")
    if "ix_system_settings_key" not in indexes:
        op.create_index("ix_system_settings_key", "system_settings", ["key"])

    for key, value, label, description in DEFAULT_SETTINGS:
        existing = bind.execute(
            sa.text("SELECT id FROM system_settings WHERE `key` = :key LIMIT 1"),
            {"key": key},
        ).first()
        if existing:
            continue
        bind.execute(
            sa.text(
                """
                INSERT INTO system_settings (`key`, `value`, label, description, created_at, updated_at)
                VALUES (:key, :value, :label, :description, NOW(), NOW())
                """
            ),
            {
                "key": key,
                "value": json.dumps(value),
                "label": label,
                "description": description,
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())
    if "system_settings" in tables:
        op.drop_table("system_settings")
