package persistence

import (
	"context"
	"fmt"

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

// FindAllRecycleBinItems retrieves all recycle bin items (Admin only typically)
func (r *RecordRepository) FindAllRecycleBinItems(ctx context.Context) ([]models.SObject, error) {
	q := query.From(constants.TableRecycleBin).
		Select([]string{"*"}).
		OrderBy(constants.FieldSysRecycleBin_DeletedDate, constants.SortDESC).
		Build()

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

	return query.ScanRowsToSObjects(rows)
}

// FindRecycleBinItemsByUserPage retrieves one keyset page of recycle bin items
// deleted by a specific user, matched on the stable DeletedById. There is no
// display-name fallback: keying on the mutable DeletedBy was the collision/
// rename bug this fixes (pingcap-inc/NexusCRM#1750). Legacy tombstones written
// before DeletedById existed (DeletedById IS NULL) carry only a mutable
// display-name snapshot and so have no authoritative delete-time identity; they
// are intentionally excluded from scope=mine (visible via scope=all) rather than
// attributed by resolving that name to a current user, which would misattribute
// them whenever the name was since renamed or reused.
func (r *RecordRepository) FindRecycleBinItemsByUserPage(ctx context.Context, deleterID string, afterDeletedDate string, afterID string, limit int) ([]models.SObject, error) {
	if limit <= 0 {
		return nil, nil
	}
	builder := query.From(constants.TableRecycleBin).
		Select([]string{"*"}).
		Where(constants.FieldSysRecycleBin_DeletedByID, deleterID)
	addRecycleBinKeysetCursor(builder, afterDeletedDate, afterID)
	q := builder.
		OrderBy(constants.FieldSysRecycleBin_DeletedDate, constants.SortDESC).
		AddOrderBy(constants.FieldSysRecycleBin_ID, constants.SortDESC).
		Limit(limit).
		Build()

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

	return query.ScanRowsToSObjects(rows)
}

// FindAllRecycleBinItemsPage retrieves one keyset page of all recycle bin items.
func (r *RecordRepository) FindAllRecycleBinItemsPage(ctx context.Context, afterDeletedDate string, afterID string, limit int) ([]models.SObject, error) {
	if limit <= 0 {
		return nil, nil
	}
	builder := query.From(constants.TableRecycleBin).
		Select([]string{"*"})
	addRecycleBinKeysetCursor(builder, afterDeletedDate, afterID)
	q := builder.
		OrderBy(constants.FieldSysRecycleBin_DeletedDate, constants.SortDESC).
		AddOrderBy(constants.FieldSysRecycleBin_ID, constants.SortDESC).
		Limit(limit).
		Build()

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

	return query.ScanRowsToSObjects(rows)
}

func addRecycleBinKeysetCursor(builder *query.Builder, afterDeletedDate string, afterID string) {
	if afterDeletedDate == "" || afterID == "" {
		return
	}
	// Column names are generated constants; values remain parameterized.
	builder.WhereRaw(
		fmt.Sprintf("(`%s` < ? OR (`%s` = ? AND `%s` < ?))",
			constants.FieldSysRecycleBin_DeletedDate,
			constants.FieldSysRecycleBin_DeletedDate,
			constants.FieldSysRecycleBin_ID,
		),
		[]interface{}{afterDeletedDate, afterDeletedDate, afterID},
	)
}

// FindRecycleBinEntry finds a recycle bin entry by original Record ID
func (r *RecordRepository) FindRecycleBinEntry(ctx context.Context, recordID string) (models.SObject, error) {
	q := query.From(constants.TableRecycleBin).
		Select([]string{"*"}).
		Where(constants.FieldRecordID, recordID).
		OrderBy(constants.FieldSysRecycleBin_DeletedDate, constants.SortDESC).
		AddOrderBy(constants.FieldSysRecycleBin_ID, constants.SortDESC).
		Limit(1).
		Build()

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

	results, err := query.ScanRowsToSObjects(rows)
	if err != nil {
		return nil, err
	}
	if len(results) == 0 {
		return nil, nil
	}
	return results[0], nil
}

// FindRecycleBinEntriesByDeleteOperation returns all tombstones written by one
// logical delete operation. The records layer uses this lineage authority for
// cascade restore rather than rediscovering an active/deleted child graph.
func (r *RecordRepository) FindRecycleBinEntriesByDeleteOperation(ctx context.Context, operationID string) ([]models.SObject, error) {
	if operationID == "" {
		return nil, nil
	}
	q := query.From(constants.TableRecycleBin).
		Select([]string{"*"}).
		Where(constants.FieldSysRecycleBin_DeleteOperationID, operationID).
		OrderBy(constants.FieldSysRecycleBin_CascadeDepth, constants.SortASC).
		AddOrderBy(constants.FieldSysRecycleBin_DeletedDate, constants.SortASC).
		AddOrderBy(constants.FieldSysRecycleBin_ID, constants.SortASC).
		Build()

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

	return query.ScanRowsToSObjects(rows)
}