"""create orders and instances tables

Revision ID: 018_orders_and_instances
Revises: 017_balance_logs_unique_ref
Create Date: 2026-05-18
"""

from alembic import op
import sqlalchemy as sa

revision = "018_orders_and_instances"
down_revision = "017_balance_logs_unique_ref"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "orders",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        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.Column("user_id", sa.BigInteger(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("out_order_no", sa.String(64), nullable=False),
        sa.Column(
            "status",
            sa.Enum("pending", "paid", "cancelled", "refunded", name="orderstatus"),
            nullable=False,
            server_default="paid",
        ),
        sa.Column("total_amount", sa.Integer(), nullable=False),
        sa.Column("discount_amount", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("promo_code_id", sa.Integer(), sa.ForeignKey("promo_codes.id", ondelete="SET NULL"), nullable=True),
        sa.Column("items_snapshot", sa.JSON(), nullable=True),
        sa.Column("remark", sa.Text(), nullable=True),
    )
    op.create_index("ix_orders_user_id", "orders", ["user_id"])
    op.create_index("ix_orders_out_order_no", "orders", ["out_order_no"], unique=True)
    op.create_index("ix_orders_status", "orders", ["status"])

    op.create_table(
        "instances",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        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.Column("user_id", sa.BigInteger(), sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("order_id", sa.Integer(), sa.ForeignKey("orders.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("product_id", sa.Integer(), sa.ForeignKey("products.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("cloud_server_id", sa.Integer(), sa.ForeignKey("cloud_servers.id", ondelete="SET NULL"), nullable=True),
        # 业务状态
        sa.Column(
            "status",
            sa.Enum("provisioning", "active", "suspended", "terminated", "failed", name="instancestatus"),
            nullable=False,
            server_default="provisioning",
        ),
        sa.Column(
            "suspend_type",
            sa.Enum("traffic", "due", "manual", name="suspendtype"),
            nullable=True,
        ),
        sa.Column("is_locked", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("internet", sa.Integer(), nullable=False, server_default="1"),
        # 计费
        sa.Column("cycle", sa.String(16), nullable=False),
        sa.Column("unit_price", sa.Integer(), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("auto_renew", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("next_renew_amount", sa.Integer(), nullable=False, server_default="0"),
        # 用户自定义
        sa.Column("alias", sa.String(64), nullable=True),
        sa.Column("hostname", sa.String(64), nullable=True),
        sa.Column("os_image", sa.String(128), nullable=True),
        # 硬件规格
        sa.Column("cpu", sa.Integer(), nullable=True),
        sa.Column("memory", sa.Integer(), nullable=True),
        sa.Column("system_disk_size", sa.Integer(), nullable=True),
        sa.Column("data_disk_size", sa.Integer(), nullable=True),
        sa.Column("in_bw", sa.Integer(), nullable=True),
        sa.Column("out_bw", sa.Integer(), nullable=True),
        sa.Column("ip_num", sa.Integer(), nullable=True),
        sa.Column("nat_acl_limit", sa.Integer(), nullable=True),
        sa.Column("traffic_quota", sa.Integer(), nullable=True),
        sa.Column("traffic_type", sa.Integer(), nullable=True),
        # 魔方云上游
        sa.Column("upstream_id", sa.Integer(), nullable=True),
        sa.Column("upstream_task_id", sa.Integer(), nullable=True),
        sa.Column("main_ip", sa.String(64), nullable=True),
        sa.Column("root_password", sa.String(512), nullable=True),
        sa.Column("ssh_port", sa.Integer(), nullable=True),
        # 开通追踪
        sa.Column("provision_attempts", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("last_provision_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("provision_log", sa.Text(), nullable=True),
        # 时间
        sa.Column("terminated_at", sa.DateTime(timezone=True), nullable=True),
    )
    op.create_index("ix_instances_user_id", "instances", ["user_id"])
    op.create_index("ix_instances_order_id", "instances", ["order_id"])
    op.create_index("ix_instances_product_id", "instances", ["product_id"])
    op.create_index("ix_instances_cloud_server_id", "instances", ["cloud_server_id"])
    op.create_index("ix_instances_status", "instances", ["status"])
    op.create_index("ix_instances_upstream_id", "instances", ["upstream_id"])
    op.create_index("ix_instances_expires_at", "instances", ["expires_at"])


def downgrade() -> None:
    op.drop_index("ix_instances_expires_at", table_name="instances")
    op.drop_index("ix_instances_upstream_id", table_name="instances")
    op.drop_index("ix_instances_status", table_name="instances")
    op.drop_index("ix_instances_cloud_server_id", table_name="instances")
    op.drop_index("ix_instances_product_id", table_name="instances")
    op.drop_index("ix_instances_order_id", table_name="instances")
    op.drop_index("ix_instances_user_id", table_name="instances")
    op.drop_table("instances")

    op.drop_index("ix_orders_status", table_name="orders")
    op.drop_index("ix_orders_out_order_no", table_name="orders")
    op.drop_index("ix_orders_user_id", table_name="orders")
    op.drop_table("orders")

    op.drop_enum("instancestatus")
    op.drop_enum("suspendtype")
    op.drop_enum("orderstatus")
