//! `keg db` command — Database operations

use anyhow::{Context, Result};
use console::{style, Emoji};
use std::process::Command;

/// Run database actions
pub fn run_db_action(action: DbAction) -> Result<()> {
    let project_dir = std::env::current_dir().context("Failed to get current directory")?;

    println!();
    println!("{} {}", Emoji("🗄️", ""), style("Database Operations").bold());
    println!("{}", style("━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━").dim());

    match action {
        DbAction::Create => db_create(&project_dir)?,
        DbAction::Drop => db_drop(&project_dir)?,
        DbAction::Seed => db_seed(&project_dir)?,
        DbAction::Psql => db_psql(&project_dir)?,
        DbAction::Schema => db_schema(&project_dir)?,
        DbAction::Reset => db_reset(&project_dir)?,
    }

    Ok(())
}

fn db_create(project_dir: &std::path::Path) -> Result<()> {
    println!("  {} Creating database...", style("📦").cyan());

    // Try using sqlx first, fall back to psql
    let result = Command::new("sqlx")
        .args(["database", "create"])
        .current_dir(project_dir)
        .status();

    if result.map(|s| !s.success()).unwrap_or(true) {
        // Fall back to psql
        let db_url = std::env::var("DATABASE_URL").context("DATABASE_URL not set")?;
        let db_name = extract_db_name(&db_url)?;

        println!("  {} Using psql to create database...", style("→").dim());

        let status = Command::new("psql")
            .args(["-c", &format!("CREATE DATABASE {};", db_name)])
            .env("PGPASSWORD", extract_password(&db_url).unwrap_or_else(|| "".to_string()))
            .current_dir(project_dir)
            .status()
            .context("Failed to run psql")?;

        if !status.success() {
            anyhow::bail!("Failed to create database");
        }
    }

    println!();
    println!("{} {}", Emoji("✅", ""), style("Database created successfully!").green());
    Ok(())
}

fn db_drop(project_dir: &std::path::Path) -> Result<()> {
    println!("  {} Dropping database...", style("🗑️").cyan());
    println!("  {} {}", style("⚠️").yellow(), style("WARNING: This will delete all data!").red().bold());

    // Try using sqlx first, fall back to psql
    let result = Command::new("sqlx")
        .args(["database", "drop"])
        .current_dir(project_dir)
        .status();

    if result.map(|s| !s.success()).unwrap_or(true) {
        // Fall back to psql
        let db_url = std::env::var("DATABASE_URL").context("DATABASE_URL not set")?;
        let db_name = extract_db_name(&db_url)?;

        println!("  {} Using psql to drop database...", style("→").dim());

        let status = Command::new("psql")
            .args(["-c", &format!("DROP DATABASE {};", db_name)])
            .env("PGPASSWORD", extract_password(&db_url).unwrap_or_else(|| "".to_string()))
            .current_dir(project_dir)
            .status()
            .context("Failed to run psql")?;

        if !status.success() {
            anyhow::bail!("Failed to drop database");
        }
    }

    println!();
    println!("{} {}", Emoji("✅", ""), style("Database dropped successfully!").green());
    Ok(())
}

fn db_seed(project_dir: &std::path::Path) -> Result<()> {
    println!("  {} Seeding database...", style("🌱").cyan());

    // Check for seed script
    let seed_file = project_dir.join("migrations").join("seed.sql");

    if seed_file.exists() {
        let db_url = std::env::var("DATABASE_URL").context("DATABASE_URL not set")?;

        let status = Command::new("psql")
            .args([&db_url, "-f", seed_file.to_str().unwrap()])
            .current_dir(project_dir)
            .status()
            .context("Failed to run seed script")?;

        if !status.success() {
            anyhow::bail!("Seed failed");
        }
    } else {
        println!("  {} {}", style("ℹ").dim(), style("No seed.sql found in migrations/. Creating sample...").dim());

        // Create a sample seed file
        let seed_content = r#"-- Sample seed data
-- Add your seed SQL here

-- Example:
-- INSERT INTO users (name, email) VALUES ('Admin', 'admin@example.com');
"#;
        std::fs::write(&seed_file, seed_content)?;
        println!("  {} {}", style("✓").green(), style("Created migrations/seed.sql").dim());
        println!("  {} {}", style("→").cyan(), style("Edit migrations/seed.sql and run `keg db seed` again").dim());
        return Ok(());
    }

    println!();
    println!("{} {}", Emoji("✅", ""), style("Database seeded successfully!").green());
    Ok(())
}

fn db_psql(project_dir: &std::path::Path) -> Result<()> {
    println!("  {} Opening psql shell...", style("🔗").cyan());

    let db_url = std::env::var("DATABASE_URL").context("DATABASE_URL not set")?;

    let mut cmd = Command::new("psql");
    cmd.arg(&db_url)
        .current_dir(project_dir)
        .status()
        .context("Failed to open psql. Is psql installed?")?;

    Ok(())
}

fn db_schema(project_dir: &std::path::Path) -> Result<()> {
    println!("  {} Showing database schema...", style("📋").cyan());

    let db_url = std::env::var("DATABASE_URL").context("DATABASE_URL not set")?;

    // Query to show tables
    let tables_query = r#"
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
"#;

    let output = Command::new("psql")
        .args(["-c", tables_query])
        .arg(&db_url)
        .current_dir(project_dir)
        .output()
        .context("Failed to run psql. Is psql installed?")?;

    println!();
    println!("{}", style("Tables:").bold());
    println!("{}", String::from_utf8_lossy(&output.stdout));

    Ok(())
}

fn db_reset(project_dir: &std::path::Path) -> Result<()> {
    println!();
    println!("{} {} This will reset the database!", style("⚠️").yellow(), style("WARNING").red().bold());
    println!();

    // Drop
    db_drop(project_dir)?;

    // Create
    db_create(project_dir)?;

    // Run migrations
    println!();
    println!("  {} Running migrations...", style("📦").cyan());
    let migrate_result = Command::new("sqlx")
        .args(["migrate", "run"])
        .current_dir(project_dir)
        .status();

    if migrate_result.map(|s| !s.success()).unwrap_or(false) {
        println!("  {} {}", style("⚠").yellow(), style("sqlx-cli not available, skipping migrations").dim());
    }

    // Seed
    db_seed(project_dir)?;

    println!();
    println!("{} {}", Emoji("✅", ""), style("Database reset complete!").green());
    Ok(())
}

// Helper functions
fn extract_db_name(url: &str) -> Result<String> {
    // Parse postgresql://user:pass@host:5432/dbname
    if let Some(i) = url.rfind('/') {
        let after_slash = &url[i+1..];
        if let Some(j) = after_slash.find('?') {
            Ok(after_slash[..j].to_string())
        } else {
            Ok(after_slash.to_string())
        }
    } else {
        anyhow::bail!("Invalid DATABASE_URL format")
    }
}

fn extract_password(url: &str) -> Option<String> {
    // Extract password from postgresql://user:pass@host:5432/dbname
    if let Some(i) = url.find(':') {
        if let Some(j) = url.find('@') {
            return Some(url[i+1..j].to_string());
        }
    }
    None
}

#[derive(clap::Subcommand)]
pub enum DbAction {
    /// Create the database
    Create,
    /// Drop the database
    Drop,
    /// Seed the database with sample data
    Seed,
    /// Open psql shell
    Psql,
    /// Show database schema
    Schema,
    /// Reset the database (drop + create + seed)
    Reset,
}
