app_core.py

from __future__ import annotations

import os
import urllib.parse
from collections import defaultdict
from pathlib import Path

import psycopg2
from dotenv import load_dotenv
from psycopg2.extras import RealDictCursor


BASE_DIR = Path(__file__).resolve().parent
load_dotenv(dotenv_path=BASE_DIR / "sites" / ".env")

DB_CONFIG = {
    "dbname": os.getenv("DB_NAME", "rust_items_db"),
    "user": os.getenv("DB_USER", "postgres"),
    "password": os.getenv("DB_PASSWORD", ""),
    "host": os.getenv("DB_HOST", "localhost"),
    "port": os.getenv("DB_PORT", "5432"),
}
MAX_EDGES_PER_ROUTE_FOR_CHAINS = 30
LOOKUP_LIMIT = 80

def get_market_url(site, item_name, is_tf2=False):
    site = site.lower().strip()
    name_q = urllib.parse.quote(item_name)
    name_plus = urllib.parse.quote_plus(item_name)
    name_ = item_name.lower().replace(" ", "-")

    if site == "csdeals":
        game = "tf2" if is_tf2 else "rust"
        return f"https://cs.deals/new/market?game={game}&name={name_plus}"

    elif site == "dmarket":
        game = "tf2-skins" if is_tf2 else "rust-skins"
        return f"https://dmarket.com/ingame-items/item-list/{game}?title={name_q}"

    elif site == "lootfarm":
        game_id = "440" if is_tf2 else "252490"
        return f"https://loot.farm/#skin={game_id}_{name_q}"

    elif site == "steam":
        game_id = "440" if is_tf2 else "252490"
        return f"https://steamcommunity.com/market/listings/{game_id}/{name_q}"

    elif site == "lis_skins":
        return f"https://lis-skins.com/market/rust/?query={name_q}"

    elif site == "skinport":
        game = "tf2" if is_tf2 else "rust"
        return f"https://skinport.com/ru/{game}/market?search={name_plus}"

    elif site == "tradeit":
        game = "tf2" if is_tf2 else "rust"
        return f"https://tradeit.gg/{game}/trade?search={name_plus}"

    elif site == "itrade":
        return f"https://itrade.gg/trade/rust?search={name_q}"

    elif site == "rusttm":
        if is_tf2:
            return f"https://tf2.tm/?s=price&t=all&search={name_q}"
        else:
            return f"https://rust.tm/?s=price&t=all&search={name_q}"

    elif site == "mannco":
        #game = "tf2" if is_tf2 else "rust"
        #return f"https://mannco.store/{game}?&search={name_q}"
        game_id = "440" if is_tf2 else "252490"
        return f"https://mannco.store/item/{game_id}-{name_}"

    elif site == "rustskins":
        return f"https://rustskins.com/ru/rust/item/{name_}"

    elif site == "shadowpay":
        game = "tf2-items" if is_tf2 else "rust-items"
        return f"https://shadowpay.com/{game}?price_from=0&price_to=100000&is_stattrak&hold_days&search={name_plus}"

    elif site == "swapgg":
        game_id = "440" if is_tf2 else "252490"
        return f"https://swap.gg/trade?search={name_plus}&game={game_id}"

    return ""


def parse_input(val, default_val):
    val = str(val).strip()
    if not val: return default_val
    try:
        return float(val)
    except Exception:
        return default_val


def safe_float(value, default=0.0):
    try:
        if value is None: return default
        return float(value)
    except Exception:
        return default


def safe_int(value, default=0):
    try:
        if value is None: return default
        return int(value)
    except Exception:
        return default


def normalize_exit_mode(value, default="best"):
    mode = str(value or default).strip().lower()
    return mode if mode in {"best", "sell", "trade", "instant"} else default


def normalize_site_set(values):
    if not values:
        return None
    normalized = {str(v).strip().lower() for v in values if str(v).strip()}
    normalized.discard("all")
    return normalized or None


def is_tf2_game(game_value):
    return str(game_value).lower() == "tf2"


def normalize_craft_state(game_value, craft_state):
    if not is_tf2_game(game_value):
        return None
    state = str(craft_state or "").strip().lower()
    if state in {"craftable", "uncraftable", "unknown"}:
        return state
    return "unknown"


def normalize_item_identity(item_name, game_value, craft_state=None):
    game = str(game_value or "rust").lower()
    name = str(item_name or "").strip()
    if game != "tf2":
        return name, game, None

    state = normalize_craft_state(game, craft_state)
    lower_name = name.lower()
    if lower_name.startswith("uncraftable "):
        name = name[len("uncraftable "):].strip()
        if state == "unknown":
            state = "uncraftable"
    elif lower_name.startswith("craftable "):
        name = name[len("craftable "):].strip()
        if state == "unknown":
            state = "craftable"
    return name, game, state


def format_item_display_name(item_name, game_value, craft_state=None):
    name, game, state = normalize_item_identity(item_name, game_value, craft_state)
    if game != "tf2":
        return name
    if state == "uncraftable":
        return f"Uncraftable {name}"
    if state == "craftable":
        return name
    return f"{name} (Craft: Unknown)"


def resolve_pair_craft_state(game_value, buy_state, sell_state):
    game = str(game_value or "rust").lower()
    if game != "tf2":
        return True, None, "n/a"

    left = normalize_craft_state(game, buy_state)
    right = normalize_craft_state(game, sell_state)

    if left == "unknown" and right == "unknown":
        # Both sites do not provide craft state, so these pairs are filtered out as the riskiest.
        return False, "unknown", "unknown_vs_unknown"
    if left != "unknown" and right != "unknown":
        if left != right:
            return False, None, "mismatch"
        return True, left, "exact"

    resolved = left if left != "unknown" else right
    return True, resolved, "known_vs_unknown"


def fmt_dt(value):
    if not value: return "Нет данных"
    try:
        return value.strftime("%Y-%m-%d %H:%M:%S")
    except Exception:
        return str(value)


# =========================

def get_sites():
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cur = conn.cursor()
        cur.execute("SELECT name FROM sites ORDER BY name;")
        rows = cur.fetchall()
        cur.close()
        conn.close()
        return ["ALL"] + [r[0] for r in rows]
    except Exception:
        return ["ALL", "Dmarket", "LOOT.Farm", "CS.Money", "skinport", "rusttm", "lis_skins"]


def fetch_latest_db_timestamp():
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()
    cur.execute("SELECT MAX(updated_at) FROM item_site_data;")
    value = cur.fetchone()[0]
    cur.close()
    conn.close()
    return value


def get_log_site_filters():
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cur = conn.cursor()
        cur.execute("""
            SELECT site_name FROM site_catalog
            UNION
            SELECT DISTINCT site_name FROM site_runs
            ORDER BY site_name;
        """)
        rows = cur.fetchall()
        cur.close()
        conn.close()
        return ["ALL"] + [r[0] for r in rows]
    except Exception:
        return ["ALL"]


def fetch_rows(ordered=True):
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor(cursor_factory=RealDictCursor)
    order_sql = " ORDER BY i.name, i.craft_state NULLS LAST, s.name" if ordered else ""
    cur.execute("""
        SELECT i.name AS item_name, i.image, i.game, i.craft_state, s.name AS site_name, d.buy_price, d.sell_price, d.trade_price, d.instant_sell_price, d.can_deposit, d.can_buy, d.sales_7d, d.sales_30d, d.updated_at
        FROM item_site_data d JOIN items i ON i.id = d.item_id JOIN sites s ON s.id = d.site_id
    """ + order_sql + ";")
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return rows


def lookup_rows(item_name):
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute("""
        SELECT i.name AS item_name, i.image, i.game, i.craft_state, s.name AS site_name, d.buy_price, d.sell_price, d.trade_price, d.instant_sell_price, d.can_deposit, d.can_buy, d.sales_7d, d.sales_30d, d.updated_at
        FROM item_site_data d JOIN items i ON i.id = d.item_id JOIN sites s ON s.id = d.site_id
        WHERE LOWER(i.name) LIKE LOWER(%s) ORDER BY i.name, i.craft_state NULLS LAST, s.name LIMIT %s;
    """, (f"%{item_name}%", LOOKUP_LIMIT))
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return rows


def fetch_site_catalog_rows():
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute("""
        SELECT sc.site_name, sc.run_interval_seconds, sc.supports_rust, sc.supports_tf2, sc.has_history_job,
               s.last_parsed
        FROM site_catalog sc
        LEFT JOIN sites s ON s.name = sc.site_name
        ORDER BY sc.site_name;
    """)
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return rows


def fetch_site_run_logs(limit=200, site_name=None, errors_only=False):
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor(cursor_factory=RealDictCursor)
    clauses = []
    params = []

    if site_name and site_name != "ALL":
        clauses.append("site_name = %s")
        params.append(site_name)
    if errors_only:
        clauses.append("success = FALSE")

    where_sql = f"WHERE {' AND '.join(clauses)}" if clauses else ""
    try:
        cur.execute(
            f"""
            SELECT site_name, run_kind, game, success, items_count, duration_ms, error_message, logged_at, started_at, finished_at
            FROM site_runs
            {where_sql}
            ORDER BY logged_at DESC
            LIMIT %s
            """,
            (*params, limit),
        )
    except psycopg2.errors.UndefinedColumn:
        conn.rollback()
        cur.execute(
            f"""
            SELECT site_name, run_kind, game, success, items_count, duration_ms, error_message, finished_at AS logged_at, started_at, finished_at
            FROM site_runs
            {where_sql}
            ORDER BY finished_at DESC
            LIMIT %s
            """,
            (*params, limit),
        )
    rows = cur.fetchall()
    cur.close()
    conn.close()
    return rows


# =========================
# Analysis and logic
# =========================

def choose_exit_price(row, exit_mode):
    can_deposit = bool(row.get("can_deposit"))
    instant = safe_float(row.get("instant_sell_price"))
    trade = safe_float(row.get("trade_price"))
    market = safe_float(row.get("sell_price"))

    if exit_mode == "instant": return ("instant", instant) if can_deposit and instant > 0 else (None, 0.0)
    if exit_mode == "sell": return ("sell", market) if can_deposit and market > 0 else (None, 0.0)
    if exit_mode == "trade": return ("trade", trade) if can_deposit and trade > 0 else (None, 0.0)

    candidates = []
    if can_deposit and instant > 0: candidates.append(("instant", instant))
    if can_deposit and market > 0: candidates.append(("sell", market))
    if can_deposit and trade > 0: candidates.append(("trade", trade))

    if not candidates: return (None, 0.0)
    return max(candidates, key=lambda x: x[1])


def normalize_market_rows(rows):
    normalized = []
    for row in rows:
        item_name, game, craft_state = normalize_item_identity(
            row.get("item_name"),
            row.get("game") or "rust",
            row.get("craft_state"),
        )
        mapped = dict(row)
        mapped["item_name"] = item_name
        mapped["game"] = game
        mapped["craft_state"] = craft_state
        mapped["item_display"] = format_item_display_name(item_name, game, craft_state)
        normalized.append(mapped)
    return normalized


def build_direct_deals(rows, balance, min_price, buy_sites, sell_sites, min_roi, keyword, exit_mode, ignore_sites, min_sales=0, sales_period="7д", min_sales_sites=None):
    keyword = (keyword or "").strip().lower()
    ignored = {s.strip().lower() for s in ignore_sites}
    buy_sites_filter = normalize_site_set(buy_sites)
    sell_sites_filter = normalize_site_set(sell_sites)
    mode = normalize_exit_mode(exit_mode, "best")
    by_item = defaultdict(list)
    normalized_rows = normalize_market_rows(rows)

    # Decide which sales field to use for filtering.
    use_7d = "7д" in sales_period
    use_30d = "30д" in sales_period

    # Normalize selected sites for sales checks.
    if min_sales_sites is None or not min_sales_sites:
        # If no specific sites are selected, check all of them.
        check_sales_sites = {"dmarket", "rustskins", "rusttm", "mannco", "skinport"}
    else:
        # Convert site names to lowercase for comparison.
        check_sales_sites = {site.lower() for site in min_sales_sites}

    for row in normalized_rows:
        item_display = row.get("item_display") or row["item_name"]
        if keyword and keyword not in str(item_display).lower() and keyword not in str(row["item_name"]).lower():
            continue
        item_key = (row["item_name"], row.get("game") or "rust")
        by_item[item_key].append(row)

    deals = []
    for (item_name, game), group in by_item.items():
        sales_by_site = {
            "dmarket": {"sales_7d": 0, "sales_30d": 0},
            "rustskins": {"sales_7d": 0, "sales_30d": 0},
            "rusttm": {"sales_7d": 0, "sales_30d": 0},
            "mannco": {"sales_7d": 0, "sales_30d": 0},
            "skinport": {"sales_7d": 0, "sales_30d": 0},
        }

        for r in group:
            s_name = r["site_name"].lower()
            normalized_site = "rusttm" if s_name in ("rusttm", "rust.tm") else s_name
            if normalized_site in sales_by_site:
                sales_by_site[normalized_site]["sales_7d"] = max(
                    sales_by_site[normalized_site]["sales_7d"],
                    safe_int(r.get("sales_7d"), 0),
                )
                sales_by_site[normalized_site]["sales_30d"] = max(
                    sales_by_site[normalized_site]["sales_30d"],
                    safe_int(r.get("sales_30d"), 0),
                )
        
        # Check the sales filter for the whole item.
        if min_sales > 0:
            # Use the maximum sales count across selected sites.
            max_sales = 0
            for site_name, site_data in sales_by_site.items():
                # Check only selected sites.
                if site_name not in check_sales_sites:
                    continue
                    
                if use_7d and use_30d:
                    # If "7d+30d" is selected, use the maximum from both periods.
                    max_sales = max(max_sales, site_data["sales_7d"], site_data["sales_30d"])
                elif use_30d:
                    # If only "30d" is selected.
                    max_sales = max(max_sales, site_data["sales_30d"])
                else:
                    # Default to "7d".
                    max_sales = max(max_sales, site_data["sales_7d"])
            
            # Skip the item if it does not reach the minimum sales threshold.
            if max_sales < min_sales:
                continue

        for buy in group:
            buy_site = buy["site_name"]
            if buy_site.lower() in ignored: continue

            buy_price = safe_float(buy["buy_price"])
            can_buy = bool(buy.get("can_buy"))

            if buy_sites_filter is not None and buy_site.lower() not in buy_sites_filter: continue
            if not can_buy or buy_price <= 0: continue
            if buy_price > balance or buy_price < min_price: continue

            for sell in group:
                sell_site = sell["site_name"]
                if sell_site == buy_site: continue
                if sell_site.lower() in ignored: continue
                if sell_sites_filter is not None and sell_site.lower() not in sell_sites_filter: continue

                craft_ok, resolved_craft_state, craft_match_mode = resolve_pair_craft_state(
                    game,
                    buy.get("craft_state"),
                    sell.get("craft_state"),
                )
                if not craft_ok:
                    continue

                sell_type, exit_price = choose_exit_price(sell, mode)
                if not sell_type or exit_price <= 0: continue

                profit = exit_price - buy_price
                roi = (profit / buy_price) * 100.0 if buy_price > 0 else 0.0

                if roi < min_roi or roi > 1000.0: continue

                item_display = format_item_display_name(item_name, game, resolved_craft_state)
                craft_uid = resolved_craft_state or "none"
                deals.append({
                    "uid": f"{item_name}_{buy_site}_{sell_site}_{game}_{craft_uid}_{craft_match_mode}",
                    "item": item_name,
                    "item_display": item_display,
                    "craft_state": resolved_craft_state,
                    "craft_match_mode": craft_match_mode,
                    "buy_site": buy_site,
                    "sell_site": sell_site,
                    "image": buy.get("image") or sell.get("image"),
                    "buy_price": buy_price, "sell_price": exit_price, "sell_type": sell_type,
                    "exit_mode": mode, "profit": profit, "roi": roi,
                    "sales_dmarket": sales_by_site["dmarket"]["sales_7d"],
                    "sales_rustskins": sales_by_site["rustskins"]["sales_7d"],
                    "sales_rusttm": sales_by_site["rusttm"]["sales_7d"],
                    "sales_mannco": sales_by_site["mannco"]["sales_7d"],
                    "sales_skinport": sales_by_site["skinport"]["sales_7d"],
                    "sales30_dmarket": sales_by_site["dmarket"]["sales_30d"],
                    "sales30_rustskins": sales_by_site["rustskins"]["sales_30d"],
                    "sales30_rusttm": sales_by_site["rusttm"]["sales_30d"],
                    "sales30_mannco": sales_by_site["mannco"]["sales_30d"],
                    "sales30_skinport": sales_by_site["skinport"]["sales_30d"],
                    "updated_at": buy.get("updated_at") or sell.get("updated_at"),
                    "can_buy": can_buy, "can_deposit": bool(sell.get("can_deposit")),
                    "game": game
                })

    return deals


def build_chain_edges(rows, min_price, keyword, ignore_sites):
    keyword = (keyword or "").strip().lower()
    ignored = {s.strip().lower() for s in ignore_sites}
    by_item = defaultdict(list)
    normalized_rows = normalize_market_rows(rows)

    for row in normalized_rows:
        item_display = row.get("item_display") or row["item_name"]
        if keyword and keyword not in str(item_display).lower() and keyword not in str(row["item_name"]).lower():
            continue
        item_key = (row["item_name"], row.get("game") or "rust")
        by_item[item_key].append(row)

    deals = []
    for (item_name, game), group in by_item.items():
        for buy in group:
            buy_site = buy["site_name"]
            if buy_site.lower() in ignored: continue

            buy_price = safe_float(buy["buy_price"])
            if not bool(buy.get("can_buy")) or buy_price <= 0 or buy_price < min_price: continue

            for sell in group:
                sell_site = sell["site_name"]
                if sell_site == buy_site or sell_site.lower() in ignored or not bool(sell.get("can_deposit")): continue

                craft_ok, resolved_craft_state, craft_match_mode = resolve_pair_craft_state(
                    game,
                    buy.get("craft_state"),
                    sell.get("craft_state"),
                )
                if not craft_ok:
                    continue

                instant = safe_float(sell.get("instant_sell_price"))
                market = safe_float(sell.get("sell_price"))
                trade = safe_float(sell.get("trade_price"))
                best_exit = max(instant, market, trade)

                if best_exit <= 0: continue
                item_display = format_item_display_name(item_name, game, resolved_craft_state)
                deals.append({
                    "item": item_name,
                    "item_display": item_display,
                    "craft_state": resolved_craft_state,
                    "craft_match_mode": craft_match_mode,
                    "buy_site": buy_site,
                    "sell_site": sell_site,
                    "buy_price": buy_price, "instant_price": instant, "sell_price": market, "trade_price": trade,
                    "best_exit_price": best_exit, "roi": ((best_exit - buy_price) / buy_price) * 100.0,
                    "game": game
                })
    return deals


def get_chain_exit_price(leg, exit_mode):
    mode = normalize_exit_mode(exit_mode, "best")
    if mode == "instant":
        return leg["instant_price"], "instant"
    if mode == "sell":
        return leg["sell_price"], "sell"
    if mode == "trade":
        return leg["trade_price"], "trade"
    candidates = []
    if leg["instant_price"] > 0:
        candidates.append(("instant", leg["instant_price"]))
    if leg["sell_price"] > 0:
        candidates.append(("sell", leg["sell_price"]))
    if leg["trade_price"] > 0:
        candidates.append(("trade", leg["trade_price"]))
    if not candidates:
        return 0.0, None
    best_kind, best_value = max(candidates, key=lambda item: item[1])
    return best_value, best_kind


def build_multi_chains(
    all_edges,
    balance,
    buy_sites,
    sell_sites,
    min_chain_roi,
    hops,
    exit_mode,
    max_edges_per_route=MAX_EDGES_PER_ROUTE_FOR_CHAINS,
):
    buy_sites_filter = normalize_site_set(buy_sites)
    sell_sites_filter = normalize_site_set(sell_sites)
    mode = normalize_exit_mode(exit_mode, "best")
    route_cap = max(1, safe_int(max_edges_per_route, MAX_EDGES_PER_ROUTE_FOR_CHAINS))
    routes = defaultdict(list)
    for e in all_edges:
        routes[(e["buy_site"], e["sell_site"], e.get("game", "rust"))].append(e)

    best_edges = []
    for route, edges in routes.items():
        edges.sort(key=lambda x: x["roi"], reverse=True)
        best_edges.extend(edges[:route_cap])

    adjacency = defaultdict(list)
    for e in best_edges: adjacency[e["buy_site"]].append(e)

    chains = []
    seen = set()
    starts = list(adjacency.keys()) if buy_sites_filter is None else [s for s in adjacency if s.lower() in buy_sites_filter]

    for start_site in starts:
        for e1 in adjacency.get(start_site, []):
            game = e1.get("game", "rust")
            buy1 = e1["buy_price"]
            if balance != float('inf') and buy1 > balance: continue

            sell1, type1 = get_chain_exit_price(e1, mode)
            if sell1 <= 0: continue

            if hops == 2:
                for e2 in adjacency.get(e1["sell_site"], []):
                    if e2.get("game", "rust") != game:
                        continue
                    end_site = e2["sell_site"]
                    if sell_sites_filter is not None and end_site.lower() not in sell_sites_filter: continue
                    buy2 = e2["buy_price"]
                    if buy2 > sell1: continue
                    sell2, type2 = get_chain_exit_price(e2, mode)
                    if sell2 <= 0: continue

                    profit = sell2 - buy1
                    roi = (profit / buy1) * 100.0 if buy1 > 0 else 0.0
                    if roi < min_chain_roi or roi > 1000.0: continue

                    k = (
                        e1["item"],
                        e1.get("craft_state") or "unknown",
                        e1.get("craft_match_mode") or "n/a",
                        game,
                        e1["buy_site"],
                        e2["buy_site"],
                        e2["sell_site"],
                        e2["item"],
                        e2.get("craft_state") or "unknown",
                        e2.get("craft_match_mode") or "n/a",
                    )
                    if k in seen: continue
                    seen.add(k)

                    chains.append({
                        "path": f"{start_site} → {e1['sell_site']} → {end_site}", "legs": [e1, e2],
                        "step_details": [
                            {"cost": buy1, "earned": sell1, "sell_type": type1, "step_roi": e1["roi"]},
                            {"cost": buy2, "earned": sell2, "sell_type": type2,
                             "step_roi": ((sell2 - buy2) / buy2) * 100.0}
                        ],
                        "compound_roi": roi, "start_cost": buy1, "final_returned": sell2,
                        "profit_money": profit, "leftovers": sell1 - buy2,
                        "craft_confidence": (
                            "known_vs_unknown"
                            if any(str(leg.get("craft_match_mode") or "").lower() == "known_vs_unknown" for leg in (e1, e2))
                            else "exact"
                        ),
                        "game": game,
                    })
            elif hops == 3:
                for e2 in adjacency.get(e1["sell_site"], []):
                    if e2.get("game", "rust") != game:
                        continue
                    buy2 = e2["buy_price"]
                    if buy2 > sell1: continue
                    sell2, type2 = get_chain_exit_price(e2, mode)
                    if sell2 <= 0: continue
                    leftover1 = sell1 - buy2

                    for e3 in adjacency.get(e2["sell_site"], []):
                        if e3.get("game", "rust") != game:
                            continue
                        end_site = e3["sell_site"]
                        if sell_sites_filter is not None and end_site.lower() not in sell_sites_filter: continue
                        buy3 = e3["buy_price"]
                        if buy3 > sell2: continue
                        sell3, type3 = get_chain_exit_price(e3, mode)
                        if sell3 <= 0: continue

                        profit = sell3 - buy1
                        roi = (profit / buy1) * 100.0 if buy1 > 0 else 0.0
                        if roi < min_chain_roi or roi > 1000.0: continue

                        k = (
                            e1["item"],
                            e1.get("craft_state") or "unknown",
                            e1.get("craft_match_mode") or "n/a",
                            e2["item"],
                            e2.get("craft_state") or "unknown",
                            e2.get("craft_match_mode") or "n/a",
                            e3["item"],
                            e3.get("craft_state") or "unknown",
                            e3.get("craft_match_mode") or "n/a",
                            game,
                            e1["buy_site"],
                            e2["buy_site"],
                            e3["buy_site"],
                            e3["sell_site"],
                        )
                        if k in seen: continue
                        seen.add(k)

                        chains.append({
                            "path": f"{start_site} → {e1['sell_site']} → {e2['sell_site']} → {end_site}",
                            "legs": [e1, e2, e3],
                            "step_details": [
                                {"cost": buy1, "earned": sell1, "sell_type": type1, "step_roi": e1["roi"]},
                                {"cost": buy2, "earned": sell2, "sell_type": type2,
                                 "step_roi": ((sell2 - buy2) / buy2) * 100.0},
                                {"cost": buy3, "earned": sell3, "sell_type": type3,
                                 "step_roi": ((sell3 - buy3) / buy3) * 100.0}
                            ],
                            "compound_roi": roi, "start_cost": buy1, "final_returned": sell3,
                            "profit_money": profit, "leftovers": leftover1 + (sell2 - buy3),
                            "craft_confidence": (
                                "known_vs_unknown"
                                if any(str(leg.get("craft_match_mode") or "").lower() == "known_vs_unknown" for leg in (e1, e2, e3))
                                else "exact"
                            ),
                            "game": game,
                        })

    chains.sort(key=lambda x: (x["compound_roi"], x["profit_money"]), reverse=True)
    return chains


def update_watchlist_deals(rows, watchlist_dict, exit_mode):
    by_item = defaultdict(list)
    for row in normalize_market_rows(rows):
        item_key = (row["item_name"], row.get("game") or "rust")
        by_item[item_key].append(row)

    def _pick_row(group, site_name, target_craft_state, game, prefer_buy):
        candidates = [r for r in group if r["site_name"] == site_name]
        if not candidates:
            return None
        if game != "tf2":
            if prefer_buy:
                return min(candidates, key=lambda r: safe_float(r.get("buy_price"), float("inf")))
            return max(candidates, key=lambda r: choose_exit_price(r, exit_mode)[1])

        target_state = normalize_craft_state(game, target_craft_state)

        def rank(row):
            row_state = normalize_craft_state(game, row.get("craft_state"))
            if target_state == "unknown":
                return 0 if row_state == "unknown" else 1
            if row_state == target_state:
                return 0
            if row_state == "unknown":
                return 1
            return 2

        ranked = [r for r in candidates if rank(r) < 2]
        if not ranked:
            return None
        best_rank = min(rank(r) for r in ranked)
        best_rows = [r for r in ranked if rank(r) == best_rank]
        if prefer_buy:
            return min(best_rows, key=lambda r: safe_float(r.get("buy_price"), float("inf")))
        return max(best_rows, key=lambda r: choose_exit_price(r, exit_mode)[1])

    for uid, deal in list(watchlist_dict.items()):
        item_name, game, target_state = normalize_item_identity(
            deal.get("item"),
            deal.get("game", "rust"),
            deal.get("craft_state"),
        )
        group = by_item.get((item_name, game), [])
        if not group:
            continue

        buy_row = _pick_row(group, deal["buy_site"], target_state, game, prefer_buy=True)
        sell_row = _pick_row(group, deal["sell_site"], target_state, game, prefer_buy=False)
        if not buy_row or not sell_row:
            continue

        craft_ok, resolved_craft_state, craft_match_mode = resolve_pair_craft_state(
            game,
            buy_row.get("craft_state"),
            sell_row.get("craft_state"),
        )
        if not craft_ok:
            continue

        buy_price = safe_float(buy_row["buy_price"])
        sell_type, exit_price = choose_exit_price(sell_row, exit_mode)
        deal["item"] = item_name
        deal["item_display"] = format_item_display_name(item_name, game, resolved_craft_state)
        deal["craft_state"] = resolved_craft_state
        deal["craft_match_mode"] = craft_match_mode
        deal["buy_price"] = buy_price
        deal["sell_price"] = exit_price
        deal["sell_type"] = sell_type
        deal["can_buy"] = bool(buy_row.get("can_buy"))
        deal["can_deposit"] = bool(sell_row.get("can_deposit"))
        deal["updated_at"] = buy_row.get("updated_at") or sell_row.get("updated_at")
        deal["image"] = buy_row.get("image") or sell_row.get("image")
        deal["game"] = game

        if exit_price > 0 and buy_price > 0:
            deal["profit"] = exit_price - buy_price
            deal["roi"] = (deal["profit"] / buy_price) * 100.0
        else:
            deal["profit"] = 0
            deal["roi"] = 0