using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WK5.Core.Models;
using WK5.Core.Services;

namespace WK5.Core.Basis.Filter
{
    public class KundenÜbersichtFilter : PageFilter
    {
        private int _phase = 1;
        public const int MaxPhase = 5;
        public const int MinPhase = 1;
        public bool DoPhaseSearch => !String.IsNullOrWhiteSpace(Suchbegriff);
        public const int AbortableUntilPhase = 1;

        public int Phase
        {
            get
            {
                if (_phase < MinPhase)
                {
                    return MinPhase;
                }
                else if (_phase > MaxPhase)
                {
                    return MaxPhase;
                }
                else
                {
                    return _phase;
                }
            }

            set
            {
                if (value < MinPhase)
                {
                    _phase = MinPhase;
                }
                else if (value > MaxPhase)
                {
                    _phase = MaxPhase;
                }
                else
                {
                    _phase = value;
                }
            }
        }

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

                return false;
            }
        }

        public bool GesperrteKundenSuchen { get; set; } = true;        
        public bool TelefonnummerSuchen { get; set; } = false;
        public override string ToCountQuery(FbController2 fbController)
        {
            Suchbegriff = Suchbegriff.Replace("%", String.Empty);

            sqlBuilder.Clear();
            sqlBuilder.Append("SELECT COUNT(*) FROM KUNDEN WHERE 1=1");
            BuildQueryConditions(fbController);
            return sqlBuilder.ToString();
        }

        public override string ToSqlQuery(FbController2 fbController)
        {
            Suchbegriff = Suchbegriff.Replace("%", String.Empty);

            sqlBuilder.Clear();
            sqlBuilder.Append(@$" SELECT FIRST {Limit} SKIP {(Seite - 1) * Limit} 
KUND_A_NR, KUND_A_NAME1, KUND_A_NAME2, KUND_A_NAME3, KUND_A_STRASSE, KUND_A_LAND, KUND_A_PLZ, KUND_A_ORT, KUND_A_USTIDNR, KUND_L_SPERRE, KUND_A_TEL1,
(SELECT SUM(OP) AS OFFENEPOSTEN FROM
(
select
BELE_A_TYP, 
CASE WHEN BELE_A_TYP = 'GU' THEN
     -COALESCE(SUM(BELE_N_BRUTTO), 0) - coalesce(SUM(BezahltBetrag), 0)
ELSE
     COALESCE(SUM(BELE_N_BRUTTO), 0) - coalesce(SUM(BezahltBetrag), 0)
END AS OP
from
  KUNDENBELEGE
where
  (
    (
      BELE_A_TYP ='RE'
    and BELE_L_BEZAHLT = 'N'
    ) or
   (BELE_A_TYP ='GU' and BELE_L_VERRECHNET = 'N' )
  )
 and BELE_A_KUNDENNR = KUND_A_NR
 GROUP BY BELE_A_TYP)) AS OP 
FROM KUNDEN WHERE 1=1");

            BuildQueryConditions(fbController);

            sqlBuilder.Append(" ORDER BY KUND_WK5_N_WICHTIGKEIT DESC, KUND_A_NR DESC");

            return sqlBuilder.ToString();
        }

        protected override void BuildQueryConditions(FbController2 fbController)
        {
            if (!TelefonnummerSuchen)
            {
                if (!String.IsNullOrWhiteSpace(Suchbegriff))
                {
                    if (Suchbegriff.Length > 255)
                    {
                        throw new ArgumentOutOfRangeException("Der Suchbegriff darf nicht mehr als 255 Zeichen haben");
                    }

                    // "Etiketten" "201mm"
                    //fbController.AddParameter("@SUCHBEGRIFF", $"%{String.Join($"%{(SuchbegriffOderVerknüpfung ? "|" : "&")}%", Suchbegriff.Split(' ', '-', '_', ','))}%");
                    sqlBuilder.Append(" AND ( ");
                    switch (Phase)
                    {
                        case 1:
                            BuildPhase1Condition(fbController);
                            break;
                        case 2:
                            BuildPhase2Condition(fbController);
                            break;
                        case 3:
                            BuildPhase3Condition(fbController);
                            break;
                        case 4:
                            BuildPhase4Condition(fbController);
                            break;
                        case 5:
                            BuildPhase5Condition(fbController);
                            break;
                        default:
                            BuildPhase1Condition(fbController);
                            break;
                    }
                    sqlBuilder.Append(" ) ");

                    if (!GesperrteKundenSuchen)
                    {
                        sqlBuilder.Append(" AND KUND_L_SPERRE = 'N' ");
                    }
                }
            }
            else
            {
                sqlBuilder.Append(" AND KUND_A_NR IN (SELECT TELF_A_NR FROM TELEFONNUMMERN WHERE TELF_A_TYP = @TELF_A_TYP AND CAST(TELF_A_TELNR AS VARCHAR(255)) LIKE @TELF_A_TELNR)");
                fbController.AddParameter("@TELF_A_TYP", "KU");
                fbController.AddParameter("@TELF_A_TELNR", $"%{Telefonnummer.Sanitize(Suchbegriff)}%");
            }
        }

        private void BuildPhase1Condition(FbController2 fbController)
        {
            sqlBuilder.Append(" CAST(UPPER(KUND_A_NR) AS VARCHAR(255)) = @SUCHBEGRIFF ");
            fbController.AddParameter("@SUCHBEGRIFF", Suchbegriff.ToUpper());
        }

        private void BuildPhase2Condition(FbController2 fbController)
        {
            List<string> searches = new List<string>();
            foreach (string suchfeld in Suchfelder)
            {
                searches.Add($" CAST(UPPER({suchfeld}) AS VARCHAR(255)) = @SUCHBEGRIFF ");
            }

            sqlBuilder.Append(String.Join(" OR ", searches));

            fbController.AddParameter("@SUCHBEGRIFF", Suchbegriff.ToUpper());
        }
        private void BuildPhase3Condition(FbController2 fbController)
        {
            List<string> searches = new List<string>();
            foreach (string suchfeld in Suchfelder)
            {
                searches.Add($" CAST(UPPER({suchfeld}) AS VARCHAR(255)) SIMILAR TO @SUCHBEGRIFF escape '\\'");
            }

            sqlBuilder.Append(String.Join(" OR ", searches));

            fbController.AddParameter("@SUCHBEGRIFF", $"%{FbController2.SanitizeRegex(Suchbegriff).Replace(' ', '_')}%");
        }

        private void BuildPhase4Condition(FbController2 fbController)
        {
            List<string> searches = new List<string>();
            int zähler = 0;
            foreach (string suchfeld in Suchfelder)
            {
                List<string> subsearches = new List<string>();
                foreach (string token in Suchbegriff.Split(' ', '-', '_', ','))
                {
                    zähler++;
                    subsearches.Add($" CAST(UPPER({suchfeld}) AS VARCHAR(255)) SIMILAR TO @SUCHBEGRIFF{zähler} escape '\\' ");
                    fbController.AddParameter($"@SUCHBEGRIFF{zähler}", $"%{FbController2.SanitizeRegex(token)}%");

                }

                searches.Add($"({String.Join(" AND ", subsearches)})");
            }

            sqlBuilder.Append(String.Join(" OR ", searches));
        }

        private void BuildPhase5Condition(FbController2 fbController)
        {
            List<string> searches = new List<string>();
            int zähler = 0;
            foreach (string suchfeld in Suchfelder)
            {
                List<string> subsearches = new List<string>();
                foreach (string token in Suchbegriff.Split(' ', '-', '_', ','))
                {
                    zähler++;
                    subsearches.Add($" CAST(UPPER({suchfeld}) AS VARCHAR(255)) SIMILAR TO @SUCHBEGRIFF{zähler} escape '\\' ");
                    fbController.AddParameter($"@SUCHBEGRIFF{zähler}", $"%{FbController2.SanitizeRegex(token)}%");
                }

                searches.Add($"({String.Join(" OR ", subsearches)})");
            }

            sqlBuilder.Append(String.Join(" OR ", searches));

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

        private List<string> Suchfelder { get; set; } = new List<string>()
        {
            "KUND_A_NR",
            "KUND_A_SUCHCODE",
            "KUND_A_USTIDNR",
            "KUND_A_PLZ",
            "KUND_A_NAME1",
            "KUND_A_NAME2",
            "KUND_A_NAME3",
            "KUND_A_ORT",
            "KUND_A_GLN"
        };
    }
}
