// ═══════════════════════════════════════════════════════════════
// backend/services/googleSheets.js
// NordSafe EHS — Google Sheets API Service
// ───────────────────────────────────────────────────────────────
// هذا الملف هو الجسر بين NordSafe وGoogle Sheets
// أنشئ مجلد services في backend/ وضع هذا الملف فيه
// Google Sheets = بديل عن SharePoint Lists للزبائن على Google Workspace
// ═══════════════════════════════════════════════════════════════
const fetch = require("node-fetch");

const SHEETS_BASE = "https://sheets.googleapis.com/v4/spreadsheets";
const DRIVE_BASE = "https://www.googleapis.com/drive/v3";

// ─── Helper: أرسل أي طلب لـ Google API ────────────────────────
const gapi = async (accessToken, method, url, data = null) => {
  const m = String(method).toUpperCase();
  const opts = {
    method: m,
    headers: {
      Authorization: `Bearer ${accessToken}`,
      "Content-Type": "application/json",
    },
  };
  if (data != null && m !== "GET" && m !== "DELETE") {
    opts.body = JSON.stringify(data);
  }

  const res = await fetch(url, opts);
  const text = await res.text();

  if (!res.ok) {
    const err = new Error(`Google API ${res.status}`);
    try {
      err.response = { data: text ? JSON.parse(text) : text };
    } catch {
      err.response = { data: text };
    }
    throw err;
  }

  if (!text) return {};
  try {
    return JSON.parse(text);
  } catch {
    return text;
  }
};

// ─── تجديد Access Token ────────────────────────────────────────
const refreshAccessToken = async (refreshToken) => {
  const body = new URLSearchParams({
    client_id: process.env.GOOGLE_CLIENT_ID,
    client_secret: process.env.GOOGLE_CLIENT_SECRET,
    refresh_token: refreshToken,
    grant_type: "refresh_token",
  }).toString();

  const res = await fetch("https://oauth2.googleapis.com/token", {
    method: "POST",
    headers: { "Content-Type": "application/x-www-form-urlencoded" },
    body,
  });

  const json = await res.json().catch(() => ({}));
  if (!res.ok) {
    throw new Error(json.error_description || json.error || `Google token refresh failed: ${res.status}`);
  }

  return {
    accessToken: json.access_token,
    // Google لا يعطي refresh_token جديد عادةً — احتفظ بالقديم
    refreshToken: refreshToken,
  };
};

// ─── إنشاء Spreadsheet جديد للشركة (= CREATE DATABASE) ────────
// يُستدعى مرة واحدة فقط عند onboarding الزبون
const createCompanySpreadsheet = async (accessToken, companyName) => {
  const sheets = [
    "Incidents",
    "Deviations",
    "RiskAssessments",
    "Permits",
    "SiteMonitoring",
    "JSA",
    "ToolboxMeetings",
    "Checklists",
    "Equipment",
    "SDS",
  ];

  // إنشاء الـ Spreadsheet مع كل الـ sheets دفعة واحدة
  const result = await gapi(accessToken, "post", SHEETS_BASE, {
    properties: {
      title: `NordSafe EHS — ${companyName}`,
      locale: "en_US",
      timeZone: "Asia/Riyadh",
    },
    sheets: sheets.map((name, i) => ({
      properties: {
        sheetId: i,
        title: name,
        index: i,
        gridProperties: { rowCount: 10000, columnCount: 20 },
      },
    })),
  });

  const spreadsheetId = result.spreadsheetId;

  // أضف headers لكل sheet
  const headerMap = {
    Incidents: ["ID", "Title", "Status", "Severity", "Location", "Description", "ReportedBy", "IncidentDate", "NordSafeId", "CreatedAt"],
    Deviations: ["ID", "Title", "Status", "Category", "Location", "Description", "ReportedBy", "DeviationDate", "NordSafeId", "CreatedAt"],
    RiskAssessments: ["ID", "Title", "Status", "RiskLevel", "Location", "Description", "AssessedBy", "ReviewDate", "NordSafeId", "CreatedAt"],
    Permits: ["ID", "Title", "Status", "PermitType", "Location", "Description", "RequestedBy", "ValidUntil", "NordSafeId", "CreatedAt"],
    SiteMonitoring: ["ID", "Title", "Status", "InspectionType", "Location", "Findings", "InspectedBy", "InspectionDate", "NordSafeId", "CreatedAt"],
    JSA: ["ID", "Title", "Status", "TaskActivity", "Location", "Description", "PreparedBy", "NordSafeId", "CreatedAt"],
    ToolboxMeetings: ["ID", "Title", "Topic", "Location", "Participants", "KeyPoints", "ConductedBy", "MeetingDate", "NordSafeId", "CreatedAt"],
    Checklists: ["ID", "Title", "Type", "Location", "Result", "Notes", "CompletedBy", "Date", "NordSafeId", "CreatedAt"],
    Equipment: ["ID", "Equipment", "Status", "Location", "InspectionDate", "NextInspection", "ResponsiblePerson", "NordSafeId", "CreatedAt"],
    SDS: ["ID", "ProductName", "Manufacturer", "HazardLevel", "Location", "NordSafeId", "CreatedAt"],
  };

  // كتابة headers في الـ row الأول لكل sheet
  const requests = Object.entries(headerMap).map(([sheetName, headers]) => ({
    range: `${sheetName}!A1:${String.fromCharCode(64 + headers.length)}1`,
    majorDimension: "ROWS",
    values: [headers],
  }));

  await gapi(accessToken, "post", `${SHEETS_BASE}/${spreadsheetId}/values:batchUpdate`, {
    valueInputOption: "RAW",
    data: requests,
  });

  // Format headers — Bold + Background
  const formatRequests = sheets.map((_, i) => ({
    repeatCell: {
      range: { sheetId: i, startRowIndex: 0, endRowIndex: 1 },
      cell: {
        userEnteredFormat: {
          backgroundColor: { red: 0.09, green: 0.18, blue: 0.31 },
          textFormat: { foregroundColor: { red: 1, green: 1, blue: 1 }, bold: true, fontSize: 10 },
          horizontalAlignment: "CENTER",
        },
      },
      fields: "userEnteredFormat",
    },
  }));

  await gapi(accessToken, "post", `${SHEETS_BASE}/${spreadsheetId}:batchUpdate`, {
    requests: formatRequests,
  });

  console.log(`✅ Google Spreadsheet created: ${spreadsheetId}`);
  return { spreadsheetId, url: `https://docs.google.com/spreadsheets/d/${spreadsheetId}` };
};

// ─── إضافة row (= INSERT INTO) ────────────────────────────────
const appendRow = async (accessToken, spreadsheetId, sheetName, values) => {
  return await gapi(
    accessToken,
    "post",
    `${SHEETS_BASE}/${spreadsheetId}/values/${sheetName}:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS`,
    { values: [values], majorDimension: "ROWS" },
  );
};

// ─── جلب كل rows (= SELECT *) ─────────────────────────────────
const getRows = async (accessToken, spreadsheetId, sheetName) => {
  const res = await gapi(accessToken, "get", `${SHEETS_BASE}/${spreadsheetId}/values/${sheetName}`);

  const rows = res.values || [];
  if (rows.length < 2) return []; // فقط headers أو فاضي

  const headers = rows[0];
  return rows.slice(1).map((row, index) => {
    const obj = { _rowIndex: index + 2 }; // +2 لأن headers في row 1
    headers.forEach((h, i) => {
      obj[h] = row[i] || "";
    });
    return obj;
  });
};

// ─── تحديث row (= UPDATE) ─────────────────────────────────────
// rowIndex = رقم الـ row الفعلي في الـ sheet (يبدأ من 2)
const updateRow = async (accessToken, spreadsheetId, sheetName, rowIndex, headers, values) => {
  const endCol = String.fromCharCode(64 + headers.length);
  return await gapi(
    accessToken,
    "put",
    `${SHEETS_BASE}/${spreadsheetId}/values/${sheetName}!A${rowIndex}:${endCol}${rowIndex}?valueInputOption=USER_ENTERED`,
    { values: [values], majorDimension: "ROWS" },
  );
};

// ─── حذف row (= DELETE) ───────────────────────────────────────
// Google Sheets لا يحذف rows — يمسح محتواها (أو نستخدم batchUpdate)
const deleteRow = async (accessToken, spreadsheetId, sheetId, rowIndex) => {
  return await gapi(accessToken, "post", `${SHEETS_BASE}/${spreadsheetId}:batchUpdate`, {
    requests: [
      {
        deleteDimension: {
          range: {
            sheetId,
            dimension: "ROWS",
            startIndex: rowIndex - 1, // 0-based
            endIndex: rowIndex,
          },
        },
      },
    ],
  });
};

// ─── البحث في rows (= SELECT WHERE) ──────────────────────────
// Google Sheets لا يدعم SQL — نفلتر في Node
const findRows = async (accessToken, spreadsheetId, sheetName, filterFn) => {
  const rows = await getRows(accessToken, spreadsheetId, sheetName);
  return rows.filter(filterFn);
};

// ─── جلب معلومات الـ Spreadsheet ─────────────────────────────
const getSpreadsheetInfo = async (accessToken, spreadsheetId) => {
  return await gapi(accessToken, "get", `${SHEETS_BASE}/${spreadsheetId}?fields=spreadsheetId,properties.title,sheets.properties`);
};

module.exports = {
  gapi,
  refreshAccessToken,
  createCompanySpreadsheet,
  appendRow,
  getRows,
  updateRow,
  deleteRow,
  findRows,
  getSpreadsheetInfo,
};
