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

namespace WK5.Core.Models
{
    public class Telefonnummer
    {
        public string TELF_A_NR { get; set; } = String.Empty;
        public string TELF_A_TYP { get; set; } = String.Empty;
        public string TELF_A_TELNR { get; set; } = String.Empty;

        public static async Task AddAsync(string TELF_A_NR, string TELF_A_TYP, string TELF_A_TELNR, FbController2? fbController = null)
        {
            string sql = "INSERT INTO TELEFONNUMMERN (TELF_A_NR, TELF_A_TYP, TELF_A_TELNR) VALUES (@TELF_A_NR,@TELF_A_TYP,@TELF_A_TELNR)";
            
            bool doDispose = false;
            if (fbController is null)
            {
                fbController = new FbController2();
                doDispose = true;
            }

            fbController.AddParameter("@TELF_A_NR", TELF_A_NR);
            fbController.AddParameter("@TELF_A_TYP", TELF_A_TYP);
            fbController.AddParameter("@TELF_A_TELNR", TELF_A_TELNR);
            await fbController.QueryAsync(sql);
            if (doDispose)
                fbController.Dispose();
        }
        public static async Task UpdateAsync(string TELF_A_NR, string TELF_A_TYP, string TELF_A_TELNR, FbController2? fbController = null)
        {
            string sql = "UPDATE TELEFONNUMMERN SET TELF_A_TELNR = @TELF_A_TELNR WHERE TELF_A_TYP = @TELF_A_TYP AND TELF_A_NR = @TELF_A_NR";

            bool doDispose = false;
            if (fbController is null)
            {
                fbController = new FbController2();
                doDispose = true;
            }

            fbController.AddParameter("@TELF_A_NR", TELF_A_NR);
            fbController.AddParameter("@TELF_A_TYP", TELF_A_TYP);
            fbController.AddParameter("@TELF_A_TELNR", TELF_A_TELNR);
            await fbController.QueryAsync(sql);
            if (doDispose)
                fbController.Dispose();
        }
        public static async Task InsertOrUpdateAsync(string TELF_A_NR, string TELF_A_TYP, string TELF_A_TELNR, FbController2? fbController = null)
        {
            string sql = "UPDATE OR INSERT INTO TELEFONNUMMERN (TELF_A_NR, TELF_A_TYP, TELF_A_TELNR) VALUES (@TELF_A_NR, @TELF_A_TYP, @TELF_A_TELNR) MATCHING(TELF_A_TYP, TELF_A_NR, TELF_A_TELNR)";

            bool doDispose = false;
            if (fbController is null)
            {
                fbController = new FbController2();
                doDispose = true;
            }

            fbController.AddParameter("@TELF_A_NR", TELF_A_NR);
            fbController.AddParameter("@TELF_A_TYP", TELF_A_TYP);
            fbController.AddParameter("@TELF_A_TELNR", TELF_A_TELNR);
            await fbController.QueryAsync(sql);

            if(doDispose)
                fbController.Dispose();
        }
        public static async Task<Telefonnummer?> GetAsync(string TELF_A_NR, string TELF_A_TYP, FbController2? fbController = null)
        {
            string sql = "SELECT * FROM TELEFONNUMMERN WHERE TELF_A_NR = @TELF_A_NR AND TELF_A_TYP = @TELF_A_TYP";

            bool doDispose = false;
            if (fbController is null)
            {
                fbController = new FbController2();
                doDispose = true;
            }


            fbController.AddParameter("@TELF_A_NR", TELF_A_NR);
            fbController.AddParameter("@TELF_A_TYP", TELF_A_TYP);
            DataRow? row = await fbController.SelectRowAsync(sql);

            if (doDispose)
                fbController.Dispose();

            if (row is null)
                return null;
            else
                return ObjectErweiterung.DataRowZuObjekt(new Telefonnummer(), row);
        }
        public static async IAsyncEnumerable<Telefonnummer> GetAllAsync(FbController2? fbController = null)
        {
            string sql = "SELECT * FROM TELEFONNUMMERN";

            bool doDispose = false;
            if (fbController is null)
            {
                fbController = new FbController2();
                doDispose = true;
            }

            DataTable data = await fbController.SelectDataAsync(sql);
            if (doDispose)
                fbController.Dispose();
            foreach (DataRow row in data.Rows)
            {
                if(row is not null)
                {
                    yield return ObjectErweiterung.DataRowZuObjekt(new Telefonnummer(), row);
                }
            }            
        }

        public static async Task DeleteAsync(string TELF_A_NR, string TELF_A_TYP, FbController2? fbController = null)
        {
            string sql = "DELETE FROM TELEFONNUMMERN WHERE TELF_A_NR = @TELF_A_NR AND TELF_A_TYP = @TELF_A_TYP";

            bool doDispose = false;
            if (fbController is null)
            {
                fbController = new FbController2();
                doDispose = true;
            }


            fbController.AddParameter("@TELF_A_NR", TELF_A_NR);
            fbController.AddParameter("@TELF_A_TYP", TELF_A_TYP);
            await fbController.QueryAsync(sql);

            if (doDispose)
                fbController.Dispose();
        }

        public static string Sanitize(string telefonnummer)
        {
            if (telefonnummer.StartsWith("+"))
            {
                telefonnummer = "00" + telefonnummer;
            }

            if (telefonnummer.StartsWith("000"))
            {
                telefonnummer = "00" + telefonnummer.Substring(3);
            }

            Regex regex = new Regex("\\D");
            return regex.Replace(telefonnummer, "");

        }
        
    }
}
