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 DictTypeRepository interface {
	InsertDictType(ctx context.Context, dictType *model.DictType) (sql.Result, error)
	UpdateDictType(ctx context.Context, dictType *model.DictType) (sql.Result, error)
	DeleteDictType(ctx context.Context, id int64) (sql.Result, error)
	GetDictTypeByID(ctx context.Context, id int64) (*model.DictType, error)
	SelectDictTypesPage(ctx context.Context, pageInfo *v1.DictTypePageInfo) ([]model.DictType, int64, error)
	SelectDictTypesWithDatas(ctx context.Context) ([]model.DictType, error)
}

type dictTypeRepository struct {
	generic GenericRepository[model.DictType]
}

func NewDictTypeRepository(r *Repository, v *viper.Viper, s *sid.Sid) DictTypeRepository {
	return &dictTypeRepository{generic: NewGenericRepository[model.DictType](r, v, s)}
}

const (
	// SQL statement to insert a new menu record.
	insertDictTypeSQL = `
		INSERT INTO sys_dict_type (id, type, name, description, created_at, created_by, updated_at, updated_by) VALUES (:id, :type, :name, :description, :created_at, :created_by, :updated_at, :updated_by)`

	// SQL statement to update an existing menu record.
	updateDictTypeSQL = `
		UPDATE sys_dict_type 
		SET 
			type = :type,
			name = :name,
			description = :description,
			updated_at = :updated_at,
			updated_by = :updated_by
		WHERE id = :id`

	// SQL statement to delete a dict_type by ID.
	deleteDictTypeSQL = `
		DELETE FROM sys_dict_type 
		WHERE id = $1`

	// SQL statement to get a dict_type by ID.
	getDictTypeByIDSQL = `
		SELECT 
			id, type, name, description
		FROM sys_dict_type 
		WHERE id = $1`

	countDictTypeSQL = `
		SELECT COUNT(*) FROM sys_dict_type`

	// SQL statement to list all dict_type with creator and updater usernames.
	selectDictTypePageSQL = `
		SELECT 
            dt.id,
            dt.type,
            dt.name,
            dt.description,
            dt.created_at,
            COALESCE(creator.username, 'Unknown') AS creator,
            dt.updated_at,
            COALESCE(updater.username, 'Unknown') AS updater
        FROM sys_dict_type dt
        LEFT JOIN sys_user creator ON dt.created_by = creator.id
        LEFT JOIN sys_user updater ON dt.updated_by = updater.id`

	// SQL statement to get dict_types
	selectDictTypesWithDictDatasSQL = `
		SELECT
            dt.type AS type,
            dd.label AS dd_label,
            dd.value AS dd_value
        FROM sys_dict_type dt
        LEFT JOIN sys_dict_data dd ON dt.id = dd.type_id`
)

// DeleteDictType implements DictTypeRepository.
func (d *dictTypeRepository) DeleteDictType(ctx context.Context, id int64) (sql.Result, error) {
	if id <= 0 {
		return nil, errors.New("invalid dict_type id")
	}

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

// GetDictTypeByID implements DictTypeRepository.
func (d *dictTypeRepository) GetDictTypeByID(ctx context.Context, id int64) (*model.DictType, error) {
	if id <= 0 {
		return nil, errors.New("invalid dict_type id")
	}

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

// InsertDictType implements DictTypeRepository.
func (d *dictTypeRepository) InsertDictType(ctx context.Context, dictType *model.DictType) (sql.Result, error) {
	if dictType == nil {
		return nil, errors.New("dict_type can not be nil")
	}

	return d.generic.Insert(ctx, insertDictTypeSQL, dictType)
}

// SelectDictTypesPage implements DictTypeRepository.
func (d *dictTypeRepository) SelectDictTypesPage(ctx context.Context, pageInfo *v1.DictTypePageInfo) ([]model.DictType, int64, error) {
	var (
		total  int64
		err    error
		query  strings.Builder
		params = make(map[string]interface{})
		order  = "DESC"
	)

	if pageInfo.Asc {
		order = "ASC"
	}

	query.WriteString(selectDictTypePageSQL)

	total, err = d.generic.Count(ctx, countDictTypeSQL)
	if err != nil {
		return nil, 0, err
	}

	query.WriteString(fmt.Sprintf(" ORDER BY %s %s LIMIT :size OFFSET :offset", pageInfo.Sort, order))
	params["size"] = pageInfo.Size
	params["offset"] = pageInfo.Offset
	resources, err := d.generic.SelectNamed(ctx, query.String(), params)
	if err != nil {
		return nil, 0, err
	}
	return resources, total, nil
}

// SelectDictTypesWithDatas implements DictTypeRepository.
func (d *dictTypeRepository) SelectDictTypesWithDatas(ctx context.Context) ([]model.DictType, error) {
	var rows []struct {
		Type  string `db:"type"`
		Label string `db:"dd_label"`
		Value string `db:"dd_value"`
	}

	db := d.generic.GetDB(ctx)
	if err := db.Select(&rows, selectDictTypesWithDictDatasSQL); err != nil {
		return nil, err
	}

	dictTypeMap := make(map[string]*model.DictType)
	for _, row := range rows {
		dictType, exists := dictTypeMap[row.Type]
		if !exists {
			dictType = &model.DictType{Type: row.Type}
			dictType.DictDtas = make([]model.DictData, 0)
			dictTypeMap[dictType.Type] = dictType
		}
		if row.Label != "" { // 检查是否有关联订单
			dictType.DictDtas = append(dictType.DictDtas, model.DictData{Label: row.Label, Value: row.Value})
		}
	}

	// 4. 转换为切片返回
	dictTypes := make([]model.DictType, 0, len(dictTypeMap))
	for _, dictType := range dictTypeMap {
		dictTypes = append(dictTypes, *dictType)
	}
	return dictTypes, nil
}

// UpdateDictType implements DictTypeRepository.
func (d *dictTypeRepository) UpdateDictType(ctx context.Context, dictType *model.DictType) (sql.Result, error) {
	if dictType == nil {
		return nil, errors.New("dict_type can not be nil")
	}

	return d.generic.Update(ctx, updateDictTypeSQL, dictType)
}
optimize the code and add comments