package controlplane

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"
)

// orphanStandardActionSatelliteQuery flags satellite rows that have outlived
// their _System_StandardAction parent. It is the read-side dual of the
// reconcile-side cascade in
// bootstrap.cascadeDeactivateOrphanedStandardActionSatellites: both treat a
// satellite whose StandardActionId no longer resolves to a PRESENT
// (non-deleted) parent as an orphan to be removed. Liveness keys on existence
// (IsDeleted), NOT visibility: _System_StandardAction.IsActive is an admin
// disable-without-delete toggle (preserved across pack updates), so an
// IsActive=0 parent still owns its satellites. Reconcile-dropped parents set
// IsActive=0 AND IsDeleted=1 together, so IsDeleted alone still catches them.
//
// The three customizable satellites are scoped to IsCustomized=0 because the
// standard-action reconcile contract (see InitializeStandardActionsForOwner)
// intentionally PRESERVES admin-customized (IsCustomized=1) rows even when
// their parent is dropped. Flagging those would fail-fail cmd/setup verify on a
// supported customization state with no apply-side repair path.
// _System_StandardActionInternalAccessScope is L1-only (no admin author), so
// every live row must track a live parent regardless of IsCustomized.
const orphanStandardActionSatelliteQuery = `
SELECT Surface, Sample
FROM (
  SELECT '_System_ActionSchema' AS Surface,
         CONCAT(COALESCE(s.StandardActionId, ''), '/', COALESCE(s.Direction, ''), '/', COALESCE(s.APIName, '')) AS Sample
  FROM _System_ActionSchema s
  LEFT JOIN _System_StandardAction a ON a.Id = s.StandardActionId
  WHERE COALESCE(s.IsDeleted, 0) = 0
    AND COALESCE(s.IsActive, 1) = 1
    AND COALESCE(s.IsCustomized, 0) = 0
    AND (a.Id IS NULL OR COALESCE(a.IsDeleted, 0) <> 0)

  UNION ALL

  SELECT '_System_ActionFieldDefault' AS Surface,
         CONCAT(COALESCE(s.StandardActionId, ''), '/', COALESCE(s.TargetObject, ''), '/', COALESCE(s.FieldAPIName, ''), '/', COALESCE(s.Priority, 0)) AS Sample
  FROM _System_ActionFieldDefault s
  LEFT JOIN _System_StandardAction a ON a.Id = s.StandardActionId
  WHERE COALESCE(s.IsDeleted, 0) = 0
    AND COALESCE(s.IsActive, 1) = 1
    AND COALESCE(s.IsCustomized, 0) = 0
    AND (a.Id IS NULL OR COALESCE(a.IsDeleted, 0) <> 0)

  UNION ALL

  SELECT '_System_ActionInputGuard' AS Surface,
         CONCAT(COALESCE(s.StandardActionId, ''), '/', COALESCE(s.GuardKey, '')) AS Sample
  FROM _System_ActionInputGuard s
  LEFT JOIN _System_StandardAction a ON a.Id = s.StandardActionId
  WHERE COALESCE(s.IsDeleted, 0) = 0
    AND COALESCE(s.IsActive, 1) = 1
    AND COALESCE(s.IsCustomized, 0) = 0
    AND (a.Id IS NULL OR COALESCE(a.IsDeleted, 0) <> 0)

  UNION ALL

  SELECT '_System_StandardActionInternalAccessScope' AS Surface,
         CONCAT(COALESCE(s.StandardActionId, ''), '/', COALESCE(s.ObjectApiName, ''), '/', COALESCE(s.Operation, '')) AS Sample
  FROM _System_StandardActionInternalAccessScope s
  LEFT JOIN _System_StandardAction a ON a.Id = s.StandardActionId
  WHERE COALESCE(s.IsDeleted, 0) = 0
    AND COALESCE(s.IsActive, 1) = 1
    AND (a.Id IS NULL OR COALESCE(a.IsDeleted, 0) <> 0)
) issues
ORDER BY Surface, Sample
LIMIT 11`

// VerifyActionTableConsistency is the fail-closed gate for
// docs/design/action-table-bridge.md.
//
// The current architecture intentionally has no direct row-to-row bridge from
// _System_Action to _System_StandardAction. _System_Action is object-scoped
// quick/custom/automation action metadata keyed by (ObjectApiName, Name).
// _System_StandardAction is the global invocable-action registry keyed by Name.
// The two surfaces are disjoint namespaces; a shared Name across them is NOT a
// conflict, so this gate does not infer a bridge from name equality.
//
// The one fact this gate enforces is the supported bridge: every standard-
// action satellite row (_System_ActionSchema, _System_ActionFieldDefault,
// _System_ActionInputGuard, _System_StandardActionInternalAccessScope) joins to
// its _System_StandardAction parent by StandardActionId and must not outlive a
// present (non-deleted) parent — admin-disabled (IsActive=0) parents still
// count as present. The reconcile-side cascade keeps this invariant true; this
// gate fails closed if it is ever violated.
func VerifyActionTableConsistency(ctx context.Context, rawDB *sql.DB) error {
	if rawDB == nil {
		return fmt.Errorf("action table consistency verify requires a database handle")
	}

	if orphans, err := orphanStandardActionSatellites(ctx, rawDB); err != nil {
		return fmt.Errorf("action table consistency: %w", err)
	} else if len(orphans) > 0 {
		return fmt.Errorf("action table consistency failed: active standard-action satellite rows without a present (non-deleted) _System_StandardAction parent: [%s]; run cmd/setup apply or repair the rows before cmd/setup verify passes",
			strings.Join(formatActionTableSamples(orphans), "; "))
	}

	log.Println("action table bridge consistency verified")
	return nil
}

func orphanStandardActionSatellites(ctx context.Context, rawDB *sql.DB) ([]string, error) {
	rows, err := rawDB.QueryContext(ctx, orphanStandardActionSatelliteQuery)
	if err != nil {
		return nil, fmt.Errorf("query orphan standard-action satellite rows: %w", err)
	}
	defer rows.Close()

	var out []string
	for rows.Next() {
		var surface, sample string
		if err := rows.Scan(&surface, &sample); err != nil {
			return nil, fmt.Errorf("scan orphan standard-action satellite row: %w", err)
		}
		out = append(out, strings.TrimSpace(surface)+" "+strings.TrimSpace(sample))
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate orphan standard-action satellite rows: %w", err)
	}
	return out, nil
}

func formatActionTableSamples(samples []string) []string {
	if len(samples) <= 10 {
		return samples
	}
	out := make([]string, 0, 11)
	out = append(out, samples[:10]...)
	out = append(out, "truncated")
	return out
}