package repository

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

	"github.com/spf13/viper"

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

// MenuRepository interface defines menu-specific database operations.
type MenuRepository interface {
	InsertMenu(ctx context.Context, menu *model.Menu) (sql.Result, error)
	UpdateMenu(ctx context.Context, menu *model.Menu) (sql.Result, error)
	DeleteMenu(ctx context.Context, id int64) (sql.Result, error)
	GetMenuByID(ctx context.Context, id int64) (*model.Menu, error)
	GetChildrenMenuNumber(ctx context.Context, id int64) (int64, error)
	ListMenus(ctx context.Context) ([]model.Menu, error)
	SelectAssignedMenusByRoleIDs(ctx context.Context, roleIDs []int64) ([]model.Menu, error)
	SelectMenusByRoleID(ctx context.Context, roleIDs int64) ([]model.Menu, error)
}

// menuRepository implements MenuRepository.
type menuRepository struct {
	generic GenericRepository[model.Menu]
}

// NewMenuRepository creates a new MenuRepository instance.
func NewMenuRepository(r *Repository, v *viper.Viper, s *sid.Sid) MenuRepository {
	return &menuRepository{generic: NewGenericRepository[model.Menu](r, v, s)}
}

const (
	insertMenuSQL = `INSERT INTO sys_menu (id, parent_id, name, url, icon, sort_order, created_at, created_by, open_style, updated_at, updated_by) VALUES (:id, :parent_id, :name, :url, :icon, :sort_order, :created_at, :created_by, :open_style, :updated_at, :updated_by)`

	updateMenuSQL = `UPDATE sys_menu SET parent_id = :parent_id, name = :name, url = :url, icon = :icon, sort_order = :sort_order, updated_at = :updated_at, updated_by = :updated_by, open_style = :open_style WHERE id = :id`

	deleteMenuSQL = `DELETE FROM sys_menu WHERE id = $1`

	countChildren = `SELECT COUNT(*) FROM sys_menu WHERE parent_id = $1`

	getMenuByIDSQL = `SELECT id, parent_id, name, url, icon, sort_order, open_style FROM sys_menu WHERE id = $1`

	selectMenuSQL = `SELECT m.id, COALESCE(m.parent_id, 0) as parent_id, m.name, m.url, m.icon, m.sort_order, m.created_at, COALESCE(creator.username, 'Unknown') AS creator, m.updated_at, m.open_style, COALESCE(updater.username, 'Unknown') AS updater FROM sys_menu m LEFT JOIN sys_user creator ON m.created_by = creator.id LEFT JOIN sys_user updater ON m.updated_by = updater.id ORDER BY m.sort_order`

	getMenuSQLByRoleIDSQL = `SELECT 
            sm.id,
            COALESCE(sm.parent_id, 0) as parent_id,
            sm.name,
            sm.url,
            COALESCE(sm.icon, '') as icon,
            sm.sort_order,
            sm.open_style,
            CASE 
                WHEN srm.role_id IS NOT NULL THEN true 
                ELSE false 
            END AS checked
        FROM sys_menu sm
        LEFT JOIN sys_role_menu srm 
            ON srm.menu_id = sm.id 
            AND srm.role_id = $1
        ORDER BY sm.sort_order`

	getMenuSQLByRoleIDsSQL = `SELECT 
            sm.id,
            COALESCE(sm.parent_id, 0) as parent_id,
            sm.name,
            sm.url,
            COALESCE(sm.icon, '') as icon,
            sm.sort_order,
            sm.open_style
        FROM sys_menu sm
        JOIN sys_role_menu srm ON srm.menu_id = sm.id WHERE srm.role_id IN (?) ORDER BY sm.sort_order`

)

func (mr *menuRepository) InsertMenu(ctx context.Context, menu *model.Menu) (sql.Result, error) {
	if menu == nil {
		return nil, errors.New("menu cannot be nil")
	}
	return mr.generic.Insert(ctx, insertMenuSQL, menu)
}

func (mr *menuRepository) UpdateMenu(ctx context.Context, menu *model.Menu) (sql.Result, error) {
	if menu == nil {
		return nil, errors.New("menu cannot be nil")
	}
	return mr.generic.Update(ctx, updateMenuSQL, menu)
}

func (mr *menuRepository) DeleteMenu(ctx context.Context, id int64) (sql.Result, error) {
	if id <= 0 {
		return nil, errors.New("invalid menu id")
	}
	return mr.generic.Delete(ctx, deleteMenuSQL, id)
}

func (mr *menuRepository) GetMenuByID(ctx context.Context, id int64) (*model.Menu, error) {
	if id <= 0 {
		return nil, errors.New("invalid menu id")
	}

	return mr.generic.Get(ctx, getMenuByIDSQL, id)
}

func (mr *menuRepository) GetChildrenMenuNumber(ctx context.Context, id int64) (int64, error) {
	if id <= 0 {
		return 0, errors.New("invalid menu id")
	}

	var count int64
	err := mr.generic.GetDB(ctx).GetContext(ctx, &count, countChildren, id)
	return count, err
}

func (mr *menuRepository) ListMenus(ctx context.Context) ([]model.Menu, error) {
	return mr.generic.Select(ctx, selectMenuSQL)
}

func (mr *menuRepository) SelectAssignedMenusByRoleIDs(ctx context.Context, roleIDs []int64) ([]model.Menu, error) {
	if len(roleIDs) == 0 {
		return nil, errors.New("role_ids cannot be empty")
	}

	return mr.generic.SelectIn(ctx, getMenuSQLByRoleIDsSQL, roleIDs)
}

func (mr *menuRepository) SelectMenusByRoleID(ctx context.Context, roleID int64) ([]model.Menu, error) {
	if roleID < 0 {
		return nil, errors.New("invalid role ID")
	}

	return mr.generic.Select(ctx, getMenuSQLByRoleIDSQL, roleID)
}
format the code and add comments