cgrates/engine/storage_sql.go

/*
Real-time Online/Offline Charging System (OCS) for Telecom & ISP environments
Copyright (C) ITsysCOM GmbH

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License
along with this program.  If not, see <https://www.gnu.org/licenses/>
*/

package engine

import (
	"database/sql"
	"fmt"
	"os"
	"path"
	"strings"
	"time"

	"github.com/cgrates/birpc/context"
	"github.com/cgrates/cgrates/config"
	"github.com/cgrates/cgrates/utils"
	"github.com/cgrates/guardian"
	"gorm.io/gorm"
)

type SQLImpl interface {
	extraFieldsExistsQry(string) string
	extraFieldsValueQry(string, string) string
	notExtraFieldsExistsQry(string) string
	notExtraFieldsValueQry(string, string) string
	valueQry(string, string, string, []string, bool) []string // will query for every type of filtering in case of needed
	cdrIDQuery(string) string                                 // will get the unique *cdrID for every CDR
	existField(string, string) string                         // will query for every element on json type if the field exists
}

type SQLStorage struct {
	DB *sql.DB
	db *gorm.DB
	ms utils.Marshaler
	DataDB
	SQLImpl
}

func (sqls *SQLStorage) Close() {
	sqls.DB.Close()
	// sqls.db
}

func (sqls *SQLStorage) ExportGormDB() *gorm.DB {
	return sqls.db
}

func (sqls *SQLStorage) Flush(scriptsPath string) (err error) {
	for _, scriptName := range []string{utils.CreateDBTablesSQL,
		utils.CreateCDRsTablesSQL, utils.CreateTariffPlanTablesSQL} {
		if err := sqls.CreateTablesFromScript(path.Join(scriptsPath, scriptName)); err != nil {
			return err
		}
	}
	if _, err := sqls.DB.Query(fmt.Sprintf("SELECT 1 FROM %s", utils.CDRsTBL)); err != nil {
		return err
	}
	return nil
}

func (sqls *SQLStorage) SelectDatabase(dbName string) (err error) {
	return
}

// returns all keys in table matching the Tenant and ID
func (sqls *SQLStorage) getAllKeysMatchingTenantID(_ *context.Context, table string, tntID *utils.TenantID) (ids []string, err error) {
	matchingTntID := []utils.TenantID{}
	if err = sqls.db.Table(table).Select("tenant, id").Where("tenant = ? AND id LIKE ?", tntID.Tenant, tntID.ID+"%").
		Find(&matchingTntID).Error; err != nil {
		return nil, err
	}
	ids = make([]string, len(matchingTntID))
	for i, result := range matchingTntID {
		ids[i] = utils.ConcatenatedKey(result.Tenant, result.ID)
	}
	return
}

// GetKeysForPrefix will look for keys matching the prefix given
func (sqls *SQLStorage) GetKeysForPrefix(ctx *context.Context, prefix string) (keys []string, err error) {
	keyLen := len(utils.AccountPrefix)
	if len(prefix) < keyLen {
		return nil, fmt.Errorf("unsupported prefix in GetKeysForPrefix: %q", prefix)
	}
	category := prefix[:keyLen]
	tntID := utils.NewTenantID(prefix[keyLen:])

	switch category {
	case utils.AccountPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLAccounts, tntID)
	case utils.IPProfilesPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLIPProfiles, tntID)
	case utils.IPAllocationsPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLIPAllocations, tntID)
	case utils.ActionProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLActionProfiles, tntID)
	case utils.ChargerProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLChargerProfiles, tntID)
	case utils.AttributeProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLAttributeProfiles, tntID)
	case utils.ResourceProfilesPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLResourceProfiles, tntID)
	case utils.ResourcesPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLResources, tntID)
	case utils.StatQueueProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLStatQueueProfiles, tntID)
	case utils.StatQueuePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLStatQueues, tntID)
	case utils.ThresholdProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLThresholdProfiles, tntID)
	case utils.ThresholdPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLThresholds, tntID)
	case utils.FilterPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLFilters, tntID)
	case utils.RouteProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLRouteProfiles, tntID)
	case utils.RateProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLRateProfiles, tntID)
	case utils.RankingProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLRankingProfiles, tntID)
	case utils.RankingPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLRankings, tntID)
	case utils.TrendProfilePrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLTrendProfiles, tntID)
	case utils.TrendPrefix:
		keys, err = sqls.getAllKeysMatchingTenantID(ctx, utils.TBLTrends, tntID)
	case utils.AttributeFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.AttributeFilterIndexes, tntID.Tenant)
	case utils.ResourceFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.ResourceFilterIndexes, tntID.Tenant)
	case utils.IPFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.IPFilterIndexes, tntID.Tenant)
	case utils.StatFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.StatFilterIndexes, tntID.Tenant)
	case utils.ThresholdFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.ThresholdFilterIndexes, tntID.Tenant)
	case utils.RouteFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.RouteFilterIndexes, tntID.Tenant)
	case utils.ChargerFilterIndexes:
		keys, err = sqls.getAllIndexKeys(utils.ChargerFilterIndexes, tntID.Tenant)
	case utils.ActionPlanIndexes:
		keys, err = sqls.getAllIndexKeys(utils.ActionPlanIndexes, tntID.Tenant)
	case utils.ActionProfilesFilterIndexPrfx:
		keys, err = sqls.getAllIndexKeys(utils.ActionProfilesFilterIndexPrfx, tntID.Tenant)
	case utils.AccountFilterIndexPrfx:
		keys, err = sqls.getAllIndexKeys(utils.AccountFilterIndexPrfx, tntID.Tenant)
	case utils.RateProfilesFilterIndexPrfx:
		keys, err = sqls.getAllIndexKeys(utils.RateProfilesFilterIndexPrfx, tntID.Tenant)
	case utils.RateFilterIndexPrfx:
		keys, err = sqls.getAllIndexKeys(utils.RateFilterIndexPrfx, tntID.Tenant)
	case utils.FilterIndexPrfx:
		keys, err = sqls.getAllIndexKeys(utils.FilterIndexPrfx, tntID.Tenant)
	default:
		err = fmt.Errorf("unsupported prefix in GetKeysForPrefix: %q", prefix)
	}
	for i := range keys { // bring the prefix back to match redis style keys to satisfy functions using it
		keys[i] = category + keys[i]
	}
	return keys, err
}

func (sqls *SQLStorage) CreateTablesFromScript(scriptPath string) error {
	fileContent, err := os.ReadFile(scriptPath)
	if err != nil {
		return err
	}
	qries := strings.Split(string(fileContent), ";") // Script has normally multiple queries separate by ';' go driver does not understand this so we handle it here
	for _, qry := range qries {
		qry = strings.TrimSpace(qry) // Avoid empty queries
		if len(qry) == 0 {
			continue
		}
		if _, err := sqls.DB.Exec(qry); err != nil {
			return err
		}
	}
	return nil
}

func (sqls *SQLStorage) IsDBEmpty() (bool, error) {
	sqlTables := []string{
		utils.CDRsTBL, utils.TBLVersions, utils.TBLAccounts,
		utils.TBLIPProfiles, utils.TBLIPAllocations, utils.TBLActionProfiles,
		utils.TBLChargerProfiles, utils.TBLAttributeProfiles, utils.TBLResourceProfiles,
		utils.TBLResources, utils.TBLStatQueueProfiles, utils.TBLStatQueues,
		utils.TBLThresholdProfiles, utils.TBLThresholds, utils.TBLFilters,
		utils.TBLRouteProfiles, utils.TBLRateProfiles, utils.TBLRates,
		utils.TBLRankingProfiles, utils.TBLRankings, utils.TBLTrendProfiles,
		utils.TBLTrends, utils.TBLLoadIDs, utils.TBLIndexes,
	}
	for _, tbl := range sqlTables {
		if !sqls.db.Migrator().HasTable(tbl) {
			continue
		}
		var count int64
		if err := sqls.db.Table(tbl).Count(&count).Error; err != nil {
			return false, err
		}
		if count > 0 {
			return false, nil // Table contains data
		}
	}
	return true, nil // All tables empty
}

// GetVersions returns slice of all versions or a specific version if tag is specified
func (sqls *SQLStorage) GetVersions(itm string) (vrs Versions, err error) {
	q := sqls.db.Model(&TBLVersion{})
	if itm != utils.TBLVersions && itm != utils.EmptyString {
		q = sqls.db.Where(&TBLVersion{Item: itm})
	}
	var verModels []*TBLVersion
	if err = q.Find(&verModels).Error; err != nil {
		return
	}
	vrs = make(Versions)
	for _, verModel := range verModels {
		vrs[verModel.Item] = verModel.Version
	}
	if len(vrs) == 0 {
		return nil, utils.ErrNotFound
	}
	return
}

// RemoveVersions will remove specific versions out of storage
func (sqls *SQLStorage) RemoveVersions(vrs Versions) (err error) {
	if len(vrs) == 0 { // Remove all if no key provided
		err = sqls.db.Delete(TBLVersion{}).Error
		return
	}
	tx := sqls.db.Begin()
	for key := range vrs {
		if err = tx.Where(&TBLVersion{Item: key}).Delete(TBLVersion{}).Error; err != nil {
			tx.Rollback()
			return
		}
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) SetCDR(_ *context.Context, cdr *utils.CGREvent, allowUpdate bool) error {
	tx := sqls.db.Begin()
	if tx.Error != nil {
		return tx.Error
	}
	cdrTable := &utils.CDRSQLTable{
		Tenant:    cdr.Tenant,
		Opts:      cdr.APIOpts,
		Event:     cdr.Event,
		CreatedAt: time.Now(),
	}
	saved := tx.Save(cdrTable)
	if saved.Error != nil {
		tx.Rollback()
		if !allowUpdate {
			if strings.Contains(saved.Error.Error(), "1062") || strings.Contains(saved.Error.Error(), "duplicate key") { // returns 1062/pq when key is duplicated
				return utils.ErrExists
			}
			return saved.Error
		}
		tx = sqls.db.Begin()
		if tx.Error != nil {
			return tx.Error
		}

		cdrID := utils.IfaceAsString(cdr.APIOpts[utils.MetaCDRID])
		updated := tx.Model(&utils.CDRSQLTable{}).Where(
			sqls.cdrIDQuery(cdrID)).Updates(
			utils.CDRSQLTable{Opts: cdr.APIOpts, Event: cdr.Event, UpdatedAt: time.Now()})
		if updated.Error != nil {
			tx.Rollback()
			return updated.Error
		}
	}
	tx.Commit()
	return nil
}

// GetCDRs has ability to get the filtered CDRs, count them or simply return them
// qryFltr.Unscoped will ignore soft deletes or delete records permanently
func (sqls *SQLStorage) GetCDRs(ctx *context.Context, qryFltr []*Filter, opts map[string]any) ([]*utils.CDR, error) {
	q := sqls.db.Table(utils.CDRsTBL)
	var excludedCdrQueryFilterTypes []*FilterRule
	for _, fltr := range qryFltr {
		for _, rule := range fltr.Rules {
			if !cdrQueryFilterTypes.Has(rule.Type) || checkNestedFields(rule.Element, rule.Values) {
				excludedCdrQueryFilterTypes = append(excludedCdrQueryFilterTypes, rule)
				continue
			}
			var elem, field string
			switch {
			case strings.HasPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaReq+utils.NestingSep):
				elem = "event"
				field = strings.TrimPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaReq+utils.NestingSep)
			case strings.HasPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaOpts+utils.NestingSep):
				elem = "opts"
				field = strings.TrimPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaOpts+utils.NestingSep)
			}
			var count int64
			if _ = sqls.db.Table(utils.CDRsTBL).Where(
				sqls.existField(elem, field)).Count(&count); count > 0 &&
				(rule.Type == utils.MetaNotExists ||
					rule.Type == utils.MetaNotString) {
				continue
			}
			conditions := sqls.valueQry(rule.Type, elem, field, rule.Values, strings.HasPrefix(rule.Type, utils.MetaNot))
			q.Where(strings.Join(conditions, " OR "))
		}
	}

	limit, offset, maxItems, err := utils.GetPaginateOpts(opts)
	if err != nil {
		return nil, fmt.Errorf("could not retrieve paginator opts: %w", err)
	}
	if maxItems < limit+offset {
		return nil, fmt.Errorf("sum of limit and offset exceeds maxItems")
	}
	if limit > 0 {
		q = q.Limit(limit)
	}
	if offset > 0 {
		q = q.Offset(offset)
	}

	// Execute query
	results := make([]*utils.CDRSQLTable, 0)
	if err = q.Find(&results).Error; err != nil {
		return nil, err
	}

	//convert into CDR
	cdrs := make([]*utils.CDR, 0, len(results))
	for _, val := range results {
		cdr := &utils.CDR{
			Tenant:    val.Tenant,
			Opts:      val.Opts,
			Event:     val.Event,
			CreatedAt: val.CreatedAt,
			UpdatedAt: val.UpdatedAt,
			DeletedAt: val.DeletedAt,
		}
		// here we wil do our filtration, meaning that we will filter those cdrs who cannot be filtered in the databes eg: *ai, *rsr..
		if len(excludedCdrQueryFilterTypes) != 0 {
			var pass bool
			dP := cdr.CGREvent().AsDataProvider()
			for _, fltr := range excludedCdrQueryFilterTypes {
				if pass, err = fltr.Pass(ctx, dP); err != nil {
					return nil, err
				} else if !pass {
					break
				}
			}
			// if the cdr passed the filtration, get it as result, else continue
			if !pass {
				continue
			}
		}
		cdrs = append(cdrs, cdr)
	}
	if len(cdrs) == 0 {
		return nil, utils.ErrNotFound
	}
	return cdrs, nil
}

func (sqls *SQLStorage) RemoveCDRs(ctx *context.Context, qryFltr []*Filter) (err error) {
	q := sqls.db.Table(utils.CDRsTBL)
	var excludedCdrQueryFilterTypes []*FilterRule
	for _, fltr := range qryFltr {
		for _, rule := range fltr.Rules {
			if !cdrQueryFilterTypes.Has(rule.Type) || checkNestedFields(rule.Element, rule.Values) {
				excludedCdrQueryFilterTypes = append(excludedCdrQueryFilterTypes, rule)
				continue
			}
			var elem, field string
			switch {
			case strings.HasPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaReq+utils.NestingSep):
				elem = "event"
				field = strings.TrimPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaReq+utils.NestingSep)
			case strings.HasPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaOpts+utils.NestingSep):
				elem = "opts"
				field = strings.TrimPrefix(rule.Element, utils.DynamicDataPrefix+utils.MetaOpts+utils.NestingSep)
			}
			var count int64
			if _ = sqls.db.Table(utils.CDRsTBL).Where(
				sqls.existField(elem, field)).Count(&count); count > 0 &&
				(rule.Type == utils.MetaNotExists ||
					rule.Type == utils.MetaNotString) {
				continue
			}
			conditions := sqls.valueQry(rule.Type, elem, field, rule.Values, strings.HasPrefix(rule.Type, utils.MetaNot))
			q.Where(strings.Join(conditions, " OR "))
		}
	}
	// if we do not have any filters that cannot be queried in database, just delete all the results (e.g. *rsr, *ai, *cronexp ..))
	if len(excludedCdrQueryFilterTypes) == 0 {
		if err = q.Delete(nil).Error; err != nil {
			q.Rollback()
			return err
		}
		return
	}
	// in the other case, if we have such filters, check the results based on those filters
	results := make([]*utils.CDRSQLTable, 0)
	if err = q.Find(&results).Error; err != nil {
		return
	}
	// this means nothing in database matched, so we will not check the filtration process
	if len(results) == 0 {
		return
	}
	// keep the result for quering with other filter type that are not allowed in database
	q = sqls.db.Table(utils.CDRsTBL)          // reset the query
	remCdr := make([]string, 0, len(results)) // we will keep the *cdrID of every CDR taht matched the those filters
	for _, cdr := range results {
		if len(excludedCdrQueryFilterTypes) != 0 {
			newCdr := &utils.CDR{
				Tenant: cdr.Tenant,
				Opts:   cdr.Opts,
				Event:  cdr.Event,
			}
			var pass bool
			dP := newCdr.CGREvent().AsDataProvider()
			// check if the filter pass
			for _, fltr := range excludedCdrQueryFilterTypes {
				if pass, err = fltr.Pass(ctx, dP); err != nil {
					return err
				} else if !pass {
					break
				}
			}
			if pass {
				// if the filters passed, remove the CDR by it's *cdrID
				remCdr = append(remCdr, sqls.cdrIDQuery(utils.IfaceAsString(newCdr.Opts[utils.MetaCDRID])))
			}
		}
	}
	// this means nothing PASSED trough filtration process, so nothing will be deleted
	if len(remCdr) == 0 {
		return
	}
	q.Where(strings.Join(remCdr, " OR "))
	if err = q.Delete(nil).Error; err != nil {
		q.Rollback()
		return err
	}
	return
}

// GetAccountDrv will get the account from the DB matching the tenant and id provided.
// Decimal fields ending in `.0` will be read as whole numbers but still in decimal type.
// (50.0 -> 50)
func (sqls *SQLStorage) GetAccountDrv(ctx *context.Context, tenant, id string) (ap *utils.Account, err error) {
	var result []*AccountJSONMdl
	if err = sqls.db.Model(&AccountJSONMdl{}).Where(&AccountJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToAccount(result[0].Account)
}

// SetAccountDrv will set in DB the provided Account
func (sqls *SQLStorage) SetAccountDrv(ctx *context.Context, ap *utils.Account) (err error) {
	tx := sqls.db.Begin()
	mdl := &AccountJSONMdl{
		Tenant:  ap.Tenant,
		ID:      ap.ID,
		Account: ap.AsMapStringInterface(),
	}
	if err = tx.Model(&AccountJSONMdl{}).Where(
		AccountJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		AccountJSONMdl{Account: mdl.Account}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

// RemoveAccountDrv will remove from DB the account matching the tenamt and id provided
func (sqls *SQLStorage) RemoveAccountDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&AccountJSONMdl{}).Where(&AccountJSONMdl{Tenant: tenant, ID: id}).
		Delete(&AccountJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetIPProfileDrv(ctx *context.Context, tenant, id string) (*utils.IPProfile, error) {
	var result []*IPProfileMdl
	if err := sqls.db.Model(&IPProfileMdl{}).Where(&IPProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToIPProfile(result[0].IPProfile)
}

func (sqls *SQLStorage) SetIPProfileDrv(ctx *context.Context, ipp *utils.IPProfile) error {
	tx := sqls.db.Begin()
	mdl := &IPProfileMdl{
		Tenant:    ipp.Tenant,
		ID:        ipp.ID,
		IPProfile: ipp.AsMapStringInterface(),
	}
	if err := tx.Model(&IPProfileMdl{}).Where(
		IPProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		IPProfileMdl{IPProfile: mdl.IPProfile}).Error; err != nil {
		tx.Rollback()
		return err
	}
	if err := tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) RemoveIPProfileDrv(ctx *context.Context, tenant, id string) error {
	tx := sqls.db.Begin()
	if err := tx.Model(&IPProfileMdl{}).Where(&IPProfileMdl{Tenant: tenant, ID: id}).
		Delete(&IPProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) GetIPAllocationsDrv(ctx *context.Context, tenant, id string) (*utils.IPAllocations, error) {
	var result []*IPAllocationMdl
	if err := sqls.db.Model(&IPAllocationMdl{}).Where(&IPAllocationMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToIPAllocations(result[0].IPAllocation), nil
}

func (sqls *SQLStorage) SetIPAllocationsDrv(ctx *context.Context, ip *utils.IPAllocations) error {
	tx := sqls.db.Begin()
	mdl := &IPAllocationMdl{
		Tenant:       ip.Tenant,
		ID:           ip.ID,
		IPAllocation: ip.AsMapStringInterface(),
	}
	if err := tx.Model(&IPAllocationMdl{}).Where(
		IPAllocationMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		IPAllocationMdl{IPAllocation: mdl.IPAllocation}).Error; err != nil {
		tx.Rollback()
		return err
	}
	if err := tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) RemoveIPAllocationsDrv(ctx *context.Context, tenant, id string) error {
	tx := sqls.db.Begin()
	if err := tx.Model(&IPAllocationMdl{}).Where(&IPAllocationMdl{Tenant: tenant, ID: id}).
		Delete(&IPAllocationMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) GetActionProfileDrv(ctx *context.Context, tenant, id string) (ap *utils.ActionProfile, err error) {
	var result []*ActionProfileJSONMdl
	if err := sqls.db.Model(&ActionProfileJSONMdl{}).Where(&ActionProfileJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToActionProfile(result[0].ActionProfile)
}

func (sqls *SQLStorage) SetActionProfileDrv(ctx *context.Context, ap *utils.ActionProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &ActionProfileJSONMdl{
		Tenant:        ap.Tenant,
		ID:            ap.ID,
		ActionProfile: ap.AsMapStringInterface(),
	}
	if err := tx.Model(&ActionProfileJSONMdl{}).Where(
		ActionProfileJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		ActionProfileJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	if err := tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) RemoveActionProfileDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err := tx.Model(&ActionProfileJSONMdl{}).Where(&ActionProfileJSONMdl{Tenant: tenant, ID: id}).
		Delete(&ActionProfileJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) GetChargerProfileDrv(_ *context.Context, tenant, id string) (cp *utils.ChargerProfile, err error) {
	var result []*ChargerProfileMdl
	if err := sqls.db.Model(&ChargerProfileMdl{}).Where(&ChargerProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}

	return utils.MapStringInterfaceToChargerProfile(result[0].ChargerProfile)
}

func (sqls *SQLStorage) SetChargerProfileDrv(_ *context.Context, cp *utils.ChargerProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &ChargerProfileMdl{
		Tenant:         cp.Tenant,
		ID:             cp.ID,
		ChargerProfile: cp.AsMapStringInterface(),
	}
	if err := tx.Model(&ChargerProfileMdl{}).Where(
		ChargerProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		ChargerProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	if err := tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) RemoveChargerProfileDrv(_ *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err := tx.Model(&ChargerProfileMdl{}).Where(&ChargerProfileMdl{Tenant: tenant, ID: id}).
		Delete(&ChargerProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return nil
}

func (sqls *SQLStorage) GetAttributeProfileDrv(ctx *context.Context, tenant, id string) (ap *utils.AttributeProfile, err error) {
	var result []*AttributeProfileMdl
	if err := sqls.db.Model(&AttributeProfileMdl{}).Where(&AttributeProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToAttributeProfile(result[0].AttributeProfile)
}

func (sqls *SQLStorage) SetAttributeProfileDrv(ctx *context.Context, ap *utils.AttributeProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &AttributeProfileMdl{
		Tenant:           ap.Tenant,
		ID:               ap.ID,
		AttributeProfile: ap.AsMapStringInterface(),
	}
	if err = tx.Model(&AttributeProfileMdl{}).Where(
		AttributeProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		AttributeProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveAttributeProfileDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&AttributeProfileMdl{}).Where(&AttributeProfileMdl{Tenant: tenant, ID: id}).
		Delete(&AttributeProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetResourceProfileDrv(ctx *context.Context, tenant, id string) (rsp *utils.ResourceProfile, err error) {
	var result []*ResourceProfileMdl
	if err = sqls.db.Model(&ResourceProfileMdl{}).Where(&ResourceProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToResourceProfile(result[0].ResourceProfile)
}

func (sqls *SQLStorage) SetResourceProfileDrv(ctx *context.Context, rsp *utils.ResourceProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &ResourceProfileMdl{
		Tenant:          rsp.Tenant,
		ID:              rsp.ID,
		ResourceProfile: rsp.AsMapStringInterface(),
	}
	if err = tx.Model(&ResourceProfileMdl{}).Where(
		ResourceProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		ResourceProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveResourceProfileDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&ResourceProfileMdl{}).Where(&ResourceProfileMdl{Tenant: tenant, ID: id}).
		Delete(&ResourceProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetResourceDrv(ctx *context.Context, tenant, id string) (r *utils.Resource, err error) {
	var result []*ResourceJSONMdl
	if err = sqls.db.Model(&ResourceJSONMdl{}).Where(&ResourceJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToResource(result[0].Resource), nil
}

func (sqls *SQLStorage) SetResourceDrv(ctx *context.Context, r *utils.Resource) (err error) {
	tx := sqls.db.Begin()
	mdl := &ResourceJSONMdl{
		Tenant:   r.Tenant,
		ID:       r.ID,
		Resource: r.AsMapStringInterface(),
	}
	if err = tx.Model(&ResourceJSONMdl{}).Where(
		ResourceJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		ResourceJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveResourceDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&ResourceJSONMdl{}).Where(&ResourceJSONMdl{Tenant: tenant, ID: id}).
		Delete(&ResourceJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetStatQueueProfileDrv(ctx *context.Context, tenant string, id string) (sq *StatQueueProfile, err error) {
	var result []*StatQueueProfileMdl
	if err = sqls.db.Model(&StatQueueProfileMdl{}).Where(&StatQueueProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return MapStringInterfaceToStatQueueProfile(result[0].StatQueueProfile)
}

func (sqls *SQLStorage) SetStatQueueProfileDrv(ctx *context.Context, sq *StatQueueProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &StatQueueProfileMdl{
		Tenant:           sq.Tenant,
		ID:               sq.ID,
		StatQueueProfile: sq.AsMapStringInterface(),
	}
	if err = tx.Model(&StatQueueProfileMdl{}).Where(
		StatQueueProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		StatQueueProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemStatQueueProfileDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&StatQueueProfileMdl{}).Where(&StatQueueProfileMdl{Tenant: tenant, ID: id}).
		Delete(&StatQueueProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetStatQueueDrv(ctx *context.Context, tenant, id string) (sq *StatQueue, err error) {
	var result []*StatQueueMdl
	if err = sqls.db.Model(&StatQueueMdl{}).Where(&StatQueueMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	ssq, err := MapStringInterfaceToStoredStatQueue(result[0].StatQueue)
	if err != nil {
		return nil, err
	}
	return ssq.AsStatQueue(sqls.ms)
}

func (sqls *SQLStorage) SetStatQueueDrv(ctx *context.Context, ssq *StoredStatQueue, sq *StatQueue) (err error) {
	if ssq == nil {
		if ssq, err = NewStoredStatQueue(sq, sqls.ms); err != nil {
			return
		}
	}
	tx := sqls.db.Begin()
	mdl := &StatQueueMdl{
		Tenant:    ssq.Tenant,
		ID:        ssq.ID,
		StatQueue: ssq.AsMapStringInterface(),
	}
	if err = tx.Model(&StatQueueMdl{}).Where(
		StatQueueMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		StatQueueMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemStatQueueDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&StatQueueMdl{}).Where(&StatQueueMdl{Tenant: tenant, ID: id}).
		Delete(&StatQueueMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetThresholdProfileDrv(ctx *context.Context, tenant, id string) (tp *ThresholdProfile, err error) {
	var result []*ThresholdProfileMdl
	if err = sqls.db.Model(&ThresholdProfileMdl{}).Where(&ThresholdProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return MapStringInterfaceToThresholdProfile(result[0].ThresholdProfile)
}

func (sqls *SQLStorage) SetThresholdProfileDrv(ctx *context.Context, tp *ThresholdProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &ThresholdProfileMdl{
		Tenant:           tp.Tenant,
		ID:               tp.ID,
		ThresholdProfile: tp.AsMapStringInterface(),
	}
	if err = tx.Model(&ThresholdProfileMdl{}).Where(
		ThresholdProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		ThresholdProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemThresholdProfileDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&ThresholdProfileMdl{}).Where(&ThresholdProfileMdl{Tenant: tenant, ID: id}).
		Delete(&ThresholdProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetThresholdDrv(ctx *context.Context, tenant, id string) (tp *Threshold, err error) {
	var result []*ThresholdJSONMdl
	if err = sqls.db.Model(&ThresholdJSONMdl{}).Where(&ThresholdJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return MapStringInterfaceToThreshold(result[0].Threshold)
}

func (sqls *SQLStorage) SetThresholdDrv(ctx *context.Context, t *Threshold) (err error) {
	tx := sqls.db.Begin()
	mdl := &ThresholdJSONMdl{
		Tenant:    t.Tenant,
		ID:        t.ID,
		Threshold: t.AsMapStringInterface(),
	}
	if err = tx.Model(&ThresholdJSONMdl{}).Where(
		ThresholdJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		ThresholdJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveThresholdDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&ThresholdJSONMdl{}).Where(&ThresholdJSONMdl{Tenant: tenant, ID: id}).
		Delete(&ThresholdJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetFilterDrv(ctx *context.Context, tenant, id string) (f *Filter, err error) {
	var result []*FilterJSONMdl
	if err = sqls.db.Model(&FilterJSONMdl{}).Where(&FilterJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return MapStringInterfaceToFilter(result[0].Filter)
}

func (sqls *SQLStorage) SetFilterDrv(ctx *context.Context, f *Filter) (err error) {
	tx := sqls.db.Begin()
	mdl := &FilterJSONMdl{
		Tenant: f.Tenant,
		ID:     f.ID,
		Filter: f.AsMapStringInterface(),
	}
	if err = tx.Model(&FilterJSONMdl{}).Where(
		FilterJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		FilterJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveFilterDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&FilterJSONMdl{}).Where(&FilterJSONMdl{Tenant: tenant, ID: id}).
		Delete(&FilterJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetRouteProfileDrv(ctx *context.Context, tenant, id string) (rp *utils.RouteProfile, err error) {
	var result []*RouteProfileMdl
	if err = sqls.db.Model(&RouteProfileMdl{}).Where(&RouteProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToRouteProfile(result[0].RouteProfile), nil
}

func (sqls *SQLStorage) SetRouteProfileDrv(ctx *context.Context, rp *utils.RouteProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &RouteProfileMdl{
		Tenant:       rp.Tenant,
		ID:           rp.ID,
		RouteProfile: rp.AsMapStringInterface(),
	}
	if err = tx.Model(&RouteProfileMdl{}).Where(
		RouteProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		RouteProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveRouteProfileDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&RouteProfileMdl{}).Where(&RouteProfileMdl{Tenant: tenant, ID: id}).
		Delete(&RouteProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) SetRateProfileDrv(ctx *context.Context, rpp *utils.RateProfile, optOverwrite bool) (err error) {
	tx := sqls.db.Begin()
	rpMdl := &RateProfileJSONMdl{
		Tenant:      rpp.Tenant,
		ID:          rpp.ID,
		RateProfile: rpp.AsMapStringInterface(),
	}
	if optOverwrite {
		if err = tx.Model(&RateMdl{}).Where(&RateMdl{Tenant: rpMdl.Tenant, RateProfileID: rpMdl.ID}).
			Delete(&RateMdl{}).Error; err != nil {
			tx.Rollback()
			return err
		}
		if err = tx.Model(&RateProfileJSONMdl{}).Where(
			RateProfileJSONMdl{Tenant: rpMdl.Tenant, ID: rpMdl.ID}).Delete(
			RateProfileJSONMdl{}).Error; err != nil {
			tx.Rollback()
			return
		}
	}
	var existingRP []*RateProfileJSONMdl
	result := tx.Where(RateProfileJSONMdl{Tenant: rpMdl.Tenant, ID: rpMdl.ID}).Find(&existingRP)
	if result.Error != nil {
		tx.Rollback()
		return result.Error
	}
	if result.RowsAffected > 0 {
		// Update existing
		rpMdl.PK = existingRP[0].PK
	}
	if err = tx.Save(rpMdl).Error; err != nil {
		tx.Rollback()
		return err
	}
	for rID, rate := range rpp.Rates {
		rMdl := &RateMdl{
			Tenant:        rpp.Tenant,
			ID:            rID,
			Rate:          rate.AsMapStringInterface(),
			RateProfileID: rpp.ID,
		}
		if optOverwrite {
			if err = tx.Model(&RateMdl{}).Where(
				RateMdl{Tenant: rMdl.Tenant, ID: rMdl.ID}).Delete(
				RateMdl{}).Error; err != nil {
				tx.Rollback()
				return
			}
		}
		var existingRT []*RateMdl
		result := tx.Where(RateMdl{Tenant: rMdl.Tenant, ID: rMdl.ID, RateProfileID: rpMdl.ID}).Find(&existingRT)
		if result.Error != nil {
			tx.Rollback()
			return result.Error
		}
		if result.RowsAffected > 0 {
			// Update existing
			rMdl.PK = existingRT[0].PK
		}
		if err = tx.Save(rMdl).Error; err != nil {
			tx.Rollback()
			return err
		}
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetRateProfileDrv(ctx *context.Context, tenant, id string) (rpp *utils.RateProfile, err error) {
	var rpResult []*RateProfileJSONMdl
	if err = sqls.db.Model(&RateProfileJSONMdl{}).Where(&RateProfileJSONMdl{Tenant: tenant,
		ID: id}).Find(&rpResult).Error; err != nil {
		return nil, err
	}
	if len(rpResult) == 0 {
		return nil, utils.ErrNotFound
	}

	if rpp, err = utils.MapStringInterfaceToRateProfile(rpResult[0].RateProfile); err != nil {
		return nil, err
	}

	var rtResult []*RateMdl
	if err = sqls.db.Model(&RateMdl{}).Where(&RateMdl{Tenant: tenant,
		RateProfileID: id}).Find(&rtResult).Error; err != nil { // find all rates for that rating profile
		return nil, err
	}
	if len(rtResult) == 0 {
		return nil, utils.ErrNotFound
	}
	for _, rateMdl := range rtResult {
		if rt, err := utils.MapStringInterfaceToRate(rateMdl.Rate); err != nil {
			return nil, err
		} else {
			rpp.Rates[rt.ID] = rt
		}
	}
	return
}

// GetRateProfileRatesDrv will return back all the RateIDs and Rates from a RateProfile
func (sqls *SQLStorage) GetRateProfileRatesDrv(ctx *context.Context, tnt, profileID, rtPrfx string, needIDs bool) (rateIDs []string, rates []*utils.Rate, err error) {
	tx := sqls.db.Model(&RateMdl{}).Where(&RateMdl{RateProfileID: profileID})
	if rtPrfx != utils.EmptyString {
		tx = tx.Where("id LIKE ?", rtPrfx+"%")
	}
	var rtResult []*RateMdl
	if err = tx.Find(&rtResult).Error; err != nil {
		return nil, nil, err
	}
	if len(rtResult) == 0 {
		return nil, nil, utils.ErrNotFound
	}
	for _, ratesMdl := range rtResult {
		rateIDs = append(rateIDs, ratesMdl.ID)
	}
	if needIDs {
		// Only return IDs
		return rateIDs, nil, nil
	}
	for _, rateMdl := range rtResult {
		if rt, err := utils.MapStringInterfaceToRate(rateMdl.Rate); err != nil {
			return nil, nil, err
		} else {
			rates = append(rates, rt)
		}
	}
	return
}

func (sqls *SQLStorage) RemoveRateProfileDrv(ctx *context.Context, tenant, id string, rateIDs *[]string) (err error) {
	tx := sqls.db.Begin()
	if rateIDs != nil {
		for _, rateID := range *rateIDs {
			if err = tx.Model(&RateMdl{}).Where(&RateMdl{Tenant: tenant, ID: rateID, RateProfileID: id}).
				Delete(&RateMdl{}).Error; err != nil {
				tx.Rollback()
				return err
			}
		}
		tx.Commit()
		return
	}

	if err = tx.Model(&RateMdl{}).Where(&RateMdl{Tenant: tenant, RateProfileID: id}).
		Delete(&RateMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	if err = tx.Model(&RateProfileJSONMdl{}).Where(&RateProfileJSONMdl{Tenant: tenant, ID: id}).
		Delete(&RateProfileJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) SetRankingProfileDrv(ctx *context.Context, rp *utils.RankingProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &RankingProfileMdl{
		Tenant:         rp.Tenant,
		ID:             rp.ID,
		RankingProfile: rp.AsMapStringInterface(),
	}
	if err = tx.Model(&RankingProfileMdl{}).Where(
		RankingProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		RankingProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetRankingProfileDrv(ctx *context.Context, tenant string, id string) (rp *utils.RankingProfile, err error) {
	var result []*RankingProfileMdl
	if err = sqls.db.Model(&RankingProfileMdl{}).Where(&RankingProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToRankingProfile(result[0].RankingProfile), nil
}

func (sqls *SQLStorage) RemRankingProfileDrv(ctx *context.Context, tenant string, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&RankingProfileMdl{}).Where(&RankingProfileMdl{Tenant: tenant, ID: id}).
		Delete(&RankingProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetRankingDrv(ctx *context.Context, tenant, id string) (rn *utils.Ranking, err error) {
	var result []*RankingJSONMdl
	if err = sqls.db.Model(&RankingJSONMdl{}).Where(&RankingJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToRanking(result[0].Ranking), nil
}

func (sqls *SQLStorage) SetRankingDrv(_ *context.Context, rn *utils.Ranking) (err error) {
	tx := sqls.db.Begin()
	mdl := &RankingJSONMdl{
		Tenant:  rn.Tenant,
		ID:      rn.ID,
		Ranking: rn.AsMapStringInterface(),
	}
	if err = tx.Model(&RankingJSONMdl{}).Where(
		RankingJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		RankingJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveRankingDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&RankingJSONMdl{}).Where(&RankingJSONMdl{Tenant: tenant, ID: id}).
		Delete(&RankingJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) SetTrendProfileDrv(ctx *context.Context, tp *utils.TrendProfile) (err error) {
	tx := sqls.db.Begin()
	mdl := &TrendProfileMdl{
		Tenant:       tp.Tenant,
		ID:           tp.ID,
		TrendProfile: tp.AsMapStringInterface(),
	}
	if err = tx.Model(&TrendProfileMdl{}).Where(
		TrendProfileMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		TrendProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return

}

func (sqls *SQLStorage) GetTrendProfileDrv(ctx *context.Context, tenant string, id string) (tp *utils.TrendProfile, err error) {
	var result []*TrendProfileMdl
	if err = sqls.db.Model(&TrendProfileMdl{}).Where(&TrendProfileMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToTrendProfile(result[0].TrendProfile)
}

func (sqls *SQLStorage) RemTrendProfileDrv(ctx *context.Context, tenant string, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&TrendProfileMdl{}).Where(&TrendProfileMdl{Tenant: tenant, ID: id}).
		Delete(&TrendProfileMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) GetTrendDrv(ctx *context.Context, tenant, id string) (t *utils.Trend, err error) {
	var result []*TrendJSONMdl
	if err = sqls.db.Model(&TrendJSONMdl{}).Where(&TrendJSONMdl{Tenant: tenant,
		ID: id}).Find(&result).Error; err != nil {
		return nil, err
	}
	if len(result) == 0 {
		return nil, utils.ErrNotFound
	}
	return utils.MapStringInterfaceToTrend(result[0].Trend)
}

func (sqls *SQLStorage) SetTrendDrv(ctx *context.Context, t *utils.Trend) (err error) {
	tx := sqls.db.Begin()
	mdl := &TrendJSONMdl{
		Tenant: t.Tenant,
		ID:     t.ID,
		Trend:  t.AsMapStringInterface(),
	}
	if err = tx.Model(&TrendJSONMdl{}).Where(
		TrendJSONMdl{Tenant: mdl.Tenant, ID: mdl.ID}).Delete(
		TrendJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return
	}
	if err = tx.Save(mdl).Error; err != nil {
		tx.Rollback()
		return
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) RemoveTrendDrv(ctx *context.Context, tenant, id string) (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&TrendJSONMdl{}).Where(&TrendJSONMdl{Tenant: tenant, ID: id}).
		Delete(&TrendJSONMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

// Used to check if specific subject is stored using prefix key attached to entity
func (sqls *SQLStorage) HasDataDrv(ctx *context.Context, category, subject, tenant string) (has bool, err error) {
	var categoryModelMap = map[string]any{
		utils.AccountPrefix:          &AccountJSONMdl{},
		utils.ActionProfilePrefix:    &ActionProfileJSONMdl{},
		utils.ResourcesPrefix:        &ResourceJSONMdl{},
		utils.ResourceProfilesPrefix: &ResourceProfileMdl{},
		utils.IPAllocationsPrefix:    &IPAllocationMdl{},
		utils.IPProfilesPrefix:       &IPProfileMdl{},
		utils.StatQueuePrefix:        &StatQueueMdl{},
		utils.StatQueueProfilePrefix: &StatQueueProfileMdl{},
		utils.ThresholdPrefix:        &ThresholdJSONMdl{},
		utils.ThresholdProfilePrefix: &ThresholdProfileMdl{},
		utils.FilterPrefix:           &FilterJSONMdl{},
		utils.RouteProfilePrefix:     &RouteProfileMdl{},
		utils.AttributeProfilePrefix: &AttributeProfileMdl{},
		utils.ChargerProfilePrefix:   &ChargerProfileMdl{},
		utils.RateProfilePrefix:      &RateProfileJSONMdl{},
		utils.RankingPrefix:          &RankingJSONMdl{},
		utils.RankingProfilePrefix:   &RankingProfileMdl{},
		utils.TrendPrefix:            &TrendJSONMdl{},
		utils.TrendProfilePrefix:     &TrendProfileMdl{},
	}
	model, ok := categoryModelMap[category]
	if !ok {
		return false, fmt.Errorf("unsupported category in HasDataDrv: %s", category)
	}
	var count int64 // if it finds 1, return
	err = sqls.db.Model(model).Where("tenant = ? AND id = ?", tenant, subject).
		Limit(1).Count(&count).Error

	return count > 0, err
}

// AddLoadHistory adds a single load instance to the load history.
func (sqls *SQLStorage) AddLoadHistory(ldInst *utils.LoadInstance,
	loadHistSize int, transactionID string) error {
	if loadHistSize == 0 { // Load history disabled
		return nil
	}
	// Make sure we do it locked since other instances can modify the history while we read it.
	err := guardian.Guardian.Guard(context.TODO(), func(ctx *context.Context) error {
		return sqls.db.Transaction(func(tx *gorm.DB) error {
			var mdl []*LoadInstanceMdl
			if qErr := tx.Table(utils.LoadInstKey).Where(&LoadInstanceMdl{Key: utils.LoadInstKey}).Find(&mdl).Error; qErr != nil {
				return qErr
			}
			var existingLoadHistory []*utils.LoadInstance
			if len(mdl) != 0 && len(mdl[0].LoadInstance) > 0 {
				existingLoadHistory = utils.MapStringInterfaceToLoadInstances(mdl[0].LoadInstance)
			}

			// Insert at the first position
			existingLoadHistory = append(existingLoadHistory, nil)
			copy(existingLoadHistory[1:], existingLoadHistory[0:])
			existingLoadHistory[0] = ldInst

			histLen := len(existingLoadHistory)
			if histLen >= loadHistSize { // Have hit maximum history allowed, remove oldest element
				existingLoadHistory = existingLoadHistory[:loadHistSize]
			}
			newMdl := &LoadInstanceMdl{
				Key:          utils.LoadInstKey,
				LoadInstance: utils.LoadInstancesAsMapStringInterface(existingLoadHistory),
			}

			return tx.Table(utils.LoadInstKey).Save(&newMdl).Error
		})
	}, config.CgrConfig().GeneralCfg().LockingTimeout, utils.LoadInstKey)

	if err != nil {
		return err
	}
	if errCh := Cache.Remove(context.TODO(), utils.LoadInstKey, utils.EmptyString,
		cacheCommit(transactionID), transactionID); errCh != nil {
		return errCh
	}
	return err
}

// Limit will only retrieve the last n items out of history, newest first
func (sqls *SQLStorage) GetLoadHistory(limit int, skipCache bool,
	transactionID string) (loadInsts []*utils.LoadInstance, err error) {
	if limit == 0 {
		return nil, nil
	}
	if !skipCache {
		x, ok := Cache.Get(utils.LoadInstKey, utils.EmptyString)
		if ok {
			if x != nil {
				items, ok := x.([]*utils.LoadInstance)
				if !ok {
					return nil, utils.ErrCastFailed
				}
				if len(items) < limit || limit == -1 {
					return items, nil
				}
				return items[:limit], nil
			}
			return nil, utils.ErrNotFound
		}
	}
	var mdl []*LoadInstanceMdl
	if err := sqls.db.Table(utils.LoadInstKey).Where(&LoadInstanceMdl{Key: utils.LoadInstKey}).Find(&mdl).Error; err != nil {
		return nil, err
	} else if len(mdl) == 0 {
		return nil, utils.ErrNotFound
	}
	loadInstances := utils.MapStringInterfaceToLoadInstances(mdl[0].LoadInstance)
	cCommit := cacheCommit(transactionID)
	if errCh := Cache.Remove(context.TODO(), utils.LoadInstKey, utils.EmptyString, cCommit, transactionID); errCh != nil {
		return nil, errCh
	}
	if errCh := Cache.Set(context.TODO(), utils.LoadInstKey, utils.EmptyString, loadInstances, nil, cCommit, transactionID); errCh != nil {
		return nil, errCh
	}
	if len(loadInstances) < limit || limit == -1 {
		return loadInstances, nil
	}
	return loadInstances[:limit], nil
}

func (sqls *SQLStorage) GetItemLoadIDsDrv(ctx *context.Context, itemIDPrefix string) (loadIDs map[string]int64, err error) {
	var mdl []LoadIDMdl
	tx := sqls.db.Table(utils.TBLLoadIDs)
	if err := tx.Find(&mdl).Error; err != nil {
		return nil, err
	} else if len(mdl) == 0 || len(mdl[0].LoadIDs) == 0 {
		return nil, utils.ErrNotFound
	}
	loadIDs = make(map[string]int64)
	// Filter by prefix if specified
	if itemIDPrefix != utils.EmptyString {
		for key, value := range mdl[0].LoadIDs {
			if strings.HasPrefix(key, itemIDPrefix) {
				loadIDs[key] = int64(value.(float64))
			}
		}
		if len(loadIDs) == 0 {
			return nil, utils.ErrNotFound
		}
	} else {
		for k, v := range mdl[0].LoadIDs {
			loadIDs[k] = int64(v.(float64))
		}
	}
	return loadIDs, nil
}

func (sqls *SQLStorage) SetLoadIDsDrv(ctx *context.Context, loadIDs map[string]int64) error {
	return sqls.db.Transaction(func(tx *gorm.DB) error {
		var existing []*LoadIDMdl
		if err := tx.Table(utils.TBLLoadIDs).Find(&existing).Error; err != nil {
			return err
		}
		if len(existing) == 0 {
			loadIdsMapAny := make(map[string]any)
			for k, v := range loadIDs {
				loadIdsMapAny[k] = v
			}
			existing = append([]*LoadIDMdl{}, &LoadIDMdl{
				LoadIDs: loadIdsMapAny,
			})
		}
		for k, v := range loadIDs {
			existing[0].LoadIDs[k] = v
		}
		return tx.Table(utils.TBLLoadIDs).Save(&existing[0]).Error
	})
}

func (sqls *SQLStorage) RemoveLoadIDsDrv() (err error) {
	tx := sqls.db.Begin()
	if err = tx.Model(&LoadIDMdl{}).Delete(&LoadIDMdl{}).Error; err != nil {
		tx.Rollback()
		return err
	}
	tx.Commit()
	return
}

func (sqls *SQLStorage) getAllIndexKeys(tenant, typePrefix string) ([]string, error) {
	var keys []string
	if err := sqls.db.Model(&IndexMdl{}).
		Where("tenant = ? AND type LIKE ?", tenant, typePrefix+"%").
		Pluck("key", &keys).Error; err != nil {
		return nil, err
	}
	return keys, nil
}

// GetIndexesDrv retrieves Indexes from DB
// tenants, item types, keys and values are stored in seperate columns
func (sqls *SQLStorage) GetIndexesDrv(ctx *context.Context, idxItmType, tntCtx, idxKey, transactionID string) (indexes map[string]utils.StringSet, err error) {
	originItemType := utils.CacheInstanceToPrefix[idxItmType]
	itemType := originItemType
	if transactionID != utils.EmptyString {
		itemType = "tmp_" + utils.ConcatenatedKey(originItemType, transactionID)
	}
	var indexesFound []*IndexMdl
	if err := sqls.db.Transaction(func(tx *gorm.DB) error {
		tx = tx.Where(&IndexMdl{Tenant: tntCtx, Type: itemType})
		if len(idxKey) != 0 {
			tx = tx.Where(&IndexMdl{Key: idxKey})
		}
		return tx.Find(&indexesFound).Error
	}); err != nil {
		return nil, err
	}
	if len(indexesFound) == 0 {
		return nil, utils.ErrNotFound
	}
	indexes = make(map[string]utils.StringSet)
	for _, indexFound := range indexesFound {
		indexes[indexFound.Key] = utils.MapStringAnyToStringSet(indexFound.Value)
	}
	return indexes, nil
}

// SetIndexesDrv stores Indexes into DB
// tenants, item types, keys and values are stored in seperate columns
func (sqls *SQLStorage) SetIndexesDrv(ctx *context.Context, idxItmType, tntCtx string,
	indexes map[string]utils.StringSet, commit bool, transactionID string) (err error) {
	originItemType := utils.CacheInstanceToPrefix[idxItmType]
	itemType := originItemType
	if transactionID != utils.EmptyString {
		itemType = "tmp_" + utils.ConcatenatedKey(originItemType, transactionID)
	}
	if commit && transactionID != utils.EmptyString { // only fully commit transactions
		keys, err := sqls.getAllIndexKeys(tntCtx, itemType)
		if err != nil {
			return err
		}
		err = sqls.db.Transaction(func(tx *gorm.DB) error {
			for _, key := range keys {
				// ensure no duplicates exist
				if err := tx.Where(&IndexMdl{Tenant: tntCtx, Type: originItemType,
					Key: key}, key).Delete(&IndexMdl{}).Error; err != nil {
					return err
				}
				if err := tx.Model(&IndexMdl{}).Where(&IndexMdl{Tenant: tntCtx,
					Type: "tmp_" + utils.ConcatenatedKey(originItemType, transactionID),
					Key:  key}). // only update the key
					Update("type", originItemType).Error; err != nil {
					return err
				}
			}
			return nil
		})
		if err != nil {
			return err
		}
	}
	var lastErr error
	for idxKey, itmMp := range indexes {
		err := sqls.db.Transaction(func(tx *gorm.DB) error {
			if err := tx.Where(&IndexMdl{Tenant: tntCtx, Type: itemType, Key: idxKey}).
				Delete(&IndexMdl{}).Error; err != nil {
				return err
			}
			if len(itmMp) == 0 {
				//  DELETE entry it empty index
				return nil
			}
			// upsert entry
			return tx.Save(&IndexMdl{
				Tenant: tntCtx,
				Type:   itemType,
				Key:    idxKey,
				Value:  itmMp.ToMapStringAny(),
			}).Error
		})
		if err != nil {
			lastErr = err
		}
	}
	return lastErr
}

// RemoveIndexesDrv removes the indexes
func (sqls *SQLStorage) RemoveIndexesDrv(ctx *context.Context, idxItmType, tntCtx, idxKey string) (err error) {
	if len(idxKey) != 0 {
		return sqls.db.Transaction(func(tx *gorm.DB) error {
			return tx.Where(&IndexMdl{Tenant: tntCtx, Type: idxItmType, Key: idxKey}).
				Delete(&IndexMdl{}).Error
		})
	}
	return sqls.db.Transaction(func(tx *gorm.DB) error {
		return tx.Where(&IndexMdl{Tenant: tntCtx, Type: idxItmType}).
			Delete(&IndexMdl{}).Error
	})
}

// DataDB method not implemented yet
func (sqls *SQLStorage) GetConfigSectionsDrv(ctx *context.Context, nodeID string, sectionIDs []string) (sectionMap map[string][]byte, err error) {
	return nil, utils.ErrNotImplemented
}

// DataDB method not implemented yet
func (sqls *SQLStorage) SetConfigSectionsDrv(ctx *context.Context, nodeID string, sectionsData map[string][]byte) (err error) {
	return utils.ErrNotImplemented
}

// DataDB method not implemented yet
func (sqls *SQLStorage) RemoveConfigSectionsDrv(ctx *context.Context, nodeID string, sectionIDs []string) (err error) {
	return utils.ErrNotImplemented
}

// ConfigDB method not implemented yet
func (sqls *SQLStorage) GetSection(ctx *context.Context, section string, val any) (err error) {
	return utils.ErrNotImplemented
}

// ConfigDB method not implemented yet
func (sqls *SQLStorage) SetSection(_ *context.Context, section string, jsn any) (err error) {
	return utils.ErrNotImplemented
}

// Only intended for InternalDB
func (sqls *SQLStorage) BackupConfigDB(backupFolderPath string, zip bool) (err error) {
	return utils.ErrNotImplemented
}

// BackupDataDB used only for InternalDB
func (sqls *SQLStorage) BackupDataDB(backupFolderPath string, zip bool) (err error) {
	return utils.ErrNotImplemented
}

// Will dump everything inside DB to a file, only for InternalDB
func (sqls *SQLStorage) DumpConfigDB() (err error) {
	return utils.ErrNotImplemented
}

// Will dump everything inside DB to a file, only for InternalDB
func (sqls *SQLStorage) DumpDataDB() (err error) {
	return utils.ErrNotImplemented
}

// Will rewrite every dump file of DataDB,  only for InternalDB
func (sqls *SQLStorage) RewriteDataDB() (err error) {
	return utils.ErrNotImplemented
}

// Only intended for InternalDB
func (sqls *SQLStorage) RewriteConfigDB() (err error) {
	return utils.ErrNotImplemented
}