package store

import (
	"database/sql"
	"encoding/json"
	"time"
)

// PBS DR tier (SLICE 1): the HOST-scoped one-time PBS token secret — the host/agent twin of the
// customer-scoped one_time_secrets pair (SaveOneTimeSecret/ConsumeOneTimeSecret). The hub stores
// the tenantsync-returned token secret here; the agent consumes it EXACTLY ONCE with its per-host
// key. Transient custody: never logged, never in desired-state or any served config.

// SaveHostPBSSecret stores (last-write-wins) the one-time PBS token secret for a host, resetting
// the consumed flag (a re-issue supersedes any prior unconsumed value). Never logged.
//
// It returns the host's new secret GENERATION — a monotonic counter advanced by exactly this
// mint. The caller stamps it into the pbs_dr descriptor, which is what makes a re-key visible to
// the agent: without it the descriptor is byte-identical across a re-issue (only the side-table
// secret rotates), the converged agent short-circuits on its content hash, and the fresh secret is
// never consumed — the R-39 failure. See the column's note in store.go.
//
// The UPSERT and the read are one statement (RETURNING), so two concurrent mints cannot both
// report the same generation.
func (s *Store) SaveHostPBSSecret(hostID, value string) (int64, error) {
	var gen int64
	err := s.db.QueryRow(`
		INSERT INTO host_pbs_secrets (host_id, value, created_at, consumed_at, generation)
		VALUES (?, ?, datetime('now'), NULL, 1)
		ON CONFLICT(host_id) DO UPDATE SET
			value       = excluded.value,
			created_at  = datetime('now'),
			consumed_at = NULL,
			generation  = host_pbs_secrets.generation + 1
		RETURNING generation`,
		hostID, value).Scan(&gen)
	return gen, err
}

// HostPBSSecretGeneration returns the host's current secret generation (0 = no secret ever stored).
// Read-only; used when refreshing a descriptor without minting.
func (s *Store) HostPBSSecretGeneration(hostID string) (int64, error) {
	var gen int64
	err := s.db.QueryRow(`SELECT generation FROM host_pbs_secrets WHERE host_id = ?`, hostID).Scan(&gen)
	if err == sql.ErrNoRows {
		return 0, nil
	}
	return gen, err
}

// ConsumeHostPBSSecret returns the host's one-time PBS token secret and marks it consumed in the
// SAME transaction (single use). A second call — or a call when none is stored — returns
// ("", sql.ErrNoRows). The value is never logged.
func (s *Store) ConsumeHostPBSSecret(hostID string) (string, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return "", err
	}
	defer tx.Rollback()
	var value string
	err = tx.QueryRow(`SELECT value FROM host_pbs_secrets WHERE host_id = ? AND consumed_at IS NULL`, hostID).Scan(&value)
	if err != nil {
		return "", err // sql.ErrNoRows when absent OR already consumed
	}
	if _, err := tx.Exec(`UPDATE host_pbs_secrets SET consumed_at = datetime('now') WHERE host_id = ?`, hostID); err != nil {
		return "", err
	}
	if err := tx.Commit(); err != nil {
		return "", err
	}
	return value, nil
}

// RestageHostPBSSecret re-arms an ALREADY-STORED one-time PBS secret for re-consumption by clearing
// its consumed flag — WITHOUT changing the secret value, WITHOUT inserting a row, and WITHOUT any
// endpoint/token interaction. This is the PBS-DR self-heal primitive
// (SPIKE-pbsdr-selfheal-2026-07-15, e8f8c44): a box that lost its converged marker (re-install /
// snapshot rollback) but whose ep0 token is still valid re-consumes the SAME secret and converges in
// one agent tick (spike SQ-2b′, zero ep0 churn). Returns restaged=true when a row existed (its
// consumed flag is now cleared, so ConsumeHostPBSSecret will serve it once); restaged=false when NO
// secret is stored for the host — the caller must escalate to Re-issue (a fresh mint). The value is
// never read or logged here, and NO host generation is bumped (a re-stage changes no descriptor
// content — a bump would trigger an agent desired-state refetch loop).
func (s *Store) RestageHostPBSSecret(hostID string) (bool, error) {
	res, err := s.db.Exec(`UPDATE host_pbs_secrets SET consumed_at = NULL WHERE host_id = ?`, hostID)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return false, err
	}
	return n > 0, nil
}

// PBSDRHealRow is the per-host self-heal decision input (SPIKE-pbsdr-selfheal-2026-07-15): the host's
// descriptor enable/provision state (parsed from desired_json) joined to the agent's latest reported
// pbs_dr.state (from the newest host_report). Mirrors GetHostOOBStates' latest-report-per-host shape.
type PBSDRHealRow struct {
	HostID                string
	CustomerID            string
	DescriptorEnabled     bool   // desired_json pbs_dr.enabled — the DR-ON reality
	DescriptorProvisioned bool   // desired_json pbs_dr.namespace != "" (was provisioned, not a bare enable)
	ReportedState         string // latest report pbs_dr.state ("" = no report / no stanza)
	ReportID              int64  // id of that latest host_report (0 = none); the debounce distinctness key

	// R-39 consumed_at honesty (Scenario F). SecretUnconsumedFor is how long an UNCONSUMED secret has
	// been staged for this host (0 when none is staged, or when it has already been consumed).
	//
	// A staged-but-unconsumed secret sitting under a box that reports `applied` is the exact
	// fingerprint of the 2026-07-18 N100 failure: the hub minted, the agent short-circuited, and both
	// tiers reported success while the box served a revoked credential. Briefly unconsumed is NORMAL
	// (a fresh mint, or a deliberate re-stage, is consumed on the agent's next tick) — only a
	// SUSTAINED one is a lie, which is why the caller applies a grace window rather than alarming on
	// presence alone.
	SecretUnconsumedFor time.Duration
}

// PBSDRHealStates returns one row per host: its descriptor enable/provision flags + the agent's
// latest reported pbs_dr.state + that report's id. Malformed JSON degrades to zero values (never an
// error) — the reconciler only acts on positively-parsed stuck states. One query for the whole fleet
// (the reconciler filters to enabled+provisioned hosts).
func (s *Store) PBSDRHealStates() ([]PBSDRHealRow, error) {
	rows, err := s.db.Query(`
		SELECT h.host_id, h.customer_id, h.desired_json, latest.mx, hr.report_json,
		       ps.created_at, ps.consumed_at
		FROM hosts h
		LEFT JOIN (SELECT host_id, MAX(id) AS mx FROM host_reports GROUP BY host_id) latest
		       ON latest.host_id = h.host_id
		LEFT JOIN host_reports hr ON hr.id = latest.mx
		LEFT JOIN host_pbs_secrets ps ON ps.host_id = h.host_id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []PBSDRHealRow
	for rows.Next() {
		var hostID, customerID, desiredJSON string
		var reportID sql.NullInt64
		var reportJSON, secretCreatedAt, secretConsumedAt sql.NullString
		if err := rows.Scan(&hostID, &customerID, &desiredJSON, &reportID, &reportJSON,
			&secretCreatedAt, &secretConsumedAt); err != nil {
			return nil, err
		}
		r := PBSDRHealRow{HostID: hostID, CustomerID: customerID}
		var dd struct {
			PBSDR *struct {
				Enabled   bool   `json:"enabled"`
				Namespace string `json:"namespace"`
			} `json:"pbs_dr"`
		}
		if json.Unmarshal([]byte(desiredJSON), &dd) == nil && dd.PBSDR != nil {
			r.DescriptorEnabled = dd.PBSDR.Enabled
			r.DescriptorProvisioned = dd.PBSDR.Namespace != ""
		}
		if reportID.Valid {
			r.ReportID = reportID.Int64
		}
		if reportJSON.Valid {
			var rr struct {
				PBSDR *struct {
					State string `json:"state"`
				} `json:"pbs_dr"`
			}
			if json.Unmarshal([]byte(reportJSON.String), &rr) == nil && rr.PBSDR != nil {
				r.ReportedState = rr.PBSDR.State
			}
		}
		// Unconsumed-secret age: only when a secret is staged AND still unconsumed.
		if secretCreatedAt.Valid && !secretConsumedAt.Valid {
			// SQLite datetime('now') is UTC and format-varied — parseSQLiteTime is the house parser
			// (it returns the zero time on an unparseable value, which we treat as "unknown", never
			// as "infinitely stale").
			if created := parseSQLiteTime(secretCreatedAt.String); !created.IsZero() {
				if age := time.Since(created); age > 0 {
					r.SecretUnconsumedFor = age
				}
			}
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// SetHostPBSSecretCreatedAtForTest back-dates a staged secret's created_at. TEST-ONLY seam: the
// grace-window behaviour of the consumed_at honesty gauge (R-39 Scenario F) is otherwise only
// observable by sleeping for 15 minutes. It touches nothing else — not the value, not consumed_at,
// not the generation.
func (s *Store) SetHostPBSSecretCreatedAtForTest(hostID, sqliteTime string) error {
	_, err := s.db.Exec(`UPDATE host_pbs_secrets SET created_at = ? WHERE host_id = ?`, sqliteTime, hostID)
	return err
}
