//! Database persistence layer

use crate::checklist::Checklist;
use crate::config::ProjectConfig;
use crate::error::Result;
use crate::phase::{PhaseTransition, ProjectPhase};
use crate::LifecycleError;
use chrono::{DateTime, Utc};
use sqlx::sqlite::{SqlitePool, SqlitePoolOptions};
use sqlx::Row;
use std::path::Path;

/// Database connection pool
pub type DbPool = SqlitePool;

/// Database manager
pub struct Database {
    pool: DbPool,
}

impl std::fmt::Debug for Database {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        f.debug_struct("Database")
            .field("pool", &"SqlitePool")
            .finish()
    }
}

impl Database {
    /// Initialize database at given path
    pub async fn init(db_path: &Path) -> Result<Self> {
        let connection_string = format!("sqlite://{}", db_path.display());

        let pool = SqlitePoolOptions::new()
            .max_connections(5)
            .connect(&connection_string)
            .await
            .map_err(|e| {
                LifecycleError::database(format!("Failed to connect to database: {}", e))
            })?;

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

    /// Run database migrations
    async fn run_migrations(&self) -> Result<()> {
        // Projects table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS projects (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                version TEXT NOT NULL,
                description TEXT,
                project_type TEXT NOT NULL,
                mode TEXT NOT NULL,
                current_phase TEXT NOT NULL,
                primary_language TEXT NOT NULL,
                languages TEXT NOT NULL,
                config_json TEXT NOT NULL,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL
            )
            "#,
        )
        .execute(&self.pool)
        .await
        .map_err(|e| LifecycleError::database(format!("Failed to create projects table: {}", e)))?;

        // Phase history 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,
                checklist_completed BOOLEAN NOT NULL,
                notes TEXT,
                transitioned_at DATETIME NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id)
            )
            "#,
        )
        .execute(&self.pool)
        .await
        .map_err(|e| {
            LifecycleError::database(format!("Failed to create phase_transitions table: {}", e))
        })?;

        // Checklists table
        sqlx::query(
            r#"
            CREATE TABLE IF NOT EXISTS checklists (
                id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                phase TEXT NOT NULL,
                checklist_json TEXT NOT NULL,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id),
                UNIQUE(project_id, phase)
            )
            "#,
        )
        .execute(&self.pool)
        .await
        .map_err(|e| {
            LifecycleError::database(format!("Failed to create checklists table: {}", e))
        })?;

        Ok(())
    }

    /// Save project configuration
    pub async fn save_project(&self, project_id: &str, config: &ProjectConfig) -> Result<()> {
        let config_json = serde_json::to_string(config)?;
        let now = Utc::now();

        sqlx::query(
            r#"
            INSERT INTO projects
            (id, name, version, description, project_type, mode, current_phase,
             primary_language, languages, config_json, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
                config_json = excluded.config_json,
                current_phase = excluded.current_phase,
                updated_at = excluded.updated_at
            "#,
        )
        .bind(project_id)
        .bind(&config.project.name)
        .bind(&config.project.version)
        .bind(&config.project.description)
        .bind(config.project_type.to_string())
        .bind(config.mode.to_string())
        .bind(config.phase.to_string())
        .bind(&config.primary_language)
        .bind(config.languages.join(","))
        .bind(config_json)
        .bind(now.to_rfc3339())
        .bind(now.to_rfc3339())
        .execute(&self.pool)
        .await
        .map_err(|e| LifecycleError::database(format!("Failed to save project: {}", e)))?;

        Ok(())
    }

    /// Load project configuration
    pub async fn load_project(&self, project_id: &str) -> Result<Option<ProjectConfig>> {
        let row = sqlx::query("SELECT config_json FROM projects WHERE id = ?")
            .bind(project_id)
            .fetch_optional(&self.pool)
            .await
            .map_err(|e| LifecycleError::database(format!("Failed to load project: {}", e)))?;

        if let Some(row) = row {
            let config_json: String = row.get("config_json");
            let config = serde_json::from_str(&config_json)?;
            Ok(Some(config))
        } else {
            Ok(None)
        }
    }

    /// Save phase transition
    pub async fn save_phase_transition(
        &self,
        project_id: &str,
        transition: &PhaseTransition,
    ) -> Result<()> {
        let id = uuid::Uuid::new_v4().to_string();

        sqlx::query(
            r#"
            INSERT INTO phase_transitions
            (id, project_id, from_phase, to_phase, checklist_completed, notes, transitioned_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            "#,
        )
        .bind(id)
        .bind(project_id)
        .bind(transition.from.to_string())
        .bind(transition.to.to_string())
        .bind(transition.checklist_completed)
        .bind(&transition.notes)
        .bind(transition.timestamp.to_rfc3339())
        .execute(&self.pool)
        .await
        .map_err(|e| LifecycleError::database(format!("Failed to save transition: {}", e)))?;

        Ok(())
    }

    /// Load phase transitions for project
    pub async fn load_phase_history(&self, project_id: &str) -> Result<Vec<PhaseTransition>> {
        let rows = sqlx::query(
            "SELECT from_phase, to_phase, checklist_completed, notes, transitioned_at FROM phase_transitions WHERE project_id = ? ORDER BY transitioned_at ASC"
        )
        .bind(project_id)
        .fetch_all(&self.pool)
        .await
        .map_err(|e| LifecycleError::database(format!("Failed to load transitions: {}", e)))?;

        let mut transitions = vec![];
        for row in rows {
            let from_str: String = row.get("from_phase");
            let to_str: String = row.get("to_phase");
            let checklist_completed: bool = row.get("checklist_completed");
            let notes: String = row.get("notes");
            let timestamp_str: String = row.get("transitioned_at");
            let timestamp = DateTime::parse_from_rfc3339(&timestamp_str)
                .ok()
                .map(|dt| dt.with_timezone(&Utc))
                .unwrap_or_else(Utc::now);

            let from = ProjectPhase::from_str(&from_str)?;
            let to = ProjectPhase::from_str(&to_str)?;

            transitions.push(PhaseTransition {
                from,
                to,
                timestamp,
                checklist_completed,
                notes,
            });
        }

        Ok(transitions)
    }

    /// Save checklist
    pub async fn save_checklist(&self, project_id: &str, checklist: &Checklist) -> Result<()> {
        let checklist_json = serde_json::to_string(checklist)?;
        let now = Utc::now();

        sqlx::query(
            r#"
            INSERT INTO checklists
            (id, project_id, phase, checklist_json, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(project_id, phase) DO UPDATE SET
                checklist_json = excluded.checklist_json,
                updated_at = excluded.updated_at
            "#,
        )
        .bind(uuid::Uuid::new_v4().to_string())
        .bind(project_id)
        .bind(checklist.phase.to_string())
        .bind(checklist_json)
        .bind(now.to_rfc3339())
        .bind(now.to_rfc3339())
        .execute(&self.pool)
        .await
        .map_err(|e| LifecycleError::database(format!("Failed to save checklist: {}", e)))?;

        Ok(())
    }

    /// Load checklist
    pub async fn load_checklist(&self, project_id: &str, phase: &str) -> Result<Option<Checklist>> {
        let row =
            sqlx::query("SELECT checklist_json FROM checklists WHERE project_id = ? AND phase = ?")
                .bind(project_id)
                .bind(phase)
                .fetch_optional(&self.pool)
                .await
                .map_err(|e| {
                    LifecycleError::database(format!("Failed to load checklist: {}", e))
                })?;

        if let Some(row) = row {
            let checklist_json: String = row.get("checklist_json");
            let checklist = serde_json::from_str(&checklist_json)?;
            Ok(Some(checklist))
        } else {
            Ok(None)
        }
    }

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

    /// Close database connection
    pub async fn close(&self) -> Result<()> {
        self.pool.close().await;
        Ok(())
    }
}

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

    #[tokio::test]
    async fn test_database_init() {
        let temp_dir = tempfile::tempdir().unwrap();
        let db_path = temp_dir.path().join("test.db");

        // Create the file first
        std::fs::File::create(&db_path).ok();

        let db = Database::init(&db_path).await;
        assert!(db.is_ok(), "Database initialization failed: {:?}", db.err());
    }
}
