package persistence

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

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

// --- Sharing Rule Queries ---

// GetSharingRules queries sharing rules for an object
func (r *MetadataRepository) GetSharingRules(ctx context.Context, objectAPIName string) ([]*models.SharingRule, error) {
	return r.querySharingRules(ctx, &objectAPIName)
}

// GetAllSharingRules queries the complete effective sharing-rule set in one
// snapshot read. Catalog publication uses this to avoid one remote query per
// object while preserving the same row authority and scan semantics as
// GetSharingRules.
func (r *MetadataRepository) GetAllSharingRules(ctx context.Context) ([]*models.SharingRule, error) {
	return r.querySharingRules(ctx, nil)
}

func (r *MetadataRepository) querySharingRules(ctx context.Context, objectAPIName *string) ([]*models.SharingRule, error) {
	executor := r.GetExecutor(ctx)
	sharingCols := []string{
		constants.FieldID,
		constants.FieldSharingRule_ObjectAPIName,
		constants.FieldSharingRule_Name,
		constants.FieldSharingRule_Criteria,
		constants.FieldSharingRule_AccessLevel,
		constants.FieldSharingRule_SharedFromRoleID,
		constants.FieldSharingRule_SharedFromGroupID,
		constants.FieldSharingRule_ShareWithRoleID,
		constants.FieldSharingRule_ShareWithGroupID,
	}

	qr := query.From(constants.TableSharingRule).
		Select(sharingCols).
		Where(constants.FieldIsDeleted, false)
	if objectAPIName != nil {
		qr = qr.WhereRaw(fmt.Sprintf("LOWER(`%s`) = LOWER(?)", constants.FieldSharingRule_ObjectAPIName), []interface{}{*objectAPIName})
	}
	built := qr.Build()

	rows, err := executor.QueryContext(ctx, built.SQL, built.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	rules := make([]*models.SharingRule, 0)
	for rows.Next() {
		var rule models.SharingRule
		var criteria sql.NullString
		var sharedFromRoleID, sharedFromGroupID, shareWithRoleID, shareWithGroupID sql.NullString
		if err := rows.Scan(
			&rule.ID,
			&rule.ObjectAPIName,
			&rule.Name,
			&criteria,
			&rule.AccessLevel,
			&sharedFromRoleID,
			&sharedFromGroupID,
			&shareWithRoleID,
			&shareWithGroupID,
		); err != nil {
			return nil, apperrors.NewInternalError("error scanning sharing rule", err)
		}
		if criteria.Valid {
			rule.Criteria = criteria.String
		}
		if sharedFromRoleID.Valid {
			rule.SharedFromRoleID = &sharedFromRoleID.String
		}
		if sharedFromGroupID.Valid {
			rule.SharedFromGroupID = &sharedFromGroupID.String
		}
		if shareWithRoleID.Valid {
			rule.ShareWithRoleID = &shareWithRoleID.String
		}
		if shareWithGroupID.Valid {
			rule.ShareWithGroupID = &shareWithGroupID.String
		}
		rules = append(rules, &rule)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating sharing rules", err)
	}
	return rules, nil
}