package controllers

import (
	"nearle/db"
	"nearle/domain"
	"nearle/models"
	"nearle/utils"
	"net/http"
	"strconv"
	"strings"
	"time"

	"github.com/gofiber/fiber/v2"
	"gorm.io/gorm"
)

const (
	base = `SELECT COUNT(*) AS total,
		SUM(CASE WHEN orderstatus = 'created' THEN 1 ELSE 0 END) AS created,
		SUM(CASE WHEN orderstatus = 'pending' THEN 1 ELSE 0 END) AS pending,
		SUM(CASE WHEN orderstatus = 'processing' THEN 1 ELSE 0 END) AS processing,
		SUM(CASE WHEN orderstatus = 'delivered' THEN 1 ELSE 0 END) AS delivered,
		SUM(CASE WHEN orderstatus = 'cancelled' THEN 1 ELSE 0 END) AS cancelled`
)

func CreateCustomerOrder(c *fiber.Ctx) error {

	var data models.Customerorder

	if err := c.BodyParser(&data); err != nil {
		return err
	}

	tx := db.DB.Begin()

	data.Orders.Orderid = domain.GetSequenceno(data.Orders.Tenantid, "ORD")

	if data.Orders.Customerid != 0 && data.Orders.Deliveryid != 0 {

		// utils.Logger.Debug("coming to existing for creating order with location")

		pid, err := domain.UpdateCustomerv2(data.Pickup)
		if err != nil {

			return c.JSON(fiber.Map{
				"code":    http.StatusConflict,
				"message": err.Error(),
				"status":  false,
			})

		}
		utils.Logger.Infow("Pickup Location ID updated", "pid", pid)

		data.Orders.Pickuplocationid = pid

		did, err1 := domain.UpdateCustomerv2(data.Drop)
		if err1 != nil {

			return c.JSON(fiber.Map{
				"code":    http.StatusConflict,
				"message": err1.Error(),
				"status":  false,
			})

		}

		utils.Logger.Infow("Delivery Location ID updated", "did", did)
		data.Orders.Deliverylocationid = did

		t1 := tx.Create(&data.Orders)
		if t1.Error != nil {

			utils.Logger.Errorw("Tx Create Error", "error", t1.Error)

			tx.Rollback()
			return c.JSON(fiber.Map{
				"code":    http.StatusConflict,
				"message": t1.Error,
				"status":  false,
			})
		}

	} else {

		if data.Orders.Customerid == 0 {

			cid := domain.CheckCustomer(data.Pickup.Contactno)
			if cid != 0 {

				pid, err := domain.UpdateCustomerv2(data.Pickup)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err.Error(),
						"status":  false,
					})

				}

				utils.Logger.Infow("Customer exists, updating", "pid", pid)

				data.Orders.Pickuplocationid = pid

			} else {

				cid, err := domain.CreateCustomerv2(data.Pickup)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err,
						"status":  false,
					})

				}

				utils.Logger.Infow("Created new pickup customer", "customerid", cid.Customerid)

				data.Orders.Customerid = cid.Customerid
				data.Orders.Pickuplocationid = cid.Locationid

			}

		}

		if data.Orders.Deliveryid == 0 {

			utils.Logger.Infow("Creating new drop customer")

			cid := domain.CheckCustomer(data.Drop.Contactno)
			if cid != 0 {

				pid, err := domain.UpdateCustomerv2(data.Drop)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err.Error(),
						"status":  false,
					})

				}

				data.Orders.Deliverylocationid = pid

			} else {

				did, err := domain.CreateCustomerv2(data.Drop)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err,
						"status":  false,
					})

				}

				utils.Logger.Infow("Created new drop customer", "customerid", did.Customerid)

				data.Orders.Deliveryid = did.Customerid
				data.Orders.Deliverylocationid = did.Locationid

			}

		}

		t2 := tx.Create(&data.Orders)
		if t2.Error != nil {

			utils.Logger.Errorw("Tx Create Error", "error", t2.Error)
			tx.Rollback()
			return t2.Error
		}

	}

	domain.UpdateSeqno(data.Orders.Tenantid, "ORD")

	err := tx.Commit().Error

	result := domain.GetOrderbyid(data.Orders.Orderheaderid)

	if err != nil {

		return c.JSON(fiber.Map{
			"code":    http.StatusConflict,
			"message": err.Error(),
			"status":  false,
		})

	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": result,
	})

}

func CreateCustomerOrderv2(c *fiber.Ctx) error {

	var data models.Customerorder

	if err := c.BodyParser(&data); err != nil {
		return err
	}

	tx := db.DB.Begin()

	data.Orders.Orderid = domain.GetSequenceno(data.Orders.Tenantid, "ORD")

	if data.Orders.Customerid != 0 && data.Orders.Deliveryid != 0 {

		utils.Logger.Infow("Normal creating order")

		err := domain.UpdateCustomerv1(data.Pickup)
		if err != nil {

			return c.JSON(fiber.Map{
				"code":    http.StatusConflict,
				"message": err.Error(),
				"status":  false,
			})

		}

		err1 := domain.UpdateCustomerv1(data.Drop)
		if err1 != nil {

			return c.JSON(fiber.Map{
				"code":    http.StatusConflict,
				"message": err1.Error(),
				"status":  false,
			})

		}

		t1 := tx.Create(&data.Orders)
		if t1.Error != nil {

			utils.Logger.Errorw("Tx Create Error", "error", t1.Error)

			tx.Rollback()
			return c.JSON(fiber.Map{
				"code":    http.StatusConflict,
				"message": t1.Error,
				"status":  false,
			})
		}

	} else {

		utils.Logger.Infow("Creating customer and order")

		if data.Orders.Customerid == 0 {

			cid := domain.CheckCustomer(data.Pickup.Contactno)
			if cid != 0 {

				data.Pickup.Customerid = cid

				err := domain.UpdateCustomerv1(data.Pickup)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err.Error(),
						"status":  false,
					})

				}

				data.Orders.Customerid = cid

			} else {

				cid, err := domain.CreateCustomerv1(data.Pickup)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err,
						"status":  false,
					})

				}

				data.Orders.Customerid = cid

			}

		}

		if data.Orders.Deliveryid == 0 {

			dcid := domain.CheckCustomer(data.Drop.Contactno)

			if dcid != 0 {

				data.Drop.Customerid = dcid

				err := domain.UpdateCustomerv1(data.Drop)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err.Error(),
						"status":  false,
					})

				}

				data.Orders.Deliveryid = dcid

			} else {

				did, err := domain.CreateCustomerv1(data.Drop)
				if err != nil {

					return c.JSON(fiber.Map{
						"code":    http.StatusConflict,
						"message": err,
						"status":  false,
					})
				}

				data.Orders.Deliveryid = did

			}

		}

		t2 := tx.Create(&data.Orders)
		if t2.Error != nil {

			utils.Logger.Errorw("Tx Create Error", "error", t2.Error)
			tx.Rollback()
			return t2.Error
		}

	}

	domain.UpdateSeqno(data.Orders.Tenantid, "ORD")

	err := tx.Commit().Error

	utils.Logger.Infow("Order created with header ID", "orderheaderid", data.Orders.Orderheaderid)

	result := domain.GetOrderbyidV2(data.Orders.Orderheaderid)

	if err != nil {

		return c.JSON(fiber.Map{
			"code":    http.StatusConflict,
			"message": err.Error(),
			"status":  false,
		})

	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": result,
	})

}

func CreateOrder(c *fiber.Ctx) error {
	// Wrapper to parse "orders" key
	type OrderWrapper struct {
		Orders models.Orders `json:"orders"`
	}

	var wrapper OrderWrapper

	// ✅ Parse the JSON body
	if err := c.BodyParser(&wrapper); err != nil {
		utils.Logger.Errorw("BodyParser error", "error", err)
		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
			"code":    http.StatusBadRequest,
			"message": "Invalid request body",
			"status":  false,
		})
	}

	data := wrapper.Orders
	utils.Logger.Infow("Creating Order", "tenantid", data.Tenantid)

	// ❌ Check for missing tenantid
	if data.Tenantid == 0 {
		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
			"code":    http.StatusBadRequest,
			"message": "Tenant ID is required",
			"status":  false,
		})
	}

	// ✅ Fallback if orderdate is missing
	if strings.TrimSpace(data.Orderdate) == "" {
		data.Orderdate = time.Now().Format("2006-01-02 15:04:05")
	}

	// ✅ Start DB transaction
	tx := db.DB.Begin()

	// ✅ Generate order ID
	data.Orderid = domain.GetSequenceno(data.Tenantid, "ORD")

	// ✅ Insert order
	if err := tx.Create(&data).Error; err != nil {
		tx.Rollback()
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Failed to create order",
			"status":  false,
		})
	}

	// ✅ Update order sequence
	domain.UpdateSeqno(data.Tenantid, "ORD")

	// ✅ Commit transaction
	if err := tx.Commit().Error; err != nil {
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Transaction failed",
			"status":  false,
		})
	}

	// ✅ Fetch inserted order by ID
	result := domain.GetOrderbyid(data.Orderheaderid)

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": result,
	})
}

func CreateOrders(c *fiber.Ctx) error {

	var orders []models.Orders

	if err := c.BodyParser(&orders); err != nil {
		return err
	}

	tx := db.DB.Begin()

	for i := range orders {

		// utils.Logger.Debugf("tenantid: %v", orders[i].Tenantid)

		orders[i].Orderid = domain.GetSequenceno(orders[i].Tenantid, "ORD")
		t1 := tx.Create(&orders[i])
		if t1.Error != nil {
			utils.Logger.Errorw("Tx Create Error", "error", t1.Error)
			tx.Rollback()
			return c.JSON(fiber.Map{
				"code":    http.StatusInternalServerError,
				"message": "Error creating order",
				"status":  false,
			})
		}
		domain.UpdateSeqno(orders[i].Tenantid, "ORD")
	}

	tx.Commit()

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
	})

}

func UpdateOrder(c *fiber.Ctx) error {

	var orders models.Orders

	if err := c.BodyParser(&orders); err != nil {
		return err
	}

	tx := db.DB.Begin()

	t1 := tx.Where("orderheaderid=?", orders.Orderheaderid).Updates(&orders)
	if t1.Error != nil {
		utils.Error("UpdateOrder t1 updates error", "error", t1.Error)
		tx.Rollback()
		return c.JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Error updating order",
			"status":  false,
		})
	}

	tx.Commit()

	return c.JSON(fiber.Map{
		"code":    http.StatusAccepted,
		"message": "Success",
		"status":  true,
	})

}

func UpdateMultipleOrders(c *fiber.Ctx) error {

	var orders []models.Orders

	// Parse JSON array input
	if err := c.BodyParser(&orders); err != nil {
		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
			"code":    http.StatusBadRequest,
			"message": "Invalid request body",
			"status":  false,
		})
	}

	tx := db.DB.Begin()

	for _, order := range orders {

		// Update each order by orderheaderid
		if err := tx.Model(&models.Orders{}).
			Where("orderheaderid = ?", order.Orderheaderid).
			Updates(map[string]interface{}{
				"orderstatus": order.Orderstatus,
				"cancelled":   order.Cancelled,
			}).Error; err != nil {

			tx.Rollback()
			return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
				"code":    http.StatusInternalServerError,
				"message": "Failed to update order " + strconv.Itoa(order.Orderheaderid),
				"status":  false,
			})
		}
	}

	if err := tx.Commit().Error; err != nil {
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Transaction commit failed",
			"status":  false,
		})
	}

	return c.Status(http.StatusAccepted).JSON(fiber.Map{
		"code":    http.StatusAccepted,
		"message": "Orders updated successfully",
		"status":  true,
	})
}

func GetOrdersv2(c *fiber.Ctx) error {

	var result []models.OrderInfo

	tid, _ := strconv.Atoi(c.Query("tenantid"))
	pid, _ := strconv.Atoi(c.Query("partnerid"))
	cid, _ := strconv.Atoi(c.Query("customerid"))
	mid, _ := strconv.Atoi(c.Query("customerid"))
	aid, _ := strconv.Atoi(c.Query("applocationid"))
	uid, _ := strconv.Atoi(c.Query("appuserid"))
	Pageno, _ := strconv.Atoi(c.Query("pageno"))
	Pagesize, _ := strconv.Atoi(c.Query("pagesize"))
	stat := c.Query("status")
	fdate := c.Query("fromdate")
	tdate := c.Query("todate")

	if tid != 0 {

		result = domain.GetTenantOrdersv2(stat, fdate, tdate, tid, Pageno, Pagesize)

	} else if pid != 0 {

		result = domain.GetPartnerOrdersv2(stat, fdate, tdate, pid, Pageno, Pagesize)

	} else if cid != 0 {

		result = domain.GetCustomerOrdersv2(stat, fdate, tdate, cid, mid, Pageno, Pagesize)

	} else if aid != 0 {

		result = domain.GetAdminOrdersv2(stat, fdate, tdate, aid, Pageno, Pagesize)

	} else if uid != 0 {

		result = domain.GetUserOrdersv2(stat, fdate, tdate, uid, Pageno, Pagesize)

	} else {

		result = domain.GetAllOrdersv2(stat, fdate, tdate, Pageno, Pagesize)

	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": result,
	})

}

func GetOrders(c *fiber.Ctx) error {
	var result []models.OrderInfo
	aid, _ := strconv.Atoi(c.Query("applocationid"))
	tid, _ := strconv.Atoi(c.Query("tenantid"))
	pid, _ := strconv.Atoi(c.Query("partnerid"))
	cid, _ := strconv.Atoi(c.Query("customerid"))
	mid, _ := strconv.Atoi(c.Query("moduleid"))
	lid, _ := strconv.Atoi(c.Query("locationid"))
	uid, _ := strconv.Atoi(c.Query("appuserid"))
	configid, _ := strconv.Atoi(c.Query("configid"))

	stat := c.Query("status")
	fdate := c.Query("fromdate")
	tdate := c.Query("todate")
	pageno, _ := strconv.Atoi(c.Query("pageno"))
	pagesize, _ := strconv.Atoi(c.Query("pagesize"))
	keyword := c.Query("keyword")

	if pageno <= 0 {
		pageno = 1
	}
	if pagesize <= 0 {
		pagesize = 10
	}

	var info models.DeliveryQuery
	info.Partnerid = pid
	info.Tenantid = tid
	info.UserID = uid
	info.Appuserid = uid
	info.Locationid = lid
	info.Configid = configid
	info.Fromdate = fdate
	info.ToDate = tdate
	info.Status = stat
	info.Pageno = pageno
	info.Pagesize = pagesize
	info.Keyword = keyword
	info.Applocationid = aid

	if tid != 0 && lid != 0 && aid != 0 {
		result = domain.GetTenantLocationAppOrders(info)
	} else if tid != 0 && lid != 0 {
		result = domain.GetTenantLocationOrders(info)
	} else if tid != 0 && aid != 0 {
		result = domain.GetTenantAppOrders(info)
	} else if tid != 0 {
		result = domain.GetTenantOrders(info)
	} else if aid != 0 {
		result = domain.GetAppOrders(info)
	} else if pid != 0 {
		result = domain.GetPartnerOrders(stat, fdate, tdate, pid, pageno, pagesize, keyword)
	} else if cid != 0 {
		result = domain.GetCustomerOrders(stat, fdate, tdate, cid, mid, pageno, pagesize, keyword)
	} else if lid != 0 {
		result = domain.GetLocationOrders(stat, fdate, tdate, lid, pageno, pagesize, keyword)
	} else if uid != 0 {
		result = domain.GetUserOrders(stat, fdate, tdate, uid, pageno, pagesize, keyword)
	} else {
		result = domain.GetAllOrders(stat, fdate, tdate, aid, pageno, pagesize, keyword)
	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": result,
	})
}

func GetOrdersbytenant(c *fiber.Ctx) error {

	tid, _ := strconv.Atoi(c.Query("tenantid"))
	stat := c.Query("status")
	fdate := c.Query("fromdate")
	tdate := c.Query("todate")

	var q1 string
	var data []models.OrderInfo

	var params []interface{}
	params = append(params, tid)

	if stat != "" {

		q1 = `SELECT a.orderheaderid,a.tenantid,a.partnerid,a.locationid,a.orderid,a.orderdate,a.orderstatus,a.pending,
			a.customerid,b.firstname AS customername,b.contactno,a.pickupaddress,a.pickuplat,a.pickuplong,
			a.ordernotes,a.deliverylocationid,a.deliveryaddress, a.deliverylat ,a.deliverylong
			FROM orders a
			INNER JOIN customers b ON a.customerid=b.customerid
			where a.tenantid=? and a.orderstatus='created' and orderdate between ? and ? order by a.orderheaderid desc`
		params = append(params, fdate, tdate)

	} else {

		q1 = `SELECT a.orderheaderid,a.tenantid,a.partnerid,a.locationid,a.orderid,a.orderdate,a.orderstatus,a.pending,
			a.customerid,b.firstname AS customername,b.contactno,a.pickupaddress,a.pickuplat,a.pickuplong,
			a.ordernotes,a.deliverylocationid,a.deliveryaddress, a.deliverylat ,a.deliverylong
			FROM orders a
			INNER JOIN customers b ON a.customerid=b.customerid
			where a.tenantid=? and a.orderstatus='cancelled' and orderdate between ? and ? order by a.orderheaderid desc`
		params = append(params, fdate, tdate)

	}

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

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": data,
	})

}

func GetTenantLocationSummary(c *fiber.Ctx) error {

	tid, _ := strconv.Atoi(c.Query("tenantid"))
	lid, _ := strconv.Atoi(c.Query("locationid"))

	var data []models.TenantLocationSummary
	var q1 string

	if lid == 0 {

		q1 = `SELECT 
			a.locationid,b.locationname,b.suburb as locationsuburb,	
			COUNT(*) AS total,
			COUNT(CASE WHEN a.orderstatus = 'created' THEN 1 ELSE NULL END) AS created,
			COUNT(CASE WHEN a.orderstatus = 'pending' THEN 1 ELSE NULL END) AS pending,
			COUNT(CASE WHEN a.orderstatus = 'delivered' THEN 1 ELSE NULL END) AS delivered,
			COUNT(CASE WHEN a.orderstatus = 'cancelled' THEN 1 ELSE NULL END) AS cancelled
			FROM orders a inner JOIN tenantlocations b ON a.locationid=b.locationid
			WHERE a.tenantid= ? GROUP BY a.locationid`
		db.DB.Raw(q1, tid).Find(&data)

	} else {

		q1 = `SELECT 
			a.locationid,b.locationname,	
			COUNT(*) AS total,
			COUNT(CASE WHEN a.orderstatus = 'created' THEN 1 ELSE NULL END) AS created,
			COUNT(CASE WHEN a.orderstatus = 'pending' THEN 1 ELSE NULL END) AS pending,
			COUNT(CASE WHEN a.orderstatus = 'delivered' THEN 1 ELSE NULL END) AS delivered,
			COUNT(CASE WHEN a.orderstatus = 'cancelled' THEN 1 ELSE NULL END) AS cancelled
			FROM orders a inner JOIN tenantlocations b ON a.locationid=b.locationid
			WHERE a.tenantid= ? and a.locationid= ? GROUP BY a.locationid`
		db.DB.Raw(q1, tid, lid).Find(&data)

	}

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

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

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": data,
	})
}

func GetlocationOrderSummary(c *fiber.Ctx) error {
	tenantIDStr := c.Query("tenantid")
	tenantID, _ := strconv.Atoi(tenantIDStr)

	var data []models.Ordersummary
	var q1 string
	var params []interface{}

	q1 = `
		SELECT b.locationname,a.applocationid, COUNT(*) AS total,
			SUM(CASE WHEN orderstatus = 'created' THEN 1 ELSE 0 END) AS created,
			SUM(CASE WHEN orderstatus = 'pending' THEN 1 ELSE 0 END) AS pending,
			SUM(CASE WHEN orderstatus = 'processing' THEN 1 ELSE 0 END) AS processing,
			SUM(CASE WHEN orderstatus = 'delivered' THEN 1 ELSE 0 END) AS delivered,
			SUM(CASE WHEN orderstatus = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
		FROM orders a 
		INNER JOIN app_location b ON a.applocationid=b.applocationid`

	if tenantID != 0 {
		q1 += " WHERE a.tenantid = ?"
		params = append(params, tenantID)
	}

	q1 += " GROUP BY a.applocationid, b.locationname"

	if err := db.DB.Raw(q1, params...).Scan(&data).Error; err != nil {
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"status":  false,
			"code":    http.StatusInternalServerError,
			"message": err.Error(),
		})
	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": data,
	})
}

func GetOrderInsight(c *fiber.Ctx) error {

	tenantIDStr := c.Query("tenantid")
	tenantID, _ := strconv.Atoi(tenantIDStr)

	var locations []models.OrderInsight
	var params []interface{}

	// Query 1: Get distinct locations
	q1 := `
		SELECT DISTINCT 
			a.applocationid, 
			b.locationname
		FROM orders a
		INNER JOIN app_location b 
			ON a.applocationid = b.applocationid
		WHERE b.status = 'Active'
	`

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

	// ✅ OLD STYLE DB USAGE
	if err := db.DB.Raw(q1, params...).Scan(&locations).Error; err != nil {
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": err.Error(),
			"status":  false,
		})
	}

	// Query 2: Monthly order counts
	q2 := `
		SELECT 
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 1 THEN 1 ELSE 0 END), 0) AS jan,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 2 THEN 1 ELSE 0 END), 0) AS feb,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 3 THEN 1 ELSE 0 END), 0) AS mar,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 4 THEN 1 ELSE 0 END), 0) AS apr,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 5 THEN 1 ELSE 0 END), 0) AS may,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 6 THEN 1 ELSE 0 END), 0) AS jun,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 7 THEN 1 ELSE 0 END), 0) AS jul,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 8 THEN 1 ELSE 0 END), 0) AS aug,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 9 THEN 1 ELSE 0 END), 0) AS sep,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 10 THEN 1 ELSE 0 END), 0) AS oct,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 11 THEN 1 ELSE 0 END), 0) AS nov,
			COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 12 THEN 1 ELSE 0 END), 0) AS dece
		FROM orders a
		WHERE a.applocationid = ?
		  AND EXTRACT(YEAR FROM a.orderdate) = EXTRACT(YEAR FROM CURRENT_DATE)
	`

	if tenantID != 0 {
		q2 += " AND a.tenantid = ?"
	}

	// Fetch monthly data per location
	for i := range locations {
		var orderMonths models.Ordermonths

		if tenantID != 0 {
			if err := db.DB.Raw(
				q2,
				locations[i].Applocationid,
				tenantID,
			).Scan(&orderMonths).Error; err != nil {
				return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
					"code":    http.StatusInternalServerError,
					"message": err.Error(),
					"status":  false,
				})
			}
		} else {
			if err := db.DB.Raw(
				q2,
				locations[i].Applocationid,
			).Scan(&orderMonths).Error; err != nil {
				return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
					"code":    http.StatusInternalServerError,
					"message": err.Error(),
					"status":  false,
				})
			}
		}

		locations[i].Ordermonths = &orderMonths
	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": locations,
	})
}

// func GetOrderInsight(c *fiber.Ctx) error {

// 	var data []models.OrderInsight

// 	q1 := `SELECT distinct a.applocationid,b.locationname from orders a INNER JOIN app_location b ON a.applocationid=b.applocationid
// 	WHERE b.status='Active'`

// 	q2 := `SELECT a.applocationid,b.locationname,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 1 THEN 1 ELSE 0 END) AS jan,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 2 THEN 1 ELSE 0 END) AS feb,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 3 THEN 1 ELSE 0 END) AS mar,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 4 THEN 1 ELSE 0 END) AS apr,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 5 THEN 1 ELSE 0 END) AS may,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 6 THEN 1 ELSE 0 END) AS jun,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 7 THEN 1 ELSE 0 END) AS jul,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 8 THEN 1 ELSE 0 END) AS aug,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 9 THEN 1 ELSE 0 END) AS sep,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 10 THEN 1 ELSE 0 END) AS oct,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 11 THEN 1 ELSE 0 END) AS nov,
// 	SUM(CASE WHEN MONTH(a.orderdate) = 12 THEN 1 ELSE 0 END) AS dece
// 	FROM orders a
// 	INNER JOIN app_location b ON a.applocationid=b.applocationid
// 	where b.status='Active' and a.applocationid=? and year(a.orderdate)=year(curdate())
// 	GROUP BY b.locationname, a.applocationid
// 	ORDER BY month(a.orderdate) asc`

// 	err := db.DB.Raw(q1).Preload("Ordermonths", func(db *gorm.DB) *gorm.DB {
// 		return db.Raw(q2)
// 	}).Find(&data).Error

// 	//err := db.DB.Raw(q1).Find(&data).Error

// 	if err != nil {

// 		return c.JSON(fiber.Map{
// 			"code":    http.StatusConflict,
// 			"message": err.Error(),
// 			"status":  false,
// 		})

// 	}

// 	return c.JSON(fiber.Map{
// 			"code":    http.StatusOK,
// 			"message": "Success",
// 			"status":  true,
// 			"details": data,
// 		})

// }

func GetOrderSummary(c *fiber.Ctx) error {
	tid, _ := strconv.Atoi(c.Query("tenantid"))
	pid, _ := strconv.Atoi(c.Query("partnerid"))
	cid, _ := strconv.Atoi(c.Query("customerid"))
	lid, _ := strconv.Atoi(c.Query("locationid"))
	aid, _ := strconv.Atoi(c.Query("applocationid"))
	fdate := c.Query("fromdate")
	tdate := c.Query("todate")

	var data models.Ordersummary
	var q1 string

	var params []interface{}
	q1 = base + ` 
FROM orders a
INNER JOIN tenants b 
  ON a.tenantid = b.tenantid
WHERE a.categoryid <> 2 
AND b.moduleid = 6`
	if tid != 0 {
		q1 += " AND a.tenantid=?"
		params = append(params, tid)
	}
	if lid != 0 && (tid != 0 || pid == 0) {
		q1 += " AND a.locationid=?"
		params = append(params, lid)
	}
	if pid != 0 {
		q1 += " AND a.partnerid=?"
		params = append(params, pid)
	}
	if cid != 0 {
		q1 += " AND a.customerid=?"
		params = append(params, cid)
	}
	if aid != 0 {
		q1 += " AND a.applocationid=?"
		params = append(params, aid)
	}
	if fdate != "" && tdate != "" {
		q1 += " AND a.deliverytime >= ? AND a.deliverytime < (?::date + INTERVAL '1 day')"
		params = append(params, fdate, tdate)
	}

	utils.Logger.Debugw("CancelOrder-Query", "q1", q1)

	if err := db.DB.Raw(q1, params...).Scan(&data).Error; err != nil {
		return c.JSON(fiber.Map{
			"code":    500,
			"status":  false,
			"message": err.Error(),
		})
	}

	return c.JSON(fiber.Map{
		"code":    200,
		"status":  true,
		"message": "Success",
		"details": data,
	})
}

func GetCustomerOrderCount(cid int) int {

	var count int
	q1 := "SELECT count(*) as count FROM orders WHERE customerid=?"

	db.DB.Raw(q1, cid).Find(&count)
	return count

}

func UpdateOrderStatus(input models.Orders) error {

	tx := db.DB.Begin()

	t1 := tx.Where("orderheaderid=?", input.Orderheaderid).Updates(&input)
	if t1.Error != nil {
		utils.Error("UpdateOrderStatus t1 updates error", "error", t1.Error)
		tx.Rollback()
		return tx.Error
	}

	tx.Commit()
	return nil

}

// func CreateOrderv3(c *fiber.Ctx) error {
// 	type OrderWrapper struct {
// 		Orders models.Orders `json:"orders"`
// 	}

// 	var wrapper OrderWrapper

// 	// Parse JSON body
// 	if err := c.BodyParser(&wrapper); err != nil {
// 		utils.Logger.Errorw("BodyParser error", "error", err)
// 		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
// 			"code":    http.StatusBadRequest,
// 			"message": "Invalid request body",
// 			"status":  false,
// 		})
// 	}

// 	data := wrapper.Orders

// 	// Check required field
// 	if data.Tenantid == 0 {
// 		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
// 			"code":    http.StatusBadRequest,
// 			"message": "Tenant ID is required",
// 			"status":  false,
// 		})
// 	}

// 	// Default values if missing
// 	if strings.TrimSpace(data.Orderdate) == "" {
// 		data.Orderdate = time.Now().Format("2006-01-02 15:04:05")
// 	}

// 	if strings.TrimSpace(data.Deliverytime) == "" {
// 		data.Deliverytime = time.Now().Format("2006-01-02 15:04:05")
// 	}

// 	// Start transaction
// 	tx := db.DB.Begin()

// 	// Generate Order ID
// 	data.Orderid = domain.GetSequenceno(data.Tenantid, "ORD")

// 	// Insert order
// 	if err := tx.Create(&data).Error; err != nil {
// 		tx.Rollback()
// 		log.Println("Create order error:", err)
// 		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
// 			"code":    http.StatusInternalServerError,
// 			"message": "Failed to create order",
// 			"status":  false,
// 		})
// 	}

// 	// Insert order items
// 	for _, item := range data.Items {
// 		item.Orderheaderid = data.Orderheaderid
// 		item.Tenantid = data.Tenantid
// 		item.Locationid = data.Locationid

// 		if err := tx.Table("orderdetails").Create(&item).Error; err != nil {
// 			tx.Rollback()
// 			log.Println("Insert item error:", err)
// 			return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
// 				"code":    http.StatusInternalServerError,
// 				"message": "Failed to insert order item",
// 				"status":  false,
// 			})
// 		}
// 	}

// 	// Update sequence
// 	if err := domain.UpdateSeqno(data.Tenantid, "ORD"); err != nil {
// 		log.Println("Sequence update error:", err)
// 	}

// 	// Commit
// 	if err := tx.Commit().Error; err != nil {
// 		log.Println("Commit failed:", err)
// 		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
// 			"code":    http.StatusInternalServerError,
// 			"message": "Transaction failed",
// 			"status":  false,
// 		})
// 	}

// 	// Return order data
// 	var order models.Orders
// 	if err := db.DB.Where("orderheaderid = ?", data.Orderheaderid).First(&order).Error; err != nil {
// 		log.Println("Fetch order error:", err)
// 		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
// 			"code":    http.StatusInternalServerError,
// 			"message": "Failed to fetch order details",
// 			"status":  false,
// 		})
// 	}

// 	// 🔽 Manually fetch order items and assign to Items
// 	var items []models.OrderDetail
// 	if err := db.DB.Table("orderdetails").Where("orderheaderid = ?", data.Orderheaderid).Find(&items).Error; err != nil {
// 		log.Println("Fetch order items error:", err)
// 	}
// 	order.Items = items

// 	return c.Status(http.StatusOK).JSON(fiber.Map{
// 		"code":    http.StatusOK,
// 		"message": "Order created successfully",
// 		"status":  true,
// 		"details": order,
// 	})

// }

func CreateOrderv3(c *fiber.Ctx) error {
	type OrderWrapper struct {
		Orders models.Orders `json:"orders"`
	}

	var wrapper OrderWrapper

	if err := c.BodyParser(&wrapper); err != nil {
		utils.Logger.Errorw("BodyParser error", "error", err)
		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
			"code":    http.StatusBadRequest,
			"message": "Invalid request body",
			"status":  false,
		})
	}

	data := wrapper.Orders

	if data.Tenantid == 0 {
		return c.Status(http.StatusBadRequest).JSON(fiber.Map{
			"code":    http.StatusBadRequest,
			"message": "Tenant ID is required",
			"status":  false,
		})
	}

	if strings.TrimSpace(data.Orderdate) == "" {
		data.Orderdate = time.Now().Format("2006-01-02 15:04:05")
	}

	if strings.TrimSpace(data.Deliverytime) == "" {
		data.Deliverytime = time.Now().Format("2006-01-02 15:04:05")
	}

	tx := db.DB.Begin()

	data.Orderid = domain.GetSequenceno(data.Tenantid, "ORD")

	if err := tx.Create(&data).Error; err != nil {
		tx.Rollback()
		utils.Logger.Errorw("Create order error", "error", err)
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Failed to create order",
			"status":  false,
		})
	}

	for _, item := range data.Items {
		item.Orderheaderid = data.Orderheaderid
		item.Tenantid = data.Tenantid
		item.Locationid = data.Locationid

		if err := tx.Table("orderdetails").Create(&item).Error; err != nil {
			tx.Rollback()
			utils.Logger.Errorw("Insert item error", "error", err)
			return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
				"code":    http.StatusInternalServerError,
				"message": "Failed to insert order item",
				"status":  false,
			})
		}

		stock := models.Productstock{
			Tenantid:   data.Tenantid,
			Stockdate:  time.Now(),
			Locationid: data.Locationid,
			Productid:  item.Productid,
			Quantity:   int(item.Orderqty),
			Stocktype:  "out",
			Status:     "Active",
		}

		if err := tx.Table("productstocks").Create(&stock).Error; err != nil {
			tx.Rollback()
			utils.Logger.Errorw("Insert product stock error", "error", err)
			return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
				"code":    http.StatusInternalServerError,
				"message": "Failed to insert product stock",
				"status":  false,
			})
		}
	}

	if err := domain.UpdateSeqno(data.Tenantid, "ORD"); err != nil {
		utils.Logger.Errorw("Sequence update error", "error", err)
	}

	if err := tx.Commit().Error; err != nil {
		utils.Logger.Errorw("Commit failed", "error", err)
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Transaction failed",
			"status":  false,
		})
	}

	var order models.Orders
	if err := db.DB.Where("orderheaderid = ?", data.Orderheaderid).First(&order).Error; err != nil {
		utils.Logger.Errorw("Fetch order error", "error", err)
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"code":    http.StatusInternalServerError,
			"message": "Failed to fetch order details",
			"status":  false,
		})
	}

	var items []models.OrderDetail
	if err := db.DB.Table("orderdetails").Where("orderheaderid = ?", data.Orderheaderid).Find(&items).Error; err != nil {
		utils.Logger.Errorw("Fetch order items error", "error", err)
	}
	order.Items = items

	return c.Status(http.StatusOK).JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Order created successfully",
		"status":  true,
		"details": order,
	})
}

func GetOrderDetails(c *fiber.Ctx) error {
	orderHeaderIDStr := c.Query("orderheaderid")
	if orderHeaderIDStr == "" {
		return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{
			"code":    400,
			"message": "orderheaderid is required",
			"status":  false,
			"details": []interface{}{},
		})
	}

	orderHeaderID, err := strconv.Atoi(orderHeaderIDStr)
	if err != nil {
		return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{
			"code":    400,
			"message": "invalid orderheaderid",
			"status":  false,
			"details": []interface{}{},
		})
	}

	details, orderAmount, totalTaxAmount, err := domain.GetOrderDetailsByHeaderID(orderHeaderID)
	if err != nil {
		return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{
			"code":    500,
			"message": "Failed to fetch order details",
			"status":  false,
			"details": []interface{}{},
		})
	}

	return c.JSON(fiber.Map{
		"code": 200,
		"pricedetails": fiber.Map{
			"orderamount":    orderAmount,
			"totaltaxamount": totalTaxAmount,
		},
		"details": details,
		"message": "Success",
		"status":  true,
	})
}

func GetCustomerOrders(c *fiber.Ctx) error {
	customerID := c.Query("customerid")
	tenantID := c.Query("tenantid")
	moduleID := c.Query("moduleid")
	fromDate := c.Query("fromdate")
	toDate := c.Query("todate")
	orderStatus := c.Query("orderstatus")
	keyword := c.Query("keyword")
	pageNo, _ := strconv.Atoi(c.Query("pageno", "1"))
	pageSize, _ := strconv.Atoi(c.Query("pagesize", "10"))

	if pageNo < 1 {
		pageNo = 1
	}
	if pageSize < 1 {
		pageSize = 10
	}
	offset := (pageNo - 1) * pageSize

	// Ensure response is [] not null
	orders := make([]models.CustomerOrder, 0)
	var orderdetails models.OrderDetails

	baseQuery := `
	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 deliverytime,
		a.pending, a.processing, a.ready, a.delivered AS delivered, a.cancelled,
		a.deliverycharge, a.kms, a.customerid, 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, a.taxamount,
		b.tenantname, b.tenanttoken, b.primarycontact AS tenantcontactno, b.postcode AS tenantpostcode, b.suburb AS tenantsuburb, b.city AS tenantcity,
		c.locationname, c.contactno AS locationcontactno, c.postcode AS locationpostcode, c.suburb AS locationsuburb, c.city AS locationcity,
		d.locationname AS applocation, f.orderstatus AS deliverystatus,
		f.deliveryid, f.assigntime, f.starttime, f.arrivaltime, f.pickuptime, f.deliverytime AS finaldeliverytime, f.canceltime,
		f.droplat, f.droplon, g.firstname AS rider
	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 deliveries f ON a.orderheaderid = f.orderheaderid
	LEFT JOIN app_users g ON f.userid = g.userid
	LEFT JOIN orderdetails h ON h.orderheaderid = a.orderheaderid
	WHERE 1=1
	`

	params := []interface{}{}

	if customerID != "" {
		baseQuery += " AND a.customerid = ?"
		params = append(params, customerID)
	}
	if tenantID != "" && tenantID != "0" {
		baseQuery += " AND a.tenantid = ?"
		params = append(params, tenantID)
	}
	if moduleID != "" {
		baseQuery += " AND a.moduleid = ?"
		params = append(params, moduleID)
	}
	if fromDate != "" && toDate != "" {
		baseQuery += " AND a.orderdate::date BETWEEN ?::date AND ?::date"
		params = append(params, fromDate, toDate)
	}
	if orderStatus != "" {
		baseQuery += " AND a.orderstatus = ?"
		params = append(params, orderStatus)
	}
	if keyword != "" {
		baseQuery += " AND h.productname LIKE ?"
		params = append(params, "%"+keyword+"%")
	}

	// ✅ Pagination enabled
	baseQuery += " ORDER BY a.orderheaderid DESC LIMIT ? OFFSET ?"
	params = append(params, pageSize, offset)

	utils.Logger.Debugw("Executing query", "query", baseQuery, "params", params)

	if err := db.DB.Raw(baseQuery, params...).Scan(&orders).Error; err != nil {
		utils.Logger.Errorw("Query execution error", "error", err)
		return c.Status(500).JSON(fiber.Map{
			"code":    500,
			"status":  false,
			"message": "Failed to fetch customer orders",
			"error":   err.Error(),
		})
	}

	for i := range orders {
		orderDetails, orderAmount, totalTaxAmount, _ := domain.GetOrderDetailsByHeaderID(orders[i].Orderheaderid)
		orders[i].OrderDetails = orderDetails
		orders[i].Orderamount = orderAmount
		orderdetails.Totaltaxamount = totalTaxAmount
	}

	return c.JSON(fiber.Map{
		"code":    200,
		"status":  true,
		"message": "Customer orders fetched successfully",
		"data":    orders,
	})
}

func GetOrderInsightDaily(c *fiber.Ctx) error {
	dbConn, ok := c.Locals("DB").(*gorm.DB)
	if !ok || dbConn == nil {
		return c.JSON(fiber.Map{
			"code":    500,
			"message": "Database connection not found",
			"status":  false,
		})
	}

	tenantIDStr := c.Query("tenantid")
	tenantID, _ := strconv.Atoi(tenantIDStr)

	var locations []models.OrderInsightv1
	var params []interface{}

	// ✅ Query 1: Get ALL locations (even without orders)
	q1 := `SELECT b.locationid, b.locationname
		   FROM tenantlocations b
		   WHERE b.status = 'Active'`

	if tenantID != 0 {
		q1 += " AND b.tenantid = ?"
		params = append(params, tenantID)
	}

	if err := dbConn.Raw(q1, params...).Scan(&locations).Error; err != nil {
		return c.JSON(fiber.Map{
			"code":    http.StatusConflict,
			"message": err.Error(),
			"status":  false,
		})
	}

	// ✅ Query 2: Fetch order insights for each location
	q2 := `SELECT 
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 1 THEN 1 ELSE 0 END), 0) AS jan,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 2 THEN 1 ELSE 0 END), 0) AS feb,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 3 THEN 1 ELSE 0 END), 0) AS mar,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 4 THEN 1 ELSE 0 END), 0) AS apr,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 5 THEN 1 ELSE 0 END), 0) AS may,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 6 THEN 1 ELSE 0 END), 0) AS jun,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 7 THEN 1 ELSE 0 END), 0) AS jul,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 8 THEN 1 ELSE 0 END), 0) AS aug,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 9 THEN 1 ELSE 0 END), 0) AS sep,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 10 THEN 1 ELSE 0 END), 0) AS oct,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 11 THEN 1 ELSE 0 END), 0) AS nov,
		COALESCE(SUM(CASE WHEN EXTRACT(MONTH FROM a.orderdate) = 12 THEN 1 ELSE 0 END), 0) AS dece
		FROM orders a
		WHERE a.locationid = ? AND EXTRACT(YEAR FROM a.orderdate) = EXTRACT(YEAR FROM CURRENT_DATE)`

	if tenantID != 0 {
		q2 += " AND a.tenantid = ?"
	}

	// ✅ Attach monthly order counts for each location
	for i := range locations {
		var orderMonths models.Ordermonths

		if tenantID != 0 {
			if err := dbConn.Raw(q2, locations[i].Locationid, tenantID).Scan(&orderMonths).Error; err != nil {
				return c.JSON(fiber.Map{
					"code":    http.StatusConflict,
					"message": err.Error(),
					"status":  false,
				})
			}
		} else {
			if err := dbConn.Raw(q2, locations[i].Locationid).Scan(&orderMonths).Error; err != nil {
				return c.JSON(fiber.Map{
					"code":    http.StatusConflict,
					"message": err.Error(),
					"status":  false,
				})
			}
		}

		locations[i].Ordermonths = &orderMonths
	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": locations,
	})
}

func GetOrderSummaryDaily(c *fiber.Ctx) error {
	tid, _ := strconv.Atoi(c.Query("tenantid"))
	pid, _ := strconv.Atoi(c.Query("partnerid"))
	cid, _ := strconv.Atoi(c.Query("customerid"))
	lid, _ := strconv.Atoi(c.Query("locationid"))
	fdate := c.Query("fromdate")
	tdate := c.Query("todate")

	var data []models.Ordersummarydaily
	var q1 string

	// 👇 join with tenants instead of locations
	baseWithTenant := base + `, t.tenantid, t.tenantname
		FROM orders o
		INNER JOIN tenants t ON o.tenantid = t.tenantid`

	// Filters
	var params []interface{}
	q1 = baseWithTenant

	if tid != 0 {
		q1 += " WHERE o.tenantid = ?"
		params = append(params, tid)
	} else if pid != 0 {
		q1 += " WHERE o.partnerid = ?"
		params = append(params, pid)
	} else if cid != 0 {
		q1 += " WHERE o.customerid = ?"
		params = append(params, cid)
	} else if lid != 0 {
		q1 += " WHERE o.locationid = ?"
		params = append(params, lid)
	} else {
		q1 += " WHERE 1=1"
	}

	if fdate != "" && tdate != "" {
		q1 += " AND o.orderdate::date BETWEEN ?::date AND ?::date"
		params = append(params, fdate, tdate)
	}

	// 👇 group by tenantid & tenantname
	q1 += ` GROUP BY t.tenantid, t.tenantname`

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

	if err := db.DB.Raw(q1, params...).Scan(&data).Error; err != nil {
		return c.JSON(fiber.Map{
			"code":    http.StatusConflict,
			"message": err.Error(),
			"status":  false,
		})
	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": data,
	})
}

func GetLocationOrderSummaryDaily(c *fiber.Ctx) error {
	tenantIDStr := c.Query("tenantid")
	tenantID, _ := strconv.Atoi(tenantIDStr)

	var data []models.Ordersummarylocation
	var q1 string
	var params []interface{}

	q1 = `
		SELECT 
			l.locationid, l.locationname, 
			COALESCE(COUNT(o.orderid), 0) AS total,
			COALESCE(SUM(CASE WHEN o.orderstatus = 'created' THEN 1 ELSE 0 END), 0) AS created,
			COALESCE(SUM(CASE WHEN o.orderstatus = 'pending' THEN 1 ELSE 0 END), 0) AS pending,
			COALESCE(SUM(CASE WHEN o.orderstatus = 'processing' THEN 1 ELSE 0 END), 0) AS processing,
			COALESCE(SUM(CASE WHEN o.orderstatus = 'delivered' THEN 1 ELSE 0 END), 0) AS delivered,
			COALESCE(SUM(CASE WHEN o.orderstatus = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled
		FROM tenantlocations l
		LEFT JOIN orders o 
			ON l.locationid = o.locationid 
			AND l.tenantid = o.tenantid
	`

	if tenantID != 0 {
		q1 += " WHERE l.tenantid = ?"
		params = append(params, tenantID)
	}

	q1 += " GROUP BY l.locationid, l.locationname ORDER BY l.locationid"

	if err := db.DB.Raw(q1, params...).Scan(&data).Error; err != nil {
		return c.Status(http.StatusInternalServerError).JSON(fiber.Map{
			"status":  false,
			"code":    http.StatusInternalServerError,
			"message": err.Error(),
		})
	}

	return c.JSON(fiber.Map{
		"code":    http.StatusOK,
		"message": "Success",
		"status":  true,
		"details": data,
	})
}

func GetCustomerOrderByLocation(c *fiber.Ctx) error {
	cid, _ := strconv.Atoi(c.Query("customerid"))

	result, err := domain.GetCustomerOrderByLocation(cid)
	if err != nil {
		return c.JSON(fiber.Map{
			"status":  false,
			"code":    http.StatusInternalServerError,
			"message": err.Error(),
			"details": []interface{}{}, // return [] on error
		})
	}

	return c.JSON(fiber.Map{
		"status":  true,
		"code":    http.StatusOK,
		"message": "Successful",
		"details": result,
	})
}