import { supabase } from "@/lib/supabase";

export type TeamHoursSource = "app" | "excel_history";

export interface TeamHoursRecord {
  id: string;
  recordSource: TeamHoursSource;
  sourceKey: string;
  workDate: string;
  projectName: string;
  client: string;
  country: string;
  workedByName: string;
  workedByEmail: string;
  taskName: string;
  durationMinutes: number;
  notes: string;
}

export interface TeamHoursRosterMember {
  email: string;
  fullName: string;
}

type DbTeamHoursRecord = {
  id: string;
  record_source: TeamHoursSource | string | null;
  source_key: string | null;
  work_date: string;
  project_name: string | null;
  client: string | null;
  country: string | null;
  worked_by_name: string | null;
  worked_by_email: string | null;
  task_name: string | null;
  duration_minutes: number | string | null;
  notes: string | null;
};

type DbRosterMember = {
  email: string | null;
  full_name: string | null;
};

function clean(value: unknown): string {
  return String(value ?? "").trim().replace(/\s+/g, " ");
}

function mapRecord(row: DbTeamHoursRecord): TeamHoursRecord {
  return {
    id: clean(row.id),
    recordSource: row.record_source === "app" ? "app" : "excel_history",
    sourceKey: clean(row.source_key),
    workDate: clean(row.work_date),
    projectName: clean(row.project_name) || "Sin nombre",
    client: clean(row.client),
    country: clean(row.country),
    workedByName: clean(row.worked_by_name) || "Sin nombre",
    workedByEmail: clean(row.worked_by_email).toLowerCase(),
    taskName: clean(row.task_name) || "Sin tarea",
    durationMinutes: Math.max(0, Math.round(Number(row.duration_minutes) || 0)),
    notes: clean(row.notes),
  };
}

export async function loadTeamHoursRecords({
  from,
  to,
}: {
  from?: string;
  to?: string;
} = {}): Promise<TeamHoursRecord[]> {
  const pageSize = 1000;
  const rows: DbTeamHoursRecord[] = [];

  for (let offset = 0; ; offset += pageSize) {
    const { data, error } = await supabase.rpc("tablero_cdc_get_team_hours_records", {
      p_from: from || null,
      p_to: to || null,
      p_limit: pageSize,
      p_offset: offset,
    });

    if (error) {
      console.error("No se pudieron cargar las horas del equipo:", error);
      throw new Error("No pudimos cargar las horas ahora. Intenta nuevamente.");
    }

    const batch = (data || []) as DbTeamHoursRecord[];
    rows.push(...batch);

    if (batch.length < pageSize) break;
  }

  return rows.map(mapRecord).filter((row) => row.durationMinutes > 0 && row.workDate);
}

export async function loadTeamHoursRoster(): Promise<TeamHoursRosterMember[]> {
  const { data, error } = await supabase.rpc("tablero_cdc_get_team_hours_roster");

  if (error) {
    // Compatibilidad durante despliegue: el módulo sigue mostrando a quienes tengan horas
    // aunque el pequeño RPC de roster todavía no se haya instalado.
    console.warn("No se pudo cargar el roster completo del equipo:", error);
    return [];
  }

  return ((data || []) as DbRosterMember[])
    .map((row) => ({
      email: clean(row.email).toLowerCase(),
      fullName: clean(row.full_name) || clean(row.email),
    }))
    .filter((row) => row.email && row.fullName);
}
