const [spreadsheetId, outputPath, rangesArg = ""] = process.argv.slice(2);

if (!spreadsheetId || !outputPath) {
  console.error("Usage: spreadsheetId outputPath [ranges]");
  process.exit(1);
}

import fs from "fs";
import path from "path";

// Token placeholder - replaced at runtime by Sauna
const TOKEN = "GOOGLE_SHEETS_TOKEN";
const BASE_URL = "https://sheets.googleapis.com/v4/spreadsheets";

async function fetchWithAuth(url) {
  const response = await fetch(url, {
    headers: { Authorization: `Bearer ${TOKEN}` },
  });

  if (!response.ok) {
    const error = await response.text();
    throw new Error(`API error ${response.status}: ${error}`);
  }

  return response.json();
}

// Extract hyperlink from a cell (checks both direct hyperlinks and HYPERLINK formulas)
function extractHyperlink(cell) {
  if (!cell) return null;

  // Check direct hyperlink first
  if (cell.hyperlink) {
    return {
      url: cell.hyperlink,
      text: cell.formattedValue || cell.hyperlink,
    };
  }

  // Check HYPERLINK formula
  const formula = cell.userEnteredValue?.formulaValue || "";
  const match = formula.match(/=HYPERLINK\("([^"]+)"(?:,\s*"([^"]*)")?\)/i);
  if (match) {
    return {
      url: match[1],
      text: match[2] || cell.formattedValue || match[1],
    };
  }

  return null;
}

// Transform API response into a cleaner structure
function transformSheetData(apiResponse, sheetNames) {
  const result = {
    spreadsheetId,
    title: apiResponse.properties?.title || "Unknown",
    sheets: [],
  };

  if (!apiResponse.sheets) return result;

  for (let i = 0; i < apiResponse.sheets.length; i++) {
    const sheet = apiResponse.sheets[i];
    const sheetName = sheetNames[i] || `Sheet${i + 1}`;
    const sheetData = {
      name: sheetName,
      rows: [],
    };

    // Process row data
    const data = sheet.data?.[0];
    if (data?.rowData) {
      for (let rowIdx = 0; rowIdx < data.rowData.length; rowIdx++) {
        const row = data.rowData[rowIdx];
        if (!row.values) continue;

        const cells = [];
        let hasContent = false;

        for (let colIdx = 0; colIdx < row.values.length; colIdx++) {
          const cell = row.values[colIdx];
          const value = cell?.formattedValue || null;
          const hyperlink = extractHyperlink(cell);

          if (value || hyperlink) hasContent = true;

          cells.push({
            column: colIdx,
            value,
            hyperlink: hyperlink?.url || null,
            hyperlinkText: hyperlink?.text || null,
          });
        }

        // Only include rows with content
        if (hasContent) {
          sheetData.rows.push({
            row: rowIdx + 1, // 1-indexed for user reference
            cells: cells.filter((c) => c.value || c.hyperlink),
          });
        }
      }
    }

    result.sheets.push(sheetData);
  }

  return result;
}

try {
  console.log(`Reading spreadsheet: ${spreadsheetId}`);

  // First, get spreadsheet metadata to know sheet names
  const metaUrl = `${BASE_URL}/${spreadsheetId}?fields=properties.title,sheets.properties.title`;
  const meta = await fetchWithAuth(metaUrl);

  const spreadsheetTitle = meta.properties?.title || "Unknown";
  const allSheetNames = meta.sheets?.map((s) => s.properties.title) || [];

  console.log(`  Spreadsheet: ${spreadsheetTitle}`);
  console.log(`  Sheets: ${allSheetNames.join(", ")}`);

  // Determine which ranges to fetch
  let rangesToFetch = [];
  if (rangesArg && rangesArg.trim()) {
    rangesToFetch = rangesArg.split(",").map((r) => r.trim());
  } else {
    // Fetch all sheets
    rangesToFetch = allSheetNames;
  }

  // Build URL with ranges and fields for hyperlinks
  const fields =
    "properties.title,sheets.data.rowData.values(formattedValue,hyperlink,userEnteredValue)";
  const rangeParams = rangesToFetch
    .map((r) => `ranges=${encodeURIComponent(r)}`)
    .join("&");

  const dataUrl = `${BASE_URL}/${spreadsheetId}?${rangeParams}&fields=${encodeURIComponent(fields)}`;
  const apiResponse = await fetchWithAuth(dataUrl);

  // Transform to cleaner structure
  const result = transformSheetData(apiResponse, rangesToFetch);

  // Count totals
  let totalRows = 0;
  let totalHyperlinks = 0;
  for (const sheet of result.sheets) {
    totalRows += sheet.rows.length;
    for (const row of sheet.rows) {
      for (const cell of row.cells) {
        if (cell.hyperlink) totalHyperlinks++;
      }
    }
  }

  console.log(`  Total rows with data: ${totalRows}`);
  console.log(`  Hyperlinks found: ${totalHyperlinks}`);

  // Ensure output directory exists
  const dir = path.dirname(outputPath);
  if (dir && dir !== ".") {
    fs.mkdirSync(dir, { recursive: true });
  }

  fs.writeFileSync(outputPath, JSON.stringify(result, null, 2));
  console.log(`  Written to: ${outputPath}`);

  // Output summary for agent
  console.log(
    JSON.stringify({
      success: true,
      spreadsheetId,
      title: spreadsheetTitle,
      outputPath,
      sheetsRead: result.sheets.length,
      totalRows,
      totalHyperlinks,
      sheetNames: result.sheets.map((s) => s.name),
    })
  );
} catch (error) {
  console.error(`Error: ${error.message}`);
  process.exit(1);
}