OpenFin/backend/routers/institutional.py

"""
Institutional reports router — CFTC COT + EIA petroleum weekly.
GET  /api/institutional/reports        — list with filters
GET  /api/institutional/reports/{id}   — single report detail
POST /api/institutional/refresh        — trigger fetch
GET  /api/institutional/stats          — counts + latest dates
"""
import json
import logging
from datetime import datetime, timedelta
from typing import Any, Dict, List, Optional

from fastapi import APIRouter, HTTPException, Query

from services.database import get_conn, get_config

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/api/institutional", tags=["institutional"])


# ── DB helpers ─────────────────────────────────────────────────────────────────

def _row_to_dict(row) -> Dict:
    d = dict(row)
    for field in ("raw_data_json", "key_points_json"):
        raw = d.pop(field, None) or ("[]" if "key_points" in field else "{}")
        try:
            d[field.replace("_json", "")] = json.loads(raw)
        except Exception:
            d[field.replace("_json", "")] = [] if "key_points" in field else {}
    return d


def save_institutional_report(report: Dict) -> Optional[int]:
    """Insert or update an institutional report. Returns row id."""
    conn = get_conn()
    try:
        existing = conn.execute(
            "SELECT id FROM institutional_reports WHERE report_type=? AND report_date=?",
            (report["report_type"], report["report_date"]),
        ).fetchone()

        raw_json = json.dumps(report.get("raw_data", {}), ensure_ascii=False)
        kp_json  = json.dumps(report.get("key_points", []), ensure_ascii=False)

        if existing:
            conn.execute(
                """UPDATE institutional_reports SET
                   fetch_date=datetime('now'), raw_data_json=?, key_points_json=?,
                   trading_implications=?, signal_energy=?, signal_metals=?,
                   signal_indices=?, signal_forex=?, ai_summary=?, importance=?,
                   title=?
                   WHERE id=?""",
                (
                    raw_json, kp_json,
                    report.get("trading_implications", ""),
                    report.get("signal_energy", "neutral"),
                    report.get("signal_metals", "neutral"),
                    report.get("signal_indices", "neutral"),
                    report.get("signal_forex", "neutral"),
                    report.get("ai_summary", ""),
                    report.get("importance", 2),
                    report.get("title", ""),
                    existing["id"],
                ),
            )
            conn.commit()
            return existing["id"]

        cursor = conn.execute(
            """INSERT INTO institutional_reports
               (report_type, report_date, title, source, importance, category,
                raw_data_json, key_points_json, trading_implications,
                signal_energy, signal_metals, signal_indices, signal_forex, ai_summary)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (
                report["report_type"],
                report["report_date"],
                report.get("title", ""),
                report.get("source", ""),
                report.get("importance", 2),
                report.get("category", "multi"),
                raw_json, kp_json,
                report.get("trading_implications", ""),
                report.get("signal_energy", "neutral"),
                report.get("signal_metals", "neutral"),
                report.get("signal_indices", "neutral"),
                report.get("signal_forex", "neutral"),
                report.get("ai_summary", ""),
            ),
        )
        conn.commit()
        return cursor.lastrowid
    finally:
        conn.close()


# ── Routes ─────────────────────────────────────────────────────────────────────

@router.get("/reports")
def list_reports(
    report_type: Optional[str] = Query(None),
    category:    Optional[str] = Query(None),
    importance:  Optional[int] = Query(None),
    days:        int           = Query(90),
    limit:       int           = Query(50),
):
    conn = get_conn()
    try:
        conditions = ["1=1"]
        params: List[Any] = []

        if report_type:
            conditions.append("report_type = ?")
            params.append(report_type)
        if category:
            # For categories that map to a signal column, also match reports
            # where that signal is non-neutral (most reports have category='multi')
            _signal_col = {
                'forex':    'signal_forex',
                'energy':   'signal_energy',
                'metals':   'signal_metals',
                'equities': 'signal_indices',
                'indices':  'signal_indices',
            }.get(category)
            if _signal_col:
                conditions.append(f"(category = ? OR {_signal_col} != 'neutral')")
            else:
                conditions.append("category = ?")
            params.append(category)
        if importance:
            conditions.append("importance >= ?")
            params.append(importance)
        if days:
            cutoff = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d")
            conditions.append("report_date >= ?")
            params.append(cutoff)

        where = " AND ".join(conditions)
        rows = conn.execute(
            f"SELECT * FROM institutional_reports WHERE {where} "
            f"ORDER BY report_date DESC, importance DESC LIMIT ?",
            params + [limit],
        ).fetchall()
        return [_row_to_dict(r) for r in rows]
    finally:
        conn.close()


@router.get("/reports/{report_id}")
def get_report(report_id: int):
    conn = get_conn()
    try:
        row = conn.execute(
            "SELECT * FROM institutional_reports WHERE id=?", (report_id,)
        ).fetchone()
        if not row:
            raise HTTPException(status_code=404, detail="Report not found")
        return _row_to_dict(row)
    finally:
        conn.close()


@router.post("/refresh")
def refresh_reports(report_type: Optional[str] = Query(None)):
    """Trigger a live fetch of any/all institutional report types."""
    results: Dict[str, str] = {}

    def _run(label: str, fetch_fn, *args):
        try:
            report = fetch_fn(*args)
            if report:
                save_institutional_report(report)
                results[label] = "ok"
                logger.info(f"[Institutional] {label} saved: {report['report_date']}")
            else:
                results[label] = "no_data"
        except Exception as e:
            logger.warning(f"[Institutional] {label} refresh failed: {e}")
            results[label] = f"error: {str(e)[:100]}"

    if not report_type or report_type == "cot":
        from services.cot_fetcher import fetch_cot_report
        _run("cot", fetch_cot_report)

    if not report_type or report_type == "eia":
        eia_key = get_config("eia_api_key") or ""
        if eia_key:
            from services.eia_fetcher import fetch_eia_report
            _run("eia", fetch_eia_report, eia_key)
        else:
            results["eia"] = "no_api_key"

    if not report_type or report_type == "earnings":
        from services.earnings_fetcher import fetch_earnings_report
        _run("earnings", fetch_earnings_report)

    if not report_type or report_type == "vx_curve":
        from services.vx_fetcher import fetch_vx_report
        _run("vx_curve", fetch_vx_report)

    if not report_type or report_type == "central_bank":
        from services.central_bank_fetcher import fetch_central_bank_reports
        _run("central_bank", fetch_central_bank_reports)

    if not report_type or report_type == "sentiment":
        from services.sentiment_fetcher import fetch_sentiment_report
        _run("sentiment", fetch_sentiment_report)

    return {"status": "done", "results": results}


@router.get("/stats")
def get_stats():
    conn = get_conn()
    try:
        total    = conn.execute("SELECT COUNT(*) FROM institutional_reports").fetchone()[0]
        by_type  = dict(conn.execute(
            "SELECT report_type, COUNT(*) FROM institutional_reports GROUP BY report_type"
        ).fetchall())
        latest_cot = conn.execute(
            "SELECT MAX(report_date) FROM institutional_reports WHERE report_type='cot'"
        ).fetchone()[0]
        latest_eia = conn.execute(
            "SELECT MAX(report_date) FROM institutional_reports WHERE report_type='eia'"
        ).fetchone()[0]
        absorbed = conn.execute(
            "SELECT COUNT(*) FROM institutional_reports "
            "WHERE absorbed_score IS NOT NULL AND absorbed_score > 0.3"
        ).fetchone()[0]
        high_importance = conn.execute(
            "SELECT COUNT(*) FROM institutional_reports WHERE importance=3"
        ).fetchone()[0]
        return {
            "total": total,
            "by_type": by_type,
            "latest_cot": latest_cot,
            "latest_eia": latest_eia,
            "absorbed_count": absorbed,
            "high_importance_count": high_importance,
        }
    finally:
        conn.close()