---

using Dapper;
using Microsoft.Data.Sqlite;

public class TodoList
{
    public static void AddTaskCommand(SqliteConnection connection)
    {
        while (true) 
        {
            Console.WriteLine("Введите название задачи:");
            var taskTitle = Console.ReadLine();
            if (taskTitle == null || taskTitle.Length <= 3)
            {
                continue;
            }

            connection.Execute("INSERT INTO tasks(title) VALUES (@Title)", new
            {
                Title = taskTitle,
            });
            break;
        }
    }
    
    public static List<Task> GetAllTasks(SqliteConnection connection)
    {
        var rows = connection.Query<dynamic>(
            "SELECT tasks.id as TaskId, tasks.finished as TaskFinished, tags.id as TagId, tasks.title as TaskTitle, tags.title as TagTitle " +
            "FROM tasks INNER JOIN task_tags ON task_tags.task_id = tasks.id " +
            "INNER JOIN tags ON tags.id = task_tags.tag_id");

        var tasks = rows.GroupBy(it => it.TaskId).Select(group =>
        {
            var taskId = Convert.ToInt32(group.Key);
            var taskTitle = group.First().TaskTitle;
            var taskFinished = group.First().TaskFinished;

            var tags = group.Select(item => new Tag
            {
                Id = Convert.ToInt32(item.TagId),
                Title = item.TagTitle
            }).ToList();

            return new Task
            {
                Id = taskId,
                Title = taskTitle,
                Finished = Convert.ToBoolean(taskFinished),
                Tags = tags
            };
        });

public static List<Task> GetAllTasks(SqliteConnection connection)
{
    var rows = connection.Query<dynamic>("SELECT tasks.id as TaskId, tasks.finished as TaskFinished, tags.id as TagId, tasks.title as TaskTitle, tags.title as TagTitle FROM tasks INNER JOIN task_tags ON task_tags.task_id = tasks.id INNER JOIN tags ON tags.id = task_tags.tag_id");  
    var tasks = rows.GroupBy((it) => it.TaskId).Select(it =>
    {
        var taskId = Convert.ToInt32(it.Key);
        var taskTitle = it.First().TaskTitle;
        var taskFinished = it.First().TaskFinished;
        var tags = it.Select(it => new Tag
        {
           Id = Convert.ToInt32(it.TagId),
           Title = it.TagTitle, 
        });
        return new Task
        {
            Id = taskId,
            Title = taskTitle,
            Finished = Convert.ToBoolean(taskFinished),
            Tags = tags.ToList(),
        };
    });

    return tasks.ToList();
}
        return tasks.ToList();
    }

    public static void DeleteTask(SqliteConnection connection)
    {
        Console.WriteLine("Введите номер задачи для удаления:");
        var id = Convert.ToInt32(Console.ReadLine());
        connection.Execute("DELETE FROM tasks WHERE id = @Id", new { Id = id });
    }

    public static void SetFinishedTaskCommand(SqliteConnection connection)
    {
        Console.WriteLine("Введите номер задачи для выполнения:");
        var id = Convert.ToInt32(Console.ReadLine());
        connection.Execute("UPDATE tasks SET finished = NOT finished WHERE id = @Id", new { Id = id });
    }

    public static void RemoveTaskCommand(SqliteConnection connection)
    {
        Console.WriteLine("Введите номер задачи для удаления всех задач:");
        var id = Convert.ToInt32(Console.ReadLine());
        connection.Execute("DELETE FROM tasks WHERE id = @Id", new { Id = id });
    }

    public static void RemoveAllTasks(SqliteConnection connection)
    {
        connection.Execute("DELETE FROM tasks");
    }
}