flutter_wordle/lib/db/wordle_database.dart

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class WordleDatabase {
  static final WordleDatabase _instance = WordleDatabase._internal();
  Database? _db;

  factory WordleDatabase() {
    return _instance;
  }

  WordleDatabase._internal();

  Future<Database> get database async {
    if (_db != null) {
      return _db!;
    }

    _db = await _initializeDatabase();
    return _db!;
  }

  Future<Database> _initializeDatabase() async {
    // Get the default database path
    String path = join(await getDatabasesPath(), 'wordle_game.db');

    return await openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        // Create tables for each game mode
        await db.execute('''
          CREATE TABLE board (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            correctWord TEXT NOT NULL,
            maxGuesses INTEGER NOT NULL,
            guesses TEXT NOT NULL,
            hasWon INTEGER NOT NULL
          )
        ''');

        await db.execute('''
          CREATE TABLE classic_game_history (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          date TEXT NOT NULL,
          board_id INTEGER NOT NULL,
          FOREIGN KEY (board_id) REFERENCES board (id) ON DELETE CASCADE
          )
          ''');

        await db.execute('''
          CREATE TABLE duel_game_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT NOT NULL,
            winner INTEGER NOT NULL CHECK (winner IN (0, 1)),
            numberOfTurns INTEGER NOT NULL,
            player1score INTEGER NOT NULL,
            player2score INTEGER NOT NULL
          )
        ''');

        await db.execute('''
          CREATE TABLE duel_game_boards (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            duel_game_id INTEGER NOT NULL,
            player INTEGER NOT NULL CHECK (player IN (1, 2)),
            correctWord TEXT NOT NULL,
            maxGuesses INTEGER NOT NULL,
            guesses TEXT NOT NULL,
            hasWon INTEGER NOT NULL,
            FOREIGN KEY (duel_game_id) REFERENCES duel_game_history (id)
          )
        ''');

        await db.execute('''
          CREATE TABLE survival_game_history (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          date TEXT NOT NULL,
          maxLevelReached INTEGER NOT NULL
          )
          ''');

        await db.execute('''
          CREATE TABLE survival_game_boards (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          survival_game_id INTEGER NOT NULL,
          board_id INTEGER NOT NULL,
          FOREIGN KEY (survival_game_id) REFERENCES survival_game_history (id) ON DELETE CASCADE,
          FOREIGN KEY (board_id) REFERENCES board (id) ON DELETE CASCADE
          )
          ''');
      },
    );
  }
}