"""add performance indexes

쿼리 성능 향상을 위한 복합 인덱스 추가.
자주 사용되는 필터 및 조회 패턴에 맞춰 인덱스를 생성합니다.

Revision ID: 0002
Revises: 0001
Create Date: 2026-03-10

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

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


def _safe_create_index(table, index_name, columns):
    """인덱스가 이미 존재하면 스킵."""
    conn = op.get_bind()
    result = conn.execute(sa.text(
        "SELECT 1 FROM pg_indexes WHERE tablename=:t AND indexname=:i"
    ), {"t": table, "i": index_name})
    if not result.fetchone():
        op.create_index(index_name, table, columns)


def upgrade() -> None:
    _safe_create_index("trade_history", "ix_trade_history_status_created_at", ["status", "created_at"])
    _safe_create_index("trade_history", "ix_trade_history_stock_order_created", ["stock_code", "order_type", "created_at"])
    _safe_create_index("market_data", "ix_market_data_stock_code_timestamp", ["stock_code", "timestamp"])
    _safe_create_index("stock_analysis", "ix_stock_analysis_stock_code_analysis_date", ["stock_code", "analysis_date"])
    _safe_create_index("portfolio", "ix_portfolio_quantity", ["quantity"])
    _safe_create_index("active_trades", "ix_active_trades_stock_code_status", ["stock_code", "status"])
    _safe_create_index("active_trades", "ix_active_trades_status", ["status"])
    _safe_create_index("watchlist_stocks", "ix_watchlist_stocks_is_active_stock_code", ["is_active", "stock_code"])
    _safe_create_index("claude_analysis_log", "ix_claude_analysis_log_stock_code_created_at", ["stock_code", "created_at"])


def downgrade() -> None:
    # ── claude_analysis_log ───────────────────────────────────────────────
    with op.batch_alter_table("claude_analysis_log", schema=None) as batch_op:
        batch_op.drop_index("ix_claude_analysis_log_stock_code_created_at")

    # ── watchlist_stocks ──────────────────────────────────────────────────
    with op.batch_alter_table("watchlist_stocks", schema=None) as batch_op:
        batch_op.drop_index("ix_watchlist_stocks_is_active_stock_code")

    # ── active_trades ─────────────────────────────────────────────────────
    with op.batch_alter_table("active_trades", schema=None) as batch_op:
        batch_op.drop_index("ix_active_trades_status")
        batch_op.drop_index("ix_active_trades_stock_code_status")

    # ── portfolio ─────────────────────────────────────────────────────────
    with op.batch_alter_table("portfolio", schema=None) as batch_op:
        batch_op.drop_index("ix_portfolio_quantity")

    # ── stock_analysis ────────────────────────────────────────────────────
    with op.batch_alter_table("stock_analysis", schema=None) as batch_op:
        batch_op.drop_index("ix_stock_analysis_stock_code_analysis_date")

    # ── market_data ───────────────────────────────────────────────────────
    with op.batch_alter_table("market_data", schema=None) as batch_op:
        batch_op.drop_index("ix_market_data_stock_code_timestamp")

    # ── trade_history ─────────────────────────────────────────────────────
    with op.batch_alter_table("trade_history", schema=None) as batch_op:
        batch_op.drop_index("ix_trade_history_stock_order_created")
        batch_op.drop_index("ix_trade_history_status_created_at")