using Dapper;
using Sonex.Data.Database;
using System.Text;

namespace Sonex.Data.Records;

public sealed class ActivityBarRecord
{
    public long LineId { get; set; }

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

    public DateTime StartTime { get; set; }

    public DateTime EndTime { get; set; }

    public double DurationMinutes { get; set; }

    public int OperationId { get; set; }

    public int LineCount { get; set; }

    public double Quantity { get; set; }

    public double? QuantityPerHour { get; set; }

    public double? EffectivenessPercent { get; set; }

    public bool IsManualEntry { get; set; }

    public static Task<DB.SingleResult<ActivityBarRecord>> Get(long lineId, CancellationToken ct = default)
    {
        return DB.QuerySingleAsync<ActivityBarRecord>(
            """
            SELECT line_id,
                   locus_account_name,
                   start_time,
                   end_time,
                   duration_minutes,
                   operation_id,
                   line_count,
                   quantity,
                   quantity_per_hour,
                   effectiveness_percent,
                   is_manual_entry
            FROM sonex.activity_bars
            WHERE line_id = @lineId
            LIMIT 1;
            """,
            new { lineId },
            ct: ct);
    }

    public static Task<DB.Result<ActivityBarRecord>> GetAll(CancellationToken ct = default)
    {
        return GetAll(10000, null, null, ct);
    }

    public static Task<DB.Result<ActivityBarRecord>> GetAll(
        int limit,
        string? whereSql,
        IReadOnlyDictionary<string, object?>? whereParameters,
        CancellationToken ct = default)
    {
        var sql = new StringBuilder();
        sql.AppendLine(
            """
            SELECT line_id,
                   locus_account_name,
                   start_time,
                   end_time,
                   duration_minutes,
                   operation_id,
                   line_count,
                   quantity,
                   quantity_per_hour,
                   effectiveness_percent,
                   is_manual_entry
            FROM sonex.activity_bars
            """);

        if (!string.IsNullOrWhiteSpace(whereSql))
        {
            sql.AppendLine();
            sql.Append("WHERE ");
            sql.AppendLine(whereSql.Trim());
        }

        sql.AppendLine(
            """
            ORDER BY start_time ASC, line_id ASC
            LIMIT @limit;
            """);

        var parameters = new DynamicParameters();
        parameters.Add("limit", limit);

        if (whereParameters is not null)
        {
            foreach (var item in whereParameters)
            {
                parameters.Add(item.Key, item.Value);
            }
        }

        return DB.QueryListAsync<ActivityBarRecord>(
            sql.ToString(),
            parameters,
            ct: ct);
    }

    public static Task<DB.Result<ActivityBarRecord>> GetRange(
        DateTime rangeStart,
        DateTime rangeEnd,
        int limit = 10000,
        CancellationToken ct = default)
    {
        var whereParameters = new Dictionary<string, object?>
        {
            { "rangeStart", rangeStart },
            { "rangeEnd", rangeEnd }
        };

        return GetAll(
            limit,
            "start_time < @rangeEnd AND end_time > @rangeStart",
            whereParameters,
            ct);
    }
}