//! Database persistence abstraction layer
//!
//! This module provides a trait-based abstraction for database operations,
//! allowing multiple backend implementations (SQLite, SurrealDB, PostgreSQL, etc.)
//!
//! # Architecture
//!
//! The module is structured around the `Database` trait which defines all
//! operations needed for syntaxis management. Multiple implementations
//! provide concrete backends:
//!
//! - `SqliteDatabase`: SQLite-based implementation (default)
//! - `SurrealDatabase`: SurrealDB implementation (cloud-ready)
//! - `MockDatabase`: In-memory mock for testing
//!
//! # Usage
//!
//! ```ignore
//! use syntaxis_core::persistence::{Database, SqliteDatabase};
//!
//! let db = SqliteDatabase::new("workspace.db").await?;
//! let projects = db.list_projects().await?;
//! ```

pub mod config;
pub mod error;
pub mod migration;
pub mod sqlite_impl;
#[cfg(feature = "surrealdb-backend")]
pub mod surrealdb_impl;

// Re-export public types
pub use config::DatabaseConfig;
#[cfg(feature = "surrealdb-backend")]
pub use config::{SurrealDbBackendConfig, SurrealEngineConfig};
pub use error::PersistenceError;
pub use sqlite_impl::SqliteDatabase;
#[cfg(feature = "surrealdb-backend")]
pub use surrealdb_impl::SurrealDatabase;

use crate::error::Result;
use async_trait::async_trait;

// ============================================================================
// DATA TYPES THAT MUST BE MOVED FROM persistence.rs
// ============================================================================

use serde::{Deserialize, Serialize};

/// Project representation in database
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbProject {
    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,
}

/// Checklist item representation
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbChecklistItem {
    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,
    pub task_type: String,
    pub task_priority: String,
    pub task_due: Option<String>,
    pub task_estimation: Option<String>,
    pub task_deps: String,
    pub task_note: Option<String>,
    pub task_name: Option<String>,
}

/// Phase transition record
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbPhaseTransition {
    pub id: String,
    pub project_id: String,
    pub from_phase: String,
    pub to_phase: String,
    pub timestamp: String,
    pub reason: Option<String>,
}

/// Security assessment record
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbSecurityAssessment {
    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,
}

/// Phase history record
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbPhaseHistory {
    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>,
}

/// Tool configuration record
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbToolConfiguration {
    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,
}

/// Tool dependency record
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbToolDependency {
    pub id: String,
    pub project_id: String,
    pub tool_name: String,
    pub dependency_name: String,
}

/// Security assessment detail
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbSecurityAssessmentDetail {
    pub id: String,
    pub assessment_id: String,
    pub rule_name: String,
    pub passed: bool,
}

/// Team member record
#[allow(missing_docs)]
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq, Eq, sqlx::FromRow)]
pub struct DbTeamMember {
    pub id: String,
    pub name: String,
    pub email: String,
}

// ============================================================================
// CORE DATABASE TRAIT
// ============================================================================

/// Core database operations trait
///
/// This trait defines all database operations required for syntaxis management.
/// Implementations must support async operations and return proper error types.
///
/// # Implementing
///
/// Implementors should:
/// - Handle connection pooling internally
/// - Return proper errors via `PersistenceError`
/// - Ensure thread-safety via `Send + Sync + Clone`
/// - Initialize schema on first connection
#[async_trait]
pub trait Database: Send + Sync + Clone {
    // ========== PROJECT OPERATIONS ==========

    /// Create a new project
    async fn create_project(&self, project: &DbProject) -> Result<DbProject>;

    /// Get project by ID
    async fn get_project(&self, id: &str) -> Result<Option<DbProject>>;

    /// List all projects
    async fn list_projects(&self) -> Result<Vec<DbProject>>;

    /// Update project
    async fn update_project(&self, project: &DbProject) -> Result<DbProject>;

    /// Delete project
    async fn delete_project(&self, id: &str) -> Result<()>;

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

    /// Create checklist item
    async fn create_checklist_item(&self, item: &DbChecklistItem) -> Result<DbChecklistItem>;

    /// Get checklist items for project
    async fn get_checklist_items(&self, project_id: &str) -> Result<Vec<DbChecklistItem>>;

    /// Get checklist items for specific phase
    async fn get_checklist_items_by_phase(
        &self,
        project_id: &str,
        phase: &str,
    ) -> Result<Vec<DbChecklistItem>>;

    /// Update checklist item
    async fn update_checklist_item(&self, item: &DbChecklistItem) -> Result<DbChecklistItem>;

    /// Mark checklist item as completed
    async fn complete_checklist_item(&self, id: &str) -> Result<()>;

    /// Delete checklist item
    async fn delete_checklist_item(&self, id: &str) -> Result<()>;

    /// Get completion percentage for project phase
    async fn get_completion_percentage(&self, project_id: &str, phase: &str) -> Result<f64>;

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

    /// Record phase transition
    async fn record_phase_transition(
        &self,
        transition: &DbPhaseTransition,
    ) -> Result<DbPhaseTransition>;

    /// Get phase history for project
    async fn get_phase_history(&self, project_id: &str) -> Result<Vec<DbPhaseTransition>>;

    /// Get current phase of project
    async fn get_current_phase(&self, project_id: &str) -> Result<String>;

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

    /// Create security assessment
    async fn create_security_assessment(
        &self,
        assessment: &DbSecurityAssessment,
    ) -> Result<DbSecurityAssessment>;

    /// Get latest assessment for project
    async fn get_latest_assessment(&self, project_id: &str)
        -> Result<Option<DbSecurityAssessment>>;

    /// List assessments for project
    async fn list_assessments(&self, project_id: &str) -> Result<Vec<DbSecurityAssessment>>;

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

    /// Record phase history entry
    async fn record_phase_history(&self, history: &DbPhaseHistory) -> Result<DbPhaseHistory>;

    /// Get phase history for project
    async fn get_phase_history_records(&self, project_id: &str) -> Result<Vec<DbPhaseHistory>>;

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

    /// Create tool configuration
    async fn create_tool_config(&self, config: &DbToolConfiguration)
        -> Result<DbToolConfiguration>;

    /// Get tool configuration
    async fn get_tool_config(&self, id: &str) -> Result<Option<DbToolConfiguration>>;

    /// List tool configurations for project
    async fn list_tool_configs(&self, project_id: &str) -> Result<Vec<DbToolConfiguration>>;

    /// Find tool configuration by name
    async fn find_tool_config(
        &self,
        project_id: &str,
        tool_name: &str,
    ) -> Result<Option<DbToolConfiguration>>;

    /// Update tool configuration
    async fn update_tool_config(&self, config: &DbToolConfiguration)
        -> Result<DbToolConfiguration>;

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

    /// Create tool dependency
    async fn create_tool_dependency(
        &self,
        dependency: &DbToolDependency,
    ) -> Result<DbToolDependency>;

    /// Get tool dependencies for project
    async fn get_tool_dependencies(&self, project_id: &str) -> Result<Vec<DbToolDependency>>;

    /// Delete tool dependency
    async fn delete_tool_dependency(&self, id: &str) -> Result<()>;

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

    /// Create assessment detail
    async fn create_assessment_detail(
        &self,
        detail: &DbSecurityAssessmentDetail,
    ) -> Result<DbSecurityAssessmentDetail>;

    /// Get assessment details
    async fn get_assessment_details(
        &self,
        assessment_id: &str,
    ) -> Result<Vec<DbSecurityAssessmentDetail>>;

    /// Delete assessment detail
    async fn delete_assessment_detail(&self, id: &str) -> Result<()>;

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

    /// Create team member
    async fn create_team_member(&self, member: &DbTeamMember) -> Result<DbTeamMember>;

    /// Get team member
    async fn get_team_member(&self, id: &str) -> Result<Option<DbTeamMember>>;

    /// List team members
    async fn list_team_members(&self) -> Result<Vec<DbTeamMember>>;

    /// Update team member
    async fn update_team_member(&self, member: &DbTeamMember) -> Result<DbTeamMember>;

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

    /// Check database connection health
    async fn ping(&self) -> Result<()>;

    /// Initialize database schema (called on startup)
    async fn init_schema(&self) -> Result<()>;
}

// ============================================================================
// CONCRETE IMPLEMENTATIONS (exported at module level)
// ============================================================================

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

    #[test]
    fn test_db_project_debug() {
        let project = DbProject {
            id: "test".to_string(),
            name: "Test Project".to_string(),
            version: "1.0".to_string(),
            description: "A test project".to_string(),
            project_type: "MultiLang".to_string(),
            current_phase: "Creation".to_string(),
            created_at: "2025-01-01T00:00:00Z".to_string(),
            updated_at: "2025-01-01T00:00:00Z".to_string(),
        };

        let debug_str = format!("{:?}", project);
        assert!(debug_str.contains("DbProject"));
    }

    #[test]
    fn test_checklist_item_serialization() {
        let item = DbChecklistItem {
            id: "1".to_string(),
            project_id: "p1".to_string(),
            phase: "Creation".to_string(),
            task_id: "t1".to_string(),
            description: "Do something".to_string(),
            completed: false,
            completed_at: None,
            created_at: "2025-01-01T00:00:00Z".to_string(),
            task_type: "Feature".to_string(),
            task_priority: "High".to_string(),
            task_due: None,
            task_estimation: Some("5d".to_string()),
            task_deps: "[]".to_string(),
            task_note: None,
            task_name: Some("Test Task".to_string()),
        };

        let json = serde_json::to_string(&item).expect("Should serialize");
        let restored: DbChecklistItem = serde_json::from_str(&json).expect("Should deserialize");

        assert_eq!(item, restored);
    }
}
