using Sonex.Data.Database;

namespace Sonex.Data.Records;

public sealed class DepartmentRecord
{
    public long Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string Symbol { get; set; } = string.Empty;

    public string Description { get; set; } = string.Empty;

    public static Task<DB.SingleResult<DepartmentRecord>> Get(long id)
    {
        return DB.QuerySingleAsync<DepartmentRecord>(
            """
            SELECT id,
                   name,
                   symbol,
                   description
            FROM sonex.departments
            WHERE id = @id
            LIMIT 1;
            """,
            new { id });
    }

    public static Task<DB.Result<DepartmentRecord>> GetAll()
    {
        return DB.QueryListAsync<DepartmentRecord>(
            """
            SELECT id,
                   name,
                   symbol,
                   description
            FROM sonex.departments
            ORDER BY name ASC, symbol ASC;
            """);
    }

    public static Task<DB.SingleResult<long>> Create(
        string name,
        string symbol,
        string description)
    {
        return DB.QuerySingleAsync<long>(
            """
            INSERT INTO sonex.departments (name, symbol, description)
            VALUES (@name, @symbol, @description)
            RETURNING id;
            """,
            new
            {
                name,
                symbol,
                description
            });
    }

    public async Task<DB.SingleResult<bool>> Save()
    {
        var result = await DB.QuerySingleAsync<bool>(
            """
            UPDATE sonex.departments
            SET name = @Name,
                symbol = @Symbol,
                description = @Description,
                updated_at = NOW()
            WHERE id = @Id
            RETURNING TRUE;
            """,
            new
            {
                Id,
                Name,
                Symbol,
                Description
            }).ConfigureAwait(false);

        return result;
    }

    public Task<DB.SingleResult<bool>> Delete()
    {
        return DB.QuerySingleAsync<bool>(
            """
            DELETE FROM sonex.departments
            WHERE id = @Id
            RETURNING TRUE;
            """,
            new
            {
                Id
            });
    }
}