using System;

namespace WK5.Core.Basis.Filter
{
    public class ZugangFilter : PageFilter<ZugangFilterOption>
    {
        public ZugangFilter()
        {
            Option = ZugangFilterOption.OhneRechnung;
        }

        public bool IsFilterActive
        {
            get
            {
                if (!String.IsNullOrWhiteSpace(Suchbegriff))
                {
                    return true;
                }

                if (Option is not ZugangFilterOption.OhneRechnung)
                {
                    return true;
                }

                return false;
            }
        }

        public bool ZeitraumFiltern { get; set; } = false;
        public DateTime Von { get; set; } = DateTime.Now.AddDays(-14);
        public DateTime Bis { get; set; } = DateTime.Now;
        public int LieferantenId { get; set; }

        public override string ToCountQuery(FbController2 fbController)
        {
            sqlBuilder.Append($"SELECT COUNT(*) FROM ZUGAENGE WHERE 1 = 1");
            BuildQueryConditions(fbController);
            string sql = sqlBuilder.ToString();
            sqlBuilder.Clear();
            return sql;
        }

        public override string ToSqlQuery(FbController2 fbController)
        {
            sqlBuilder.Append($"SELECT FIRST {Limit} SKIP {(Seite - 1) * Limit} Z.*, LIEF_A_NAME1, LIEF_A_KUNDENNR, LIEF_A_LIEFLAND, LIEF_A_BIC, LIEF_A_IBAN FROM ZUGAENGE Z LEFT JOIN LIEFERANTEN L ON L.LIEF_N_NR = Z.ZUGA_N_LIEF WHERE 1 = 1");
            BuildQueryConditions(fbController);
            sqlBuilder.Append(" ORDER BY ZUGA_N_NR DESC");
            string sql = sqlBuilder.ToString();
            sqlBuilder.Clear();
            return sql;
        }

        protected override void BuildQueryConditions(FbController2 fbController)
        {
            sqlBuilder.Append(Option switch
            {
                ZugangFilterOption.OhneRechnung => " AND COALESCE(ZUGA_A_RE_NR, '') = '' AND ZUGA_L_GEPRUEFT = 'N'",
                _ => String.Empty
            });

            if (!String.IsNullOrWhiteSpace(Suchbegriff))
            {
                sqlBuilder.Append(@" AND 
(
   CAST(ZUGA_N_NR as VARCHAR(255)) LIKE @SUCHBEGRIFF
OR CAST(ZUGA_N_LIEF as VARCHAR(255)) LIKE @SUCHBEGRIFF
OR UPPER(CAST(ZUGA_A_LS_NR as VARCHAR(255))) LIKE @SUCHBEGRIFF
OR UPPER(CAST(ZUGA_A_RE_NR as VARCHAR(255))) LIKE @SUCHBEGRIFF
)");


                fbController.AddParameter("@SUCHBEGRIFF", $"%{Suchbegriff}%");
            }

            if (LieferantenId is > 0)
            {
                sqlBuilder.Append(" AND ZUGA_N_LIEF = @LIEFERANTEN_ID");
                fbController.AddParameter("@LIEFERANTEN_ID", LieferantenId);
            }

            if (ZeitraumFiltern)
            {
                if (Von != default)
                {
                    sqlBuilder.Append(" AND ZUGA_D_DATUM >= @VON");
                    fbController.AddParameter("@VON", Von);
                }

                if (Bis != default)
                {
                    sqlBuilder.Append(" AND ZUGA_D_DATUM <= @BIS");
                    fbController.AddParameter("@BIS", Bis);
                }
            }
        }
    }

    public enum ZugangFilterOption
    {
        OhneRechnung,
        Alle
    }
}
