acmcc/supabase/functions/migrate-from-source/index.ts

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

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",
};

// Tables in dependency order (parents first)
const MIGRATION_TABLES = [
  // No FK dependencies
  "associations",
  "company_settings",
  "email_templates",
  "fee_schedule_subcategories",
  "fee_schedules",
  "notify_board_templates",
  "owner_notification_templates",
  "announcements",
  // Depends on associations
  "units",
  "association_faqs",
  "association_fee_rules",
  "bank_accounts",
  "bids_quotes",
  "blocked_dates",
  "board_members",
  "board_votes",
  "budgets",
  "calendar_events",
  "call_logs",
  "chart_of_accounts",
  "checklists",
  "checks",
  "client_requests",
  "documents",
  "email_routing_rules",
  "email_senders",
  "email_server_settings",
  "estoppels",
  "inspections",
  "invoices",
  "legal_matters",
  "owner_update_tags",
  "parking_records",
  "payables",
  "payment_plans",
  "projects",
  "shared_folders",
  "status_updates",
  "tasks",
  "vendors",
  "violations",
  "violation_responses",
  "work_orders",
  // Depends on associations + owners/units
  "owners",
  "owner_updates",
  "owner_ledger_entries",
  "collections",
  "admin_payments",
  "arc_applications",
  "arc_application_comments",
  "arc_application_votes",
  "bank_reconciliations",
  "bank_transactions",
  "bank_transfers",
  "bill_approvals",
  "bill_comments",
  "billable_expenses",
  "bills",
  "client_invoices",
  "client_invoice_items",
  "deposit_batches",
  "deposit_batch_items",
  "document_validation_proofs",
  "email_history",
  "entity_comments",
  "entity_votes",
  "homeowner_requests",
  "in_app_notifications",
  "journal_entries",
  "owner_notification_proofs",
  // Auth-related
  "profiles",
  "user_roles",
  "role_permissions",
] as const;

type MappingRow = {
  mapping_type: "table" | "column" | "id_value";
  source_table: string | null;
  destination_table: string | null;
  source_field: string | null;
  destination_field: string | null;
  source_value: string | null;
  destination_value: string | null;
};

type ColumnMapping = {
  sourceField: string;
  destinationField: string;
};

type IdValueMapping = {
  sourceField: string;
  destinationField: string | null;
  sourceValue: string;
  destinationValue: string;
};

function isMissingValue(value: unknown) {
  return value === null || value === undefined || value === "";
}

function normalizeName(value: unknown) {
  return String(value || "")
    .trim()
    .toLowerCase()
    .replace(/\s+/g, " ");
}

function buildMappingIndexes(mappings: MappingRow[]) {
  const sourceTableByTargetTable = new Map<string, string>();
  const columnMappingsByTargetTable = new Map<string, ColumnMapping[]>();
  const idMappingsByTargetTable = new Map<string, IdValueMapping[]>();

  for (const mapping of mappings) {
    if (mapping.mapping_type === "table" && mapping.source_table && mapping.destination_table) {
      sourceTableByTargetTable.set(mapping.destination_table, mapping.source_table);
      continue;
    }

    if (
      mapping.mapping_type === "column" &&
      mapping.source_field &&
      mapping.destination_field
    ) {
      const tableKey = mapping.destination_table || mapping.source_table;
      if (!tableKey) continue;

      const existing = columnMappingsByTargetTable.get(tableKey) || [];
      existing.push({
        sourceField: mapping.source_field,
        destinationField: mapping.destination_field,
      });
      columnMappingsByTargetTable.set(tableKey, existing);
      continue;
    }

    if (
      mapping.mapping_type === "id_value" &&
      mapping.source_field &&
      mapping.source_value &&
      mapping.destination_value
    ) {
      const tableKey = mapping.destination_table || mapping.source_table;
      if (!tableKey) continue;

      const existing = idMappingsByTargetTable.get(tableKey) || [];
      existing.push({
        sourceField: mapping.source_field,
        destinationField: mapping.destination_field,
        sourceValue: mapping.source_value,
        destinationValue: mapping.destination_value,
      });
      idMappingsByTargetTable.set(tableKey, existing);
    }
  }

  return {
    sourceTableByTargetTable,
    columnMappingsByTargetTable,
    idMappingsByTargetTable,
  };
}

function applyIdValueMappings(
  row: Record<string, unknown>,
  mappings: IdValueMapping[],
): Record<string, unknown> {
  const transformed = { ...row };

  for (const mapping of mappings) {
    const currentValue = transformed[mapping.sourceField];
    if (String(currentValue ?? "") !== mapping.sourceValue) continue;

    const targetField = mapping.destinationField || mapping.sourceField;
    transformed[targetField] = mapping.destinationValue;
  }

  return transformed;
}

function applyColumnMappings(
  row: Record<string, unknown>,
  mappings: ColumnMapping[],
): Record<string, unknown> {
  const transformed = { ...row };

  for (const mapping of mappings) {
    if (!(mapping.sourceField in transformed)) continue;
    if (!(mapping.destinationField in transformed)) {
      transformed[mapping.destinationField] = transformed[mapping.sourceField];
    }
    delete transformed[mapping.sourceField];
  }

  return transformed;
}

function applyAssociationReferenceMappings(
  row: Record<string, unknown>,
  associationIdMap: Map<string, string>,
): Record<string, unknown> {
  if (associationIdMap.size === 0) return row;

  const transformed = { ...row };
  const mapValue = (value: unknown) => {
    if (typeof value === "string" && associationIdMap.has(value)) {
      return associationIdMap.get(value)!;
    }
    return value;
  };

  transformed.id = mapValue(transformed.id);
  transformed.association_id = mapValue(transformed.association_id);
  transformed.client_id = mapValue(transformed.client_id);

  if (Array.isArray(transformed.assigned_client_ids)) {
    transformed.assigned_client_ids = transformed.assigned_client_ids.map(mapValue);
  }

  return transformed;
}

function applyLegacyFallbacks(row: Record<string, unknown>): Record<string, unknown> {
  const transformed = { ...row };

  if (isMissingValue(transformed.association_id)) {
    if (!isMissingValue(transformed.client_id)) {
      transformed.association_id = transformed.client_id;
    } else if (
      Array.isArray(transformed.assigned_client_ids) &&
      transformed.assigned_client_ids.length === 1
    ) {
      transformed.association_id = transformed.assigned_client_ids[0];
    }
  }

  return transformed;
}

function stripUnknownColumns(
  row: Record<string, unknown>,
  validColumns: Set<string>,
): Record<string, unknown> {
  const cleaned: Record<string, unknown> = {};

  for (const key of Object.keys(row)) {
    if (validColumns.has(key)) {
      cleaned[key] = row[key];
    }
  }

  return cleaned;
}

async function getTargetColumns(targetClient: any, tableName: string) {
  try {
    const { data, error } = await targetClient.from(tableName).select("*").limit(1);
    if (error) {
      console.error(`[${tableName}] schema probe error:`, error.message);
      return null;
    }

    if (data && data.length > 0) {
      return new Set(Object.keys(data[0]));
    }

    return null;
  } catch {
    return null;
  }
}

async function buildAssociationIdMap(
  sourceClient: any,
  targetClient: any,
  sourceAssociationsTable: string,
) {
  try {
    let sourceRows: Array<{ id: string; name: string | null }> = [];
    let offset = 0;
    const limit = 1000;
    let fetching = true;

    while (fetching) {
      const { data, error } = await sourceClient
        .from(sourceAssociationsTable)
        .select("id, name")
        .range(offset, offset + limit - 1);

      if (error) {
        console.error("Failed to build association ID map from source:", error.message);
        return new Map<string, string>();
      }

      if (!data || data.length === 0) {
        fetching = false;
      } else {
        sourceRows = sourceRows.concat(data as Array<{ id: string; name: string | null }>);
        if (data.length < limit) fetching = false;
        offset += limit;
      }
    }

    const { data: targetRows, error: targetError } = await targetClient
      .from("associations")
      .select("id, name");

    if (targetError) {
      console.error("Failed to build association ID map from target:", targetError.message);
      return new Map<string, string>();
    }

    const targetIdByName = new Map<string, string>();
    for (const row of targetRows || []) {
      const key = normalizeName(row.name);
      if (key && !targetIdByName.has(key)) {
        targetIdByName.set(key, row.id);
      }
    }

    const associationIdMap = new Map<string, string>();
    for (const row of sourceRows) {
      const key = normalizeName(row.name);
      const matchingTargetId = targetIdByName.get(key);
      if (row.id && matchingTargetId && row.id !== matchingTargetId) {
        associationIdMap.set(row.id, matchingTargetId);
      }
    }

    return associationIdMap;
  } catch (error) {
    console.error("Failed to create association ID map:", error);
    return new Map<string, string>();
  }
}

async function upsertWithUnknownColumnRecovery(
  targetClient: any,
  tableName: string,
  rows: Record<string, unknown>[],
) {
  let workingRows = rows.map((row) => ({ ...row }));
  const strippedColumns = new Set<string>();

  for (let attempt = 0; attempt < 20; attempt++) {
    const { error } = await targetClient.from(tableName).upsert(workingRows, { onConflict: "id" });

    if (!error) {
      return {
        success: true,
        rows: workingRows,
        strippedColumns: [...strippedColumns],
      };
    }

    const unknownColumn = error.message?.match(/Could not find the '([^']+)' column/)?.[1];
    if (!unknownColumn) {
      return {
        success: false,
        rows: workingRows,
        strippedColumns: [...strippedColumns],
        error,
      };
    }

    strippedColumns.add(unknownColumn);
    workingRows = workingRows.map((row) => {
      const cleaned = { ...row };
      delete cleaned[unknownColumn];
      return cleaned;
    });
    console.log(`[${tableName}] stripping unknown column during upsert: ${unknownColumn}`);
  }

  return {
    success: false,
    rows: workingRows,
    strippedColumns: [...strippedColumns],
    error: { message: "Exceeded unknown-column retry limit" },
  };
}

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

  try {
    const authHeader = req.headers.get("Authorization");
    if (!authHeader) throw new Error("No authorization header");

    const targetUrl = Deno.env.get("SUPABASE_URL")!;
    const targetServiceKey = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!;
    const sourceUrl = Deno.env.get("SOURCE_SUPABASE_URL");
    const sourceServiceKey = Deno.env.get("SOURCE_SUPABASE_SERVICE_ROLE_KEY");

    if (!sourceUrl || !sourceServiceKey) {
      throw new Error("Source Supabase credentials not configured");
    }

    const targetClient = createClient(targetUrl, targetServiceKey);
    const token = authHeader.replace("Bearer ", "");

    const payloadBase64 = token.split(".")[1];
    if (!payloadBase64) throw new Error("Invalid token");
    const payload = JSON.parse(atob(payloadBase64));
    const userId = payload.sub;
    if (!userId) throw new Error("Unauthorized");

    const { data: roleData } = await targetClient
      .from("user_roles")
      .select("role")
      .eq("user_id", userId)
      .eq("role", "admin")
      .single();
    if (!roleData) throw new Error("Admin access required");

    const body = await req.json().catch(() => ({}));
    const action = body.action || "migrate_tables";
    const selectedTables = body.tables || MIGRATION_TABLES;

    const { data: mappingsData, error: mappingsError } = await targetClient
      .from("migration_field_mappings")
      .select(
        "mapping_type, source_table, destination_table, source_field, destination_field, source_value, destination_value",
      )
      .eq("is_active", true);

    if (mappingsError) {
      console.error("Failed to load migration mappings:", mappingsError.message);
    }

    const {
      sourceTableByTargetTable,
      columnMappingsByTargetTable,
      idMappingsByTargetTable,
    } = buildMappingIndexes((mappingsData || []) as MappingRow[]);

    const resolveSourceTable = (targetTable: string) => {
      return sourceTableByTargetTable.get(targetTable) || targetTable;
    };

    const sourceClient = createClient(sourceUrl, sourceServiceKey);
    const associationIdMap = await buildAssociationIdMap(
      sourceClient,
      targetClient,
      resolveSourceTable("associations"),
    );

    if (action === "list_source_tables") {
      const tableCounts: Record<string, number> = {};

      for (const targetTable of MIGRATION_TABLES) {
        const sourceTable = resolveSourceTable(targetTable);

        try {
          const { count } = await sourceClient
            .from(sourceTable)
            .select("*", { count: "exact", head: true });
          tableCounts[targetTable] = count ?? 0;
        } catch {
          tableCounts[targetTable] = -1;
        }
      }

      return new Response(JSON.stringify({ success: true, tables: tableCounts }), {
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      });
    }

    if (action === "import_users_with_passwords") {
      const usersList = body.users || [];
      if (!Array.isArray(usersList) || usersList.length === 0) {
        throw new Error("No users provided. Send an array of {email, encrypted_password, ...} objects.");
      }

      const results: { created: number; skipped: number; errors: string[] } = {
        created: 0,
        skipped: 0,
        errors: [],
      };

      for (const userData of usersList) {
        try {
          if (!userData.email) {
            results.errors.push("Row missing email field");
            continue;
          }

          const createPayload: Record<string, unknown> = {
            email: userData.email,
            email_confirm: true,
            user_metadata: userData.user_metadata || {},
          };

          if (userData.encrypted_password) {
            createPayload.password_hash = userData.encrypted_password;
          } else if (userData.password) {
            createPayload.password = userData.password;
          } else {
            createPayload.password = `Temp${crypto.randomUUID().slice(0, 8)}!`;
          }

          const { error: createErr } = await targetClient.auth.admin.createUser(createPayload);

          if (createErr) {
            if (createErr.message?.includes("already been registered")) {
              results.skipped++;
            } else {
              results.errors.push(`${userData.email}: ${createErr.message}`);
            }
            continue;
          }

          results.created++;
        } catch (e) {
          results.errors.push(`${userData.email || "unknown"}: ${(e as Error).message}`);
        }
      }

      return new Response(JSON.stringify({ success: true, users: results }), {
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      });
    }

    if (action === "migrate_users") {
      const results: { created: number; skipped: number; errors: string[] } = {
        created: 0,
        skipped: 0,
        errors: [],
      };

      let page = 1;
      const perPage = 100;
      let hasMore = true;

      while (hasMore) {
        const {
          data: { users },
          error,
        } = await sourceClient.auth.admin.listUsers({ page, perPage });

        if (error) {
          results.errors.push(`Failed to list users page ${page}: ${(error as Error).message}`);
          break;
        }

        if (!users || users.length === 0) {
          hasMore = false;
          break;
        }

        for (const srcUser of users) {
          try {
            const tempPassword = `Temp${crypto.randomUUID().slice(0, 8)}!`;
            const { error: createErr } = await targetClient.auth.admin.createUser({
              email: srcUser.email!,
              password: tempPassword,
              email_confirm: true,
              user_metadata: srcUser.user_metadata || {},
            });

            if (createErr) {
              if (createErr.message?.includes("already been registered")) {
                results.skipped++;
              } else {
                results.errors.push(`${srcUser.email}: ${createErr.message}`);
              }
              continue;
            }

            results.created++;
          } catch (e) {
            results.errors.push(`${srcUser.email}: ${(e as Error).message}`);
          }
        }

        if (users.length < perPage) hasMore = false;
        page++;
      }

      return new Response(JSON.stringify({ success: true, users: results }), {
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      });
    }

    if (action === "migrate_tables") {
      const results: Record<string, { inserted: number; skipped: number; error?: string }> = {};

      for (const requestedTable of selectedTables) {
        if (!MIGRATION_TABLES.includes(requestedTable)) continue;

        const targetTable = requestedTable;
        const sourceTable = resolveSourceTable(targetTable);
        const columnMappings = columnMappingsByTargetTable.get(targetTable) || [];
        const idMappings = idMappingsByTargetTable.get(targetTable) || [];

        try {
          let allRows: Record<string, unknown>[] = [];
          let offset = 0;
          const limit = 1000;
          let fetching = true;

          while (fetching) {
            const { data, error } = await sourceClient
              .from(sourceTable)
              .select("*")
              .range(offset, offset + limit - 1);

            if (error) {
              console.error(`[${targetTable}] fetch error from ${sourceTable} at offset ${offset}:`, error.message);
              throw error;
            }

            if (!data || data.length === 0) {
              fetching = false;
            } else {
              allRows = allRows.concat(data as Record<string, unknown>[]);
              if (data.length < limit) fetching = false;
              offset += limit;
            }
          }

          console.log(`[${targetTable}] fetched ${allRows.length} rows from source table ${sourceTable}`);

          if (allRows.length === 0) {
            results[targetTable] = { inserted: 0, skipped: 0 };
            continue;
          }

          let transformedRows = allRows.map((row) => {
            let transformed = applyIdValueMappings(row, idMappings);
            transformed = applyAssociationReferenceMappings(transformed, associationIdMap);
            transformed = applyColumnMappings(transformed, columnMappings);
            transformed = applyLegacyFallbacks(transformed);
            transformed = applyAssociationReferenceMappings(transformed, associationIdMap);
            return transformed;
          });

          const validColumns = await getTargetColumns(targetClient, targetTable);
          if (validColumns) {
            const strippedColumns = new Set<string>();
            transformedRows = transformedRows.map((row) => {
              for (const key of Object.keys(row)) {
                if (!validColumns.has(key)) strippedColumns.add(key);
              }
              return stripUnknownColumns(row, validColumns);
            });

            if (strippedColumns.size > 0) {
              console.log(`[${targetTable}] stripped columns not in target: ${[...strippedColumns].join(", ")}`);
            }
          }

          let inserted = 0;
          let skipped = 0;
          const batchSize = 100;

          for (let i = 0; i < transformedRows.length; i += batchSize) {
            const batch = transformedRows.slice(i, i + batchSize);
            const batchResult = await upsertWithUnknownColumnRecovery(targetClient, targetTable, batch);

            if (!batchResult.success) {
              console.error(
                `[${targetTable}] batch upsert error at offset ${i}:`,
                batchResult.error?.message,
                batchResult.error?.details,
                batchResult.error?.hint,
              );

              for (const row of batchResult.rows) {
                const singleResult = await upsertWithUnknownColumnRecovery(targetClient, targetTable, [row]);
                if (!singleResult.success) {
                  console.error(`[${targetTable}] single row error:`, singleResult.error?.message);
                  skipped++;
                } else {
                  inserted++;
                }
              }
            } else {
              if (batchResult.strippedColumns.length > 0) {
                console.log(`[${targetTable}] stripped during upsert: ${batchResult.strippedColumns.join(", ")}`);
              }
              inserted += batchResult.rows.length;
            }
          }

          console.log(`[${targetTable}] result: ${inserted} inserted/updated, ${skipped} skipped`);
          results[targetTable] = { inserted, skipped };
        } catch (e) {
          console.error(`[${targetTable}] fatal error:`, (e as Error).message);
          results[targetTable] = { inserted: 0, skipped: 0, error: (e as Error).message };
        }
      }

      return new Response(JSON.stringify({ success: true, results }), {
        headers: { ...corsHeaders, "Content-Type": "application/json" },
      });
    }

    throw new Error(`Unknown action: ${action}`);
  } catch (error) {
    console.error("Migration error:", error);
    return new Response(
      JSON.stringify({ success: false, error: (error as Error).message }),
      { status: 400, headers: { ...corsHeaders, "Content-Type": "application/json" } },
    );
  }
});