site-web-targo/supabase/functions/import-csv/index.ts

import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers":
    "authorization, x-client-info, apikey, content-type, x-supabase-client-platform, x-supabase-client-platform-version, x-supabase-client-runtime, x-supabase-client-runtime-version",
};

const BATCH_SIZE = 200;
const MAX_RETRIES = 3;
const RETRY_DELAY_MS = 2000;

async function upsertWithRetry(
  supabase: any,
  table: string,
  batch: any[],
  onConflict: string,
  ignoreDuplicates = false
) {
  for (let attempt = 0; attempt < MAX_RETRIES; attempt++) {
    const opts: any = { onConflict };
    if (ignoreDuplicates) opts.ignoreDuplicates = true;
    const { error } = await supabase.from(table).upsert(batch, opts);
    if (!error) return { error: null };
    if (error.message?.includes("statement timeout") && attempt < MAX_RETRIES - 1) {
      console.warn(`[Retry ${attempt + 1}/${MAX_RETRIES}] timeout on ${table}, waiting ${RETRY_DELAY_MS}ms...`);
      await new Promise((r) => setTimeout(r, RETRY_DELAY_MS * (attempt + 1)));
      continue;
    }
    return { error };
  }
  return { error: { message: "Max retries exceeded" } };
}

Deno.serve(async (req) => {
  if (req.method === "OPTIONS") {
    return new Response(null, { headers: corsHeaders });
  }

  try {
    // --- Auth check: require a valid JWT ---
    const authHeader = req.headers.get("Authorization");
    if (!authHeader) {
      return new Response(
        JSON.stringify({ error: "Unauthorized: missing auth header" }),
        { status: 401, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    const authClient = createClient(
      Deno.env.get("SUPABASE_URL")!,
      Deno.env.get("SUPABASE_ANON_KEY")!,
      { global: { headers: { Authorization: authHeader } } }
    );

    const token = authHeader.replace("Bearer ", "");
    const { data: claimsData, error: authError } = await authClient.auth.getClaims(token);
    if (authError || !claimsData?.claims?.sub) {
      return new Response(
        JSON.stringify({ error: "Unauthorized: invalid token" }),
        { status: 401, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }
    const userId = claimsData.claims.sub;

    // --- Admin role check ---
    const serviceClient = createClient(
      Deno.env.get("SUPABASE_URL")!,
      Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
    );
    const { data: roleRow } = await serviceClient
      .from("user_roles")
      .select("role")
      .eq("user_id", userId)
      .eq("role", "admin")
      .maybeSingle();

    if (!roleRow) {
      return new Response(
        JSON.stringify({ error: "Forbidden: admin role required" }),
        { status: 403, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    // serviceClient already created above with service role
    const supabase = serviceClient;

    const { type, rows } = await req.json();

    if (!type || !rows || !Array.isArray(rows) || rows.length === 0) {
      return new Response(
        JSON.stringify({ error: "Missing 'type' (addresses|fiber) or 'rows' array" }),
        { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    let inserted = 0;
    let errors = 0;
    const errorMessages: string[] = [];

    if (type === "addresses") {
      const mapped = rows.map((r: Record<string, string>) => ({
        identifiant_unique_adresse: r.identifiant_unique_adresse,
        date_diffusion_version: r.date_diffusion_version || null,
        date_creation: r.date_creation || null,
        date_modification: r.date_modification || null,
        date_fin: r.date_fin || null,
        numero_municipal: r.numero_municipal || null,
        numero_municipal_suffixe: r.numero_municipal_suffixe || null,
        numero_unite: r.numero_unite || null,
        type_unite: r.type_unite || null,
        code_postal: r.code_postal || null,
        seqodo: r.seqodo || null,
        generique_odonyme: r.generique_odonyme || null,
        particule_odonyme: r.particule_odonyme || null,
        specifique_odonyme: r.specifique_odonyme || null,
        point_cardinal_odonyme: r.point_cardinal_odonyme || null,
        odonyme_recompose_normal: r.odonyme_recompose_normal || null,
        odonyme_recompose_court: r.odonyme_recompose_court || null,
        odonyme_recompose_long: r.odonyme_recompose_long || null,
        discriminant_odonyme: r.discriminant_odonyme || null,
        odonyme_officiel_ctop: r.odonyme_officiel_ctop || null,
        adresse_formatee: r.adresse_formatee || null,
        qualite_positionnement_geometrique: r.qualite_positionnement_geometrique || null,
        etat: r.etat || null,
        code_utilisation: r.code_utilisation || null,
        categorie: r.categorie || null,
        nombre_unite: r.nombre_unite ? parseInt(r.nombre_unite) : null,
        numero_lot: r.numero_lot || null,
        matricule_unite_evaluation: r.matricule_unite_evaluation || null,
        nosqnocivq: r.nosqnocivq || null,
        caracteristique_adresse: r.caracteristique_adresse || null,
        code_region_administrative: r.code_region_administrative || null,
        nom_region_administrative: r.nom_region_administrative || null,
        code_mrc: r.code_mrc || null,
        nom_mrc: r.nom_mrc || null,
        code_municipalite: r.code_municipalite || null,
        nom_municipalite: r.nom_municipalite || null,
        nom_municipalite_complet: r.nom_municipalite_complet || null,
        code_arrondissement: r.code_arrondissement || null,
        nom_arrondissement: r.nom_arrondissement || null,
        code_communaute_metropolitaine: r.code_communaute_metropolitaine || null,
        nom_communaute_metropolitaine: r.nom_communaute_metropolitaine || null,
        identifiant_reseau_routier: r.identifiant_reseau_routier || null,
        cote_route: r.cote_route || null,
        longitude: r.longitude ? parseFloat(r.longitude) : null,
        latitude: r.latitude ? parseFloat(r.latitude) : null,
      }));

      for (let i = 0; i < mapped.length; i += BATCH_SIZE) {
        const batch = mapped.slice(i, i + BATCH_SIZE);
        const { error } = await upsertWithRetry(supabase, "addresses", batch, "identifiant_unique_adresse");
        if (error) {
          console.error(`Addresses batch error:`, error.message);
          errors += batch.length;
          if (errorMessages.length < 3) errorMessages.push(error.message);
        } else {
          inserted += batch.length;
        }
      }
    } else if (type === "fiber") {
      const mapped = rows
        .filter((r: Record<string, string>) => r.uuidadresse || r.idGouvQc)
        .map((r: Record<string, string>) => ({
          id_placemark: r.id_placemark || null,
          id_appart: r.id_appart || null,
          uuidadresse: r.idGouvQc || r.uuidadresse,
          nom: r.nom || null,
          civique: r.civique || null,
          appartement: r.appartement || null,
          rue: r.rue || null,
          ville: r.ville || null,
          code_postal: r.code_postal || null,
          lien_googlemap: r.lien_googlemap || null,
          zone_tarifaire: r.zone_tarifaire ? parseInt(r.zone_tarifaire) : 1,
          max_speed: r.max_speed ? parseInt(r.max_speed) : 0,
          commentaire: r.commentaire || null,
        }));

      for (let i = 0; i < mapped.length; i += BATCH_SIZE) {
        const batch = mapped.slice(i, i + BATCH_SIZE);
        const { error } = await upsertWithRetry(supabase, "fiber_availability", batch, "uuidadresse", true);
        if (error) {
          console.error(`Fiber batch error:`, error.message);
          errors += batch.length;
          if (errorMessages.length < 3) errorMessages.push(error.message);
        } else {
          inserted += batch.length;
        }
      }
    } else {
      return new Response(
        JSON.stringify({ error: "Type must be 'addresses' or 'fiber'" }),
        { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } }
      );
    }

    return new Response(
      JSON.stringify({ success: true, type, inserted, errors, errorMessages }),
      { headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  } catch (err) {
    console.error("Import error:", err);
    return new Response(
      JSON.stringify({ error: err.message }),
      { status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  }
});