//! SQLite database implementation
//!
//! Provides a concrete SQLite implementation of the Database trait.
//! This implementation wraps the existing persistence.rs functionality
//! into the trait-based abstraction.

use super::{
    Database, DbChecklistItem, DbPhaseHistory, DbPhaseTransition, DbProject, DbSecurityAssessment,
    DbSecurityAssessmentDetail, DbTeamMember, DbToolConfiguration, DbToolDependency,
};
use crate::error::Result;
use chrono::Utc;
use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
use std::path::Path;

/// SQLite database implementation
#[derive(Debug, Clone)]
pub struct SqliteDatabase {
    pool: SqlitePool,
}

impl SqliteDatabase {
    /// Create new SQLite database at given path
    pub async fn new(db_path: &str) -> Result<Self> {
        // Create parent directories if needed
        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?;
            }
        }

        // Build database URL
        let database_url = if db_path.starts_with('/') {
            format!("sqlite://{}", db_path)
        } else {
            format!("sqlite:{}", db_path)
        };

        let database_url = format!("{}?mode=rwc", database_url);

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

        // Initialize schema
        Self::init_schema_internal(&pool).await?;

        Ok(Self { pool })
    }

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

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

        Ok(Self { pool })
    }

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

    /// Internal schema initialization
    async fn init_schema_internal(pool: &SqlitePool) -> Result<()> {
        // Enable foreign keys
        sqlx::query("PRAGMA foreign_keys = ON")
            .execute(pool)
            .await?;

        // Create projects table
        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?;

        // Create checklist_items table
        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?;

        // Create phase_transitions table
        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?;

        // Create security_assessments table
        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?;

        // Create phase_history table
        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?;

        // Create tool_configurations table
        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 tool_dependencies table
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS tool_dependencies (
                id TEXT PRIMARY KEY NOT NULL,
                project_id TEXT NOT NULL,
                tool_name TEXT NOT NULL,
                dependency_name TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        // Create security_assessment_details table
        sqlx::query(
            "CREATE TABLE IF NOT EXISTS security_assessment_details (
                id TEXT PRIMARY KEY NOT NULL,
                assessment_id TEXT NOT NULL,
                rule_name TEXT NOT NULL,
                passed BOOLEAN NOT NULL,
                FOREIGN KEY (assessment_id) REFERENCES security_assessments(id) ON DELETE CASCADE
            )",
        )
        .execute(pool)
        .await?;

        // Create users table
        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?;

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

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

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

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

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

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

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

        Ok(())
    }
}

#[async_trait::async_trait]
impl Database for SqliteDatabase {
    // ========== PROJECT OPERATIONS ==========

    async fn create_project(&self, project: &DbProject) -> Result<DbProject> {
        sqlx::query(
            "INSERT OR REPLACE INTO projects (id, name, version, description, project_type, current_phase, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&project.id)
        .bind(&project.name)
        .bind(&project.version)
        .bind(&project.description)
        .bind(&project.project_type)
        .bind(&project.current_phase)
        .bind(&project.created_at)
        .bind(&project.updated_at)
        .execute(&self.pool)
        .await?;

        Ok(project.clone())
    }

    async fn get_project(&self, id: &str) -> Result<Option<DbProject>> {
        let project = sqlx::query_as::<_, DbProject>(
            "SELECT id, name, version, description, project_type, current_phase, created_at, updated_at FROM projects WHERE id = ?"
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;

        Ok(project)
    }

    async fn list_projects(&self) -> Result<Vec<DbProject>> {
        let projects = sqlx::query_as::<_, DbProject>(
            "SELECT id, name, version, description, project_type, current_phase, created_at, updated_at FROM projects ORDER BY created_at DESC"
        )
        .fetch_all(&self.pool)
        .await?;

        Ok(projects)
    }

    async fn update_project(&self, project: &DbProject) -> Result<DbProject> {
        sqlx::query(
            "UPDATE projects SET name = ?, version = ?, description = ?, project_type = ?, current_phase = ?, updated_at = ? WHERE id = ?"
        )
        .bind(&project.name)
        .bind(&project.version)
        .bind(&project.description)
        .bind(&project.project_type)
        .bind(&project.current_phase)
        .bind(&project.updated_at)
        .bind(&project.id)
        .execute(&self.pool)
        .await?;

        Ok(project.clone())
    }

    async fn delete_project(&self, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM projects WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    // ========== CHECKLIST OPERATIONS ==========

    async fn create_checklist_item(&self, item: &DbChecklistItem) -> Result<DbChecklistItem> {
        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(&item.id)
        .bind(&item.project_id)
        .bind(&item.phase)
        .bind(&item.task_id)
        .bind(&item.description)
        .bind(item.completed)
        .bind(&item.completed_at)
        .bind(&item.created_at)
        .bind(&item.task_type)
        .bind(&item.task_priority)
        .bind(&item.task_due)
        .bind(&item.task_estimation)
        .bind(&item.task_deps)
        .bind(&item.task_note)
        .bind(&item.task_name)
        .execute(&self.pool)
        .await?;

        Ok(item.clone())
    }

    async fn get_checklist_items(&self, project_id: &str) -> Result<Vec<DbChecklistItem>> {
        let items = sqlx::query_as::<_, DbChecklistItem>(
            "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(&self.pool)
        .await?;

        Ok(items)
    }

    async fn get_checklist_items_by_phase(
        &self,
        project_id: &str,
        phase: &str,
    ) -> Result<Vec<DbChecklistItem>> {
        let items = sqlx::query_as::<_, DbChecklistItem>(
            "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(&self.pool)
        .await?;

        Ok(items)
    }

    async fn update_checklist_item(&self, item: &DbChecklistItem) -> Result<DbChecklistItem> {
        sqlx::query(
            "UPDATE checklist_items SET description = ?, completed = ?, completed_at = ?, task_type = ?, task_priority = ?, task_due = ?, task_estimation = ?, task_deps = ?, task_note = ?, task_name = ? WHERE id = ?"
        )
        .bind(&item.description)
        .bind(item.completed)
        .bind(&item.completed_at)
        .bind(&item.task_type)
        .bind(&item.task_priority)
        .bind(&item.task_due)
        .bind(&item.task_estimation)
        .bind(&item.task_deps)
        .bind(&item.task_note)
        .bind(&item.task_name)
        .bind(&item.id)
        .execute(&self.pool)
        .await?;

        Ok(item.clone())
    }

    async fn complete_checklist_item(&self, id: &str) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        sqlx::query("UPDATE checklist_items SET completed = 1, completed_at = ? WHERE id = ?")
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    async fn delete_checklist_item(&self, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM checklist_items WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    async fn get_completion_percentage(&self, project_id: &str, phase: &str) -> 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 = ? AND phase = ?",
        )
        .bind(project_id)
        .bind(phase)
        .fetch_one(&self.pool)
        .await?;

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

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

    // ========== PHASE TRANSITION OPERATIONS ==========

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

        Ok(transition.clone())
    }

    async fn get_phase_history(&self, project_id: &str) -> Result<Vec<DbPhaseTransition>> {
        let transitions = sqlx::query_as::<_, DbPhaseTransition>(
            "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(&self.pool)
        .await?;

        Ok(transitions)
    }

    async fn get_current_phase(&self, project_id: &str) -> Result<String> {
        let row: (String,) = sqlx::query_as("SELECT current_phase FROM projects WHERE id = ?")
            .bind(project_id)
            .fetch_one(&self.pool)
            .await?;

        Ok(row.0)
    }

    // ========== SECURITY ASSESSMENT OPERATIONS ==========

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

        Ok(assessment.clone())
    }

    async fn get_latest_assessment(
        &self,
        project_id: &str,
    ) -> Result<Option<DbSecurityAssessment>> {
        let assessment = sqlx::query_as::<_, DbSecurityAssessment>(
            "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(&self.pool)
        .await?;

        Ok(assessment)
    }

    async fn list_assessments(&self, project_id: &str) -> Result<Vec<DbSecurityAssessment>> {
        let assessments = sqlx::query_as::<_, DbSecurityAssessment>(
            "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"
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await?;

        Ok(assessments)
    }

    // ========== PHASE HISTORY OPERATIONS ==========

    async fn record_phase_history(&self, history: &DbPhaseHistory) -> Result<DbPhaseHistory> {
        sqlx::query(
            "INSERT INTO phase_history (id, project_id, phase, duration_days, started_at, completed_at, notes)
             VALUES (?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&history.id)
        .bind(&history.project_id)
        .bind(&history.phase)
        .bind(history.duration_days)
        .bind(&history.started_at)
        .bind(&history.completed_at)
        .bind(&history.notes)
        .execute(&self.pool)
        .await?;

        Ok(history.clone())
    }

    async fn get_phase_history_records(&self, project_id: &str) -> Result<Vec<DbPhaseHistory>> {
        let records = sqlx::query_as::<_, DbPhaseHistory>(
            "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(&self.pool)
        .await?;

        Ok(records)
    }

    // ========== TOOL CONFIGURATION OPERATIONS ==========

    async fn create_tool_config(
        &self,
        config: &DbToolConfiguration,
    ) -> Result<DbToolConfiguration> {
        sqlx::query(
            "INSERT INTO tool_configurations (id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&config.id)
        .bind(&config.project_id)
        .bind(&config.tool_name)
        .bind(config.enabled)
        .bind(&config.config_json)
        .bind(&config.phases)
        .bind(&config.created_at)
        .bind(&config.updated_at)
        .execute(&self.pool)
        .await?;

        Ok(config.clone())
    }

    async fn get_tool_config(&self, id: &str) -> Result<Option<DbToolConfiguration>> {
        let config = sqlx::query_as::<_, DbToolConfiguration>(
            "SELECT id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at FROM tool_configurations WHERE id = ?"
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;

        Ok(config)
    }

    async fn list_tool_configs(&self, project_id: &str) -> Result<Vec<DbToolConfiguration>> {
        let configs = sqlx::query_as::<_, DbToolConfiguration>(
            "SELECT id, project_id, tool_name, enabled, config_json, phases, created_at, updated_at FROM tool_configurations WHERE project_id = ?"
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await?;

        Ok(configs)
    }

    async fn find_tool_config(
        &self,
        project_id: &str,
        tool_name: &str,
    ) -> Result<Option<DbToolConfiguration>> {
        let config = sqlx::query_as::<_, DbToolConfiguration>(
            "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(&self.pool)
        .await?;

        Ok(config)
    }

    async fn update_tool_config(
        &self,
        config: &DbToolConfiguration,
    ) -> Result<DbToolConfiguration> {
        sqlx::query(
            "UPDATE tool_configurations SET enabled = ?, config_json = ?, phases = ?, updated_at = ? WHERE id = ?"
        )
        .bind(config.enabled)
        .bind(&config.config_json)
        .bind(&config.phases)
        .bind(&config.updated_at)
        .bind(&config.id)
        .execute(&self.pool)
        .await?;

        Ok(config.clone())
    }

    // ========== TOOL DEPENDENCY OPERATIONS ==========

    async fn create_tool_dependency(
        &self,
        dependency: &DbToolDependency,
    ) -> Result<DbToolDependency> {
        sqlx::query(
            "INSERT INTO tool_dependencies (id, project_id, tool_name, dependency_name)
             VALUES (?, ?, ?, ?)",
        )
        .bind(&dependency.id)
        .bind(&dependency.project_id)
        .bind(&dependency.tool_name)
        .bind(&dependency.dependency_name)
        .execute(&self.pool)
        .await?;

        Ok(dependency.clone())
    }

    async fn get_tool_dependencies(&self, project_id: &str) -> Result<Vec<DbToolDependency>> {
        let dependencies = sqlx::query_as::<_, DbToolDependency>(
            "SELECT id, project_id, tool_name, dependency_name FROM tool_dependencies WHERE project_id = ?"
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await?;

        Ok(dependencies)
    }

    async fn delete_tool_dependency(&self, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM tool_dependencies WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    // ========== SECURITY ASSESSMENT DETAIL OPERATIONS ==========

    async fn create_assessment_detail(
        &self,
        detail: &DbSecurityAssessmentDetail,
    ) -> Result<DbSecurityAssessmentDetail> {
        sqlx::query(
            "INSERT INTO security_assessment_details (id, assessment_id, rule_name, passed)
             VALUES (?, ?, ?, ?)",
        )
        .bind(&detail.id)
        .bind(&detail.assessment_id)
        .bind(&detail.rule_name)
        .bind(detail.passed)
        .execute(&self.pool)
        .await?;

        Ok(detail.clone())
    }

    async fn get_assessment_details(
        &self,
        assessment_id: &str,
    ) -> Result<Vec<DbSecurityAssessmentDetail>> {
        let details = sqlx::query_as::<_, DbSecurityAssessmentDetail>(
            "SELECT id, assessment_id, rule_name, passed FROM security_assessment_details WHERE assessment_id = ?"
        )
        .bind(assessment_id)
        .fetch_all(&self.pool)
        .await?;

        Ok(details)
    }

    async fn delete_assessment_detail(&self, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM security_assessment_details WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }

    // ========== TEAM MEMBER OPERATIONS ==========

    async fn create_team_member(&self, member: &DbTeamMember) -> Result<DbTeamMember> {
        sqlx::query(
            "INSERT INTO users (id, name, email, created_at)
             VALUES (?, ?, ?, ?)",
        )
        .bind(&member.id)
        .bind(&member.name)
        .bind(&member.email)
        .bind(Utc::now().to_rfc3339())
        .execute(&self.pool)
        .await?;

        Ok(member.clone())
    }

    async fn get_team_member(&self, id: &str) -> Result<Option<DbTeamMember>> {
        let member =
            sqlx::query_as::<_, DbTeamMember>("SELECT id, name, email FROM users WHERE id = ?")
                .bind(id)
                .fetch_optional(&self.pool)
                .await?;

        Ok(member)
    }

    async fn list_team_members(&self) -> Result<Vec<DbTeamMember>> {
        let members = sqlx::query_as::<_, DbTeamMember>("SELECT id, name, email FROM users")
            .fetch_all(&self.pool)
            .await?;

        Ok(members)
    }

    async fn update_team_member(&self, member: &DbTeamMember) -> Result<DbTeamMember> {
        sqlx::query("UPDATE users SET name = ?, email = ? WHERE id = ?")
            .bind(&member.name)
            .bind(&member.email)
            .bind(&member.id)
            .execute(&self.pool)
            .await?;

        Ok(member.clone())
    }

    // ========== HEALTH/UTILITY OPERATIONS ==========

    async fn ping(&self) -> Result<()> {
        sqlx::query("SELECT 1").execute(&self.pool).await?;
        Ok(())
    }

    async fn init_schema(&self) -> Result<()> {
        Self::init_schema_internal(&self.pool).await
    }
}

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

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

    #[tokio::test]
    async fn test_sqlite_ping() {
        let db = SqliteDatabase::new_memory().await.unwrap();
        assert!(db.ping().await.is_ok());
    }

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

        let project = DbProject {
            id: "test1".to_string(),
            name: "Test Project".to_string(),
            version: "1.0.0".to_string(),
            description: "A 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(),
        };

        // Create
        let created = db.create_project(&project).await.unwrap();
        assert_eq!(created.name, "Test Project");

        // Read
        let fetched = db.get_project("test1").await.unwrap();
        assert!(fetched.is_some());
        assert_eq!(fetched.unwrap().name, "Test Project");

        // List
        let all = db.list_projects().await.unwrap();
        assert_eq!(all.len(), 1);

        // Delete
        db.delete_project("test1").await.unwrap();
        let deleted = db.get_project("test1").await.unwrap();
        assert!(deleted.is_none());
    }
}
