//! Database persistence layer for syntaxis management
//!
//! This module handles all database operations using SQLite with sqlx.
//! All operations are async and return Result<T>.

use crate::error::Result;
use chrono::Utc;
use serde::{Deserialize, Serialize};
use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
use std::path::Path;
use tools_shared::{ensure_dir, find_global_db_path};

/// Database connection pool
#[derive(Debug, Clone)]
pub struct PersistenceLayer {
    pool: SqlitePool,
}

impl PersistenceLayer {
    /// Create new persistence layer with database at given path
    pub async fn new(db_path: &str) -> Result<Self> {
        // Create database file if it doesn't exist
        let path = Path::new(db_path);
        if let Some(parent) = path.parent() {
            if !parent.as_os_str().is_empty() {
                tokio::fs::create_dir_all(parent).await?;
            }
        }

        // Create connection pool
        // Proper SQLite URL format for sqlx:
        // - Absolute paths: sqlite:///absolute/path (three slashes)
        // - Relative paths: sqlite:relative/path (one slash after sqlite:)
        let database_url = if db_path.starts_with('/') {
            // Absolute path: sqlite:///absolute/path/to/file.db
            format!("sqlite://{}", db_path)
        } else {
            // Relative path: sqlite:relative/path/to/file.db
            format!("sqlite:{}", db_path)
        };

        // Add connection options for file creation and pragmas
        let database_url = format!("{}?mode=rwc", database_url);

        let pool = SqlitePoolOptions::new()
            .max_connections(5)
            .connect(&database_url)
            .await?;

        // Run migrations
        Self::run_migrations(&pool).await?;

        Ok(Self { pool })
    }

    /// Create persistence layer using global database path
    ///
    /// Uses shared utilities to discover global database location:
    /// `~/.local/share/tools/{tool_name}/{tool_name}.db`
    pub async fn global(tool_name: &str) -> Result<Self> {
        let db_path = find_global_db_path(tool_name);

        // Ensure directory exists
        if let Some(parent) = db_path.parent() {
            ensure_dir(&parent.to_path_buf())?;
        }

        let db_path_str = db_path.to_string_lossy().to_string();
        Self::new(&db_path_str).await
    }

    /// Create new in-memory database (for testing)
    pub async fn new_memory() -> Result<Self> {
        let pool = SqlitePoolOptions::new()
            .max_connections(5)
            .connect("sqlite::memory:")
            .await?;

        Self::run_migrations(&pool).await?;

        Ok(Self { pool })
    }

    /// Run database migrations
    async fn run_migrations(pool: &SqlitePool) -> Result<()> {
        // Enable foreign keys
        sqlx::query("PRAGMA foreign_keys = ON")
            .execute(pool)
            .await?;

        // Create tables
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS projects (
                id TEXT PRIMARY KEY NOT NULL,
                name TEXT NOT NULL,
                version TEXT NOT NULL,
                description TEXT NOT NULL,
                project_type TEXT NOT NULL DEFAULT 'MultiLang',
                current_phase TEXT NOT NULL DEFAULT 'Creation',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE TABLE IF NOT EXISTS checklist_items (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                phase 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,
                task_type TEXT DEFAULT '',
                task_priority TEXT DEFAULT '',
                task_due TEXT,
                task_estimation TEXT,
                task_deps TEXT DEFAULT '',
                task_note TEXT,
                task_name TEXT,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE TABLE IF NOT EXISTS phase_transitions (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                from_phase TEXT NOT NULL,
                to_phase TEXT NOT NULL,
                timestamp TEXT NOT NULL,
                reason TEXT,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE TABLE IF NOT EXISTS security_assessments (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                profile TEXT NOT NULL,
                risk_level TEXT NOT NULL,
                passed_rules INTEGER NOT NULL,
                failed_rules INTEGER NOT NULL,
                critical_issues INTEGER NOT NULL,
                assessment_date TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE TABLE IF NOT EXISTS phase_history (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                phase TEXT NOT NULL,
                duration_days INTEGER NOT NULL,
                started_at TEXT NOT NULL,
                completed_at TEXT,
                notes TEXT,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE TABLE IF NOT EXISTS tool_configurations (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                tool_name TEXT NOT NULL,
                enabled BOOLEAN NOT NULL DEFAULT 0,
                config_json TEXT NOT NULL DEFAULT '{}',
                phases TEXT DEFAULT '',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        // Create indices
        sqlx::query("CREATE INDEX IF NOT EXISTS idx_projects_created ON projects(created_at DESC)")
            .execute(pool)
            .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_checklist_project ON checklist_items(project_id)",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_checklist_phase ON checklist_items(project_id, phase)",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_transitions_project ON phase_transitions(project_id)",
        )
        .execute(pool)
        .await?;

        sqlx::query("CREATE INDEX IF NOT EXISTS idx_assessments_project ON security_assessments(project_id)")
            .execute(pool)
            .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_phase_history_project ON phase_history(project_id, started_at DESC)",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_tool_config_project ON tool_configurations(project_id, tool_name)",
        )
        .execute(pool)
        .await?;

        // Phase 5: Task Dependencies
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS task_dependencies (
                id TEXT PRIMARY KEY NOT NULL,
                task_id TEXT NOT NULL,
                depends_on_task_id TEXT NOT NULL,
                dependency_type TEXT NOT NULL DEFAULT 'finish_to_start',
                created_at TEXT NOT NULL,
                FOREIGN KEY (task_id) REFERENCES checklist_items(id) ON DELETE CASCADE,
                FOREIGN KEY (depends_on_task_id) REFERENCES checklist_items(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query("CREATE INDEX IF NOT EXISTS idx_task_deps_task ON task_dependencies(task_id)")
            .execute(pool)
            .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_task_deps_depends ON task_dependencies(depends_on_task_id)",
        )
        .execute(pool)
        .await?;

        // Phase 5: Milestones
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS milestones (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                name TEXT NOT NULL,
                description TEXT,
                due_date TEXT NOT NULL,
                completed BOOLEAN NOT NULL DEFAULT 0,
                completed_at TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        sqlx::query("CREATE INDEX IF NOT EXISTS idx_milestones_project ON milestones(project_id)")
            .execute(pool)
            .await?;

        // Phase 5: Users (for team assignment)
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS users (
                id TEXT PRIMARY KEY NOT NULL,
                name TEXT NOT NULL,
                email TEXT NOT NULL,
                created_at TEXT NOT NULL
            )",
        )
        .execute(pool)
        .await?;

        // Add assignee_id column to checklist_items if it doesn't exist
        // SQLite doesn't support ALTER COLUMN IF NOT EXISTS, so we handle errors gracefully
        let _ = sqlx::query("ALTER TABLE checklist_items ADD COLUMN assignee_id TEXT")
            .execute(pool)
            .await;

        // Add parent_task_id column for subtasks
        let _ = sqlx::query("ALTER TABLE checklist_items ADD COLUMN parent_task_id TEXT")
            .execute(pool)
            .await;

        // Add recurrence_rule column for recurring tasks
        let _ = sqlx::query("ALTER TABLE checklist_items ADD COLUMN recurrence_rule TEXT")
            .execute(pool)
            .await;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_checklist_assignee ON checklist_items(assignee_id)",
        )
        .execute(pool)
        .await?;

        sqlx::query(
            "CREATE INDEX IF NOT EXISTS idx_checklist_parent ON checklist_items(parent_task_id)",
        )
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Get database pool
    pub fn pool(&self) -> &SqlitePool {
        &self.pool
    }

    /// Ping the database to check if it's accessible
    ///
    /// Returns `Ok(())` if the database is accessible, `Err` otherwise
    pub async fn ping(&self) -> Result<()> {
        sqlx::query("SELECT 1").execute(&self.pool).await?;
        Ok(())
    }
}

/// Stored project entity
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct ProjectEntity {
    pub id: String,
    pub name: String,
    pub version: String,
    pub description: String,
    pub project_type: String,
    pub current_phase: String,
    pub created_at: String,
    pub updated_at: String,
}

impl ProjectEntity {
    /// Create new project entity
    pub fn new(id: String, name: String, version: String, description: String) -> Self {
        let now = Utc::now().to_rfc3339();
        Self {
            id,
            name,
            version,
            description,
            project_type: "MultiLang".to_string(),
            current_phase: "Creation".to_string(),
            created_at: now.clone(),
            updated_at: now,
        }
    }

    /// Save project to database
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT OR REPLACE INTO projects (id, name, version, description, project_type, current_phase, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.name)
        .bind(&self.version)
        .bind(&self.description)
        .bind(&self.project_type)
        .bind(&self.current_phase)
        .bind(&self.created_at)
        .bind(&self.updated_at)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load project from database
    pub async fn load(id: &str, db: &PersistenceLayer) -> Result<Option<Self>> {
        let project = sqlx::query_as::<_, ProjectEntity>(
            "SELECT id, name, version, description, project_type, current_phase, created_at, updated_at FROM projects WHERE id = ?"
        )
        .bind(id)
        .fetch_optional(db.pool())
        .await?;

        Ok(project)
    }

    /// Load all projects
    pub async fn load_all(db: &PersistenceLayer) -> Result<Vec<Self>> {
        let projects = sqlx::query_as::<_, ProjectEntity>(
            "SELECT id, name, version, description, project_type, current_phase, created_at, updated_at FROM projects ORDER BY created_at DESC"
        )
        .fetch_all(db.pool())
        .await?;

        Ok(projects)
    }

    /// Delete project
    pub async fn delete(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query("DELETE FROM projects WHERE id = ?")
            .bind(&self.id)
            .execute(db.pool())
            .await?;

        Ok(())
    }
}

/// Stored checklist item entity
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct ChecklistItemEntity {
    pub id: String,
    pub project_id: String,
    pub phase: String,
    pub task_id: String,
    pub description: String,
    pub completed: bool,
    pub completed_at: Option<String>,
    pub created_at: String,
    #[sqlx(default)]
    pub task_type: String,
    #[sqlx(default)]
    pub task_priority: String,
    #[sqlx(default)]
    pub task_due: Option<String>,
    #[sqlx(default)]
    pub task_estimation: Option<String>,
    #[sqlx(default)]
    pub task_deps: Vec<String>,
    #[sqlx(default)]
    pub task_note: Option<String>,
    #[sqlx(default)]
    pub task_name: Option<String>,
}

impl ChecklistItemEntity {
    /// Create new checklist item
    pub fn new(
        id: String,
        project_id: String,
        phase: String,
        task_id: String,
        description: String,
    ) -> Self {
        Self {
            id,
            project_id,
            phase,
            task_id,
            description,
            completed: false,
            completed_at: None,
            created_at: Utc::now().to_rfc3339(),
            task_type: String::new(),
            task_priority: String::new(),
            task_due: None,
            task_estimation: None,
            task_deps: Vec::new(),
            task_note: None,
            task_name: None,
        }
    }

    /// Save to database with all task fields
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        let task_deps_str = self.task_deps.join(",");
        sqlx::query(
            "INSERT OR REPLACE INTO checklist_items (id, project_id, phase, task_id, description, completed, completed_at, created_at, task_type, task_priority, task_due, task_estimation, task_deps, task_note, task_name)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.project_id)
        .bind(&self.phase)
        .bind(&self.task_id)
        .bind(&self.description)
        .bind(self.completed)
        .bind(&self.completed_at)
        .bind(&self.created_at)
        .bind(&self.task_type)
        .bind(&self.task_priority)
        .bind(&self.task_due)
        .bind(&self.task_estimation)
        .bind(&task_deps_str)
        .bind(&self.task_note)
        .bind(&self.task_name)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load items for project
    pub async fn load_for_project(project_id: &str, db: &PersistenceLayer) -> Result<Vec<Self>> {
        let items = sqlx::query_as::<_, (String, String, String, String, String, bool, Option<String>, String, String, String, Option<String>, Option<String>, String, Option<String>, Option<String>)>(
            "SELECT id, project_id, phase, task_id, description, completed, completed_at, created_at, task_type, task_priority, task_due, task_estimation, task_deps, task_note, task_name
             FROM checklist_items WHERE project_id = ? ORDER BY phase, created_at"
        )
        .bind(project_id)
        .fetch_all(db.pool())
        .await?
        .into_iter()
        .map(|(id, project_id, phase, task_id, description, completed, completed_at, created_at, task_type, task_priority, task_due, task_estimation, task_deps_str, task_note, task_name)| {
            let task_deps = if task_deps_str.is_empty() {
                Vec::new()
            } else {
                task_deps_str.split(',').map(|s| s.to_string()).collect()
            };
            ChecklistItemEntity {
                id,
                project_id,
                phase,
                task_id,
                description,
                completed,
                completed_at,
                created_at,
                task_type,
                task_priority,
                task_due,
                task_estimation,
                task_deps,
                task_note,
                task_name,
            }
        })
        .collect();

        Ok(items)
    }

    /// Load items for phase with all task fields
    pub async fn load_for_phase(
        project_id: &str,
        phase: &str,
        db: &PersistenceLayer,
    ) -> Result<Vec<Self>> {
        let items = sqlx::query_as::<_, (String, String, String, String, String, bool, Option<String>, String, String, String, Option<String>, Option<String>, String, Option<String>, Option<String>)>(
            "SELECT id, project_id, phase, task_id, description, completed, completed_at, created_at, task_type, task_priority, task_due, task_estimation, task_deps, task_note, task_name
             FROM checklist_items WHERE project_id = ? AND phase = ? ORDER BY created_at"
        )
        .bind(project_id)
        .bind(phase)
        .fetch_all(db.pool())
        .await?
        .into_iter()
        .map(|(id, project_id, phase, task_id, description, completed, completed_at, created_at, task_type, task_priority, task_due, task_estimation, task_deps_str, task_note, task_name)| {
            let task_deps = if task_deps_str.is_empty() {
                Vec::new()
            } else {
                task_deps_str.split(',').map(|s| s.to_string()).collect()
            };
            ChecklistItemEntity {
                id,
                project_id,
                phase,
                task_id,
                description,
                completed,
                completed_at,
                created_at,
                task_type,
                task_priority,
                task_due,
                task_estimation,
                task_deps,
                task_note,
                task_name,
            }
        })
        .collect();

        Ok(items)
    }

    /// Mark as completed
    pub async fn mark_completed(&mut self, db: &PersistenceLayer) -> Result<()> {
        self.completed = true;
        self.completed_at = Some(Utc::now().to_rfc3339());
        self.save(db).await?;
        Ok(())
    }

    /// Calculate completion percentage for project
    pub async fn completion_percentage(project_id: &str, db: &PersistenceLayer) -> Result<f64> {
        let row: (i64, i64) = sqlx::query_as(
            "SELECT COUNT(*) as total, SUM(CASE WHEN completed THEN 1 ELSE 0 END) as completed
             FROM checklist_items WHERE project_id = ?",
        )
        .bind(project_id)
        .fetch_one(db.pool())
        .await?;

        let (total, completed) = row;
        if total == 0 {
            return Ok(0.0);
        }

        Ok((completed as f64 / total as f64) * 100.0)
    }
}

/// Stored phase transition entity
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct PhaseTransitionEntity {
    pub id: String,
    pub project_id: String,
    pub from_phase: String,
    pub to_phase: String,
    pub timestamp: String,
    pub reason: Option<String>,
}

impl PhaseTransitionEntity {
    /// Create new transition
    pub fn new(
        id: String,
        project_id: String,
        from_phase: String,
        to_phase: String,
        reason: Option<String>,
    ) -> Self {
        Self {
            id,
            project_id,
            from_phase,
            to_phase,
            timestamp: Utc::now().to_rfc3339(),
            reason,
        }
    }

    /// Save transition
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT INTO phase_transitions (id, project_id, from_phase, to_phase, timestamp, reason)
             VALUES (?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.project_id)
        .bind(&self.from_phase)
        .bind(&self.to_phase)
        .bind(&self.timestamp)
        .bind(&self.reason)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load transitions for project
    pub async fn load_for_project(project_id: &str, db: &PersistenceLayer) -> Result<Vec<Self>> {
        let transitions = sqlx::query_as::<_, PhaseTransitionEntity>(
            "SELECT id, project_id, from_phase, to_phase, timestamp, reason
             FROM phase_transitions WHERE project_id = ? ORDER BY timestamp DESC",
        )
        .bind(project_id)
        .fetch_all(db.pool())
        .await?;

        Ok(transitions)
    }
}

/// Stored security assessment entity
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct SecurityAssessmentEntity {
    pub id: String,
    pub project_id: String,
    pub profile: String,
    pub risk_level: String,
    pub passed_rules: i32,
    pub failed_rules: i32,
    pub critical_issues: i32,
    pub assessment_date: String,
}

impl SecurityAssessmentEntity {
    /// Create new assessment
    pub fn new(
        id: String,
        project_id: String,
        profile: String,
        risk_level: String,
        passed_rules: i32,
        failed_rules: i32,
        critical_issues: i32,
    ) -> Self {
        Self {
            id,
            project_id,
            profile,
            risk_level,
            passed_rules,
            failed_rules,
            critical_issues,
            assessment_date: Utc::now().to_rfc3339(),
        }
    }

    /// Save assessment
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT INTO security_assessments (id, project_id, profile, risk_level, passed_rules, failed_rules, critical_issues, assessment_date)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.project_id)
        .bind(&self.profile)
        .bind(&self.risk_level)
        .bind(self.passed_rules)
        .bind(self.failed_rules)
        .bind(self.critical_issues)
        .bind(&self.assessment_date)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load latest assessment
    pub async fn load_latest(project_id: &str, db: &PersistenceLayer) -> Result<Option<Self>> {
        let assessment = sqlx::query_as::<_, SecurityAssessmentEntity>(
            "SELECT id, project_id, profile, risk_level, passed_rules, failed_rules, critical_issues, assessment_date
             FROM security_assessments WHERE project_id = ? ORDER BY assessment_date DESC LIMIT 1"
        )
        .bind(project_id)
        .fetch_optional(db.pool())
        .await?;

        Ok(assessment)
    }
}

/// Phase history entity for tracking phase duration and metadata
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct PhaseHistoryEntity {
    pub id: String,
    pub project_id: String,
    pub phase: String,
    pub duration_days: i32,
    pub started_at: String,
    pub completed_at: Option<String>,
    pub notes: Option<String>,
}

impl PhaseHistoryEntity {
    /// Create new phase history entity
    pub fn new(id: String, project_id: String, phase: String) -> Self {
        let now = Utc::now().to_rfc3339();
        Self {
            id,
            project_id,
            phase,
            duration_days: 0,
            started_at: now,
            completed_at: None,
            notes: None,
        }
    }

    /// Save phase history to database
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT OR REPLACE INTO phase_history (id, project_id, phase, duration_days, started_at, completed_at, notes)
             VALUES (?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.project_id)
        .bind(&self.phase)
        .bind(self.duration_days)
        .bind(&self.started_at)
        .bind(&self.completed_at)
        .bind(&self.notes)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load phase history by id
    pub async fn load(id: &str, db: &PersistenceLayer) -> Result<Option<Self>> {
        sqlx::query_as::<_, PhaseHistoryEntity>(
            "SELECT id, project_id, phase, duration_days, started_at, completed_at, notes
             FROM phase_history WHERE id = ?",
        )
        .bind(id)
        .fetch_optional(db.pool())
        .await
        .map_err(Into::into)
    }

    /// Load all phase history for a project
    pub async fn load_for_project(project_id: &str, db: &PersistenceLayer) -> Result<Vec<Self>> {
        sqlx::query_as::<_, PhaseHistoryEntity>(
            "SELECT id, project_id, phase, duration_days, started_at, completed_at, notes
             FROM phase_history WHERE project_id = ? ORDER BY started_at DESC",
        )
        .bind(project_id)
        .fetch_all(db.pool())
        .await
        .map_err(Into::into)
    }

    /// Mark phase as completed
    pub async fn mark_completed(&mut self, db: &PersistenceLayer) -> Result<()> {
        self.completed_at = Some(Utc::now().to_rfc3339());
        self.save(db).await
    }
}

/// Tool configuration entity
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct ToolConfigurationEntity {
    pub id: String,
    pub project_id: String,
    pub tool_name: String,
    pub enabled: bool,
    pub config_json: String,
    pub phases: String,
    pub created_at: String,
    pub updated_at: String,
}

impl ToolConfigurationEntity {
    /// Create new tool configuration
    pub fn new(id: String, project_id: String, tool_name: String) -> Self {
        let now = Utc::now().to_rfc3339();
        Self {
            id,
            project_id,
            tool_name,
            enabled: false,
            config_json: "{}".to_string(),
            phases: String::new(),
            created_at: now.clone(),
            updated_at: now,
        }
    }

    /// Set complex configuration as JSON
    pub fn with_config(&mut self, config: serde_json::Value) -> &mut Self {
        self.config_json = config.to_string();
        self
    }

    /// Set phases (stored as CSV)
    pub fn with_phases(&mut self, phases: Vec<&str>) -> &mut Self {
        self.phases = phases.join(",");
        self
    }

    /// Save tool configuration to database
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT OR REPLACE INTO tool_configurations (id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.project_id)
        .bind(&self.tool_name)
        .bind(self.enabled)
        .bind(&self.config_json)
        .bind(&self.phases)
        .bind(&self.created_at)
        .bind(&self.updated_at)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load tool configuration by id
    pub async fn load(id: &str, db: &PersistenceLayer) -> Result<Option<Self>> {
        sqlx::query_as::<_, ToolConfigurationEntity>(
            "SELECT id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at
             FROM tool_configurations WHERE id = ?",
        )
        .bind(id)
        .fetch_optional(db.pool())
        .await
        .map_err(Into::into)
    }

    /// Load tool configurations for a project
    pub async fn load_for_project(project_id: &str, db: &PersistenceLayer) -> Result<Vec<Self>> {
        sqlx::query_as::<_, ToolConfigurationEntity>(
            "SELECT id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at
             FROM tool_configurations WHERE project_id = ? ORDER BY tool_name",
        )
        .bind(project_id)
        .fetch_all(db.pool())
        .await
        .map_err(Into::into)
    }

    /// Load tool configuration by project and tool name
    pub async fn load_by_name(
        project_id: &str,
        tool_name: &str,
        db: &PersistenceLayer,
    ) -> Result<Option<Self>> {
        sqlx::query_as::<_, ToolConfigurationEntity>(
            "SELECT id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at
             FROM tool_configurations WHERE project_id = ? AND tool_name = ?",
        )
        .bind(project_id)
        .bind(tool_name)
        .fetch_optional(db.pool())
        .await
        .map_err(Into::into)
    }

    /// Get parsed configuration
    pub fn get_config(&self) -> Result<serde_json::Value> {
        serde_json::from_str(&self.config_json).map_err(Into::into)
    }

    /// Get phases as vector
    pub fn get_phases(&self) -> Vec<String> {
        if self.phases.is_empty() {
            Vec::new()
        } else {
            self.phases.split(',').map(|s| s.to_string()).collect()
        }
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[tokio::test]
    async fn test_persistence_layer_memory_creation() {
        let db = PersistenceLayer::new_memory().await;
        assert!(db.is_ok());
    }

    #[tokio::test]
    async fn test_project_entity_save_load() {
        let db = PersistenceLayer::new_memory().await.unwrap();
        let project = ProjectEntity::new(
            "test-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test Description".to_string(),
        );

        project.save(&db).await.unwrap();

        let loaded = ProjectEntity::load("test-1", &db).await.unwrap();
        assert!(loaded.is_some());
        assert_eq!(loaded.unwrap().name, "Test Project");
    }

    #[tokio::test]
    async fn test_checklist_item_save_load() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let item = ChecklistItemEntity::new(
            "item-1".to_string(),
            "proj-1".to_string(),
            "Creation".to_string(),
            "setup".to_string(),
            "Setup project".to_string(),
        );

        item.save(&db).await.unwrap();

        let items = ChecklistItemEntity::load_for_project("proj-1", &db)
            .await
            .unwrap();
        assert_eq!(items.len(), 1);
    }

    #[tokio::test]
    async fn test_completion_percentage() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let mut item1 = ChecklistItemEntity::new(
            "item-1".to_string(),
            "proj-1".to_string(),
            "Creation".to_string(),
            "task1".to_string(),
            "Task 1".to_string(),
        );
        let item2 = ChecklistItemEntity::new(
            "item-2".to_string(),
            "proj-1".to_string(),
            "Creation".to_string(),
            "task2".to_string(),
            "Task 2".to_string(),
        );

        item1.save(&db).await.unwrap();
        item2.save(&db).await.unwrap();

        item1.mark_completed(&db).await.unwrap();

        let percentage = ChecklistItemEntity::completion_percentage("proj-1", &db)
            .await
            .unwrap();
        assert!((percentage - 50.0).abs() < 0.01);
    }

    #[tokio::test]
    async fn test_phase_transition_save_load() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let transition = PhaseTransitionEntity::new(
            "trans-1".to_string(),
            "proj-1".to_string(),
            "Creation".to_string(),
            "Devel".to_string(),
            Some("Ready for development".to_string()),
        );

        transition.save(&db).await.unwrap();

        let transitions = PhaseTransitionEntity::load_for_project("proj-1", &db)
            .await
            .unwrap();
        assert_eq!(transitions.len(), 1);
    }

    #[tokio::test]
    async fn test_security_assessment_save_load() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let assessment = SecurityAssessmentEntity::new(
            "assess-1".to_string(),
            "proj-1".to_string(),
            "WebApi".to_string(),
            "Medium".to_string(),
            5,
            2,
            1,
        );

        assessment.save(&db).await.unwrap();

        let loaded = SecurityAssessmentEntity::load_latest("proj-1", &db)
            .await
            .unwrap();
        assert!(loaded.is_some());
    }

    #[tokio::test]
    async fn test_project_load_all() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        let p1 = ProjectEntity::new("p1".into(), "P1".into(), "0.1.0".into(), "Desc1".into());
        let p2 = ProjectEntity::new("p2".into(), "P2".into(), "0.1.0".into(), "Desc2".into());

        p1.save(&db).await.unwrap();
        p2.save(&db).await.unwrap();

        let all = ProjectEntity::load_all(&db).await.unwrap();
        assert_eq!(all.len(), 2);
    }

    #[tokio::test]
    async fn test_phase_history_save_load() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let phase_history = PhaseHistoryEntity::new(
            "ph-1".to_string(),
            "proj-1".to_string(),
            "Creation".to_string(),
        );

        phase_history.save(&db).await.unwrap();

        let loaded = PhaseHistoryEntity::load("ph-1", &db).await.unwrap();
        assert!(loaded.is_some());
        assert_eq!(loaded.unwrap().phase, "Creation");
    }

    #[tokio::test]
    async fn test_phase_history_load_for_project() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let history1 = PhaseHistoryEntity::new(
            "ph-1".to_string(),
            "proj-1".to_string(),
            "Creation".to_string(),
        );
        let history2 = PhaseHistoryEntity::new(
            "ph-2".to_string(),
            "proj-1".to_string(),
            "Development".to_string(),
        );

        history1.save(&db).await.unwrap();
        history2.save(&db).await.unwrap();

        let histories = PhaseHistoryEntity::load_for_project("proj-1", &db)
            .await
            .unwrap();
        assert_eq!(histories.len(), 2);
    }

    #[tokio::test]
    async fn test_tool_configuration_save_load() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let tool = ToolConfigurationEntity::new(
            "tool-1".to_string(),
            "proj-1".to_string(),
            "tracking".to_string(),
        );

        tool.save(&db).await.unwrap();

        let loaded = ToolConfigurationEntity::load("tool-1", &db).await.unwrap();
        assert!(loaded.is_some());
        assert_eq!(loaded.unwrap().tool_name, "tracking");
    }

    #[tokio::test]
    async fn test_tool_configuration_load_for_project() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let tool1 = ToolConfigurationEntity::new(
            "tool-1".to_string(),
            "proj-1".to_string(),
            "tracking".to_string(),
        );
        let tool2 = ToolConfigurationEntity::new(
            "tool-2".to_string(),
            "proj-1".to_string(),
            "docs".to_string(),
        );

        tool1.save(&db).await.unwrap();
        tool2.save(&db).await.unwrap();

        let tools = ToolConfigurationEntity::load_for_project("proj-1", &db)
            .await
            .unwrap();
        assert_eq!(tools.len(), 2);
    }

    #[tokio::test]
    async fn test_tool_configuration_load_by_name() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let tool = ToolConfigurationEntity::new(
            "tool-1".to_string(),
            "proj-1".to_string(),
            "tracking".to_string(),
        );
        tool.save(&db).await.unwrap();

        let loaded = ToolConfigurationEntity::load_by_name("proj-1", "tracking", &db)
            .await
            .unwrap();
        assert!(loaded.is_some());
        assert_eq!(loaded.unwrap().tool_name, "tracking");
    }

    #[tokio::test]
    async fn test_tool_configuration_with_phases() {
        let db = PersistenceLayer::new_memory().await.unwrap();

        // Create parent project first
        let project = ProjectEntity::new(
            "proj-1".to_string(),
            "Test Project".to_string(),
            "0.1.0".to_string(),
            "Test".to_string(),
        );
        project.save(&db).await.unwrap();

        let mut tool = ToolConfigurationEntity::new(
            "tool-1".to_string(),
            "proj-1".to_string(),
            "tracking".to_string(),
        );
        tool.with_phases(vec!["Creation", "Development", "Review"]);
        tool.save(&db).await.unwrap();

        let loaded = ToolConfigurationEntity::load("tool-1", &db)
            .await
            .unwrap()
            .unwrap();
        let phases = loaded.get_phases();
        assert_eq!(phases.len(), 3);
        assert_eq!(phases[0], "Creation");
    }
}

/// Task dependency entity for Phase 5
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct TaskDependencyEntity {
    pub id: String,
    pub task_id: String,
    pub depends_on_task_id: String,
    pub dependency_type: String,
    pub created_at: String,
}

impl TaskDependencyEntity {
    /// Create new task dependency
    pub fn new(
        id: String,
        task_id: String,
        depends_on_task_id: String,
        dependency_type: String,
    ) -> Self {
        Self {
            id,
            task_id,
            depends_on_task_id,
            dependency_type,
            created_at: Utc::now().to_rfc3339(),
        }
    }

    /// Save task dependency to database
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT OR REPLACE INTO task_dependencies (id, task_id, depends_on_task_id, dependency_type, created_at)
             VALUES (?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.task_id)
        .bind(&self.depends_on_task_id)
        .bind(&self.dependency_type)
        .bind(&self.created_at)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load dependencies for a task
    pub async fn load_for_task(task_id: &str, db: &PersistenceLayer) -> Result<Vec<Self>> {
        let deps = sqlx::query_as::<_, TaskDependencyEntity>(
            "SELECT id, task_id, depends_on_task_id, dependency_type, created_at
             FROM task_dependencies WHERE task_id = ?",
        )
        .bind(task_id)
        .fetch_all(db.pool())
        .await?;

        Ok(deps)
    }

    /// Delete dependency
    pub async fn delete(id: &str, db: &PersistenceLayer) -> Result<()> {
        sqlx::query("DELETE FROM task_dependencies WHERE id = ?")
            .bind(id)
            .execute(db.pool())
            .await?;

        Ok(())
    }
}

/// Milestone entity for Phase 5
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct MilestoneEntity {
    pub id: String,
    pub project_id: String,
    pub name: String,
    pub description: Option<String>,
    pub due_date: String,
    pub completed: bool,
    pub completed_at: Option<String>,
    pub created_at: String,
}

impl MilestoneEntity {
    /// Create new milestone
    pub fn new(
        id: String,
        project_id: String,
        name: String,
        description: Option<String>,
        due_date: String,
    ) -> Self {
        Self {
            id,
            project_id,
            name,
            description,
            due_date,
            completed: false,
            completed_at: None,
            created_at: Utc::now().to_rfc3339(),
        }
    }

    /// Save milestone to database
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT OR REPLACE INTO milestones (id, project_id, name, description, due_date, completed, completed_at, created_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&self.id)
        .bind(&self.project_id)
        .bind(&self.name)
        .bind(&self.description)
        .bind(&self.due_date)
        .bind(self.completed)
        .bind(&self.completed_at)
        .bind(&self.created_at)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load milestone by id
    pub async fn load(id: &str, db: &PersistenceLayer) -> Result<Option<Self>> {
        let milestone = sqlx::query_as::<_, MilestoneEntity>(
            "SELECT id, project_id, name, description, due_date, completed, completed_at, created_at
             FROM milestones WHERE id = ?",
        )
        .bind(id)
        .fetch_optional(db.pool())
        .await?;

        Ok(milestone)
    }

    /// Load milestones for a project
    pub async fn load_for_project(project_id: &str, db: &PersistenceLayer) -> Result<Vec<Self>> {
        let milestones = sqlx::query_as::<_, MilestoneEntity>(
            "SELECT id, project_id, name, description, due_date, completed, completed_at, created_at
             FROM milestones WHERE project_id = ? ORDER BY due_date",
        )
        .bind(project_id)
        .fetch_all(db.pool())
        .await?;

        Ok(milestones)
    }

    /// Mark milestone as completed
    pub async fn mark_completed(&mut self, db: &PersistenceLayer) -> Result<()> {
        self.completed = true;
        self.completed_at = Some(Utc::now().to_rfc3339());
        self.save(db).await
    }
}

/// User entity for Phase 5 team assignment
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, sqlx::FromRow)]
pub struct UserEntity {
    pub id: String,
    pub name: String,
    pub email: String,
    pub created_at: String,
}

impl UserEntity {
    /// Create new user
    pub fn new(id: String, name: String, email: String) -> Self {
        Self {
            id,
            name,
            email,
            created_at: Utc::now().to_rfc3339(),
        }
    }

    /// Save user to database
    pub async fn save(&self, db: &PersistenceLayer) -> Result<()> {
        sqlx::query(
            "INSERT OR REPLACE INTO users (id, name, email, created_at)
             VALUES (?, ?, ?, ?)",
        )
        .bind(&self.id)
        .bind(&self.name)
        .bind(&self.email)
        .bind(&self.created_at)
        .execute(db.pool())
        .await?;

        Ok(())
    }

    /// Load user by id
    pub async fn load(id: &str, db: &PersistenceLayer) -> Result<Option<Self>> {
        let user = sqlx::query_as::<_, UserEntity>(
            "SELECT id, name, email, created_at FROM users WHERE id = ?",
        )
        .bind(id)
        .fetch_optional(db.pool())
        .await?;

        Ok(user)
    }
}
