const DB_NAME = "LoginAnalyticsDB";
const STORE_NAME = "logins";

const REQUIRED_KEYS = [
  "email",
  "LoginTime",
  "UpdatedTime",
  "revoked",
  "expiry",
  "ipaddress",
  "regionName",
];

function parseExcelDate(excelValue) {
  if (typeof excelValue === "number") {
    return new Date(Math.round((excelValue - 25569) * 86400 * 1000));
  }
  return new Date(excelValue);
}

function toUtcFormatted(dateStrOrNum) {
  const date = parseExcelDate(dateStrOrNum);
  if (isNaN(date)) return "";
  const y = date.getUTCFullYear();
  const m = String(date.getUTCMonth() + 1).padStart(2, "0");
  const d = String(date.getUTCDate()).padStart(2, "0");
  const h = String(date.getUTCHours()).padStart(2, "0");
  const min = String(date.getUTCMinutes()).padStart(2, "0");
  const s = String(date.getUTCSeconds()).padStart(2, "0");
  return `${y}-${m}-${d} ${h}:${min}:${s}`;
}

function addMinutesUTC(dateStrOrNum, minutes) {
  const base = parseExcelDate(dateStrOrNum);
  if (isNaN(base)) return "";
  const future = new Date(base.getTime() + minutes * 60000);
  return toUtcFormatted(future);
}

function openDB(callback) {
  const request = indexedDB.open(DB_NAME, 1);

  request.onupgradeneeded = function (event) {
    const db = event.target.result;

    // ✅ Create object store *once* during upgrade
    if (!db.objectStoreNames.contains(STORE_NAME)) {
      const store = db.createObjectStore(STORE_NAME, {
        keyPath: "id",
        autoIncrement: true,
      });
      store.createIndex("email", "email", { unique: false });
      store.createIndex("ipaddress", "ipaddress", { unique: false });
      store.createIndex("revoked", "revoked", { unique: false });
    }

    // console.log("🆕 Object store created during upgrade.");
  };

  request.onsuccess = function (event) {
    const db = event.target.result;

    // ✅ Wait for version upgrade transactions to complete
    if (db.objectStoreNames.contains(STORE_NAME)) {
      callback(db);
    } else {
      // console.error("❌ Object store not found after opening DB.");
    }
  };

  request.onerror = function (event) {
    console.error("❌ Failed to open IndexedDB:", event.target.errorCode);
  };
}

function importExcelFromPath(filepath) {
  fetch(filepath)
    .then((res) => res.arrayBuffer())
    .then((arrayBuffer) => {
      const data = new Uint8Array(arrayBuffer);
      const workbook = XLSX.read(data, { type: "array" });

      const sheet = workbook.Sheets[workbook.SheetNames[0]];
      const rawRows = XLSX.utils.sheet_to_json(sheet, { defval: "" });

      const cleanedRows = rawRows.map((row) => {
        const cleaned = { clientid: "digital-id" };

        REQUIRED_KEYS.forEach((key) => {
          let value = row.hasOwnProperty(key) ? row[key] : "";

          if (key === "LoginTime" || key === "UpdatedTime") {
            value = toUtcFormatted(value);
          } else if (key === "revoked") {
            value = parseInt(value) === 1 ? 1 : null;
          } else if (key === "expiry") {
            value = addMinutesUTC(row["LoginTime"], 30);
          }

          cleaned[key] = value;
        });

        return cleaned;
      });

      openDB((db) => {
        const tx = db.transaction(STORE_NAME, "readwrite");
        const store = tx.objectStore(STORE_NAME);
        cleanedRows.forEach((item) => {
          store.add(item);
        });

        tx.oncomplete = () =>
          // console.log(`✅ Imported ${cleanedRows.length} rows into IndexedDB.`);
          (tx.onerror = (err) =>
            console.error("❌ Error saving to IndexedDB:", err));
      });
    })
    .catch((err) => {
      console.error("❌ Failed to load Excel file:", err);
    });
}

window.addEventListener("DOMContentLoaded", () => {
  importExcelFromPath("./logins.xlsx"); // relative path
});