package domain

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

// base = `SELECT DISTINCT a.orderheaderid, a.applocationid, h.locationname AS applocation, a.tenantid, a.locationid, a.partnerid, a.configid, a.categoryid, a.subcategoryid, a.moduleid,
// 		a.orderid, a.orderstatus, a.orderdate, a.ordernotes, a.itemcount, a.deliverytime AS deliverydate,
// 		a.pending, a.processing, a.ready, a.delivered AS completed, a.cancelled, f.orderstatus AS deliverystatus,
// 		f.assigntime, f.starttime, f.arrivaltime, f.pickuptime, f.deliverytime, f.canceltime,
// 		a.deliverycharge, a.kms, f.actualkms, f.deliveryamt,
// 		a.customerid, a.pickuplocationid, a.pickupaddress, a.pickuplat, a.pickuplong,
// 		a.pickupcustomer, a.pickupcontactno, a.pickuplocation as pickupsuburb, a.pickupcity,
// 		a.deliveryid AS deliverycustomerid,  a.deliveryaddress, a.deliverylat, a.deliverylong, a.deliverytype,
// 	a.deliverycustomer,a.deliverycontactno,a.deliverylocation as deliverysuburb, a.deliverycity, a.paymenttype, a.smsdelivery, b.customertoken,
// 		c.tenantname, c.tenanttoken, c.primarycontact AS tenantcontactno, c.postcode AS tenantpostcode, c.suburb AS tenantsuburb, c.city AS tenantcity,
// 		d.locationname, d.contactno AS locationcontactno, d.postcode AS locationpostcode, d.suburb AS locationsuburb, d.city AS locationcity, g.firstname AS rider, g.contactno AS ridercontactno,
// 		g.userfcmtoken as ridertoken
// 		FROM orders a
// 		INNER JOIN customers b ON a.customerid = b.customerid
// 		INNER JOIN tenants c ON a.tenantid = c.tenantid
// 		INNER JOIN tenantlocations d ON a.locationid = d.locationid
// 		LEFT JOIN deliveries f ON a.orderheaderid = f.orderheaderid
// 		LEFT JOIN app_users g ON f.userid = g.userid
// 		INNER JOIN app_location h ON a.applocationid = h.applocationid
// 		INNER JOIN app_locationconfig i ON a.applocationid = i.applocationid`

const (
	base = `SELECT DISTINCT a.orderheaderid, a.applocationid, h.locationname AS applocation, a.tenantid, a.locationid, a.partnerid, a.configid, a.categoryid, a.subcategoryid, a.moduleid,
		a.orderid, a.orderstatus, a.orderdate, a.ordernotes, a.itemcount, a.deliverytime AS deliverydate,
		a.pending, a.processing, a.ready, a.delivered AS completed, a.cancelled, 
		a.deliverycharge, a.kms,
		a.customerid, a.pickuplocationid, a.pickupaddress, a.pickuplat, a.pickuplong, a.pickupslot,
		a.pickupcustomer, a.pickupcontactno, a.pickuplocation as pickupsuburb, a.pickupcity,
		a.deliveryid AS deliverycustomerid,  a.deliveryaddress, a.deliverylat, a.deliverylong, a.deliverytype, 
		a.deliverycustomer,a.deliverycontactno,a.deliverylocation as deliverysuburb, a.deliverycity, a.paymenttype, a.smsdelivery, a.quantity, a.collectionamt, b.customertoken,
		c.tenantname, c.tenanttoken, c.primarycontact AS tenantcontactno, c.postcode AS tenantpostcode, c.suburb AS tenantsuburb, c.city AS tenantcity,
		d.locationname, d.contactno AS locationcontactno, d.postcode AS locationpostcode, d.suburb AS locationsuburb, d.city AS locationcity, 
		f.slab, f.pricingdate, f.baseprice, f.minkm, f.priceperkm, f.maxkm, f.orders, f.othercharges, f.surgecharges
		FROM orders a
		LEFT JOIN customers b ON a.customerid = b.customerid
		INNER JOIN tenants c ON a.tenantid = c.tenantid
		INNER JOIN tenantlocations d ON a.locationid = d.locationid
		INNER JOIN app_location h ON a.applocationid = h.applocationid
		INNER JOIN app_locationconfig i ON a.applocationid = i.applocationid
		LEFT JOIN tenantpricing f ON a.tenantid = f.tenantid`

	orderdetails = `SELECT DISTINCT a.orderheaderid, a.applocationid,
	a.tenantid, a.locationid, a.partnerid, a.configid, a.categoryid, a.subcategoryid, a.moduleid,
	a.orderid, a.orderstatus, a.orderdate, a.ordernotes, a.itemcount, a.deliverytime AS deliverydate,
	a.pending, a.processing, a.ready, a.delivered AS completed, a.cancelled,
	a.deliverycharge, a.kms, 
	a.customerid, a.pickupaddress, a.pickuplat, a.pickuplong, a.pickupslot,
	a.pickupcustomer, a.pickupcontactno, a.pickuplocation as pickupsuburb, a.pickupcity,
	a.deliveryid AS deliverycustomerid,  a.deliveryaddress, a.deliverylat, a.deliverylong, a.deliverytype, 
	a.deliverycustomer,a.deliverycontactno, a.deliverylocation as deliverysuburb, a.deliverycity, a.paymenttype, a.smsdelivery, a.orderamount, a.quantity, a.collectionamt,
	b.tenantname, b.tenanttoken, b.primarycontact AS tenantcontactno, b.postcode AS tenantpostcode, b.suburb AS tenantsuburb,b.city AS tenantcity, b.address AS tenantaddress,
	c.locationname, c.contactno AS locationcontactno, c.postcode AS locationpostcode, c.suburb AS locationsuburb, c.city AS locationcity, c.address AS locationaddress,
	d.locationname AS applocation, f.slab, f.pricingdate, f.baseprice, f.minkm, f.priceperkm, f.maxkm, f.orders, f.othercharges, f.surgecharges
	FROM orders a
	INNER JOIN tenants b ON a.tenantid = b.tenantid
	INNER JOIN tenantlocations c ON a.locationid = c.locationid
	INNER JOIN app_location d ON a.applocationid = d.applocationid
	INNER JOIN app_locationconfig e ON d.applocationid = e.applocationid
	LEFT JOIN tenantpricing f ON a.tenantid = f.tenantid`
)

func GetTenantLocationOrders(input models.DeliveryQuery) []models.OrderInfo {
	var data []models.OrderInfo
	var query string
	var params []interface{}

	offset := (input.Pageno - 1) * input.Pagesize
	baseQuery := base + ` WHERE c.moduleid = 6 and a.tenantid = ? and a.locationid = ?`
	params = append(params, input.Tenantid, input.Locationid)

	utils.Info("Fetching Tenant Location Orders")
	// Order status filtering
	if input.Status == "ongoing" {
		query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
	} else {
		query = baseQuery + ` AND a.orderstatus = ?`
		params = append(params, input.Status)
	}

	// Date filtering
	query += ` AND a.deliverytime::date BETWEEN ? AND ?`
	params = append(params, input.Fromdate, input.ToDate)

	// 🔍 Case-insensitive keyword filter
	if input.Keyword != "" {

		k := "%" + strings.ToLower(input.Keyword) + "%"

		query += ` AND (
        LOWER(a.pickupcustomer) LIKE ? OR
        LOWER(c.tenantname) LIKE ? OR
        LOWER(a.deliverycustomer) LIKE ? OR
        LOWER(a.pickupcontactno) LIKE ? OR
        LOWER(a.deliverycontactno) LIKE ? OR
        LOWER(a.orderid) LIKE ?
    )`

		params = append(params, k, k, k, k, k, k)
	}

	// 🔧 Config ID filter
	if input.Configid != 0 {
		query += ` AND a.configid = ?`
		params = append(params, input.Configid)
	}

	// Final ordering & paging
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, input.Pagesize, offset)

	// Debug log
	utils.Logger.Debugf("Executing: %s", query)

	db.DB.Raw(query, params...).Find(&data)
	return data
}
func GetTenantLocationAppOrders(input models.DeliveryQuery) []models.OrderInfo {
	var data []models.OrderInfo
	var query string
	var params []interface{}

	offset := (input.Pageno - 1) * input.Pagesize

	baseQuery := base + ` WHERE c.moduleid = 6 
                          AND a.tenantid = ? 
                          AND a.locationid = ?
                          AND a.applocationid = ?`

	params = append(params, input.Tenantid, input.Locationid, input.Applocationid)

	// status
	if input.Status == "ongoing" {
		query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
	} else {
		query = baseQuery + ` AND a.orderstatus = ?`
		params = append(params, input.Status)
	}

	// date filter
	query += ` AND a.deliverytime::date BETWEEN ? AND ?`
	params = append(params, input.Fromdate, input.ToDate)

	// keyword filter
	if input.Keyword != "" {
		k := "%" + strings.ToLower(input.Keyword) + "%"
		query += ` AND (
			LOWER(a.pickupcustomer) LIKE ? OR
			LOWER(c.tenantname) LIKE ? OR
			LOWER(a.deliverycustomer) LIKE ? OR
			LOWER(a.pickupcontactno) LIKE ? OR
			LOWER(a.deliverycontactno) LIKE ? OR
			LOWER(a.orderid) LIKE ?
		)`
		params = append(params, k, k, k, k, k, k)
	}

	// configid filter
	if input.Configid != 0 {
		query += ` AND a.configid = ?`
		params = append(params, input.Configid)
	}

	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, input.Pagesize, offset)

	db.DB.Raw(query, params...).Find(&data)
	return data
}

func GetTenantAppOrders(input models.DeliveryQuery) []models.OrderInfo {
	var data []models.OrderInfo
	var query string
	var params []interface{}

	offset := (input.Pageno - 1) * input.Pagesize

	// Base query for Tenant + AppLocation
	baseQuery := base + ` WHERE c.moduleid = 6 
		AND a.tenantid = ? 
		AND a.applocationid = ?`

	params = append(params, input.Tenantid, input.Applocationid)

	// 🔥 Status filter
	if input.Status == "ongoing" {
		query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
	} else {
		query = baseQuery + ` AND a.orderstatus = ?`
		params = append(params, input.Status)
	}

	// 📅 Date filter
	query += ` AND a.deliverytime::date BETWEEN ? AND ?`
	params = append(params, input.Fromdate, input.ToDate)

	// 🔍 Keyword (case-insensitive)
	if input.Keyword != "" {
		k := "%" + strings.ToLower(input.Keyword) + "%"

		query += ` AND (
			LOWER(a.pickupcustomer) LIKE ? OR
			LOWER(c.tenantname) LIKE ? OR
			LOWER(a.deliverycustomer) LIKE ? OR
			LOWER(a.pickupcontactno) LIKE ? OR
			LOWER(a.deliverycontactno) LIKE ? OR
			LOWER(a.orderid) LIKE ?
		)`
		params = append(params, k, k, k, k, k, k)
	}

	// ⚙️ Config ID filter
	if input.Configid != 0 {
		query += ` AND a.configid = ?`
		params = append(params, input.Configid)
	}

	// 📦 Order & Pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, input.Pagesize, offset)

	// Execute
	db.DB.Raw(query, params...).Find(&data)

	return data
}

func GetAppOrders(input models.DeliveryQuery) []models.OrderInfo {
	var data []models.OrderInfo
	var query string
	var params []interface{}

	offset := (input.Pageno - 1) * input.Pagesize

	// Base query for only AppLocation
	baseQuery := base + ` WHERE c.moduleid = 6 
		AND a.applocationid = ?`

	params = append(params, input.Applocationid)

	// 🔥 Status filter
	if input.Status == "ongoing" {
		query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
	} else {
		query = baseQuery + ` AND a.orderstatus = ?`
		params = append(params, input.Status)
	}

	// 📅 Date filter
	query += ` AND a.deliverytime::date BETWEEN ? AND ?`
	params = append(params, input.Fromdate, input.ToDate)

	// 🔍 Keyword (case-insensitive)
	if input.Keyword != "" {
		k := "%" + strings.ToLower(input.Keyword) + "%"

		query += ` AND (
			LOWER(a.pickupcustomer) LIKE ? OR
			LOWER(c.tenantname) LIKE ? OR
			LOWER(a.deliverycustomer) LIKE ? OR
			LOWER(a.pickupcontactno) LIKE ? OR
			LOWER(a.deliverycontactno) LIKE ? OR
			LOWER(a.orderid) LIKE ?
		)`
		params = append(params, k, k, k, k, k, k)
	}

	// ⚙️ Config ID filter
	if input.Configid != 0 {
		query += ` AND a.configid = ?`
		params = append(params, input.Configid)
	}

	// 📦 Order & Pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, input.Pagesize, offset)

	// Execute
	db.DB.Raw(query, params...).Find(&data)

	return data
}

func GetTenantOrders(input models.DeliveryQuery) []models.OrderInfo {
	var data []models.OrderInfo
	var query string
	var params []interface{}

	offset := (input.Pageno - 1) * input.Pagesize
	baseQuery := base + ` WHERE c.moduleid = 6 and a.tenantid = ?`
	params = append(params, input.Tenantid)

	utils.Info("Fetching Tenant Orders")
	// Order status filtering
	if input.Status == "ongoing" {
		query = baseQuery + ` AND a.orderstatus IN ('pending','processing','ready')`
	} else {
		query = baseQuery + ` AND a.orderstatus = ?`
		params = append(params, input.Status)
	}

	// Date filtering
	query += ` AND a.deliverytime::date BETWEEN ? AND ?`
	params = append(params, input.Fromdate, input.ToDate)

	// 🔍 Keyword filter (case-insensitive)
	if input.Keyword != "" {

		keyword := strings.ToLower(input.Keyword)
		like := "%" + keyword + "%"

		query += ` AND (
        LOWER(a.pickupcustomer) LIKE ? OR
        LOWER(c.tenantname) LIKE ? OR
        LOWER(a.deliverycustomer) LIKE ? OR
        LOWER(a.pickupcontactno) LIKE ? OR
        LOWER(a.deliverycontactno) LIKE ? OR
        LOWER(a.orderid) LIKE ?
    )`

		params = append(params, like, like, like, like, like, like)
	}

	// 🔧 Config ID filter
	if input.Configid != 0 {
		query += ` AND a.configid = ?`
		params = append(params, input.Configid)
	}

	// Final ordering & paging
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, input.Pagesize, offset)

	// Debug log
	utils.Logger.Debugf("Executing: %s", query)

	db.DB.Raw(query, params...).Find(&data)
	return data
}

func GetTenantOrdersv2(stat, fdate, tdate string, tid, pageno, pagesize int) []models.OrderInfo {

	var q1 string

	var data []models.OrderInfo
	offset := (pageno - 1) * pagesize

	if stat == "ongoing" {

		q1 = orderdetails + ` where a.moduleid=6 and a.tenantid=? and a.orderstatus in ('pending','processing','ready') and  a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
		order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	} else {

		q1 = orderdetails + ` where a.moduleid=6 and a.tenantid=? and a.orderstatus= '` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
		  order by a.deliverytime LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	}
	// print(q1)
	db.DB.Raw(q1, tid).Find(&data)

	return data

}

func GetPartnerOrders(stat, fdate, tdate string, pid, pageno, pagesize int, keyword string) []models.OrderInfo {
	var data []models.OrderInfo

	if pageno <= 0 {
		pageno = 1
	}
	if pagesize <= 0 {
		pagesize = 10
	}
	offset := (pageno - 1) * pagesize

	utils.Info("Getting partner order details")

	// Base SELECT query (with joins)
	query := orderdetails + ` WHERE b.moduleid = 6 and a.partnerid = ?`
	params := []interface{}{pid}

	// Filter conditions
	if fdate != "" && tdate != "" {
		query += ` AND a.deliverytime::date BETWEEN ? AND ?`
		params = append(params, fdate, tdate)
	}
	if stat != "" {
		query += ` AND a.orderstatus = ?`
		params = append(params, stat)
	}
	if keyword != "" {
		query += ` AND (
			a.pickupcustomer LIKE ? OR
			b.tenantname LIKE ? OR
			a.deliverycustomer LIKE ? OR
			a.pickupcontactno LIKE ? OR
			a.deliverycontactno LIKE ? OR
			a.orderid LIKE ?
		)`
		like := "%" + keyword + "%"
		params = append(params, like, like, like, like, like, like)
	}

	// ✅ Count query for pagination
	var total int64
	countQuery := `
	SELECT COUNT(DISTINCT a.orderheaderid)
	FROM orders a
	INNER JOIN tenants b ON a.tenantid = b.tenantid
	INNER JOIN tenantlocations c ON a.locationid = c.locationid
	INNER JOIN app_location d ON a.applocationid = d.applocationid
	INNER JOIN app_locationconfig e ON d.applocationid = e.applocationid
	WHERE a.partnerid = ?`
	countParams := []interface{}{pid}

	// Reapply filters to countQuery
	if fdate != "" && tdate != "" {
		countQuery += ` AND a.deliverytime::date BETWEEN ? AND ?`
		countParams = append(countParams, fdate, tdate)
	}
	if stat != "" {
		countQuery += ` AND a.orderstatus = ?`
		countParams = append(countParams, stat)
	}
	if keyword != "" {
		countQuery += ` AND (
			a.pickupcustomer LIKE ? OR
			b.tenantname LIKE ? OR
			a.deliverycustomer LIKE ? OR
			a.pickupcontactno LIKE ? OR
			a.deliverycontactno LIKE ? OR
			a.orderid LIKE ?
		)`
		like := "%" + keyword + "%"
		countParams = append(countParams, like, like, like, like, like, like)
	}

	db.DB.Raw(countQuery, countParams...).Scan(&total)

	// ✅ Offset boundary fix
	if int64(offset) >= total {
		offset = 0
		pageno = 1
	}

	// Append pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, pagesize, offset)

	utils.Logger.Debugf("QUERY: %s", query)
	utils.Logger.Debugf("PARAMS: %v", params)

	db.DB.Raw(query, params...).Find(&data)
	utils.Logger.Infof("RESULT COUNT: %d", len(data))

	return data
}

func GetPartnerOrdersv2(stat, fdate, tdate string, pid, pageno, pagesize int) []models.OrderInfo {

	var data []models.OrderInfo
	var q1 string

	offset := (pageno - 1) * pagesize

	if stat != "" {

		if fdate != "" && tdate != "" {

			q1 = orderdetails + ` where a.moduleid=6 and a.partnerid=` + strconv.Itoa(pid) + ` and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'
			order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

		} else {

			q1 = orderdetails + ` where a.moduleid=6 and a.partnerid=` + strconv.Itoa(pid) + ` and a.orderstatus='` + stat + `' 
			order by a.deliverytime LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

		}

	} else {

		q1 = orderdetails + ` where a.moduleid=6 and a.partnerid=` + strconv.Itoa(pid) + ` and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
		 order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	}

	// print(q1)

	db.DB.Raw(q1).Find(&data)
	return data
}

func GetAllOrders(stat, fdate, tdate string, aid, pageno, pagesize int, keyword string) []models.OrderInfo {
	var data []models.OrderInfo
	offset := (pageno - 1) * pagesize
	utils.Info("All orders")

	// Base query
	query := orderdetails + `
		WHERE b.moduleid = 6 
		AND a.deliverytime::date BETWEEN ? AND ?
	`
	params := []interface{}{fdate, tdate}

	// 🔹 applocationid filter
	if aid != 0 {
		query += ` AND a.applocationid = ?`
		params = append(params, aid)
	}

	// 🔹 Status filter
	if stat != "" {
		query += ` AND a.orderstatus = ?`
		params = append(params, stat)
	}

	// 🔹 Keyword filter (case-insensitive)
	if keyword != "" {
		k := "%" + strings.ToLower(keyword) + "%"

		query += `
					AND (
						LOWER(a.pickupcustomer) LIKE ? OR
						LOWER(b.tenantname) LIKE ? OR
						LOWER(a.deliverycustomer) LIKE ? OR
						LOWER(a.pickupcontactno) LIKE ? OR
						LOWER(a.deliverycontactno) LIKE ? OR
						LOWER(a.orderid) LIKE ?
					)`

		params = append(params, k, k, k, k, k, k)
	}

	// Sorting + pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, pagesize, offset)

	// Debug
	utils.Logger.Debugf("Executing query: %s", query)
	print(query)
	// DB execution
	db.DB.Raw(query, params...).Find(&data)

	return data
}

func GetAllOrdersv2(stat, fdate, tdate string, pageno, pagesize int) []models.OrderInfo {

	var data []models.OrderInfo
	var q1 string
	offset := (pageno - 1) * pagesize

	if stat != "" {

		q1 = orderdetails + ` where a.moduleid=6 and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
		    order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	} else {

		q1 = orderdetails + ` where a.moduleid=6 and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
		order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	}

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

	db.DB.Raw(q1).Find(&data)
	return data
}

func GetUserOrdersv2(stat, fdate, tdate string, uid, pageno, pagesize int) []models.OrderInfo {

	var data []models.OrderInfo
	var q1 string
	offset := (pageno - 1) * pagesize

	if uid != 0 {

		if fdate != "" && tdate != "" {

			if stat != "" {

				q1 = orderdetails + ` where a.moduleid=6 and e.status = 'Active' and e.userid=` + strconv.Itoa(uid) + ` and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'  
				order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

			} else {

				q1 = orderdetails + ` where a.moduleid=6 and e.status = 'Active' and  e.userid=` + strconv.Itoa(uid) + ` and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
				order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

			}

		} else if stat != "" {

			q1 = orderdetails + ` where a.moduleid=6 and e.status = 'Active' and e.userid=` + strconv.Itoa(uid) + ` and a.orderstatus='` + stat + `' 
			order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

		}

	}

	// print(q1)

	db.DB.Raw(q1).Find(&data)
	return data
}

func GetLocationOrders(stat, fdate, tdate string, lid, pageno, pagesize int, keyword string) []models.OrderInfo {
	var data []models.OrderInfo
	offset := (pageno - 1) * pagesize

	utils.Info("Getting location order details")

	// Start query and params
	query := orderdetails + `
		WHERE b.moduleid = 6 and e.status = 'Active' AND a.locationid = ?
	`
	params := []interface{}{lid}

	// Add date filter
	if fdate != "" && tdate != "" {
		query += ` AND a.deliverytime::date BETWEEN ? AND ?`
		params = append(params, fdate, tdate)
	}

	// Add status filter
	if stat != "" {
		query += ` AND a.orderstatus = ?`
		params = append(params, stat)
	}

	// Add keyword filter
	if keyword != "" {
		query += `
		AND (
			a.pickupcustomer LIKE ? OR
			a.tenantname LIKE ? OR
			a.deliverycustomer LIKE ? OR
			a.pickupcontactno LIKE ? OR
			a.deliverycontactno LIKE ? OR
			a.orderid LIKE ?
		)`
		likeKeyword := "%" + keyword + "%"
		params = append(params, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword)
	}

	// Add sorting and pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, pagesize, offset)

	// Debugging
	utils.Logger.Debugf("Executing query: %s", query)

	// Execute
	db.DB.Raw(query, params...).Find(&data)

	return data
}

func GetUserOrders(stat, fdate, tdate string, uid, pageno, pagesize int, keyword string) []models.OrderInfo {
	var data []models.OrderInfo
	offset := (pageno - 1) * pagesize

	utils.Info("Getting user order details")

	// Start query and params
	query := orderdetails + `
		WHERE b.moduleid = 6 and e.status = 'Active' AND e.userid = ?
	`
	params := []interface{}{uid}

	// Add date filter
	if fdate != "" && tdate != "" {
		query += ` AND a.deliverytime::date BETWEEN ? AND ?`
		params = append(params, fdate, tdate)
	}

	// Add status filter
	if stat != "" {
		query += ` AND a.orderstatus = ?`
		params = append(params, stat)
	}

	// Add keyword filter
	if keyword != "" {
		query += `
		AND (
			a.pickupcustomer LIKE ? OR
			a.tenantname LIKE ? OR
			a.deliverycustomer LIKE ? OR
			a.pickupcontactno LIKE ? OR
			a.deliverycontactno LIKE ? OR
			a.orderid LIKE ?
		)`
		likeKeyword := "%" + keyword + "%"
		params = append(params, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword)
	}

	// Add sorting and pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, pagesize, offset)

	// Debugging
	utils.Logger.Debugf("Executing query: %s", query)

	// Execute
	db.DB.Raw(query, params...).Find(&data)

	return data
}

func GetAdminOrders(stat, fdate, tdate string, aid, pageno, pagesize int, keyword string) []models.OrderInfo {
	var data []models.OrderInfo
	offset := (pageno - 1) * pagesize

	utils.Info("Getting admin order details")

	// Start query and params
	query := orderdetails + ` WHERE 1=1`
	var params []interface{}

	// applocationid filter (aid)
	if aid != 0 {
		query += ` AND a.applocationid = ?`
		params = append(params, aid)
	}

	// date filter
	if fdate != "" && tdate != "" {
		query += ` AND a.deliverytime::date BETWEEN ? AND ?`
		params = append(params, fdate, tdate)
	}

	// status filter
	if stat != "" {
		query += ` AND a.orderstatus = ?`
		params = append(params, stat)
	}

	// keyword search
	if keyword != "" {
		query += `
		AND (
			a.pickupcustomer LIKE ? OR
			a.tenantname LIKE ? OR
			a.deliverycustomer LIKE ? OR
			a.pickupcontactno LIKE ? OR
			a.deliverycontactno LIKE ? OR
			a.orderid LIKE ?
		)`
		likeKeyword := "%" + keyword + "%"
		params = append(params,
			likeKeyword, likeKeyword, likeKeyword,
			likeKeyword, likeKeyword, likeKeyword,
		)
	}

	// Sorting and pagination
	query += ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, pagesize, offset)

	// Debugging
	utils.Logger.Debugf("Executing query: %s", query)

	// Execute the query
	db.DB.Raw(query, params...).Find(&data)

	return data
}

func GetAdminOrdersv2(stat, fdate, tdate string, aid, pageno, pagesize int) []models.OrderInfo {

	var data []models.OrderInfo
	var q1 string

	utils.Info("Getting admin order details")
	offset := (pageno - 1) * pagesize

	if aid != 0 {

		if fdate != "" && tdate != "" {

			if stat != "" {

				q1 = orderdetails + ` where a.moduleid=6 and a.applocationid=` + strconv.Itoa(aid) + ` and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `'  
				order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

			} else {

				q1 = orderdetails + ` where a.moduleid=6 and a.applocationid=` + strconv.Itoa(aid) + ` and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
				order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

			}

		} else if stat != "" {

			q1 = orderdetails + ` where a.moduleid=6 and a.applocationid=` + strconv.Itoa(aid) + ` and a.orderstatus='` + stat + `' 
			order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

		}
	} else {

		if stat != "" {

			q1 = orderdetails + ` where a.moduleid=6 and a.orderstatus='` + stat + `' and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
			order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

		} else {

			q1 = orderdetails + ` where a.moduleid=6 and a.deliverytime::date between '` + fdate + `' and '` + tdate + `' 
			order by a.orderheaderid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

		}

	}

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

	db.DB.Raw(q1).Find(&data)
	return data
}

func GetCustomerOrders(stat, fdate, tdate string, cid, mid, pageno, pagesize int, keyword string) []models.OrderInfo {
	var data []models.OrderInfo
	utils.Info("Getting customer order details")
	// Safety defaults
	if pageno <= 0 {
		pageno = 1
	}
	if pagesize <= 0 {
		pagesize = 10
	}
	offset := (pageno - 1) * pagesize

	// Prepare base query
	baseQuery := orderdetails + ` WHERE b.moduleid = 6 and a.customerid = ?`
	params := []interface{}{cid}

	// Filters
	if mid != 0 {
		baseQuery += ` AND a.moduleid = ?`
		params = append(params, mid)
	}
	if fdate != "" && tdate != "" {
		baseQuery += ` AND a.orderdate::date BETWEEN ? AND ?`
		params = append(params, fdate, tdate)
	}
	if stat != "" {
		baseQuery += ` AND a.orderstatus = ?`
		params = append(params, stat)
	}
	if keyword != "" {
		baseQuery += ` AND (
			a.pickupcustomer LIKE ? OR
			b.tenantname LIKE ? OR
			a.deliverycustomer LIKE ? OR
			a.pickupcontactno LIKE ? OR
			a.deliverycontactno LIKE ? OR
			a.orderid LIKE ?
		)`
		like := "%" + keyword + "%"
		params = append(params, like, like, like, like, like, like)
	}

	// Count total
	var total int64
	countQuery := strings.Replace(baseQuery, "SELECT DISTINCT a.orderheaderid, ", "SELECT COUNT(DISTINCT a.orderheaderid) ", 1)
	db.DB.Raw(countQuery, params...).Scan(&total)

	// Prevent out-of-bound offset
	if int64(offset) >= total {
		offset = 0
		pageno = 1
	}

	// Final paginated query
	finalQuery := baseQuery + ` ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?`
	params = append(params, pagesize, offset)

	utils.Logger.Debugf("QUERY: %s", finalQuery)
	utils.Logger.Debugf("PARAMS: %v", params)

	res := db.DB.Raw(finalQuery, params...).Find(&data)
	if res.Error != nil {
		utils.Error("ERROR querying customer orders", "error", res.Error)
	}
	utils.Logger.Infof("RESULT COUNT: %d", len(data))

	print(finalQuery)

	return data
}

func GetCustomerOrdersv2(stat, fdate, tdate string, cid, mid, pageno, pagesize int) []models.OrderInfo {

	var data []models.OrderInfo
	var q1 string

	if stat != "" {

		if fdate != "" && tdate != "" {

			q1 = orderdetails + ` where  a.moduleid=6 and a.customerid=` + strconv.Itoa(cid) + ` and a.orderstatus='` + stat + `' and a.orderdate::date between '` + fdate + `' and '` + tdate + `'  order by a.orderheaderid desc`

		} else {

			q1 = orderdetails + ` where a.moduleid=6 and a.customerid=` + strconv.Itoa(cid) + ` and a.orderstatus='` + stat + `' order by a.orderheaderid desc`

		}

	} else {

		if mid != 0 {

			q1 = orderdetails + ` where  a.moduleid=` + strconv.Itoa(mid) + ` and a.customerid=` + strconv.Itoa(cid) + ` and a.orderdate::date between '` + fdate + `' and '` + tdate + `'  order by a.orderheaderid desc`

		} else {

			q1 = orderdetails + ` where a.customerid=` + strconv.Itoa(cid) + ` and a.orderdate::date between '` + fdate + `' and '` + tdate + `'  order by a.orderheaderid desc`

		}

	}

	// print(q1)

	db.DB.Raw(q1).Find(&data)
	return data
}

func GetOrderbyid(oid int) models.OrderInfo {

	var data models.OrderInfo

	q1 := orderdetails + ` where a.orderheaderid=` + strconv.Itoa(oid)

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

	return data

}

func GetOrderbyStatus(uid int, stat string) models.OrderInfo {

	var data models.OrderInfo

	q1 := orderdetails + ` where a.orderheaderid=` + strconv.Itoa(uid)

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

	return data

}

func GetOrderbyidV2(oid int) models.OrderInfo {

	var data models.OrderInfo

	q1 := orderdetails + ` where a.orderheaderid=` + strconv.Itoa(oid)

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

	return data

}

func GetOrderDetailsByHeaderID(orderHeaderID int) ([]models.OrderDetails, float64, float64, error) {
	var details []models.OrderDetails
	var orderAmount float64
	var totalTaxAmount float64

	query := `
		SELECT 
			a.orderdetailid,
			a.orderheaderid,
			a.tenantid,
			a.locationid,
			a.productid,
			a.productname,
			a.productdescription,
			a.supplyqty,
			a.balanceqty,
			a.orderqty,
			a.price,
			a.unitid,
			a.unitname,
			a.productaddonid,
			a.addontypeid,
			a.productmapid,
			a.productvariantid,
			a.productaddondescription,
			a.discountid,
			a.discountname,
			a.discountcode,
			a.discountterms,
			a.discountpercentage,
			a.discountamount,
			a.landingamount,
			a.taxpercentage,
			a.taxamount,
			a.productsumprice,
			a.itemstatus,
			a.delivered,
			a.taxamount,
			COALESCE(b.orderamount, 0) as orderamount,
			COALESCE(b.taxamount, 0) as totaltaxamount,
			c.productimage
		FROM orderdetails a
		LEFT JOIN orders b ON b.orderheaderid = a.orderheaderid
		LEFT JOIN products c ON a.productid = c.productid
		WHERE a.orderheaderid = ?
	`

	err := db.DB.Raw(query, orderHeaderID).Scan(&details).Error
	if err != nil {
		return nil, 0, 0, err
	}

	if len(details) > 0 {
		orderAmount = details[0].Orderamount
	}

	if len(details) > 0 {
		totalTaxAmount = details[0].Totaltaxamount
	}

	return details, orderAmount, totalTaxAmount, nil
}

func GetCustomerOrderByLocation(customerid int) ([]models.CustomerOrderLocation, error) {
	query := `
		SELECT 
			c.locationname   AS locationname,
			COUNT(a.orderid) AS ordercount,
			c.contactno      AS contactno,
			c.address        AS address,
			c.suburb         AS suburb,
			c.city           AS city,
			c.state          AS state,
			c.postcode       AS postcode,
			c.latitude       AS latitude,
			c.longitude      AS longitude
		FROM orders a
		LEFT JOIN tenantlocations c ON a.locationid = c.locationid
		WHERE a.customerid = ?
		GROUP BY 
			c.locationname
	`

	var result []models.CustomerOrderLocation
	err := db.DB.Raw(query, customerid).Scan(&result).Error

	// Debug actual data
	utils.Logger.Debugf("result: %+v", result)

	// return [] instead of null
	if result == nil {
		result = []models.CustomerOrderLocation{}
	}

	return result, err
}