import sqlite3
import json
import logging
import datetime
from pathlib import Path
from fastapi import APIRouter, HTTPException, Query
from pydantic import BaseModel
from typing import Dict, Any

from config import DEFAULT_MODEL
from common.llm_factory import create_llm

from api.autotrain.autotrain_route import get_db, _ai_generate_rule

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/autotrain/pipeline", tags=["Auto-Train Pipeline"])

# Helpers for Prompt File
def read_prompt_module(module_name: str) -> str:
    path = Path(__file__).resolve().parents[3] / "agent" / "prompt_module" / module_name
    if path.exists():
        return path.read_text(encoding="utf-8")
    return ""

def append_rule_to_prompt(module_name: str, rule_text: str) -> bool:
    path = Path(__file__).resolve().parents[3] / "agent" / "prompt_module" / module_name
    if not path.exists():
        return False
    
    # Backup
    timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_path = path.with_name(f"{path.stem}.{timestamp}.backup{path.suffix}")
    backup_path.write_text(path.read_text(encoding="utf-8"), encoding="utf-8")
    
    # Append
    with path.open("a", encoding="utf-8") as f:
        f.write("\n\n" + rule_text)
    return True

def rollback_prompt(module_name: str) -> bool:
    path = Path(__file__).resolve().parents[3] / "agent" / "prompt_module" / module_name
    backups = list(path.parent.glob(f"{path.stem}.*.backup{path.suffix}"))
    if not backups:
        return False
    latest_backup = sorted(backups)[-1]
    path.write_text(latest_backup.read_text(encoding="utf-8"), encoding="utf-8")
    return True

def get_prompt_version() -> str:
    import hashlib
    import glob
    hasher = hashlib.md5()
    files = glob.glob("agent/prompt_module/*.txt")
    for f in sorted(files):
        try:
            with open(f, "rb") as file:
                hasher.update(file.read())
        except Exception:
            pass
    return "v-" + hasher.hexdigest()[:6]

@router.post("/run")
async def run_pipeline():
    run_id = f"PL-{datetime.datetime.now().strftime('%Y%m%d%H%M%S')}"
    
    conn = get_db()
    cur = conn.cursor()
    cur.execute("INSERT INTO pipeline_runs (run_id, started_at) VALUES (?, ?)", (run_id, datetime.datetime.now().isoformat()))
    conn.commit()
    
    try:
        cur.execute("SELECT * FROM feedbacks WHERE status = 'pending' LIMIT 10")
        feedbacks = [dict(row) for row in cur.fetchall()]
        
        rules_generated = 0
        rules_approved = 0
        
        for fb in feedbacks:
            try:
                rule = await _ai_generate_rule(fb)
                rules_generated += 1
                if rule.get("confidence", 0) >= 0.7:
                    rules_approved += 1
                    new_rule = rule.get("new_rule")
                    if new_rule:
                        append_rule_to_prompt("02_rules.txt", new_rule)
                        
                cur.execute("UPDATE feedbacks SET status = 'processed' WHERE id = ?", (fb["id"],))
            except Exception as e:
                logger.error(f"Rule gen error for FB {fb.get('id')}: {e}")
                
        cur.execute("""
            UPDATE pipeline_runs 
            SET status = 'success', completed_at = ?, feedbacks_processed = ?, rules_generated = ?, rules_approved = ?, rules_deployed = ?
            WHERE run_id = ?
        """, (datetime.datetime.now().isoformat(), len(feedbacks), rules_generated, rules_approved, rules_approved, run_id))
        conn.commit()
        
        return {"status": "success", "run_id": run_id, "feedbacks": len(feedbacks), "rules": rules_generated}
    except Exception as e:
        logger.error(f"Pipeline error: {e}", exc_info=True)
        cur.execute("UPDATE pipeline_runs SET status = 'failed' WHERE run_id = ?", (run_id,))
        conn.commit()
        return {"status": "error", "message": str(e)}
    finally:
        conn.close()

@router.get("/history")
async def get_history(limit: int = 50):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT * FROM pipeline_runs ORDER BY started_at DESC LIMIT ?", (limit,))
    rows = [dict(r) for r in cur.fetchall()]
    conn.close()
    return {"runs": rows}

@router.get("/history/{run_id}")
async def get_history_detail(run_id: str):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT * FROM pipeline_runs WHERE run_id = ?", (run_id,))
    row = cur.fetchone()
    conn.close()
    if not row:
        raise HTTPException(status_code=404, detail="Run not found")
    return dict(row)

@router.post("/rollback/{run_id}")
async def rollback_pipeline(run_id: str):
    success = rollback_prompt("02_rules.txt")
    if success:
        return {"status": "success"}
    return {"status": "error", "message": "No backups found"}
