"""initial schema

Revision ID: 0001
Revises:
Create Date: 2025-01-01
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

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

UUID = postgresql.UUID(as_uuid=True)
JSONB = postgresql.JSONB(astext_type=sa.Text())


def _common() -> list[sa.Column]:
    return [
        sa.Column("id", UUID, primary_key=True, nullable=False),
        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),
    ]


def upgrade() -> None:
    op.create_table(
        "users",
        *_common(),
        sa.Column("telegram_id", sa.BigInteger(), nullable=False),
        sa.Column("username", sa.String(64)),
        sa.Column("first_name", sa.String(128)),
        sa.Column("last_name", sa.String(128)),
        sa.Column("language", sa.String(8), nullable=False, server_default="fa"),
        sa.Column("is_admin", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("is_blocked", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("note", sa.Text()),
        sa.Column("referral_code", sa.String(16), nullable=False),
        sa.Column("referred_by", UUID, sa.ForeignKey("users.id", ondelete="SET NULL")),
    )
    op.create_index("ix_users_telegram_id", "users", ["telegram_id"], unique=True)
    op.create_index("ix_users_username", "users", ["username"])
    op.create_index("ix_users_referral_code", "users", ["referral_code"], unique=True)
    op.create_index("ix_users_referred_by", "users", ["referred_by"])
    op.create_index("ix_users_created_at", "users", ["created_at"])

    op.create_table(
        "wallets",
        *_common(),
        sa.Column("user_id", UUID, sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False, unique=True),
        sa.Column("balance", sa.Numeric(18, 2), nullable=False, server_default="0"),
        sa.Column("locked_balance", sa.Numeric(18, 2), nullable=False, server_default="0"),
    )
    op.create_index("ix_wallets_created_at", "wallets", ["created_at"])

    op.create_table(
        "admins",
        *_common(),
        sa.Column("username", sa.String(64), nullable=False),
        sa.Column("password_hash", sa.String(255), nullable=False),
        sa.Column("role", sa.String(32), nullable=False, server_default="owner"),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
    )
    op.create_index("ix_admins_username", "admins", ["username"], unique=True)
    op.create_index("ix_admins_created_at", "admins", ["created_at"])

    op.create_table(
        "categories",
        *_common(),
        sa.Column("title", sa.String(128), nullable=False),
        sa.Column("slug", sa.String(64), nullable=False),
        sa.Column("icon", sa.String(16)),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
    )
    op.create_index("ix_categories_slug", "categories", ["slug"], unique=True)
    op.create_index("ix_categories_enabled", "categories", ["enabled"])
    op.create_index("ix_categories_created_at", "categories", ["created_at"])

    op.create_table(
        "products",
        *_common(),
        sa.Column("category_id", UUID, sa.ForeignKey("categories.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("title", sa.String(160), nullable=False),
        sa.Column("slug", sa.String(96), nullable=False),
        sa.Column("description", sa.Text()),
        sa.Column("price", sa.Numeric(18, 2), nullable=False),
        sa.Column("discount_price", sa.Numeric(18, 2)),
        sa.Column("currency", sa.String(8), nullable=False, server_default="IRT"),
        sa.Column("flow", sa.String(32), nullable=False),
        sa.Column("icon", sa.String(16)),
        sa.Column("image", sa.String(255)),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("data", JSONB, nullable=False, server_default="{}"),
    )
    op.create_index("ix_products_slug", "products", ["slug"], unique=True)
    op.create_index("ix_products_category_id", "products", ["category_id"])
    op.create_index("ix_products_flow", "products", ["flow"])
    op.create_index("ix_products_enabled", "products", ["enabled"])
    op.create_index("ix_products_created_at", "products", ["created_at"])

    op.create_table(
        "coupons",
        *_common(),
        sa.Column("code", sa.String(32), nullable=False),
        sa.Column("type", sa.String(16), nullable=False, server_default="percent"),
        sa.Column("value", sa.Numeric(18, 2), nullable=False),
        sa.Column("max_discount", sa.Numeric(18, 2)),
        sa.Column("minimum_order", sa.Numeric(18, 2), nullable=False, server_default="0"),
        sa.Column("usage_limit", sa.Integer()),
        sa.Column("per_user_limit", sa.Integer()),
        sa.Column("used_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("expires_at", sa.DateTime(timezone=True)),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
    )
    op.create_index("ix_coupons_code", "coupons", ["code"], unique=True)
    op.create_index("ix_coupons_created_at", "coupons", ["created_at"])

    op.create_table(
        "orders",
        *_common(),
        sa.Column("order_number", sa.String(24), nullable=False),
        sa.Column("user_id", UUID, sa.ForeignKey("users.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("product_id", UUID, sa.ForeignKey("products.id", ondelete="SET NULL")),
        sa.Column("coupon_id", UUID, sa.ForeignKey("coupons.id", ondelete="SET NULL")),
        sa.Column("status", sa.String(24), nullable=False, server_default="draft"),
        sa.Column("payment_status", sa.String(24), nullable=False, server_default="pending"),
        sa.Column("payment_method", sa.String(16)),
        sa.Column("quantity", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("price", sa.Numeric(18, 2), nullable=False, server_default="0"),
        sa.Column("discount", sa.Numeric(18, 2), nullable=False, server_default="0"),
        sa.Column("final_price", sa.Numeric(18, 2), nullable=False, server_default="0"),
        sa.Column("currency", sa.String(8), nullable=False, server_default="IRT"),
        sa.Column("input_data", JSONB, nullable=False, server_default="{}"),
        sa.Column("product_snapshot", JSONB, nullable=False, server_default="{}"),
        sa.Column("result_data", JSONB, nullable=False, server_default="{}"),
        sa.Column("error_message", sa.Text()),
        sa.Column("completed_at", sa.DateTime(timezone=True)),
    )
    op.create_index("ix_orders_order_number", "orders", ["order_number"], unique=True)
    op.create_index("ix_orders_user_id", "orders", ["user_id"])
    op.create_index("ix_orders_product_id", "orders", ["product_id"])
    op.create_index("ix_orders_status", "orders", ["status"])
    op.create_index("ix_orders_payment_status", "orders", ["payment_status"])
    op.create_index("ix_orders_created_at", "orders", ["created_at"])

    op.create_table(
        "payments",
        *_common(),
        sa.Column("order_id", UUID, sa.ForeignKey("orders.id", ondelete="CASCADE"), nullable=False),
        sa.Column("method", sa.String(16), nullable=False),
        sa.Column("status", sa.String(24), nullable=False, server_default="pending"),
        sa.Column("amount", sa.Numeric(18, 2), nullable=False),
        sa.Column("currency", sa.String(8), nullable=False, server_default="IRT"),
        sa.Column("original_amount", sa.Numeric(24, 6)),
        sa.Column("payable_amount", sa.Numeric(24, 6)),
        sa.Column("pay_currency", sa.String(8)),
        sa.Column("wallet_address", sa.String(128)),
        sa.Column("tx_hash", sa.String(128), unique=True),
        sa.Column("receipt_image", sa.String(255)),
        sa.Column("expires_at", sa.DateTime(timezone=True)),
        sa.Column("paid_at", sa.DateTime(timezone=True)),
        sa.Column("note", sa.Text()),
    )
    op.create_index("ix_payments_order_id", "payments", ["order_id"])
    op.create_index("ix_payments_method", "payments", ["method"])
    op.create_index("ix_payments_status", "payments", ["status"])
    op.create_index("ix_payments_payable_amount", "payments", ["payable_amount"])
    op.create_index("ix_payments_expires_at", "payments", ["expires_at"])
    op.create_index("ix_payments_created_at", "payments", ["created_at"])
    # Two live TRON invoices can never share a payable amount; paid ones keep theirs forever.
    op.execute(
        "CREATE UNIQUE INDEX uq_payments_tron_payable_amount ON payments (payable_amount) "
        "WHERE method = 'tron' AND status IN ('pending', 'paid')"
    )

    op.create_table(
        "transactions",
        *_common(),
        sa.Column("wallet_id", UUID, sa.ForeignKey("wallets.id", ondelete="CASCADE"), nullable=False),
        sa.Column("type", sa.String(16), nullable=False),
        sa.Column("amount", sa.Numeric(18, 2), nullable=False),
        sa.Column("balance_after", sa.Numeric(18, 2), nullable=False),
        sa.Column("description", sa.Text()),
        sa.Column("reference", sa.String(64)),
        sa.Column("order_id", UUID, sa.ForeignKey("orders.id", ondelete="SET NULL")),
    )
    op.create_index("ix_transactions_wallet_id", "transactions", ["wallet_id"])
    op.create_index("ix_transactions_type", "transactions", ["type"])
    op.create_index("ix_transactions_reference", "transactions", ["reference"])
    op.create_index("ix_transactions_order_id", "transactions", ["order_id"])
    op.create_index("ix_transactions_created_at", "transactions", ["created_at"])

    op.create_table(
        "referrals",
        *_common(),
        sa.Column("referrer_user_id", UUID, sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("referred_user_id", UUID, sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("order_id", UUID, sa.ForeignKey("orders.id", ondelete="SET NULL")),
        sa.Column("reward", sa.Numeric(18, 2), nullable=False, server_default="0"),
        sa.Column("status", sa.String(16), nullable=False, server_default="pending"),
    )
    op.create_index("ix_referrals_referrer_user_id", "referrals", ["referrer_user_id"])
    op.create_index("ix_referrals_referred_user_id", "referrals", ["referred_user_id"])
    op.create_index("ix_referrals_order_id", "referrals", ["order_id"])
    op.create_index("ix_referrals_status", "referrals", ["status"])
    op.create_index("ix_referrals_created_at", "referrals", ["created_at"])

    op.create_table(
        "bank_cards",
        *_common(),
        sa.Column("user_id", UUID, sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("title", sa.String(128)),
        sa.Column("card_number", sa.String(32)),
        sa.Column("image", sa.String(255)),
        sa.Column("status", sa.String(16), nullable=False, server_default="pending"),
    )
    op.create_index("ix_bank_cards_user_id", "bank_cards", ["user_id"])
    op.create_index("ix_bank_cards_status", "bank_cards", ["status"])
    op.create_index("ix_bank_cards_created_at", "bank_cards", ["created_at"])

    op.create_table(
        "support_tickets",
        *_common(),
        sa.Column("user_id", UUID, sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False),
        sa.Column("subject", sa.String(160)),
        sa.Column("message", sa.Text(), nullable=False),
        sa.Column("reply", sa.Text()),
        sa.Column("attachment", sa.String(255)),
        sa.Column("status", sa.String(16), nullable=False, server_default="open"),
    )
    op.create_index("ix_support_tickets_user_id", "support_tickets", ["user_id"])
    op.create_index("ix_support_tickets_status", "support_tickets", ["status"])
    op.create_index("ix_support_tickets_created_at", "support_tickets", ["created_at"])

    op.create_table(
        "settings",
        *_common(),
        sa.Column("key", sa.String(64), nullable=False),
        sa.Column("value", JSONB, nullable=False),
        sa.Column("title", sa.String(160)),
        sa.Column("is_public", sa.Boolean(), nullable=False, server_default=sa.false()),
    )
    op.create_index("ix_settings_key", "settings", ["key"], unique=True)
    op.create_index("ix_settings_created_at", "settings", ["created_at"])

    op.create_table(
        "logs",
        *_common(),
        sa.Column("type", sa.String(32), nullable=False),
        sa.Column("event", sa.String(64), nullable=False),
        sa.Column("message", sa.Text()),
        sa.Column("user_id", UUID, sa.ForeignKey("users.id", ondelete="SET NULL")),
        sa.Column("order_id", UUID, sa.ForeignKey("orders.id", ondelete="SET NULL")),
        sa.Column("payment_id", UUID, sa.ForeignKey("payments.id", ondelete="SET NULL")),
        sa.Column("data", JSONB, nullable=False, server_default="{}"),
    )
    op.create_index("ix_logs_type", "logs", ["type"])
    op.create_index("ix_logs_event", "logs", ["event"])
    op.create_index("ix_logs_user_id", "logs", ["user_id"])
    op.create_index("ix_logs_order_id", "logs", ["order_id"])
    op.create_index("ix_logs_payment_id", "logs", ["payment_id"])
    op.create_index("ix_logs_created_at", "logs", ["created_at"])


def downgrade() -> None:
    op.drop_table("logs")
    op.drop_table("settings")
    op.drop_table("support_tickets")
    op.drop_table("bank_cards")
    op.drop_table("referrals")
    op.drop_table("transactions")
    op.execute("DROP INDEX IF EXISTS uq_payments_tron_payable_amount")
    op.drop_table("payments")
    op.drop_table("orders")
    op.drop_table("coupons")
    op.drop_table("products")
    op.drop_table("categories")
    op.drop_table("admins")
    op.drop_table("wallets")
    op.drop_table("users")