wsb 0.134.3832

// Schema-Based CRUD Operations - Complete Implementation
// Following Directive D081: Complete replacement with zero backward compatibility

use anyhow::Result;
use sqlx::{Row, SqlitePool};

use crate::entities::schema_models::{
    Directive, DirectiveCategory, Feature, FeatureState, Priority, Project, Session, Task,
    TaskStatus,
};

fn parse_db_timestamp(value: &str) -> Result<chrono::DateTime<chrono::Utc>> {
    if let Ok(parsed) = chrono::DateTime::parse_from_rfc3339(value) {
        return Ok(parsed.with_timezone(&chrono::Utc));
    }
    let parsed = chrono::NaiveDateTime::parse_from_str(value, "%Y-%m-%d %H:%M:%S")?;
    Ok(parsed.and_utc())
}

/// Project CRUD operations
pub mod projects {
    use super::*;

    /// Create new project with validation
    pub async fn create(pool: &SqlitePool, name: String, description: String) -> Result<Project> {
        let next_id = get_next_project_id(pool).await?;
        let project = Project::new(next_id.clone(), name, description)
            .map_err(|e| anyhow::anyhow!("Failed to create project: {}", e))?;

        sqlx::query(r#"
            INSERT INTO projects (id, name, description, status, current_phase, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        "#)
        .bind(&project.id)
        .bind(&project.name)
        .bind(&project.description)
        .bind(&project.status)
        .bind(&project.current_phase)
        .bind(&project.created_at.to_rfc3339())
        .bind(&project.updated_at.to_rfc3339())
        .execute(pool)
        .await?;

        Ok(project)
    }

    /// Get project by ID
    pub async fn get_by_id(pool: &SqlitePool, id: &str) -> Result<Option<Project>> {
        let row = sqlx::query(
            r#"
            SELECT id, name, description, status, current_phase, created_at, updated_at 
            FROM projects WHERE id = ?
        "#,
        )
        .bind(id)
        .fetch_optional(pool)
        .await?;

        if let Some(row) = row {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let project = Project::from_db_row(
                row.get("id"),
                row.get("name"),
                row.get("description"),
                row.get("status"),
                row.get("current_phase"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse project from DB: {}", e))?;
            Ok(Some(project))
        } else {
            Ok(None)
        }
    }

    /// List active projects
    pub async fn list_active(pool: &SqlitePool) -> Result<Vec<Project>> {
        let rows = sqlx::query(
            r#"
            SELECT id, name, description, status, current_phase, created_at, updated_at 
            FROM projects WHERE status = 'active' ORDER BY created_at DESC
        "#,
        )
        .fetch_all(pool)
        .await?;

        let mut projects = Vec::new();
        for row in rows {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let project = Project::from_db_row(
                row.get("id"),
                row.get("name"),
                row.get("description"),
                row.get("status"),
                row.get("current_phase"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse project from DB: {}", e))?;
            projects.push(project);
        }
        Ok(projects)
    }

    /// Update project
    pub async fn update(
        pool: &SqlitePool,
        id: &str,
        name: Option<String>,
        description: Option<String>,
        current_phase: Option<String>,
    ) -> Result<()> {
        let mut project = get_by_id(pool, id)
            .await?
            .ok_or_else(|| anyhow::anyhow!("Project not found: {}", id))?;

        project
            .update(name, description, current_phase)
            .map_err(|e| anyhow::anyhow!("Failed to update project: {}", e))?;

        sqlx::query(
            r#"
            UPDATE projects 
            SET name = ?, description = ?, current_phase = ?, updated_at = ?
            WHERE id = ?
        "#,
        )
        .bind(&project.name)
        .bind(&project.description)
        .bind(&project.current_phase)
        .bind(&project.updated_at.to_rfc3339())
        .bind(id)
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Delete project (CASCADE will handle dependent entities)
    pub async fn delete(pool: &SqlitePool, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM projects WHERE id = ?")
            .bind(id)
            .execute(pool)
            .await?;
        Ok(())
    }

    /// Get next sequential project ID
    async fn get_next_project_id(pool: &SqlitePool) -> Result<String> {
        let max_id: Option<String> = sqlx::query_scalar(
            "SELECT id FROM projects ORDER BY CAST(SUBSTR(id, 2) AS INTEGER) DESC LIMIT 1",
        )
        .fetch_optional(pool)
        .await?;

        match max_id {
            Some(id) => {
                let num_str = &id[1..];
                let num: u32 = num_str.parse().unwrap_or(0);
                Ok(format!("P{:03}", num + 1))
            }
            None => Ok("P001".to_string()),
        }
    }
}

/// Feature CRUD operations
pub mod features {
    use super::*;

    /// Create new feature with validation
    pub async fn create(
        pool: &SqlitePool,
        project_id: String,
        name: String,
        description: String,
        category: Option<String>,
    ) -> Result<Feature> {
        let next_id = get_next_feature_id(pool).await?;
        let feature = Feature::new(
            next_id.clone(),
            project_id.clone(),
            next_id.clone(),
            name,
            description,
            category,
        )
        .map_err(|e| anyhow::anyhow!("Failed to create feature: {}", e))?;

        sqlx::query(r#"
            INSERT INTO features (id, project_id, code, name, description, category, state, test_status, priority, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#)
        .bind(&feature.id)
        .bind(&feature.project_id)
        .bind(&feature.code)
        .bind(&feature.name)
        .bind(&feature.description)
        .bind(&feature.category)
        .bind(&feature.state)
        .bind(&feature.test_status)
        .bind(&feature.priority)
        .bind(&feature.created_at.to_rfc3339())
        .bind(&feature.updated_at.to_rfc3339())
        .execute(pool)
        .await?;

        Ok(feature)
    }

    /// Get feature by ID
    pub async fn get_by_id(pool: &SqlitePool, id: &str) -> Result<Option<Feature>> {
        let row = sqlx::query(r#"
            SELECT id, project_id, code, name, description, category, state, test_status, priority, notes, created_at, updated_at 
            FROM features WHERE id = ?
        "#)
        .bind(id)
        .fetch_optional(pool)
        .await?;

        if let Some(row) = row {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let feature = Feature::from_db_row(
                row.get("id"),
                row.get("project_id"),
                row.get("code"),
                row.get("name"),
                row.get("description"),
                row.get("category"),
                row.get("state"),
                row.get("test_status"),
                row.get("priority"),
                row.get("notes"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse feature from DB: {}", e))?;
            Ok(Some(feature))
        } else {
            Ok(None)
        }
    }

    /// List features by project
    pub async fn list_by_project(pool: &SqlitePool, project_id: &str) -> Result<Vec<Feature>> {
        let rows = sqlx::query(r#"
            SELECT id, project_id, code, name, description, category, state, test_status, priority, notes, created_at, updated_at 
            FROM features WHERE project_id = ? ORDER BY created_at DESC
        "#)
        .bind(project_id)
        .fetch_all(pool)
        .await?;

        let mut features = Vec::new();
        for row in rows {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let feature = Feature::from_db_row(
                row.get("id"),
                row.get("project_id"),
                row.get("code"),
                row.get("name"),
                row.get("description"),
                row.get("category"),
                row.get("state"),
                row.get("test_status"),
                row.get("priority"),
                row.get("notes"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse feature from DB: {}", e))?;
            features.push(feature);
        }
        Ok(features)
    }

    /// Update feature state
    pub async fn update_state(pool: &SqlitePool, id: &str, new_state: FeatureState) -> Result<()> {
        // Simplified implementation - just update the state directly
        sqlx::query(
            r#"
            UPDATE features 
            SET state = ?, updated_at = ?
            WHERE id = ?
        "#,
        )
        .bind(new_state.as_str())
        .bind(chrono::Utc::now().to_rfc3339())
        .bind(id)
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Delete feature (CASCADE: manually delete related tasks since feature_ids is JSON)
    pub async fn delete(pool: &SqlitePool, id: &str) -> Result<()> {
        // First, delete tasks that reference this feature_id
        // Since feature_ids is stored as JSON/text, we need to check for the feature_id
        sqlx::query("DELETE FROM tasks WHERE feature_ids = ? OR feature_ids LIKE ?")
            .bind(id) // Exact match for simple string case
            .bind(format!("%{}%", id)) // Pattern match for JSON array case
            .execute(pool)
            .await?;

        // Now delete the feature itself
        sqlx::query("DELETE FROM features WHERE id = ?")
            .bind(id)
            .execute(pool)
            .await?;
        Ok(())
    }

    /// Get next sequential feature ID
    async fn get_next_feature_id(pool: &SqlitePool) -> Result<String> {
        let max_id: Option<String> = sqlx::query_scalar(
            "SELECT id FROM features ORDER BY CAST(SUBSTR(id, 2) AS INTEGER) DESC LIMIT 1",
        )
        .fetch_optional(pool)
        .await?;

        match max_id {
            Some(id) => {
                let num_str = &id[1..];
                let num: u32 = num_str.parse().unwrap_or(0);
                Ok(format!("F{:05}", num + 1))
            }
            None => Ok("F00001".to_string()),
        }
    }
}

/// Task CRUD operations
pub mod tasks {
    use super::*;

    /// Create new task with validation
    pub async fn create(
        pool: &SqlitePool,
        project_id: String,
        feature_id: String,
        task_description: String,
        category: String,
    ) -> Result<Task> {
        let next_id = get_next_task_id(pool).await?;
        let task = Task::new(
            next_id.clone(),
            project_id,
            feature_id,
            task_description,
            category,
        )
        .map_err(|e| anyhow::anyhow!("Failed to create task: {}", e))?;

        // Tasks table uses feature_ids (JSON array) and different field names
        let feature_ids_json = format!("{}", task.feature_id); // Store single feature_id as simple string for now

        sqlx::query(r#"
            INSERT INTO tasks (id, project_id, code, title, description, category, status, priority, feature_ids, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#)
        .bind(&task.id)
        .bind(&task.project_id)
        .bind(&task.id) // Using task ID as code
        .bind(&task.task) // title
        .bind(&task.task) // description (reusing task content)
        .bind(&task.category)
        .bind(&task.status)
        .bind(&task.priority)
        .bind(&feature_ids_json) // Store feature_id in feature_ids field
        .bind(&task.created_at.to_rfc3339())
        .bind(&task.updated_at.to_rfc3339())
        .execute(pool)
        .await?;

        Ok(task)
    }

    /// Get task by ID
    pub async fn get_by_id(pool: &SqlitePool, id: &str) -> Result<Option<Task>> {
        let row = sqlx::query(r#"
            SELECT id, project_id, feature_ids, title, category, status, priority, assigned_to, depends_on, notes, created_at, updated_at 
            FROM tasks WHERE id = ?
        "#)
        .bind(id)
        .fetch_optional(pool)
        .await?;

        if let Some(row) = row {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            // Extract first feature_id from feature_ids field
            let feature_ids_str: String = row.get("feature_ids");
            let feature_id = if feature_ids_str.starts_with('[') {
                // Handle JSON array case later
                feature_ids_str.trim_matches(['[', ']', '"']).to_string()
            } else {
                feature_ids_str // Simple string case
            };

            let task = Task::from_db_row(
                row.get("id"),
                row.get("project_id"),
                feature_id,
                row.get("title"), // task description is in title field
                row.get("priority"),
                row.get("status"),
                row.get("category"),
                row.get("depends_on"),
                row.get("assigned_to"),
                row.get("notes"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse task from DB: {}", e))?;
            Ok(Some(task))
        } else {
            Ok(None)
        }
    }

    /// List tasks by project with optional status filter
    pub async fn list_by_project(
        pool: &SqlitePool,
        project_id: &str,
        status: Option<TaskStatus>,
    ) -> Result<Vec<Task>> {
        let query = if status.is_some() {
            "SELECT id, project_id, feature_ids, title, category, status, priority, assigned_to, depends_on, notes, created_at, updated_at FROM tasks WHERE project_id = ? AND status = ? ORDER BY created_at DESC"
        } else {
            "SELECT id, project_id, feature_ids, title, category, status, priority, assigned_to, depends_on, notes, created_at, updated_at FROM tasks WHERE project_id = ? ORDER BY created_at DESC"
        };

        let rows = if let Some(status_filter) = status {
            sqlx::query(query)
                .bind(project_id)
                .bind(status_filter.as_str())
                .fetch_all(pool)
                .await?
        } else {
            sqlx::query(query).bind(project_id).fetch_all(pool).await?
        };

        let mut tasks = Vec::new();
        for row in rows {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            // Extract first feature_id from feature_ids field
            let feature_ids_str: String = row.get("feature_ids");
            let feature_id = if feature_ids_str.starts_with('[') {
                // Handle JSON array case later
                feature_ids_str.trim_matches(['[', ']', '"']).to_string()
            } else {
                feature_ids_str // Simple string case
            };

            let task = Task::from_db_row(
                row.get("id"),
                row.get("project_id"),
                feature_id,
                row.get("title"), // task description is in title field
                row.get("priority"),
                row.get("status"),
                row.get("category"),
                row.get("depends_on"),
                row.get("assigned_to"),
                row.get("notes"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse task from DB: {}", e))?;
            tasks.push(task);
        }

        Ok(tasks)
    }

    /// Update task status
    pub async fn update_status(pool: &SqlitePool, id: &str, new_status: TaskStatus) -> Result<()> {
        sqlx::query(
            r#"
            UPDATE tasks 
            SET status = ?, updated_at = ?
            WHERE id = ?
        "#,
        )
        .bind(new_status.as_str())
        .bind(chrono::Utc::now().to_rfc3339())
        .bind(id)
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Update complete task object
    pub async fn update(pool: &SqlitePool, task: &Task) -> Result<()> {
        let feature_ids_json = format!("{}", task.feature_id);

        sqlx::query(r#"
            UPDATE tasks 
            SET title = ?, description = ?, category = ?, status = ?, priority = ?, feature_ids = ?, assigned_to = ?, depends_on = ?, notes = ?, updated_at = ?
            WHERE id = ?
        "#)
        .bind(&task.task) // title
        .bind(&task.task) // description (using task content for both)
        .bind(&task.category)
        .bind(&task.status)
        .bind(&task.priority)
        .bind(&feature_ids_json)
        .bind(&task.assigned)
        .bind(&task.dependencies)
        .bind(&task.notes)
        .bind(chrono::Utc::now().to_rfc3339())
        .bind(&task.id)
        .execute(pool)
        .await?;
        Ok(())
    }

    /// Complete task
    pub async fn complete(
        pool: &SqlitePool,
        id: &str,
        _completion_notes: Option<String>,
    ) -> Result<()> {
        sqlx::query(
            r#"
            UPDATE tasks 
            SET status = ?, updated_at = ?
            WHERE id = ?
        "#,
        )
        .bind("completed")
        .bind(chrono::Utc::now().to_rfc3339())
        .bind(id)
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Delete task
    pub async fn delete(pool: &SqlitePool, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM tasks WHERE id = ?")
            .bind(id)
            .execute(pool)
            .await?;
        Ok(())
    }

    /// Get next sequential task ID
    async fn get_next_task_id(pool: &SqlitePool) -> Result<String> {
        let max_id: Option<String> = sqlx::query_scalar(
            "SELECT id FROM tasks ORDER BY CAST(SUBSTR(id, 2) AS INTEGER) DESC LIMIT 1",
        )
        .fetch_optional(pool)
        .await?;

        match max_id {
            Some(id) => {
                let num_str = &id[1..];
                let num: u32 = num_str.parse().unwrap_or(0);
                Ok(format!("T{:06}", num + 1))
            }
            None => Ok("T000001".to_string()),
        }
    }
}

/// Session CRUD operations
pub mod sessions {
    use super::*;

    /// Create new session with validation
    pub async fn create(
        pool: &SqlitePool,
        project_id: String,
        session_name: String,
        focus_area: Option<String>,
    ) -> Result<Session> {
        let next_id = get_next_session_id(pool).await?;
        let focus = focus_area.unwrap_or_else(|| "General development".to_string());
        let session = Session::new(next_id.clone(), project_id, session_name, focus)
            .map_err(|e| anyhow::anyhow!("Failed to create session: {}", e))?;

        // Sessions table uses 'state' instead of 'status'
        sqlx::query(r#"
            INSERT INTO sessions (id, project_id, title, date, start_time, state, focus, major_achievement, completed_tasks, key_achievements, files_modified, issues_resolved, started_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#)
        .bind(&session.id)
        .bind(&session.project_id)
        .bind(&session.title)
        .bind(&session.date)
        .bind(&session.start_time)
        .bind(&session.status) // Will map to 'state' column
        .bind(&session.focus)
        .bind(&session.major_achievement)
        .bind(&session.completed_tasks)
        .bind(&session.key_achievements)
        .bind(&session.files_modified)
        .bind(&session.issues_resolved)
        .bind(&session.created_at.to_rfc3339()) // Maps to started_at
        .bind(&session.updated_at.to_rfc3339())
        .execute(pool)
        .await?;

        Ok(session)
    }

    /// List sessions by project
    pub async fn list_by_project(pool: &SqlitePool, project_id: &str) -> Result<Vec<Session>> {
        let rows = sqlx::query(r#"
            SELECT id, project_id, title, date, start_time, end_time, state, focus, major_achievement, completed_tasks, key_achievements, files_modified, issues_resolved, started_at, updated_at
            FROM sessions WHERE project_id = ? ORDER BY started_at DESC
        "#)
        .bind(project_id)
        .fetch_all(pool)
        .await?;

        let mut sessions = Vec::new();
        for row in rows {
            let started_at = parse_db_timestamp(&row.get::<String, _>("started_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let session = Session::from_db_row(
                row.get("id"),
                row.get("project_id"),
                row.get("title"),
                row.get("date"),
                row.get("start_time"),
                row.get("end_time"),
                row.get("state"),
                row.get("focus"),
                row.get("major_achievement"),
                row.get("completed_tasks"),
                row.get("key_achievements"),
                row.get("files_modified"),
                row.get("issues_resolved"),
                started_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse session from DB: {}", e))?;
            sessions.push(session);
        }

        Ok(sessions)
    }

    /// Get session by ID
    pub async fn get_by_id(pool: &SqlitePool, id: &str) -> Result<Option<Session>> {
        let row = sqlx::query(r#"
            SELECT id, project_id, title, date, start_time, end_time, state, focus, major_achievement, completed_tasks, key_achievements, files_modified, issues_resolved, started_at, updated_at
            FROM sessions WHERE id = ?
        "#)
        .bind(id)
        .fetch_optional(pool)
        .await?;

        if let Some(row) = row {
            let started_at = parse_db_timestamp(&row.get::<String, _>("started_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let session = Session::from_db_row(
                row.get("id"),
                row.get("project_id"),
                row.get("title"),
                row.get("date"),
                row.get("start_time"),
                row.get("end_time"),
                row.get("state"), // Map state to status
                row.get("focus"),
                row.get("major_achievement"),
                row.get("completed_tasks"),
                row.get("key_achievements"),
                row.get("files_modified"),
                row.get("issues_resolved"),
                started_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse session from DB: {}", e))?;
            Ok(Some(session))
        } else {
            Ok(None)
        }
    }

    /// Complete session
    pub async fn complete(pool: &SqlitePool, id: &str, _summary: String) -> Result<()> {
        // TODO: Implement session completion when schema is finalized
        sqlx::query(
            r#"
            UPDATE sessions 
            SET state = ?
            WHERE id = ?
        "#,
        )
        .bind("completed")
        .bind(id)
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Delete session (SET NULL will update dependent tasks and audit trails)
    pub async fn delete(pool: &SqlitePool, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM sessions WHERE id = ?")
            .bind(id)
            .execute(pool)
            .await?;
        Ok(())
    }

    /// Get next sequential session ID
    async fn get_next_session_id(pool: &SqlitePool) -> Result<String> {
        let max_id: Option<String> = sqlx::query_scalar(
            "SELECT id FROM sessions ORDER BY CAST(SUBSTR(id, 2) AS INTEGER) DESC LIMIT 1",
        )
        .fetch_optional(pool)
        .await?;

        match max_id {
            Some(id) => {
                let num_str = &id[1..];
                let num: u32 = num_str.parse().unwrap_or(0);
                Ok(format!("S{:06}", num + 1))
            }
            None => Ok("S000001".to_string()),
        }
    }
}

/// Directive CRUD operations
pub mod directives {
    use super::*;

    /// Create new directive with validation
    pub async fn create(
        pool: &SqlitePool,
        project_id: String,
        title: String,
        rule: String,
        category: DirectiveCategory,
        priority: Priority,
    ) -> Result<Directive> {
        let next_id = get_next_directive_id(pool).await?;
        let directive = Directive::new(next_id.clone(), project_id, title, rule)
            .map_err(|e| anyhow::anyhow!("Failed to create directive: {}", e))?;

        sqlx::query(r#"
            INSERT INTO directives (id, project_id, code, title, rule, category, priority, status, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        "#)
        .bind(&directive.id)
        .bind(&directive.project_id)
        .bind(&directive.id) // Use directive ID as code
        .bind(&directive.title)
        .bind(&directive.rule)
        .bind(category.as_str())
        .bind(priority.as_str())
        .bind("active") // Default status
        .bind(&directive.created_at.to_rfc3339())
        .bind(&directive.updated_at.to_rfc3339())
        .execute(pool)
        .await?;

        // Fetch the created directive from database to get all fields properly set
        get_by_id(pool, &directive.id)
            .await?
            .ok_or_else(|| anyhow::anyhow!("Failed to retrieve created directive"))
    }

    /// Get directive by ID
    pub async fn get_by_id(pool: &SqlitePool, id: &str) -> Result<Option<Directive>> {
        let row = sqlx::query(r#"
            SELECT id, project_id, title, rule, priority, status, context, rationale, category, created_at, updated_at 
            FROM directives WHERE id = ?
        "#)
        .bind(id)
        .fetch_optional(pool)
        .await?;

        if let Some(row) = row {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let directive = Directive::from_db_row(
                row.get("id"),
                row.get("project_id"),
                row.get("title"),
                row.get("rule"),
                row.get("priority"),
                row.get("status"),
                row.get("context"),
                row.get("rationale"),
                row.get("category"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse directive from DB: {}", e))?;
            Ok(Some(directive))
        } else {
            Ok(None)
        }
    }

    /// List active directives by project
    pub async fn list_active_by_project(
        pool: &SqlitePool,
        project_id: &str,
    ) -> Result<Vec<Directive>> {
        let rows = sqlx::query(r#"
            SELECT id, project_id, title, rule, priority, status, context, rationale, category, created_at, updated_at 
            FROM directives WHERE project_id = ? AND status = 'active' ORDER BY priority DESC, created_at DESC
        "#)
        .bind(project_id)
        .fetch_all(pool)
        .await?;

        let mut directives = Vec::new();
        for row in rows {
            let created_at = parse_db_timestamp(&row.get::<String, _>("created_at"))?;
            let updated_at = parse_db_timestamp(&row.get::<String, _>("updated_at"))?;

            let directive = Directive::from_db_row(
                row.get("id"),
                row.get("project_id"),
                row.get("title"),
                row.get("rule"),
                row.get("priority"),
                row.get("status"),
                row.get("context"),
                row.get("rationale"),
                row.get("category"),
                created_at,
                updated_at,
            )
            .map_err(|e| anyhow::anyhow!("Failed to parse directive from DB: {}", e))?;
            directives.push(directive);
        }
        Ok(directives)
    }

    /// Deactivate directive
    pub async fn deactivate(pool: &SqlitePool, id: &str) -> Result<()> {
        // Simplified implementation - just update status to inactive
        sqlx::query(
            r#"
            UPDATE directives 
            SET status = ?, updated_at = ?
            WHERE id = ?
        "#,
        )
        .bind("inactive")
        .bind(chrono::Utc::now().to_rfc3339())
        .bind(id)
        .execute(pool)
        .await?;

        Ok(())
    }

    /// Delete directive
    pub async fn delete(pool: &SqlitePool, id: &str) -> Result<()> {
        sqlx::query("DELETE FROM directives WHERE id = ?")
            .bind(id)
            .execute(pool)
            .await?;
        Ok(())
    }

    /// Get next sequential directive ID
    async fn get_next_directive_id(pool: &SqlitePool) -> Result<String> {
        let max_id: Option<String> = sqlx::query_scalar(
            "SELECT id FROM directives ORDER BY CAST(SUBSTR(id, 2) AS INTEGER) DESC LIMIT 1",
        )
        .fetch_optional(pool)
        .await?;

        match max_id {
            Some(id) => {
                let num_str = &id[1..];
                let num: u32 = num_str.parse().unwrap_or(0);
                Ok(format!("D{:03}", num + 1))
            }
            None => Ok("D001".to_string()),
        }
    }
}

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

    #[test]
    fn parse_db_timestamp_accepts_sqlite_and_rfc3339_formats() {
        let sqlite = parse_db_timestamp("2026-03-28 22:15:04").unwrap();
        let rfc3339 = parse_db_timestamp("2026-03-28T22:15:04Z").unwrap();

        assert_eq!(sqlite.timestamp(), rfc3339.timestamp());
    }
}