package dbHandler

import (
	"errors"
	"fmt"
	"path/filepath"
	"reflect"
	"strings"

	"gitea.tecamino.com/paadi/tecamino-logger/logging"
	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

// DBHandler
//
// Description:
//
//	Wraps the GORM database connection and provides helper methods for
//	common CRUD operations, as well as integrated logging for traceability.
//
// Fields:
//   - db:     Active GORM database connection.
//   - logger: Pointer to a custom logger instance for structured logging.
type DBHandler struct {
	db     *gorm.DB
	logger *logging.Logger
}

// NewDBHandler
//
// Description:
//
//	Creates a new database handler using the specified SQLite database file.
//
// Behavior:
//  1. Opens a GORM connection to the database file at `dbPath`.
//  2. Wraps it in a `DBHandler` struct with logging support.
//
// Parameters:
//   - dbPath: Path to the SQLite database file.
//   - logger: Logging instance to record DB operations.
//
// Returns:
//   - dH:  A pointer to the initialized `DBHandler`.
//   - err: Any error encountered during database connection.
func NewDBHandler(name, path string, logger *logging.Logger) (dH *DBHandler, err error) {
	if logger == nil {
		logger, err = logging.NewLogger(filepath.Join(path, name)+".log", nil)
		if err != nil {
			return
		}
	}

	dH = &DBHandler{logger: logger}

	if filepath.Ext(name) == "" {
		name += ".db"
	}
	dbPath := filepath.Join(path, name)
	logger.Debug("NewDBHandler", "open database "+dbPath)
	dH.db, err = gorm.Open(sqlite.Open(dbPath), &gorm.Config{})
	return
}

// addNewTable
//
// Description:
//
//	Uses GORM’s `AutoMigrate` to create or update the database schema
//	for the provided model type.
//
// Parameters:
//   - model: Struct type representing the database table schema.
//
// Returns:
//   - error: Any migration error encountered.
func (dH *DBHandler) AddNewTable(model any) error {
	return dH.db.AutoMigrate(&model)
}

// addNewColum
//
// Description:
//
//	Inserts a new record into the database table corresponding to `model`.
//
// Parameters:
//   - model: Struct instance containing values to be inserted.
//
// Returns:
//   - error: Any error encountered during record creation.
func (dH *DBHandler) AddNewColum(model any) error {
	return dH.db.Create(model).Error
}

// getById
//
// Description:
//
//	Retrieves a record (or all records) from a table by numeric ID.
//
// Behavior:
//   - If `id == 0`, returns all records in the table.
//   - Otherwise, fetches the record matching the given ID.
//
// Parameters:
//   - model: Pointer to a slice or struct to store the result.
//   - id:    Numeric ID to query by.
//
// Returns:
//   - error: Any query error or “not found” message.
func (dH *DBHandler) GetById(model any, id uint) error {
	dH.logger.Debug("getById", "find id "+fmt.Sprint(id))

	if id == 0 {
		return dH.db.Find(model).Error
	}

	err := dH.db.First(model, id).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return fmt.Errorf("no record found for id: %v", id)
	} else if err != nil {
		return fmt.Errorf("query failed: %w", err)
	}
	return nil
}

// getByKey
//
// Description:
//
//	Retrieves one or more records matching a key/value pair.
//
// Behavior:
//   - If `LikeSearch` is true, performs a SQL LIKE query.
//   - Otherwise, performs an exact match query.
//
// Parameters:
//   - model:      Pointer to a slice or struct to store results.
//   - key:        Column name (e.g., "email").
//   - value:      Value to match or partially match.
//   - LikeSearch: If true, replaces '*' with '%' for wildcard matching.
//
// Returns:
//   - error: Any database query error.
func (dH *DBHandler) GetByKey(model any, key string, value any, LikeSearch bool) error {
	if LikeSearch {
		value = strings.ReplaceAll(fmt.Sprint(value), "*", "%")
		dH.logger.Debug("getByKey", "find like key "+key+" value "+fmt.Sprint(value))
		return dH.db.Where(key+" LIKE ?", value).Find(model).Error
	}

	dH.logger.Debug("getByKey", "find equal key "+key+" value "+fmt.Sprint(value))
	return dH.db.Find(model, key+" = ?", value).Error
}

// updateValuesById
//
// Description:
//
//	Updates record fields based on their unique ID.
//
// Behavior:
//  1. Confirms that `model` is a pointer to a struct.
//  2. Fetches the record by ID.
//  3. Updates all non-zero fields using `gorm.Model.Updates`.
//
// Parameters:
//   - model: Pointer to struct containing new values.
//   - id:    Numeric ID of the record to update.
//
// Returns:
//   - error: If the model is invalid or query/update fails.
func (dH *DBHandler) UpdateValuesById(model any, id uint) error {
	dH.logger.Debug("updateValuesById", "model"+fmt.Sprint(model))
	modelType := reflect.TypeOf(model)
	if modelType.Kind() != reflect.Ptr {
		return errors.New("model must be a pointer to struct")
	}

	lookUpModel := reflect.New(modelType.Elem()).Interface()
	if err := dH.GetById(lookUpModel, id); err != nil {
		return err
	}
	return dH.db.Model(lookUpModel).Updates(model).Error
}

// updateValuesByKey
//
// Description:
//
//	Updates records based on a key/value match.
//
// Behavior:
//  1. Confirms model type.
//  2. Fetches the matching record(s) using `getByKey`.
//  3. Updates all non-zero fields.
//
// Parameters:
//   - model: Pointer to struct containing updated values.
//   - key:   Column name to filter by.
//   - value: Value to match.
//
// Returns:
//   - error: Any query or update error.
func (dH *DBHandler) UpdateValuesByKey(model any, key string, value any) error {
	dH.logger.Debug("updateValuesByKey", "model"+fmt.Sprint(model))
	modelType := reflect.TypeOf(model)
	if modelType.Kind() != reflect.Ptr {
		return errors.New("model must be a pointer to struct")
	}

	lookUpModel := reflect.New(modelType.Elem()).Interface()
	if err := dH.GetByKey(lookUpModel, key, value, false); err != nil {
		return err
	}
	return dH.db.Model(lookUpModel).Updates(model).Error
}

// deleteById
//
// Description:
//
//	Deletes records by their ID(s).
//
// Behavior:
//   - If the first ID == 0, all records in the table are deleted.
//   - Otherwise, deletes the provided IDs.
//
// Parameters:
//   - model: Model struct type representing the table.
//   - id:    Variadic list of IDs to delete.
//
// Returns:
//   - error: Any deletion error.
func (dH *DBHandler) DeleteById(model any, ids ...uint) error {
	if len(ids) == 0 {
		return fmt.Errorf("no IDs provided")
	}

	if ids[0] == 0 {
		dH.logger.Debug("deleteById", "delete all")
		return dH.db.Where("1 = 1").Delete(model).Error
	}

	dH.logger.Debug("deleteById", "delete ids "+fmt.Sprint(ids))
	err := dH.Exists(model, "id", ids, false)

	// Always use Model() to avoid GORM’s “limit 1” safeguard
	if err := dH.db.Model(model).Where("id IN ?", ids).Delete(nil).Error; err != nil {
		return fmt.Errorf("delete failed: %w", err)
	}

	return err
}

// deleteByKey
//
// Description:
//
//	Deletes records that match a key/value pair.
//
// Behavior:
//   - Supports LIKE queries if `LikeSearch` is true.
//
// Parameters:
//   - model:      Model struct type representing the table.
//   - key:        Column name to filter by.
//   - value:      Value to match.
//   - LikeSearch: Whether to use wildcard search.
//
// Returns:
//   - error: Any deletion error.
func (dH *DBHandler) DeleteByKey(model any, key string, value any, LikeSearch bool) error {
	if LikeSearch {
		value = strings.ReplaceAll(fmt.Sprint(value), "*", "%")
		dH.logger.Debug("deleteByKey", "delete like key "+key+" value "+fmt.Sprint(value))
		return dH.db.Where(key+" LIKE ?", value).Delete(model).Error
	}

	dH.logger.Debug("deleteByKey", "delete equal key "+key+" value "+fmt.Sprint(value))
	return dH.db.Where(key+" = ?", value).Delete(model).Error
}

// exists
//
// Description:
//
//	Checks whether a record exists matching the specified key/value filter.
//
// Behavior:
//   - Performs a `First` query on the database.
//   - If `LikeSearch` is true, performs a LIKE query.
//   - Returns an error if the record does not exist or query fails.
//
// Parameters:
//   - model:      Model struct type to search.
//   - key:        Column name to filter by.
//   - value:      Value to match.
//   - LikeSearch: Whether to use wildcard search.
//
// Returns:
//   - error: “no record found” or DB error.
func (dH *DBHandler) Exists(model any, key string, value any, likeSearch bool) error {
	var query string
	var args any

	// Detect LIKE and IN behavior
	val := reflect.ValueOf(value)
	isSlice := val.Kind() == reflect.Slice

	switch {
	case isSlice:
		if val.Len() == 0 {
			return fmt.Errorf("empty slice for %s", key)
		}
		query = fmt.Sprintf("%s IN ?", key)
		args = value

	case likeSearch:
		str := strings.ReplaceAll(fmt.Sprint(value), "*", "%")
		query = fmt.Sprintf("%s LIKE ?", key)
		args = str

	default:
		query = fmt.Sprintf("%s = ?", key)
		args = value
	}

	dH.logger.Debug("exists", "checking existence for key "+query+" value "+fmt.Sprint(args))

	var count int64
	tx := dH.db.Model(model).Where(query, args).Count(&count)
	if tx.Error != nil {
		return fmt.Errorf("query failed: %w", tx.Error)
	}

	if count == 0 {
		return fmt.Errorf("no record found for %s %v", key, value)
	}

	return nil
}