package mysql

import (
	"context"

	"finance-portal/model"
	"gorm.io/gorm"
)

type NSRevrec struct {
	db *gorm.DB
}

func newNSRevrec(ds *datastore) *NSRevrec {
	return &NSRevrec{db: ds.db}
}

func (ns *NSRevrec) ListByCloudCondition(_ context.Context, tranDate string) (records []*model.NSRevRec, err error) {

	tx := ns.db.Table("ns_revrec").
		Select("ns_revrec.*", "ns_revrec_status.sf_revrec_id", "ns_revrec_status.sf_revrec_number", "ns_revrec_status.sync_time").
		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.tran_date >= ?", tranDate)

	err = tx.Find(&records).Error

	return
}