package persistence

import (
	"context"
	"database/sql"

	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// ListCustomPermissions returns all custom permissions
func (r *PermissionRepository) ListCustomPermissions(ctx context.Context) ([]models.SystemCustomPermission, error) {
	qr := query.From(constants.TableCustomPermission).
		Select([]string{
			constants.FieldID,
			constants.FieldSysCustomPermission_APIName,
			constants.FieldSysCustomPermission_Label,
			constants.FieldSysCustomPermission_Description,
			constants.FieldSysCustomPermission_IsActive,
			constants.FieldCreatedDate,
		}).
		Build()

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

	var perms []models.SystemCustomPermission
	for rows.Next() {
		var p models.SystemCustomPermission
		if err := rows.Scan(&p.ID, &p.APIName, &p.Label, &p.Description, &p.IsActive, &p.CreatedDate); err != nil {
			return nil, apperrors.NewInternalError("failed to scan custom permission", err)
		}
		perms = append(perms, p)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating custom permissions", err)
	}

	return perms, nil
}

// GetCustomPermission returns a single custom permission by ID
func (r *PermissionRepository) GetCustomPermission(ctx context.Context, id string) (*models.SystemCustomPermission, error) {
	qr := query.From(constants.TableCustomPermission).
		Select([]string{
			constants.FieldID,
			constants.FieldSysCustomPermission_APIName,
			constants.FieldSysCustomPermission_Label,
			constants.FieldSysCustomPermission_Description,
			constants.FieldSysCustomPermission_IsActive,
			constants.FieldCreatedDate,
		}).
		Where(constants.FieldID, id).
		Build()

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

	return &p, nil
}

// CreateCustomPermission creates a new custom permission
func (r *PermissionRepository) CreateCustomPermission(ctx context.Context, perm *models.SystemCustomPermission) error {
	qr := query.Insert(constants.TableCustomPermission, map[string]interface{}{
		constants.FieldID:                              perm.ID,
		constants.FieldSysCustomPermission_APIName:     perm.APIName,
		constants.FieldSysCustomPermission_Label:       perm.Label,
		constants.FieldSysCustomPermission_Description: perm.Description,
		constants.FieldSysCustomPermission_IsActive:    perm.IsActive,
		constants.FieldCreatedDate:                     query.Raw("NOW()"),
		constants.FieldLastModifiedDate:                query.Raw("NOW()"),
	}).Build()

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

	return nil
}

// UpdateCustomPermission updates an existing custom permission
func (r *PermissionRepository) UpdateCustomPermission(ctx context.Context, perm *models.SystemCustomPermission) error {
	qr := query.Update(constants.TableCustomPermission).
		Set(map[string]interface{}{
			constants.FieldSysCustomPermission_APIName:     perm.APIName,
			constants.FieldSysCustomPermission_Label:       perm.Label,
			constants.FieldSysCustomPermission_Description: perm.Description,
			constants.FieldSysCustomPermission_IsActive:    perm.IsActive,
			constants.FieldLastModifiedDate:                query.Raw("NOW()"),
		}).
		Where(constants.FieldID, perm.ID).
		Build()

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

	return nil
}

// DeleteCustomPermission deletes a custom permission and its references
func (r *PermissionRepository) DeleteCustomPermission(ctx context.Context, id string) error {
	delProfileQr := query.Delete(constants.TableProfileCustomPermission).
		Where(constants.FieldProfileCustomPermission_CustomPermissionID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, delProfileQr.SQL, delProfileQr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete profile custom permission refs", err)
	}

	delPermSetQr := query.Delete(constants.TablePermissionSetCustomPermission).
		Where(constants.FieldPermissionSetCustomPermission_CustomPermissionID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, delPermSetQr.SQL, delPermSetQr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete permset custom permission refs", err)
	}

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

	return nil
}