package persistence

import (
	"context"
	"database/sql"

	"github.com/nexuscrm/backend/internal/domain/ports"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/backend/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// ListPermissionSetGroups returns all permission set groups
func (r *PermissionRepository) ListPermissionSetGroups(ctx context.Context) ([]models.PermissionSetGroup, error) {
	qr := query.From(constants.TablePermissionSetGroup).
		Select([]string{
			constants.FieldID,
			constants.FieldPermissionSetGroup_APIName,
			constants.FieldPermissionSetGroup_Label,
			constants.FieldPermissionSetGroup_Description,
			constants.FieldCreatedDate,
		}).
		Build()

	rows, err := r.getExecutor(ctx).QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query permission set groups", err)
	}
	defer rows.Close()

	var groups []models.PermissionSetGroup
	for rows.Next() {
		var g models.PermissionSetGroup
		if err := rows.Scan(&g.ID, &g.APIName, &g.Label, &g.Description, &g.CreatedDate); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission set group", err)
		}
		groups = append(groups, g)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating permission set groups", err)
	}

	return groups, nil
}

// GetPermissionSetGroupByID returns a single permission set group by ID
func (r *PermissionRepository) GetPermissionSetGroupByID(ctx context.Context, id string) (*models.PermissionSetGroup, error) {
	qr := query.From(constants.TablePermissionSetGroup).
		Select([]string{
			constants.FieldID,
			constants.FieldPermissionSetGroup_APIName,
			constants.FieldPermissionSetGroup_Label,
			constants.FieldPermissionSetGroup_Description,
			constants.FieldCreatedDate,
		}).
		Where(constants.FieldID, id).
		Build()

	var g models.PermissionSetGroup
	err := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&g.ID, &g.APIName, &g.Label, &g.Description, &g.CreatedDate,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query permission set group", err)
	}

	return &g, nil
}

// GetPermissionSetGroupComponents returns the component permission sets for a group
func (r *PermissionRepository) GetPermissionSetGroupComponents(ctx context.Context, groupID string) ([]ports.PermissionSetRef, error) {
	compSQL := `SELECT ps.Id, ps.Name, ps.Label FROM ` + constants.TablePermissionSetGroupComponent + ` gc ` +
		`JOIN ` + constants.TablePermissionSet + ` ps ON ps.Id = gc.` + constants.FieldPermissionSetGroupComponent_PermissionSetID + ` ` +
		`WHERE gc.` + constants.FieldPermissionSetGroupComponent_GroupID + ` = ?`

	rows, err := r.getExecutor(ctx).QueryContext(ctx, compSQL, groupID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query group components", err)
	}
	defer rows.Close()

	var refs []ports.PermissionSetRef
	for rows.Next() {
		var ref ports.PermissionSetRef
		if err := rows.Scan(&ref.ID, &ref.APIName, &ref.Label); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission set reference", err)
		}
		refs = append(refs, ref)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating group components", err)
	}

	return refs, nil
}

// CreatePermissionSetGroup creates a new permission set group with its components
func (r *PermissionRepository) CreatePermissionSetGroup(ctx context.Context, group *models.PermissionSetGroup, permSetIDs []string) error {
	qr := query.Insert(constants.TablePermissionSetGroup, map[string]interface{}{
		constants.FieldID:                             group.ID,
		constants.FieldPermissionSetGroup_APIName:     group.APIName,
		constants.FieldPermissionSetGroup_Label:       group.Label,
		constants.FieldPermissionSetGroup_Description: group.Description,
		constants.FieldCreatedDate:                    query.Raw("NOW()"),
		constants.FieldLastModifiedDate:               query.Raw("NOW()"),
	}).Build()

	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to create permission set group", err)
	}

	for _, psID := range permSetIDs {
		compQr := query.Insert(constants.TablePermissionSetGroupComponent, map[string]interface{}{
			constants.FieldID: utils.GenerateID(),
			constants.FieldPermissionSetGroupComponent_GroupID:         group.ID,
			constants.FieldPermissionSetGroupComponent_PermissionSetID: psID,
			constants.FieldCreatedDate:                                 query.Raw("NOW()"),
			constants.FieldLastModifiedDate:                            query.Raw("NOW()"),
		}).Build()

		if _, err := r.getExecutor(ctx).ExecContext(ctx, compQr.SQL, compQr.Params...); err != nil {
			return apperrors.NewInternalError("failed to add component to group", err)
		}
	}

	return nil
}

// UpdatePermissionSetGroup updates an existing group and replaces its components
func (r *PermissionRepository) UpdatePermissionSetGroup(ctx context.Context, group *models.PermissionSetGroup, permSetIDs []string) error {
	qr := query.Update(constants.TablePermissionSetGroup).
		Set(map[string]interface{}{
			constants.FieldPermissionSetGroup_APIName:     group.APIName,
			constants.FieldPermissionSetGroup_Label:       group.Label,
			constants.FieldPermissionSetGroup_Description: group.Description,
			constants.FieldLastModifiedDate:               query.Raw("NOW()"),
		}).
		Where(constants.FieldID, group.ID).
		Build()

	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to update permission set group", err)
	}

	delQr := query.Delete(constants.TablePermissionSetGroupComponent).
		Where(constants.FieldPermissionSetGroupComponent_GroupID, group.ID).
		Build()

	if _, err := r.getExecutor(ctx).ExecContext(ctx, delQr.SQL, delQr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete group components", err)
	}

	for _, psID := range permSetIDs {
		compQr := query.Insert(constants.TablePermissionSetGroupComponent, map[string]interface{}{
			constants.FieldID: utils.GenerateID(),
			constants.FieldPermissionSetGroupComponent_GroupID:         group.ID,
			constants.FieldPermissionSetGroupComponent_PermissionSetID: psID,
			constants.FieldCreatedDate:                                 query.Raw("NOW()"),
			constants.FieldLastModifiedDate:                            query.Raw("NOW()"),
		}).Build()

		if _, err := r.getExecutor(ctx).ExecContext(ctx, compQr.SQL, compQr.Params...); err != nil {
			return apperrors.NewInternalError("failed to add component to group", err)
		}
	}

	return nil
}

// DeletePermissionSetGroup deletes a group, its assignments, and its components
func (r *PermissionRepository) DeletePermissionSetGroup(ctx context.Context, id string) error {
	delAssignQr := query.Delete(constants.TablePermissionSetGroupAssignment).
		Where(constants.FieldPermissionSetGroupAssignment_GroupID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, delAssignQr.SQL, delAssignQr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete group assignments", err)
	}

	delCompQr := query.Delete(constants.TablePermissionSetGroupComponent).
		Where(constants.FieldPermissionSetGroupComponent_GroupID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, delCompQr.SQL, delCompQr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete group components", err)
	}

	delQr := query.Delete(constants.TablePermissionSetGroup).
		Where(constants.FieldID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, delQr.SQL, delQr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete permission set group", err)
	}

	return nil
}