package repository

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	v1 "template-plus/api/v1"
	"template-plus/internal/model"
	"template-plus/pkg/sid"

	"github.com/spf13/viper"
)

type DictDataRepository interface {
	InsertDictData(ctx context.Context, dictData *model.DictData) (sql.Result, error)
	UpdateDictData(ctx context.Context, dictData *model.DictData) (sql.Result, error)
	DeleteDictData(ctx context.Context, id int64) (sql.Result, error)
	GetDictDataByID(ctx context.Context, id int64) (*model.DictData, error)
	GetCountDictData(ctx context.Context, dictTypeID int64) (int64, error)
	SelectDictDatasPage(ctx context.Context, pageInfo *v1.DictDataPageInfo) ([]model.DictData, int64, error)
}

type dictDataRepository struct {
	generic GenericRepository[model.DictData]
}

func NewDictDataRepository(r *Repository, v *viper.Viper, s *sid.Sid) DictDataRepository {
	return &dictDataRepository{generic: NewGenericRepository[model.DictData](r, v, s)}
}

const (
	// SQL statement to insert a new dict_data record.
	insertDictDataSQL = `
		INSERT INTO sys_dict_data (id, type_id, value, label, description, created_at, created_by, updated_at, updated_by) VALUES (:id, :type_id, :value, :label, :description, :created_at, :created_by, :updated_at, :updated_by)`

	// SQL statement to update an existing dict_data record.
	updateDictDataSQL = `
		UPDATE sys_dict_data
		SET
			type_id = :type_id,
			value = :value,
			label = :label,
			description = :description,
			updated_at = :updated_at,
			updated_by = :updated_by
		WHERE id = :id`

	// SQL statement to delete a dict_data by ID.
	deleteDictDataSQL = `
		DELETE FROM sys_dict_data
		WHERE id = $1`

	// SQL statement to get a dict_data by ID.
	getDictDataByIDSQL = `
		SELECT
			id, type_id, value, label, description
		FROM sys_dict_data
		WHERE id = $1`

	countDictDataSQL = `
		SELECT COUNT(*) FROM sys_dict_data`

	// SQL statement to list all dict_data with creator and updater usernames.
	selectDictDataPageSQL = `
		SELECT 
			dd.type_id,
            dd.id,
            dd.value,
            dd.label,
            dd.description,
            dd.created_at,
            COALESCE(creator.username, 'Unknown') AS creator,
            dd.updated_at,
            COALESCE(updater.username, 'Unknown') AS updater
        FROM sys_dict_data dd
        LEFT JOIN sys_user creator ON dd.created_by = creator.id
        LEFT JOIN sys_user updater ON dd.updated_by = updater.id`
)

// DeleteDictData implements DictDataRepository.
func (d *dictDataRepository) DeleteDictData(ctx context.Context, id int64) (sql.Result, error) {
	if id <= 0 {
		return nil, errors.New("invalid dict_data id")
	}

	return d.generic.Delete(ctx, deleteDictDataSQL, id)
}

// GetDictDataByID implements DictDataRepository.
func (d *dictDataRepository) GetDictDataByID(ctx context.Context, id int64) (*model.DictData, error) {
	if id <= 0 {
		return nil, errors.New("invalid dict_data id")
	}

	return d.generic.Get(ctx, getDictDataByIDSQL, id)
}

// InsertDictData implements DictDataRepository.
func (d *dictDataRepository) InsertDictData(ctx context.Context, dictData *model.DictData) (sql.Result, error) {
	if dictData == nil {
		return nil, errors.New("dict_data cannot be nil")
	}

	return d.generic.Insert(ctx, insertDictDataSQL, dictData)
}

// SelectDictDatasPage implements DictDataRepository.
func (d *dictDataRepository) SelectDictDatasPage(ctx context.Context, pageInfo *v1.DictDataPageInfo) ([]model.DictData, int64, error) {
	if pageInfo == nil {
		return nil, 0, errors.New("pageInfo cannot be nil")
	}

	var (
		total  int64
		err    error
		query  strings.Builder
		params = make(map[string]any)
		order  = "DESC"
	)

	if pageInfo.Asc {
		order = "ASC"
	}

	query.WriteString(selectDictDataPageSQL)

	// Get total count of dict_types.
	if pageInfo.DictTypeID != 0 {
		query.WriteString(" WHERE dd.type_id = :type_id")
		params["type_id"] = pageInfo.DictTypeID
		total, err = d.generic.Count(ctx, countDictDataSQL+" WHERE type_id = $1", pageInfo.DictTypeID)
	} else {
		total, err = d.generic.Count(ctx, countDictDataSQL)
	}

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

	// Append order, limit and offset for pagination.
	query.WriteString(fmt.Sprintf(" ORDER BY %s %s LIMIT :size OFFSET :offset", pageInfo.Sort, order))
	params["size"] = pageInfo.Size
	params["offset"] = pageInfo.Offset

	// Fetch paginated results.
	dictDatas, err := d.generic.SelectNamed(ctx, query.String(), params)
	if err != nil {
		return nil, 0, err
	}

	return dictDatas, total, nil
}

// UpdateDictData implements DictDataRepository.
func (d *dictDataRepository) UpdateDictData(ctx context.Context, dictData *model.DictData) (sql.Result, error) {
	if dictData == nil {
		return nil, errors.New("dict_data cannot be null")
	}

	return d.generic.Update(ctx, updateDictDataSQL, dictData)
}

// GetCountDictData implements DictDataRepository.
func (d *dictDataRepository) GetCountDictData(ctx context.Context, dictTypeID int64) (int64, error) {
	if dictTypeID <= 0 {
		return d.generic.Count(ctx, countAuditLogSQL)
	} else {
		return d.generic.Count(ctx, countAuditLogSQL+" WHERE type_id = $1", dictTypeID)
	}
}
please optimize the code