package persistence

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

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

const (
	listViewRepoVisibilityPrivate           = "Private"
	listViewDeveloperNameUniqueIndexName    = "uniq_listview_object_developer_name"
	listViewDeveloperNameGeneratedIndexName = "idx__System_ListView_ObjectApiName_DeveloperName"
)

// GetPrivateListViews is the parameterized caller-owned state read used by the
// private Runtime capability. It does not include legacy/global/shared rows.
func (r *MetadataRepository) GetPrivateListViews(ctx context.Context, objectAPIName string, ownerID string) ([]*models.ListView, error) {
	qr := query.From(constants.TableListView).
		Select(constants.ColsSystemListView).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldObjectAPIName), []interface{}{objectAPIName}).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldSysListView_OwnerID), []interface{}{ownerID}).
		WhereRaw(fmt.Sprintf("LOWER(`%s`.`%s`) = ?", constants.TableListView, constants.FieldSysListView_Visibility), []interface{}{strings.ToLower(listViewRepoVisibilityPrivate)}).
		Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	views := make([]*models.ListView, 0)
	for rows.Next() {
		view, scanErr := r.scanListView(rows)
		if scanErr != nil {
			return nil, scanErr
		}
		views = append(views, view)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating private list views", err)
	}
	return views, nil
}

// GetPrivateListView applies object, owner, and visibility in the SQL predicate
// so a caller cannot distinguish a foreign row from an absent row.
func (r *MetadataRepository) GetPrivateListView(ctx context.Context, objectAPIName string, id string, ownerID string) (*models.ListView, error) {
	qr := query.From(constants.TableListView).
		Select(constants.ColsSystemListView).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldID), []interface{}{id}).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldObjectAPIName), []interface{}{objectAPIName}).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldSysListView_OwnerID), []interface{}{ownerID}).
		WhereRaw(fmt.Sprintf("LOWER(`%s`.`%s`) = ?", constants.TableListView, constants.FieldSysListView_Visibility), []interface{}{strings.ToLower(listViewRepoVisibilityPrivate)}).
		Build()
	return r.scanListView(r.GetExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...))
}

// 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.
		WhereRaw(listViewLegacyGlobalPredicate(), nil).
		// 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()

	exec := r.GetExecutor(ctx)
	rows, err := exec.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
}

// GetListViewsWithReadFilter queries list views for an object using a generic
// record-read filter produced by the permission/RLS layer. An empty filter
// means the caller has an unrestricted bypass for _System_ListView.
func (r *MetadataRepository) GetListViewsWithReadFilter(ctx context.Context, objectAPIName string, readFilterSQL string) ([]*models.ListView, error) {
	builder := query.From(constants.TableListView).
		Select(constants.ColsSystemListView).
		Where(constants.FieldObjectAPIName, objectAPIName)
	if readFilterSQL = strings.TrimSpace(readFilterSQL); readFilterSQL != "" {
		builder = builder.WhereRaw(listViewLegacyOrReadFilterPredicate(readFilterSQL), nil)
	}
	qr := builder.Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.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 RLS-filtered list view")
			continue
		}
		views = append(views, view)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating RLS-filtered list views", err)
	}
	return views, nil
}

// GetListView reads a single list view row by ID without applying visibility
// predicates. Callers are responsible for authorization.
func (r *MetadataRepository) GetListView(ctx context.Context, id string) (*models.ListView, error) {
	exec := r.GetExecutor(ctx)
	return r.getListView(ctx, exec, id, false)
}

// GetListViewForUpdate reads and locks a single list view row by ID for callers
// that are about to capture old state and write in the same transaction.
func (r *MetadataRepository) GetListViewForUpdate(ctx context.Context, id string) (*models.ListView, error) {
	exec := r.GetExecutor(ctx)
	return r.getListView(ctx, exec, id, true)
}

func (r *MetadataRepository) getListView(ctx context.Context, exec Executor, id string, forUpdate bool) (*models.ListView, error) {
	qr := query.From(constants.TableListView).
		Select(constants.ColsSystemListView).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldID), []interface{}{id}).
		Build()
	sqlText := qr.SQL
	if forUpdate {
		sqlText += " FOR UPDATE"
	}

	view, err := r.scanListView(exec.QueryRowContext(ctx, sqlText, qr.Params...))
	if err != nil {
		return nil, err
	}
	return view, nil
}

// GetListViewByDeveloperName reads one list view by object-scoped,
// case-insensitive DeveloperName. Callers are responsible for authorization and
// non-leaking conflict behavior.
func (r *MetadataRepository) GetListViewByDeveloperName(ctx context.Context, objectAPIName, developerName string) (*models.ListView, error) {
	exec := r.GetExecutor(ctx)
	return r.getListViewByDeveloperName(ctx, exec, objectAPIName, developerName, false)
}

// GetListViewByDeveloperNameForUpdate reads and locks the object-scoped
// DeveloperName match for callers implementing update/upsert authoring.
func (r *MetadataRepository) GetListViewByDeveloperNameForUpdate(ctx context.Context, objectAPIName, developerName string) (*models.ListView, error) {
	exec := r.GetExecutor(ctx)
	return r.getListViewByDeveloperName(ctx, exec, objectAPIName, developerName, true)
}

func (r *MetadataRepository) getListViewByDeveloperName(ctx context.Context, exec Executor, objectAPIName, developerName string, forUpdate bool) (*models.ListView, error) {
	qr := query.From(constants.TableListView).
		Select(constants.ColsSystemListView).
		WhereRaw(fmt.Sprintf("`%s`.`%s` = ?", constants.TableListView, constants.FieldObjectAPIName), []interface{}{objectAPIName}).
		WhereRaw(fmt.Sprintf("LOWER(`%s`.`%s`) = ?", constants.TableListView, constants.FieldSysListView_DeveloperName), []interface{}{strings.ToLower(strings.TrimSpace(developerName))}).
		Build()
	sqlText := qr.SQL
	if forUpdate {
		sqlText += " FOR UPDATE"
	}

	view, err := r.scanListView(exec.QueryRowContext(ctx, sqlText, qr.Params...))
	if err != nil {
		return nil, err
	}
	return view, 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_FilterLogic:   view.FilterLogic,
		constants.FieldSysListView_SortField:     view.SortField,
		constants.FieldSysListView_SortDirection: view.SortDirection,
		constants.FieldSysListView_Fields:        fieldsJSON,
		constants.FieldSysListView_OwnerID:       view.OwnerID,
		constants.FieldSysListView_Visibility:    view.Visibility,
		constants.FieldSysListView_DeveloperName: view.DeveloperName,
	}).Build()

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

// UpdateListViewPatch updates only the fields present in patch, guarded by the
// caller-owned private row predicate provided by ListViewService.
func (r *MetadataRepository) UpdateListViewPatch(ctx context.Context, id string, ownerID string, patch models.ListViewPatch) error {
	updates := make(map[string]interface{})
	if patch.Label != nil {
		updates[constants.FieldSysListView_Label] = *patch.Label
	}
	if patch.FilterExpr != nil {
		updates[constants.FieldSysListView_FilterExpr] = *patch.FilterExpr
	}
	if patch.FilterLogic != nil {
		updates[constants.FieldSysListView_FilterLogic] = *patch.FilterLogic
	}
	if patch.SortField != nil {
		updates[constants.FieldSysListView_SortField] = *patch.SortField
	}
	if patch.SortDirection != nil {
		updates[constants.FieldSysListView_SortDirection] = *patch.SortDirection
	}
	if patch.Fields != nil {
		fieldsJSON, err := r.marshalJSON(*patch.Fields)
		if err != nil {
			return apperrors.NewInternalError("failed to marshal fields", err)
		}
		updates[constants.FieldSysListView_Fields] = fieldsJSON
	}
	if patch.DeveloperName != nil {
		updates[constants.FieldSysListView_DeveloperName] = *patch.DeveloperName
	}
	if len(updates) == 0 {
		return nil
	}

	qr := query.Update(constants.TableListView).
		Set(updates).
		Where(constants.FieldID, id).
		Where(constants.FieldSysListView_OwnerID, ownerID).
		Where(constants.FieldSysListView_Visibility, listViewRepoVisibilityPrivate).
		Build()

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

// DeletePrivateListView deletes only the caller-owned private row selected by
// ListViewService. The owner/private predicate closes the read-check/write race
// if visibility or ownership changes between authorization and delete.
func (r *MetadataRepository) DeletePrivateListView(ctx context.Context, id string, ownerID string) error {
	qr := query.Delete(constants.TableListView).
		Where(constants.FieldID, id).
		Where(constants.FieldSysListView_OwnerID, ownerID).
		Where(constants.FieldSysListView_Visibility, listViewRepoVisibilityPrivate).
		Build()
	exec := r.GetExecutor(ctx)
	result, err := exec.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return err
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return sql.ErrNoRows
	}
	return nil
}

func listViewLegacyGlobalPredicate() string {
	return fmt.Sprintf(
		"`%s`.`%s` IS NULL AND `%s`.`%s` IS NULL",
		constants.TableListView,
		constants.FieldSysListView_OwnerID,
		constants.TableListView,
		constants.FieldSysListView_Visibility,
	)
}

func listViewLegacyOrReadFilterPredicate(readFilterSQL string) string {
	return fmt.Sprintf(
		"((%s) OR (%s))",
		listViewLegacyGlobalPredicate(),
		readFilterSQL,
	)
}

func mapListViewWriteError(err error) error {
	if err == nil || !IsDuplicateKeyError(err) {
		return err
	}
	keyName := ExtractDuplicateKeyName(err.Error())
	if isListViewDeveloperNameUniqueIndex(keyName) {
		return apperrors.NewConflictError("list view", constants.FieldSysListView_DeveloperName, "")
	}
	return err
}

func isListViewDeveloperNameUniqueIndex(keyName string) bool {
	keyName = strings.TrimSpace(keyName)
	return strings.EqualFold(keyName, listViewDeveloperNameUniqueIndexName) ||
		strings.EqualFold(keyName, listViewDeveloperNameGeneratedIndexName)
}