"""Screening log and coin-state candidate queries."""

import json
from datetime import datetime, timedelta

from app.config.config_loader import state_score_thresholds
from app.db.schema import get_conn


def log_screening(layer, symbol, state, score, price, signals,
                  sector="", leader_status="", is_meme=0,
                  change_24h=0, funding_rate=0, detail=None):
    """Record one screening-layer observation."""
    conn = get_conn()
    conn.execute("""
    INSERT INTO screening_log (scan_time, layer, symbol, state, score, price, signals,
        sector, leader_status, is_meme, change_24h, funding_rate, detail_json)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """, (
        datetime.now().isoformat(), layer, symbol, state, score, price,
        json.dumps(signals, ensure_ascii=False) if isinstance(signals, list) else signals,
        sector, leader_status, is_meme, change_24h, funding_rate,
        json.dumps(detail, ensure_ascii=False) if detail else "{}",
    ))
    conn.commit()
    conn.close()


def get_screening_history(hours=24, limit=100):
    """Read recent fine-screening rows."""
    conn = get_conn()
    rows = conn.execute("""
    SELECT * FROM screening_log
    WHERE layer='细筛' AND scan_time >= %s
    ORDER BY score DESC, scan_time DESC LIMIT %s
    """, ((datetime.now() - timedelta(hours=float(hours or 24))).isoformat(), limit)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_candidates_for_confirm(limit=None):
    """Read candidates for confirm layer, preferring the latest screening window."""
    try:
        _, _, accumulate_threshold = state_score_thresholds()
    except Exception:
        accumulate_threshold = 3
    conn = get_conn()
    limit = max(1, min(int(limit or 50), 100))
    rows = conn.execute("""
    SELECT * FROM coin_state
    WHERE state IN ('加速', '蓄力')
      AND score >= %s
      AND detected_at >= %s
    ORDER BY score DESC, detected_at DESC
    LIMIT %s
    """, (accumulate_threshold, (datetime.now() - timedelta(minutes=45)).isoformat(), limit)).fetchall()
    if not rows:
        rows = conn.execute("""
        SELECT * FROM coin_state
        WHERE state IN ('加速', '蓄力')
          AND score >= 5
        ORDER BY score DESC, detected_at DESC
        LIMIT %s
        """, (limit,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]