using Microsoft.Data.SqlClient;
using PaymentServer.Models;
using System.Text.RegularExpressions;


namespace PaymentServer.Services
{
    public static class PaymentDataService
    {
        static string connectionString = "Server=LPP-SQL01;Database=NLCF;Trusted_Connection=True;TrustServerCertificate=True;";
        static string baseQuery = $@"
                  SELECT TOP (5000)
                  SUBSTRING(TRXDSCRN, PATINDEX('%[0-9]^3-[0-9]^7-[0-9]^3%', TRXDSCRN), 16) as ContractNumber
                 ,BACHNUMB, VENDORID, PORDNMBR,  PRCHAMNT, DOCDATE, DEX_ROW_TS
                 ,app.CustomerKey
                 ,app.TotalListPrice
                 ,app.EquipmentCost
                 ,ps.PersonnelName
                 ,su.Email
                 FROM [NLCF].[dbo].[PM30200] as pif
	                LEFT JOIN[LCCPHL-PDSQL003].[LEAFCore].[dbo].[Application] as app
		                ON
				                 app.ContractID = TRY_CAST(SUBSTRING(TRXDSCRN, PATINDEX('%[0-9]^3-[0-9]^7-[0-9]^3%', TRXDSCRN) + 5, 7) as int)
		                AND app.ScheduleID = TRY_CAST(SUBSTRING(TRXDSCRN, PATINDEX('%[0-9]^3-[0-9]^7-[0-9]^3%', TRXDSCRN) + 13, 3) as int)
		                AND app.LessorID = TRY_CAST(SUBSTRING(TRXDSCRN, PATINDEX('%[0-9]^3-[0-9]^7-[0-9]^3%', TRXDSCRN), 4) as int)
	                LEFT JOIN[LCCPHL-PDSQL003].[LEAFCore].[dbo].[Personnel] as ps
		                ON app.MarketingRepID = ps.PersonnelID
	                LEFT JOIN[LCCPHL-PDSQL003].[LEAFCore].[dbo].[SalesUser] as su
		                ON
			                (PARSENAME(REPLACE(ps.PersonnelName, ' ', '.'), 1) = su.FirstName AND REPLACE(REVERSE(PARSENAME(REVERSE(REPLACE(ps.PersonnelName, ' ', '.')), 1)), ',', '') = su.LastName)
                WHERE DOCTYPE = 1
                AND(BACHNUMB LIKE '%ACH%' OR BACHNUMB LIKE '%CKS%' OR BACHNUMB LIKE '%WIRE%' OR BACHNUMB LIKE '%CHK%' OR BACHNUMB LIKE '%CK%')";
        static Regex contractNumberRegex = new Regex(@"\d{3}-\d{7}-\d{3}");
            
        public static string? ValidContractNumber(string contractNumber)
        {
            Match match = contractNumberRegex.Match(contractNumber);
            if (match.Success)
                return match.Value;
            else return null;

        }

        public static List<VendorPayment>? GetContractData(string contractNumber)
        {   
            // Validate the contract number to make sure nothing strange get's added to the query
            string query = baseQuery + $" AND TRXDSCRN = '{ValidContractNumber(contractNumber)}'";
          return PullPaymentData(query);

        }

        public static List<VendorPayment>? GetNew(DateTime minTimestamp)
        {
            string query = baseQuery +$@" AND DEX_ROW_TS >= '{minTimestamp.ToString("yyyy-MM-dd HH:mm:ss")} 
            AND TRXDSCRN LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]%''";
            return PullPaymentData(query);
        }

        static List<VendorPayment>? PullPaymentData(string sqlQuery)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sqlQuery, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                List<VendorPayment>? payments = new List<VendorPayment>();
                try
                {
                    while (reader.Read())
                    {
                        VendorPayment paymentData = new VendorPayment
                        {
                            contractNumber = reader.GetString(0),
                            bachNumber = reader.GetString(1),
                            vendorId = reader.GetString(2),
                            pordNumber = reader.GetString(3),
                            purchaseAmount = reader.GetDecimal(4),
                            docDate = reader.GetDateTime(5),
                            dexRowTimestamp = reader.GetDateTime(6),
                            customerKey = reader.IsDBNull(7) ? null : reader.GetInt32(7),
                            totalListPrice = reader.IsDBNull(8) ? null : reader.GetDecimal(8),
                            equipmentCost = reader.IsDBNull(9) ? null : reader.GetDecimal(9),
                            salesRep = reader.IsDBNull(10) ? null : reader.GetString(10),
                            salesRepEmail = reader.IsDBNull(11) ? null : reader.GetString(11)

                        };
                        payments.Add(paymentData);
                        Console.WriteLine(String.Format("{0}", paymentData));
                    }
                }
                finally
                {
                    reader.Close();
                }
                Console.WriteLine("Failed to get data...");
                return payments;
            }
        }
    
    }
}