acmcc/supabase/functions/import-violations-csv/index.ts

import { createClient } from "https://esm.sh/@supabase/supabase-js@2.49.4";
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";

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

// CSV parser that handles multiline quoted fields
function parseCSV(text: string): Record<string, string>[] {
  const rows: string[][] = [];
  let currentRow: string[] = [];
  let currentField = "";
  let inQuotes = false;
  
  for (let i = 0; i < text.length; i++) {
    const char = text[i];
    const nextChar = text[i + 1];
    
    if (inQuotes) {
      if (char === '"' && nextChar === '"') {
        currentField += '"';
        i++; // skip escaped quote
      } else if (char === '"') {
        inQuotes = false;
      } else {
        currentField += char;
      }
    } else {
      if (char === '"') {
        inQuotes = true;
      } else if (char === ',') {
        currentRow.push(currentField);
        currentField = "";
      } else if (char === '\n' || (char === '\r' && nextChar === '\n')) {
        currentRow.push(currentField);
        currentField = "";
        rows.push(currentRow);
        currentRow = [];
        if (char === '\r') i++; // skip \n after \r
      } else {
        currentField += char;
      }
    }
  }
  
  // Push last field and row
  if (currentField || currentRow.length > 0) {
    currentRow.push(currentField);
    rows.push(currentRow);
  }
  
  if (rows.length < 2) return [];
  
  const headers = rows[0].map(h => h.trim());
  const results: Record<string, string>[] = [];
  
  for (let i = 1; i < rows.length; i++) {
    if (rows[i].length < 2) continue; // skip empty rows
    const obj: Record<string, string> = {};
    for (let j = 0; j < headers.length; j++) {
      obj[headers[j]] = rows[i][j] ?? "";
    }
    results.push(obj);
  }
  
  return results;
}

function tryParseJSON(str: string): any {
  if (!str || str === "[]" || str === "") return null;
  try {
    return JSON.parse(str);
  } catch {
    return null;
  }
}

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

  try {
    const supabaseUrl = Deno.env.get("SUPABASE_URL")!;
    const serviceRoleKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
    const supabase = createClient(supabaseUrl, serviceRoleKey);

    // Accept CSV either as body or fetch from a URL param
    const url = new URL(req.url);
    const csvUrl = url.searchParams.get("csv_url");
    let csvText: string;
    if (csvUrl) {
      const resp = await fetch(csvUrl);
      csvText = await resp.text();
    } else {
      csvText = await req.text();
    }
    const records = parseCSV(csvText);
    
    console.log(`Parsed ${records.length} violation records from CSV`);

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

    for (const row of records) {
      // Map priority values
      let priority = row.priority || null;
      if (priority === 'normal') priority = 'medium';
      
      const violation = {
        id: row.id || undefined,
        association_id: row.client_id,
        title: row.violation_type || "Untitled Violation",
        address: row.address || null,
        violation_type: row.violation_type || null,
        status: row.status || null,
        photo_url: row.photo_url || null,
        notes: row.notes || null,
        created_at: row.created_at || undefined,
        updated_at: row.updated_at || undefined,
        violation_date: row.violation_date || null,
        due_date: row.due_date || null,
        property_id: row.property_id || null,
        priority: priority,
        assigned_to: row.assigned_to || null,
        notice_level: row.notice_level || null,
        notice_history: tryParseJSON(row.notice_history),
        stage: row.stage || null,
        timeline_entries: tryParseJSON(row.timeline_entries),
        photo_urls: tryParseJSON(row.photo_urls),
        description: row.description || null,
        created_by: null, // Skip created_by to avoid FK issues
        unit_id: row.unit_id || null,
      };

      // Remove empty string keys that should be null/undefined
      if (!violation.property_id) delete (violation as any).property_id;
      if (!violation.assigned_to) delete (violation as any).assigned_to;
      if (!violation.created_by) delete (violation as any).created_by;
      if (!violation.unit_id) delete (violation as any).unit_id;
      if (!violation.due_date) delete (violation as any).due_date;

      const { error } = await supabase
        .from("violations")
        .upsert(violation, { onConflict: "id" });

      if (error) {
        console.error(`Error inserting ${row.id}: ${error.message}`);
        errors.push(`${row.id}: ${error.message}`);
      } else {
        inserted++;
      }
    }

    return new Response(
      JSON.stringify({ 
        success: true, 
        total: records.length, 
        inserted, 
        errors: errors.length,
        errorDetails: errors.slice(0, 10)
      }),
      { headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  } catch (err) {
    console.error("Import error:", err);
    return new Response(
      JSON.stringify({ success: false, error: err.message }),
      { status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
    );
  }
});