"""create_product_domain_tables

Revision ID: 004
Revises: 003
Create Date: 2026-05-16 00:00:00.000000

包含：product_categories / product_groups / products / product_pricings /
product_config_options / product_config_option_values / product_custom_fields /
promo_codes 共 8 张表。
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "004"
down_revision: Union[str, None] = "003"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ========== product_categories ==========
    op.create_table(
        "product_categories",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("slug", sa.String(length=64), nullable=False),
        sa.Column("name", sa.String(length=100), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("icon", sa.String(length=64), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("status", sa.String(length=16), nullable=False, server_default="active"),
        sa.Column("is_featured", sa.Boolean(), 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"),
    )
    op.create_index("ix_product_categories_slug", "product_categories", ["slug"], unique=True)

    # ========== product_groups ==========
    op.create_table(
        "product_groups",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("category_id", sa.Integer(), nullable=False),
        sa.Column("slug", sa.String(length=64), nullable=False),
        sa.Column("name", sa.String(length=100), nullable=False),
        sa.Column("headline", sa.String(length=200), nullable=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("icon", sa.String(length=64), 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(["category_id"], ["product_categories.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_product_groups_category_id", "product_groups", ["category_id"])
    op.create_index(
        "ix_product_groups_category_slug",
        "product_groups",
        ["category_id", "slug"],
        unique=True,
    )

    # ========== products ==========
    op.create_table(
        "products",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("group_id", sa.Integer(), nullable=False),
        sa.Column("sku", sa.String(length=64), nullable=False),
        sa.Column("name", sa.String(length=150), nullable=False),
        sa.Column("subtitle", sa.String(length=200), nullable=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("type", sa.String(length=16), nullable=False, server_default="server"),
        sa.Column("status", sa.String(length=16), nullable=False, server_default="draft"),
        sa.Column("cpu", sa.String(length=64), nullable=True),
        sa.Column("memory", sa.String(length=64), nullable=True),
        sa.Column("disk", sa.String(length=64), nullable=True),
        sa.Column("bandwidth", sa.String(length=64), nullable=True),
        sa.Column("traffic", sa.String(length=64), nullable=True),
        sa.Column("location", sa.String(length=64), nullable=True),
        sa.Column("features", sa.Text(), nullable=True),
        sa.Column("badges", sa.Text(), nullable=True),
        sa.Column("base_setup_fee", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("base_price", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("stock_policy", sa.String(length=16), nullable=False, server_default="unlimited"),
        sa.Column("stock", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("min_quantity", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("max_quantity", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("auto_provision", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("trial_enabled", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("trial_days", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("is_featured", sa.Boolean(), 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"], ["product_groups.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_products_sku", "products", ["sku"], unique=True)
    op.create_index("ix_products_group_id", "products", ["group_id"])
    op.create_index("ix_products_type", "products", ["type"])
    op.create_index("ix_products_status", "products", ["status"])

    # ========== product_pricings ==========
    op.create_table(
        "product_pricings",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("product_id", sa.Integer(), nullable=False),
        sa.Column("cycle", sa.String(length=16), nullable=False),
        sa.Column("price", sa.Integer(), nullable=False),
        sa.Column("original_price", sa.Integer(), nullable=True),
        sa.Column("setup_fee", sa.Integer(), 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(["product_id"], ["products.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("product_id", "cycle", name="uq_pricing_product_cycle"),
    )
    op.create_index("ix_product_pricings_product_id", "product_pricings", ["product_id"])

    # ========== product_config_options ==========
    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"])

    # ========== product_config_option_values ==========
    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"],
    )

    # ========== product_custom_fields ==========
    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"])

    # ========== promo_codes ==========
    op.create_table(
        "promo_codes",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("code", sa.String(length=64), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("type", sa.String(length=16), nullable=False),
        sa.Column("value", sa.Integer(), nullable=False),
        sa.Column("scope", sa.String(length=16), nullable=False, server_default="all"),
        sa.Column("scope_ids", sa.Text(), nullable=True),
        sa.Column("starts_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("max_uses", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("max_uses_per_user", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("used_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("applicable_cycles", sa.String(length=255), nullable=True),
        sa.Column("min_order_amount", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("new_users_only", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("applies_to_renewals", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("is_stackable", sa.Boolean(), nullable=False, server_default="0"),
        sa.Column("is_enabled", sa.Boolean(), nullable=False, server_default="1"),
        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"),
    )
    op.create_index("ix_promo_codes_code", "promo_codes", ["code"], unique=True)


def downgrade() -> None:
    op.drop_index("ix_promo_codes_code", table_name="promo_codes")
    op.drop_table("promo_codes")

    op.drop_index("ix_product_custom_fields_product_id", table_name="product_custom_fields")
    op.drop_table("product_custom_fields")

    op.drop_index(
        "ix_product_config_option_values_option_id",
        table_name="product_config_option_values",
    )
    op.drop_table("product_config_option_values")

    op.drop_index("ix_product_config_options_product_id", table_name="product_config_options")
    op.drop_table("product_config_options")

    op.drop_index("ix_product_pricings_product_id", table_name="product_pricings")
    op.drop_table("product_pricings")

    op.drop_index("ix_products_status", table_name="products")
    op.drop_index("ix_products_type", table_name="products")
    op.drop_index("ix_products_group_id", table_name="products")
    op.drop_index("ix_products_sku", table_name="products")
    op.drop_table("products")

    op.drop_index("ix_product_groups_category_slug", table_name="product_groups")
    op.drop_index("ix_product_groups_category_id", table_name="product_groups")
    op.drop_table("product_groups")

    op.drop_index("ix_product_categories_slug", table_name="product_categories")
    op.drop_table("product_categories")
