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"
)

// DictTypeRepository defines methods for CRUD operations on DictType entities.
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]
}

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

// SQL query constants
const (
	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
	)`

	updateDictTypeSQL = `UPDATE sys_dict_type 
	SET 
		type = :type,
		name = :name,
		description = :description,
		updated_at = :updated_at,
		updated_by = :updated_by
	WHERE id = :id`

	deleteDictTypeSQL = `DELETE FROM sys_dict_type 
	WHERE id = $1`

	getDictTypeByIDSQL = `SELECT 
		id,
		type,
		name,
		description
	FROM sys_dict_type 
	WHERE id = $1`

	countDictTypeSQL = `SELECT COUNT(*) 
	FROM sys_dict_type`

	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`

	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 deletes a DictType by its ID.
// Returns sql.Result and error if the ID is invalid or the operation fails.
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 fetches a DictType by its ID.
// Returns the DictType or an error if the ID is invalid or not found.
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 inserts a new DictType into the database.
// Returns sql.Result or an error if dictType is nil or the insert fails.
func (d *dictTypeRepository) InsertDictType(ctx context.Context, dictType *model.DictType) (sql.Result, error) {
	if dictType == nil {
		return nil, errors.New("dict_type cannot be nil")
	}
	return d.generic.Insert(ctx, insertDictTypeSQL, dictType)
}

// UpdateDictType updates an existing DictType based on its ID.
// Returns sql.Result or an error if dictType is nil or the update fails.
func (d *dictTypeRepository) UpdateDictType(ctx context.Context, dictType *model.DictType) (sql.Result, error) {
	if dictType == nil {
		return nil, errors.New("dict_type cannot be nil")
	}
	return d.generic.Update(ctx, updateDictTypeSQL, dictType)
}

// SelectDictTypesPage returns a paginated list of DictTypes, including creator/updater info.
// It supports sorting and pagination as specified by pageInfo.
// Returns the list of DictTypes, total count, and an error if any.
func (d *dictTypeRepository) SelectDictTypesPage(ctx context.Context, pageInfo *v1.DictTypePageInfo) ([]model.DictType, int64, error) {
	if pageInfo == nil {
		return nil, 0, errors.New("pageInfo cannot be nil")
	}

	order := "DESC"
	if pageInfo.Asc {
		order = "ASC"
	}

	var query strings.Builder
	query.WriteString(selectDictTypePageSQL)

	// Get total count of dict_types.
	total, err := d.generic.Count(ctx, countDictTypeSQL)
	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))

	// Parameters for pagination.
	params := map[string]interface{}{
		"size":   pageInfo.Size,
		"offset": pageInfo.Offset,
	}

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

	return dictTypes, total, nil
}

// SelectDictTypesWithDatas returns a list of DictTypes where each DictType
// includes its associated DictDatas.
// Returns a slice of DictTypes or an error.
func (d *dictTypeRepository) SelectDictTypesWithDatas(ctx context.Context) ([]model.DictType, error) {
	// Temporary struct to hold joined query results.
	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
	}

	// Map to aggregate DictDatas per DictType.
	dictTypeMap := make(map[string]*model.DictType)

	for _, row := range rows {
		dictType, exists := dictTypeMap[row.Type]
		if !exists {
			dictType = &model.DictType{
				Type:      row.Type,
				DictDatas: make([]model.DictData, 0), // Fixed typo DictDtas -> DictDatas
			}
			dictTypeMap[row.Type] = dictType
		}
		// Only append if Label is not empty (indicates associated DictData exists).
		if row.Label != "" {
			dictType.DictDatas = append(dictType.DictDatas, model.DictData{
				Label: row.Label,
				Value: row.Value,
			})
		}
	}

	// Convert map to slice for return.
	dictTypes := make([]model.DictType, 0, len(dictTypeMap))
	for _, dt := range dictTypeMap {
		dictTypes = append(dictTypes, *dt)
	}

	return dictTypes, nil
}
please optimize the code