using System;
using System.Collections.Generic;
using System.Data;
using HMS.Class.Helper;
using HMS.Models;
using HMS.ViewModel;

namespace HMS.Class.DAL
{
    /// <summary>
    /// Patient DAL class.
    /// </summary>
    public class PatientDAL : IDisposable
    {
        #region Variable Declaration

        private DBHelper databaseHelper;

        #endregion

        #region Public Methods

        /// <summary>
        /// Gets all.
        /// </summary>
        /// <param name="roleType">Type of the role.</param>
        /// <param name="searchField">The search field.</param>
        /// <param name="searchValue">The search value.</param>
        /// <param name="sortField">The sort field.</param>
        /// <param name="sortOrder">The sort order.</param>
        /// <param name="pageNo">The page no.</param>
        /// <param name="pageSize">Size of the page.</param>
        /// <returns>
        /// Returns all users.
        /// </returns>
        public List<PatientModel> GetAll(string searchField, string searchValue, string sortField, string sortOrder, int pageNo, int pageSize)
        {
            try
            {
                this.databaseHelper = new DBHelper();

                this.databaseHelper.SetParameterToSQLCommand("@SearchField", searchField);
                this.databaseHelper.SetParameterToSQLCommand("@SearchValue", searchValue);
                this.databaseHelper.SetParameterToSQLCommand("@SortField", sortField);
                this.databaseHelper.SetParameterToSQLCommand("@SortOrder", sortOrder);
                this.databaseHelper.SetParameterToSQLCommand("@PageNo", pageNo);
                this.databaseHelper.SetParameterToSQLCommand("@PageSize", pageSize);

                this.databaseHelper.SetParameterToSQLCommand("@CompanyId", CommonLogic.GetSessionValue(StringConstants.CompanyId));

                IDataReader dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spS_Patient");

                return this.GetPatientData(dataReader);
            }
            catch
            {
                throw;
            }
            finally
            {
                this.databaseHelper.CloseConnection();
            }
        }

        /// <summary>
        /// Gets the by id.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <returns>Returns user by id.</returns>
        public PatientModel GetById(long userId)
        {
            try
            {
                this.databaseHelper = new DBHelper();

                this.databaseHelper.SetParameterToSQLCommand("@UserId", userId);
                this.databaseHelper.SetParameterToSQLCommand("@RoleId", RoleType.Patient.GetHashCode());
                IDataReader dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spS_PatientById");

                List<PatientModel> userList = this.GetPatientData(dataReader);

                if (userList != null && userList.Count > 0)
                {
                    return userList[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                this.databaseHelper.CloseConnection();
            }
        }



        /// <summary>
        /// Saves the specified user.
        /// </summary>
        /// <param name="patient">The user.</param>
        /// <returns>
        /// Returns user id if success else duplicate column name.
        /// </returns>
        public PatientModel Save(PatientModel patient)
        {
            this.databaseHelper = new DBHelper();

            this.databaseHelper.SetParameterToSQLCommand("@FirstName", patient.FirstName);
            this.databaseHelper.SetParameterToSQLCommand("@MiddleName", patient.MiddleName);
            this.databaseHelper.SetParameterToSQLCommand("@LastName", patient.LastName);
            this.databaseHelper.SetParameterToSQLCommand("@Email", patient.Email);
            this.databaseHelper.SetParameterToSQLCommand("@Password", patient.Password);
            this.databaseHelper.SetParameterToSQLCommand("@Phone", patient.Phone);
            this.databaseHelper.SetParameterToSQLCommand("@RoleId", patient.RoleId);
            this.databaseHelper.SetParameterToSQLCommand("@IsActive", patient.IsActive);
            this.databaseHelper.SetParameterToSQLCommand("@CreatedBy", patient.CreatedBy);
            this.databaseHelper.SetParameterToSQLCommand("@CompanyId", patient.CompanyId);
            this.databaseHelper.SetParameterToSQLCommand("@BirthDate", patient.BirthDate);
            this.databaseHelper.SetParameterToSQLCommand("@Gender", patient.Gender);
            this.databaseHelper.SetParameterToSQLCommand("@BloodGroup", patient.BloodGroup);
            this.databaseHelper.SetParameterToSQLCommand("@Address1", patient.Address1);

            if (!string.IsNullOrEmpty(patient.ProfilePic))
            {
                this.databaseHelper.SetParameterToSQLCommand("@ProfilePic", patient.ProfilePic);
            }

            IDataReader dataReader;
            PatientModel tempUser = new PatientModel();

            if (patient.UserId > 0)
            {
                this.databaseHelper.SetParameterToSQLCommand("@UserId", patient.UserId);
                dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spU_Patient");
            }
            else
            {
                dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spI_Patient");
            }

            if (dataReader != null)
            {
                using (dataReader)
                {
                    while (dataReader.Read())
                    {
                        tempUser.UserId = SqlHelper.GetDBLongValue(dataReader["RETURNVAL"]);
                        tempUser.DuplicateColumn = SqlHelper.GetDBStringValue(dataReader["DuplicateColumn"]);
                    }

                    return tempUser;
                }
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Updates the multiple records.
        /// </summary>
        /// <param name="operationType">Type of the operation.</param>
        /// <param name="multiIds">The multi ids.</param>
        /// <returns>Returns 1 if success else 0.</returns>
        public int UpdateMultipleRecords(MultiOperationType operationType, string multiIds)
        {
            this.databaseHelper = new DBHelper();

            this.databaseHelper.SetParameterToSQLCommand("@MultiIds", multiIds);
            this.databaseHelper.SetParameterToSQLCommand("@OperationType", (int)operationType);

            return SqlHelper.ParseNativeInt(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spM_Patient").ToString());
        }


        #region Patient Checkup

        public List<PatientCheckupModel> GetAllCheckupDetail(long patientId)
        {
            try
            {
                this.databaseHelper = new DBHelper();

                this.databaseHelper.SetParameterToSQLCommand("@PatientId", patientId);

                IDataReader dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spS_CheckupDetailByPatientId");


                if (dataReader != null)
                {
                    using (dataReader)
                    {
                        PatientCheckupModel patientCheckup;
                        List<PatientCheckupModel> patientCheckupList = new List<PatientCheckupModel>();

                        while (dataReader.Read())
                        {
                            patientCheckup = new PatientCheckupModel();

                            patientCheckup.PatientCheckupId = SqlHelper.GetDBLongValue(dataReader["PatientCheckupId"]);
                            patientCheckup.PatientId = SqlHelper.GetDBLongValue(dataReader["PatientId"]);
                            patientCheckup.CheckupDate = SqlHelper.GetDBDateTimeValue(dataReader["CheckupDate"]);
                            patientCheckup.DoctorId = SqlHelper.GetDBLongValue(dataReader["DoctorId"]);
                            patientCheckup.DoctorName = SqlHelper.GetDBStringValue(dataReader["DoctorName"]);
                            patientCheckup.Symptoms = SqlHelper.GetDBStringValue(dataReader["Symptoms"]);
                            patientCheckup.Diagnosis = SqlHelper.GetDBStringValue(dataReader["Diagnosis"]);

                            patientCheckupList.Add(patientCheckup);
                        }

                        return patientCheckupList;
                    }
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                this.databaseHelper.CloseConnection();
            }
        }

        public PatientCheckupModel GetCheckupDetail(long patientCheckupId)
        {
            try
            {
                this.databaseHelper = new DBHelper();

                this.databaseHelper.SetParameterToSQLCommand("@PatientCheckupId", patientCheckupId);

                IDataReader dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spS_CheckupDetailById");


                if (dataReader != null)
                {
                    using (dataReader)
                    {
                        PatientCheckupModel patientCheckup;
                        List<PatientCheckupModel> patientCheckupList = new List<PatientCheckupModel>();

                        while (dataReader.Read())
                        {
                            patientCheckup = new PatientCheckupModel();

                            patientCheckup.PatientCheckupId = SqlHelper.GetDBLongValue(dataReader["PatientCheckupId"]);
                            patientCheckup.PatientId = SqlHelper.GetDBLongValue(dataReader["PatientId"]);
                            patientCheckup.CheckupDate = SqlHelper.GetDBDateTimeValue(dataReader["CheckupDate"]);
                            patientCheckup.DoctorId = SqlHelper.GetDBLongValue(dataReader["DoctorId"]);
                            patientCheckup.Symptoms = SqlHelper.GetDBStringValue(dataReader["Symptoms"]);
                            patientCheckup.Diagnosis = SqlHelper.GetDBStringValue(dataReader["Diagnosis"]);

                            patientCheckupList.Add(patientCheckup);
                        }


                        if (patientCheckupList != null && patientCheckupList.Count > 0)
                        {
                            return patientCheckupList[0];
                        }
                        else
                        {
                            return null;
                        }

                    }
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                this.databaseHelper.CloseConnection();
            }
        }

        public long SaveCheckupDetail(PatientCheckupViewModel patientCheckupVM)
        {
            this.databaseHelper = new DBHelper();

            this.databaseHelper.SetParameterToSQLCommand("@PatientId", patientCheckupVM.PatientCheckup.PatientId);
            this.databaseHelper.SetParameterToSQLCommand("@DoctorId", patientCheckupVM.PatientCheckup.DoctorId);
            this.databaseHelper.SetParameterToSQLCommand("@Symptoms", patientCheckupVM.PatientCheckup.Symptoms);
            this.databaseHelper.SetParameterToSQLCommand("@Diagnosis", patientCheckupVM.PatientCheckup.Diagnosis);
            this.databaseHelper.SetParameterToSQLCommand("@CheckupDate", patientCheckupVM.PatientCheckup.CheckupDate);
            this.databaseHelper.SetParameterToSQLCommand("@CreatedBy", CommonLogic.GetSessionValue(StringConstants.UserId));

            if (patientCheckupVM.PatientCheckup.PatientCheckupId > 0)
            {
                this.databaseHelper.SetParameterToSQLCommand("@PatientCheckupId", patientCheckupVM.PatientCheckup.PatientCheckupId);
                return SqlHelper.ParseNativeLong(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spU_PatientCheckup").ToString());
            }
            else
            {
                return SqlHelper.ParseNativeLong(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spI_PatientCheckup").ToString());
            }
        }

        public int DeleteCheckupDetail(long patientCheckupId)
        {
            this.databaseHelper = new DBHelper();

            this.databaseHelper.SetParameterToSQLCommand("@PatientCheckupId", patientCheckupId);
            return SqlHelper.ParseNativeInt(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spD_PatientCheckup").ToString());
        }

        #endregion

        #region Patient Prescription

        public List<PrescriptionModel> GetAllPrescriptionDetail(long patientId)
        {
            try
            {
                this.databaseHelper = new DBHelper();

                this.databaseHelper.SetParameterToSQLCommand("@PatientId", patientId);

                IDataReader dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spS_PrescriptionByPatientId");

                return this.GetPrescriptionData(dataReader);
            }
            catch
            {
                throw;
            }
            finally
            {
                this.databaseHelper.CloseConnection();
            }
        }

        public PrescriptionModel GetPrescriptionDetailByCheckupId(long patientCheckupId)
        {
            try
            {
                this.databaseHelper = new DBHelper();

                this.databaseHelper.SetParameterToSQLCommand("@PatientCheckupId", patientCheckupId);

                IDataReader dataReader = this.databaseHelper.GetReaderByStoredProcedure("HMS_spS_PrescriptionByCheckupId");

                List<PrescriptionModel> prescriptionList = this.GetPrescriptionData(dataReader);

                if (prescriptionList != null && prescriptionList.Count > 0)
                {
                    return prescriptionList[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                this.databaseHelper.CloseConnection();
            }
        }

        private List<PrescriptionModel> GetPrescriptionData(IDataReader dataReader)
        {
            if (dataReader != null)
            {
                using (dataReader)
                {
                    PrescriptionModel prescription;
                    List<PrescriptionModel> prescriptionList = new List<PrescriptionModel>();

                    while (dataReader.Read())
                    {
                        prescription = new PrescriptionModel();
                        prescription.PrescriptionId = SqlHelper.GetDBLongValue(dataReader["PrescriptionId"]);
                        prescription.PatientCheckupId = SqlHelper.GetDBLongValue(dataReader["PatientCheckupId"]);
                        prescription.PatientId = SqlHelper.GetDBLongValue(dataReader["PatientId"]);
                        prescription.TreatmentId = SqlHelper.GetDBLongValue(dataReader["TreatmentId"]);
                        prescription.PrescriptionDate = SqlHelper.GetDBDateTimeValue(dataReader["PrescriptionDate"]);
                        prescription.CaseHistory = SqlHelper.GetDBStringValue(dataReader["CaseHistory"]);
                        prescription.ExtraNotes = SqlHelper.GetDBStringValue(dataReader["ExtraNotes"]);

                        prescriptionList.Add(prescription);
                    }

                    dataReader.NextResult();

                    PrescriptionDetailModel prescriptionDetail;
                    List<PrescriptionDetailModel> prescriptionDetailList = new List<PrescriptionDetailModel>();

                    while (dataReader.Read())
                    {
                        prescriptionDetail = new PrescriptionDetailModel();

                        prescriptionDetail.PrescriptionDetailId = SqlHelper.GetDBLongValue(dataReader["PrescriptionDetailId"]);
                        prescriptionDetail.PrescriptionId = SqlHelper.GetDBLongValue(dataReader["PrescriptionId"]);

                        prescriptionDetail.PatientId = SqlHelper.GetDBLongValue(dataReader["PatientId"]);
                        prescriptionDetail.MedicineId = SqlHelper.GetDBLongValue(dataReader["MedicineId"]);
                        prescriptionDetail.MedicineName = SqlHelper.GetDBStringValue(dataReader["MedicineName"]);
                        prescriptionDetail.NoOfDays = SqlHelper.GetDBIntValue(dataReader["NoOfDays"]);
                        prescriptionDetail.WhenToTake = SqlHelper.GetDBStringValue(dataReader["WhenToTake"]);
                        prescriptionDetail.IsBeforeMeal = SqlHelper.GetDBBoolValue(dataReader["IsBeforeMeal"]);

                        prescriptionDetailList.Add(prescriptionDetail);
                    }

                    // Do mapping 

                    foreach (PrescriptionModel p in prescriptionList)
                    {
                        p.MedicineList = prescriptionDetailList.FindAll(c => c.PrescriptionId == p.PrescriptionId);
                    }

                    // End mapping 

                    return prescriptionList;
                }
            }
            else
            {
                return null;
            }
        }

        public long SavePrescription(PrescriptionModel prescription)
        {
            this.databaseHelper = new DBHelper();

            this.databaseHelper.SetParameterToSQLCommand("@PatientCheckupId", prescription.PatientCheckupId);
            this.databaseHelper.SetParameterToSQLCommand("@PatientId", prescription.PatientId);
            this.databaseHelper.SetParameterToSQLCommand("@TreatmentId", prescription.TreatmentId);
            this.databaseHelper.SetParameterToSQLCommand("@CaseHistory", prescription.CaseHistory);
            this.databaseHelper.SetParameterToSQLCommand("@ExtraNotes", prescription.ExtraNotes);

            if (prescription.PrescriptionId > 0)
            {
                this.databaseHelper.SetParameterToSQLCommand("@PrescriptionId", prescription.PrescriptionId);
                return SqlHelper.ParseNativeLong(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spU_Prescription").ToString());
            }
            else
            {
                return SqlHelper.ParseNativeLong(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spI_Prescription").ToString());
            }
        }

        public void SavePrescriptionDetail(long prescriptionId, long patientId, string prescriptionData)
        {
            this.databaseHelper = new DBHelper();

            this.databaseHelper.SetParameterToSQLCommand("@PrescriptionId", prescriptionId);
            this.databaseHelper.SetParameterToSQLCommand("@PatientId", patientId);
            this.databaseHelper.SetParameterToSQLCommand("@PrescriptionData", prescriptionData);

            SqlHelper.ParseNativeLong(this.databaseHelper.GetExecuteScalarByStoredProcedure("HMS_spU_PrescriptionDetail").ToString());

        }

        #endregion

        #endregion

        #region Dispose Methods

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            this.Dispose(true);
        }

        /// <summary>
        /// Releases unmanaged and - optionally - managed resources.
        /// </summary>
        /// <param name="disposing"><c>true</c> to release both managed and unmanaged resources; <c>false</c> to release only unmanaged resources.</param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                this.databaseHelper.Dispose();
            }
        }

        #endregion

        #region Helper Methods

        /// <summary>
        /// Gets the user data.
        /// </summary>
        /// <param name="dataReader">The data reader.</param>
        /// <returns>Returns user list.</returns>
        private List<PatientModel> GetPatientData(IDataReader dataReader)
        {
            if (dataReader != null)
            {
                using (dataReader)
                {
                    PatientModel patient;
                    List<PatientModel> patientList = new List<PatientModel>();

                    while (dataReader.Read())
                    {
                        patient = new PatientModel();

                        patient.UserId = SqlHelper.GetDBLongValue(dataReader["UserId"]);
                        patient.FirstName = SqlHelper.GetDBStringValue(dataReader["FirstName"]);
                        patient.MiddleName = SqlHelper.GetDBStringValue(dataReader["MiddleName"]);
                        patient.LastName = SqlHelper.GetDBStringValue(dataReader["LastName"]);
                        patient.Email = SqlHelper.GetDBStringValue(dataReader["Email"]);
                        patient.Password = SqlHelper.GetDBStringValue(dataReader["Password"]);
                        patient.Phone = SqlHelper.GetDBStringValue(dataReader["Phone"]);
                        patient.IsActive = SqlHelper.GetDBBoolValue(dataReader["IsActive"]);
                        patient.RoleId = SqlHelper.GetDBIntValue(dataReader["RoleId"]);
                        patient.CompanyId = SqlHelper.GetDBLongValue(dataReader["CompanyId"]);
                        patient.BirthDate = SqlHelper.GetDBDateTimeValue(dataReader["BirthDate"]);
                        patient.Gender = SqlHelper.GetDBStringValue(dataReader["Gender"]);
                        patient.BloodGroup = SqlHelper.GetDBStringValue(dataReader["BloodGroup"]);
                        patient.Address1 = SqlHelper.GetDBStringValue(dataReader["Address1"]);
                        patient.ProfilePic = SqlHelper.GetDBStringValue(dataReader["ProfilePic"]);
                        patient.PatientCode = SqlHelper.GetDBStringValue(dataReader["PatientCode"]);
                        patient.CompanyName = SqlHelper.GetDBStringValue(dataReader["CompanyName"]);
                        patientList.Add(patient);
                    }

                    if (patientList.Count > 0)
                    {
                        dataReader.NextResult();

                        while (dataReader.Read())
                        {
                            patientList[0].TotalRecordCount = SqlHelper.GetDBIntValue(dataReader["TotalRecordCount"]);
                        }
                    }

                    return patientList;
                }
            }
            else
            {
                return null;
            }
        }

        #endregion
    }
}