import 'package:sqflite_common/sqflite.dart';
import '../../domain/entities/pokemon.dart';
import '../dto/pokemon_dto.dart';

const _createSql = '''
  CREATE TABLE IF NOT EXISTS pokemon (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    name_fr TEXT,
    name_en TEXT,
    type1 TEXT NOT NULL,
    type2 TEXT,
    hp INTEGER NOT NULL,
    atk INTEGER NOT NULL,
    def INTEGER NOT NULL,
    spd INTEGER NOT NULL,
    description TEXT,
    description_en TEXT,
    isCaught INTEGER NOT NULL DEFAULT 0,
    isSeen INTEGER NOT NULL DEFAULT 0
  )
''';

class PokemonLocalDataSource {
  final String languageCode;

  PokemonLocalDataSource({this.languageCode = 'fr'});

  Future<Database>? _dbFuture;

  Future<Database> _getDb() {
    return _dbFuture ??= openDatabase(
      'pokedex.db',
      version: 5,
      onUpgrade: (db, oldVersion, newVersion) async {
        // Drop and recreate on any upgrade to pick up new columns cleanly.
        await db.execute('DROP TABLE IF EXISTS pokemon');
        await db.execute(_createSql);
      },
      onCreate: (db, version) async {
        await db.execute(_createSql);
      },
    );
  }

  Future<List<Pokemon>> getAll() async {
    final db = await _getDb();
    final rows = await db.query('pokemon');
    return rows.map((r) => PokemonDto.fromDb(r, languageCode: languageCode)).toList();
  }

  Future<Pokemon?> getById(int id) async {
    final db = await _getDb();
    final rows = await db.query('pokemon', where: 'id = ?', whereArgs: [id]);
    if (rows.isEmpty) return null;
    return PokemonDto.fromDb(rows.first, languageCode: languageCode);
  }

  Future<void> saveAll(List<Pokemon> pokemons) async {
    final db = await _getDb();
    final batch = db.batch();
    for (final p in pokemons) {
      batch.insert('pokemon', PokemonDto.toDb(p),
          conflictAlgorithm: ConflictAlgorithm.replace);
    }
    await batch.commit(noResult: true);
  }

  Future<void> update(Pokemon pokemon) async {
    final db = await _getDb();
    await db.update('pokemon', PokemonDto.toDb(pokemon),
        where: 'id = ?', whereArgs: [pokemon.id]);
  }

  Future<int> caughtCount() async {
    final db = await _getDb();
    final result =
        await db.rawQuery('SELECT COUNT(*) FROM pokemon WHERE isCaught = 1');
    return result.isNotEmpty ? (result.first.values.first as int? ?? 0) : 0;
  }

  Future<int> seenCount() async {
    final db = await _getDb();
    final result =
        await db.rawQuery('SELECT COUNT(*) FROM pokemon WHERE isSeen = 1');
    return result.isNotEmpty ? (result.first.values.first as int? ?? 0) : 0;
  }
}