"""
Market Events — unified admin API for market_events + instrument impacts.
Prefix: /api/market-events

Merges what was split across /api/timeline and /api/impact.
"""
import json
import logging
from typing import Any, Dict, List, Optional

from fastapi import APIRouter, HTTPException, Query
from pydantic import BaseModel

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/api/market-events", tags=["Market Events"])


# ── Schemas ───────────────────────────────────────────────────────────────────

class SourceRef(BaseModel):
    title: str
    source: str = ""
    url: str = ""
    date: str = ""
    original_score: float = 0.0


class EventCreate(BaseModel):
    name: str
    start_date: str
    end_date: Optional[str] = None
    level: str = "short"
    category: str = "fundamental"
    sub_type: str = ""
    description: str = ""
    market_impact: str = ""
    affected_assets: List[str] = []
    impact_score: float = 0.5
    absorption_pct: Optional[float] = None
    parent_event_id: Optional[int] = None
    source_refs: List[SourceRef] = []


class EventUpdate(EventCreate):
    pass


class InstrumentImpactCreate(BaseModel):
    instrument_id: str
    impact_score: float = 0.5
    direction: str = "neutral"
    rationale: str = ""
    confidence: float = 0.7


class InstrumentImpactAdjust(BaseModel):
    adjusted_score: float
    adjusted_direction: str
    override_rationale: str = ""


# ── Helpers ───────────────────────────────────────────────────────────────────

def _parse_event(ev: Dict) -> Dict:
    """Normalize JSON fields on a raw DB row."""
    for field in ("affected_assets", "source_refs", "relevant_indicators"):
        val = ev.get(field)
        if isinstance(val, str):
            try:
                ev[field] = json.loads(val) if val else []
            except Exception:
                ev[field] = []
        elif val is None:
            ev[field] = []
    return ev


# ── List / filter ─────────────────────────────────────────────────────────────

_SORT_COLS = {
    "date":             "me.start_date",
    "score":            "me.impact_score",
    "name":             "me.name",
    "instrument_score": "COALESCE(ii.adjusted_score, ii.impact_score)",
}

@router.get("/db-status")
def db_status() -> Dict[str, Any]:
    """Quick health-check of all tables needed for market event generation."""
    from services.database import get_conn
    conn = get_conn()
    status: Dict[str, Any] = {}

    tables = {
        "market_events":        "SELECT COUNT(*), MAX(created_at) FROM market_events",
        "economic_events":      "SELECT COUNT(*), MAX(fetched_at) FROM economic_events",
        "macro_gauge_snapshots":"SELECT COUNT(*), MAX(snapshot_date) FROM macro_gauge_snapshots",
        "macro_regime_history": "SELECT COUNT(*), MAX(timestamp) FROM macro_regime_history",
        "institutional_reports":"SELECT COUNT(*), MAX(fetch_date) FROM institutional_reports",
        "ai_desks":             "SELECT COUNT(*), NULL FROM ai_desks",
    }
    for table, sql in tables.items():
        try:
            row = conn.execute(sql).fetchone()
            status[table] = {"count": row[0], "latest": row[1]}
        except Exception as e:
            status[table] = {"count": 0, "latest": None, "error": str(e)}

    # Check market_events columns
    existing_cols = {row[1] for row in conn.execute("PRAGMA table_info(market_events)").fetchall()}
    required_cols = ["sub_type", "origin", "source_refs", "actual_value", "expected_value", "surprise_pct"]
    status["market_events"]["columns_ok"] = all(c in existing_cols for c in required_cols)
    status["market_events"]["missing_columns"] = [c for c in required_cols if c not in existing_cols]

    # Summary of ai_desks types
    try:
        desks = conn.execute("SELECT type, active FROM ai_desks").fetchall()
        status["ai_desks"]["desks"] = [{"type": r[0], "active": bool(r[1])} for r in desks]
    except Exception:
        pass

    conn.close()
    return status


@router.get("")
def list_events(
    # text / category / level
    search:    Optional[str]  = Query(None),
    category:  Optional[str]  = Query(None),
    level:     Optional[str]  = Query(None),
    origin:    Optional[str]  = Query(None),
    # event date range (start_date)
    date_from: Optional[str]  = Query(None),
    date_to:   Optional[str]  = Query(None),
    # generation date range (created_at)
    gen_date_from: Optional[str] = Query(None, description="Filter by generation date (created_at) from"),
    gen_date_to:   Optional[str] = Query(None, description="Filter by generation date (created_at) to"),
    # global impact score
    min_score: float           = Query(0.0, ge=0.0, le=1.0),
    # instrument impact filter
    instrument:            Optional[str]  = Query(None, description="Filter by impact on this ticker"),
    min_instrument_score:  float          = Query(0.0, ge=0.0, le=1.0),
    instrument_direction:  Optional[str]  = Query(None, description="bullish|bearish|neutral"),
    # evaluation state
    evaluated: Optional[bool]  = Query(None),
    # sort
    sort_by:   str             = Query("date",  description="date|score|name|instrument_score"),
    sort_dir:  str             = Query("desc",  description="asc|desc"),
    # pagination
    limit:     int             = Query(200, ge=1, le=1000),
    offset:    int             = Query(0,   ge=0),
) -> Dict[str, Any]:
    from services.database import get_conn

    conn = get_conn()

    # Decide join type: INNER when filtering by instrument, LEFT otherwise
    join_type = "INNER" if instrument else "LEFT"
    join_clause = (
        f"{join_type} JOIN instrument_impacts ii "
        "ON ii.source_type='event' AND ii.source_id=me.id"
        + (f" AND ii.instrument_id='{instrument}'" if instrument else "")
    )

    where_parts: List[str] = []
    params:      List[Any] = []

    if search:
        where_parts.append("(me.name LIKE ? OR me.description LIKE ? OR me.sub_type LIKE ?)")
        like = f"%{search}%"
        params += [like, like, like]
    if category:
        where_parts.append("me.category=?")
        params.append(category)
    if level:
        where_parts.append("me.level=?")
        params.append(level)
    if origin:
        where_parts.append("me.origin=?")
        params.append(origin)
    if date_from:
        where_parts.append("me.start_date>=?")
        params.append(date_from)
    if date_to:
        where_parts.append("me.start_date<=?")
        params.append(date_to)
    if gen_date_from:
        where_parts.append("date(me.created_at)>=?")
        params.append(gen_date_from)
    if gen_date_to:
        where_parts.append("date(me.created_at)<=?")
        params.append(gen_date_to)
    if min_score > 0:
        where_parts.append("me.impact_score>=?")
        params.append(min_score)
    if instrument and min_instrument_score > 0:
        where_parts.append("COALESCE(ii.adjusted_score, ii.impact_score)>=?")
        params.append(min_instrument_score)
    if instrument and instrument_direction:
        where_parts.append("ii.direction=?")
        params.append(instrument_direction)
    if evaluated is True:
        where_parts.append(
            "EXISTS (SELECT 1 FROM instrument_impacts x WHERE x.source_type='event' AND x.source_id=me.id)"
        )
    if evaluated is False:
        where_parts.append(
            "NOT EXISTS (SELECT 1 FROM instrument_impacts x WHERE x.source_type='event' AND x.source_id=me.id)"
        )

    where_sql = ("WHERE " + " AND ".join(where_parts)) if where_parts else ""

    sort_col = _SORT_COLS.get(sort_by, "me.start_date")
    if sort_by == "instrument_score" and not instrument:
        sort_col = "me.impact_score"
    order_sql = f"ORDER BY {sort_col} {'DESC' if sort_dir == 'desc' else 'ASC'}"

    # Always GROUP BY — the LEFT JOIN on instrument_impacts creates duplicate rows
    # when an event has multiple impacts (one row per impact without GROUP BY)
    group_sql = "GROUP BY me.id"

    # Extra columns: evaluated flag + optional instrument score
    evaluated_col = (
        ", (CASE WHEN EXISTS ("
        "SELECT 1 FROM instrument_impacts _ei "
        "WHERE _ei.source_type='event' AND _ei.source_id=me.id"
        ") THEN 1 ELSE 0 END) as evaluated"
    )
    inst_col = (
        ", MAX(COALESCE(ii.adjusted_score, ii.impact_score)) as inst_score"
        ", ii.direction as inst_direction"
    ) if instrument else ", NULL as inst_score, NULL as inst_direction"

    count_sql = f"""
        SELECT COUNT(DISTINCT me.id) FROM market_events me {join_clause} {where_sql}
    """
    data_sql = f"""
        SELECT me.* {evaluated_col} {inst_col}
        FROM market_events me {join_clause}
        {where_sql} {group_sql} {order_sql}
        LIMIT ? OFFSET ?
    """

    try:
        total = conn.execute(count_sql, params).fetchone()[0]
        rows  = conn.execute(data_sql, params + [limit, offset]).fetchall()
    finally:
        conn.close()

    events = []
    for row in rows:
        ev = _parse_event(dict(row))
        if instrument and ev.get("inst_score") is not None:
            ev["instrument_filter"] = {
                "ticker": instrument,
                "score":  round(float(ev.pop("inst_score")), 3),
                "direction": ev.pop("inst_direction", None),
            }
        else:
            ev.pop("inst_score", None)
            ev.pop("inst_direction", None)
        events.append(ev)

    return {"total": total, "offset": offset, "limit": limit, "events": events}


# ── CRUD ──────────────────────────────────────────────────────────────────────

@router.post("", status_code=201)
def create_event(body: EventCreate) -> Dict[str, Any]:
    from services.database import save_market_event
    data = body.dict()
    data["source_refs"] = [r.dict() for r in body.source_refs]
    data.setdefault("origin", "manual")
    new_id = save_market_event(data)
    return {"id": new_id, "status": "created"}


@router.get("/{event_id}")
def get_event(event_id: int) -> Dict[str, Any]:
    from services.database import get_all_market_events, get_impacts_for_source
    events = get_all_market_events()
    ev = next((e for e in events if e["id"] == event_id), None)
    if not ev:
        raise HTTPException(404, f"Event {event_id} not found")
    ev = _parse_event(ev)
    impacts = get_impacts_for_source("event", event_id)
    ev["impacts"] = impacts
    ev["evaluated"] = len(impacts) > 0
    return ev


@router.put("/{event_id}")
def update_event(event_id: int, body: EventUpdate) -> Dict[str, Any]:
    from services.database import update_market_event
    data = body.dict()
    data["source_refs"] = [r.dict() for r in body.source_refs]
    ok = update_market_event(event_id, data)
    if not ok:
        raise HTTPException(404, "Event not found")
    return {"status": "updated"}


@router.delete("/{event_id}")
def delete_event(event_id: int) -> Dict[str, Any]:
    from services.database import delete_market_event, delete_impacts_for_source
    delete_impacts_for_source("event", event_id)
    delete_market_event(event_id)
    return {"status": "deleted"}


# ── AI evaluation ─────────────────────────────────────────────────────────────

@router.post("/{event_id}/evaluate")
def evaluate_event(
    event_id: int,
    force: bool = Query(False),
) -> Dict[str, Any]:
    """Trigger AI impact evaluation — evaluates instrument-level impacts."""
    from services.impact_service import evaluate_event_impacts
    try:
        result = evaluate_event_impacts(event_id, force=force)
        if "error" in result:
            raise HTTPException(400, result["error"])
        return result
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"[market_events] evaluate {event_id} failed: {e}")
        raise HTTPException(500, str(e))


@router.post("/bulk-evaluate")
def bulk_evaluate(
    event_ids: List[int],
    force: bool = Query(False),
) -> Dict[str, Any]:
    from services.impact_service import evaluate_event_impacts
    results, errors = [], []
    for eid in event_ids[:30]:
        try:
            r = evaluate_event_impacts(eid, force=force)
            if "error" not in r:
                results.append({"event_id": eid, "n_instruments": r.get("n_instruments", 0)})
            else:
                errors.append({"event_id": eid, "error": r["error"]})
        except Exception as e:
            errors.append({"event_id": eid, "error": str(e)})
    return {"evaluated": len(results), "errors": len(errors), "results": results, "error_details": errors}


# ── Instrument impacts ────────────────────────────────────────────────────────

@router.get("/{event_id}/impacts")
def get_impacts(event_id: int) -> List[Dict[str, Any]]:
    from services.database import get_impacts_for_source
    return get_impacts_for_source("event", event_id)


@router.post("/{event_id}/impacts", status_code=201)
def add_impact(event_id: int, body: InstrumentImpactCreate) -> Dict[str, Any]:
    """Manually add an instrument impact (not AI-generated)."""
    from services.database import get_all_market_events, save_instrument_impacts
    events = get_all_market_events()
    ev = next((e for e in events if e["id"] == event_id), None)
    if not ev:
        raise HTTPException(404, "Event not found")
    save_instrument_impacts([{
        "source_type":   "event",
        "source_id":     event_id,
        "source_name":   ev["name"],
        "source_date":   ev["start_date"],
        "category_name": ev.get("sub_type") or ev.get("category", ""),
        "instrument_id": body.instrument_id,
        "impact_score":  body.impact_score,
        "direction":     body.direction,
        "rationale":     body.rationale,
        "confidence":    body.confidence,
        "ai_generated":  0,
    }])
    return {"status": "added"}


@router.put("/{event_id}/impacts/{impact_id}")
def adjust_impact(event_id: int, impact_id: int, body: InstrumentImpactAdjust) -> Dict[str, Any]:
    from services.database import update_impact_adjustment
    ok = update_impact_adjustment(impact_id, body.adjusted_score, body.adjusted_direction, body.override_rationale)
    if not ok:
        raise HTTPException(404, f"Impact {impact_id} not found")
    return {"id": impact_id, "status": "adjusted"}


@router.delete("/{event_id}/impacts/{impact_id}")
def delete_impact(event_id: int, impact_id: int) -> Dict[str, Any]:
    from services.database import get_conn
    conn = get_conn()
    try:
        conn.execute("DELETE FROM instrument_impacts WHERE id=? AND source_id=?", (impact_id, event_id))
        conn.commit()
    finally:
        conn.close()
    return {"status": "deleted"}