"""
Shared database module for task management system.
Provides persistent storage using SQLite with thread-safe operations.
"""

import sqlite3
import json
import os
import threading
from datetime import datetime
from typing import Dict, List, Optional, Any
from contextlib import contextmanager
from pathlib import Path

from .models import Task, Project, HumanResource, AgentResource, TaskStatus, TaskPriority


class DatabaseManager:
    """Thread-safe database manager for task management system."""
    
    def __init__(self, db_path: Optional[str] = None):
        if db_path is None:
            # Use user's home directory for database
            home_dir = Path.home()
            db_dir = home_dir / ".task_manager_mcp"
            db_dir.mkdir(exist_ok=True)
            db_path = str(db_dir / "tasks.db")
        
        self.db_path = db_path
        self._local = threading.local()
        self._lock = threading.Lock()
        self._initialize_database()
    
    def _get_connection(self) -> sqlite3.Connection:
        """Get thread-local database connection."""
        if not hasattr(self._local, 'connection'):
            self._local.connection = sqlite3.connect(
                self.db_path,
                check_same_thread=False,
                timeout=30.0
            )
            self._local.connection.execute("PRAGMA journal_mode=WAL")
            self._local.connection.execute("PRAGMA foreign_keys=OFF")
            self._local.connection.row_factory = sqlite3.Row
        return self._local.connection
    
    @contextmanager
    def _transaction(self):
        """Context manager for database transactions."""
        conn = self._get_connection()
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
    
    def _initialize_database(self):
        """Initialize database schema."""
        with self._transaction() as conn:
            # Tasks table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS tasks (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    description TEXT,
                    status TEXT DEFAULT 'todo',
                    priority INTEGER DEFAULT 3,
                    time_cost REAL DEFAULT 1.0,
                    actual_time_spent REAL DEFAULT 0.0,
                    progress REAL DEFAULT 0.0,
                    importance INTEGER DEFAULT 3,
                    benefit INTEGER DEFAULT 5,
                    marginal_cost INTEGER DEFAULT 3,
                    project_id TEXT,
                    assignee TEXT,
                    dependencies TEXT, -- JSON array
                    subtasks TEXT, -- JSON array
                    due_date TEXT, -- ISO format
                    estimated_completion TEXT, -- ISO format
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # Projects table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS projects (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    description TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # Resources table
            conn.execute("""
                CREATE TABLE IF NOT EXISTS resources (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    type TEXT NOT NULL, -- 'human' or 'agent'
                    skills TEXT, -- JSON array
                    load_value REAL DEFAULT 0.0,
                    status TEXT DEFAULT 'available',
                    cost_per_hour REAL,
                    cost_per_task REAL,
                    system_prompt TEXT,
                    tools TEXT, -- JSON array
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # Create indexes
            conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_project_id ON tasks(project_id)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_assignee ON tasks(assignee)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_resources_type ON resources(type)")
    
    def _safe_json_loads(self, data: str) -> Any:
        """Safely load JSON data."""
        if not data:
            return []
        try:
            return json.loads(data)
        except (json.JSONDecodeError, TypeError):
            return []
    
    def _safe_json_dumps(self, data: Any) -> str:
        """Safely dump JSON data with proper encoding."""
        if not data:
            return "[]"
        try:
            return json.dumps(data, ensure_ascii=False, default=str)
        except (TypeError, ValueError):
            return "[]"
    
    # Task operations
    def create_task(self, task: Task) -> bool:
        """Create a new task."""
        try:
            with self._transaction() as conn:
                conn.execute("""
                    INSERT INTO tasks (
                        id, name, description, status, priority, time_cost,
                        actual_time_spent, progress, importance, benefit, marginal_cost,
                        project_id, assignee, dependencies, subtasks, due_date,
                        estimated_completion, updated_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    task.id, task.name, task.description, task.status.value,
                    task.priority, task.time_cost, task.actual_time_spent,
                    task.progress, task.importance, task.benefit, task.marginal_cost,
                    task.project_id, task.assignee,
                    self._safe_json_dumps(task.dependencies),
                    self._safe_json_dumps(task.subtasks),
                    task.due_date.isoformat() if task.due_date else None,
                    task.estimated_completion.isoformat() if task.estimated_completion else None,
                    datetime.now().isoformat()
                ))
            return True
        except Exception as e:
            print(f"Error creating task: {e}")
            return False
    
    def get_task(self, task_id: str) -> Optional[Task]:
        """Get a task by ID."""
        try:
            conn = self._get_connection()
            row = conn.execute("SELECT * FROM tasks WHERE id = ?", (task_id,)).fetchone()
            if not row:
                return None
            
            return Task(
                id=row['id'],
                name=row['name'],
                description=row['description'] or "",
                status=TaskStatus(row['status']),
                priority=TaskPriority(row['priority']),
                time_cost=row['time_cost'],
                actual_time_spent=row['actual_time_spent'],
                progress=row['progress'],
                importance=row['importance'],
                benefit=row['benefit'],
                marginal_cost=row['marginal_cost'],
                project_id=row['project_id'],
                assignee=row['assignee'],
                dependencies=self._safe_json_loads(row['dependencies']),
                subtasks=self._safe_json_loads(row['subtasks']),
                due_date=datetime.fromisoformat(row['due_date']) if row['due_date'] else None,
                estimated_completion=datetime.fromisoformat(row['estimated_completion']) if row['estimated_completion'] else None,
                created_at=datetime.fromisoformat(row['created_at']),
                updated_at=datetime.fromisoformat(row['updated_at'])
            )
        except Exception as e:
            print(f"Error getting task: {e}")
            return None
    
    def update_task(self, task: Task) -> bool:
        """Update an existing task."""
        try:
            with self._transaction() as conn:
                conn.execute("""
                    UPDATE tasks SET
                        name = ?, description = ?, status = ?, priority = ?,
                        time_cost = ?, actual_time_spent = ?, progress = ?,
                        importance = ?, benefit = ?, marginal_cost = ?,
                        project_id = ?, assignee = ?, dependencies = ?,
                        subtasks = ?, due_date = ?, estimated_completion = ?,
                        updated_at = ?
                    WHERE id = ?
                """, (
                    task.name, task.description, task.status.value, task.priority,
                    task.time_cost, task.actual_time_spent, task.progress,
                    task.importance, task.benefit, task.marginal_cost,
                    task.project_id, task.assignee,
                    self._safe_json_dumps(task.dependencies),
                    self._safe_json_dumps(task.subtasks),
                    task.due_date.isoformat() if task.due_date else None,
                    task.estimated_completion.isoformat() if task.estimated_completion else None,
                    datetime.now().isoformat(),
                    task.id
                ))
            return True
        except Exception as e:
            print(f"Error updating task: {e}")
            return False
    
    def list_tasks(self, project_id: Optional[str] = None, assignee: Optional[str] = None) -> List[Task]:
        """List tasks with optional filtering."""
        try:
            conn = self._get_connection()
            query = "SELECT * FROM tasks WHERE 1=1"
            params = []
            
            if project_id:
                query += " AND project_id = ?"
                params.append(project_id)
            
            if assignee:
                query += " AND assignee = ?"
                params.append(assignee)
            
            query += " ORDER BY created_at DESC"
            
            rows = conn.execute(query, params).fetchall()
            tasks = []
            
            for row in rows:
                task = Task(
                    id=row['id'],
                    name=row['name'],
                    description=row['description'] or "",
                    status=TaskStatus(row['status']),
                    priority=TaskPriority(row['priority']),
                    time_cost=row['time_cost'],
                    actual_time_spent=row['actual_time_spent'],
                    progress=row['progress'],
                    importance=row['importance'],
                    benefit=row['benefit'],
                    marginal_cost=row['marginal_cost'],
                    project_id=row['project_id'],
                    assignee=row['assignee'],
                    dependencies=self._safe_json_loads(row['dependencies']),
                    subtasks=self._safe_json_loads(row['subtasks']),
                    due_date=datetime.fromisoformat(row['due_date']) if row['due_date'] else None,
                    estimated_completion=datetime.fromisoformat(row['estimated_completion']) if row['estimated_completion'] else None,
                    created_at=datetime.fromisoformat(row['created_at']),
                    updated_at=datetime.fromisoformat(row['updated_at'])
                )
                tasks.append(task)
            
            return tasks
        except Exception as e:
            print(f"Error listing tasks: {e}")
            return []


# Global database instance
_db_instance = None
_db_lock = threading.Lock()

def get_database() -> DatabaseManager:
    """Get global database instance (singleton)."""
    global _db_instance
    if _db_instance is None:
        with _db_lock:
            if _db_instance is None:
                _db_instance = DatabaseManager()
    return _db_instance