/// <reference path="../pb_data/types.d.ts" />
const XLSX = require('xlsx');
const axios = require('axios');

// ================================================
// CONFIG — ONLY EDIT THESE VALUES
// ================================================
const EXCEL_FILE_URL = "https://czflex.sharepoint.com/:x:/r/sites/Team/Shared%20Documents/General/JOBS%201000-9999.xlsx?d=wc20d9b5023f84403b4d345ca88382eb4&csf=1&web=1&e=aNwcPv";
const EXCEL_SHEET_NAME = "Job Sheet";

const AZURE_TENANT_ID = "3fd97ea7-b124-41f1-855f-52d8ac3b16c7";
const AZURE_CLIENT_ID = "3c846e71-9609-40e1-b458-0eb805e21b9f";
const AZURE_CLIENT_SECRET = "lG~8Q~kKp5nyc-SK8THNE4ZLTdp81zv_gVm2jdm~"; // Store in Settings → Secret keys (see below)

// Run every 5 minutes
routerAdd("GET", "/_cron/sync-excel", (c) => {
  const correlationId = $security.randomString(8);
  console.log(`[${new Date().toISOString()}] Sync started (${correlationId})`);

  // Use secret storage (secure!)
  const clientSecret = $app.settings().meta.appSecret || AZURE_CLIENT_SECRET;

  // ================================================
  // 1. Get OAuth 2.0 token (client credentials flow)
  // ================================================
  $os.exec("curl", "-X", "POST",
    `https://login.microsoftonline.com/${AZURE_TENANT_ID}/oauth2/v2.0/token`,
    "-d", `client_id=${AZURE_CLIENT_ID}`,
    "-d", `client_secret=${clientSecret}`,
    "-d", "grant_type=client_credentials",
    "-d", "scope=https://graph.microsoft.com/.default"
  ).then(async (tokenRes) => {
    const tokenData = JSON.parse(tokenRes.stdout);
    const accessToken = tokenData.access_token;

    // ================================================
    // 2. Download Excel file
    // ================================================
    const fileRes = await $http.send({
      url: EXCEL_FILE_URL + "?:@microsoft.graph.downloadUrl",
      method: "GET",
      headers: { Authorization: `Bearer ${accessToken}` }
    });

    if (fileRes.statusCode !== 200) {
      console.error("Failed to download Excel:", fileRes.raw);
      return c.json(500, { error: "Download failed" });
    }

    const workbook = XLSX.read(fileRes.raw, { type: "buffer", cellDates: true });
    const sheet = workbook.Sheets[EXCEL_SHEET_NAME];
    if (!sheet) return console.error("Sheet not found");

    const range = XLSX.utils.decode_range(sheet["!ref"]);
    const merges = sheet["!merges"] || [];

    const getValue = (r, c) => {
      const addr = XLSX.utils.encode_cell({ r, c });
      const cell = sheet[addr];
      if (cell) return cell.v ?? cell.w ?? null;
      for (const m of merges) {
        if (m.s.r <= r && r <= m.e.r && m.s.c <= c && c <= m.e.c) {
          const master = sheet[XLSX.utils.encode_cell({ r: m.s.r, c: m.s.c })];
          return master ? (master.v ?? master.w ?? null) : null;
        }
      }
      return null;
    };

    // ================================================
    // 3. Parse headers & data (same as browser tool)
    // ================================================
    const HEADER_ROW = 2;
    const DATA_START = 3;

    const rawHeaders = [];
    for (let c = range.s.c; c <= range.e.c; c++) {
      let h = getValue(HEADER_ROW, c);
      rawHeaders.push(h ? String(h).trim() : '');
    }

    const HEADER_MAP = {
      "Job_Number": "Job_Number",
      "Job_Full_Name": "Job_Full_Name",
      "Job_Name": "Job_Name",
      "Job Address": "Job_Address",
      "Job Type": "Job_Type",
      "Flag": "Flag",
      "Job Status": "Job_Status",
      "Job Division": "Division",
      "Estimator": "Estimator",
      "Office Rep": "Office_Rep",
      "Manager": "Project_Manager",
      "Company/Client": "Company_Client",
      "Contact Person": "Contact_Person",
      "Phone Number": "Phone",
      "Email": "Email",
      "Due Date": "Due_Date",
      "Due Date Source": "Due_Date_Source",
      "Due Date Counter": "Due_Date_Counter",
      "Due Time": "Due_Time",
      "Docs Uploaded": "Docs_Uploaded",
      "QB Created": "QB_Created",
      "Added to Calendar": "Calendar_Added",
      "PSwift Uploaded": "PSwift_Uploaded",
      "Voxer Created": "Voxer_Created",
      "Estimate Reviewed": "Estimate_Reviewed",
      "Estimate Draft": "Estimate_Draft",
      "Estimate Approved": "Estimate_Approved",
      "Start Date": "Start_Date",
      "Schedule\nConfidence": "Schedule_Confidence",
      "Notes": "Notes",
      "Job QB Link": "Job_QB_Link",
      "Voxer Link": "Voxer_Link",
      "Job_Codes": "Job_Codes",
      "Submission Date": "Submission_Date",
      "Active": "Active",
      "Job Folder Link": "Job_Folder_Link",
      "Tax Exempt": "Tax_Exempt",
      "pb_id": "id"
    };

    const rows = [];
    for (let r = DATA_START; r <= range.e.r; r++) {
      const row = { id: null, Notes: null, Job_Number: null };
      let hasData = false;

      for (let c = range.s.c; c <= range.e.c; c++) {
        const excelHeader = rawHeaders[c - range.s.c];
        const pbField = HEADER_MAP[excelHeader];
        if (!pbField) continue;

        let val = getValue(r, c);
        if (val !== null && val !== undefined) {
          const s = String(val).trim();
          if (s === '' || /^n\/?a$/i.test(s) || s === '#N/A') val = null;
        }

        if (pbField === "id" && excelHeader === "pb_id") row.id = val;
        else if (pbField === "Notes") row.Notes = val;
        else if (pbField === "Job_Number") row.Job_Number = val;

        row[pbField] = val;
        if (val !== null && val !== '') hasData = true;
      }

      if (hasData) rows.push(row);
    }

    // ================================================
    // 4. Full Sync Excel → PocketBase
    // ================================================
    let created = 0, updated = 0;
    for (const row of rows) {
      try {
        if (row.id) {
          // Update existing
          const cleanRow = { ...row };
          delete cleanRow.id;
          $app.dao().runInTransaction((txDao) => {
            txDao.saveRecord(txDao.findRecordById("Job_Info_TestEnv", row.id, cleanRow));
          });
          updated++;
        } else {
          // Create new
          const record = $app.dao().newRecord("Job_Info_TestEnv");
          Object.assign(record, row);
          $app.dao().saveRecord(record);
          created++;
        }
      } catch (err) {
        console.error(`Row error (Job_Number ${row.Job_Number}):`, err.message);
      }
    }

    // ================================================
    // 5. Bidirectional Notes Sync (PB → Excel if PB is newer)
    // ================================================
    const pbRecords = arrayOf(new Record({
      collection: $app.dao().findCollectionByNameOrId("Job_Info_TestEnv")
    })).fill($app.dao().findRecordsByExpr("Job_Info_TestEnv", DB().and()));

    for (const pbRec of pbRecords) {
      const excelMatch = rows.find(r => r.Job_Number === pbRec.getString("Job_Number"));
      if (!excelMatch) continue;

      const pbNotes = pbRec.getString("Notes") || "";
      const excelNotes = excelMatch.Notes || "";

      if (pbNotes !== excelNotes && pbRec.getDate("updated") > new Date()) {
        // Push PB → Excel
        console.log(`Pushing Notes to Excel for ${pbRec.getString("Job_Number")}`);
        // Future: Use Graph workbook API to update cell
        // For now: just log (add Graph PATCH later)
      }
    }

    console.log(`Sync complete: ${created} created, ${updated} updated`);
    return c.json(200, { status: "success", created, updated });
  }).catch(err => {
    console.error("Sync failed:", err);
    return c.json(500, { error: err.message });
  });
}, null, { cron: "*/5 * * * *" }); // Every 5 minutes