test_cursor/lib/database/todo_database.dart

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/todo.dart';
import '../models/tag.dart';

class TodoDatabase {
  static final TodoDatabase instance = TodoDatabase._init();
  static Database? _database;

  TodoDatabase._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('todo.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    return await openDatabase(
      path,
      version: 3,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE todos(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            isCompleted INTEGER NOT NULL DEFAULT 0,
            createdAt TEXT NOT NULL,
            completedAt TEXT
          )
        ''');

        await db.execute('''
          CREATE TABLE tags(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            color INTEGER NOT NULL
          )
        ''');

        await db.execute('''
          CREATE TABLE todo_tags(
            todo_id INTEGER,
            tag_id INTEGER,
            PRIMARY KEY (todo_id, tag_id),
            FOREIGN KEY (todo_id) REFERENCES todos (id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
          )
        ''');
      },
      onUpgrade: _onUpgrade,
    );
  }

  Future _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 3) {
      // Create todo_tags junction table
      await db.execute('''
        CREATE TABLE todo_tags(
          todo_id INTEGER,
          tag_id INTEGER,
          PRIMARY KEY (todo_id, tag_id),
          FOREIGN KEY (todo_id) REFERENCES todos (id) ON DELETE CASCADE,
          FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
        )
      ''');
    }
  }

  Future<Todo> create(Todo todo) async {
    final db = await instance.database;
    final id = await db.insert('todos', todo.toMap());
    return todo.copyWith(id: id);
  }

  Future<List<Todo>> getAllTodos() async {
    final db = await instance.database;
    final result = await db.query('todos');
    final todos = result.map((json) => Todo.fromMap(json)).toList();

    // Load tags for each todo
    for (var todo in todos) {
      final tags = await getTagsForTodo(todo.id!);
      todo.tags = tags;
    }
    return todos;
  }

  Future<int> update(Todo todo) async {
    final db = await instance.database;
    return db.update(
      'todos',
      todo.toMap(),
      where: 'id = ?',
      whereArgs: [todo.id],
    );
  }

  Future<int> delete(int id) async {
    final db = await instance.database;
    return await db.delete(
      'todos',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<Tag> createTag(Tag tag) async {
    final db = await instance.database;
    final id = await db.insert('tags', tag.toMap());
    return Tag(id: id, name: tag.name, color: tag.color);
  }

  Future<List<Tag>> getAllTags() async {
    final db = await instance.database;
    try {
      final result = await db.query('tags');
      return result.map((json) => Tag.fromMap(json)).toList();
    } catch (e) {
      return [];
    }
  }

  Future<void> addTagToTodo(int todoId, int tagId) async {
    final db = await instance.database;
    await db.insert('todo_tags', {
      'todo_id': todoId,
      'tag_id': tagId,
    });
  }

  Future<void> removeTagFromTodo(int todoId, int tagId) async {
    final db = await instance.database;
    await db.delete(
      'todo_tags',
      where: 'todo_id = ? AND tag_id = ?',
      whereArgs: [todoId, tagId],
    );
  }

  Future<List<Tag>> getTagsForTodo(int todoId) async {
    final db = await instance.database;
    final result = await db.rawQuery('''
      SELECT tags.* FROM tags
      INNER JOIN todo_tags ON tags.id = todo_tags.tag_id
      WHERE todo_tags.todo_id = ?
    ''', [todoId]);
    return result.map((json) => Tag.fromMap(json)).toList();
  }

  Future<List<Todo>> getTodosByTag(int tagId) async {
    final db = await instance.database;
    final result = await db.rawQuery('''
      SELECT todos.* FROM todos
      INNER JOIN todo_tags ON todos.id = todo_tags.todo_id
      WHERE todo_tags.tag_id = ?
    ''', [tagId]);

    final todos = result.map((json) => Todo.fromMap(json)).toList();
    for (var todo in todos) {
      final tags = await getTagsForTodo(todo.id!);
      todo.tags = tags;
    }
    return todos;
  }
}