"""
=========================================
CV6 AI Trading OS
Trade History — Service Layer
CRUD + PnL + Filter + CSV Export
=========================================
"""

import csv
import io
import time
from typing import List, Optional, Dict, Any

from sqlalchemy.orm import Session
from loguru import logger

from app.history.history_model import TradeHistory
from app.history.history_schemas import TradeCreate, TradeUpdate, PnLSummary


class TradeHistoryService:

    # ─── Create ─────────────────────────────────────────────────────

    def create(self, db: Session, data: TradeCreate) -> TradeHistory:
        record = TradeHistory(**data.model_dump())
        record.updated_at = time.time()
        db.add(record)
        db.commit()
        db.refresh(record)
        logger.info(f"[History] Created: id={record.id} {record.symbol} {record.status}")
        return record

    # ─── Read ────────────────────────────────────────────────────────

    def get_by_id(self, db: Session, trade_id: int) -> Optional[TradeHistory]:
        return db.query(TradeHistory).filter(TradeHistory.id == trade_id).first()

    def get_all(
        self,
        db: Session,
        status:  Optional[str]   = None,
        symbol:  Optional[str]   = None,
        broker:  Optional[str]   = None,
        from_ts: Optional[float] = None,
        to_ts:   Optional[float] = None,
        limit:   int             = 100,
        offset:  int             = 0,
    ) -> List[TradeHistory]:
        q = db.query(TradeHistory)
        if status:
            q = q.filter(TradeHistory.status == status.upper())
        if symbol:
            q = q.filter(TradeHistory.symbol == symbol.upper())
        if broker:
            q = q.filter(TradeHistory.broker == broker.lower())
        if from_ts:
            q = q.filter(TradeHistory.placed_at >= from_ts)
        if to_ts:
            q = q.filter(TradeHistory.placed_at <= to_ts)
        return q.order_by(TradeHistory.placed_at.desc()).offset(offset).limit(limit).all()

    def count(self, db: Session, status: Optional[str] = None) -> int:
        q = db.query(TradeHistory)
        if status:
            q = q.filter(TradeHistory.status == status.upper())
        return q.count()

    # ─── Update ──────────────────────────────────────────────────────

    def update(self, db: Session, trade_id: int, data: TradeUpdate) -> Optional[TradeHistory]:
        record = self.get_by_id(db, trade_id)
        if not record:
            return None
        updates = data.model_dump(exclude_none=True)
        updates["updated_at"] = time.time()
        for k, v in updates.items():
            setattr(record, k, v)
        db.commit()
        db.refresh(record)
        logger.info(f"[History] Updated: id={trade_id} fields={list(updates.keys())}")
        return record

    # ─── PnL Summary ─────────────────────────────────────────────────

    def pnl_summary(
        self,
        db: Session,
        symbol: Optional[str]    = None,
        broker: Optional[str]    = None,
        from_ts: Optional[float] = None,
        to_ts:   Optional[float] = None,
    ) -> PnLSummary:
        trades = self.get_all(db, status="EXECUTED", symbol=symbol,
                              broker=broker, from_ts=from_ts, to_ts=to_ts,
                              limit=100000)
        total_pnl    = sum(t.pnl for t in trades)
        total_charges= sum(t.charges for t in trades)
        wins  = [t for t in trades if t.pnl > 0]
        losses= [t for t in trades if t.pnl < 0]
        n = len(trades)
        return PnLSummary(
            total_trades=n,
            total_pnl=round(total_pnl, 2),
            total_charges=round(total_charges, 2),
            net_pnl=round(total_pnl - total_charges, 2),
            winning_trades=len(wins),
            losing_trades=len(losses),
            win_rate_pct=round(len(wins) / n * 100, 1) if n else 0.0,
            avg_win=round(sum(t.pnl for t in wins)   / len(wins),   2) if wins   else 0.0,
            avg_loss=round(sum(t.pnl for t in losses)/ len(losses), 2) if losses else 0.0,
        )

    # ─── CSV Export ──────────────────────────────────────────────────

    def export_csv(
        self,
        db: Session,
        status:  Optional[str]   = None,
        symbol:  Optional[str]   = None,
        from_ts: Optional[float] = None,
        to_ts:   Optional[float] = None,
    ) -> str:
        trades = self.get_all(db, status=status, symbol=symbol,
                              from_ts=from_ts, to_ts=to_ts, limit=100000)
        if not trades:
            return ""
        fields = [
            "id", "order_id", "broker", "symbol", "exchange", "trade_type",
            "status", "quantity", "price", "avg_price", "filled_qty",
            "product", "order_type", "pnl", "charges", "ai_signal",
            "ai_confidence", "strategy", "placed_at", "executed_at",
        ]
        buf = io.StringIO()
        w = csv.DictWriter(buf, fieldnames=fields, extrasaction="ignore")
        w.writeheader()
        for t in trades:
            w.writerow({f: getattr(t, f, "") for f in fields})
        return buf.getvalue()


    def clear_all(self, db: Session) -> int:
        """Delete all trade history records. Returns count deleted."""
        count = db.query(TradeHistory).count()
        db.query(TradeHistory).delete()
        db.commit()
        logger.info(f"[History] Cleared {count} records from trade history")
        return count


# Singleton
history_service = TradeHistoryService()