#![allow(dead_code)]

//! SQLite persistence layer for syntaxis data
//!
//! Stores and retrieves project configuration, checklist items,
//! phase transitions, security assessments, and tool configurations.

use serde::{Deserialize, Serialize};
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use sqlx::{FromRow, Pool, Sqlite};
use std::path::Path;
use thiserror::Error;

/// Database persistence errors
#[derive(Error, Debug)]
pub enum PersistenceError {
    #[error("Database error: {0}")]
    Database(#[from] sqlx::Error),

    #[error("Not found: {0}")]
    NotFound(String),

    #[error("Invalid data: {0}")]
    InvalidData(String),

    #[error("Migration error: {0}")]
    Migration(String),
}

/// Project metadata stored in database
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct ProjectRecord {
    pub id: String,
    pub name: String,
    pub description: String,
    pub project_type: String,
    pub current_phase: String,
    pub created_at: String,
    pub updated_at: String,
}

/// Checklist item record
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct ChecklistItemRecord {
    pub id: String,
    pub project_id: String,
    pub task_id: String,
    pub description: String,
    pub completed: bool,
    pub completed_at: Option<String>,
    pub created_at: String,
}

/// Phase transition history record
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct PhaseTransitionRecord {
    pub id: String,
    pub project_id: String,
    pub from_phase: String,
    pub to_phase: String,
    pub reason: Option<String>,
    pub transitioned_at: String,
}

/// Security assessment record
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct SecurityAssessmentRecord {
    pub id: String,
    pub project_id: String,
    pub profile: String,
    pub risk_level: String,
    pub findings_count: i32,
    pub assessed_at: String,
}

/// Tool configuration record
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct ToolConfigRecord {
    pub id: String,
    pub project_id: String,
    pub tool_name: String,
    pub enabled: bool,
    pub configured_at: String,
}

/// Database pool wrapper
pub struct Database {
    pool: Pool<Sqlite>,
}

impl Database {
    /// Create or connect to SQLite database
    pub async fn new(db_path: &Path) -> Result<Self, PersistenceError> {
        // Ensure parent directory exists
        if let Some(parent) = db_path.parent() {
            std::fs::create_dir_all(parent).map_err(|e| {
                PersistenceError::InvalidData(format!("Failed to create db directory: {}", e))
            })?;
        }

        let options = SqliteConnectOptions::new()
            .filename(db_path)
            .create_if_missing(true);

        let pool = SqlitePoolOptions::new()
            .max_connections(5)
            .connect_with(options)
            .await?;

        let db = Self { pool };
        db.initialize().await?;
        Ok(db)
    }

    /// Initialize database schema
    async fn initialize(&self) -> Result<(), PersistenceError> {
        // Projects table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS projects (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT,
                project_type TEXT NOT NULL,
                current_phase TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // Checklist items table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS checklist_items (
                id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                task_id TEXT NOT NULL,
                description TEXT NOT NULL,
                completed BOOLEAN NOT NULL DEFAULT 0,
                completed_at TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id)
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // Phase transitions table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS phase_transitions (
                id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                from_phase TEXT NOT NULL,
                to_phase TEXT NOT NULL,
                reason TEXT,
                transitioned_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id)
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // Security assessments table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS security_assessments (
                id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                profile TEXT NOT NULL,
                risk_level TEXT NOT NULL,
                findings_count INTEGER NOT NULL,
                assessed_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id)
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        // Tool configurations table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS tool_configs (
                id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                tool_name TEXT NOT NULL,
                enabled BOOLEAN NOT NULL,
                configured_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id)
            )
            "#,
        )
        .execute(&self.pool)
        .await?;

        Ok(())
    }

    // ==================== Projects ====================

    /// Save or update a project
    pub async fn save_project(&self, project: &ProjectRecord) -> Result<(), PersistenceError> {
        sqlx::query(
            r#"
            INSERT OR REPLACE INTO projects
            (id, name, description, project_type, current_phase, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(&project.id)
        .bind(&project.name)
        .bind(&project.description)
        .bind(&project.project_type)
        .bind(&project.current_phase)
        .bind(&project.created_at)
        .bind(&project.updated_at)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Get a project by ID
    pub async fn get_project(&self, project_id: &str) -> Result<ProjectRecord, PersistenceError> {
        sqlx::query_as::<_, ProjectRecord>(
            "SELECT id, name, description, project_type, current_phase, created_at, updated_at FROM projects WHERE id = ?"
        )
        .bind(project_id)
        .fetch_optional(&self.pool)
        .await?
        .ok_or_else(|| PersistenceError::NotFound(format!("Project {} not found", project_id)))
    }

    /// Get all projects
    pub async fn get_all_projects(&self) -> Result<Vec<ProjectRecord>, PersistenceError> {
        sqlx::query_as::<_, ProjectRecord>(
            "SELECT id, name, description, project_type, current_phase, created_at, updated_at FROM projects ORDER BY created_at DESC"
        )
        .fetch_all(&self.pool)
        .await
        .map_err(PersistenceError::from)
    }

    /// Delete a project
    pub async fn delete_project(&self, project_id: &str) -> Result<(), PersistenceError> {
        sqlx::query("DELETE FROM projects WHERE id = ?")
            .bind(project_id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    // ==================== Checklist Items ====================

    /// Save a checklist item
    pub async fn save_checklist_item(
        &self,
        item: &ChecklistItemRecord,
    ) -> Result<(), PersistenceError> {
        sqlx::query(
            r#"
            INSERT OR REPLACE INTO checklist_items
            (id, project_id, task_id, description, completed, completed_at, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(&item.id)
        .bind(&item.project_id)
        .bind(&item.task_id)
        .bind(&item.description)
        .bind(item.completed)
        .bind(&item.completed_at)
        .bind(&item.created_at)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Get checklist items for a project
    pub async fn get_checklist_items(
        &self,
        project_id: &str,
    ) -> Result<Vec<ChecklistItemRecord>, PersistenceError> {
        sqlx::query_as::<_, ChecklistItemRecord>(
            r#"
            SELECT id, project_id, task_id, description, completed, completed_at, created_at
            FROM checklist_items
            WHERE project_id = ?
            ORDER BY created_at DESC
            "#,
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await
        .map_err(PersistenceError::from)
    }

    /// Mark checklist item as completed
    pub async fn complete_checklist_item(
        &self,
        item_id: &str,
        completed_at: &str,
    ) -> Result<(), PersistenceError> {
        sqlx::query("UPDATE checklist_items SET completed = 1, completed_at = ? WHERE id = ?")
            .bind(completed_at)
            .bind(item_id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    // ==================== Phase Transitions ====================

    /// Record a phase transition
    pub async fn record_phase_transition(
        &self,
        transition: &PhaseTransitionRecord,
    ) -> Result<(), PersistenceError> {
        sqlx::query(
            r#"
            INSERT INTO phase_transitions
            (id, project_id, from_phase, to_phase, reason, transitioned_at)
            VALUES (?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(&transition.id)
        .bind(&transition.project_id)
        .bind(&transition.from_phase)
        .bind(&transition.to_phase)
        .bind(&transition.reason)
        .bind(&transition.transitioned_at)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Get phase transition history for a project
    pub async fn get_phase_history(
        &self,
        project_id: &str,
    ) -> Result<Vec<PhaseTransitionRecord>, PersistenceError> {
        sqlx::query_as::<_, PhaseTransitionRecord>(
            r#"
            SELECT id, project_id, from_phase, to_phase, reason, transitioned_at
            FROM phase_transitions
            WHERE project_id = ?
            ORDER BY transitioned_at DESC
            "#,
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await
        .map_err(PersistenceError::from)
    }

    // ==================== Security Assessments ====================

    /// Save security assessment
    pub async fn save_security_assessment(
        &self,
        assessment: &SecurityAssessmentRecord,
    ) -> Result<(), PersistenceError> {
        sqlx::query(
            r#"
            INSERT OR REPLACE INTO security_assessments
            (id, project_id, profile, risk_level, findings_count, assessed_at)
            VALUES (?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(&assessment.id)
        .bind(&assessment.project_id)
        .bind(&assessment.profile)
        .bind(&assessment.risk_level)
        .bind(assessment.findings_count)
        .bind(&assessment.assessed_at)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Get latest security assessment for a project
    pub async fn get_security_assessment(
        &self,
        project_id: &str,
    ) -> Result<SecurityAssessmentRecord, PersistenceError> {
        sqlx::query_as::<_, SecurityAssessmentRecord>(
            r#"
            SELECT id, project_id, profile, risk_level, findings_count, assessed_at
            FROM security_assessments
            WHERE project_id = ?
            ORDER BY assessed_at DESC
            LIMIT 1
            "#,
        )
        .bind(project_id)
        .fetch_optional(&self.pool)
        .await?
        .ok_or_else(|| {
            PersistenceError::NotFound(format!("No assessment found for project {}", project_id))
        })
    }

    // ==================== Tool Configurations ====================

    /// Save tool configuration
    pub async fn save_tool_config(
        &self,
        config: &ToolConfigRecord,
    ) -> Result<(), PersistenceError> {
        sqlx::query(
            r#"
            INSERT OR REPLACE INTO tool_configs
            (id, project_id, tool_name, enabled, configured_at)
            VALUES (?, ?, ?, ?, ?)
            "#,
        )
        .bind(&config.id)
        .bind(&config.project_id)
        .bind(&config.tool_name)
        .bind(config.enabled)
        .bind(&config.configured_at)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Get tool configurations for a project
    pub async fn get_tool_configs(
        &self,
        project_id: &str,
    ) -> Result<Vec<ToolConfigRecord>, PersistenceError> {
        sqlx::query_as::<_, ToolConfigRecord>(
            r#"
            SELECT id, project_id, tool_name, enabled, configured_at
            FROM tool_configs
            WHERE project_id = ?
            ORDER BY configured_at DESC
            "#,
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await
        .map_err(PersistenceError::from)
    }

    // ==================== Statistics ====================

    /// Get database statistics
    pub async fn get_stats(&self) -> Result<DatabaseStats, PersistenceError> {
        let projects = sqlx::query_scalar::<_, i32>("SELECT COUNT(*) FROM projects")
            .fetch_one(&self.pool)
            .await? as usize;

        let items = sqlx::query_scalar::<_, i32>("SELECT COUNT(*) FROM checklist_items")
            .fetch_one(&self.pool)
            .await? as usize;

        let transitions = sqlx::query_scalar::<_, i32>("SELECT COUNT(*) FROM phase_transitions")
            .fetch_one(&self.pool)
            .await? as usize;

        let assessments = sqlx::query_scalar::<_, i32>("SELECT COUNT(*) FROM security_assessments")
            .fetch_one(&self.pool)
            .await? as usize;

        let tools = sqlx::query_scalar::<_, i32>("SELECT COUNT(*) FROM tool_configs")
            .fetch_one(&self.pool)
            .await? as usize;

        Ok(DatabaseStats {
            projects,
            checklist_items: items,
            phase_transitions: transitions,
            security_assessments: assessments,
            tool_configs: tools,
        })
    }
}

/// Database statistics
#[derive(Debug, Clone, Serialize)]
pub struct DatabaseStats {
    pub projects: usize,
    pub checklist_items: usize,
    pub phase_transitions: usize,
    pub security_assessments: usize,
    pub tool_configs: usize,
}

#[cfg(test)]
mod tests {
    use super::*;
    use chrono::Utc;
    use uuid::Uuid;

    #[tokio::test]
    async fn test_database_initialization() {
        let db_path = std::env::temp_dir().join("test_project.db");
        let _db = Database::new(&db_path)
            .await
            .expect("Failed to create database");

        // Cleanup
        let _ = std::fs::remove_file(&db_path);
    }

    #[tokio::test]
    async fn test_save_and_get_project() {
        let db_path = std::env::temp_dir().join("test_lifecycle_project.db");
        let db = Database::new(&db_path)
            .await
            .expect("Failed to create database");

        let project = ProjectRecord {
            id: Uuid::new_v4().to_string(),
            name: "Test Project".to_string(),
            description: "Test Description".to_string(),
            project_type: "MultiLang".to_string(),
            current_phase: "Creation".to_string(),
            created_at: Utc::now().to_rfc3339(),
            updated_at: Utc::now().to_rfc3339(),
        };

        db.save_project(&project)
            .await
            .expect("Failed to save project");
        let retrieved = db
            .get_project(&project.id)
            .await
            .expect("Failed to get project");

        assert_eq!(project.id, retrieved.id);
        assert_eq!(project.name, retrieved.name);

        let _ = std::fs::remove_file(&db_path);
    }

    #[tokio::test]
    async fn test_checklist_items() {
        let db_path = std::env::temp_dir().join("test_lifecycle_checklist.db");
        let db = Database::new(&db_path)
            .await
            .expect("Failed to create database");

        let project_id = Uuid::new_v4().to_string();

        // Create project first (foreign key requirement)
        let project = ProjectRecord {
            id: project_id.clone(),
            name: "Test Project".to_string(),
            description: "Test".to_string(),
            project_type: "MultiLang".to_string(),
            current_phase: "Creation".to_string(),
            created_at: Utc::now().to_rfc3339(),
            updated_at: Utc::now().to_rfc3339(),
        };
        db.save_project(&project)
            .await
            .expect("Failed to save project");

        let item = ChecklistItemRecord {
            id: Uuid::new_v4().to_string(),
            project_id: project_id.clone(),
            task_id: "task-1".to_string(),
            description: "Setup project".to_string(),
            completed: false,
            completed_at: None,
            created_at: Utc::now().to_rfc3339(),
        };

        db.save_checklist_item(&item)
            .await
            .expect("Failed to save item");
        let items = db
            .get_checklist_items(&project_id)
            .await
            .expect("Failed to get items");

        assert_eq!(items.len(), 1);
        assert_eq!(items[0].description, "Setup project");

        let _ = std::fs::remove_file(&db_path);
    }

    #[tokio::test]
    async fn test_phase_transitions() {
        let db_path = std::env::temp_dir().join("test_lifecycle_phases.db");
        let db = Database::new(&db_path)
            .await
            .expect("Failed to create database");

        let project_id = Uuid::new_v4().to_string();

        // Create project first (foreign key requirement)
        let project = ProjectRecord {
            id: project_id.clone(),
            name: "Test Project".to_string(),
            description: "Test".to_string(),
            project_type: "MultiLang".to_string(),
            current_phase: "Creation".to_string(),
            created_at: Utc::now().to_rfc3339(),
            updated_at: Utc::now().to_rfc3339(),
        };
        db.save_project(&project)
            .await
            .expect("Failed to save project");

        let transition = PhaseTransitionRecord {
            id: Uuid::new_v4().to_string(),
            project_id: project_id.clone(),
            from_phase: "Creation".to_string(),
            to_phase: "Development".to_string(),
            reason: Some("Ready to develop".to_string()),
            transitioned_at: Utc::now().to_rfc3339(),
        };

        db.record_phase_transition(&transition)
            .await
            .expect("Failed to record transition");
        let history = db
            .get_phase_history(&project_id)
            .await
            .expect("Failed to get history");

        assert_eq!(history.len(), 1);
        assert_eq!(history[0].to_phase, "Development");

        let _ = std::fs::remove_file(&db_path);
    }

    #[tokio::test]
    async fn test_database_stats() {
        let db_path = std::env::temp_dir().join("test_lifecycle_stats.db");
        let db = Database::new(&db_path)
            .await
            .expect("Failed to create database");

        let stats = db.get_stats().await.expect("Failed to get stats");
        assert_eq!(stats.projects, 0);
        assert_eq!(stats.checklist_items, 0);

        let _ = std::fs::remove_file(&db_path);
    }
}
