"""config_options_v2

Revision ID: 011
Revises: 010
Create Date: 2026-05-16 00:00:00.000000

砍掉 004 留下来的零调用旧表（product_config_options /
product_config_option_values / product_custom_fields），重建一套照搬 mfcw
的 4 表结构 + 1 张 product 多对多挂载表：

- config_option_groups            全局选项组（独立于产品，可挂多个产品）
- config_options                  组下字段，type ∈ 22 个具名类型
- config_option_values            字段的可选值（dropdown/radio 用）
- config_option_value_pricings    value × cycle × price 矩阵（与 product_pricings 同形）
- product_config_links            products × config_option_groups
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "011"
down_revision: Union[str, None] = "010"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ---- 先 drop 004 留下来的零调用旧表 ----
    # MySQL 不允许单独 drop 被 FK 引用的 index，drop_table 时会一起清掉
    op.drop_table("product_custom_fields")
    op.drop_table("product_config_option_values")
    op.drop_table("product_config_options")

    # ---- config_option_groups ----
    op.create_table(
        "config_option_groups",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("name", sa.String(length=100), nullable=False),
        sa.Column("description", sa.String(length=255), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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"),
    )

    # ---- config_options ----
    op.create_table(
        "config_options",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("group_id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=100), nullable=False, comment="展示名，如 操作系统"),
        sa.Column("key", sa.String(length=64), nullable=False, comment="提交键名 / 上游字段名"),
        sa.Column(
            "type",
            sa.String(length=32),
            nullable=False,
            comment="22 类具名类型，见 ConfigOptionType 枚举",
        ),
        sa.Column(
            "is_required", sa.Boolean(), nullable=False, server_default="0"
        ),
        sa.Column(
            "is_upgradable",
            sa.Boolean(),
            nullable=False,
            server_default="0",
            comment="是否允许升级时改这个字段",
        ),
        # range / quantity 类专用
        sa.Column("min_value", sa.Integer(), nullable=True),
        sa.Column("max_value", sa.Integer(), nullable=True),
        sa.Column("step", sa.Integer(), nullable=True),
        sa.Column("unit", sa.String(length=32), nullable=True, comment="GB / Mbps / 个"),
        sa.Column("description", sa.String(length=500), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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.ForeignKeyConstraint(["group_id"], ["config_option_groups.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_config_options_group_id", "config_options", ["group_id"])

    # ---- config_option_values ----
    op.create_table(
        "config_option_values",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("option_id", sa.Integer(), nullable=False),
        sa.Column("label", sa.String(length=150), nullable=False, comment="展示文本"),
        sa.Column("value", sa.String(length=255), nullable=False, comment="提交值 / 上游 ID"),
        sa.Column(
            "parent_value_id",
            sa.Integer(),
            nullable=True,
            comment="层级联动场景下指向上一级 value",
        ),
        sa.Column("is_default", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("is_enabled", sa.Boolean(), nullable=False, server_default="1"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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.ForeignKeyConstraint(["option_id"], ["config_options.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(
            ["parent_value_id"], ["config_option_values.id"], ondelete="SET NULL"
        ),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_config_option_values_option_id", "config_option_values", ["option_id"])

    # ---- config_option_value_pricings ----
    op.create_table(
        "config_option_value_pricings",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("value_id", sa.Integer(), nullable=False),
        sa.Column("cycle", sa.String(length=16), nullable=False),
        sa.Column("price", sa.Integer(), nullable=False, server_default="0", comment="加价(分)"),
        sa.Column(
            "setup_fee", sa.Integer(), nullable=False, server_default="0", comment="额外安装费(分)"
        ),
        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.ForeignKeyConstraint(["value_id"], ["config_option_values.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("value_id", "cycle", name="uq_value_cycle"),
    )
    op.create_index(
        "ix_config_option_value_pricings_value_id",
        "config_option_value_pricings",
        ["value_id"],
    )

    # ---- product_config_links ----
    op.create_table(
        "product_config_links",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("product_id", sa.Integer(), nullable=False),
        sa.Column("group_id", sa.Integer(), nullable=False),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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.ForeignKeyConstraint(["product_id"], ["products.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["group_id"], ["config_option_groups.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("product_id", "group_id", name="uq_product_group_link"),
    )
    op.create_index("ix_product_config_links_product_id", "product_config_links", ["product_id"])
    op.create_index("ix_product_config_links_group_id", "product_config_links", ["group_id"])


def downgrade() -> None:
    op.drop_index("ix_product_config_links_group_id", table_name="product_config_links")
    op.drop_index("ix_product_config_links_product_id", table_name="product_config_links")
    op.drop_table("product_config_links")

    op.drop_index(
        "ix_config_option_value_pricings_value_id", table_name="config_option_value_pricings"
    )
    op.drop_table("config_option_value_pricings")

    op.drop_index("ix_config_option_values_option_id", table_name="config_option_values")
    op.drop_table("config_option_values")

    op.drop_index("ix_config_options_group_id", table_name="config_options")
    op.drop_table("config_options")

    op.drop_table("config_option_groups")

    # 重建 004 旧表（仅基础列，让 downgrade 不留空）
    op.create_table(
        "product_config_options",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("product_id", sa.Integer(), nullable=False),
        sa.Column("key", sa.String(length=64), nullable=False),
        sa.Column("label", sa.String(length=100), nullable=False),
        sa.Column("description", sa.String(length=500), nullable=True),
        sa.Column("type", sa.String(length=16), nullable=False),
        sa.Column("price_mode", sa.String(length=16), nullable=False, server_default="fixed"),
        sa.Column("min_value", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("max_value", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("step", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("default_value", sa.String(length=255), nullable=True),
        sa.Column("unit", sa.String(length=32), nullable=True),
        sa.Column("is_required", sa.Boolean(), nullable=False, server_default="1"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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.ForeignKeyConstraint(["product_id"], ["products.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        "ix_product_config_options_product_id", "product_config_options", ["product_id"]
    )

    op.create_table(
        "product_config_option_values",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("option_id", sa.Integer(), nullable=False),
        sa.Column("value", sa.String(length=100), nullable=False),
        sa.Column("label", sa.String(length=150), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("price_delta", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("setup_fee_delta", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("is_default", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("is_enabled", sa.Boolean(), nullable=False, server_default="1"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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.ForeignKeyConstraint(
            ["option_id"], ["product_config_options.id"], ondelete="CASCADE"
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("option_id", "value", name="uq_option_value"),
    )
    op.create_index(
        "ix_product_config_option_values_option_id",
        "product_config_option_values",
        ["option_id"],
    )

    op.create_table(
        "product_custom_fields",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("product_id", sa.Integer(), nullable=False),
        sa.Column("key", sa.String(length=64), nullable=False),
        sa.Column("label", sa.String(length=100), nullable=False),
        sa.Column("description", sa.String(length=500), nullable=True),
        sa.Column("placeholder", sa.String(length=200), nullable=True),
        sa.Column("type", sa.String(length=16), nullable=False),
        sa.Column("options", sa.Text(), nullable=True),
        sa.Column("default_value", sa.String(length=500), nullable=True),
        sa.Column("validation_regex", sa.String(length=255), nullable=True),
        sa.Column("validation_message", sa.String(length=255), nullable=True),
        sa.Column("min_length", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("max_length", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("is_required", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("is_admin_only", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("show_on_invoice", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        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.ForeignKeyConstraint(["product_id"], ["products.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(
        "ix_product_custom_fields_product_id", "product_custom_fields", ["product_id"]
    )
