package repository

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"

	"github.com/spf13/viper"

	v1 "template-plus/api/v1"
	"template-plus/internal/model"
	"template-plus/pkg/sid"
)

// ButtonRepository interface defines button-specific database operations.
type ButtonRepository interface {
	InsertButton(ctx context.Context, button *model.Button) (sql.Result, error)
	UpdateButton(ctx context.Context, button *model.Button) (sql.Result, error)
	DeleteButton(ctx context.Context, id int64) (sql.Result, error)
	GetButtonByID(ctx context.Context, id int64) (*model.Button, error)
	SelectButtonPage(ctx context.Context, pageInfo *v1.ButtonPageInfo) ([]model.Button, int64, error)
	SelectAssignedButtonsByRoleIDs(ctx context.Context, roleIDs []int64) ([]model.Button, error)
	SelectButtonsByRoleID(ctx context.Context, menuID, roleID int64) ([]model.Button, error)
}

// buttonRepository implements ButtonRepository.
type buttonRepository struct {
	generic GenericRepository[model.Button]
}

// NewButtonRepository creates a new ButtonRepository instance.
func NewButtonRepository(r *Repository, v *viper.Viper, s *sid.Sid) ButtonRepository {
	return &buttonRepository{generic: NewGenericRepository[model.Button](r, v, s)}
}

const (
	insertButtonSQL = `
		INSERT INTO sys_button (
			id, menu_id, name, code, description, 
			created_at, created_by, updated_at, updated_by
		) VALUES (
			:id, :menu_id, :name, :code, :description,
			:created_at, :created_by, :updated_at, :updated_by
		)`

	updateButtonSQL = `
		UPDATE sys_button 
		SET menu_id = :menu_id,
			code = :code,
			name = :name,
			description = :description,
			updated_at = :updated_at,
			updated_by = :updated_by
		WHERE id = :id`

	deleteButtonSQL = `
		DELETE FROM sys_button 
		WHERE id = $1`

	getButtonByIDSQL = `
		SELECT id, menu_id, code, name, description,
			created_at,
			COALESCE(created_by, 0) AS created_by,
			updated_at,
			COALESCE(updated_by, 0) AS updated_by
		FROM sys_button
		WHERE id = $1`

	countButtonSQL = `
		SELECT COUNT(*) 
		FROM sys_button`
	
	selectButtonsPageSQL = `
		SELECT b.id, b.menu_id, b.code, b.name, b.description,
			b.created_at,
			COALESCE(creator.username, 'Unknown') AS creator,
			b.updated_at,
			COALESCE(updater.username, 'Unknown') AS updater
		FROM sys_button b
		LEFT JOIN sys_user creator ON b.created_by = creator.id
		LEFT JOIN sys_user updater ON b.updated_by = updater.id`

	selectButtonByRolesIDSQL = `
		SELECT sb.id, sb.code, sb.name,
			COALESCE(sb.description, '') as description,
			CASE WHEN srbp.role_id IS NOT NULL THEN true ELSE false END AS checked
		FROM sys_button sb
		LEFT JOIN sys_role_button_permission srbp 
			ON srbp.button_id = sb.id 
			AND srbp.role_id = $1`

	selectButtonsByRoleIDsSQL = `
		SELECT sb.code
		FROM sys_button sb
		JOIN sys_role_button_permission srbp ON srbp.button_id = sb.id
		WHERE srbp.role_id in (?)`

)

// InsertButton inserts a new button into the database.
func (br *buttonRepository) InsertButton(ctx context.Context, button *model.Button) (sql.Result, error) {
	if button == nil {
		return nil, errors.New("button cannot be nil")
	}

	return br.generic.Insert(ctx, insertButtonSQL, button)
}

// UpdateButton updates an existing button in the database.
func (br *buttonRepository) UpdateButton(ctx context.Context, button *model.Button) (sql.Result, error) {
	if button == nil {
		return nil, errors.New("button cannot be nil")
	}

	return br.generic.Update(ctx, updateButtonSQL, button)
}

// GetButtonByID retrieves a button from the database by ID.
func (br *buttonRepository) GetButtonByID(ctx context.Context, id int64) (*model.Button, error) {
	if id <= 0 {
		return nil, errors.New("invalid button ID")
	}

	return br.generic.Get(ctx, getButtonByIDSQL, id)
}

// ListButtonsByPage retrieves a paginated list of buttons from the database.
func (br *buttonRepository) SelectButtonPage(ctx context.Context, pageInfo *v1.ButtonPageInfo) ([]model.Button, int64, error) {
	var (
		total   int64
		err     error
		query   strings.Builder
		params  = make(map[string]interface{})
		order   = "DESC"
		buttons []model.Button
	)

	if pageInfo.Asc {
		order = "ASC"
	}

	// Build query
	query.WriteString(selectButtonsPageSQL)

	if pageInfo.MenuID > 0 {
		query.WriteString(" WHERE b.menu_id = :menu_id")
		params["menu_id"] = pageInfo.MenuID
		total, err = br.generic.Count(ctx, countButtonSQL+" WHERE menu_id = $1", pageInfo.MenuID)
	} else {
		total, err = br.generic.Count(ctx, countButtonSQL)
	}

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

	query.WriteString(fmt.Sprintf(" ORDER BY b.%s %s LIMIT :size OFFSET :offset", pageInfo.Sort, order))
	params["size"] = pageInfo.Size
	params["offset"] = pageInfo.Offset

	buttons, err = br.generic.SelectNamed(ctx, query.String(), params)
	if err != nil {
		return nil, 0, err
	}
	return buttons, total, nil
}

// ListButtonsByRoleIDs implements ButtonRepository.
func (br *buttonRepository) SelectAssignedButtonsByRoleIDs(ctx context.Context, roleIDs []int64) ([]model.Button, error) {
	if len(roleIDs) == 0 {
		return nil, errors.New("role_ids cannot be empty")
	}

	return br.generic.SelectIn(ctx, selectButtonsByRoleIDsSQL, roleIDs)
}

// ListButtonsByRoleID implements ButtonRepository.
func (br *buttonRepository) SelectButtonsByRoleID(ctx context.Context, menuID, roleID int64) ([]model.Button, error) {
	if roleID < 0 {
		return nil, errors.New("invalid role ID")
	}

	if menuID > 0 {
		return br.generic.Select(ctx, selectButtonByRolesIDSQL+" where sb.menu_id = $2", roleID, menuID)
	} else {
		return br.generic.Select(ctx, selectButtonByRolesIDSQL, roleID)
	}
}

// DeleteButton deletes a button from the database by ID.
func (br *buttonRepository) DeleteButton(ctx context.Context, id int64) (sql.Result, error) {
	if id <= 0 {
		return nil, errors.New("invalid button ID")
	}

	return br.generic.Delete(ctx, deleteButtonSQL, id)
}
please optimize the code