using KarleyLibrary.Erweiterungen;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace WK5.Core.Models
{
    public class Personal : ICloneable
    {
        #region Datenbankfelder
        public int PERS_N_NR { get; set; }
        public string PERS_A_NAME { get; set; } = String.Empty;
        public string PERS_A_VORNAME { get; set; } = String.Empty;
        public string? PERS_A_ZEICHEN { get; set; }
        public bool PERS_L_VERTRETER { get; set; }
        public bool PERS_L_INAKTIV { get; set; }
        public string PERS_A_EMAIL { get; set; } = String.Empty;

        public string? PERS_A_USERNAME { get; set; }
        public string? PERS_A_PASSWORD { get; set; }
        public string? PERS_A_STRASSE { get; set; }
        public string? PERS_A_LAND { get; set; }
        public string? PERS_A_PLZ { get; set; }
        public string? PERS_A_ORT { get; set; }
        public string? PERS_A_TELPRIVAT1 { get; set; }
        public string? PERS_A_TELPRIVAT2 { get; set; }
        public string? PERS_A_TELFIRMA { get; set; }
        public string? PERS_A_FAXFIRMA { get; set; }
        public string? PERS_A_BERUF { get; set; }
        public string? PERS_A_FUNKTION { get; set; }
        public string? PERS_A_ABTEILUNG { get; set; }
        public string? PERS_A_KARTENNR { get; set; }
        public string? PERS_A_BANKKONTO { get; set; }
        public string? PERS_A_BANKNAME { get; set; }
        public string? PERS_A_BLZ { get; set; }
        public string? PERS_A_NOTIZ { get; set; }
        public string? PERS_A_SELEKTION1 { get; set; }
        public string? PERS_A_SELEKTION2 { get; set; }
        public string? PERS_A_SELEKTION3 { get; set; }
        public string? PERS_A_SPRACHE { get; set; }
        public string? PERS_A_ARTIKELSUCHE { get; set; }
        public string? PERS_A_PFAD_DOC_EX { get; set; }
        public string? PERS_A_PFAD_DOC_IM { get; set; }
        public string? PERS_A_PFAD_BELE_EX { get; set; }
        public string? PERS_A_PFAD_TANNS_IM { get; set; }
        public string? PERS_A_PFAD_TANNS_EX { get; set; }
        public string? PERS_A_PFAD_TANNS_PDF { get; set; }
        public string? PERS_A_MAIL_FONT { get; set; }
        public bool PERS_L_ADMIN { get; set; }
        public bool PERS_L_SYSTEM { get; set; }
        public bool PERS_L_TICKET { get; set; }
        public bool PERS_L_PREISEVERSCHLEIERN { get; set; }
        public bool PERS_L_ANZEIGEFENSTERLISTE { get; set; }
        public bool PERS_L_LAGER { get; set; }
        public bool PERS_L_ZUGANG { get; set; }
        public bool PERS_L_FERTINFO { get; set; }
        public bool PERS_L_AUINFO { get; set; }
        public bool PERS_L_LSINFO { get; set; }
        public bool PERS_L_OHNE_PROJEKTKALK { get; set; }
        public bool PERS_L_DOCK_LOESCHEN { get; set; }
        public bool PERS_L_DOCK_GES { get; set; }
        public bool PERS_L_WORD { get; set; }
        public bool PERS_L_BELESUCHE_AN { get; set; }
        public bool PERS_L_BELESUCHE_AU { get; set; }
        public bool PERS_L_BELESUCHE_LS { get; set; }
        public bool PERS_L_BELESUCHE_LSOFF { get; set; }
        public bool PERS_L_BELESUCHE_RE { get; set; }
        public bool PERS_L_BELESUCHE_GU { get; set; }
        public bool PERS_L_EINSATZPLAN { get; set; }
        public bool PERS_L_EIRE_PRUEF { get; set; }
        public bool PERS_L_TANNS { get; set; }
        public bool PERS_L_HILFELOKAL { get; set; }
        public DateTime PERS_D_GEBDATUM { get; set; }
        public DateTime PERS_D_EINSTELLDAT { get; set; }
        public decimal PERS_N_SKALIERUNG { get; set; }
        public decimal PERS_N_ZEIT_MO { get; set; }
        public decimal PERS_N_ZEIT_DI { get; set; }
        public decimal PERS_N_ZEIT_MI { get; set; }
        public decimal PERS_N_ZEIT_DO { get; set; }
        public decimal PERS_N_ZEIT_FR { get; set; }
        public DateTime PERS_D_LASTLOGIN { get; set; }
        public DateTime PERS_D_LASTLOGOUT { get; set; }
        public DateTime PERS_TIMESTAMP { get; set; }
        public int PERS_N_KOSTENSTELL { get; set; }
        public int PERS_N_BELEGTAGE { get; set; }
        public int PERS_N_STUNDENSATZ { get; set; }
        public int PERS_N_KOSTENSTELLE { get; set; }
        public int PERS_N_RECHTEGRUPPE { get; set; }
        public int PERS_N_NIEDERLASSUNG { get; set; }
        public int PERS_N_MAIL_SIZE { get; set; }
        public int PERS_N_SPERRUSERID { get; set; }
        public int PERS_N_LASTUSER { get; set; }
        #endregion

        public string GetName() => Regex.Replace($"{PERS_A_VORNAME} {PERS_A_NAME}", "  +/g", " ").Trim();

        public static async Task<Personal?> GetPersonalAsync(int PERS_N_NR)
        {
            using FbController2 fbController = new FbController2();
            fbController.AddParameter("@PERS_N_NR", PERS_N_NR);
            var row = await fbController.SelectRowAsync("SELECT * FROM PERSONAL WHERE PERS_N_NR = @PERS_N_NR");
            return row is null ? null : ObjectErweiterung.DataRowZuObjekt(new Personal(), row);
        }
        public static async IAsyncEnumerable<Personal> GetAllPersonalAsync()
        {
            using FbController2 fbController = new FbController2();
            DataTable data = await fbController.SelectDataAsync("SELECT * FROM PERSONAL ORDER BY PERS_A_VORNAME, PERS_A_NAME");
            foreach (DataRow row in data.Rows)
            {
                yield return ObjectErweiterung.DataRowZuObjekt(new Personal(), row);
            }
        }

        public static async IAsyncEnumerable<Gruppe> GetGruppenAsync(int personalnummer)
        {
            using FbController2 fbController = new FbController2();

            fbController.AddParameter("@ID", personalnummer);

            DataTable data = await fbController.SelectDataAsync("SELECT GRUP.* FROM WK5_PERSONAL_ZU_GRUPPE LEFT JOIN WK5_GRUPPEN GRUP ON PZUG_N_GRUPPE_ID = GRUP_N_ID WHERE PZUG_N_PERSONAL_ID = @ID");

            foreach (DataRow row in data.Rows)
            {
                if (row is not null)
                {
                    yield return ObjectErweiterung.DataRowZuObjekt(new Gruppe(), row);
                }
            }
        }

        public object Clone()
        {
            return this.MemberwiseClone();
        }
    }
}
