package mysql

import (
	"context"

	"gorm.io/gorm"

	"data-platform/model"
)

type salesforce struct {
	db *gorm.DB
}

func newSalesforce(ds *datastore) *salesforce {
	return &salesforce{db: ds.db}
}

func (s *salesforce) ListPsRejectBySync(ctx context.Context) (records []*model.PsManDaysReject, err error) {
	err = s.db.WithContext(ctx).
		Table("ps_man_days_reject").
		Select("ps_man_days_reject.*, ps_man_days.salesforce_id").
		Joins("LEFT JOIN ps_man_days ON ps_man_days.name = ps_man_days_reject.ps_man_days_id").
		Where("sync = 0 or sync is null").
		Scan(&records).Error

	return
}

func (s *salesforce) UpdateRejectSync(_ context.Context, psdRej model.PsManDaysReject, sync int) (err error) {
	err = s.db.Model(&model.PsManDaysReject{}).Where("id = ?", psdRej.ID).Updates(map[string]interface{}{
		"sync":      sync,
		"sync_time": gorm.Expr("NOW()"),
	}).Error

	return
}

func (s *salesforce) ListInvoicesNotSync(ctx context.Context) (records []*model.InvoiceRewrite, err error) {
	err = s.db.WithContext(ctx).
		Table("invoice_rewrite").
		Select("invoice_rewrite.*, public_collection.collection_id as collection_id").
		Joins("LEFT JOIN public_collection ON public_collection.ns_collection_number = invoice_rewrite.ns_collection_number").
		Where("invoice_rewrite.deleted_at is null and invoice_rewrite.sync = 0 or invoice_rewrite.sync is null or (invoice_rewrite.sync=1 and (invoice_rewrite.sync_time IS NULL OR invoice_rewrite.sync_time != invoice_rewrite.updated_time))").
		Scan(&records).Error

	return
}

func (s *salesforce) UpdateInvoice(_ context.Context, invoice model.InvoiceRewrite) (err error) {
	err = s.db.Omit("order_id,collection_id").Save(&invoice).Error

	return
}

func (s *salesforce) ListCreateMc(ctx context.Context) (records []*model.MarketplaceCollection, err error) {
	err = s.db.WithContext(ctx).
		Table("marketplace_collection").
		Select("marketplace_collection.*", "salesforce_order.salesforce_id as order_id", "marketplace_collection_status.sync").
		Joins("LEFT JOIN marketplace_collection_status ON marketplace_collection_status.related_mk_id = marketplace_collection.mk_id").
		Joins("LEFT JOIN salesforce_order ON salesforce_order.order_number_name = marketplace_collection.salesforce_order_number").
		Where("marketplace_collection_status.sync = 0 or marketplace_collection_status.sync is null").
		Where("marketplace_collection.collection_id = '' or marketplace_collection.collection_id is null").
		Where("marketplace_collection.mk_id > (SELECT COALESCE(MAX(mk_id), 0) FROM marketplace_collection_bak)").
		Where("marketplace_collection.salesforce_order_number is not null").
		Scan(&records).Error

	return
}

func (s *salesforce) ListUpdateMc(ctx context.Context) (records []*model.MarketplaceCollection, err error) {
	err = s.db.WithContext(ctx).
		Table("marketplace_collection mc").
		Select(`mc.*, 
            salesforce_order.salesforce_id as order_id,
            mcs.collection_id as created_collection_id,
            mcs.collection_number as created_collection_number,
            CASE 
                WHEN mc.collection_status != mcb.collection_status THEN 1 
                ELSE 0 
            END AS schange,
			CASE 
                WHEN mc.payable_date != mcb.payable_date THEN 1 
                ELSE 0 
            END AS dchange`,
		).
		Joins("LEFT JOIN marketplace_collection_status mcs ON mcs.related_mk_id = mc.mk_id").
		Joins("LEFT JOIN salesforce_order ON salesforce_order.order_number_name = mc.salesforce_order_number").
		Joins("LEFT JOIN marketplace_collection_bak mcb ON mc.mk_id = mcb.mk_id").
		Where(`(
			mc.payable_amount != mcb.payable_amount OR 
			mc.payable_date != mcb.payable_date OR 
			mc.collections_date != mcb.collections_date OR 
			mc.collection_status != mcb.collection_status
		)`).
		Where("mc.salesforce_order_number is not null").
		Where("mcs.sync = 1 or mcs.sync = 0 or mcs.sync is null").
		Scan(&records).Error

	return
}

func (s *salesforce) CreatedMcStatus(_ context.Context, invoice model.MarketplaceCollectionStatus) (err error) {
	err = s.db.Omit("order_id").Create(&invoice).Error

	return
}

func (s *salesforce) GetMcStatusByMkID(_ context.Context, mkID string) (record *model.MarketplaceCollectionStatus, err error) {
	err = s.db.Where("related_mk_id = ?", mkID).First(&record).Error

	return
}

func (s *salesforce) SaveMcStatus(_ context.Context, record *model.MarketplaceCollectionStatus) (err error) {
	err = s.db.Save(&record).Error

	return
}

func (s *salesforce) CopyMc(ctx context.Context) (err error) {
	if err = s.db.WithContext(ctx).Exec("truncate table marketplace_collection_bak1").Error; err != nil {
		return
	}

	if err = s.db.WithContext(ctx).Exec("INSERT INTO marketplace_collection_bak1 SELECT * FROM marketplace_collection_bak").Error; err != nil {
		return
	}
	// Delete all existing records in marketplace_collection_bak
	if err = s.db.WithContext(ctx).Exec("truncate table marketplace_collection_bak").Error; err != nil {
		return
	}

	// Copy all records from marketplace_collection to marketplace_collection_bak
	err = s.db.WithContext(ctx).Exec("INSERT INTO marketplace_collection_bak SELECT * FROM marketplace_collection").Error
	return
}

func (s *salesforce) ListPbcNotSync(ctx context.Context) (records []*model.PublicCollection, err error) {
	err = s.db.WithContext(ctx).
		Table("public_collection").
		Select("public_collection.*").
		Where("public_collection.sync = 0 or public_collection.sync is null or (public_collection.sync = 1 and public_collection.sync_time != updated_time)").
		Where("public_collection.deleted_at is null").
		Scan(&records).Error

	return
}

func (s *salesforce) ListPbc(ctx context.Context) (records []*model.PublicCollection, err error) {
	// list all public collections
	err = s.db.Find(&records).Error

	return
}

func (s *salesforce) UpdatePbc(_ context.Context, pbc model.PublicCollection) (err error) {
	err = s.db.Omit("order_id").Save(&pbc).Error

	return
}

func (s *salesforce) ListNSRevrecNotSync(ctx context.Context) (records []*model.NSRevRec, err error) {
	err = s.db.WithContext(ctx).
		Table("ns_revrec").
		Select("ns_revrec.*", "salesforce_order.salesforce_id as order_id", "ns_revrec_status.sync", "ns_revrec_status.sync_time", "ns_revrec_status.sf_revrec_id", "ns_revrec_status.sf_revrec_number").
		Joins("LEFT JOIN salesforce_order ON salesforce_order.sub_order_number_name = ns_revrec.order_number").
		Joins("LEFT JOIN ns_revrec_status ON ns_revrec_status.row_id = ns_revrec.row_id").
		Joins("INNER JOIN (SELECT row_id, MAX(created_at) as max_created_at FROM ns_revrec GROUP BY row_id) latest ON ns_revrec.row_id = latest.row_id AND ns_revrec.created_at = latest.max_created_at").
		Where("ns_revrec_status.sync = 0 or ns_revrec_status.sync is null or (ns_revrec_status.sync_time is not null and ns_revrec.updated_time > ns_revrec_status.sync_time)").
		//Where("ns_revrec_status.sync = 1").
		Where("ns_revrec.revrec_number like '%Exam%' or ns_revrec.revrec_number like '%高级%' or ns_revrec.revrec_number like '%Advanced%' or ns_revrec.revrec_number is null or ns_revrec.revrec_number = '' ").
		//Where("ns_revrec.row_id in (1460732, 1460734, 1460752, 1460754, 1460756, 1460758, 1460859, 1460861, 1408814, 1401434, 1404047, 1367322)").
		Scan(&records).Error

	return
}

func (s *salesforce) GetNSRevrecStatusByRowID(_ context.Context, rowID string) (record *model.NSRevRecStatus, err error) {
	err = s.db.Where("row_id = ?", rowID).First(&record).Error

	return
}

func (s *salesforce) UpdateNSRevrecStatus(_ context.Context, nsRevrecStatus *model.NSRevRecStatus) (err error) {
	err = s.db.Save(&nsRevrecStatus).Error

	return
}

func (s *salesforce) ListOrg(_ context.Context) (records []*model.SalesforceOrg, err error) {
	err = s.db.Where("is_deleted = ?", 0).Find(&records).Error

	return
}

func (s *salesforce) ListOrder(_ context.Context) (records []*model.SfOrder, err error) {
	err = s.db.Find(&records).Error

	return
}