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

namespace Sonex.Data.Records;

public sealed class LocationRecord
{
    public int locid { get; set; }
    public string state { get; set; } = string.Empty;
    public string name { get; set; } = string.Empty;
    public string? name2 { get; set; }
    public string? pin { get; set; }
    public int? maxcontain { get; set; }
    public int? maxitems { get; set; }
    public short? aisle { get; set; }
    public short? aisleside { get; set; }
    public short? aisle2 { get; set; }
    public short? aisleside2 { get; set; }
    public string? type { get; set; }
    public string? subtype { get; set; }
    public string? usage { get; set; }
    public string? warehouse { get; set; }
    public string? zone { get; set; }
    public string? pickzone { get; set; }
    public string? truckzone { get; set; }
    public string? fillzone { get; set; }
    public string? section { get; set; }
    public string? dock { get; set; }
    public string storecond { get; set; } = string.Empty;
    public int grafx { get; set; }
    public int grafy { get; set; }
    public int grafz { get; set; }
    public int distx { get; set; }
    public int disty { get; set; }
    public int distz { get; set; }
    public int grafdx { get; set; }
    public int grafdy { get; set; }
    public int grafdz { get; set; }
    public short? beamgrp { get; set; }
    public int? idleftbeam { get; set; }
    public int? idrightbeam { get; set; }
    public int? beamgrpid { get; set; }
    public int? accesscost { get; set; }
    public int pickseq { get; set; }
    public int fillseq1 { get; set; }
    public int fillseq2 { get; set; }
    public int pickseqn { get; set; }
    public int fillseq1n { get; set; }
    public int fillseq2n { get; set; }
    public string blocked { get; set; } = string.Empty;
    public string blockedin { get; set; } = string.Empty;
    public string blockedout { get; set; } = string.Empty;
    public short? weightclass { get; set; }
    public short? heightclass { get; set; }
    public decimal? volume { get; set; }
    public decimal? width { get; set; }
    public decimal? depth { get; set; }
    public decimal? height { get; set; }
    public int? nrcontainers { get; set; }
    public int? nrcontcoming { get; set; }
    public int? nrcontcomingimm { get; set; }
    public int? nrcontcomingfinal { get; set; }
    public string isfull { get; set; } = string.Empty;
    public string notfull { get; set; } = string.Empty;
    public string scanmth { get; set; } = string.Empty;
    public int wayx1 { get; set; }
    public int wayy1 { get; set; }
    public int wayz1 { get; set; }
    public int wayx2 { get; set; }
    public int wayy2 { get; set; }
    public int wayz2 { get; set; }
    public string? shipment { get; set; }
    public string? lockstring { get; set; }
    public short? maxpalseqonloc { get; set; }
    public int? nextloc { get; set; }
    public int? prevloc { get; set; }
    public string deleteonempty { get; set; } = string.Empty;
    public string? costzone { get; set; }
    public string? subname { get; set; }
    public string? subname2 { get; set; }
    public short? block { get; set; }
    public string? inventzone { get; set; }
    public decimal? availablewidth { get; set; }
    public decimal? availablewidthoverlap { get; set; }
    public decimal? availableweight { get; set; }
    public decimal? availableweightoverlap { get; set; }
    public decimal? maxweight { get; set; }
    public string? forceclaimsetbreak { get; set; }
    public string site { get; set; } = string.Empty;
    public string? sitepermission { get; set; }
    public string? sitedescr { get; set; }

    public static Task<DB.SingleResult<LocationRecord>> Get(
        int locid,
        CancellationToken ct = default)
    {
        return DB.QuerySingleAsync<LocationRecord>(
            """
            SELECT
                *
            FROM wms.locdvw
            WHERE locid = @locid
            LIMIT 1;
            """,
            new { locid },
            ct: ct);
    }

    public static Task<DB.Result<LocationRecord>> GetAll(
        int limit,
        string? whereSql,
        IReadOnlyDictionary<string, object?>? whereParameters,
        CancellationToken ct = default)
    {
        var sql = new StringBuilder();
        sql.AppendLine(
            """
            SELECT
                *
            FROM wms.locdvw
            """);

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

        sql.AppendLine(
            """
            ORDER BY locid ASC
            LIMIT @limit;
            """);

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

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

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