// Example: SurrealDB Integration for syntaxis
// This demonstrates the trait-based abstraction pattern

// ============================================================================
// Step 1: Define Database Trait (workspace-core/src/persistence/mod.rs)
// ============================================================================

use async_trait::async_trait;
use crate::error::Result;
use crate::types::*;

/// Unified database trait - implementation agnostic
#[async_trait]
pub trait Database: Send + Sync + Clone {
    // ---- Project Management ----

    /// Create a new project
    async fn create_project(&self, name: &str, description: &str, project_type: &str) -> Result<Project>;

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

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

    /// Update project
    async fn update_project(&self, id: &str, name: &str, description: &str) -> Result<Project>;

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

    // ---- Checklist Management ----

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

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

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

    // ---- Phase Management ----

    /// Record phase transition (with graph edge)
    async fn record_phase_transition(&self, transition: &PhaseTransition) -> Result<()>;

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

    // ---- Security Assessments ----

    /// Store security assessment result
    async fn create_assessment(&self, assessment: &SecurityAssessment) -> Result<SecurityAssessment>;

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

    // ---- Tool Configuration ----

    /// Save tool configuration
    async fn set_tool_config(&self, project_id: &str, tool_name: &str, config: &serde_json::Value) -> Result<()>;

    /// Get tool configuration
    async fn get_tool_config(&self, project_id: &str, tool_name: &str) -> Result<Option<serde_json::Value>>;

    // ---- Audit Trail ----

    /// Log activity
    async fn log_activity(&self, activity: &Activity) -> Result<()>;

    /// Get activity history
    async fn get_activities(&self, project_id: &str, limit: u32) -> Result<Vec<Activity>>;
}

// ============================================================================
// Step 2: SQLite Implementation (workspace-core/src/persistence/sqlite_impl.rs)
// ============================================================================

use sqlx::{SqlitePool, Row};

#[derive(Clone)]
pub struct SqliteDatabase {
    pool: SqlitePool,
}

impl SqliteDatabase {
    pub async fn new(db_path: &str) -> Result<Self> {
        let path = std::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?;
            }
        }

        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);

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

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

        Ok(Self { pool })
    }

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

        // Create tables (existing code)
        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?;

        // ... more CREATE TABLE statements

        Ok(())
    }
}

#[async_trait]
impl Database for SqliteDatabase {
    async fn create_project(&self, name: &str, description: &str, project_type: &str) -> Result<Project> {
        let id = uuid::Uuid::new_v4().to_string();
        let now = chrono::Utc::now().to_rfc3339();

        sqlx::query(
            "INSERT INTO projects (id, name, version, description, project_type, current_phase, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        )
        .bind(&id)
        .bind(name)
        .bind("0.1.0")
        .bind(description)
        .bind(project_type)
        .bind("Creation")
        .bind(&now)
        .bind(&now)
        .execute(&self.pool)
        .await?;

        Ok(Project {
            id,
            name: name.to_string(),
            version: "0.1.0".to_string(),
            description: description.to_string(),
            project_type: project_type.to_string(),
            current_phase: "Creation".to_string(),
            created_at: now.clone(),
            updated_at: now,
        })
    }

    async fn get_project(&self, id: &str) -> Result<Project> {
        let row = sqlx::query_as::<_, Project>(
            "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_or_else(|| crate::error::LifecycleError::ProjectNotFound(id.to_string()))?;

        Ok(row)
    }

    // ... implement all other trait methods
}

// ============================================================================
// Step 3: SurrealDB Implementation (workspace-core/src/persistence/surrealdb_impl.rs)
// ============================================================================

use surrealdb::{Client, Surreal};
use surrealdb::opt::auth::Root;

#[derive(Clone)]
pub struct SurrealDatabase {
    db: Surreal<Client>,
}

impl SurrealDatabase {
    pub async fn new(url: &str, namespace: &str, database: &str) -> Result<Self> {
        // Connect to SurrealDB
        let db = Surreal::new::<Ws>(url)
            .await?;

        // Authenticate (if needed)
        db.signin(Root {
            username: "root",
            password: "root",
        })
        .await?;

        // Select namespace and database
        db.use_ns(namespace).use_db(database).await?;

        // Initialize schema
        Self::init_schema(&db).await?;

        Ok(Self { db })
    }

    async fn init_schema(db: &Surreal<Client>) -> Result<()> {
        // Define collection for projects
        db.query(
            "DEFINE TABLE projects SCHEMAFULL
                COMMENT = 'Project collection with typed schema';"
        )
        .await?;

        // Define fields
        db.query("DEFINE FIELD projects.id AS string")
            .await?;
        db.query("DEFINE FIELD projects.name AS string")
            .await?;
        db.query("DEFINE FIELD projects.version AS string")
            .await?;
        db.query("DEFINE FIELD projects.current_phase AS enum<Creation,Development,Testing,Publishing,Archived>")
            .await?;

        // Define graph relation for phase transitions
        db.query(
            "DEFINE TABLE transitioned_to SCHEMALESS
                COMMENT = 'Phase transition relationships';"
        )
        .await?;

        // Define indices
        db.query("DEFINE INDEX idx_projects_created ON TABLE projects COLUMNS created_at DESC")
            .await?;
        db.query("DEFINE INDEX idx_checklist_project ON TABLE checklist_items COLUMNS project_id")
            .await?;

        Ok(())
    }
}

#[async_trait]
impl Database for SurrealDatabase {
    async fn create_project(&self, name: &str, description: &str, project_type: &str) -> Result<Project> {
        let id = uuid::Uuid::new_v4().to_string();
        let now = chrono::Utc::now().to_rfc3339();

        let project = Project {
            id: id.clone(),
            name: name.to_string(),
            version: "0.1.0".to_string(),
            description: description.to_string(),
            project_type: project_type.to_string(),
            current_phase: "Creation".to_string(),
            created_at: now.clone(),
            updated_at: now,
        };

        // Create project in SurrealDB (document-style)
        self.db.create::<Project>(("projects", &id))
            .content(&project)
            .await?;

        Ok(project)
    }

    async fn get_project(&self, id: &str) -> Result<Project> {
        let project: Option<Project> = self.db.select(("projects", id))
            .await?;

        project.ok_or_else(|| crate::error::LifecycleError::ProjectNotFound(id.to_string()))
    }

    async fn list_projects(&self) -> Result<Vec<Project>> {
        // SurrealDB uses SQL-like syntax
        let projects: Vec<Project> = self.db
            .query("SELECT * FROM projects WHERE archived = false")
            .await?
            .take(0)?;

        Ok(projects)
    }

    async fn record_phase_transition(&self, transition: &PhaseTransition) -> Result<()> {
        // Create graph edge from project to phase
        self.db.query(
            "LET $project = type::thing('projects', $project_id);
             LET $to_phase = type::thing('phases', $to_phase);
             RELATE $project->transitioned_to->$to_phase
                SET
                    reason = $reason,
                    timestamp = $timestamp,
                    from_phase = $from_phase;"
        )
        .bind(("project_id", &transition.project_id))
        .bind(("to_phase", &transition.to_phase))
        .bind(("from_phase", &transition.from_phase))
        .bind(("reason", &transition.reason))
        .bind(("timestamp", &transition.timestamp))
        .await?;

        Ok(())
    }

    async fn get_phase_history(&self, project_id: &str) -> Result<Vec<PhaseTransition>> {
        // Query graph edges for this project
        let transitions: Vec<PhaseTransition> = self.db
            .query("SELECT * FROM type::thing('projects', $id) -> transitioned_to ->")
            .bind(("id", project_id))
            .await?
            .take(0)?;

        Ok(transitions)
    }

    // ... implement remaining trait methods
}

// ============================================================================
// Step 4: Enum-based Runtime Selection
// ============================================================================

pub enum DatabaseBackend {
    Sqlite(SqliteDatabase),
    SurrealDB(SurrealDatabase),
}

impl DatabaseBackend {
    pub async fn from_config(config: &DatabaseConfig) -> Result<Self> {
        match config.engine.as_str() {
            "sqlite" => {
                let db = SqliteDatabase::new(&config.sqlite_path).await?;
                Ok(DatabaseBackend::Sqlite(db))
            }
            "surrealdb" => {
                let db = SurrealDatabase::new(
                    &config.surrealdb.url,
                    &config.surrealdb.namespace,
                    &config.surrealdb.database,
                )
                .await?;
                Ok(DatabaseBackend::SurrealDB(db))
            }
            _ => Err(crate::error::LifecycleError::Config(
                format!("Unknown database engine: {}", config.engine)
            )),
        }
    }
}

// Make enum implement Database trait (delegate pattern)
#[async_trait]
impl Database for DatabaseBackend {
    async fn create_project(&self, name: &str, description: &str, project_type: &str) -> Result<Project> {
        match self {
            DatabaseBackend::Sqlite(db) => db.create_project(name, description, project_type).await,
            DatabaseBackend::SurrealDB(db) => db.create_project(name, description, project_type).await,
        }
    }

    async fn get_project(&self, id: &str) -> Result<Project> {
        match self {
            DatabaseBackend::Sqlite(db) => db.get_project(id).await,
            DatabaseBackend::SurrealDB(db) => db.get_project(id).await,
        }
    }

    // ... delegate all trait methods
}

// ============================================================================
// Step 5: Usage in Application Code (unchanged!)
// ============================================================================

pub async fn main_application(db: impl Database) -> Result<()> {
    // Create project (works with both SQLite AND SurrealDB!)
    let project = db.create_project(
        "My Project",
        "A workspace project",
        "MultiLang",
    ).await?;

    println!("Created project: {}", project.id);

    // Fetch project
    let fetched = db.get_project(&project.id).await?;
    println!("Fetched: {}", fetched.name);

    // Record phase transition
    db.record_phase_transition(&PhaseTransition {
        project_id: project.id.clone(),
        from_phase: "Creation".to_string(),
        to_phase: "Development".to_string(),
        timestamp: chrono::Utc::now().to_rfc3339(),
        reason: Some("Starting development phase".to_string()),
    }).await?;

    Ok(())
}

// ============================================================================
// Configuration Example
// ============================================================================

#[derive(serde::Deserialize)]
pub struct DatabaseConfig {
    pub engine: String,  // "sqlite" or "surrealdb"
    pub sqlite_path: String,
    pub surrealdb: SurrealDBConfig,
}

#[derive(serde::Deserialize)]
pub struct SurrealDBConfig {
    pub url: String,
    pub namespace: String,
    pub database: String,
    pub username: String,
    pub password: String,
}

// ============================================================================
// Key Benefits of This Approach
// ============================================================================

/*
1. ✅ ZERO BREAKING CHANGES
   - Existing code using `Database` trait works with both implementations
   - Can switch backends at runtime via configuration

2. ✅ GRADUAL MIGRATION
   - Deploy with SQLite (default)
   - Add SurrealDB support incrementally
   - Let users choose which backend to use

3. ✅ TESTABLE
   - Create mock implementation for testing
   - Test business logic without database
   - Each backend tested independently

4. ✅ PERFORMANCE
   - Can benchmark both backends
   - Keep SQLite for simple deployments
   - Use SurrealDB for advanced features (graph queries, live subscriptions)

5. ✅ FUTURE-PROOF
   - Easy to add PostgreSQL support later
   - Easy to add MongoDB support later
   - Any database can implement the trait

6. ✅ OPERATIONAL FLEXIBILITY
   - Run same code with different databases
   - Migrate data at your own pace
   - Rollback easily if needed
*/