Skip to content
1        10        20        30        40        50        60        70        80        90        100       110       120       130       140       1501use duckdb::{Connection, params};2use std::path::Path;3use std::sync::Arc;4use tokio::sync::Mutex;56use super::{ColumnInfo, TablePreview};7use crate::models::{Pokemon, PokemonMove};89#[derive(Clone)]10pub struct DuckDb {11    conn: Arc<Mutex<Connection>>,12    db_path: String,13}1415impl DuckDb {16    pub fn new(db_path: &str) -> Result<Self, String> {17        let conn = if db_path == ":memory:" {18            Connection::open_in_memory()19                .map_err(|e| format!("Failed to open in-memory DuckDB: {e}"))?20        } else {21            let path = Path::new(db_path);22            if let Some(parent) = path23                .parent()24                .filter(|p| !p.as_os_str().is_empty() && !p.exists())25            {26                let _ = std::fs::create_dir_all(parent);27            }28            Connection::open(db_path)29                .map_err(|e| format!("Failed to open DuckDB file at {db_path}: {e}"))?30        };3132        Ok(Self {33            conn: Arc::new(Mutex::new(conn)),34            db_path: db_path.to_string(),35        })36    }3738    #[must_use]39    pub fn db_path(&self) -> &str {40        &self.db_path41    }4243    pub async fn init(&self) -> Result<(), String> {44        let conn = self.conn.lock().await;4546        conn.execute(47            "CREATE TABLE IF NOT EXISTS pokemon (48                id INTEGER PRIMARY KEY,49                name VARCHAR NOT NULL,50                display_name VARCHAR NOT NULL,51                types VARCHAR NOT NULL,52                hp INTEGER NOT NULL,53                attack INTEGER NOT NULL,54                defense INTEGER NOT NULL,55                sp_attack INTEGER NOT NULL,56                sp_defense INTEGER NOT NULL,57                speed INTEGER NOT NULL,58                height INTEGER NOT NULL,59                weight INTEGER NOT NULL,60                sprite_url VARCHAR NOT NULL,61                artwork_url VARCHAR NOT NULL,62                genus VARCHAR NOT NULL,63                description VARCHAR NOT NULL,64                moves_json VARCHAR NOT NULL,65                cached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP66            );",67            [],68        )69        .map_err(|e| format!("Failed to create pokemon table in DuckDB: {e}"))?;7071        // Also create a pokedex_meta table to track sync state72        conn.execute(73            "CREATE TABLE IF NOT EXISTS pokedex_meta (74                key VARCHAR PRIMARY KEY,75                value VARCHAR NOT NULL,76                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP77            );",78            [],79        )80        .map_err(|e| format!("Failed to create pokedex_meta table in DuckDB: {e}"))?;8182        log::info!("DuckDB schema initialized successfully");83        Ok(())84    }8586    pub async fn existing_pokemon_ids(&self) -> Result<std::collections::HashSet<i32>, String> {87        let conn = self.conn.lock().await;88        let mut stmt = conn89            .prepare("SELECT id FROM pokemon;")90            .map_err(|e| format!("Failed to query pokemon ids: {e}"))?;9192        let rows = stmt93            .query_map([], |row| row.get::<_, i32>(0))94            .map_err(|e| format!("Failed to fetch pokemon ids: {e}"))?;9596        let mut ids = std::collections::HashSet::new();97        for id in rows.flatten() {98            ids.insert(id);99        }100        Ok(ids)101    }102103    pub async fn save_pokemons(&self, pokemons: &[Pokemon]) -> Result<usize, String> {104        if pokemons.is_empty() {105            return Ok(0);106        }107108        let conn = self.conn.lock().await;109        conn.execute("BEGIN TRANSACTION;", [])110            .map_err(|e| format!("Failed to begin transaction: {e}"))?;111112        let mut stmt = conn113            .prepare(114                "INSERT INTO pokemon (115                    id, name, display_name, types, hp, attack, defense,116                    sp_attack, sp_defense, speed, height, weight,117                    sprite_url, artwork_url, genus, description, moves_json118                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)119                ON CONFLICT (id) DO UPDATE SET120                    name = excluded.name,121                    display_name = excluded.display_name,122                    types = excluded.types,123                    hp = excluded.hp,124                    attack = excluded.attack,125                    defense = excluded.defense,126                    sp_attack = excluded.sp_attack,127                    sp_defense = excluded.sp_defense,128                    speed = excluded.speed,129                    height = excluded.height,130                    weight = excluded.weight,131                    sprite_url = excluded.sprite_url,132                    artwork_url = excluded.artwork_url,133                    genus = excluded.genus,134                    description = excluded.description,135                    moves_json = excluded.moves_json,136                    cached_at = now();",137            )138            .map_err(|e| format!("Failed to prepare insert statement: {e}"))?;139140        let mut inserted = 0;141        for p in pokemons {142            let types_str = serde_json::to_string(&p.types)143                .map_err(|e| format!("Failed to serialize types: {e}"))?;144            let moves_str = serde_json::to_string(&p.moves)145                .map_err(|e| format!("Failed to serialize moves: {e}"))?;146147            stmt.execute(params![148                p.id,149                p.name,150                p.display_name,151                types_str,152                p.hp,153                p.attack,154                p.defense,155                p.sp_attack,156                p.sp_defense,157                p.speed,158                p.height,159                p.weight,160                p.sprite_url,161                p.artwork_url,162                p.genus,163                p.description,164                moves_str,165            ])166            .map_err(|e| format!("Failed to insert pokemon {}: {e}", p.id))?;167168            inserted += 1;169        }170171        conn.execute("COMMIT;", [])172            .map_err(|e| format!("Failed to commit transaction: {e}"))?;173174        Ok(inserted)175    }176177    pub async fn seed_if_empty(&self) -> Result<usize, String> {178        let count = self.table_row_count("pokemon").await?;179        if count >= 151 {180            log::info!("DuckDB pokemon table already has {count} entries; skipping seed");181            return Ok(usize::try_from(count).unwrap_or(0));182        }183184        let existing_ids = self.existing_pokemon_ids().await?;185        let missing_ids: Vec<i32> = (1..=151).filter(|id| !existing_ids.contains(id)).collect();186187        if missing_ids.is_empty() {188            log::info!("All 151 Pokémon already present in DuckDB; skipping seed");189            return Ok(usize::try_from(count).unwrap_or(0));190        }191192        log::info!(193            "Seeding DuckDB with {} missing Gen 1 Pokémon from PokéAPI...",194            missing_ids.len()195        );196        let fetched = crate::pokeapi::fetch_gen1_pokemon_concurrent(missing_ids, 8).await?;197        let inserted = self.save_pokemons(&fetched).await?;198        log::info!("Successfully cached {inserted} Pokémon from PokéAPI into DuckDB");199200        let total = self.table_row_count("pokemon").await?;201        Ok(usize::try_from(total).unwrap_or(inserted))202    }203204    #[allow(dead_code, clippy::too_many_lines)]205    pub async fn seed_test_data(&self) -> Result<usize, String> {206        let sample = vec![207            Pokemon {208                id: 1,209                name: "bulbasaur".to_string(),210                display_name: "Bulbasaur".to_string(),211                types: vec!["grass".to_string(), "poison".to_string()],212                hp: 45,213                attack: 49,214                defense: 49,215                sp_attack: 65,216                sp_defense: 65,217                speed: 45,218                height: 7,219                weight: 69,220                sprite_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/1.png".to_string(),221                artwork_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/other/official-artwork/1.png".to_string(),222                genus: "Seed Pokémon".to_string(),223                description: "There is a plant seed on its back right from the day this POKéMON is born. The seed slowly grows larger.".to_string(),224                moves: vec![225                    PokemonMove {226                        name: "tackle".to_string(),227                        level: 1,228                        method: "level-up".to_string(),229                    },230                    PokemonMove {231                        name: "growl".to_string(),232                        level: 4,233                        method: "level-up".to_string(),234                    },235                ],236            },237            Pokemon {238                id: 2,239                name: "ivysaur".to_string(),240                display_name: "Ivysaur".to_string(),241                types: vec!["grass".to_string(), "poison".to_string()],242                hp: 60,243                attack: 62,244                defense: 63,245                sp_attack: 80,246                sp_defense: 80,247                speed: 60,248                height: 10,249                weight: 130,250                sprite_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/2.png".to_string(),251                artwork_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/other/official-artwork/2.png".to_string(),252                genus: "Seed Pokémon".to_string(),253                description: "When the bulb on its back grows large, it appears to lose the ability to stand on its hind legs.".to_string(),254                moves: vec![255                    PokemonMove {256                        name: "tackle".to_string(),257                        level: 1,258                        method: "level-up".to_string(),259                    },260                ],261            },262            Pokemon {263                id: 3,264                name: "venusaur".to_string(),265                display_name: "Venusaur".to_string(),266                types: vec!["grass".to_string(), "poison".to_string()],267                hp: 80,268                attack: 82,269                defense: 83,270                sp_attack: 100,271                sp_defense: 100,272                speed: 80,273                height: 20,274                weight: 1000,275                sprite_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/3.png".to_string(),276                artwork_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/other/official-artwork/3.png".to_string(),277                genus: "Seed Pokémon".to_string(),278                description: "The plant blooms when it is absorbing solar energy. It stays on the move to seek sunlight.".to_string(),279                moves: vec![280                    PokemonMove {281                        name: "tackle".to_string(),282                        level: 1,283                        method: "level-up".to_string(),284                    },285                ],286            },287            Pokemon {288                id: 25,289                name: "pikachu".to_string(),290                display_name: "Pikachu".to_string(),291                types: vec!["electric".to_string()],292                hp: 35,293                attack: 55,294                defense: 40,295                sp_attack: 50,296                sp_defense: 50,297                speed: 90,298                height: 4,299                weight: 60,300                sprite_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/25.png".to_string(),301                artwork_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/other/official-artwork/25.png".to_string(),302                genus: "Mouse Pokémon".to_string(),303                description: "It has small electric sacs on both its cheeks. If threatened, it looses electric charges from the sacs.".to_string(),304                moves: vec![305                    PokemonMove {306                        name: "thunder-shock".to_string(),307                        level: 1,308                        method: "level-up".to_string(),309                    },310                ],311            },312            Pokemon {313                id: 151,314                name: "mew".to_string(),315                display_name: "Mew".to_string(),316                types: vec!["psychic".to_string()],317                hp: 100,318                attack: 100,319                defense: 100,320                sp_attack: 100,321                sp_defense: 100,322                speed: 100,323                height: 4,324                weight: 40,325                sprite_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/151.png".to_string(),326                artwork_url: "https://raw.githubusercontent.com/PokeAPI/sprites/master/sprites/pokemon/other/official-artwork/151.png".to_string(),327                genus: "New Species Pokémon".to_string(),328                description: "A POKéMON of South America that was thought to have been extinct. It is very intelligent and learns any move.".to_string(),329                moves: vec![330                    PokemonMove {331                        name: "pound".to_string(),332                        level: 1,333                        method: "level-up".to_string(),334                    },335                ],336            },337        ];338339        self.save_pokemons(&sample).await340    }341342    #[allow(dead_code)]343    pub async fn save_pokemon(&self, p: &Pokemon) -> Result<(), String> {344        let conn = self.conn.lock().await;345        let types_str = serde_json::to_string(&p.types)346            .map_err(|e| format!("Failed to serialize types: {e}"))?;347        let moves_str = serde_json::to_string(&p.moves)348            .map_err(|e| format!("Failed to serialize moves: {e}"))?;349350        conn.execute(351            "INSERT INTO pokemon (352                id, name, display_name, types, hp, attack, defense,353                sp_attack, sp_defense, speed, height, weight,354                sprite_url, artwork_url, genus, description, moves_json355            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)356            ON CONFLICT (id) DO UPDATE SET357                name = excluded.name,358                display_name = excluded.display_name,359                types = excluded.types,360                hp = excluded.hp,361                attack = excluded.attack,362                defense = excluded.defense,363                sp_attack = excluded.sp_attack,364                sp_defense = excluded.sp_defense,365                speed = excluded.speed,366                height = excluded.height,367                weight = excluded.weight,368                sprite_url = excluded.sprite_url,369                artwork_url = excluded.artwork_url,370                genus = excluded.genus,371                description = excluded.description,372                moves_json = excluded.moves_json,373                cached_at = now();",374            params![375                p.id,376                p.name,377                p.display_name,378                types_str,379                p.hp,380                p.attack,381                p.defense,382                p.sp_attack,383                p.sp_defense,384                p.speed,385                p.height,386                p.weight,387                p.sprite_url,388                p.artwork_url,389                p.genus,390                p.description,391                moves_str,392            ],393        )394        .map_err(|e| format!("Failed to upsert pokemon {}: {e}", p.id))?;395396        Ok(())397    }398399    pub async fn get_pokemon_by_id(&self, id: i32) -> Result<Option<Pokemon>, String> {400        let conn = self.conn.lock().await;401        let mut stmt = conn402            .prepare(403                "SELECT id, name, display_name, types, hp, attack, defense,404                        sp_attack, sp_defense, speed, height, weight,405                        sprite_url, artwork_url, genus, description, moves_json406                 FROM pokemon WHERE id = ?;",407            )408            .map_err(|e| format!("Failed to prepare get query: {e}"))?;409410        let mut rows = stmt411            .query(params![id])412            .map_err(|e| format!("Failed to query pokemon by id: {e}"))?;413414        if let Some(row) = rows415            .next()416            .map_err(|e| format!("Failed to read pokemon row: {e}"))?417        {418            let p = parse_pokemon_row(row)?;419            Ok(Some(p))420        } else {421            Ok(None)422        }423    }424425    pub async fn search_pokemon(426        &self,427        query: &str,428        limit: usize,429        offset: usize,430    ) -> Result<Vec<Pokemon>, String> {431        let conn = self.conn.lock().await;432        let q_clean = query.trim().to_lowercase();433434        #[allow(clippy::cast_possible_wrap)]435        let limit_i64 = limit as i64;436        #[allow(clippy::cast_possible_wrap)]437        let offset_i64 = offset as i64;438439        if q_clean.is_empty() {440            let mut stmt = conn441                .prepare(442                    "SELECT id, name, display_name, types, hp, attack, defense,443                            sp_attack, sp_defense, speed, height, weight,444                            sprite_url, artwork_url, genus, description, moves_json445                     FROM pokemon ORDER BY id ASC LIMIT ? OFFSET ?;",446                )447                .map_err(|e| format!("Failed to prepare search: {e}"))?;448449            let mut rows = stmt450                .query(params![limit_i64, offset_i64])451                .map_err(|e| format!("Failed to query pokemon: {e}"))?;452453            let mut results = Vec::new();454            while let Some(row) = rows455                .next()456                .map_err(|e| format!("Failed to read row: {e}"))?457            {458                results.push(parse_pokemon_row(row)?);459            }460            Ok(results)461        } else {462            let num_val = q_clean.trim_start_matches('#').parse::<i32>().unwrap_or(-1);463            let pattern = format!("%{q_clean}%");464465            let mut stmt = conn466                .prepare(467                    "SELECT id, name, display_name, types, hp, attack, defense,468                            sp_attack, sp_defense, speed, height, weight,469                            sprite_url, artwork_url, genus, description, moves_json470                     FROM pokemon471                     WHERE id = ? OR lower(name) LIKE ? OR lower(display_name) LIKE ? OR lower(types) LIKE ?472                     ORDER BY id ASC LIMIT ? OFFSET ?;",473                )474                .map_err(|e| format!("Failed to prepare search: {e}"))?;475476            let mut rows = stmt477                .query(params![478                    num_val, pattern, pattern, pattern, limit_i64, offset_i64479                ])480                .map_err(|e| format!("Failed to query search results: {e}"))?;481482            let mut results = Vec::new();483            while let Some(row) = rows484                .next()485                .map_err(|e| format!("Failed to read row: {e}"))?486            {487                results.push(parse_pokemon_row(row)?);488            }489            Ok(results)490        }491    }492493    pub async fn total_pokemon_count(&self, query: &str) -> Result<usize, String> {494        let conn = self.conn.lock().await;495        let q_clean = query.trim().to_lowercase();496497        if q_clean.is_empty() {498            let count: i64 = conn499                .query_row("SELECT count(*) FROM pokemon;", [], |r| r.get(0))500                .map_err(|e| format!("Failed to get count: {e}"))?;501            Ok(usize::try_from(count).unwrap_or(0))502        } else {503            let num_val = q_clean.trim_start_matches('#').parse::<i32>().unwrap_or(-1);504            let pattern = format!("%{q_clean}%");505506            let count: i64 = conn507                .query_row(508                    "SELECT count(*) FROM pokemon509                     WHERE id = ? OR lower(name) LIKE ? OR lower(display_name) LIKE ? OR lower(types) LIKE ?;",510                    params![num_val, pattern, pattern, pattern],511                    |r| r.get(0),512                )513                .map_err(|e| format!("Failed to get search count: {e}"))?;514            Ok(usize::try_from(count).unwrap_or(0))515        }516    }517518    // --- Diagnostic and Debug Inspection methods ---519520    pub async fn list_tables(&self) -> Result<Vec<String>, String> {521        let conn = self.conn.lock().await;522        let mut stmt = conn523            .prepare(524                "SELECT table_name FROM information_schema.tables525                 WHERE table_schema = 'main'526                 ORDER BY table_name;",527            )528            .map_err(|e| format!("Failed to list tables: {e}"))?;529530        let mut rows = stmt531            .query([])532            .map_err(|e| format!("Failed to query tables: {e}"))?;533534        let mut tables = Vec::new();535        while let Some(row) = rows536            .next()537            .map_err(|e| format!("Failed to read table name: {e}"))?538        {539            let name: String = row.get(0).unwrap_or_default();540            tables.push(name);541        }542        Ok(tables)543    }544545    pub async fn describe_table(&self, table: &str) -> Result<Vec<ColumnInfo>, String> {546        let conn = self.conn.lock().await;547        let mut stmt = conn548            .prepare(549                "SELECT column_name, data_type, is_nullable550                 FROM information_schema.columns551                 WHERE table_schema = 'main' AND table_name = ?552                 ORDER BY ordinal_position;",553            )554            .map_err(|e| format!("Failed to describe table {table}: {e}"))?;555556        let mut rows = stmt557            .query(params![table])558            .map_err(|e| format!("Failed to query columns for {table}: {e}"))?;559560        let mut columns = Vec::new();561        while let Some(row) = rows562            .next()563            .map_err(|e| format!("Failed to read column info: {e}"))?564        {565            let name: String = row.get(0).unwrap_or_default();566            let data_type: String = row.get(1).unwrap_or_default();567            let nullable_str: String = row.get(2).unwrap_or_default();568            let is_nullable = nullable_str.to_uppercase() == "YES";569            let is_pk = name == "id";570571            columns.push(ColumnInfo {572                name,573                data_type,574                is_nullable,575                is_pk,576            });577        }578        Ok(columns)579    }580581    pub async fn table_row_count(&self, table: &str) -> Result<i64, String> {582        let conn = self.conn.lock().await;583        // Verify table name is alphanumeric/underscore to prevent injection584        if !table.chars().all(|c| c.is_alphanumeric() || c == '_') {585            return Err("Invalid table name".to_string());586        }587588        let query = format!("SELECT count(*) FROM {table};");589        let count: i64 = conn590            .query_row(&query, [], |r| r.get(0))591            .map_err(|e| format!("Failed to get row count for {table}: {e}"))?;592        Ok(count)593    }594595    pub async fn preview_table(&self, table: &str, limit: usize) -> Result<TablePreview, String> {596        if !table.chars().all(|c| c.is_alphanumeric() || c == '_') {597            return Err("Invalid table name".to_string());598        }599        let total_rows = self.table_row_count(table).await?;600601        let conn = self.conn.lock().await;602        let query = format!("SELECT * FROM {table} LIMIT {limit};");603        let mut stmt = conn604            .prepare(&query)605            .map_err(|e| format!("Failed to prepare preview for {table}: {e}"))?;606607        let mut rows = stmt608            .query([])609            .map_err(|e| format!("Failed to execute preview for {table}: {e}"))?;610611        let col_count = rows.as_ref().map_or(0, duckdb::Statement::column_count);612        let mut col_names = Vec::new();613        if let Some(r) = rows.as_ref() {614            for i in 0..col_count {615                col_names.push(616                    r.column_name(i)617                        .map_or_else(|_| "?".to_string(), String::from),618                );619            }620        }621622        let mut data_rows = Vec::new();623        while let Some(row) = rows624            .next()625            .map_err(|e| format!("Failed to read preview row: {e}"))?626        {627            let mut row_vals = Vec::new();628            for i in 0..col_count {629                let val: duckdb::types::Value = row.get(i).unwrap_or(duckdb::types::Value::Null);630                row_vals.push(format_duck_val(&val));631            }632            data_rows.push(row_vals);633        }634635        Ok(TablePreview {636            table_name: table.to_string(),637            total_rows,638            columns: col_names,639            rows: data_rows,640        })641    }642}643644fn format_duck_val(val: &duckdb::types::Value) -> String {645    match val {646        duckdb::types::Value::Null => "NULL".to_string(),647        duckdb::types::Value::Boolean(b) => b.to_string(),648        duckdb::types::Value::TinyInt(n) => n.to_string(),649        duckdb::types::Value::SmallInt(n) => n.to_string(),650        duckdb::types::Value::Int(n) => n.to_string(),651        duckdb::types::Value::BigInt(n) => n.to_string(),652        duckdb::types::Value::HugeInt(n) => n.to_string(),653        duckdb::types::Value::UTinyInt(n) => n.to_string(),654        duckdb::types::Value::USmallInt(n) => n.to_string(),655        duckdb::types::Value::UInt(n) => n.to_string(),656        duckdb::types::Value::UBigInt(n) => n.to_string(),657        duckdb::types::Value::Float(f) => format!("{f:.2}"),658        duckdb::types::Value::Double(f) => format!("{f:.2}"),659        duckdb::types::Value::Text(s) => s.clone(),660        other => format!("{other:?}"),661    }662}663664fn parse_pokemon_row(row: &duckdb::Row) -> Result<Pokemon, String> {665    let id: i32 = row.get(0).map_err(|e| format!("col 0 id: {e}"))?;666    let name: String = row.get(1).map_err(|e| format!("col 1 name: {e}"))?;667    let display_name: String = row.get(2).map_err(|e| format!("col 2 display_name: {e}"))?;668    let types_json: String = row.get(3).map_err(|e| format!("col 3 types: {e}"))?;669    let hp: i32 = row.get(4).map_err(|e| format!("col 4 hp: {e}"))?;670    let attack: i32 = row.get(5).map_err(|e| format!("col 5 attack: {e}"))?;671    let defense: i32 = row.get(6).map_err(|e| format!("col 6 defense: {e}"))?;672    let sp_attack: i32 = row.get(7).map_err(|e| format!("col 7 sp_attack: {e}"))?;673    let sp_defense: i32 = row.get(8).map_err(|e| format!("col 8 sp_defense: {e}"))?;674    let speed: i32 = row.get(9).map_err(|e| format!("col 9 speed: {e}"))?;675    let height: i32 = row.get(10).map_err(|e| format!("col 10 height: {e}"))?;676    let weight: i32 = row.get(11).map_err(|e| format!("col 11 weight: {e}"))?;677    let sprite_url: String = row.get(12).map_err(|e| format!("col 12 sprite_url: {e}"))?;678    let artwork_url: String = row679        .get(13)680        .map_err(|e| format!("col 13 artwork_url: {e}"))?;681    let genus: String = row.get(14).map_err(|e| format!("col 14 genus: {e}"))?;682    let description: String = row683        .get(15)684        .map_err(|e| format!("col 15 description: {e}"))?;685    let moves_json: String = row.get(16).map_err(|e| format!("col 16 moves_json: {e}"))?;686687    let types: Vec<String> = serde_json::from_str(&types_json).unwrap_or_default();688    let moves: Vec<PokemonMove> = serde_json::from_str(&moves_json).unwrap_or_default();689690    Ok(Pokemon {691        id,692        name,693        display_name,694        types,695        hp,696        attack,697        defense,698        sp_attack,699        sp_defense,700        speed,701        height,702        weight,703        sprite_url,704        artwork_url,705        genus,706        description,707        moves,708    })709}710711#[cfg(test)]712mod tests {713    use super::*;714715    #[tokio::test]716    async fn test_duckdb_lifecycle() {717        let db = DuckDb::new(":memory:").expect("new in-memory duckdb");718        db.init().await.expect("init duckdb");719720        let count = db.seed_test_data().await.expect("seed duckdb");721        assert_eq!(count, 5, "Should seed 5 test Pokemon");722723        let bulbasaur = db724            .get_pokemon_by_id(1)725            .await726            .unwrap()727            .expect("bulbasaur exists");728        assert_eq!(bulbasaur.display_name, "Bulbasaur");729        assert_eq!(bulbasaur.types, vec!["grass", "poison"]);730        assert!(bulbasaur.hp > 0);731        assert!(!bulbasaur.moves.is_empty());732733        let mew = db734            .get_pokemon_by_id(151)735            .await736            .unwrap()737            .expect("mew exists");738        assert_eq!(mew.display_name, "Mew");739740        // Search test741        let search_grass = db.search_pokemon("grass", 50, 0).await.unwrap();742        assert!(!search_grass.is_empty());743        assert!(search_grass.iter().any(|p| p.name == "bulbasaur"));744745        let search_id = db.search_pokemon("25", 10, 0).await.unwrap();746        assert!(search_id.iter().any(|p| p.name == "pikachu"));747748        // Table list & describe749        let tables = db.list_tables().await.unwrap();750        assert!(tables.contains(&"pokemon".to_string()));751752        let cols = db.describe_table("pokemon").await.unwrap();753        assert!(cols.iter().any(|c| c.name == "display_name"));754755        let preview = db.preview_table("pokemon", 5).await.unwrap();756        assert_eq!(preview.rows.len(), 5);757    }758}