Motor FARO Score MVP · 0 a 100 + Confidence

export function scoreStatus(score: number): ScoreStatus {
  if (score >= 90) return "excellent";
  if (score >= 80) return "good";
  if (score >= 70) return "attention";
  if (score >= 60) return "warning";
  if (score >= 40) return "critical";
  return "emergency";
}

export function clamp(value: number, min = 0, max = 100): number {
  return Math.min(max, Math.max(min, value));
}

CREATE OR REPLACE FUNCTION faro.score_status(p_score numeric)
RETURNS text AS $$
BEGIN
  IF p_score >= 90 THEN RETURN 'excellent';
  ELSIF p_score >= 80 THEN RETURN 'good';
  ELSIF p_score >= 70 THEN RETURN 'attention';
  ELSIF p_score >= 60 THEN RETURN 'warning';
  ELSIF p_score >= 40 THEN RETURN 'critical';
  ELSE RETURN 'emergency';
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

KPI Health        = 30 × weighted_average(kpi_health_ratio × kpi_weight × data_confidence)
Tension Health    = max(0, 25 − Σ tension_penalty)
Execution Health  = max(0, min(20, 20 − execution_penalty + execution_recovery))
Evidence Health   = 15 × evidence_completion_ratio × evidence_trust_ratio
Governance Health = max(0, 10 − governance_penalty)

FARO Score = clamp(KPI + Tension + Execution + Evidence + Governance, 0, 100)

CREATE TABLE IF NOT EXISTS faro.score_model_versions (
  score_model_version_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  model_code text NOT NULL,
  version integer NOT NULL,
  name text NOT NULL,
  description text NOT NULL,
  industry_code text NULL,
  business_model_code text NULL,
  is_default boolean NOT NULL DEFAULT false,
  is_active boolean NOT NULL DEFAULT true,
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(model_code, version)
);

{
  "dimensions": {
    "kpi_health":        { "max_points": 30 },
    "tension_health":    { "max_points": 25 },
    "execution_health":  { "max_points": 20 },
    "evidence_health":   { "max_points": 15 },
    "governance_health": { "max_points": 10 }
  },
  "severity_weights": { "low": 1, "medium": 2, "high": 4, "critical": 6 },
  "status_multipliers": {
    "new": 1, "assigned": 0.95, "in_analysis": 0.90,
    "in_execution": 0.75, "in_verification": 0.45,
    "blocked": 1.10, "escalated": 1.20,
    "closed": 0, "reopened": 1.30, "rejected": 0
  },
  "evidence_trust_weights": { "low": 0.40, "medium": 0.65, "high": 0.85, "critical": 1.00 }
}

export function calculateScoreConfidence(input: {
  kpiConfidence: number;
  tensionConfidence: number;
  evidenceConfidence: number;
  governanceConfidence: number;
  moduleCoverage: number;
}): number {
  // Completeness: promedio de confianzas dimensionales
  const completeness = (
    input.kpiConfidence +
    input.tensionConfidence +
    input.governanceConfidence
  ) / 3;

  // Freshness: derivado de data_quality_metrics
  const freshness = clamp(input.kpiConfidence, 0, 100);

  // Consistency: cruces canónicos válidos
  const consistency = clamp(input.governanceConfidence, 0, 100);

  const raw =
      completeness         * 0.30
    + freshness            * 0.20
    + consistency          * 0.20
    + input.evidenceConfidence * 0.15
    + input.moduleCoverage * 0.15;

  return round2(clamp(raw, 0, 100));
}

export function confidenceStatus(confidence: number): ConfidenceStatus {
  if (confidence >= 85) return "high";
  if (confidence >= 70) return "good";
  if (confidence >= 50) return "medium";
  if (confidence >= 30) return "low";
  return "critical";
}

export type KpiDirection =
  | "higher_is_better"
  | "lower_is_better"
  | "range_is_better"
  | "binary";

export function normalizeKpiHealth(params: {
  actual: number;
  target?: number | null;
  min?: number | null;
  max?: number | null;
  direction: KpiDirection;
}): number {
  const { actual, target, min, max, direction } = params;

  if (direction === "binary") {
    return actual ? 1 : 0;
  }

  if (direction === "higher_is_better") {
    if (!target || target <= 0) return 0.5;
    return clamp(actual / target, 0, 1);
  }

  if (direction === "lower_is_better") {
    if (!target || target <= 0) return 0.5;
    if (actual <= target) return 1;
    return clamp(target / actual, 0, 1);
  }

  if (direction === "range_is_better") {
    if (min == null || max == null) return 0.5;
    if (actual >= min && actual <= max) return 1;
    if (actual < min) return clamp(actual / min, 0, 1);
    return clamp(max / actual, 0, 1);
  }

  return 0.5;
}

export function calculateKpiHealthPoints(rows: KpiInputRow[]): {
  points: number;
  confidence: number;
  drivers: ScoreDriver[];
} {
  const maxPoints = 30;

  if (rows.length === 0) {
    return { points: 0, confidence: 0, drivers: [] };
  }

  let weightedSum = 0;
  let weightTotal = 0;
  let confidenceTotal = 0;
  const drivers: ScoreDriver[] = [];

  for (const row of rows) {
    const health = normalizeKpiHealth({
      actual: row.actual,
      target: row.target,
      min: row.min_value,
      max: row.max_value,
      direction: row.direction
    });

    const weight = row.kpi_weight ?? 1;
    const dataConf = clamp(row.data_confidence / 100, 0, 1);

    weightedSum += health * weight * dataConf;
    weightTotal += weight;
    confidenceTotal += row.data_confidence;

    if (health < 0.7) {
      drivers.push({
        driver_type: "kpi",
        driver_code: row.kpi_code,
        driver_title: row.kpi_name,
        impact_points: round2(-(1 - health) * weight * 3),
        impact_direction: "negative",
        explanation: `${row.kpi_code} está en ${Math.round(health * 100)}% de su salud objetivo.`
      });
    }
  }

  const ratio = weightTotal > 0 ? weightedSum / weightTotal : 0;
  const points = round2(clamp(maxPoints * ratio, 0, maxPoints));
  const confidence = round2(confidenceTotal / rows.length);

  return { points, confidence, drivers };
}

export function calculateTensionPenalty(input: {
  severity: "low" | "medium" | "high" | "critical";
  priorityScore: number;
  confidenceScore: number;
  status: string;
  recurrenceCount?: number;
  scoreImpact?: number | null;
}): number {
  const severityWeights = { low: 1, medium: 2, high: 4, critical: 6 };

  const statusMultipliers: Record<string, number> = {
    new: 1, assigned: 0.95, in_analysis: 0.9,
    in_execution: 0.75, in_verification: 0.45,
    blocked: 1.1, escalated: 1.2,
    closed: 0, reopened: 1.3, rejected: 0
  };

  const recurrence = input.recurrenceCount ?? 0;
  const recurrenceMultiplier =
    recurrence >= 2 ? 1.4 : recurrence === 1 ? 1.2 : 1;

  const impactMultiplier = input.scoreImpact
    ? clamp(Math.abs(input.scoreImpact) / 8, 0.75, 1.5)
    : 1;

  const penalty =
    severityWeights[input.severity] *
    clamp(input.priorityScore / 100, 0, 1) *
    clamp(input.confidenceScore / 100, 0, 1) *
    (statusMultipliers[input.status] ?? 1) *
    recurrenceMultiplier *
    impactMultiplier;

  return round2(penalty);
}

export function aggregateTensionHealth(rows: TensionRow[]): {
  points: number;
  penalty: number;
  drivers: ScoreDriver[];
} {
  const maxPoints = 25;
  let totalPenalty = 0;
  const drivers: ScoreDriver[] = [];

  for (const row of rows) {
    const penalty = calculateTensionPenalty({
      severity: row.severity,
      priorityScore: row.priority_score,
      confidenceScore: row.confidence_score,
      status: row.status,
      recurrenceCount: row.recurrence_count,
      scoreImpact: row.score_impact
    });
    totalPenalty += penalty;
    if (penalty > 0) drivers.push(buildTensionDriver(row, penalty));
  }

  return {
    points: round2(clamp(maxPoints - totalPenalty, 0, maxPoints)),
    penalty: round2(totalPenalty),
    drivers
  };
}

export function aggregateExecutionHealth(rows: ActionRow[]): {
  points: number;
  penalty: number;
  recovery: number;
  drivers: ScoreDriver[];
} {
  const maxPoints = 20;
  let penalty = 0;
  let recovery = 0;
  const drivers: ScoreDriver[] = [];

  for (const row of rows) {
    if (row.is_overdue) {
      const p = Number(row.overdue_penalty_weight ?? 1);
      penalty += p;
      drivers.push(buildOverdueDriver(row, p));
    }

    if (row.blocked_penalty > 0) {
      penalty += row.blocked_penalty;
      drivers.push(buildBlockedDriver(row));
    }

    if (row.missing_responsible_penalty > 0) {
      penalty += row.missing_responsible_penalty;
    }

    if (row.status === "closed") {
      const r = Math.min(row.expected_score_recovery_max, 3);
      recovery += r;
      drivers.push(buildRecoveryDriver(row, r));
    }
  }

  return {
    points: round2(clamp(maxPoints - penalty + recovery, 0, maxPoints)),
    penalty: round2(penalty),
    recovery: round2(recovery),
    drivers
  };
}

export function calculateEvidenceHealth(input: {
  requiredCount: number;
  approvedCount: number;
  trustWeights: number[];
  rejectedCount: number;
  needsMoreInfoCount: number;
}) {
  const maxPoints = 15;

  if (input.requiredCount === 0) {
    return { points: maxPoints, penalty: 0, recovery: 0, confidence: 100 };
  }

  const completionRatio = clamp(input.approvedCount / input.requiredCount, 0, 1);

  const averageTrust =
    input.trustWeights.length > 0
      ? input.trustWeights.reduce((s, x) => s + x, 0) / input.trustWeights.length
      : 0.5;

  const basePoints = maxPoints * completionRatio * averageTrust;
  const rejectionPenalty = input.rejectedCount * 2;
  const needsMoreInfoPenalty = input.needsMoreInfoCount * 1;

  const points = clamp(basePoints - rejectionPenalty - needsMoreInfoPenalty, 0, maxPoints);

  return {
    points: round2(points),
    penalty: round2(maxPoints - points),
    recovery: round2(points),
    confidence: round2(averageTrust * 100)
  };
}

export function aggregateGovernanceHealth(input: {
  escalationsByLevel: { L2: number; L3: number; L4: number };
  criticalTensionsWithoutOwner: number;
  criticalTensionsWithoutAction: number;
  approverSlaBreaches: number;
  dataConfidenceBelowThreshold: boolean;
  weeklyReportMissing: boolean;
  criticalRulesDisabled: number;
}): { points: number; penalty: number; drivers: ScoreDriver[] } {
  const maxPoints = 10;
  let penalty = 0;
  const drivers: ScoreDriver[] = [];

  penalty += input.escalationsByLevel.L4 * 3;
  penalty += input.escalationsByLevel.L3 * 2;
  penalty += input.escalationsByLevel.L2 * 1;
  penalty += input.criticalTensionsWithoutOwner * 3;
  penalty += input.criticalTensionsWithoutAction * 3;
  penalty += input.approverSlaBreaches * 2;
  if (input.dataConfidenceBelowThreshold) penalty += 2;
  if (input.weeklyReportMissing) penalty += 1;
  penalty += input.criticalRulesDisabled * 3;

  return {
    points: round2(clamp(maxPoints - penalty, 0, maxPoints)),
    penalty: round2(penalty),
    drivers
  };
}

CREATE TABLE IF NOT EXISTS faro.score_snapshots (
  score_snapshot_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL,
  score_model_version_id uuid NULL,

  dimension_type text NOT NULL CHECK (
    dimension_type IN ('company','area','branch','module',
                       'responsible','tension','action')
  ),
  dimension_id text NULL,
  dimension_code text NULL,
  dimension_name text NULL,

  period_start date NOT NULL,
  period_end date NOT NULL,

  score_value numeric(9,2) NOT NULL CHECK (score_value >= 0 AND score_value <= 100),
  previous_score_value numeric(9,2) NULL,
  score_delta numeric(9,2) NULL,

  score_status text NOT NULL CHECK (
    score_status IN ('excellent','good','attention',
                     'warning','critical','emergency')
  ),

  score_confidence numeric(9,2) NOT NULL DEFAULT 100
    CHECK (score_confidence >= 0 AND score_confidence <= 100),
  confidence_status text NOT NULL DEFAULT 'high'
    CHECK (confidence_status IN ('high','good','medium','low','critical')),

  components jsonb NOT NULL DEFAULT '{}'::jsonb,
  drivers jsonb NOT NULL DEFAULT '[]'::jsonb,
  recovery_opportunities jsonb NOT NULL DEFAULT '[]'::jsonb,
  explanation jsonb NOT NULL DEFAULT '{}'::jsonb,

  calculated_by text NOT NULL DEFAULT 'score_engine',
  calculated_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),

  UNIQUE(company_id, dimension_type, dimension_id, period_start, period_end)
);

CREATE INDEX IF NOT EXISTS idx_score_snapshots_company_period
  ON faro.score_snapshots(company_id, period_start DESC, period_end DESC);
CREATE INDEX IF NOT EXISTS idx_score_snapshots_status
  ON faro.score_snapshots(company_id, score_status, period_end DESC);
CREATE INDEX IF NOT EXISTS idx_score_snapshots_drivers
  ON faro.score_snapshots USING gin(drivers);

export async function saveFaroScoreSnapshot(params: {
  client: pg.PoolClient;
  result: FaroScoreResult;
  scoreModelVersionId?: string | null;
}): Promise<string> {
  const r = params.result;

  const snapshot = await params.client.query(
    `
    INSERT INTO faro.score_snapshots (
      company_id, score_model_version_id,
      dimension_type, dimension_id, dimension_code, dimension_name,
      period_start, period_end,
      score_value, previous_score_value, score_delta,
      score_status, score_confidence, confidence_status,
      components, drivers, recovery_opportunities, explanation
    )
    VALUES (
      $1, $2, $3, $4, $5, $6, $7, $8,
      $9, $10, $11, $12, $13, $14,
      $15::jsonb, $16::jsonb, $17::jsonb, $18::jsonb
    )
    ON CONFLICT (company_id, dimension_type, dimension_id, period_start, period_end)
    DO UPDATE SET
      score_value = EXCLUDED.score_value,
      score_delta = EXCLUDED.score_delta,
      score_status = EXCLUDED.score_status,
      score_confidence = EXCLUDED.score_confidence,
      components = EXCLUDED.components,
      drivers = EXCLUDED.drivers,
      recovery_opportunities = EXCLUDED.recovery_opportunities,
      explanation = EXCLUDED.explanation,
      calculated_at = now()
    RETURNING score_snapshot_id
    `,
    [
      r.company_id, params.scoreModelVersionId ?? null,
      r.dimension_type, r.dimension_id, r.dimension_code, r.dimension_name,
      r.period_start, r.period_end,
      r.score_value, r.previous_score_value, r.score_delta,
      r.score_status, r.score_confidence, r.confidence_status,
      JSON.stringify(r.components),
      JSON.stringify(r.drivers),
      JSON.stringify(r.recovery_opportunities),
      JSON.stringify(r.explanation)
    ]
  );

  return snapshot.rows[0].score_snapshot_id;
}

export function buildScoreExplanation(params: {
  scoreValue: number;
  previous: number | null;
  drivers: ScoreDriver[];
}): ScoreExplanation {
  const sorted = [...params.drivers].sort(
    (a, b) => Math.abs(b.impact_points) - Math.abs(a.impact_points)
  );

  const mainNegative = sorted
    .filter((d) => d.impact_direction === "negative")
    .slice(0, 4);

  const mainPositive = sorted
    .filter((d) => d.impact_direction === "positive")
    .slice(0, 4);

  const delta = params.previous == null
    ? null
    : round2(params.scoreValue - params.previous);

  const headline = buildScoreHeadline({
    scoreValue: params.scoreValue,
    delta,
    topNegativeDriver: mainNegative[0]
  });

  const summary = buildScoreSummary({
    scoreValue: params.scoreValue,
    delta,
    mainNegative,
    mainPositive
  });

  return {
    headline,
    summary,
    main_negative_drivers: mainNegative,
    main_positive_drivers: mainPositive
  };
}

export function buildScoreHeadline(params: {
  scoreValue: number;
  delta: number | null;
  topNegativeDriver?: ScoreDriver;
}): string {
  if (params.delta !== null && params.delta <= -8) {
    return "El FARO Score cayó por deterioro ejecutivo relevante.";
  }
  if (params.scoreValue < 60) {
    return "La empresa se encuentra en zona crítica de dirección.";
  }
  if (params.topNegativeDriver) {
    return `El principal factor de riesgo es ${params.topNegativeDriver.driver_title}.`;
  }
  if (params.delta !== null && params.delta > 0) {
    return "El FARO Score mejora por avance en ejecución validada.";
  }
  return "El FARO Score se mantiene estable con focos pendientes.";
}

export function calculatePotentialRecovery(rows: ActionRow[]): {
  total_max: number;
  total_min: number;
  opportunities: ScoreDriver[];
} {
  const opportunities: ScoreDriver[] = [];
  let totalMax = 0;
  let totalMin = 0;

  for (const row of rows) {
    if (row.status === "closed" || row.status === "cancelled") continue;

    const recoveryMax = Number(row.expected_score_recovery_max ?? 0);
    const recoveryMin = Number(row.expected_score_recovery_min ?? 0);

    if (recoveryMax <= 0) continue;

    // Multiplicador por estado: cuanto más avanzada, más probable recuperar
    const readiness = row.status === "in_verification" ? 0.85
                    : row.status === "in_execution"   ? 0.65
                    : row.status === "in_analysis"    ? 0.40
                    : row.status === "assigned"       ? 0.25
                    : 0.10;

    const expectedMax = round2(recoveryMax * readiness);
    const expectedMin = round2(recoveryMin * readiness);

    totalMax += expectedMax;
    totalMin += expectedMin;

    opportunities.push({
      driver_type: "recovery",
      driver_code: row.action_code,
      driver_title: row.title,
      impact_points: expectedMax,
      impact_direction: "positive",
      explanation: `${row.action_code} puede recuperar +${expectedMax} pts si cierra con evidencia high/critical aprobada.`,
      payload: {
        action_id: row.action_id,
        status: row.status,
        expected_min: expectedMin,
        expected_max: expectedMax
      }
    });
  }

  return {
    total_max: round2(totalMax),
    total_min: round2(totalMin),
    opportunities: opportunities.sort(
      (a, b) => b.impact_points - a.impact_points
    )
  };
}

import { describe, expect, it } from "vitest";
import { calculateTensionPenalty } from "../src/score/score.tensions";

describe("FARO Score · tension penalty", () => {
  it("penalizes critical more than high", () => {
    const critical = calculateTensionPenalty({
      severity: "critical",
      priorityScore: 90,
      confidenceScore: 90,
      status: "new"
    });
    const high = calculateTensionPenalty({
      severity: "high",
      priorityScore: 90,
      confidenceScore: 90,
      status: "new"
    });
    expect(critical).toBeGreaterThan(high);
  });

  it("does not penalize closed tension", () => {
    const penalty = calculateTensionPenalty({
      severity: "critical",
      priorityScore: 100,
      confidenceScore: 100,
      status: "closed"
    });
    expect(penalty).toBe(0);
  });

  it("applies recurrence multiplier 1.4 when count >= 2", () => {
    const base = calculateTensionPenalty({
      severity: "high",
      priorityScore: 80,
      confidenceScore: 80,
      status: "new"
    });
    const recurrent = calculateTensionPenalty({
      severity: "high",
      priorityScore: 80,
      confidenceScore: 80,
      status: "new",
      recurrenceCount: 2
    });
    expect(recurrent).toBeCloseTo(base * 1.4, 1);
  });
});

import { describe, expect, it } from "vitest";
import { clamp, scoreStatus, confidenceStatus } from "../src/score/score.helpers";

describe("FARO Score · helpers", () => {
  it("clamps under 0 to 0", () => {
    expect(clamp(-10, 0, 100)).toBe(0);
  });

  it("clamps over 100 to 100", () => {
    expect(clamp(150, 0, 100)).toBe(100);
  });

  it("maps score thresholds correctly", () => {
    expect(scoreStatus(95)).toBe("excellent");
    expect(scoreStatus(82)).toBe("good");
    expect(scoreStatus(74)).toBe("attention");
    expect(scoreStatus(66)).toBe("warning");
    expect(scoreStatus(50)).toBe("critical");
    expect(scoreStatus(20)).toBe("emergency");
  });

  it("maps confidence thresholds correctly", () => {
    expect(confidenceStatus(90)).toBe("high");
    expect(confidenceStatus(75)).toBe("good");
    expect(confidenceStatus(60)).toBe("medium");
    expect(confidenceStatus(40)).toBe("low");
    expect(confidenceStatus(20)).toBe("critical");
  });
});