import logging
from dataclasses import dataclass
from typing import Optional, List

from market import Market

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

ARBITRAGE_CANDIDATE_LIMIT = 30_000
ARBITRAGE_TYPE_LIMIT = 250
ARBITRAGE_STATION_PAIR_LIMIT = 40


@dataclass(frozen=True)
class ShortlistConfig:
    candidate_limit: int = ARBITRAGE_CANDIDATE_LIMIT
    type_limit: int = ARBITRAGE_TYPE_LIMIT
    station_pair_limit: int = ARBITRAGE_STATION_PAIR_LIMIT
    rank_mode: str = "and"

@dataclass
class Trade:
    from_station: int
    to_station: int
    from_order_id: int
    to_order_id: int
    item_name: str
    type_id: int
    item_cargo_volume: float
    from_price: float
    to_price: float
    quantity: int
    cargo: float
    gross_profit: float
    net_profit: Optional[float] = None

async def arbitrage(
    market: Market,
    cargo_space: int,
    tax_rate: float,
    profit_threshold: float,
    max_capital: float,
    shortlist: Optional[ShortlistConfig] = None,
) -> List[Trade]:
    if not market.db_conn:
        return []

    shortlist = shortlist or ShortlistConfig()
    if shortlist.rank_mode not in {"and", "or"}:
        raise ValueError(f"Unsupported shortlist rank mode: {shortlist.rank_mode}")

    rank_operator = "AND" if shortlist.rank_mode == "and" else "OR"
    
    query = f"""
    WITH buy_orders AS (
        SELECT * FROM orders WHERE is_buy_order = 1
    ),
    sell_orders AS (
        SELECT * FROM orders WHERE is_buy_order = 0
    ),
    raw_trades AS (
        SELECT 
            s.location_id as from_station,
            b.location_id as to_station,
            s.order_id as from_order_id,
            b.order_id as to_order_id,
            s.item_name,
            s.type_id,
            s.item_cargo_volume,
            s.price as from_price,
            b.price as to_price,
            MIN(
                s.volume_remain, 
                b.volume_remain,
                CAST({cargo_space} / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
                CAST({max_capital} / NULLIF(s.price, 0) AS INTEGER),
                CAST({max_capital} / NULLIF(b.price, 0) AS INTEGER)
            ) as quantity
        FROM sell_orders s
        JOIN buy_orders b ON s.type_id = b.type_id
        WHERE 
            b.price * (1 - {tax_rate}) >= s.price
            AND s.item_cargo_volume > 0
            AND s.price > 0 AND b.price > 0
    ),
    candidate_trades AS (
        SELECT
            *,
            quantity * (to_price * (1 - {tax_rate}) - from_price) as gross_profit,
            quantity * item_cargo_volume as cargo,
            (quantity * (to_price * (1 - {tax_rate}) - from_price)) / NULLIF(quantity * item_cargo_volume, 0) as profit_density,
            (quantity * (to_price * (1 - {tax_rate}) - from_price)) / NULLIF(quantity * from_price, 0) as roi
        FROM raw_trades
        WHERE quantity > 0 AND quantity * (to_price * (1 - {tax_rate}) - from_price) >= {profit_threshold}
    ),
    ranked_trades AS (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY type_id
                ORDER BY gross_profit DESC, roi DESC, profit_density DESC
            ) as type_rank,
            ROW_NUMBER() OVER (
                PARTITION BY from_station, to_station
                ORDER BY gross_profit DESC, roi DESC, profit_density DESC
            ) as station_pair_rank
        FROM candidate_trades
    )
    SELECT
        from_station,
        to_station,
        from_order_id,
        to_order_id,
        item_name,
        type_id,
        item_cargo_volume,
        from_price,
        to_price,
        quantity,
        cargo,
        gross_profit
    FROM ranked_trades
        WHERE (type_rank <= {shortlist.type_limit}
            {rank_operator} station_pair_rank <= {shortlist.station_pair_limit})
    ORDER BY gross_profit DESC, roi DESC, profit_density DESC
        LIMIT {shortlist.candidate_limit}
    """

    trades = []
    async with market.db_conn.execute(query) as cursor:
        async for row in cursor:
            from_station, to_station, from_order_id, to_order_id, item_name, type_id, item_cargo_volume, from_price, to_price, quantity, cargo_volume, gross_profit = row
            
            trades.append(Trade(
                from_station=from_station,
                to_station=to_station,
                from_order_id=from_order_id,
                to_order_id=to_order_id,
                item_name=item_name,
                type_id=type_id,
                item_cargo_volume=item_cargo_volume,
                from_price=from_price,
                to_price=to_price,
                quantity=quantity,
                cargo=cargo_volume,
                gross_profit=gross_profit
            ))

    logger.info(
        "Generated %d arbitrage candidates after shortlist (limit=%d, type_limit=%d, station_pair_limit=%d, rank_mode=%s)",
        len(trades),
        shortlist.candidate_limit,
        shortlist.type_limit,
        shortlist.station_pair_limit,
        shortlist.rank_mode,
    )
    return trades

async def snipe(market: Market, cargo_space: int, current_region: int, tax_rate: float, max_capital: float, snipe_profit_threshold: float) -> Optional[Trade]:
    if not market.db_conn:
        return None
        
    query = f"""
    WITH buy_orders AS (
        SELECT * FROM orders WHERE is_buy_order = 1
    ),
    sell_orders AS (
        SELECT * FROM orders WHERE is_buy_order = 0 AND region_id = {current_region}
    )
    SELECT 
        s.location_id as from_station,
        b.location_id as to_station,
        s.order_id as from_order_id,
        b.order_id as to_order_id,
        s.item_name,
        s.type_id,
        s.item_cargo_volume,
        s.price as from_price,
        b.price as to_price,
        MIN(
            s.volume_remain, 
            b.volume_remain,
            CAST({cargo_space} / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
            CAST({max_capital} / NULLIF(s.price, 0) AS INTEGER),
            CAST({max_capital} / NULLIF(b.price, 0) AS INTEGER)
        ) as quantity
    FROM sell_orders s
    JOIN buy_orders b ON s.type_id = b.type_id
    WHERE 
        b.price * (1 - {tax_rate}) >= s.price
        AND s.item_cargo_volume > 0
        AND s.price > 0 AND b.price > 0
    """

    best_trade = None
    max_profit = 0

    async with market.db_conn.execute(query) as cursor:
        async for row in cursor:
            from_station, to_station, from_order_id, to_order_id, item_name, type_id, item_cargo_volume, from_price, to_price, quantity = row
            
            if quantity is None or quantity <= 0:
                continue
                
            effective_buy_price = to_price * (1 - tax_rate)
            gross_profit = quantity * (effective_buy_price - from_price)
            
            if gross_profit > max_profit:
                cargo_volume = quantity * item_cargo_volume
                
                best_trade = Trade(
                    from_station=from_station,
                    to_station=to_station,
                    from_order_id=from_order_id,
                    to_order_id=to_order_id,
                    item_name=item_name,
                    type_id=type_id,
                    item_cargo_volume=item_cargo_volume,
                    from_price=from_price,
                    to_price=to_price,
                    quantity=quantity,
                    cargo=cargo_volume,
                    gross_profit=gross_profit
                )
                max_profit = gross_profit

    if max_profit > snipe_profit_threshold and best_trade:
        return best_trade
    return None