package repository

import (
	"bpmsapi/dto"
	"bpmsapi/entity"
	"errors"
	"fmt"
	"log"
	"strings"

	"github.com/jmoiron/sqlx"
)

type ConfigActions interface {
	FetchStates() ([]entity.ConfigurationDropdown, error)
	FetchCities(stateId int) ([]entity.ConfigurationDropdown, error)
	FetchApartments(stateId int, cityid int) ([]entity.ConfigurationDropdown, error)
	FetchBanners(apartmentid int) ([]entity.ConfigurationDropdown, error)
	FetchApartmentsById(apartmentlist string, language_code int) ([]entity.UserApartmentInfo, error)
	FetchApartmentBasicInfo(apartmentid int) (basicinfo []entity.BasicInfo, err error)
	FetchApartmentMessages(apartmentid int, languageCode int) (apartmentlist []entity.Announcement, err error)
	AddState(input entity.AddStateInput) ([]entity.State, error)
	AddCity(input entity.AddCityInput) ([]entity.City, error)
	AddFeedback(input entity.AddFeedback) (bool, error)
	UpdateApartmentParam(tx *sqlx.Tx, Apartment_Id int, Param_Key string, Param_Value string, PARAM_SUB string, Validity string, Action string) (apartmentParamList []entity.UpdateApartmentParamResp, err error)
}

type ConfigRepository struct {
	db *sqlx.DB
}

// auth-jwt
func ConfigRepositoryInit(db *sqlx.DB) *ConfigRepository {
	return &ConfigRepository{
		db: db,
	}
}

func (configRepo *ConfigRepository) Begin() (*sqlx.Tx, error) {
	tx, err := configRepo.db.Beginx()
	if err != nil {
		return nil, err
	}
	return tx, nil
}
func (configRepo *ConfigRepository) FetchApartmentsById(apartmentlist string, language_code int, mobileno string) (apartments []entity.UserApartmentInfo, err error) {
	query := "Select atrans.Apartment_Name as Name, ba.Apartment_Id as Id, ba.Apartment_Image as Apartment_Image, buam.User_Type, buam.Door_No, buam.Block,buam.GSTN, buam.TIN, buam.PAN, bct.City_Name as City_Name, bst.State_Name as State_Name, buam.Billing_Address_Id, bas.Country as Billing_Country, bas.State as Billing_State, bas.City as Billing_City, bas.Area_Name as Billing_Area_Name, bas.Address_Line1 as Billing_Address_Line1, bas.Address_Line2 as Billing_Address_Line2, bas.Pincode as Billing_Pincode, bas.Latitude as Billing_Latitude, bas.Longitude as Billing_Longitude from BPMS_APARTMENT ba INNER JOIN BPMS_APARTMENT_TRANSLATIONS atrans on atrans.Apartment_Id = ba.Apartment_Id LEFT JOIN BPMS_STATE_TRANSLATIONS bst ON ba.State = bst.State_Id AND bst.Language_Code = ? LEFT JOIN BPMS_CITY_TRANSLATIONS bct ON ba.City = bct.City_Id AND bct.Language_Code =? INNER JOIN BPMS_USER_APARTMENT_MAPPING buam ON ba.Apartment_Id = buam.Apartment_Id INNER JOIN BPMS_USER bu on buam.User_Id  = bu.User_Id LEFT JOIN BPMS_ADDRESS bas ON buam.Billing_Address_Id = bas.Address_Id where ba.Apartment_Id IN " + apartmentlist + " and atrans.Language_Code = ? and bu.User_Mobno = ?"
	// query := "Select atrans.Apartment_Name as Name, ba.Apartment_Id as Id, ba.Apartment_Image as Apartment_Image, buam.User_Type, buam.Door_No, buam.Block,buam.GSTN, buam.TIN, buam.PAN, buam.Billing_Address_Id, bct.City_Name as City_Name, bst.State_Name as State_Name from BPMS_APARTMENT ba INNER JOIN BPMS_APARTMENT_TRANSLATIONS atrans on atrans.Apartment_Id = ba.Apartment_Id LEFT JOIN BPMS_STATE_TRANSLATIONS bst ON ba.State = bst.State_Id AND bst.Language_Code = ? LEFT JOIN BPMS_CITY_TRANSLATIONS bct ON ba.City = bct.City_Id AND bct.Language_Code =? INNER  JOIN BPMS_USER_APARTMENT_MAPPING buam ON ba.Apartment_Id = buam.Apartment_Id INNER JOIN BPMS_USER bu on buam.User_Id  = bu.User_Id where ba.Apartment_Id IN " + apartmentlist + " and atrans.Language_Code = ? and bu.User_Mobno =?"
	//query := "select atrans.Apartment_Name as Name, ba.Apartment_Id as Id from BPMS_APARTMENT ba INNER JOIN BPMS_APARTMENT_TRANSLATIONS atrans on atrans.Apartment_Id = ba.Apartment_Id  where ba.Apartment_Id IN ? AND atrans.Language_Code = ?"
	err = configRepo.db.Select(&apartments, query, language_code, language_code, language_code, mobileno)
	if err != nil {
		log.Println(err.Error())
		return nil, err
	} else {
		return apartments, nil
	}
}

// func (configRepo *ConfigRepository) FetchApartmentsById(apartmentlist string, language_code int) (apartments []entity.ConfigurationDropdown, err error) {
// 	query := "select atrans.Apartment_Name as Name, ba.Apartment_Id as Id from BPMS_APARTMENT ba INNER JOIN BPMS_APARTMENT_TRANSLATIONS atrans on atrans.Apartment_Id = ba.Apartment_Id  where ba.Apartment_Id IN" + apartmentlist + " and atrans.Language_Code = ?"
// 	//query := "select atrans.Apartment_Name as Name, ba.Apartment_Id as Id from BPMS_APARTMENT ba INNER JOIN BPMS_APARTMENT_TRANSLATIONS atrans on atrans.Apartment_Id = ba.Apartment_Id  where ba.Apartment_Id IN ? AND atrans.Language_Code = ?"
// 	err = configRepo.db.Select(&apartments, query, language_code)
// 	if err != nil {
// 		log.Println(err.Error())
// 		return nil, err
// 	} else {
// 		return apartments, nil
// 	}
// }

func (configRepo *ConfigRepository) FetchBanners(apartmentid int) (banners []entity.ConfigurationDropdown, err error) {
	err = configRepo.db.Select(&banners, "select Banner_Id as Id,Image_Path as Name from BPMS_BANNER where Apartment_Id = ? AND Status = 1", apartmentid)
	if err != nil {
		return nil, err
	} else {
		return banners, nil
	}
}

func (configRepo *ConfigRepository) FetchApartmentMessages(apartmentid int, languageCode int) (apartmentlist []entity.Announcement, err error) {
	err = configRepo.db.Select(&apartmentlist, "select Param_Value as Value,PARAM_SUB as Name from BPMS_APARTMENT_PARAM where Apartment_Id = ? and Param_Key = 'Announcement' and Validity = 'Yes'", apartmentid)
	for i := range apartmentlist {
		apartmentParam := &apartmentlist[i] // Access the current item by reference

		fmt.Println(apartmentParam.Value)
		paramVal := apartmentParam.Value
		params := strings.Split(paramVal, "$-:")
		if len(params) >= 2 {
			fmt.Println("para", params[0], "am", params[1])
			apartmentParam.Value = params[0]
			apartmentParam.News_Image = &params[1]
		} else if len(params) >= 1 {

			fmt.Println("para", params[0])
			apartmentParam.Value = params[0]

		}
	}
	//row := configRepo.db.QueryRow("select Param_Value as Id,PARAM_SUB as Name  from BPMS_APARTMENT_PARAM where Apartment_Id = ? and Param_Key = 'Announcement' and Validity = 'Yes'", apartmentid)
	//err = row.Scan(&announcement, &announcement_header)
	if err != nil {
		return nil, err
	} else {
		return apartmentlist, nil
	}
}
func (configRepo *ConfigRepository) FetchApartmentBasicInfo(apartmentid int) (basicinfo []entity.BasicInfo, err error) {
	err = configRepo.db.Select(&basicinfo, "SELECT Param_Key, Param_Value, PARAM_SUB FROM BPMS_APARTMENT_PARAM WHERE Apartment_Id = ? AND (Param_Key = 'Social Media' OR Param_Key LIKE 'FAQ_%' OR Param_Key = 'Support' OR Param_Key = 'Is_Maintenance' OR Param_Key = 'Tax_Percentage') AND Validity = 'Yes' ", apartmentid)
	if err != nil {
		log.Println("ConfigRepository: FetchApartmentBasicInfo: Fetchingapartmentinof: Input: ", apartmentid, " Error: ", err.Error())
		return nil, err
	} else {
		return basicinfo, nil
	}
}

func (configRepo *ConfigRepository) FetchStates(languagecode int) (mappedstates []entity.ConfigurationDropdown, err error) {
	err = configRepo.db.Select(&mappedstates, "SELECT strans.State_Name as Name, strans.State_Id as Id  from BPMS_STATE_TRANSLATIONS strans INNER JOIN BPMS_STATE s on s.State_Id = strans.State_Id where strans.Language_Code = ?", languagecode)
	if err != nil {
		log.Println("ConfigRepository: FetchStates: Fetchingstates: Input: ", languagecode, " Error: ", err.Error())
		return nil, err
	} else {
		return mappedstates, nil
	}
}

func (configRepo *ConfigRepository) FetchCities(stateid int, languagecode int) (mappedcities []entity.ConfigurationDropdown, err error) {
	err = configRepo.db.Select(&mappedcities, "SELECT ctrans.City_Name as Name, ctrans.City_Id as Id from BPMS_CITY_TRANSLATIONS ctrans INNER JOIN BPMS_CITY c on c.City_Id = ctrans.City_Id where ctrans.Language_Code = ? and c.State_Id = ?", languagecode, stateid)
	if err != nil {
		log.Println("ConfigRepository: FetchCities: Fetchingcities: Input: ", stateid, " Error: ", err.Error())
		return nil, err
	} else {
		return mappedcities, nil
	}
}

func (configRepo *ConfigRepository) FetchApartments(stateid int, cityid int, languagecode int) (mappedapartments []entity.ConfigurationDropdown, err error) {
	err = configRepo.db.Select(&mappedapartments, "SELECT atrans.Apartment_Name as Name, atrans.Apartment_Id as Id from BPMS_APARTMENT_TRANSLATIONS atrans INNER JOIN BPMS_APARTMENT a on a.Apartment_Id = atrans.Apartment_Id where atrans.Language_Code = ? and a.State = ? and a.City = ?", languagecode, stateid, cityid)
	if err != nil {
		log.Println("ConfigRepository: FetchApartments: Fetchingapartments: Input: ", cityid, " Error: ", err.Error())
		return nil, err
	} else {
		return mappedapartments, nil
	}
}

func (configRepo *ConfigRepository) AddState(tx *sqlx.Tx, input entity.AddStateInput) ([]entity.State, error) {

	stateQuery := "INSERT INTO `BPMS_STATE` values()"

	insert, err := tx.Exec(stateQuery)
	if err != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddState: addingstateid: Error: ", err.Error())
		return nil, err
	}

	last_stateid, inserr := insert.LastInsertId()
	if inserr != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddState: gettinglastinsertid: Error: ", inserr.Error())
		return nil, err
	}

	transQuery := "INSERT INTO `BPMS_STATE_TRANSLATIONS`( `State_Id`,`Language_Code`,`State_Name`) values(?,?,?)"
	_, err1 := tx.Exec(transQuery, last_stateid, input.Language_Code, input.State_Name)
	if err1 != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddState: insertingstate: Input: ", input.State_Name, " Error: ", err1.Error())
		return nil, err1
	}

	var allstates []entity.State
	getStates := tx.Select(&allstates, "SELECT strans.State_Name, strans.State_Id from BPMS_STATE_TRANSLATIONS strans INNER JOIN BPMS_STATE s on s.State_Id = strans.State_Id where strans.Language_Code =? ", input.Language_Code)
	if getStates != nil {
		log.Println("ConfigRepository: AddState: Fetchingstates: Error: ", getStates.Error())
		return nil, getStates
	} else {
		return allstates, nil
	}
}
func (configRepo *ConfigRepository) AddCity(tx *sqlx.Tx, input entity.AddCityInput) ([]entity.City, error) {

	cityQuery := "INSERT INTO `BPMS_CITY` values()"

	insert, err := tx.Exec(cityQuery)
	if err != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddCity: Addingcityid: Error: ", err.Error())
		return nil, err
	}

	last_cityid, inserr := insert.LastInsertId()
	if inserr != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddCity: gettingLastInsertId: Error: ", inserr.Error())
		return nil, inserr
	}
	insQuery := "UPDATE `BPMS_CITY` SET `State_Id`=? WHERE City_Id=?"
	res, err1 := tx.Exec(insQuery, input.State_Id, last_cityid)
	if err1 != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddCity: updateState: Input:", last_cityid, " Error: ", err1.Error())
		return nil, err1
	}
	rowsAffected, _ := res.RowsAffected()
	if rowsAffected == 0 {
		tx.Rollback()
		log.Println("ConfigRepository: AddCity: rowsaffected :input :cityid :", last_cityid)
		return nil, errors.New(dto.ErrorrowsAffectedAddCity)
	}

	transQuery := "INSERT INTO `BPMS_CITY_TRANSLATIONS`( `City_Id`,`Language_Code`,`City_Name`) values(?,?,?)"
	_, err2 := tx.Exec(transQuery, last_cityid, input.Language_Code, input.City_Name)
	if err2 != nil {
		tx.Rollback()
		log.Println("ConfigRepository: AddCity: Addingcity: Input:", input.City_Name, "Error: ", err2.Error())
		return nil, err2
	}

	var allcities []entity.City
	getCities := tx.Select(&allcities, "SELECT ctrans.City_Name, ctrans.City_Id from BPMS_CITY_TRANSLATIONS ctrans INNER JOIN BPMS_CITY c on c.City_Id = ctrans.City_Id where ctrans.Language_Code = ?", input.Language_Code)
	if getCities != nil {
		log.Println("ConfigRepository: AddCity: fetchingcity: Error: ", getCities.Error())
		return nil, getCities
	} else {

		return allcities, nil
	}
}

func (configRepo *ConfigRepository) AddFeedback(input entity.AddFeedback) (srqInfo []entity.ServiceRequest, err error) {
	query := "INSERT INTO `BPMS_FEEDBACK`(`User_Id`, `Feedback`, `Rating`, `FeedBack_Type`, `Service_Id`, `Technician_Id`) VALUES (:User_Id,:Feedback,:Rating,:Feedback_Type,:Service_Id,:Technician_Id)"
	_, errv := configRepo.db.NamedExec(query, input)
	if errv != nil {
		log.Println("ConfigRepository: AddFeedback: Input: ", input.Technician_ID, input.Service_ID, "Error ", errv.Error())
		return nil, errv
	} else {

		err := configRepo.db.Select(&srqInfo, "SELECT bsr.Srq_Id, bsr.Srq_Key, bsr.Srq_Service, bst.Service_Name, bs.Service_Description, bs.Service_Image_Large, bs.Service_Image_Small, bsr.Request_Type, bsr.Parent_Request, bsr.Child_Request, bsr.Srq_Apartment_Id, bat.Apartment_Name,ba.Apartment_Image, bsat.State_Name, bct.City_Name, bsr.User_Id, bu.User_Name, bu.User_Mobno, bu.User_Image, buam.Block, buam.Door_No, bsr.Adm_Id, be.Emp_Name AS Admin_Name, be.Emp_Mobno AS Admin_Mobno, bsr.Admin_Comments, bsr.Technician_Id, bet.Emp_Name As Technician_Name, bet.Emp_Mobno AS Technician_Mobno, bet.Emp_Image AS Tech_Image, bsr.Technician_Comments, bpm.Promocode_Id, bp.Name, bp.Discount, bp.Max_Discount, bsr.Srq_Service_Cost,bsr.Srq_Material_Cost, bsr.Srq_Labour_Cost, bsr.Srq_Tax_Percentage, bsr.Srq_Tax, bsr.Srq_Discount_Amount, bsr.Srq_Other_Cost, bsr.Srq_Special_Discount, bsr.Srq_Final_Cost, bsr.Quote_Service_Cost,bsr.Quote_Labour_Cost, bsr.Quote_Material_Cost, bsr.Quote_Tax, bsr.Quote_Other_Cost, bsr.Srq_Quote_Cost, bsr.Srq_Stage_1, bsr.Srq_Stage_1_ts, bsr.Srq_Stage_2, bsr.Srq_Stage_2_ts, bsr.Srq_Stage_3, bsr.Srq_Stage_3_ts, bsr.Srq_Stage_4, bsr.Srq_Stage_4_ts, bsr.Srq_Stage_5, bsr.Srq_Stage_5_ts, bsr.Srq_Stage_6, bsr.Srq_Stage_6_ts, bsr.Srq_Stage_7, bsr.Srq_Stage_7_ts, bsr.Srq_Stage_8, bsr.Srq_Stage_8_ts, bsr.Srq_Stage_9, bsr.Srq_Stage_9_ts, bsr.Is_Happycode_Validated, bsr.Srq_Payment_Mode, bsr.Srq_Payment_Status, bsr.Srq_Quote_Status, bsr.Srq_Problem_Desc, bsr.Srq_Problem_Category,bpct.Problem_Name, bsr.Srq_Category_others,GROUP_CONCAT(bsi.Image_Path) As SRQ_Image, bsr.Srq_Visit_Timeslot, bsr.Srq_Instructions, bsr.Created_Date, bf.Feedback, bf.Rating FROM BPMS_SERVICE_REQUEST bsr INNER JOIN BPMS_SERVICE_TRANSLATIONS bst ON bsr.Srq_Service = bst.Service_Id And bst.Language_Code =? INNER JOIN BPMS_SERVICE bs ON bsr.Srq_Service = bs.Service_Id LEFT JOIN BPMS_APARTMENT ba ON bsr.Srq_Apartment_Id = ba.Apartment_Id INNER JOIN BPMS_APARTMENT_TRANSLATIONS bat ON bsr.Srq_Apartment_Id = bat.Apartment_Id AND bat.Language_Code =? INNER JOIN BPMS_STATE_TRANSLATIONS bsat ON ba.State = bsat.State_Id And bsat.Language_Code =? INNER JOIN BPMS_CITY bc ON ba.State = bc.State_Id INNER JOIN BPMS_CITY_TRANSLATIONS bct ON bc.City_Id = bct.City_Id AND bct.Language_Code =? INNER JOIN BPMS_USER bu ON bsr.User_Id = bu.User_Id LEFT JOIN BPMS_PROMO_MAPPING bpm ON bsr.Srq_Id = bpm.Srq_Id LEFT JOIN BPMS_PROMOCODE bp ON bpm.Promocode_Id = bp.Promocode_Id LEFT JOIN BPMS_PROBLEM_CATEGORIES_TRANSLATIONS bpct ON bsr.Srq_Problem_Category = bpct.Problem_Id AND bpct.Language_Code =? LEFT JOIN BPMS_FEEDBACK bf ON bsr.Srq_Id = bf.Service_Id LEFT JOIN BPMS_EMPLOYEE be ON bsr.Adm_Id = be.Emp_Id LEFT JOIN BPMS_EMPLOYEE bet ON bsr.Technician_Id = bet.Emp_Id LEFT JOIN BPMS_SRQ_IMAGE bsi ON bsr.Srq_Id = bsi.Srq_Id INNER JOIN BPMS_USER_APARTMENT_MAPPING buam ON bsr.Srq_Apartment_Id = buam.Apartment_Id AND bsr.User_Id = buam.User_Id WHERE bsr.Srq_Id=?", input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Language_Code, input.Service_ID)
		if err != nil {
			log.Println("ConfigRepository: AddFeedback: tech_id: ", input.Technician_ID, " : srq_id: ", input.Service_ID, "Error ", err.Error())
			return nil, err
		} else {
			log.Println("ConfigRepository: AddFeedback: tech_id: ", input.Technician_ID, " : srq_id: ", input.Service_ID)
			return srqInfo, nil
		}

	}
}

func (configRepo *ConfigRepository) DashboardSuperAdmin(input entity.SuperAdminDashboardInput) (*entity.SuperAdminDashboard, error) {
	dashboard := entity.SuperAdminDashboard{}

	getSrqCompleted := configRepo.db.Get(&dashboard.Services_Completed, "SELECT COUNT(Srq_Id) FROM BPMS_SERVICE_REQUEST WHERE Srq_Stage_7 = 1 AND  Srq_Stage_8 != 1")
	if getSrqCompleted != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: servicecompletedcount: Error: ", getSrqCompleted.Error())
		return nil, getSrqCompleted
	}

	getSrqPending := configRepo.db.Get(&dashboard.Services_Pending, "SELECT COUNT(Srq_Id) FROM BPMS_SERVICE_REQUEST WHERE Srq_Stage_7 != 1 AND Srq_Stage_8 != 1")
	if getSrqPending != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: servicependingcount: Error: ", getSrqPending.Error())
		return nil, getSrqPending
	}

	getTechnicianCount := configRepo.db.Get(&dashboard.Technician, "SELECT COUNT(Emp_Id) FROM BPMS_ROLE_MAPPING WHERE Role_Id = 1")
	if getTechnicianCount != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: techniciancount: Error: ", getTechnicianCount.Error())
		return nil, getTechnicianCount
	}

	getUserCount := configRepo.db.Get(&dashboard.Total_User, "SELECT COUNT(User_Id) FROM BPMS_USER WHERE Account_Status != 'Rejected'")
	if getUserCount != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: totaluser: Error: ", getUserCount.Error())
		return nil, getUserCount
	}
	//1-available
	getEmpWorking := configRepo.db.Get(&dashboard.Emp_Working, "SELECT COUNT(Emp_Id) FROM BPMS_EMPLOYEE WHERE Is_Available =1")
	if getEmpWorking != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: employeeworkingtoday: Error: ", getEmpWorking.Error())
		return nil, getEmpWorking
	}
	//0-not available
	getEmpLeave := configRepo.db.Get(&dashboard.Emp_Leave, "SELECT COUNT(Emp_Id) FROM BPMS_EMPLOYEE WHERE Is_Available =0")
	if getEmpLeave != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: employeeleavetoday: Error: ", getEmpLeave.Error())
		return nil, getEmpLeave
	}

	apartmentUser := []entity.ApartmentUser{}
	getApartmentUsr := configRepo.db.Select(&apartmentUser, "SELECT bat.Apartment_Name, COUNT(bu.User_Id) AS User_Count FROM BPMS_USER bu LEFT JOIN BPMS_APARTMENT_TRANSLATIONS bat ON bu.Apartment_Id = bat.Apartment_Id AND bat.Language_Code = ? WHERE bat.Apartment_Name IS NOT NULL GROUP BY bu.Apartment_Id", input.Language_Code)

	if getApartmentUsr != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: apartmentwiseuser: Error: ", getApartmentUsr.Error())
		return nil, getApartmentUsr
	} else {
		dashboard.Apartment_Users = apartmentUser
	}

	monthCountSrq := []entity.MonthSrqCount{}
	getSrqMonthCount := configRepo.db.Select(&monthCountSrq, "SELECT COUNT(Srq_Id) AS Srq_Count, MONTH(Created_Date) AS Month, YEAR(Created_Date) as Year FROM BPMS_SERVICE_REQUEST WHERE Created_Date > DATE_SUB(curdate(), INTERVAL 6 MONTH) GROUP BY Month order by Month")
	if getSrqMonthCount != nil {
		log.Println("ConfigRepository: DashboardSuperAdmin: monthwisesrqcount: Error: ", getSrqMonthCount.Error())
		return nil, getSrqMonthCount
	} else {
		dashboard.Month_Srq = monthCountSrq
	}

	for i := range monthCountSrq {
		service_wise := []entity.ServiceWise{}
		getServiceWiseCount := configRepo.db.Select(&service_wise, "SELECT bst.Service_Name, COUNT(bsr.Srq_Id) AS Srq_Count, MONTH(bsr.Created_Date) AS Month, YEAR(bsr.Created_Date) AS Year FROM BPMS_SERVICE_REQUEST bsr LEFT JOIN BPMS_SERVICE_TRANSLATIONS bst ON bsr.Srq_Service = bst.Service_Id AND bst.Language_Code = ? WHERE bsr.Created_Date > DATE_SUB(curdate(), INTERVAL 6 MONTH) AND MONTH(bsr.Created_Date) = ? GROUP BY Month, Srq_Service", input.Language_Code, monthCountSrq[i].Month)

		if getServiceWiseCount != nil {

			log.Print("ConfigRepository: DashboardSuperAdmin: servicewisesrqcount: Error: ", getServiceWiseCount.Error())
			return nil, getServiceWiseCount
		} else {
			monthCountSrq[i].Service_Wise = service_wise
		}
	}

	return &dashboard, nil
}

func (configRepo *ConfigRepository) UpdateApartmentParam(tx *sqlx.Tx, Apartment_Id int, Param_Key string, Param_Value string, PARAM_SUB string, Validity string, Action string, newsImage string) (_ []entity.UpdateApartmentParamResp, err error) {
	if Param_Key == "Announcement" {
		if Action == "I" {

			param_value := fmt.Sprint(Param_Value, "$-:", newsImage)

			query := "INSERT INTO `BPMS_APARTMENT_PARAM`(`Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity`) VALUES (?,?,?,?,'Yes')"
			res, errv := tx.Exec(query, Apartment_Id, Param_Key, param_value, PARAM_SUB)
			log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, PARAM_SUB, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoInsert)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageDuplicateInsertion)
			}
		} else if Action == "U" {
			var value, msgVal, newsimg, param_value string
			selquery := "SELECT Param_Value FROM BPMS_APARTMENT_PARAM WHERE Apartment_Id=? AND Param_Key=? AND PARAM_SUB=? "
			errVal := tx.Get(&value, selquery, Apartment_Id, Param_Key, PARAM_SUB)
			if errVal != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: apartmentid:", Apartment_Id, "param sub", PARAM_SUB, "Error", errVal.Error())
				return nil, errVal
			}
			params := strings.Split(value, "$-:")
			if len(params) >= 1 {
				msgVal = params[0]
			}
			if len(params) >= 2 {
				newsimg = params[1]
			}

			if Param_Value != "" && newsImage != "" {
				param_value = fmt.Sprint(Param_Value, "$-:", newsImage)
				fmt.Println(param_value)
			} else if Param_Value != "" && newsImage == "" {
				param_value = fmt.Sprint(Param_Value, "$-:", newsimg)
				fmt.Println(param_value)
			} else if param_value == "" && newsImage != "" {
				param_value = fmt.Sprint(msgVal, "$-:", newsImage)
				fmt.Println(param_value)
			}

			query := "UPDATE `BPMS_APARTMENT_PARAM` SET `Param_Value`=? WHERE `Apartment_Id`=? AND `Param_Key`=? AND `PARAM_SUB`=?"
			res, errv := tx.Exec(query, param_value, Apartment_Id, Param_Key, PARAM_SUB)
			log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Param_Value, Apartment_Id, Param_Key, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, PARAM_SUB, "Error", errv.Error())
				return nil, errv
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
		} else if Action == "D" {
			query := "DELETE FROM `BPMS_APARTMENT_PARAM` WHERE `Apartment_Id`=? AND `Param_Key`=? AND `PARAM_SUB`=?"
			res, errv := tx.Exec(query, Apartment_Id, Param_Key, PARAM_SUB)
			log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, PARAM_SUB, "Error ", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoDelete)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoDelete)
			}
		} else {
			return nil, errors.New(dto.ErrorMessageUpdateApartmentParamNoInput)
		}
	} else if Param_Key == "Social Media" {
		if Action == "I" {
			query := "INSERT INTO `BPMS_APARTMENT_PARAM`(`Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity`) VALUES (?,?,?,?,'Yes')"
			res, errv := tx.Exec(query, Apartment_Id, Param_Key, Param_Value, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, PARAM_SUB, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoInsert)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageDuplicateInsertion)
			}
		} else if Action == "U" {
			query := "UPDATE `BPMS_APARTMENT_PARAM` SET `Param_Value`=? WHERE `Apartment_Id`=? AND `Param_Key`=? AND `PARAM_SUB`=?"
			res, errv := tx.Exec(query, Param_Value, Apartment_Id, Param_Key, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Param_Value, Apartment_Id, Param_Key, PARAM_SUB, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
		} else if Action == "D" {
			query := "DELETE FROM `BPMS_APARTMENT_PARAM` WHERE `Apartment_Id`=? AND `Param_Key`=? AND `PARAM_SUB`=?"
			res, errv := tx.Exec(query, Apartment_Id, Param_Key, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, PARAM_SUB, "Error ", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoDelete)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoDelete)
			}
		} else {
			return nil, errors.New(dto.ErrorMessageUpdateApartmentParamNoInput)
		}
	} else if Param_Key == "Support" {
		if Action == "I" {
			insertQuery := "INSERT INTO `BPMS_APARTMENT_PARAM`(`Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity`) SELECT ?,?,?,?,'Yes' FROM DUAL WHERE NOT EXISTS (SELECT * FROM `BPMS_APARTMENT_PARAM` WHERE `Apartment_Id` = ? AND `Param_Key` = ?)"

			res, err := tx.Exec(insertQuery, Apartment_Id, Param_Key, Param_Value, PARAM_SUB, Apartment_Id, Param_Key)
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, "Error", err.Error())
				return nil, errors.New(dto.ErrorMessageNoInsert)
			}

			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageDuplicateInsertion)
			}
		} else if Action == "U" {
			query := "UPDATE `BPMS_APARTMENT_PARAM` SET `Param_Value`=? WHERE `Apartment_Id`=? AND `Param_Key`=?"
			res, errv := tx.Exec(query, Param_Value, Apartment_Id, Param_Key)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Param_Value, Apartment_Id, Param_Key, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
		}
	} else if Param_Key == "Is_Maintenance" {
		if Action == "I" {
			insertQuery := "INSERT INTO `BPMS_APARTMENT_PARAM`(`Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity`) SELECT ?,?,?,?,'Yes' FROM DUAL WHERE NOT EXISTS (SELECT * FROM `BPMS_APARTMENT_PARAM` WHERE `Apartment_Id` = ? AND `Param_Key` = ?)"

			res, err := tx.Exec(insertQuery, Apartment_Id, Param_Key, Param_Value, PARAM_SUB, Apartment_Id, Param_Key)
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, "Error", err.Error())
				return nil, errors.New(dto.ErrorMessageNoInsert)
			}

			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageDuplicateInsertion)
			}
		} else if Action == "U" {
			query := "UPDATE `BPMS_APARTMENT_PARAM` SET `Param_Value`=? WHERE `Apartment_Id`=? AND `Param_Key`=?"
			res, errv := tx.Exec(query, Param_Value, Apartment_Id, Param_Key)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Param_Value, Apartment_Id, Param_Key, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
		} else {
			return nil, errors.New(dto.ErrorMessageUpdateApartmentParamNoInput)
		}
	} else if strings.HasPrefix(Param_Key, "FAQ_") {
		if Action == "I" {
			query := "INSERT INTO `BPMS_APARTMENT_PARAM`(`Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity`) VALUES (?,?,?,?,'Yes')"
			res, errv := tx.Exec(query, Apartment_Id, Param_Key, Param_Value, PARAM_SUB)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value, PARAM_SUB, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoInsert)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageDuplicateInsertion)
			}
		} else if Action == "U" {
			query := "UPDATE `BPMS_APARTMENT_PARAM` SET `Param_Value` = IFNULL(?, `Param_Value`), `PARAM_SUB` = IFNULL(?, `PARAM_SUB`) WHERE `Apartment_Id`= ? AND `Param_Key`= ?"
			res, errv := tx.Exec(query, NewNullString(Param_Value), NewNullString(PARAM_SUB), Apartment_Id, Param_Key)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Param_Value, Apartment_Id, Param_Key, PARAM_SUB, "Error", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoUpdate)
			}
		} else if Action == "D" {
			query := "DELETE FROM `BPMS_APARTMENT_PARAM` WHERE `Apartment_Id`=? AND `Param_Key`=?"
			res, errv := tx.Exec(query, Apartment_Id, Param_Key)
			if errv != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, "Error ", errv.Error())
				return nil, errors.New(dto.ErrorMessageNoDelete)
			}
			rowsAffected, err := res.RowsAffected()
			if err != nil {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, err
			}

			if rowsAffected == 0 {
				tx.Rollback()
				log.Println("ConfigRepository: UpdateApartmentParam: Input: ", Apartment_Id, Param_Key, Param_Value)
				return nil, errors.New(dto.ErrorMessageNoDelete)
			}
		} else {
			return nil, errors.New(dto.ErrorMessageUpdateApartmentParamNoInput)
		}
	}
	// apartmentParamList := []entity.UpdateApartmentParamResp{}
	// errs := tx.Select(&apartmentParamList, "SELECT `Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity` FROM `BPMS_APARTMENT_PARAM` where `Apartment_Id`=?", Apartment_Id)

	// for _, apartmentParam := range apartmentParamList {
	// 	if *apartmentParam.Param_Key == "Announcement" {
	// 		fmt.Println(apartmentParamList[0].Param_Value)
	// 		paramVal := apartmentParamList[0].Param_Value
	// 		params := strings.Split(paramVal, "$-:")
	// 		fmt.Println("para ", params[0], "am", params[1])
	// 		apartmentParam.Param_Value = params[0]
	// 		apartmentParam.News_Image = &params[1]

	// 	}
	// }

	apartmentParamList := []entity.UpdateApartmentParamResp{}
	errs := tx.Select(&apartmentParamList, "SELECT `Apartment_Id`, `Param_Key`, `Param_Value`, `PARAM_SUB`, `Validity` FROM `BPMS_APARTMENT_PARAM` where `Apartment_Id`=?", Apartment_Id)

	for i := range apartmentParamList {
		apartmentParam := &apartmentParamList[i] // Access the current item by reference

		if apartmentParam.Param_Key != nil && *apartmentParam.Param_Key == "Announcement" {
			fmt.Println(apartmentParam.Param_Value)
			paramVal := apartmentParam.Param_Value
			params := strings.Split(paramVal, "$-:")
			if len(params) >= 2 {
				fmt.Println("para", params[0], "am", params[1])
				apartmentParam.Param_Value = params[0]
				apartmentParam.News_Image = &params[1]
			} else if len(params) >= 1 {

				fmt.Println("para", params[0])
				apartmentParam.Param_Value = params[0]

			}
		}
	}

	if errs != nil {
		log.Println("ConfigRepository: UpdateApartmentParam: Error ", errs.Error())
		return nil, errs
	} else {
		return apartmentParamList, nil
	}
}
