package persist

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

// timeLayout is the fixed-width timestamp layout used for every stored
// created_at/updated_at value. Unlike time.RFC3339Nano it never drops trailing
// fractional zeros, so every value is exactly storedTimeLen bytes wide. SQLite
// compares these TEXT columns as plain strings, and equal width is what makes
// that comparison agree with chronological order. UTC values render the zone as
// a single "Z".
const timeLayout = "2006-01-02T15:04:05.000000000Z07:00"

// storedTimeLen is the byte length of a UTC value formatted with timeLayout:
// "2006-01-02T15:04:05.000000000Z" — the Z07:00 zone collapses to "Z" in UTC.
const storedTimeLen = 30

// checkpointsTable names the table whose timestamps are normalized when the
// Store opens. It must match the DDL in persist.go.
const checkpointsTable = "checkpoints"

// formatTime encodes t in UTC with the fixed-width timeLayout. Always writing
// UTC keeps ordering and round-trips stable across processes, and the fixed
// width is what lets ORDER BY updated_at order these TEXT columns by time.
func formatTime(t time.Time) string {
	return t.UTC().Format(timeLayout)
}

// parseTime decodes a stored timestamp. time.RFC3339Nano accepts both the
// fixed-width values this package writes and the legacy variable-width ones, so
// the same parser serves reads and the open-time normalization.
func parseTime(s string) (time.Time, error) {
	return time.Parse(time.RFC3339Nano, s)
}

// normalizeTimestamps rewrites every checkpoints timestamp that is not
// storedTimeLen bytes wide into the fixed-width timeLayout. Rows written before
// the layout existed carry variable-width RFC3339Nano text whose string order is
// not its time order; this brings them in line so LoadLatest picks the truly
// latest checkpoint.
//
// It is idempotent: only rows whose column length differs from storedTimeLen are
// selected, so running it again on a normalized table is a no-op. A row whose
// value does not parse is left exactly as it is — one bad legacy value must not
// keep the Store from opening.
func normalizeTimestamps(ctx context.Context, db *sql.DB) error {
	if err := normalizeTimeColumn(ctx, db, "created_at"); err != nil {
		return err
	}
	return normalizeTimeColumn(ctx, db, "updated_at")
}

// normalizeTimeColumn rewrites every checkpoints.<col> value that is not
// storedTimeLen bytes wide. col is a compile-time constant, never user input, so
// splicing it into the statement is safe; the length comparison and every value
// written are parameterized. All rewrites are collected before any UPDATE runs,
// so no write happens while the SELECT cursor is still open.
func normalizeTimeColumn(ctx context.Context, db *sql.DB, col string) error {
	fixes, err := collectLegacyTimestamps(ctx, db, col)
	if err != nil {
		return err
	}
	for _, f := range fixes {
		if _, err := db.ExecContext(ctx,
			"UPDATE "+checkpointsTable+" SET "+f.col+" = ? WHERE id = ?", f.value, f.id); err != nil {
			return fmt.Errorf("persist: normalize %s.%s: %w", checkpointsTable, f.col, err)
		}
	}
	return nil
}

// timeFix is one pending timestamp rewrite: which column, which row (by primary
// key id), and the fixed-width value to write.
type timeFix struct {
	col   string
	id    string
	value string
}

// collectLegacyTimestamps reads the rows of checkpoints.col whose value is not
// storedTimeLen bytes wide and returns the parseable ones as pending rewrites.
// Unparseable values are skipped, so normalization can never fail a row: the only
// errors are genuine read failures.
func collectLegacyTimestamps(ctx context.Context, db *sql.DB, col string) ([]timeFix, error) {
	rows, err := db.QueryContext(ctx,
		"SELECT id, "+col+" FROM "+checkpointsTable+" WHERE length("+col+") != ?", storedTimeLen)
	if err != nil {
		return nil, fmt.Errorf("persist: query legacy %s.%s: %w", checkpointsTable, col, err)
	}
	defer func() { _ = rows.Close() }()

	fixes := make([]timeFix, 0)
	for rows.Next() {
		var id, raw string
		if err := rows.Scan(&id, &raw); err != nil {
			return nil, fmt.Errorf("persist: scan legacy %s.%s: %w", checkpointsTable, col, err)
		}
		fixed, ok := reformatTime(raw)
		if !ok {
			continue
		}
		fixes = append(fixes, timeFix{col: col, id: id, value: fixed})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("persist: iterate legacy %s.%s: %w", checkpointsTable, col, err)
	}
	return fixes, nil
}

// reformatTime re-encodes raw with timeLayout. The bool is false when raw is
// already storedTimeLen wide or when it does not parse, in which case the caller
// leaves the stored value untouched.
func reformatTime(raw string) (string, bool) {
	if len(raw) == storedTimeLen {
		return "", false
	}
	t, err := parseTime(raw)
	if err != nil || t.IsZero() {
		return "", false
	}
	return formatTime(t), true
}