"""
SQLite database layer for Projects feature.
Manages projects and validation history.
"""

import sqlite3
import json
import logging
import os
from datetime import datetime
from pathlib import Path
from typing import List, Dict, Optional, Any

logger = logging.getLogger(__name__)


def get_database_path():
    """Get writable database path based on environment."""
    # Check if running on Hugging Face Spaces
    if os.environ.get("SPACE_ID"):
        # Use /tmp for HF Spaces (writable directory)
        db_dir = "/tmp"
    else:
        # Local development - use current directory
        db_dir = "."
    
    return os.path.join(db_dir, "projects.db")


class ProjectDatabase:
    """Database manager for projects and validations."""
    
    def __init__(self, db_path: str = None):
        """Initialize database connection and create tables if needed."""
        self.db_path = db_path or get_database_path()
        self._init_database()
    
    def _init_database(self):
        """Create tables if they don't exist."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # Projects table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS projects (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    description TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)
            
            # Validations table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS validations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    project_id INTEGER,
                    validation_type TEXT NOT NULL,
                    template_key TEXT,
                    filename TEXT,
                    status TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    results_json TEXT,
                    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
                )
            """)
            
            conn.commit()
            logger.info(f"Database initialized at {self.db_path}")
    
    # Project CRUD operations
    
    def create_project(self, name: str, description: str = "") -> int:
        """Create a new project."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute(
                    "INSERT INTO projects (name, description) VALUES (?, ?)",
                    (name, description)
                )
                conn.commit()
                project_id = cursor.lastrowid
                logger.info(f"Created project: {name} (ID: {project_id})")
                return project_id
            except sqlite3.IntegrityError:
                raise ValueError(f"Project '{name}' already exists")
    
    def list_projects(self) -> List[Dict[str, Any]]:
        """List all projects."""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("""
                SELECT p.*, COUNT(v.id) as validation_count
                FROM projects p
                LEFT JOIN validations v ON p.id = v.project_id
                GROUP BY p.id
                ORDER BY p.created_at DESC
            """)
            projects = [dict(row) for row in cursor.fetchall()]
            return projects
    
    def get_project(self, project_id: int) -> Optional[Dict[str, Any]]:
        """Get a specific project by ID."""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("""
                SELECT p.*, COUNT(v.id) as validation_count
                FROM projects p
                LEFT JOIN validations v ON p.id = v.project_id
                WHERE p.id = ?
                GROUP BY p.id
            """, (project_id,))
            row = cursor.fetchone()
            return dict(row) if row else None
    
    def delete_project(self, project_id: int) -> bool:
        """Delete a project and all associated validations."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM projects WHERE id = ?", (project_id,))
            conn.commit()
            deleted = cursor.rowcount > 0
            if deleted:
                logger.info(f"Deleted project ID: {project_id}")
            return deleted
    
    # Validation operations
    
    def save_validation(
        self,
        project_id: Optional[int],
        validation_type: str,
        filename: str,
        status: str,
        results: Dict[str, Any],
        template_key: Optional[str] = None
    ) -> int:
        """Save a validation result to a project."""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO validations 
                (project_id, validation_type, template_key, filename, status, results_json)
                VALUES (?, ?, ?, ?, ?, ?)
            """, (
                project_id,
                validation_type,
                template_key,
                filename,
                status,
                json.dumps(results)
            ))
            conn.commit()
            validation_id = cursor.lastrowid
            logger.info(f"Saved {validation_type} validation (ID: {validation_id}) to project {project_id}")
            return validation_id
    
    def get_project_validations(self, project_id: int) -> List[Dict[str, Any]]:
        """Get all validations for a specific project."""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("""
                SELECT * FROM validations
                WHERE project_id = ?
                ORDER BY created_at DESC
            """, (project_id,))
            validations = []
            for row in cursor.fetchall():
                validation = dict(row)
                # Parse JSON results
                if validation['results_json']:
                    validation['results'] = json.loads(validation['results_json'])
                    del validation['results_json']
                validations.append(validation)
            return validations
    
    def get_recent_validations(self, limit: int = 50) -> List[Dict[str, Any]]:
        """Get recent validations across all projects."""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            cursor.execute("""
                SELECT v.*, p.name as project_name
                FROM validations v
                LEFT JOIN projects p ON v.project_id = p.id
                ORDER BY v.created_at DESC
                LIMIT ?
            """, (limit,))
            validations = []
            for row in cursor.fetchall():
                validation = dict(row)
                if validation['results_json']:
                    validation['results'] = json.loads(validation['results_json'])
                    del validation['results_json']
                validations.append(validation)
            return validations


# Global database instance
db = ProjectDatabase()