// Package calendar owns the cross-aggregate read model used by the
// Workflow calendar. It performs no lifecycle mutations.
package calendar

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

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

type Store struct{ db coredb.DB }

const scheduleSourcePageSize = 256

func New(db coredb.DB) *Store { return &Store{db: db} }

func (store *Store) VisitSnapshot(ctx context.Context, params ListSnapshotParams, visitor SnapshotVisitor) error {
	if store == nil || store.db == nil {
		return fmt.Errorf("Workflow calendar database is not configured")
	}
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.From = params.From.UTC().Truncate(time.Microsecond)
	params.To = params.To.UTC().Truncate(time.Microsecond)
	if params.WorkspaceID == "" || params.From.IsZero() || params.To.IsZero() || !params.From.Before(params.To) {
		return storeerr.BadRequest("workspaceId and a valid calendar window are required")
	}
	if params.RunLimit < 1 {
		return storeerr.BadRequest("calendar snapshot run limit must be positive")
	}
	if visitor.Runs == nil || visitor.Source == nil {
		return fmt.Errorf("Workflow calendar snapshot visitor is not configured")
	}

	return coredb.WithTxOptions(ctx, store.db, coredb.TxOptions{
		IsoLevel: coredb.RepeatableRead,
	}, func(tx coredb.Tx) error {
		runs, err := listRuns(ctx, tx, params)
		if err != nil {
			return err
		}
		if err := visitor.Runs(runs); err != nil {
			return err
		}
		return visitScheduleSources(ctx, tx, params, visitor.Source)
	})
}

func listRuns(ctx context.Context, queryer coredb.Queryer, params ListSnapshotParams) ([]Run, error) {
	rows, err := queryer.Query(ctx, `
SELECT i.id, r.id, i.plan_id, i.plan_version_id, i.workflow_name_snapshot,
       COALESCE(p.plan_key, ''), COALESCE(p.display_name, ''),
       COALESCE(i.binding_id, ''), COALESCE(b.binding_key, ''), COALESCE(b.display_name, ''),
       COALESCE(i.binding_revision_id, ''), COALESCE(i.binding_source_id, ''),
       i.source_kind, i.source_occurrence_ref, i.source_occurred_at,
       r.status, r.queued_at, r.started_at, r.completed_at, r.canceled_at
FROM workflow_invocations i
JOIN workflow_run_instances r
  ON r.workspace_id = i.workspace_id AND r.invocation_id = i.id
LEFT JOIN workflow_plans p
  ON p.workspace_id = i.workspace_id AND p.id = i.plan_id
LEFT JOIN workflow_bindings b
  ON b.workspace_id = i.workspace_id AND b.id = i.binding_id
WHERE i.workspace_id = $1
  AND i.source_occurred_at >= $2
  AND i.source_occurred_at < $3
ORDER BY i.source_occurred_at ASC, i.id ASC
LIMIT $4
`, params.WorkspaceID, params.From, params.To, params.RunLimit)
	if err != nil {
		return nil, fmt.Errorf("list Workflow calendar Runs: %w", err)
	}
	defer rows.Close()

	values := make([]Run, 0)
	for rows.Next() {
		var value Run
		var startedAt, completedAt, canceledAt sql.NullTime
		if err := rows.Scan(
			&value.InvocationID, &value.RunID, &value.PlanID, &value.PlanVersionID, &value.WorkflowName,
			&value.PlanKey, &value.PlanDisplayName,
			&value.BindingID, &value.BindingKey, &value.BindingDisplayName,
			&value.BindingRevisionID, &value.BindingSourceID,
			&value.SourceKind, &value.OccurrenceKey, &value.OccursAt,
			&value.Status, &value.QueuedAt, &startedAt, &completedAt, &canceledAt,
		); err != nil {
			return nil, fmt.Errorf("scan Workflow calendar Run: %w", err)
		}
		value.OccursAt = calendarTime(value.OccursAt)
		value.QueuedAt = calendarTime(value.QueuedAt)
		value.StartedAt = calendarTimePointer(startedAt)
		value.CompletedAt = calendarTimePointer(completedAt)
		value.CanceledAt = calendarTimePointer(canceledAt)
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate Workflow calendar Runs: %w", err)
	}
	return values, nil
}

func visitScheduleSources(
	ctx context.Context,
	queryer coredb.Queryer,
	params ListSnapshotParams,
	visit func(ScheduleSource) error,
) error {
	// Raw source count cannot be capped by the item limit: overdue one-shots
	// and long-period recurring sources may project no items into this window.
	// Keyset pages bound each query and the retained row set while the outer
	// repeatable-read transaction keeps every page in the same snapshot.
	cursorAt := time.Date(1000, 1, 1, 0, 0, 0, 0, time.UTC)
	cursorID := ""
	for {
		count, nextAt, nextID, err := visitScheduleSourcePage(
			ctx, queryer, params, cursorAt, cursorID, visit,
		)
		if err != nil {
			return err
		}
		if count < scheduleSourcePageSize {
			return nil
		}
		cursorAt, cursorID = nextAt, nextID
	}
}

func visitScheduleSourcePage(
	ctx context.Context,
	queryer coredb.Queryer,
	params ListSnapshotParams,
	cursorAt time.Time,
	cursorID string,
	visit func(ScheduleSource) error,
) (int, time.Time, string, error) {
	rows, err := queryer.Query(ctx, `
SELECT source.id, source.binding_id, source.binding_revision_id, source.activation_number,
       source.next_occurrence_at, revision.plan_id, revision.plan_version_id,
       JSON_REMOVE(JSON_EXTRACT(revision.config_json, '$.source'), '$.schedule.inputs'),
       binding.binding_key, binding.display_name,
       COALESCE(plan.plan_key, ''), COALESCE(plan.display_name, '')
FROM workflow_binding_sources source
JOIN workflow_bindings binding
  ON binding.workspace_id = source.workspace_id
 AND binding.id = source.binding_id
 AND binding.state = 'enabled'
 AND binding.active_source_id = source.id
 AND binding.active_revision_id = source.binding_revision_id
JOIN workflow_binding_revisions revision
  ON revision.workspace_id = source.workspace_id
 AND revision.binding_id = source.binding_id
 AND revision.id = source.binding_revision_id
LEFT JOIN workflow_plans plan
  ON plan.workspace_id = revision.workspace_id AND plan.id = revision.plan_id
WHERE source.workspace_id = $1
  AND source.source_kind = 'schedule'
  AND source.status = 'active'
  AND source.next_occurrence_at IS NOT NULL
  AND source.next_occurrence_at < $2
  AND (
    source.next_occurrence_at > $3
    OR (source.next_occurrence_at = $3 AND source.id > $4)
  )
ORDER BY source.next_occurrence_at ASC, source.id ASC
LIMIT $5
`, params.WorkspaceID, params.To, cursorAt, cursorID, scheduleSourcePageSize)
	if err != nil {
		return 0, cursorAt, cursorID, fmt.Errorf("list Workflow calendar Schedule sources: %w", err)
	}
	defer rows.Close()

	count := 0
	for rows.Next() {
		var value ScheduleSource
		if err := rows.Scan(
			&value.ID, &value.BindingID, &value.BindingRevisionID, &value.Activation,
			&value.NextOccurrenceAt, &value.PlanID, &value.PlanVersionID, &value.SourceJSON,
			&value.BindingKey, &value.BindingDisplayName, &value.PlanKey, &value.PlanDisplayName,
		); err != nil {
			return 0, cursorAt, cursorID, fmt.Errorf("scan Workflow calendar Schedule source: %w", err)
		}
		value.NextOccurrenceAt = calendarTime(value.NextOccurrenceAt)
		cursorAt = value.NextOccurrenceAt
		cursorID = value.ID
		count++
		if err := visit(value); err != nil {
			return 0, cursorAt, cursorID, err
		}
	}
	if err := rows.Err(); err != nil {
		return 0, cursorAt, cursorID, fmt.Errorf("iterate Workflow calendar Schedule sources: %w", err)
	}
	return count, cursorAt, cursorID, nil
}

func calendarTime(value time.Time) time.Time { return value.UTC().Truncate(time.Microsecond) }

func calendarTimePointer(value sql.NullTime) *time.Time {
	if !value.Valid {
		return nil
	}
	normalized := calendarTime(value.Time)
	return &normalized
}