// Package replay — DB read side.
//
// reader.go provides the two SELECTs the replay tool needs:
//   - LoadCaptures: pull a time-windowed slice of _System_RequestCapture.
//   - LoadAuditRowsForRequest: pull the _System_AuditLog rows that were
//     written under the same RequestId (§14.9 one-to-many logical join).
//
// Both functions take a *sql.DB (or anything implementing Queryer) so tests
// can substitute sqlmock without pulling the real persistence layer.
package replay

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

// Queryer is the narrow read surface reader.go needs. *sql.DB satisfies it.
type Queryer interface {
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

// Capture is the subset of _System_RequestCapture columns the replay loop
// operates on. Body and Headers are already scrubbed (§14.3) at write time;
// they are NOT un-scrubbed here (red-line #6 — no PII re-hydration).
type Capture struct {
	RequestID      string
	ReceivedAt     time.Time
	Method         string
	Path           string
	Query          string
	Headers        string // JSON as stored
	Body           string // already scrubbed
	ResponseStatus int
	UserID         string
	Entrypoint     string
}

// CaptureFilter narrows LoadCaptures via optional predicates. Zero value
// = fully unfiltered (only the time window applies).
type CaptureFilter struct {
	Since     time.Time // required (zero → no lower bound; caller should set)
	Until     time.Time // required (zero → no upper bound)
	RequestID string    // optional: exact match
	UserID    string    // optional: exact match
	Limit     int       // required; <=0 → default 100
}

const defaultLimit = 100

// LoadCaptures returns captures in ReceivedAt-ascending order so the replay
// tool can re-issue them in their original sequence.
func LoadCaptures(ctx context.Context, q Queryer, f CaptureFilter) ([]Capture, error) {
	limit := f.Limit
	if limit <= 0 {
		limit = defaultLimit
	}
	query := `SELECT RequestId, ReceivedAt, Method, Path, COALESCE(Query, ''),
		        COALESCE(Headers, ''), COALESCE(Body, ''), ResponseStatus,
		        COALESCE(UserId, ''), Entrypoint
		 FROM _System_RequestCapture
		 WHERE 1=1`
	args := []interface{}{}
	if !f.Since.IsZero() {
		query += " AND ReceivedAt >= ?"
		args = append(args, f.Since)
	}
	if !f.Until.IsZero() {
		query += " AND ReceivedAt < ?"
		args = append(args, f.Until)
	}
	if f.RequestID != "" {
		query += " AND RequestId = ?"
		args = append(args, f.RequestID)
	}
	if f.UserID != "" {
		query += " AND UserId = ?"
		args = append(args, f.UserID)
	}
	query += " ORDER BY ReceivedAt ASC LIMIT ?"
	args = append(args, limit)

	rows, err := q.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("replay: query captures: %w", err)
	}
	defer rows.Close()

	var out []Capture
	for rows.Next() {
		var c Capture
		if err := rows.Scan(&c.RequestID, &c.ReceivedAt, &c.Method, &c.Path,
			&c.Query, &c.Headers, &c.Body, &c.ResponseStatus, &c.UserID,
			&c.Entrypoint); err != nil {
			return nil, fmt.Errorf("replay: scan capture: %w", err)
		}
		out = append(out, c)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("replay: captures rows.Err: %w", err)
	}
	return out, nil
}

// LoadAuditRowsForRequest returns all _System_AuditLog rows whose RequestId
// equals reqID. Ordered by ChangedAt ascending + FieldName for deterministic
// diff output.
func LoadAuditRowsForRequest(ctx context.Context, q Queryer, reqID string) ([]AuditRow, error) {
	if reqID == "" {
		return nil, fmt.Errorf("replay: empty RequestId")
	}
	rows, err := q.QueryContext(ctx,
		`SELECT ObjectApiName, RecordId, FieldName, COALESCE(Action, ''),
		        COALESCE(OldValue, ''), COALESCE(NewValue, ''),
		        COALESCE(Entrypoint, '')
		 FROM _System_AuditLog
		 WHERE RequestId = ?
		 ORDER BY ChangedAt ASC, FieldName ASC`, reqID)
	if err != nil {
		return nil, fmt.Errorf("replay: query auditlog: %w", err)
	}
	defer rows.Close()

	var out []AuditRow
	for rows.Next() {
		var r AuditRow
		if err := rows.Scan(&r.ObjectApiName, &r.RecordId, &r.FieldName,
			&r.Action, &r.OldValue, &r.NewValue, &r.Entrypoint); err != nil {
			return nil, fmt.Errorf("replay: scan auditrow: %w", err)
		}
		out = append(out, r)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("replay: auditlog rows.Err: %w", err)
	}
	return out, nil
}