"""
Isolated Feedback & Suggestions Database Service for FreeExile.
Manages a dedicated database to collect bug reports, gameplay suggestions,
balance adjustments, and system diagnostics from players.
Decoupled completely from game state and financial ledger databases.
"""

import os
import sqlite3
import json
import uuid
import time
from contextlib import contextmanager
from typing import Dict, List, Optional, Any, Tuple
from dataclasses import dataclass, asdict


@dataclass
class SystemDiagnosticsDTO:
    client_platform: str = "iOS"
    os_version: str = "iOS 18.2"
    device_model: str = "iPhone 16 Pro Max"
    current_fps: float = 120.0
    current_ping_ms: int = 18
    current_zone_name: str = "Cổ Trấn Huyết Lân"
    player_x: float = 0.0
    player_y: float = 0.0
    player_z: float = 0.0
    game_build_version: str = "v2.0-Authoritative"


@dataclass
class FeedbackRecord:
    feedback_id: str
    player_id: int
    player_name: str
    category: str        # BUG_REPORT, GAMEPLAY_SUGGESTION, BALANCE_ADJUSTMENT, LOCALIZATION, OTHER
    title: str
    content: str
    diagnostics: Dict[str, Any]
    status: str          # NEW, UNDER_REVIEW, PLANNED, RESOLVED, REJECTED
    admin_response: str = ""
    created_at_ms: int = 0
    updated_at_ms: int = 0


class FeedbackService:
    def __init__(self, db_path: Optional[str] = None):
        if not db_path:
            db_path = os.environ.get("FEEDBACK_DB_PATH")
        if not db_path:
            base_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..", "data"))
            os.makedirs(base_dir, exist_ok=True)
            db_path = os.path.join(base_dir, "feedback_store.db")

        self.db_path = db_path
        self._init_database()

    @contextmanager
    def _get_connection(self):
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        try:
            yield conn
        finally:
            conn.close()

    def _init_database(self) -> None:
        """Initializes the dedicated player feedback schema."""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS player_feedback (
                    feedback_id TEXT PRIMARY KEY,
                    player_id INTEGER NOT NULL,
                    player_name TEXT NOT NULL,
                    category TEXT NOT NULL,
                    title TEXT NOT NULL,
                    content TEXT NOT NULL,
                    diagnostics_json TEXT NOT NULL,
                    status TEXT NOT NULL DEFAULT 'NEW',
                    admin_response TEXT DEFAULT '',
                    created_at_ms INTEGER NOT NULL,
                    updated_at_ms INTEGER NOT NULL
                )
            """)
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_feedback_status ON player_feedback(status)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_feedback_category ON player_feedback(category)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_feedback_player ON player_feedback(player_id)")
            conn.commit()

    def submit_feedback(
        self,
        player_id: int,
        player_name: str,
        category: str,
        title: str,
        content: str,
        diagnostics: Optional[SystemDiagnosticsDTO] = None
    ) -> Tuple[bool, str, str]:
        """
        Submits new player feedback into the dedicated database.
        Returns: (success, feedback_id, message)
        """
        title = title.strip()
        content = content.strip()

        if not title:
            return False, "", "Tiêu đề góp ý không được để trống!"
        if len(content) < 10:
            return False, "", "Nội dung góp ý phải có tối thiểu 10 ký tự!"

        feedback_id = f"fb_{int(time.time())}_{uuid.uuid4().hex[:8]}"
        now_ms = int(time.time() * 1000)
        diag_dict = asdict(diagnostics) if diagnostics else asdict(SystemDiagnosticsDTO())

        try:
            with self._get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    INSERT INTO player_feedback (
                        feedback_id, player_id, player_name, category,
                        title, content, diagnostics_json, status,
                        admin_response, created_at_ms, updated_at_ms
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, 'NEW', '', ?, ?)
                """, (
                    feedback_id, player_id, player_name, category,
                    title, content, json.dumps(diag_dict, ensure_ascii=False),
                    now_ms, now_ms
                ))
                conn.commit()

            ack_msg = "Cảm ơn đại hiệp đã gửi góp ý! Ban Quản Trị FreeExile đã ghi nhận vào sổ bộ độc lập."
            return True, feedback_id, ack_msg

        except Exception as e:
            return False, "", f"Lỗi hệ thống khi lưu góp ý: {str(e)}"

    def get_feedback_by_id(self, feedback_id: str) -> Optional[FeedbackRecord]:
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM player_feedback WHERE feedback_id = ?", (feedback_id,))
            row = cursor.fetchone()
            if not row:
                return None

            return FeedbackRecord(
                feedback_id=row["feedback_id"],
                player_id=row["player_id"],
                player_name=row["player_name"],
                category=row["category"],
                title=row["title"],
                content=row["content"],
                diagnostics=json.loads(row["diagnostics_json"]),
                status=row["status"],
                admin_response=row["admin_response"],
                created_at_ms=row["created_at_ms"],
                updated_at_ms=row["updated_at_ms"]
            )

    def list_feedbacks(
        self,
        status: Optional[str] = None,
        category: Optional[str] = None,
        limit: int = 50,
        offset: int = 0
    ) -> List[FeedbackRecord]:
        query = "SELECT * FROM player_feedback WHERE 1=1"
        params: List[Any] = []

        if status:
            query += " AND status = ?"
            params.append(status)
        if category:
            query += " AND category = ?"
            params.append(category)

        query += " ORDER BY created_at_ms DESC LIMIT ? OFFSET ?"
        params.extend([limit, offset])

        results: List[FeedbackRecord] = []
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            for row in cursor.fetchall():
                results.append(FeedbackRecord(
                    feedback_id=row["feedback_id"],
                    player_id=row["player_id"],
                    player_name=row["player_name"],
                    category=row["category"],
                    title=row["title"],
                    content=row["content"],
                    diagnostics=json.loads(row["diagnostics_json"]),
                    status=row["status"],
                    admin_response=row["admin_response"],
                    created_at_ms=row["created_at_ms"],
                    updated_at_ms=row["updated_at_ms"]
                ))
        return results

    def update_feedback_status(
        self,
        feedback_id: str,
        new_status: str,
        admin_response: str = ""
    ) -> bool:
        """
        Updates feedback status (UNDER_REVIEW, PLANNED, RESOLVED, REJECTED) and logs admin notes.
        """
        valid_statuses = {"NEW", "UNDER_REVIEW", "PLANNED", "RESOLVED", "REJECTED"}
        if new_status not in valid_statuses:
            return False

        now_ms = int(time.time() * 1000)
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE player_feedback
                SET status = ?, admin_response = ?, updated_at_ms = ?
                WHERE feedback_id = ?
            """, (new_status, admin_response, now_ms, feedback_id))
            conn.commit()
            return cursor.rowcount > 0

    def get_feedback_metrics(self) -> Dict[str, Any]:
        """Provides statistics for GM / Product Team dashboards."""
        metrics: Dict[str, Any] = {"by_status": {}, "by_category": {}, "total": 0}
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT status, COUNT(*) as cnt FROM player_feedback GROUP BY status")
            for row in cursor.fetchall():
                metrics["by_status"][row["status"]] = row["cnt"]
                metrics["total"] += row["cnt"]

            cursor.execute("SELECT category, COUNT(*) as cnt FROM player_feedback GROUP BY category")
            for row in cursor.fetchall():
                metrics["by_category"][row["category"]] = row["cnt"]

        return metrics