package domain

import (
	"nearle/db"
	"nearle/models"
	"nearle/utils"
	"strconv"
	"strings"
)

func Getuserbyid(uid int) models.UserInfo {

	var user models.UserInfo

	q1 := `SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno,
			a.firstname,a.lastname,concat(a.firstname,' ',a.lastname) as fullname,a.password,a.address,a.suburb,a.city,a.state,a.postcode,
			a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.tenantid,a.shiftid,
			a.applocationid,b.locationname as applocation,b.latitude as applatitude,b.longitude as applongitude, b.radius as appradius , concat(c.starttime, ' - ', c.endtime) as shiftname                      
			from app_users a
			INNER JOIN app_location b on a.applocationid=b.applocationid
			LEFT JOIN ridershifts c ON a.shiftid = c.shiftid
			WHERE a.userid= ` + strconv.Itoa(uid)

	// print(q1)

	db.DB.Raw(q1).Find(&user)

	return user

}

func GetAllUsers(
	roleID, configID, tenantID, pageno, pagesize int,
	status, keyword string,
) []models.UserInfo {

	var users []models.UserInfo
	var params []interface{}
	var queryBuilder strings.Builder

	offset := (pageno - 1) * pagesize

	queryBuilder.WriteString(`
	SELECT 
		a.userid, a.authname, a.email, a.configid, a.roleid, a.authmode, a.contactno,
		a.firstname, a.lastname, CONCAT(a.firstname, ' ', a.lastname) AS fullname,
		a.address, a.suburb, a.city, a.state, a.postcode,
		a.userfcmtoken, a.pin, a.deviceid, a.devicetype,
		a.tenantid, a.status, a.shiftid, a.latitude, a.longitude,d.rolename,

		STRING_AGG(
			CONCAT(
				ac.applocationid, '::',
				al.locationname, '::',
				ac.status
			)::text, ','
		) AS applocations_raw

	FROM app_users a
	LEFT JOIN app_locationconfig ac ON ac.userid = a.userid
	LEFT JOIN app_location al ON al.applocationid = ac.applocationid
	LEFT JOIN app_roles d ON a.roleid = d.roleid
	WHERE 1=1
	`)

	if roleID != 0 {
		queryBuilder.WriteString(" AND a.roleid = ?")
		params = append(params, roleID)
	}
	if configID != 0 {
		queryBuilder.WriteString(" AND a.configid = ?")
		params = append(params, configID)
	}
	if tenantID != 0 {
		queryBuilder.WriteString(" AND a.tenantid = ?")
		params = append(params, tenantID)
	}
	if status != "" {
		queryBuilder.WriteString(" AND a.status = ?")
		params = append(params, status)
	}
	if keyword != "" {
		queryBuilder.WriteString(`
		AND (
			LOWER(a.firstname) LIKE ? OR 
			LOWER(a.contactno) LIKE ? OR 
			LOWER(a.suburb) LIKE ?
		)
		`)
		search := "%" + strings.ToLower(keyword) + "%"
		params = append(params, search, search, search)
	}

	queryBuilder.WriteString(`
	GROUP BY a.userid, d.rolename
	ORDER BY a.userid DESC
	LIMIT ? OFFSET ?
	`)

	params = append(params, pagesize, offset)

	// ---------- TEMP STRUCT ----------
	type tempUser struct {
		models.UserInfo
		ApplocationsRaw string `gorm:"column:applocations_raw"`
	}

	var temp []tempUser
	db.DB.Raw(queryBuilder.String(), params...).Scan(&temp)

	// ---------- MAP RESULT ----------
	for _, t := range temp {

		t.UserInfo.Applocations = []models.AppLocation{}

		if t.ApplocationsRaw != "" {
			items := strings.Split(t.ApplocationsRaw, ",")
			for _, item := range items {
				parts := strings.Split(item, "::")
				if len(parts) == 3 {
					id, err := strconv.Atoi(parts[0])
					if err == nil {
						t.UserInfo.Applocations = append(
							t.UserInfo.Applocations,
							models.AppLocation{
								Applocationid:   id,
								Applocationname: parts[1],
								Status:          parts[2],
							},
						)
					}
				}
			}
		}

		users = append(users, t.UserInfo)
	}

	return users
}

func GetTenantUserbyId(uid int) models.TenantUserInfo {

	var user models.TenantUserInfo

	q1 := `SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno,
			a.firstname,a.lastname,concat(a.firstname,' ',a.lastname) as fullname,
			a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.tenantid,a.locationid,a.applocationid,
			b.partnerid,b.moduleid,b.categoryid as categoryid,b.subcategoryid as subcategoryid,
			b.applocationid,b.tenantname,b.address as tenantaddress,b.state as tenantstate,b.city as tenantcity,
			b.postcode as tenantpostcode,b.latitude as tenantlat,b.longitude as tenantlong,c.locationname AS applocation,
			c.latitude as applatitude,c.longitude as applongitude,c.radius as appradius, d.categoryname, e.locationname
			from app_users a 
			LEFT JOIN tenants b ON a.tenantid=b.tenantid
			INNER JOIN app_location c on c.applocationid=b.applocationid
			LEFT JOIN app_category d ON b.categoryid=d.categoryid
			LEFT JOIN tenantlocations e ON a.locationid=e.locationid
			WHERE a.userid= ` + strconv.Itoa(uid)

	// print(q1)

	db.DB.Raw(q1).Find(&user)

	return user

}

func GetTenantId(uid int, contactno string) int {

	var tid int
	var q1 string

	if contactno != "" {

		q1 = `select tenantid from tenants WHERE primarycontact='` + contactno + `'`

	} else {

		q1 = `select primarycontact from tenants WHERE primarycontact='` + contactno + `'`

	}

	db.DB.Raw(q1).Find(&tid)

	return tid

}

func Getuserbyno(cno string) models.UserInfo {

	var user models.UserInfo

	q1 := `SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno,
	a.firstname,a.lastname,concat(a.firstname,' ',a.lastname) as fullname,
	a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.tenantid,a.locationid,
	b.partnerid,b.moduleid,b.categoryid as categoryid,b.subcategoryid as subcategoryid,
	b.applocationid,b.tenantname,b.address as tenantaddress,b.state as tenantstate,b.city as tenantcity,
	b.postcode as tenantpostcode,b.latitude as tenantlat,b.longitude as tenantlong
	from app_users a 
	LEFT JOIN tenants b ON a.tenantid=b.tenantid
	WHERE  a.contactno= '` + cno + `'`

	db.DB.Raw(q1).Find(&user)

	return user

}

func GetPartnerUserbyid(uid int) models.UserInfo {

	var user models.UserInfo

	q1 := `SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno,a.firstname,a.lastname,concat(a.firstname,' ',a.lastname) as fullname,
			a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.tenantid,a.applocationid,a.partnerid, 
			b.locationname AS applocation,b.latitude AS applatitude,b.longitude AS applongitude,b.radius AS appradius, b.deliveryradius, c.partnername
			from app_users a 
			INNER JOIN app_location b ON a.applocationid=b.applocationid
			LEFT JOIN partnerinfo c ON a.partnerid=c.partnerid 		
			WHERE  a.userid= ` + strconv.Itoa(uid)

	db.DB.Raw(q1).Find(&user)

	return user

}

func GetRiderUserbyid(uid int) models.RiderInfo {

	var user models.RiderInfo

	q1 := `SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno, a.address,a.state,a.city,a.postcode,a.latitude,a.longitude,
	 a.firstname,a.lastname,concat(a.firstname,' ',a.lastname) as username,a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.partnerid,a.applocationid, a.tenantid, a.locationid, a.shiftid,
	 b.starttime, b.endtime, b.shifthours, b.firstmilecharge, b.fuelcharge, c.logid, d.onduty, e.logseconds, e.deliveryradius
	from app_users a 
	LEFT JOIN ridershifts b ON a.shiftid=b.shiftid
	LEFT JOIN riderlogs c ON a.userid=c.userid
	LEFT JOIN app_userpools d on a.userid=d.userid
	LEFT JOIN app_location e on a.applocationid=e.applocationid
	WHERE  a.userid= ` + strconv.Itoa(uid)

	db.DB.Raw(q1).Find(&user)

	utils.Logger.Debugf("Query: %s", q1)

	return user

}

func GetAdminUserbyid(uid int) models.User {

	var user models.User

	q1 := `SELECT a.*,b.rolename from app_users a LEFT JOIN app_roles b ON a.roleid=b.roleid where  a.userid= ` + strconv.Itoa(uid)

	db.DB.Raw(q1).Find(&user)

	return user

}

func UpdatUser(user models.User) {

	db.DB.Table("app_users").Where("userid=?", user.Userid).Updates(&user)

	if user.Tenantid != 0 {

		db.DB.Table("tenants").Where("tenantid=?", user.Tenantid).Update("tenanttoken", user.Userfcmtoken)

	}

}

func GetUserIDByContact(contact string, configID int) int {
	var uid int
	query := `SELECT userid FROM app_users WHERE contactno = ? AND configid = ?`
	db.DB.Raw(query, contact, configID).Scan(&uid)
	return uid
}

func GetUserIDByAuth(authname string, configID int) int {
	var uid int
	query := `SELECT userid FROM app_users WHERE authname = ? AND configid = ?`
	db.DB.Raw(query, authname, configID).Scan(&uid)
	return uid
}
func GetUserStatus(userid int) string {
	var status string
	db.DB.Raw("SELECT status FROM app_users WHERE userid = ?", userid).Scan(&status)
	return status
}

func GetStoredPin(uid int) int {
	var pin int
	db.DB.Raw(`SELECT COALESCE(pin, 0) FROM app_users WHERE userid = ?`, uid).Scan(&pin)
	return pin
}

func GetAuthMode(uid int) int {
	var mode int
	db.DB.Raw(`SELECT COALESCE(authmode, 1) FROM app_users WHERE userid = ?`, uid).Scan(&mode)
	return mode
}

func UpdateUserFcmToken(uid int, token string) error {
	query := `UPDATE app_users SET userfcmtoken = ? WHERE userid = ?`
	return db.DB.Exec(query, token, uid).Error
}

// Check if device already exists for user
func IsDeviceLinked(userid int, deviceid string) bool {
	var count int64
	db.DB.Table("user_devices").
		Where("userid = ? AND deviceid = ?", userid, deviceid).
		Count(&count)
	return count > 0
}

func GetStoredDeviceID(uid int) (string, error) {
	var deviceID string
	query := `SELECT deviceid FROM app_users WHERE userid = ? LIMIT 1`

	result := db.DB.Raw(query, uid).Scan(&deviceID)

	if result.Error != nil {
		utils.Error("Error fetching deviceid", "error", result.Error)
		return "", result.Error
	}

	// If nothing found
	if result.RowsAffected == 0 {
		return "", nil
	}

	return deviceID, nil
}

func CheckUserExists(contactno, email string, configid int) (bool, error) {

	var count int64

	err := db.DB.Table("app_users").
		Where("configid = ?", configid).
		Where(
			db.DB.
				Where("contactno = ? AND ? != ''", contactno, contactno).
				Or("email = ? AND ? != ''", email, email),
		).
		Count(&count).Error

	if err != nil {
		return false, err
	}

	return count > 0, nil
}

func GetAllUsersv2(roleID, configID, tenantID, pageno, pagesize int, keyword string) []models.UserInfov2 {

	var users []models.UserInfov2
	var params []interface{}
	var queryBuilder strings.Builder

	offset := (pageno - 1) * pagesize

	queryBuilder.WriteString(`
	SELECT a.userid,a.authname,a.email,a.configid,a.roleid,a.authmode,a.contactno,a.firstname,
		a.lastname,CONCAT(a.firstname, ' ', a.lastname) AS fullname,a.address,a.suburb,
		a.city,a.state,a.postcode,a.userfcmtoken,a.pin,a.deviceid,a.devicetype,a.tenantid,
		a.status,a.shiftid,

		STRING_AGG(DISTINCT alc.applocationid::text, ',') AS applocationids_raw,

		STRING_AGG(DISTINCT d.locationname, ',') AS applocationnames_raw,

		CONCAT(c.starttime, ' - ', c.endtime) AS shiftname

	FROM app_users a
	LEFT JOIN app_locationconfig alc
	       ON alc.userid = a.userid
	      AND alc.configid = a.configid
	      AND alc.status = 'Active'
	LEFT JOIN app_location d
	       ON d.applocationid = alc.applocationid
	LEFT JOIN ridershifts c
	       ON a.shiftid = c.shiftid
	WHERE 1=1
	`)

	if roleID != 0 {
		queryBuilder.WriteString(" AND a.roleid = ?")
		params = append(params, roleID)
	}

	if configID != 0 {
		queryBuilder.WriteString(" AND a.configid = ?")
		params = append(params, configID)
	}

	if tenantID != 0 {
		queryBuilder.WriteString(" AND a.tenantid = ?")
		params = append(params, tenantID)
	}

	if keyword != "" {
		queryBuilder.WriteString(`
		AND (
			LOWER(a.firstname) LIKE ? OR
			LOWER(a.contactno) LIKE ? OR
			LOWER(a.suburb) LIKE ?
		)
		`)
		search := "%" + strings.ToLower(keyword) + "%"
		params = append(params, search, search, search)
	}

	queryBuilder.WriteString(`
	GROUP BY a.userid, c.starttime, c.endtime
	ORDER BY a.userid DESC
	LIMIT ? OFFSET ?
	`)

	params = append(params, pagesize, offset)

	// 🔹 Execute query
	db.DB.Raw(queryBuilder.String(), params...).Scan(&users)

	// 🔹 Convert RAW → API arrays
	for i := range users {

		// ✅ always initialize → [] not null
		users[i].Applocationids = []int{}
		users[i].Applocationnames = []string{}

		// IDs
		if users[i].ApplocationidsRaw != "" {
			ids := strings.Split(users[i].ApplocationidsRaw, ",")
			for _, id := range ids {
				if v, err := strconv.Atoi(id); err == nil {
					users[i].Applocationids = append(users[i].Applocationids, v)
				}
			}
		}

		// Names
		if users[i].ApplocationnamesRaw != "" {
			names := strings.Split(users[i].ApplocationnamesRaw, ",")
			for _, name := range names {
				users[i].Applocationnames = append(
					users[i].Applocationnames,
					strings.TrimSpace(name),
				)
			}
		}
	}

	return users
}