package core

import (
	"fmt"
	"log"
)

const (
	ArbitrageCandidateLimit   = 30000
	ArbitrageTypeLimit        = 250
	ArbitrageStationPairLimit = 40
)

type ShortlistConfig struct {
	CandidateLimit   int
	TypeLimit        int
	StationPairLimit int
	RankMode         string
}

func DefaultShortlistConfig() *ShortlistConfig {
	return &ShortlistConfig{
		CandidateLimit:   ArbitrageCandidateLimit,
		TypeLimit:        ArbitrageTypeLimit,
		StationPairLimit: ArbitrageStationPairLimit,
		RankMode:         "and",
	}
}

// Arbitrage calculates profitable trade pairs between stations.
func Arbitrage(
	market *Market,
	cargoSpace int,
	taxRate float64,
	profitThreshold float64,
	maxCapital float64,
	shortlist *ShortlistConfig,
) ([]Trade, error) {
	taxRate = taxRate / 100.0
	if shortlist == nil {
		shortlist = DefaultShortlistConfig()
	}
	
	if shortlist.RankMode != "and" && shortlist.RankMode != "or" {
		return nil, fmt.Errorf("unsupported shortlist rank mode: %s", shortlist.RankMode)
	}
	
	rankOperator := "AND"
	if shortlist.RankMode == "or" {
		rankOperator = "OR"
	}

	query := fmt.Sprintf(`
	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(%d / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
				CAST(%f / NULLIF(s.price, 0) AS INTEGER),
				CAST(%f / 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 - %f) >= s.price
			AND s.item_cargo_volume > 0
			AND s.price > 0 AND b.price > 0
	),
	candidate_trades AS (
		SELECT
			*,
			quantity * (to_price * (1 - %f) - from_price) as gross_profit,
			quantity * item_cargo_volume as cargo,
			(quantity * (to_price * (1 - %f) - from_price)) / NULLIF(quantity * item_cargo_volume, 0) as profit_density,
			(quantity * (to_price * (1 - %f) - from_price)) / NULLIF(quantity * from_price, 0) as roi
		FROM raw_trades
		WHERE quantity > 0 AND quantity * (to_price * (1 - %f) - from_price) >= %f
	),
	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 <= %d
			%s station_pair_rank <= %d)
	ORDER BY gross_profit DESC, roi DESC, profit_density DESC
		LIMIT %d
	`,
		cargoSpace, maxCapital, maxCapital, taxRate, 
		taxRate, taxRate, taxRate, taxRate, profitThreshold,
		shortlist.TypeLimit, rankOperator, shortlist.StationPairLimit, shortlist.CandidateLimit,
	)

	rows, err := market.DB.Query(query)
	if err != nil {
		return nil, fmt.Errorf("arbitrage query error: %v", err)
	}
	defer rows.Close()

	var trades []Trade
	for rows.Next() {
		var t Trade
		err := rows.Scan(
			&t.FromStation,
			&t.ToStation,
			&t.FromOrderID,
			&t.ToOrderID,
			&t.ItemName,
			&t.TypeID,
			&t.ItemCargoVolume,
			&t.FromPrice,
			&t.ToPrice,
			&t.Quantity,
			&t.Cargo,
			&t.GrossProfit,
		)
		if err != nil {
			return nil, fmt.Errorf("arbitrage scan error: %v", err)
		}
		
		if t.Quantity > 0 {
			trades = append(trades, t)
		}
	}

	log.Printf("Generated %d arbitrage candidates after shortlist (limit=%d, rank_mode=%s)", len(trades), shortlist.CandidateLimit, shortlist.RankMode)
	return trades, nil
}

// Snipe looks for immediate sell orders inside the current region that can be immediately flipped to buy orders.
func Snipe(
	market *Market,
	cargoSpace int,
	currentRegion int,
	taxRate float64,
	maxCapital float64,
	snipeProfitThreshold float64,
) (*Trade, error) {
	taxRate = taxRate / 100.0
	query := fmt.Sprintf(`
	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 = %d
	)
	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(%d / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
			CAST(%f / NULLIF(s.price, 0) AS INTEGER),
			CAST(%f / 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 - %f) >= s.price
		AND s.item_cargo_volume > 0
		AND s.price > 0 AND b.price > 0
	`, currentRegion, cargoSpace, maxCapital, maxCapital, taxRate)

	rows, err := market.DB.Query(query)
	if err != nil {
		return nil, fmt.Errorf("snipe query error: %v", err)
	}
	defer rows.Close()

	var bestTrade *Trade
	maxProfit := 0.0

	for rows.Next() {
		var t Trade
		err := rows.Scan(
			&t.FromStation,
			&t.ToStation,
			&t.FromOrderID,
			&t.ToOrderID,
			&t.ItemName,
			&t.TypeID,
			&t.ItemCargoVolume,
			&t.FromPrice,
			&t.ToPrice,
			&t.Quantity,
		)
		if err != nil {
			return nil, fmt.Errorf("snipe scan error: %v", err)
		}

		if t.Quantity <= 0 {
			continue
		}

		effectiveBuyPrice := t.ToPrice * (1.0 - taxRate)
		grossProfit := float64(t.Quantity) * (effectiveBuyPrice - t.FromPrice)

		if grossProfit > maxProfit {
			t.Cargo = float64(t.Quantity) * t.ItemCargoVolume
			t.GrossProfit = grossProfit
			
			tradeCopy := t // copy
			bestTrade = &tradeCopy
			maxProfit = grossProfit
		}
	}

	if maxProfit > snipeProfitThreshold && bestTrade != nil {
		return bestTrade, nil
	}

	return nil, nil
}