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"
	"github.com/rs/zerolog/log"
)

// GetListViews queries list views for an object
func (r *MetadataRepository) GetListViews(ctx context.Context, objectAPIName string) ([]*models.ListView, error) {
	qr := query.From(constants.TableListView).
		Select(constants.ColsSystemListView).
		Where(constants.FieldObjectAPIName, objectAPIName). // Builder handles case-insensitivity if needed, but standard Where is exact match.
		// Previous raw SQL used LOWER(). If we need case-insensitive, we might need WhereRaw or ensure input is normalized.
		// Standardizing on exact match for now unless strictly required.
		Build()

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

	views := make([]*models.ListView, 0)
	for rows.Next() {
		view, err := r.scanListView(rows)
		if err != nil {
			log.Warn().Err(err).Msg("Failed to scan list view")
			continue
		}
		views = append(views, view)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating list views", err)
	}
	return views, nil
}

// CreateListView creates a new list view
func (r *MetadataRepository) CreateListView(ctx context.Context, view *models.ListView) error {
	fieldsJSON, err := r.marshalJSON(view.Fields)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal fields", err)
	}

	qr := query.Insert(constants.TableListView, map[string]interface{}{
		constants.FieldID:                     view.ID,
		constants.FieldObjectAPIName:          view.ObjectAPIName,
		constants.FieldSysListView_Label:      view.Label,
		constants.FieldSysListView_FilterExpr: view.FilterExpr,
		constants.FieldSysListView_Fields:     fieldsJSON,
	}).Build()

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

// UpdateListView updates a list view
func (r *MetadataRepository) UpdateListView(ctx context.Context, id string, updates *models.ListView) error {
	fieldsJSON, err := r.marshalJSON(updates.Fields)
	if err != nil {
		return apperrors.NewInternalError("failed to marshal fields", err)
	}

	qr := query.Update(constants.TableListView).
		Set(map[string]interface{}{
			constants.FieldSysListView_Label:      updates.Label,
			constants.FieldSysListView_FilterExpr: updates.FilterExpr,
			constants.FieldSysListView_Fields:     fieldsJSON,
		}).
		Where(constants.FieldID, id).
		Build()

	result, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return err
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return sql.ErrNoRows
	}
	return nil
}

// DeleteListView deletes a list view
func (r *MetadataRepository) DeleteListView(ctx context.Context, id string) error {
	qr := query.Delete(constants.TableListView).
		Where(constants.FieldID, id).
		Build()
	result, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return err
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return sql.ErrNoRows
	}
	return nil
}

// CountListViews counts list views for an object
func (r *MetadataRepository) CountListViews(ctx context.Context, objectAPIName string) (int, error) {
	qr := query.From(constants.TableListView).
		AddSelectRaw("COUNT(*)").
		SkipID().
		Where(constants.FieldObjectAPIName, objectAPIName).
		Build()

	var count int
	err := r.db.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&count)
	return count, err
}