package persistence

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

	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"
)

// --- Validation Rule CRUD ---

// CreateValidationRule creates a new validation rule
func (r *MetadataRepository) CreateValidationRule(ctx context.Context, rule *models.ValidationRule) error {
	var description sql.NullString
	if rule.Description != nil {
		description = sql.NullString{String: *rule.Description, Valid: true}
	}
	var errorDisplayField sql.NullString
	if rule.ErrorDisplayField != nil {
		errorDisplayField = sql.NullString{String: *rule.ErrorDisplayField, Valid: true}
	}

	values := map[string]interface{}{
		constants.FieldID: rule.ID,
		constants.FieldSysValidationRule_ObjectAPIName:     rule.ObjectAPIName,
		constants.FieldSysValidationRule_Name:              rule.Name,
		constants.FieldSysValidationRule_Description:       description,
		constants.FieldSysValidationRule_Active:            rule.Active,
		constants.FieldSysValidationRule_Condition:         rule.Condition,
		constants.FieldSysValidationRule_ErrorMessage:      rule.ErrorMessage,
		constants.FieldSysValidationRule_ErrorDisplayField: errorDisplayField,
		constants.FieldSysValidationRule_IsCustomized:      rule.IsCustomized,
	}

	qr := query.Insert(constants.TableValidationRule, values).Build()
	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// UpdateValidationRule updates a validation rule
func (r *MetadataRepository) UpdateValidationRule(ctx context.Context, id string, updates *models.ValidationRule) error {
	var description sql.NullString
	if updates.Description != nil {
		description = sql.NullString{String: *updates.Description, Valid: true}
	}
	var errorDisplayField sql.NullString
	if updates.ErrorDisplayField != nil {
		errorDisplayField = sql.NullString{String: *updates.ErrorDisplayField, Valid: true}
	}

	values := map[string]interface{}{
		constants.FieldSysValidationRule_Name:              updates.Name,
		constants.FieldSysValidationRule_Description:       description,
		constants.FieldSysValidationRule_Active:            updates.Active,
		constants.FieldSysValidationRule_Condition:         updates.Condition,
		constants.FieldSysValidationRule_ErrorMessage:      updates.ErrorMessage,
		constants.FieldSysValidationRule_ErrorDisplayField: errorDisplayField,
		constants.FieldSysValidationRule_IsCustomized:      updates.IsCustomized,
	}

	qr := query.Update(constants.TableValidationRule).
		Set(values).
		Where(constants.FieldID, id).
		Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetValidationRule returns a single validation rule by ID
func (r *MetadataRepository) GetValidationRule(ctx context.Context, id string) (*models.ValidationRule, error) {
	qr := query.From(constants.TableValidationRule).
		Select(constants.ColsSystemValidationRule).
		Where(constants.FieldID, id).
		Build()

	row := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...)
	rule, err := r.scanValidationRule(row)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return rule, nil
}

// DeleteValidationRule deletes a validation rule
func (r *MetadataRepository) DeleteValidationRule(ctx context.Context, id string) error {
	qr := query.Delete(constants.TableValidationRule).
		Where(constants.FieldID, id).
		Build()
	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetAllValidationRules loads all validation rules in a single query (for cache warming)
func (r *MetadataRepository) GetAllValidationRules(ctx context.Context) ([]*models.ValidationRule, error) {
	qr := query.From(constants.TableValidationRule).
		Select(constants.ColsSystemValidationRule).
		Build()

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

	rules := make([]*models.ValidationRule, 0)
	for rows.Next() {
		rule, err := r.scanValidationRule(rows)
		if err != nil {
			continue
		}
		rules = append(rules, rule)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating all validation rules: %w", err)
	}
	return rules, nil
}

// GetValidationRules queries validation rules for an object
func (r *MetadataRepository) GetValidationRules(ctx context.Context, objectAPIName string) ([]*models.ValidationRule, error) {
	qr := query.From(constants.TableValidationRule).
		Select(constants.ColsSystemValidationRule).
		WhereRaw(fmt.Sprintf("LOWER(`%s`) = LOWER(?)", constants.FieldSysValidationRule_ObjectAPIName), []interface{}{objectAPIName}).
		Build()

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

	rules := make([]*models.ValidationRule, 0)
	for rows.Next() {
		rule, err := r.scanValidationRule(rows)
		if err != nil {
			continue
		}
		rules = append(rules, rule)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating validation rules", err)
	}
	return rules, nil
}

// CheckValidationRuleExists checks for duplicate (object_api_name, name)
func (r *MetadataRepository) CheckValidationRuleExists(ctx context.Context, objectAPIName, name string) (bool, error) {
	qr := query.From(constants.TableValidationRule).
		SkipID().
		AddSelectRaw("COUNT(*)").
		Where(constants.FieldSysValidationRule_ObjectAPIName, objectAPIName).
		Where(constants.FieldSysValidationRule_Name, name).
		Build()

	var count int
	if err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&count); err != nil {
		return false, err
	}
	return count > 0, nil
}