Dashboard Responsable MVP

GET /api/v1/me/actions?status=new,in_progress,waiting_evidence&priority=critical,high&due=overdue,today HTTP/1.1
Host: app.farodireccion.com
Accept: application/json
Cookie: faro_session=<jwt>

{
  "user": {
    "user_id": "12000000-0000-0000-0000-000000000002",
    "full_name": "María Fernández",
    "role": "commercial_manager"
  },
  "period": {
    "period_start": "2026-05-01",
    "period_end": "2026-05-31",
    "label": "Mayo 2026"
  },
  "summary": {
    "total": 8,
    "overdue": 2,
    "due_today": 1,
    "critical": 3,
    "high": 4,
    "waiting_evidence": 3,
    "in_review": 1,
    "blocked": 1,
    "closed_this_week": 2
  },
  "score": {
    "potential_recovery": 16,
    "blocked_recovery": 5,
    "recovered_this_week": 4
  },
  "items": [
    {
      "action_id": "23000000-0000-0000-0000-000000000001",
      "action_code": "ACT-COM-001",
      "title": "Revisar política de descuentos",
      "description": "Analizar descuentos aplicados por vendedor, producto, cliente y sucursal.",
      "status": "new",
      "priority": "critical",
      "due_date": "2026-06-06",
      "is_overdue": false,
      "due_bucket": "this_week",
      "action_type": "corrective",
      "closure_criteria": "Nueva política de descuentos aprobada por dirección y comunicada al equipo comercial.",
      "expected_impact": "Mejora de margen, disciplina comercial y rentabilidad.",
      "expected_score_recovery_min": 3,
      "expected_score_recovery_max": 8,
      "tension": {
        "tension_id": "22000000-0000-0000-0000-000000000001",
        "tension_code": "TNS-001",
        "title": "Crecimiento no rentable",
        "severity": "critical",
        "priority_score": 92,
        "score_impact": -8.5
      },
      "evidence": {
        "required": true,
        "required_codes": ["EVD-007", "EVD-012"],
        "missing": 2,
        "submitted": 0,
        "approved": 0,
        "requirements": [
          {
            "evidence_code": "EVD-007",
            "name": "Cambio de política",
            "trust_level": "critical",
            "status": "missing"
          },
          {
            "evidence_code": "EVD-012",
            "name": "Validación de dirección",
            "trust_level": "critical",
            "status": "missing"
          }
        ]
      },
      "approver": {
        "user_id": "12000000-0000-0000-0000-000000000001",
        "full_name": "Tomás Pombo"
      }
    }
  ]
}

GET /api/v1/actions/:action_id HTTP/1.1
Host: app.farodireccion.com
Accept: application/json

CREATE OR REPLACE VIEW faro.v_responsible_action_dashboard AS
SELECT
  a.company_id,
  a.action_id,
  a.action_code,
  ad.name AS catalog_name,
  a.title,
  a.description,
  a.action_type,
  a.status,
  a.priority,
  a.due_date,
  CASE
    WHEN a.status NOT IN ('closed', 'cancelled', 'rejected')
     AND a.due_date < CURRENT_DATE THEN true
    ELSE false
  END AS is_overdue,
  CASE
    WHEN a.due_date < CURRENT_DATE THEN 'overdue'
    WHEN a.due_date = CURRENT_DATE THEN 'today'
    WHEN a.due_date <= CURRENT_DATE + INTERVAL '7 days' THEN 'this_week'
    ELSE 'later'
  END AS due_bucket,

  a.responsible_user_id,
  ru.full_name AS responsible_name,
  ru.email AS responsible_email,

  a.approver_user_id,
  au.full_name AS approver_name,
  au.email AS approver_email,

  a.evidence_required,
  a.closure_criteria,
  a.expected_impact,
  a.expected_impact_amount,

  ad.executive_purpose,
  ad.success_metric,
  ad.expected_business_impact,
  ad.expected_score_recovery_min,
  ad.expected_score_recovery_max,
  ad.score_dimension AS action_score_dimension,

  t.tension_id,
  t.tension_code,
  t.title AS tension_title,
  t.severity AS tension_severity,
  t.priority_score AS tension_priority_score,
  t.score_impact AS tension_score_impact,
  td.business_question,
  td.executive_diagnosis,
  td.area_code,
  td.module_code,

  COALESCE(ev_summary.required_count, 0) AS evidence_required_count,
  COALESCE(ev_summary.submitted_count, 0) AS evidence_submitted_count,
  COALESCE(ev_summary.approved_count, 0) AS evidence_approved_count,
  GREATEST(
    COALESCE(ev_summary.required_count, 0) - COALESCE(ev_summary.approved_count, 0),
    0
  ) AS evidence_missing_count,

  a.payload

FROM faro.actions a

LEFT JOIN faro.action_definitions ad
  ON ad.action_code = a.action_code
 AND ad.status = 'active'

LEFT JOIN faro.tensions t
  ON t.tension_id = a.tension_id
 AND t.company_id = a.company_id

LEFT JOIN faro.tension_definitions td
  ON td.tension_code = t.tension_code
 AND td.status = 'active'

LEFT JOIN faro.users ru
  ON ru.user_id = a.responsible_user_id

LEFT JOIN faro.users au
  ON au.user_id = a.approver_user_id

LEFT JOIN (
  SELECT
    aes.company_id,
    aes.action_id,
    COUNT(*) AS required_count,
    COUNT(*) FILTER (WHERE aes.evidence_status IN ('submitted', 'approved')) AS submitted_count,
    COUNT(*) FILTER (WHERE aes.evidence_status = 'approved') AS approved_count
  FROM faro.v_action_evidence_status aes
  GROUP BY aes.company_id, aes.action_id
) ev_summary
  ON ev_summary.company_id = a.company_id
 AND ev_summary.action_id = a.action_id;

CREATE TABLE IF NOT EXISTS faro.action_events (
  action_event_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL,
  action_id uuid NOT NULL,
  event_type text NOT NULL,
  actor_user_id uuid NULL,
  from_status text NULL,
  to_status text NULL,
  title text NOT NULL,
  description text NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT action_events_event_type_check CHECK (
    event_type IN (
      'action_created', 'action_started', 'evidence_uploaded',
      'sent_to_review', 'evidence_approved', 'evidence_rejected',
      'action_blocked', 'action_escalated', 'extension_requested',
      'action_closed'
    )
  )
);

CREATE INDEX idx_action_events_action ON faro.action_events (company_id, action_id, created_at DESC);
CREATE INDEX idx_action_events_actor ON faro.action_events (company_id, actor_user_id, created_at DESC);

SELECT
  action_id,
  action_code,
  COALESCE(title, catalog_name) AS title,
  description,
  action_type,
  status,
  priority,
  due_date,
  is_overdue,
  due_bucket,
  closure_criteria,
  expected_impact,
  expected_score_recovery_min,
  expected_score_recovery_max,

  tension_id,
  tension_code,
  tension_title,
  tension_severity,
  tension_priority_score,
  tension_score_impact,
  business_question,
  executive_diagnosis,

  evidence_required,
  evidence_required_count,
  evidence_submitted_count,
  evidence_approved_count,
  evidence_missing_count,

  approver_user_id,
  approver_name,
  approver_email,

  payload

FROM faro.v_responsible_action_dashboard
WHERE company_id = $1
  AND responsible_user_id = $2
  AND ($3::text[] IS NULL OR status = ANY($3))
  AND ($4::text[] IS NULL OR priority = ANY($4))
  AND ($5::text[] IS NULL OR due_bucket = ANY($5))
  AND ($6::text IS NULL OR area_code = $6)
  AND (
    $7::text IS NULL
    OR lower(title) LIKE '%' || lower($7) || '%'
    OR lower(action_code) LIKE '%' || lower($7) || '%'
    OR lower(tension_code) LIKE '%' || lower($7) || '%'
  )
ORDER BY
  CASE
    WHEN is_overdue = true AND priority = 'critical' THEN 1
    WHEN is_overdue = true THEN 2
    WHEN due_bucket = 'today' THEN 3
    WHEN priority = 'critical' THEN 4
    WHEN priority = 'high' THEN 5
    ELSE 6
  END,
  due_date ASC,
  tension_priority_score DESC
LIMIT $8 OFFSET $9;

SELECT
  COUNT(*)::int AS total,
  COUNT(*) FILTER (WHERE is_overdue = true)::int AS overdue,
  COUNT(*) FILTER (WHERE due_bucket = 'today')::int AS due_today,
  COUNT(*) FILTER (WHERE priority = 'critical')::int AS critical,
  COUNT(*) FILTER (WHERE priority = 'high')::int AS high,
  COUNT(*) FILTER (WHERE evidence_missing_count > 0)::int AS waiting_evidence,
  COUNT(*) FILTER (WHERE status = 'in_review')::int AS in_review,
  COUNT(*) FILTER (WHERE status = 'blocked')::int AS blocked,
  COUNT(*) FILTER (
    WHERE status = 'closed'
      AND due_date >= CURRENT_DATE - INTERVAL '7 days'
  )::int AS closed_this_week,
  COALESCE(SUM(expected_score_recovery_max), 0)::numeric AS potential_recovery,
  COALESCE(SUM(expected_score_recovery_max) FILTER (WHERE status = 'blocked'), 0)::numeric AS blocked_recovery
FROM faro.v_responsible_action_dashboard
WHERE company_id = $1
  AND responsible_user_id = $2
  AND status NOT IN ('cancelled', 'rejected');

export type ActionPriority = "low" | "medium" | "high" | "critical";

// Status enum único — coordinado con WF-001 · Workflow de Acción.
// El evento `escalated` es un evento de timeline (D4), NO un estado terminal.
export type ResponsibleActionStatus =
  | "new"
  | "in_progress"
  | "blocked"
  | "waiting_evidence"
  | "in_review"
  | "approved"
  | "closed"
  | "expired"
  | "cancelled"
  | "rejected";

export type DueBucket = "overdue" | "today" | "this_week" | "later";

export type EvidenceRequirementStatus = {
  evidence_code: string;
  name: string;
  trust_level: "low" | "medium" | "high" | "critical";
  status: "missing" | "submitted" | "approved" | "rejected" | "needs_more_info";
};

export type ResponsibleActionItem = {
  action_id: string;
  action_code: string;
  title: string;
  description: string | null;
  status: ResponsibleActionStatus;
  priority: ActionPriority;
  due_date: string | null;
  is_overdue: boolean;
  due_bucket: DueBucket;
  action_type: string;
  closure_criteria: string | null;
  expected_impact: string | null;
  expected_score_recovery_min: number | null;
  expected_score_recovery_max: number | null;
  tension: {
    tension_id: string | null;
    tension_code: string | null;
    title: string | null;
    severity: string | null;
    priority_score: number | null;
    score_impact: number | null;
    business_question?: string | null;
    executive_diagnosis?: string | null;
  };
  evidence: {
    required: boolean;
    required_codes: string[];
    missing: number;
    submitted: number;
    approved: number;
    requirements: EvidenceRequirementStatus[];
  };
  approver: {
    user_id: string | null;
    full_name: string | null;
    email?: string | null;
  };
};

export type ResponsibleDashboardResponse = {
  user: {
    user_id: string;
    full_name: string;
    role: string;
  };
  period: {
    period_start: string;
    period_end: string;
    label: string;
  };
  summary: {
    total: number;
    overdue: number;
    due_today: number;
    critical: number;
    high: number;
    waiting_evidence: number;
    in_review: number;
    blocked: number;
    closed_this_week: number;
  };
  score: {
    potential_recovery: number;
    blocked_recovery: number;
    recovered_this_week: number;
  };
  items: ResponsibleActionItem[];
};

import type { ActionPriority } from "./responsible.types";

export function priorityLabel(priority: ActionPriority): string {
  const map: Record<ActionPriority, string> = {
    critical: "Crítica",
    high: "Alta",
    medium: "Media",
    low: "Baja"
  };

  return map[priority] ?? priority;
}

export function priorityTone(priority: ActionPriority) {
  const map = {
    critical: {
      badge: "bg-red-100 text-red-800 border border-red-200",
      border: "border-red-400",
      tag: "critical"
    },
    high: {
      badge: "bg-amber-100 text-amber-800 border border-amber-200",
      border: "border-amber-300",
      tag: "high"
    },
    medium: {
      badge: "bg-yellow-100 text-yellow-800 border border-yellow-200",
      border: "border-yellow-300",
      tag: "medium"
    },
    low: {
      badge: "bg-slate-100 text-slate-700 border border-slate-200",
      border: "border-slate-300",
      tag: "low"
    }
  };

  return map[priority] ?? map.low;
}

// Orden canónico de prioridad para tie-breakers en frontend.
export const PRIORITY_RANK: Record<ActionPriority, number> = {
  critical: 4,
  high: 3,
  medium: 2,
  low: 1
};

import type { ResponsibleActionStatus } from "./responsible.types";

export function actionStatusLabel(status: ResponsibleActionStatus): string {
  const map: Record<ResponsibleActionStatus, string> = {
    new: "Nueva",
    in_progress: "En ejecución",
    blocked: "Bloqueada",
    waiting_evidence: "Sin evidencia",
    in_review: "En revisión",
    approved: "Aprobada",
    closed: "Cerrada",
    expired: "Vencida",
    cancelled: "Cancelada",
    rejected: "Rechazada"
  };

  return map[status] ?? status;
}

// Estados en los que el responsable puede cargar evidencia.
export const EVIDENCE_UPLOADABLE_STATUSES: ResponsibleActionStatus[] = [
  "new",
  "in_progress",
  "waiting_evidence",
  "expired"
];

// Estados que NO se pueden cerrar sin evidencia aprobada.
export function canCloseAction(
  status: ResponsibleActionStatus,
  evidenceMissing: number,
  evidenceRequired: boolean
): boolean {
  if (status === "closed" || status === "cancelled") return false;
  if (evidenceRequired && evidenceMissing > 0) return false;
  return status === "approved" || status === "in_review";
}

import type { ResponsibleDashboardResponse } from "./responsible.types";

export type GetResponsibleActionsParams = {
  status?: string[];
  priority?: string[];
  due?: string[];
  area_code?: string;
  q?: string;
  page?: number;
  page_size?: number;
};

export async function getResponsibleActions(
  params: GetResponsibleActionsParams = {}
): Promise<ResponsibleDashboardResponse> {
  const search = new URLSearchParams();

  if (params.status?.length) search.set("status", params.status.join(","));
  if (params.priority?.length) search.set("priority", params.priority.join(","));
  if (params.due?.length) search.set("due", params.due.join(","));
  if (params.area_code) search.set("area_code", params.area_code);
  if (params.q) search.set("q", params.q);
  if (params.page) search.set("page", String(params.page));
  if (params.page_size) search.set("page_size", String(params.page_size));

  const response = await fetch(`/api/v1/me/actions?${search.toString()}`, {
    method: "GET",
    headers: { "Content-Type": "application/json" },
    cache: "no-store"
  });

  if (!response.ok) {
    throw new Error("No se pudo cargar tu tablero de acciones");
  }

  return response.json();
}

export async function updateActionStatus(actionId: string, status: string) {
  const response = await fetch(`/api/v1/actions/${actionId}/status`, {
    method: "PATCH",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ status })
  });

  if (!response.ok) {
    throw new Error("No se pudo actualizar la acción");
  }

  return response.json();
}

import { NextRequest, NextResponse } from "next/server";
import { db } from "@/lib/server/db";
import { getSessionContext } from "@/lib/server/session";

export async function GET(request: NextRequest) {
  const session = await getSessionContext();

  if (!session?.companyId || !session?.userId) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  const search = request.nextUrl.searchParams;

  const status = search.get("status")?.split(",") ?? null;
  const priority = search.get("priority")?.split(",") ?? null;
  const due = search.get("due")?.split(",") ?? null;
  const areaCode = search.get("area_code");
  const q = search.get("q");
  const page = Number(search.get("page") ?? 1);
  const pageSize = Math.min(Number(search.get("page_size") ?? 50), 200);
  const offset = (page - 1) * pageSize;

  const client = await db.connect();

  try {
    await client.query("BEGIN");

    // RLS: bind session into Postgres GUC variables.
    // Row Level Security policies de faro.actions leen estos valores.
    await client.query(`SELECT set_config('app.company_id', $1, true)`, [session.companyId]);
    await client.query(`SELECT set_config('app.user_id', $1, true)`, [session.userId]);
    await client.query(`SELECT set_config('app.role_codes', $1, true)`, [session.roleCodes.join(",")]);

    const actions = await client.query(
      `
      SELECT *
      FROM faro.v_responsible_action_dashboard
      WHERE company_id = $1
        AND responsible_user_id = $2
        AND ($3::text[] IS NULL OR status = ANY($3))
        AND ($4::text[] IS NULL OR priority = ANY($4))
        AND ($5::text[] IS NULL OR due_bucket = ANY($5))
        AND ($6::text IS NULL OR area_code = $6)
        AND (
          $7::text IS NULL
          OR lower(title) LIKE '%' || lower($7) || '%'
          OR lower(action_code) LIKE '%' || lower($7) || '%'
          OR lower(tension_code) LIKE '%' || lower($7) || '%'
        )
      ORDER BY
        CASE
          WHEN is_overdue = true AND priority = 'critical' THEN 1
          WHEN is_overdue = true THEN 2
          WHEN due_bucket = 'today' THEN 3
          WHEN priority = 'critical' THEN 4
          WHEN priority = 'high' THEN 5
          ELSE 6
        END,
        due_date ASC,
        tension_priority_score DESC
      LIMIT $8 OFFSET $9
      `,
      [session.companyId, session.userId, status, priority, due, areaCode, q, pageSize, offset]
    );

    const summary = await client.query(
      `
      SELECT
        COUNT(*)::int AS total,
        COUNT(*) FILTER (WHERE is_overdue = true)::int AS overdue,
        COUNT(*) FILTER (WHERE due_bucket = 'today')::int AS due_today,
        COUNT(*) FILTER (WHERE priority = 'critical')::int AS critical,
        COUNT(*) FILTER (WHERE priority = 'high')::int AS high,
        COUNT(*) FILTER (WHERE evidence_missing_count > 0)::int AS waiting_evidence,
        COUNT(*) FILTER (WHERE status = 'in_review')::int AS in_review,
        COUNT(*) FILTER (WHERE status = 'blocked')::int AS blocked,
        COUNT(*) FILTER (
          WHERE status = 'closed'
            AND due_date >= CURRENT_DATE - INTERVAL '7 days'
        )::int AS closed_this_week,
        COALESCE(SUM(expected_score_recovery_max), 0)::numeric AS potential_recovery,
        COALESCE(SUM(expected_score_recovery_max) FILTER (WHERE status = 'blocked'), 0)::numeric AS blocked_recovery
      FROM faro.v_responsible_action_dashboard
      WHERE company_id = $1
        AND responsible_user_id = $2
        AND status NOT IN ('cancelled', 'rejected')
      `,
      [session.companyId, session.userId]
    );

    await client.query("COMMIT");

    return NextResponse.json({
      user: {
        user_id: session.userId,
        full_name: session.fullName,
        role: session.primaryRole
      },
      period: {
        period_start: "2026-05-01",
        period_end: "2026-05-31",
        label: "Mayo 2026"
      },
      summary: {
        total: summary.rows[0].total,
        overdue: summary.rows[0].overdue,
        due_today: summary.rows[0].due_today,
        critical: summary.rows[0].critical,
        high: summary.rows[0].high,
        waiting_evidence: summary.rows[0].waiting_evidence,
        in_review: summary.rows[0].in_review,
        blocked: summary.rows[0].blocked,
        closed_this_week: summary.rows[0].closed_this_week
      },
      score: {
        potential_recovery: Number(summary.rows[0].potential_recovery ?? 0),
        blocked_recovery: Number(summary.rows[0].blocked_recovery ?? 0),
        recovered_this_week: 0
      },
      items: actions.rows.map(mapResponsibleActionRow)
    });
  } catch (error) {
    await client.query("ROLLBACK");
    return NextResponse.json(
      { error: "Could not load responsible actions" },
      { status: 500 }
    );
  } finally {
    client.release();
  }
}

function mapResponsibleActionRow(row: any) {
  const payload = row.payload ?? {};
  const requirements = payload.evidence_requirements ?? [];

  return {
    action_id: row.action_id,
    action_code: row.action_code,
    title: row.title ?? row.catalog_name,
    description: row.description,
    status: row.status,
    priority: row.priority,
    due_date: row.due_date,
    is_overdue: row.is_overdue,
    due_bucket: row.due_bucket,
    action_type: row.action_type,
    closure_criteria: row.closure_criteria,
    expected_impact: row.expected_impact ?? row.expected_business_impact,
    expected_score_recovery_min: row.expected_score_recovery_min
      ? Number(row.expected_score_recovery_min)
      : null,
    expected_score_recovery_max: row.expected_score_recovery_max
      ? Number(row.expected_score_recovery_max)
      : null,
    tension: {
      tension_id: row.tension_id,
      tension_code: row.tension_code,
      title: row.tension_title,
      severity: row.tension_severity,
      priority_score: row.tension_priority_score ? Number(row.tension_priority_score) : null,
      score_impact: row.tension_score_impact ? Number(row.tension_score_impact) : null,
      business_question: row.business_question,
      executive_diagnosis: row.executive_diagnosis
    },
    evidence: {
      required: row.evidence_required,
      required_codes: payload.evidence_required_codes ?? [],
      missing: Number(row.evidence_missing_count ?? 0),
      submitted: Number(row.evidence_submitted_count ?? 0),
      approved: Number(row.evidence_approved_count ?? 0),
      requirements: requirements.map((item: any) => ({
        evidence_code: item.evidence_code,
        name: item.name,
        trust_level: item.trust_level,
        status: "missing"
      }))
    },
    approver: {
      user_id: row.approver_user_id,
      full_name: row.approver_name,
      email: row.approver_email
    }
  };
}

import { ResponsibleDashboardPage } from "@/components/responsible/ResponsibleDashboardPage";

export const metadata = {
  title: "Mis acciones · FARO Connect",
  description: "Vista diaria de acciones asignadas, vencimientos y recuperación de Score."
};

export default function Page() {
  return <ResponsibleDashboardPage />;
}