// bulkInsertStudents.js
const fs = require("fs");
const path = require("path");
const XLSX = require("xlsx");
const { v4: uuidv4 } = require("uuid");
const db = require("./config/db"); // your database connection

(async () => {
  try {
    // Path to your Excel file (same folder)
    const filePath = path.join(__dirname, "students11.xlsx");

    if (!fs.existsSync(filePath)) {
      console.error("Excel file not found:", filePath);
      process.exit(1);
    }

    // Read Excel
    const workbook = XLSX.readFile(filePath);
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const rows = XLSX.utils.sheet_to_json(sheet);

    if (!rows.length) {
      console.log("Excel file is empty");
      process.exit(0);
    }

    // Start transaction
    const connection = await db.getConnection();
    await connection.beginTransaction();

    const inserted = [];
    const skipped = [];

    for (const row of rows) {
      const institution_id = row.institution_id;
      const name = row.name?.toString().trim();
      const grade_id = row.grade_id;
      const mobile = row.mobile?.toString().trim() || null;

      if (!institution_id || !name || !grade_id) {
        skipped.push({ row, reason: "Missing required fields" });
        continue;
      }

      const qr_id = uuidv4();
      const created_at = new Date(); // current timestamp

      try {
        await connection.query(
          `INSERT INTO students 
            (institution_id, name, grade_id, mobile_number, qr_id, status, created_at)
           VALUES (?, ?, ?, ?, ?, 'active', ?)`,
          [institution_id, name, grade_id, mobile, qr_id, created_at]
        );

        inserted.push(name);
      } catch (err) {
        skipped.push({ row, reason: err.message });
      }
    }

    await connection.commit();
    connection.release();

    console.log("Bulk insert completed!");
    console.log("Inserted:", inserted.length);
    console.log("Skipped:", skipped.length);
    if (skipped.length > 0) console.log(skipped);
  } catch (error) {
    console.error("Bulk insert failed:", error);
  }
})();
