use anyhow::{Result, anyhow};
use chrono::Utc;
use std::collections::BTreeMap;
use std::path::Path;

#[derive(Debug, Clone)]
pub struct Migration {
    pub version: i64,
    pub name: String,
    pub sql: String,
}

/// Migration discovery that reads migration files from the filesystem at runtime
pub struct RuntimeMigrationDiscovery {
    migrations_dir: std::path::PathBuf,
    migrations: BTreeMap<i64, Migration>,
}

impl RuntimeMigrationDiscovery {
    pub fn new<P: AsRef<Path>>(migrations_dir: P) -> Result<Self> {
        let migrations_dir = migrations_dir.as_ref().to_path_buf();
        let mut discovery = Self {
            migrations_dir,
            migrations: BTreeMap::new(),
        };
        discovery.discover_migrations()?;
        Ok(discovery)
    }

    fn discover_migrations(&mut self) -> Result<()> {
        if !self.migrations_dir.exists() {
            return Err(anyhow!(
                "Migrations directory does not exist: {:?}",
                self.migrations_dir
            ));
        }

        let entries = std::fs::read_dir(&self.migrations_dir)?;

        for entry in entries {
            let entry = entry?;
            let path = entry.path();

            if path.is_file() && path.extension().map_or(false, |ext| ext == "sql") {
                let migration = self.parse_migration_file(&path)?;
                self.migrations.insert(migration.version, migration);
            }
        }

        Ok(())
    }

    fn parse_migration_file(&self, path: &Path) -> Result<Migration> {
        let file_name = path
            .file_name()
            .and_then(|n| n.to_str())
            .ok_or_else(|| anyhow!("Invalid migration file name: {:?}", path))?;

        let (version, name) = parse_migration_filename(file_name)?;
        let sql = std::fs::read_to_string(path)?;

        Ok(Migration { version, name, sql })
    }

    pub fn get_migrations(&self) -> Vec<&Migration> {
        self.migrations.values().collect()
    }

    pub fn get_migration(&self, version: i64) -> Option<&Migration> {
        self.migrations.get(&version)
    }

    pub fn get_pending_migrations(&self, current_version: i64) -> Vec<&Migration> {
        self.migrations
            .values()
            .filter(|m| m.version > current_version)
            .collect()
    }

    pub fn refresh(&mut self) -> Result<()> {
        self.migrations.clear();
        self.discover_migrations()
    }
}

/// Parse migration filename to extract timestamp and name
/// Expected format: "20231201123456_initial_schema.sql" -> (20231201123456, "initial_schema")
fn parse_migration_filename(filename: &str) -> Result<(i64, String)> {
    if !filename.ends_with(".sql") {
        return Err(anyhow!(
            "Migration file must have .sql extension: {}",
            filename
        ));
    }

    let name_without_ext = &filename[..filename.len() - 4];

    // Find first underscore
    let underscore_pos = name_without_ext.find('_').ok_or_else(|| {
        anyhow!(
            "Migration filename must be in format 'YYYYMMDDHHMMSS_name.sql': {}",
            filename
        )
    })?;

    let timestamp_str = &name_without_ext[..underscore_pos];
    let name = &name_without_ext[underscore_pos + 1..];

    // Validate timestamp format (should be 14 digits for YYYYMMDDHHMMSS)
    if timestamp_str.len() != 14 {
        return Err(anyhow!(
            "Timestamp must be 14 digits (YYYYMMDDHHMMSS) in migration filename: {}",
            filename
        ));
    }

    let timestamp = timestamp_str
        .parse::<i64>()
        .map_err(|_| anyhow!("Invalid timestamp in migration filename: {}", filename))?;

    // Basic validation of timestamp format
    let year = timestamp / 10000000000;
    let month = (timestamp / 100000000) % 100;
    let day = (timestamp / 1000000) % 100;

    if year < 2000 || year > 3000 {
        return Err(anyhow!("Invalid year in timestamp: {}", timestamp_str));
    }
    if month < 1 || month > 12 {
        return Err(anyhow!("Invalid month in timestamp: {}", timestamp_str));
    }
    if day < 1 || day > 31 {
        return Err(anyhow!("Invalid day in timestamp: {}", timestamp_str));
    }

    Ok((timestamp, name.to_string()))
}

/// Generate a timestamp prefix for a new migration file
/// Returns a string in format YYYYMMDDHHMMSS
pub fn generate_migration_timestamp() -> String {
    let now = Utc::now();
    now.format("%Y%m%d%H%M%S").to_string()
}

/// Generate a full migration filename with timestamp prefix
/// Example: generate_migration_filename("add_users_table") -> "20231201123456_add_users_table.sql"
pub fn generate_migration_filename(name: &str) -> String {
    format!("{}_{}.sql", generate_migration_timestamp(), name)
}

/// Trait for migration discovery to allow different implementations
pub trait MigrationDiscovery {
    fn get_migrations(&self) -> Vec<&Migration>;
    fn get_pending_migrations(&self, current_version: i64) -> Vec<&Migration>;
}

impl MigrationDiscovery for RuntimeMigrationDiscovery {
    fn get_migrations(&self) -> Vec<&Migration> {
        self.get_migrations()
    }

    fn get_pending_migrations(&self, current_version: i64) -> Vec<&Migration> {
        self.get_pending_migrations(current_version)
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use std::fs;
    use tempfile::TempDir;

    #[test]
    fn test_parse_migration_filename() {
        let cases = vec![
            (
                "20231201123456_initial_schema.sql",
                Ok((20231201123456, "initial_schema".to_string())),
            ),
            (
                "20231202140000_add_users_table.sql",
                Ok((20231202140000, "add_users_table".to_string())),
            ),
            (
                "20240315091530_complex_migration_name.sql",
                Ok((20240315091530, "complex_migration_name".to_string())),
            ),
            ("invalid.sql", Err(())),                      // No underscore
            ("abc_invalid.sql", Err(())),                  // Non-numeric timestamp
            ("20231201123456_no_extension", Err(())),      // No .sql extension
            ("123_too_short.sql", Err(())),                // Timestamp too short
            ("202312011234567_too_long.sql", Err(())),     // Timestamp too long
            ("20231300123456_invalid_month.sql", Err(())), // Invalid month
            ("20231232123456_invalid_day.sql", Err(())),   // Invalid day
            ("19991201123456_invalid_year.sql", Err(())),  // Year too old
        ];

        for (input, expected) in cases {
            let result = parse_migration_filename(input);
            match expected {
                Ok((expected_timestamp, expected_name)) => {
                    assert!(
                        result.is_ok(),
                        "Expected success for {}, got error: {:?}",
                        input,
                        result
                    );
                    let (timestamp, name) = result.unwrap();
                    assert_eq!(
                        timestamp, expected_timestamp,
                        "Timestamp mismatch for {}",
                        input
                    );
                    assert_eq!(name, expected_name, "Name mismatch for {}", input);
                }
                Err(_) => {
                    assert!(
                        result.is_err(),
                        "Expected error for {}, got success: {:?}",
                        input,
                        result
                    );
                }
            }
        }
    }

    #[test]
    fn test_runtime_migration_discovery() {
        // Create a temporary directory with migration files
        let temp_dir = TempDir::new().unwrap();
        let migrations_dir = temp_dir.path();

        // Create test migration files with timestamp format
        fs::write(
            migrations_dir.join("20231201120000_initial_schema.sql"),
            "CREATE TABLE users (id INTEGER PRIMARY KEY);",
        )
        .unwrap();

        fs::write(
            migrations_dir.join("20231201130000_add_posts.sql"),
            "CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER);",
        )
        .unwrap();

        // Test discovery
        let discovery = RuntimeMigrationDiscovery::new(migrations_dir).unwrap();
        let migrations = discovery.get_migrations();

        assert_eq!(migrations.len(), 2);

        // Migrations should be sorted by timestamp
        assert_eq!(migrations[0].version, 20231201120000);
        assert_eq!(migrations[0].name, "initial_schema");
        assert_eq!(migrations[1].version, 20231201130000);
        assert_eq!(migrations[1].name, "add_posts");
    }

    #[test]
    fn test_pending_migrations() {
        // Create a temporary directory with migration files
        let temp_dir = TempDir::new().unwrap();
        let migrations_dir = temp_dir.path();

        fs::write(
            migrations_dir.join("20231201120000_initial_schema.sql"),
            "CREATE TABLE users (id INTEGER PRIMARY KEY);",
        )
        .unwrap();

        fs::write(
            migrations_dir.join("20231201130000_add_posts.sql"),
            "CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER);",
        )
        .unwrap();

        let discovery = RuntimeMigrationDiscovery::new(migrations_dir).unwrap();

        // No pending migrations if we're at the latest timestamp
        let pending = discovery.get_pending_migrations(20231201130000);
        assert!(pending.is_empty());

        // All migrations are pending if we're at timestamp 0
        let pending = discovery.get_pending_migrations(0);
        assert_eq!(pending.len(), 2);
        assert_eq!(pending[0].version, 20231201120000);
        assert_eq!(pending[1].version, 20231201130000);

        // One migration pending if we're at the first timestamp
        let pending = discovery.get_pending_migrations(20231201120000);
        assert_eq!(pending.len(), 1);
        assert_eq!(pending[0].version, 20231201130000);
    }
}