package domain

import (
	"errors"
	"nearle/db"
	"nearle/models"
	"nearle/utils"
	"strconv"
	"strings"
	"time"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

func GetProductCategory() []models.ProductCategory {
	var data []models.ProductCategory

	q1 := `SELECT * FROM productcategories WHERE moduleid = 2 and status = "Active"`
	db.DB.Raw(q1).Scan(&data)

	return data
}

func GetProductSubCategory(categoryID, tenantID int) []models.ProductSubCategory {
	var data []models.ProductSubCategory
	var query strings.Builder
	var args []interface{}

	query.WriteString("SELECT * FROM productsubcategories WHERE 1=1")

	if tenantID != 0 {
		query.WriteString(" AND tenantid = ?")
		args = append(args, tenantID)
	}
	if categoryID != 0 {
		query.WriteString(" AND categoryid = ?")
		args = append(args, categoryID)
	}

	db.DB.Raw(query.String(), args...).Scan(&data)
	return data
}

func CreateProduct(product *models.Products) error {

	tx := db.DB.Begin()

	t1 := tx.Create(&product)
	if t1.Error != nil {
		utils.Error("CreateProduct error", "error", t1.Error)
		tx.Rollback()
		return t1.Error
	}

	err := tx.Commit().Error
	if err != nil {

		return err

	}

	return nil

}

func CreateProductVariant(input models.Productvariant) error {

	tx := db.DB.Begin()

	t1 := tx.Create(&input)
	if t1.Error != nil {
		utils.Error("CreateProductVariant error", "error", t1.Error)
		tx.Rollback()
		return t1.Error
	}

	err := tx.Commit().Error
	if err != nil {

		return err

	}

	return nil

}

func FetchFilteredProducts(categoryID, subcategoryID, productID, applocationID, tenantID, locationID int, keyword, productStatus, approve string, pageno, pagesize int) ([]models.Tenantproducts, error) {
	offset := (pageno - 1) * pagesize
	results := make([]models.Tenantproducts, 0)

	if tenantID == 0 {
		return results, nil
	}

	var tenant models.TenantInfo
	err := db.DB.Table("tenants").Where("tenantid = ?", tenantID).First(&tenant).Error
	if err != nil {
		return nil, err
	}

	var products []models.Products
	query := db.DB.
		Debug().
		Table("products a").
		Select(`
			a.*, 
			c.categoryname, 
			d.subcatname AS subcategoryname,
			e.locationid,
			COALESCE(
				SUM(CASE WHEN e.stocktype = 'in'  THEN e.quantity ELSE 0 END) -
				SUM(CASE WHEN e.stocktype = 'out' THEN e.quantity ELSE 0 END),
				0
			) AS quantity
		`).
		Joins("LEFT JOIN productcategories c ON a.categoryid = c.categoryid").
		Joins("LEFT JOIN productsubcategories d ON a.subcategoryid = d.subcatid").
		Joins("LEFT JOIN productstocks e ON e.productid = a.productid").
		Where("a.tenantid = ?", tenantID).
		Group("a.productid"). // Important for SUM
		Order("a.productid DESC")

	if categoryID != 0 {
		query = query.Where("a.categoryid = ?", categoryID)
	}
	if subcategoryID != 0 {
		query = query.Where("a.subcategoryid = ?", subcategoryID)
	}
	if productID != 0 {
		query = query.Where("a.productid = ?", productID)
	}
	if productStatus != "" {
		query = query.Where("a.productstatus = ?", productStatus)
	}
	if locationID != 0 {
		query = query.Where("e.locationid = ?", locationID)
	}
	if approve != "" {
		query = query.Where("a.approve = ?", approve)
	}

	if keyword != "" {
		like := "%" + strings.ToLower(keyword) + "%"
		query = query.Where(
			db.DB.Where("LOWER(a.productname) LIKE ?", like).
				Or("LOWER(a.unitvalue) LIKE ?", like).
				Or("LOWER(CAST(a.productcost AS CHAR)) LIKE ?", like),
		)
	}

	if pagesize > 0 && offset >= 0 {
		query = query.Limit(pagesize).Offset(offset)
	}

	err = query.Scan(&products).Error
	if err != nil {
		return nil, err
	}

	if products == nil {
		products = []models.Products{}
	}

	results = append(results, models.Tenantproducts{
		Tenant:   tenant,
		Products: products,
	})

	return results, nil
}

func UpdateProduct(input models.Products) error {

	tx := db.DB.Begin()

	t1 := tx.Where("productid=?", input.Productid).Updates(&input)
	if t1.Error != nil {

		tx.Rollback()
		return t1.Error
	}

	err := tx.Commit().Error
	if err != nil {

		return err

	}

	return nil

}

func Getproductcount(tenantid, categoryid, subcategory int, approve string) ([]models.Productcount, error) {
	var data []models.Productcount

	baseQuery := `
		SELECT 
			COUNT(*) AS total,
			SUM(CASE WHEN a.productstatus = 'available' THEN 1 ELSE 0 END) AS available,
			SUM(CASE WHEN a.productstatus = 'outofstock' THEN 1 ELSE 0 END) AS outofstock
		FROM products a
		WHERE 1 = 1
	`

	var conditions []string
	var params []interface{}

	if tenantid != 0 {
		conditions = append(conditions, "a.tenantid = ?")
		params = append(params, tenantid)
	}
	if categoryid != 0 {
		conditions = append(conditions, "a.categoryid = ?")
		params = append(params, categoryid)
	}
	if subcategory != 0 {
		conditions = append(conditions, "a.subcategoryid = ?")
		params = append(params, subcategory)
	}
	if approve != "" {
		conditions = append(conditions, "a.approve = ?")
		params = append(params, approve)
	}

	if len(conditions) > 0 {
		baseQuery += " AND " + strings.Join(conditions, " AND ")
	}

	if err := db.DB.Raw(baseQuery, params...).Scan(&data).Error; err != nil {
		return nil, err
	}


	return data, nil
}

func GetproductbyVariant(tenantid, variantid int) ([]models.Products, error) {

	var data []models.Products

	err := db.DB.
		Table("products p").
		Select("p.*, c.categoryname, d.subcatname as subcategoryname").
		Joins("LEFT JOIN productcategories c ON p.categoryid = c.categoryid").
		Joins("LEFT JOIN productsubcategories d ON p.subcategoryid = d.subcatid").
		Where("p.tenantid = ? and p.variants = ?", tenantid, variantid).
		Order("p.productid desc").Scan(&data).Error


	if err != nil {
		return nil, err
	}

	return data, nil

}

func GetProductVariants(tenantId int, subcategoryId int) []models.Productvariant {
	var data []models.Productvariant

	var query string
	var params []interface{}

	query = `
		SELECT a.*, b.categoryname
		FROM productvariants a
		JOIN app_category b ON a.categoryid = b.categoryid
		WHERE a.tenantid = ?
	`
	params = append(params, tenantId)

	if subcategoryId != 0 {
		query += " AND a.subcategoryid = ?"
		params = append(params, subcategoryId)
	}

	db.DB.Raw(query, params...).Scan(&data)


	return data
}

func GetCatalougeProducts(tenantId, locationid, subcategoryid, pageno, pagesize int, keyword string) []models.Products {
	var data []models.Products

	var query string

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

	offset := (pageno - 1) * pagesize

	params := []interface{}{locationid, tenantId}

	query = `SELECT a.* FROM products a 
	         LEFT JOIN productlocations b ON a.productid = b.productid  
			 AND b.locationid =?  AND b.tenantid = a.tenantid  
			 WHERE a.approve=1 and a.tenantid = ? AND b.productid IS NULL order by a.productid desc LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	if subcategoryid != 0 {
		query += " AND a.subcategoryid = ?"
		params = append(params, subcategoryid)
	}

	if keyword != "" {
		query += " AND LOWER(a.productname) LIKE ?"
		params = append(params, "%"+strings.ToLower(keyword)+"%")
	}

	db.DB.Raw(query, params...).Scan(&data)

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

	return data
}

func GetLocationProducts(tenantid, locationid, subcategoryid, pageno, pagesize int, keyword string) []models.Products {
	var data []models.Products

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

	offset := (pageno - 1) * pagesize
	params := []interface{}{tenantid, locationid}

	query := `SELECT a.*,  
			  COALESCE(SUM(CASE WHEN UPPER(c.stocktype) = 'IN' THEN c.quantity ELSE 0 END), 0) AS total_in,
    		  COALESCE(SUM(CASE WHEN UPPER(c.stocktype) = 'OUT' THEN c.quantity ELSE 0 END), 0) AS total_out,
    		  COALESCE(SUM(CASE WHEN UPPER(c.stocktype) = 'IN' THEN c.quantity ELSE 0 END) - 
              SUM(CASE WHEN UPPER(c.stocktype) = 'OUT' THEN c.quantity ELSE 0 END), 0) AS productstock 
              FROM products a 
              INNER JOIN productlocations b 
                  ON a.productid = b.productid AND a.tenantid = b.tenantid
			  LEFT JOIN productstocks c 
				 ON a.productid = c.productid 
				 AND b.locationid = c.locationid
				 AND a.tenantid = c.tenantid  
              WHERE a.approve=1 AND a.tenantid = ? 
                AND b.locationid = ?`

	if subcategoryid != 0 {
		query += " AND a.subcategoryid = ?"
		params = append(params, subcategoryid)
	}

	if keyword != "" {
		query += " AND LOWER(a.productname) LIKE ?"
		params = append(params, "%"+strings.ToLower(keyword)+"%")
	}

	query += ` GROUP BY a.productid, a.productname, a.productimage, a.categoryid, a.subcategoryid,
			 a.productunit, a.productcost, a.taxpercent, a.taxamount, a.retailprice,
			 b.tenantid, b.locationid`

	query += " ORDER BY a.productid DESC LIMIT ? OFFSET ?"
	params = append(params, pagesize, offset)

	db.DB.Raw(query, params...).Scan(&data)


	return data
}

func GetStockStatement(tenantid, locationid, subcategoryid, pageno, pagesize int, keyword string) []models.Productstockstatement {
	data := make([]models.Productstockstatement, 0)

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

	offset := (pageno - 1) * pagesize

	params := []interface{}{tenantid, locationid}

	query := `SELECT a.productid,a.productname,a.productimage,a.categoryid,a.subcategoryid,a.productunit,a.unitvalue,a.productcost,a.taxpercent,a.taxamount,a.retailprice,b.tenantid,b.locationid,
     		 COALESCE( SUM(CASE WHEN UPPER(c.stocktype) = 'IN'  AND c.stockdate::date <= CURRENT_DATE THEN c.quantity ELSE 0 END) -
			           SUM(CASE WHEN UPPER(c.stocktype) = 'OUT' AND  c.stockdate::date <= CURRENT_DATE THEN c.quantity ELSE 0 END),0 )
			 AS opening,
    		 COALESCE(SUM(CASE WHEN UPPER(c.stocktype) = 'IN'  AND c.stockdate::date = CURRENT_DATE THEN c.quantity ELSE 0 END), 0) AS credit,
    		 COALESCE(SUM(CASE WHEN UPPER(c.stocktype) = 'OUT' AND  c.stockdate::date= CURRENT_DATE THEN c.quantity ELSE 0 END), 0) AS debit,
    		COALESCE(
        		( SUM(CASE WHEN UPPER(c.stocktype) = 'IN'  AND  c.stockdate::date < CURRENT_DATE THEN c.quantity ELSE 0 END) -
				  SUM(CASE WHEN UPPER(c.stocktype) = 'OUT' AND c.stockdate::date < CURRENT_DATE THEN c.quantity ELSE 0 END)
				) +
			SUM(CASE WHEN UPPER(c.stocktype) = 'IN'  AND c.stockdate::date = CURRENT_DATE THEN c.quantity ELSE 0 END) -
			SUM(CASE WHEN UPPER(c.stocktype) = 'OUT' AND  c.stockdate::date = CURRENT_DATE THEN c.quantity ELSE 0 END),
			0
			) AS closing
			FROM products a
			JOIN productlocations b  ON a.productid = b.productid AND a.tenantid = b.tenantid
			LEFT JOIN productstocks c ON a.productid = c.productid AND b.locationid = c.locationid AND b.tenantid = c.tenantid
			WHERE b.tenantid = ? AND b.locationid = ?`

	if subcategoryid != 0 {
		query += " AND a.subcategoryid = ?"
		params = append(params, subcategoryid)
	}

	if keyword != "" {
		query += " AND (CAST(a.productid AS CHAR) LIKE ? OR LOWER(a.productname) LIKE ?)"
		likeParam := "%" + strings.ToLower(keyword) + "%"
		params = append(params, likeParam, likeParam)
	}

	query += `
			GROUP BY
				a.productid, a.productname, a.productimage,
				a.categoryid, a.subcategoryid, a.productunit,
				a.productcost, a.taxpercent, a.taxamount,
				a.retailprice, b.tenantid, b.locationid
			ORDER BY a.productid DESC LIMIT ` + strconv.Itoa(pagesize) + ` OFFSET ` + strconv.Itoa(offset)

	db.DB.Raw(query, params...).Scan(&data)

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

	return data
}

func CreateProductLocation(input []models.Productlocations) error {

	var stk []models.Productstock

	tx := db.DB.Begin()

	if err := tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "tenantid"}, {Name: "locationid"}, {Name: "productid"}},
		DoUpdates: clause.AssignmentColumns([]string{"price", "minquantity", "maxquantity"}),
	}).Create(&input).Error; err != nil {
		tx.Rollback()
		return err
	}

	for _, loc := range input {

		if loc.Quantity > 0 {
			stk = append(stk, models.Productstock{
				Tenantid:   loc.Tenantid,
				Stockdate:  time.Now(),
				Locationid: loc.Locationid,
				Productid:  loc.Productid,
				Quantity:   loc.Quantity,
				Stocktype:  loc.Stocktype,
			})

		}

	}

	if len(stk) > 0 {
		t2 := tx.Create(&stk)
		if t2.Error != nil {
			tx.Rollback()
			return t2.Error
		}
	}

	err := tx.Commit().Error
	if err != nil {

		return err

	}

	return nil

}

func UpdateProductLocation(input models.Productlocations) error {

	tx := db.DB.Begin()

	t1 := tx.Where("productlocationid=?", input.Productlocationid).Updates(&input)
	if t1.Error != nil {

		tx.Rollback()
		return t1.Error
	}

	err := tx.Commit().Error
	if err != nil {

		return err

	}

	return nil

}

func FetchProductsBySubcategory(categoryID, tenantID, applocationID, locationID, productID int, keyword string) (map[string]interface{}, error) {
	utils.Logger.Infof("Domain: Fetching products for categoryID=%d, tenantID=%d", categoryID, tenantID)

	// Step 1: Fetch subcategories
	var subcategories []models.Subcategory
	err := db.DB.Table("productsubcategories").
		Where("categoryid = ?", categoryID).
		Find(&subcategories).Error
	if err != nil {
		utils.Error("Error fetching subcategories", "error", err)
		return nil, err
	}

	// Step 2: Build product query
	var products []models.Products
	query := db.DB.Table("products a").
		Joins("LEFT JOIN productlocations pl ON pl.productid = a.productid").
		Where("a.categoryid = ? AND a.tenantid = ?", categoryID, tenantID)

	if locationID > 0 {
		query = query.Where("pl.locationid = ?", locationID)
	}
	if applocationID > 0 {
		query = query.Where("a.applocationid = ?", applocationID)
	}
	if productID > 0 {
		query = query.Where("a.productid = ?", productID)
	}
	if keyword != "" {
		like := "%" + strings.ToLower(keyword) + "%"
		query = query.Where(
			db.DB.Where("LOWER(a.productname) LIKE ?", like).
				Or("LOWER(a.unitvalue) LIKE ?", like).
				Or("LOWER(CAST(a.productcost AS CHAR)) LIKE ?", like),
		)
	}

	if err := query.Select("a.*").Find(&products).Error; err != nil {
		utils.Error("Error fetching products", "error", err)
		return nil, err
	}

	// Step 3: Grouping
	var grouped []models.SubcategoryProductResponse
	var uncategorized []models.Products

	for _, subcat := range subcategories {
		var matched []models.Products
		for _, prod := range products {
			if prod.Subcategoryid != nil && *prod.Subcategoryid == subcat.Subcategoryid {
				matched = append(matched, prod)
			}
		}
		if len(matched) > 0 {
			grouped = append(grouped, models.SubcategoryProductResponse{
				SubcategoryID:   subcat.Subcategoryid,
				SubcategoryName: subcat.Subcategoryname,
				Products:        matched,
			})
		}
	}

	// Add uncategorized
	for _, p := range products {
		if p.Subcategoryid == nil {
			uncategorized = append(uncategorized, p)
		}
	}
	if len(uncategorized) > 0 {
		grouped = append(grouped, models.SubcategoryProductResponse{
			SubcategoryID:   0,
			SubcategoryName: "Uncategorized",
			Products:        uncategorized,
		})
	}

	// Step 4: Tenant info
	var tenantInfo map[string]interface{}
	for _, p := range products {
		if p.Tenantid != nil {
			tenantInfo, err = fetchTenantDetails(*p.Tenantid, applocationID)
			if err != nil {
				utils.Error("Error fetching tenant details", "error", err)
			}
			break
		}
	}

	if tenantInfo == nil {
		return map[string]interface{}{}, nil
	}

	tenantInfo["details"] = grouped
	return tenantInfo, nil
}

func fetchTenantDetails(tenantID, applocationID int) (map[string]interface{}, error) {
	var tenant struct {
		Tenantname       string
		Address          string
		Licenseno        string
		Primaryemail     string
		Primarycontact   string
		Locationname     string
		Pickuplocationid int
		Suburb           string
		City             string
		Latitude         string
		Longitude        string
		Postcode         string
	}

	err := db.DB.Raw(`
		SELECT t.tenantname,t.address,t.licenseno,t.primaryemail,t.primarycontact,l.locationid AS pickuplocationid,l.suburb,l.city,l.latitude,l.longitude,l.postcode,a.locationname
		FROM tenants t
		LEFT JOIN tenantlocations l ON t.tenantid = l.tenantid
		LEFT JOIN app_location a ON l.applocationid = a.applocationid
		WHERE t.tenantid = ? AND t.applocationid = ?
		LIMIT 1
	`, tenantID, applocationID).Scan(&tenant).Error

	if err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, err
	}

	return map[string]interface{}{
		"tenantname":       tenant.Tenantname,
		"address":          tenant.Address,
		"licenseno":        tenant.Licenseno,
		"primaryemail":     tenant.Primaryemail,
		"primarycontact":   tenant.Primarycontact,
		"locationname":     tenant.Locationname,
		"pickuplocationid": tenant.Pickuplocationid,
		"suburb":           tenant.Suburb,
		"city":             tenant.City,
		"pickuplat":        tenant.Latitude,
		"pickuplong":       tenant.Longitude,
		"postcode":         tenant.Postcode,
	}, nil
}

func UpdateProductStock(stock *models.Productstock) error {
	// Ensure the record exists
	var existing models.Productstock
	if err := db.DB.First(&existing, "productstockid = ?", stock.Productstockid).Error; err != nil {
		return err
	}

	// Update fields
	return db.DB.Model(&existing).Updates(models.Productstock{
		Tenantid:   stock.Tenantid,
		Stockdate:  stock.Stockdate,
		Locationid: stock.Locationid,
		Productid:  stock.Productid,
		Quantity:   stock.Quantity,
		Stocktype:  stock.Stocktype,
		Status:     stock.Status,
	}).Error
}

func GetProductStocks(tenantID, locationID string) ([]models.Productstocks, error) {
	var stocks []models.Productstocks
	var params []interface{}
	var conditions []string

	query := `
		SELECT
			a.productid, a.tenantid, MAX(a.stockdate) AS stockdate, a.locationid, a.stocktype, a.maxquantity, a.minquantity, a.status,
			b.applocationid, b.categoryid, b.subcategoryid, b.catalogueid, b.addonid, b.discountid, b.pricingid,
			b.productname, b.productimage, b.productdesc, b.productsku, b.brandid, b.productbrand, b.productunit,
			b.unitvalue, b.toppicks, b.productcost, b.taxamount, b.taxpercent, b.producttax, b.productstock,
			b.productcombo, b.variants, b.retailprice, b.diffprice, b.diffpercent, b.othercost, b.approve,
			b.productstatus, b.created, b.updated, c.subcatname AS subcategoryname,
			SUM(CASE WHEN a.stocktype = 'in'  THEN a.quantity ELSE 0 END) -
			SUM(CASE WHEN a.stocktype = 'out' THEN a.quantity ELSE 0 END) AS quantity
		FROM productstocks a
		JOIN products b ON a.productid = b.productid
		INNER JOIN productsubcategories c ON c.subcatid = b.subcategoryid
	`

	if tenantID != "" {
		conditions = append(conditions, "a.tenantid = ?")
		params = append(params, tenantID)
	}

	if locationID != "" {
		conditions = append(conditions, "a.locationid = ?")
		params = append(params, locationID)
	}

	if len(conditions) > 0 {
		query += " WHERE " + strings.Join(conditions, " AND ")
	}

	query += " GROUP BY a.productid"

	if err := db.DB.Raw(query, params...).Scan(&stocks).Error; err != nil {
		return nil, err
	}

	return stocks, nil
}

func GetSubCategoryWiseSummary(tenantId, locationid, subcategoryid int) ([]models.SubCategorySummary, error) {
	var data []models.SubCategorySummary

	query := `
		SELECT a.subcategoryid AS subcategoryid, c.subcatname AS subcatname, COUNT(a.productid) AS productcount
		FROM products a
		LEFT JOIN productlocations b ON a.productid = b.productid AND b.locationid = ? AND b.tenantid = a.tenantid
		LEFT JOIN productsubcategories c ON a.subcategoryid = c.subcatid
		WHERE a.approve = 1 AND a.tenantid = ? AND b.productid IS NULL
	`

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

	if subcategoryid > 0 {
		query += " AND a.subcategoryid = ?"
		params = append(params, subcategoryid)
	}

	query += `
		GROUP BY a.subcategoryid, c.subcatname
		ORDER BY a.subcategoryid ASC
	`

	if err := db.DB.Raw(query, params...).Scan(&data).Error; err != nil {
		return nil, err
	}

	return data, nil
}

func GetLocationProductSummary(tenantid, locationid int) []models.ProductSummary {
	data := make([]models.ProductSummary, 0)

	params := []interface{}{tenantid, locationid}

	query := `
		SELECT a.subcatid AS subcategoryid, a.subcatname AS subcategroyname, a.image, COUNT(DISTINCT b.productid) AS productcount
		FROM productsubcategories a
		LEFT JOIN products b ON a.subcatid = b.subcategoryid AND b.approve = 1 AND b.tenantid = ?
		LEFT JOIN productlocations c ON b.productid = c.productid AND b.tenantid = c.tenantid AND c.locationid = ?
		WHERE a.categoryid = 2
		GROUP BY a.subcatid, a.subcatname
		ORDER BY a.subcatid;
	`

	db.DB.Raw(query, params...).Scan(&data)

	total := 0
	for _, d := range data {
		total += d.Productcount
	}

	all := models.ProductSummary{
		Subcategoryid:   0,
		Subcategroyname: "All",
		Productcount:    total,
	}
	data = append([]models.ProductSummary{all}, data...)

	return data
}

func GetStockStatementSummary(tenantid, locationid int) []models.ProductSummary {
	data := make([]models.ProductSummary, 0)

	params := []interface{}{tenantid, locationid}

	query := `
		SELECT a.subcatid AS subcategoryid, a.subcatname AS subcategroyname, a.image, COUNT(DISTINCT b.productid) AS productcount
		FROM productsubcategories a
		LEFT JOIN products b ON a.subcatid = b.subcategoryid AND b.tenantid = ?
		LEFT JOIN productlocations c ON b.productid = c.productid AND b.tenantid = c.tenantid AND c.locationid = ?
		WHERE a.categoryid = 2
		GROUP BY a.subcatid, a.subcatname
		ORDER BY a.subcatid;
	`

	db.DB.Raw(query, params...).Scan(&data)

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

	// Add "All" row
	total := models.ProductSummary{
		Subcategoryid:   0,
		Subcategroyname: "All",
	}
	for _, d := range data {
		total.Productcount += d.Productcount
	}
	data = append([]models.ProductSummary{total}, data...)

	return data
}

func CreateProductDiscount(discount *models.ProductDiscount) error {

	// convert []string to comma-separated string
	if len(discount.Locationid) > 0 {
		discount.LocationidStr = strings.Join(discount.Locationid, ",")
	}

	tx := db.DB.Begin()

	if err := tx.Table("productdiscounts").Create(discount).Error; err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit().Error
}

func GetProductDiscounts(tid int, lid string) []models.ProductDiscount {

	var result []models.ProductDiscount

	query := `
        SELECT 
            discountid, discounttypeid, tenantid, productid,  moduleid,
            locationid, discountname, discountcode,
            discountterms, discountvalue, startdate, enddate, status
        FROM productdiscounts
        WHERE tenantid = ?
          AND moduleid = 2
          AND FIND_IN_SET(?, locationid)
    `

	db.DB.Raw(query, tid, lid).Scan(&result)

	// convert comma separated location ids to []string
	for i := range result {
		if result[i].LocationidStr != "" {
			result[i].Locationid = strings.Split(result[i].LocationidStr, ",")
		} else {
			result[i].Locationid = []string{}
		}
	}

	return result
}