package domain

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

func GetAllInvoice(stat, tid int) []models.Tenantsales {

	var q1 string

	var data []models.Tenantsales

	const (
		base = `select a.* from tenantsales a`
	)

	if stat == 1 {

		if tid != 0 {

			q1 = base + ` where a.billstatus=0 and a.tenantid=` + strconv.Itoa(tid) + ` and a.duedate::date > CURRENT_DATE order by salesid desc `

		} else {

			q1 = base + ` where a.billstatus=0 and a.duedate::date > CURRENT_DATE order by salesid desc `
		}

	} else if stat == 2 {

		if tid != 0 {

			q1 = base + ` where a.billstatus=0 and a.tenantid=` + strconv.Itoa(tid) + ` and a.duedate::date < CURRENT_DATE order by salesid desc `

		} else {

			q1 = base + ` where a.billstatus=0 and a.duedate::date < CURRENT_DATE order by salesid desc `

		}

	} else if stat == 3 {

		if tid != 0 {

			q1 = base + ` where a.billstatus=2 and a.tenantid=` + strconv.Itoa(tid) + ` order by salesid desc `

		} else {

			q1 = base + ` where a.billstatus=2 order by salesid desc `
		}

	} else {

		if tid != 0 {

			q1 = base + ` where a.tenantid=` + strconv.Itoa(tid) + ` order by salesid desc `

		} else {

			q1 = base + ` order by salesid desc `
		}

	}
	// print(q1)
	db.DB.Raw(q1).Preload("Tenantsalesdetails").Find(&data)

	return data

}

func GetInvoiceOrders(tid int, fdate, tdate string) models.InvoiceOrders {

	var q1 string

	var data models.InvoiceOrders

	q1 = `SELECT round(SUM(actualkms),0) AS actualkms,round(SUM(kms),0) AS kms,count(*) as deliveries FROM deliveries 
		  WHERE orderstatus='delivered' and tenantid=` + strconv.Itoa(tid) + ` and  date(deliverydate) between '` + fdate + `' and '` + tdate + `'`

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

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

	return data

}

func GetInvoiceInsight(tid int) models.InvoiceInsight {

	var q1 string

	var data models.InvoiceInsight

	if tid != 0 {

		q1 = `Select COUNT(*) AS totalcount,
			SUM(totalamount) AS total,
			SUM(CASE WHEN billstatus=0 and status='pending' AND  duedate > CURRENT_DATE  THEN 1 ELSE 0 END) AS pendingcount,
			SUM(CASE WHEN billstatus IN (0) and status='pending' AND duedate > CURRENT_DATE  THEN totalamount ELSE 0 END) AS pending,
			(SUM(CASE WHEN billstatus IN (0) and status='pending' AND duedate > CURRENT_DATE THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS pendingpercent,
			SUM(CASE WHEN billstatus = 1  THEN 1 ELSE 0 END) AS confirmedcount,
			SUM(CASE WHEN billstatus = 1  THEN totalamount ELSE 0 END) AS confirmed,
			(SUM(CASE WHEN billstatus = 1 THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS confrimedpercent,
			SUM(CASE WHEN billstatus = 2 THEN 1 ELSE 0 END) AS paidcount,
			SUM(CASE WHEN billstatus = 2 THEN totalamount ELSE 0 END) AS paid,
			(SUM(CASE WHEN billstatus = 2 THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS paidpercent,
			SUM(CASE WHEN billstatus IN (0) and status='pending' AND  duedate <= CURRENT_DATE THEN 1 ELSE 0 END) AS overduecount,
			SUM(CASE WHEN billstatus IN(0) and status='pending' AND  duedate <= CURRENT_DATE THEN totalamount ELSE 0 END) AS overdue,
			(SUM(CASE WHEN billstatus IN(0) and status='pending' AND duedate <= CURRENT_DATE THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS overduepercent 
			FROM tenantsales where tenantid=` + strconv.Itoa(tid)

	} else {

		q1 = `Select COUNT(*) AS totalcount,
		SUM(totalamount) AS total,
		SUM(CASE WHEN billstatus=0 and status='pending' AND  duedate > CURRENT_DATE  THEN 1 ELSE 0 END) AS pendingcount,
		SUM(CASE WHEN billstatus IN (0) and status='pending' AND duedate > CURRENT_DATE  THEN totalamount ELSE 0 END) AS pending,
		(SUM(CASE WHEN billstatus IN (0) and status='pending' AND duedate > CURRENT_DATE THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS pendingpercent,
		SUM(CASE WHEN billstatus = 1  THEN 1 ELSE 0 END) AS confirmedcount,
		SUM(CASE WHEN billstatus = 1  THEN totalamount ELSE 0 END) AS confirmed,
		(SUM(CASE WHEN billstatus = 1 THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS confrimedpercent,
		SUM(CASE WHEN billstatus = 2 THEN 1 ELSE 0 END) AS paidcount,
		SUM(CASE WHEN billstatus = 2 THEN totalamount ELSE 0 END) AS paid,
		(SUM(CASE WHEN billstatus = 2 THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS paidpercent,
		SUM(CASE WHEN billstatus IN (0) and status='pending' AND  duedate <= CURRENT_DATE THEN 1 ELSE 0 END) AS overduecount,
		SUM(CASE WHEN billstatus IN(0) and status='pending' AND  duedate <= CURRENT_DATE THEN totalamount ELSE 0 END) AS overdue,
		(SUM(CASE WHEN billstatus IN(0) and status='pending' AND duedate <= CURRENT_DATE THEN totalamount ELSE 0 END) / SUM(totalamount) * 100) AS overduepercent 
		FROM tenantsales`

	}

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

	return data

}

func CreateInvoice(data models.Tenantsales) error {

	tx := db.DB.Begin()

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

		utils.Error("CreateInvoice error", "error", t1.Error)
		tx.Rollback()
		return t1.Error
	}

	t2 := tx.Table("deliveries").Where("deliverydate>=? and deliverydate<=?", data.Tenantsalesdetails[0].Fromdate, data.Tenantsalesdetails[0].Todate).Update("settlementid", data.Salesid)
	if t2.Error != nil {

		utils.Error("CreateInvoice deliveries update error", "error", t2.Error)
		tx.Rollback()
		return t2.Error
	}

	UpdateSeqno(data.Tenantid, "INV")

	err := tx.Commit().Error

	if err != nil {

		return err

	}
	return nil
}

func UpdateInvoice(data models.Tenantsales) error {

	tx := db.DB.Begin()

	t1 := tx.Where("salesid=?", data.Salesid).Updates(&data)
	if t1.Error != nil {

		utils.Error("UpdateInvoice error", "error", t1.Error)
		tx.Rollback()
	}
	err := tx.Commit().Error

	if err != nil {
		return err
	}
	return nil
}

func UpdateInvoiceStatus(data models.InvoiceStatus) error {

	tx := db.DB.Begin()

	t1 := tx.Table("tenantsales").Where("salesid=?", data.Salesid).Updates(&data)
	if t1.Error != nil {

		utils.Error("UpdateInvoiceStatus error", "error", t1.Error)
		tx.Rollback()
	}
	err := tx.Commit().Error

	if err != nil {
		return err
	}
	return nil
}