"""Add charging tables

Revision ID: 004_charging
Revises: 003_energy_categories
Create Date: 2026-04-03
"""
from alembic import op
import sqlalchemy as sa

revision = "004_charging"
down_revision = "003_energy_categories"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # --- charging_merchants ---
    op.create_table(
        "charging_merchants",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("contact_person", sa.String(100)),
        sa.Column("phone", sa.String(20)),
        sa.Column("email", sa.String(100)),
        sa.Column("address", sa.String(500)),
        sa.Column("business_license", sa.String(100)),
        sa.Column("status", sa.String(20), default="active"),
        sa.Column("settlement_type", sa.String(20)),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- charging_brands ---
    op.create_table(
        "charging_brands",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("brand_name", sa.String(100), nullable=False),
        sa.Column("logo_url", sa.String(500)),
        sa.Column("country", sa.String(50)),
        sa.Column("description", sa.Text),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- charging_stations ---
    op.create_table(
        "charging_stations",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("name", sa.String(200), nullable=False),
        sa.Column("merchant_id", sa.Integer, sa.ForeignKey("charging_merchants.id")),
        sa.Column("type", sa.String(50)),
        sa.Column("address", sa.String(500)),
        sa.Column("latitude", sa.Float),
        sa.Column("longitude", sa.Float),
        sa.Column("price", sa.Float),
        sa.Column("activity", sa.Text),
        sa.Column("status", sa.String(20), default="active"),
        sa.Column("total_piles", sa.Integer, default=0),
        sa.Column("available_piles", sa.Integer, default=0),
        sa.Column("total_power_kw", sa.Float, default=0),
        sa.Column("photo_url", sa.String(500)),
        sa.Column("operating_hours", sa.String(100)),
        sa.Column("created_by", sa.Integer, sa.ForeignKey("users.id")),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- charging_piles ---
    op.create_table(
        "charging_piles",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("station_id", sa.Integer, sa.ForeignKey("charging_stations.id"), nullable=False),
        sa.Column("encoding", sa.String(100), unique=True),
        sa.Column("name", sa.String(200)),
        sa.Column("type", sa.String(50)),
        sa.Column("brand", sa.String(100)),
        sa.Column("model", sa.String(100)),
        sa.Column("rated_power_kw", sa.Float),
        sa.Column("connector_type", sa.String(50)),
        sa.Column("status", sa.String(20), default="active"),
        sa.Column("work_status", sa.String(20), default="offline"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- charging_price_strategies ---
    op.create_table(
        "charging_price_strategies",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("strategy_name", sa.String(200), nullable=False),
        sa.Column("station_id", sa.Integer, sa.ForeignKey("charging_stations.id")),
        sa.Column("bill_model", sa.String(20)),
        sa.Column("description", sa.Text),
        sa.Column("status", sa.String(20), default="inactive"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- charging_price_params ---
    op.create_table(
        "charging_price_params",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("strategy_id", sa.Integer, sa.ForeignKey("charging_price_strategies.id"), nullable=False),
        sa.Column("start_time", sa.String(10), nullable=False),
        sa.Column("end_time", sa.String(10), nullable=False),
        sa.Column("period_mark", sa.String(20)),
        sa.Column("elec_price", sa.Float, nullable=False),
        sa.Column("service_price", sa.Float, default=0),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- charging_orders ---
    op.create_table(
        "charging_orders",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("order_no", sa.String(50), unique=True, nullable=False),
        sa.Column("user_id", sa.Integer),
        sa.Column("user_name", sa.String(100)),
        sa.Column("phone", sa.String(20)),
        sa.Column("station_id", sa.Integer, sa.ForeignKey("charging_stations.id")),
        sa.Column("station_name", sa.String(200)),
        sa.Column("pile_id", sa.Integer, sa.ForeignKey("charging_piles.id")),
        sa.Column("pile_name", sa.String(200)),
        sa.Column("start_time", sa.DateTime(timezone=True)),
        sa.Column("end_time", sa.DateTime(timezone=True)),
        sa.Column("car_no", sa.String(20)),
        sa.Column("car_vin", sa.String(50)),
        sa.Column("charge_method", sa.String(20)),
        sa.Column("settle_type", sa.String(20)),
        sa.Column("pay_type", sa.String(20)),
        sa.Column("settle_time", sa.DateTime(timezone=True)),
        sa.Column("settle_price", sa.Float),
        sa.Column("paid_price", sa.Float),
        sa.Column("discount_amt", sa.Float, default=0),
        sa.Column("elec_amt", sa.Float),
        sa.Column("serve_amt", sa.Float),
        sa.Column("order_status", sa.String(20), default="charging"),
        sa.Column("charge_duration", sa.Integer),
        sa.Column("energy", sa.Float),
        sa.Column("start_soc", sa.Float),
        sa.Column("end_soc", sa.Float),
        sa.Column("abno_cause", sa.Text),
        sa.Column("order_source", sa.String(20)),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    # --- occupancy_orders ---
    op.create_table(
        "occupancy_orders",
        sa.Column("id", sa.Integer, primary_key=True, autoincrement=True),
        sa.Column("order_id", sa.Integer, sa.ForeignKey("charging_orders.id")),
        sa.Column("pile_id", sa.Integer, sa.ForeignKey("charging_piles.id")),
        sa.Column("start_time", sa.DateTime(timezone=True)),
        sa.Column("end_time", sa.DateTime(timezone=True)),
        sa.Column("occupancy_fee", sa.Float, default=0),
        sa.Column("status", sa.String(20), default="active"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )


def downgrade() -> None:
    op.drop_table("occupancy_orders")
    op.drop_table("charging_orders")
    op.drop_table("charging_price_params")
    op.drop_table("charging_price_strategies")
    op.drop_table("charging_piles")
    op.drop_table("charging_stations")
    op.drop_table("charging_brands")
    op.drop_table("charging_merchants")