"""新增素材前测表pre_test_template和素材表resources_material字段

Revision ID: 287c6c064c5d
Revises: 9216bca75ccf
Create Date: 2026-06-16 17:28:20.686564
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy import inspect


# revision identifiers, used by Alembic.
revision: str = "287c6c064c5d"
down_revision: Union[str, None] = "9216bca75ccf"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _inspector():
    return inspect(op.get_bind())


def _table_exists(table_name: str) -> bool:
    return table_name in _inspector().get_table_names(schema="public")


def _column_exists(table_name: str, column_name: str) -> bool:
    if not _table_exists(table_name):
        return False
    columns = _inspector().get_columns(table_name, schema="public")
    return any(column["name"] == column_name for column in columns)


def _index_exists(table_name: str, index_name: str) -> bool:
    if not _table_exists(table_name):
        return False
    indexes = _inspector().get_indexes(table_name, schema="public")
    return any(index["name"] == index_name for index in indexes)


def _create_pre_test_template_table() -> None:
    op.create_table(
        "pre_test_template",
        sa.Column("id", sa.String(length=32), nullable=False, comment="主键"),
        sa.Column("name", sa.String(length=128), nullable=False, comment="模板名称"),
        sa.Column("user_id", sa.String(length=32), nullable=False, comment="用户id"),
        sa.Column("note", sa.Text(), nullable=True, comment="模板备注"),
        sa.Column("platform", sa.String(length=32), nullable=True, comment="投放平台（AD/QIANCHUAN/LOCAL）"),
        sa.Column("external_action", sa.String(length=64), nullable=True, comment="转化目标"),
        sa.Column("cpa_bid", sa.Float(), nullable=True, comment="目标转化成本：[1, 10000]"),
        sa.Column("audience_gender", sa.String(length=16), nullable=True, comment="性别（ALL/MALE/FEMALE）"),
        sa.Column("audience_age", sa.Text(), nullable=True, comment="受众年龄，JSON数组, 格式：[ALL,18-23, 24-30, 31-40, 41-49, 50+]"),
        sa.Column("audience_region", sa.Text(), nullable=True, comment="受众地区，JSON数组（二级行政区域code）"),
        sa.Column("audience_network", sa.Text(), nullable=True, comment="网络类型，JSON数组, 格式：[ALL,5G,4G,3G,2G,WIFI]"),
        sa.Column("cus_name", sa.String(length=256), nullable=True, comment="客户主体名称"),
        sa.Column("pricing_type", sa.String(length=16), nullable=True, comment="出价类型（OCPC/CPA/OCPM）"),
        sa.Column("cost_cap", sa.Boolean(), nullable=True, comment="是否最优成本出价（仅AD支持）"),
        sa.Column("target_cost", sa.Boolean(), nullable=True, comment="是否稳定成本出价（仅AD支持）"),
        sa.Column("nobid", sa.Boolean(), nullable=True, comment="是否最大转化出价（仅AD支持）"),
        sa.Column("cpc_bid", sa.Float(), nullable=True, comment="目标点击成本：[1, 10000]"),
        sa.Column("budget", sa.Float(), nullable=True, comment="预算金额：[1, 10000]"),
        sa.Column("is_default", sa.Boolean(), nullable=True, comment="是否默认模板"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.PrimaryKeyConstraint("id", name="pre_test_template_pkey"),
    )

    if not _index_exists("pre_test_template", "ix_pre_test_template_user_id"):
        op.create_index(
            "ix_pre_test_template_user_id",
            "pre_test_template",
            ["user_id"],
            unique=False,
        )


def _upgrade_existing_pre_test_template_table() -> None:
    if not _column_exists("pre_test_template", "note"):
        op.add_column(
            "pre_test_template",
            sa.Column("note", sa.Text(), nullable=True, comment="模板备注"),
        )

    if _column_exists("pre_test_template", "platform"):
        op.alter_column(
            "pre_test_template",
            "platform",
            existing_type=sa.VARCHAR(length=32),
            nullable=True,
            existing_comment="投放平台（AD/QIANCHUAN/LOCAL）",
        )

    if _column_exists("pre_test_template", "external_action"):
        op.alter_column(
            "pre_test_template",
            "external_action",
            existing_type=sa.VARCHAR(length=64),
            nullable=True,
            existing_comment="转化目标",
        )

    if _column_exists("pre_test_template", "audience_gender"):
        op.alter_column(
            "pre_test_template",
            "audience_gender",
            existing_type=sa.VARCHAR(length=16),
            nullable=True,
            existing_comment="性别（ALL/MALE/FEMALE）",
        )

    if _column_exists("pre_test_template", "audience_age"):
        op.alter_column(
            "pre_test_template",
            "audience_age",
            existing_type=sa.TEXT(),
            comment="受众年龄，JSON数组, 格式：[ALL,18-23, 24-30, 31-40, 41-49, 50+]",
            existing_comment="受众年龄，JSON数组",
            existing_nullable=True,
        )

    if _column_exists("pre_test_template", "audience_network"):
        op.alter_column(
            "pre_test_template",
            "audience_network",
            existing_type=sa.TEXT(),
            comment="网络类型，JSON数组, 格式：[ALL,5G,4G,3G,2G,WIFI]",
            existing_comment="网络类型，JSON数组",
            existing_nullable=True,
        )

    if _column_exists("pre_test_template", "pricing_type"):
        op.alter_column(
            "pre_test_template",
            "pricing_type",
            existing_type=sa.VARCHAR(length=16),
            nullable=True,
            existing_comment="出价类型（OCPC/CPA/OCPM）",
        )

    if _column_exists("pre_test_template", "cost_cap"):
        op.alter_column(
            "pre_test_template",
            "cost_cap",
            existing_type=sa.BOOLEAN(),
            nullable=True,
            existing_comment="是否最优成本出价（仅AD支持）",
        )

    if _column_exists("pre_test_template", "target_cost"):
        op.alter_column(
            "pre_test_template",
            "target_cost",
            existing_type=sa.BOOLEAN(),
            nullable=True,
            existing_comment="是否稳定成本出价（仅AD支持）",
        )

    if _column_exists("pre_test_template", "nobid"):
        op.alter_column(
            "pre_test_template",
            "nobid",
            existing_type=sa.BOOLEAN(),
            nullable=True,
            existing_comment="是否最大转化出价（仅AD支持）",
        )

    if _column_exists("pre_test_template", "is_default"):
        op.alter_column(
            "pre_test_template",
            "is_default",
            existing_type=sa.BOOLEAN(),
            nullable=True,
            existing_comment="是否默认模板",
        )

    if _column_exists("pre_test_template", "status"):
        op.drop_column("pre_test_template", "status")

    if _column_exists("pre_test_template", "description"):
        op.drop_column("pre_test_template", "description")

    if not _index_exists("pre_test_template", "ix_pre_test_template_user_id"):
        op.create_index(
            "ix_pre_test_template_user_id",
            "pre_test_template",
            ["user_id"],
            unique=False,
        )


def upgrade() -> None:
    # pre_test_template 是这版迁移缺失的核心表。
    # 如果不存在，直接按当前模型源码创建完整表。
    # 如果已存在，则按原迁移逻辑补 note、调整 nullable/comment、删除旧字段。
    if not _table_exists("pre_test_template"):
        _create_pre_test_template_table()
    else:
        _upgrade_existing_pre_test_template_table()

    # resources_material 追加字段，按源码迁移逻辑保留，同时增加字段存在判断，避免重复执行报错。
    if _table_exists("resources_material"):
        if not _column_exists("resources_material", "task_id"):
            op.add_column(
                "resources_material",
                sa.Column("task_id", sa.String(length=32), nullable=True, comment="前测任务id"),
            )

        if not _column_exists("resources_material", "note"):
            op.add_column(
                "resources_material",
                sa.Column("note", sa.Text(), nullable=True, comment="前测失败备注或者其他备注"),
            )

        if not _column_exists("resources_material", "status"):
            op.add_column(
                "resources_material",
                sa.Column("status", sa.String(length=16), nullable=True, comment="前测状态（FAILED/PENDING/SUCCESS）"),
            )

        if not _column_exists("resources_material", "pre_result"):
            op.add_column(
                "resources_material",
                sa.Column("pre_result", sa.Text(), nullable=True, comment="前测结果，JSON数组对象"),
            )

        if not _column_exists("resources_material", "pre_test_template_id"):
            op.add_column(
                "resources_material",
                sa.Column("pre_test_template_id", sa.String(length=32), nullable=True, comment="前测模板id"),
            )

        if not _index_exists("resources_material", "ix_resources_material_task_id"):
            op.create_index(
                "ix_resources_material_task_id",
                "resources_material",
                ["task_id"],
                unique=False,
            )


def downgrade() -> None:
    if _table_exists("resources_material"):
        if _index_exists("resources_material", "ix_resources_material_task_id"):
            op.drop_index("ix_resources_material_task_id", table_name="resources_material")

        if _column_exists("resources_material", "pre_test_template_id"):
            op.drop_column("resources_material", "pre_test_template_id")

        if _column_exists("resources_material", "pre_result"):
            op.drop_column("resources_material", "pre_result")

        if _column_exists("resources_material", "status"):
            op.drop_column("resources_material", "status")

        if _column_exists("resources_material", "note"):
            op.drop_column("resources_material", "note")

        if _column_exists("resources_material", "task_id"):
            op.drop_column("resources_material", "task_id")

    # 这一版迁移负责新增 pre_test_template 表，所以回滚时删除该表。
    if _table_exists("pre_test_template"):
        if _index_exists("pre_test_template", "ix_pre_test_template_user_id"):
            op.drop_index("ix_pre_test_template_user_id", table_name="pre_test_template")

        op.drop_table("pre_test_template")