using iText.StyledXmlParser.Css.Util;
using Microsoft.Data.SqlClient;
using PropertyFuzeData;
using System.Data;

namespace EfformanceBillingData
{
    public class SendMailTransactionInvoiceDetail
    {
        public Guid SendMailTransactionInvoiceID { get; set; }
        public int C2MJobID { get; set; }
        public int InvoiceDetailID { get; set; }
        public int PropertyID { get; set; }
        public int UnitID { get; set; }
        public int AccountID { get; set; }
        public string? Comment { get; set; }
        public int InventoryItemID { get; set; }
        public int ChargeTypeID { get; set; }
        public int Quantity { get; set; }
        public decimal UnitCost { get; set; }

        public List<PendingSendMailTransactionInvoiceDetail> GetPendingSendMailInvoiceTransactionDetailsForClient(string connectionString, int clientID, DateOnly BeforeDate)
        {
            SQLServerHelper sQLServerHelper = new SQLServerHelper();
            //string cmdText = @"
            //    select
            //      SMTID.SendMailTransactionInvoiceID,
            //     SMTID.InvoiceDetailID,
            //        SM.LocationID,
            //        SMTID.PropertyID,
            //        J.CostCode,
            //     SMTID.Quantity,
            //     SMTID.UnitCost
            //    from SendMail SM
            //     inner join SendMailTransaction SMT on SMT.SendMailID = SM.ID
            //     inner join SendMailTransactionInvoice SMTI on SMT.ID = SMTI.SendMailTransactionID
            //     inner join SendMailTransactionInvoiceDetail SMTID on SMTI.Id = SMTID.SendMailTransactionInvoiceID
            //        inner join C2MJob J on SMTID.C2MJobID = J.C2MJobID 
            //    where SMT.SendMailTransactionTypeID = 4
            //     and SM.ClientID = @clientID
            //     and SMTI.Processed = 0
            //     and SM.CreatedOn < @beforeDate";

            string cmdText = @"
                Declare @SendMailFiltered Table (SendMailID uniqueidentifier Primary key clustered, SendMailTransactionInvoiceID uniqueidentifier, LocationID int);

                insert into @SendMailFiltered
                select
	                SM.ID,
	                SMTI.ID,
	                SM.LocationID
                from SendMail SM
	                inner join SendMailTransaction SMT on SMT.SendMailID = SM.ID
	                inner join SendMailTransactionType SMTT on SMT.SendMailTransactionTypeID = SMTT.ID
	                inner join SendMailTransactionInvoice SMTI on SMT.ID = SMTI.SendMailTransactionID
                where SMT.SendMailTransactionTypeID = 4
	                and SM.ClientID = @clientID
	                and SMTI.Processed = 0
	                and SM.CreatedOn < @beforeDate
                order by
	                SM.ID;

                Declare @Recipients Table (SendMailID UniqueIdentifier, SendMailTransactionInvoiceID uniqueidentifier, LocationID int, RecipientID UniqueIdentifier, PropertyID int, Property NVarchar(255), CostCode NVarchar(255));
                Insert Into @Recipients
                Select
	                iSMF.SendMailID,
	                iSMF.SendMailTransactionInvoiceID,
	                iSMF.LocationID,
	                iSMRQR.RecipientID As RecipientID,
	                iSMRQR.PropertyID as PropertyID,
	                iSMRQR.Property as Property,
	                iSMRQR.CostCode As CostCode
                From @SendMailFiltered iSMF
	                Inner Join SendMail SM On SM.ID = iSMF.SendMailID
	                CROSS APPLY OPENJSON(SM.[Request],'$')
	                WITH (
	                Recipients NVarchar(max) '$.recipients' AS JSON
	                ) AS iSMRQ
	                CROSS APPLY OPENJSON(iSMRQ.Recipients,'$')
	                WITH (
	                RecipientID UniqueIdentifier '$.recipientID',
	                PropertyID int '$.propertyID',
	                Property NVarchar(255) '$.property',
	                CostCode NVarchar(255) '$.costCode'
	                ) AS iSMRQR;

                select
	                R.SendMailID,
	                R.SendMailTransactionInvoiceID,
	                R.LocationID,
                    R.PropertyID,
	                R.Property,
                    R.CostCode,
	                SMTID.Quantity,
	                SMTID.UnitCost
                from
	                @Recipients R
	                inner join C2MJob J on J.RecipientID = R.RecipientID
	                inner join SendMailTransactionInvoiceDetail SMTID on SMTID.C2MJobID = J.C2MJobID;
                    ";

            List<PendingSendMailTransactionInvoiceDetail> sendMailTransactionInvoiceDetails = new();

            using (SqlConnection sqlConnection = sQLServerHelper.GetConnection(connectionString))
            {
                sqlConnection.Open();
                using SqlCommand sqlCommand = new SqlCommand(cmdText, sqlConnection);
                sqlCommand.Parameters.AddWithValue("@clientID", clientID);
                sqlCommand.Parameters.AddWithValue("@beforeDate", BeforeDate);
                using IDataReader dataReader = sqlCommand.ExecuteReader();

                while (dataReader.Read())
                {
                    sendMailTransactionInvoiceDetails.Add(new PendingSendMailTransactionInvoiceDetail
                    {
                        SendMailID = dataReader.GetGuid(0),
                        SendMailTransactionInvoiceID = dataReader.GetGuid(1),
                        LocationID = dataReader.GetInt32(2),
                        PropertyID = dataReader.GetInt32(3),
                        Property = !dataReader.IsDBNull(4) ? dataReader.GetString(4) : string.Empty,
                        CostCode = !dataReader.IsDBNull(5) ? dataReader.GetString(5) : string.Empty,
                        Quantity = dataReader.GetInt32(6),
                        UnitCost = dataReader.GetDecimal(7)
                    });
                }

            }

            return sendMailTransactionInvoiceDetails;
        }

        public class PendingSendMailTransactionInvoiceDetail
        {
            public Guid SendMailID { get; set; }
            public Guid SendMailTransactionInvoiceID { get; set; }
            public int LocationID { get; set; }
            public int PropertyID { get; set; }
            public string Property { get; set; }
            public string CostCode { get; set; }
            public int Quantity { get; set; }
            public decimal UnitCost { get; set; }
        }

    }
}
