// /pages/api/cron/stock_avex.js
import { db } from "@/helpers/api";
import fs from "fs";
import path from "path";

const CSV_URL =
     "https://export.avex.ro/wp-load.php?security_token=016d0da95f890dba&export_id=3&action=get_data";

const BATCH_SIZE = 100;

function writeLog(entry) {
     try {
          const logDir = path.join(process.cwd(), "logs", "cron", "stock_avex");
          fs.mkdirSync(logDir, { recursive: true });

          const date = new Date().toISOString().slice(0, 10); // "2026-02-17"
          const logFile = path.join(logDir, `${date}.jsonl`);

          const line = JSON.stringify({
               time: new Date().toISOString(),
               ...entry,
          });

          fs.appendFileSync(logFile, line + "\n", "utf8");
     } catch (err) {
          console.error("[stock_avex] Eroare la scriere log:", err.message);
     }
}

function parseCSV(raw) {
     const lines = raw.trim().split("\n");
     if (lines.length < 2) return [];

     const headers = splitCSVLine(lines[0]);
     const codIdx = headers.indexOf("COD");
     const stocIdx = headers.indexOf("STOC");

     if (codIdx === -1 || stocIdx === -1) {
          throw new Error(
               `Header invalid — COD=${codIdx}, STOC=${stocIdx}. Headers găsite: ${headers.join(", ")}`
          );
     }

     const result = [];
     for (let i = 1; i < lines.length; i++) {
          const line = lines[i].trim();
          if (!line) continue;

          const cols = splitCSVLine(line);
          const sku = cols[codIdx]?.trim();
          const stockRaw = cols[stocIdx]?.trim();
          const stock = parseInt(stockRaw, 10);

          if (sku && !isNaN(stock)) {
               result.push({ sku, stock });
          }
     }

     return result;
}

function splitCSVLine(line) {
     const result = [];
     let current = "";
     let inQuotes = false;

     for (let i = 0; i < line.length; i++) {
          const ch = line[i];
          if (ch === '"') {
               inQuotes = !inQuotes;
          } else if (ch === "," && !inQuotes) {
               result.push(current);
               current = "";
          } else {
               current += ch;
          }
     }
     result.push(current);
     return result;
}

function chunkArray(arr, size) {
     const chunks = [];
     for (let i = 0; i < arr.length; i += size) {
          chunks.push(arr.slice(i, i + size));
     }
     return chunks;
}

async function getExistingSkus(skus) {
     const found = await db.Products.findAll({
          attributes: ["sku"],
          where: { sku: skus },
          raw: true,
     });
     return new Set(found.map((p) => p.sku));
}

async function updateBatch(batch) {
     if (!batch.length) return 0;

     const caseLines = batch
          .map((row) => `WHEN '${escapeSql(row.sku)}' THEN ${row.stock}`)
          .join("\n               ");

     const inList = batch
          .map((row) => `'${escapeSql(row.sku)}'`)
          .join(", ");

     const sql = `
          UPDATE products
          SET stock = CASE sku
               ${caseLines}
               ELSE stock
          END
          WHERE sku IN (${inList})
     `;

     const [, meta] = await db.Products.sequelize.query(sql);
     return meta?.affectedRows ?? 0;
}

function escapeSql(str) {
     return str.replace(/\\/g, "\\\\").replace(/'/g, "\\'");
}

export default async function handler(req, res) {
     if (req.method !== "GET" && req.method !== "POST") {
          return res.status(405).json({ error: "Method not allowed" });
     }

     // Opțional: protecție endpoint cu secret
     // const { authorization } = req.headers;
     // if (authorization !== `Bearer ${process.env.CRON_SECRET}`) {
     //      return res.status(401).json({ error: "Unauthorized" });
     // }

     if (!db.initialized) {
          await db.initialize();
     }

     const startTime = Date.now();

     try {
          const fetchResp = await fetch(CSV_URL);
          if (!fetchResp.ok) {
               throw new Error(
                    `Fetch CSV eșuat: ${fetchResp.status} ${fetchResp.statusText}`
               );
          }
          const rawCSV = await fetchResp.text();

          const rows = parseCSV(rawCSV);
          if (!rows.length) {
               const entry = {
                    status: "empty_csv",
                    csv_rows: 0,
                    updated: 0,
                    not_in_site_count: 0,
                    skus_missing: [],
                    duration_ms: Date.now() - startTime,
               };
               writeLog(entry);
               return res.status(200).json(entry);
          }

          const allCsvSkus = rows.map((r) => r.sku);

          const skuChunks = chunkArray(allCsvSkus, BATCH_SIZE);
          const existingSkus = new Set();

          for (const chunk of skuChunks) {
               const found = await getExistingSkus(chunk);
               found.forEach((sku) => existingSkus.add(sku));
              
               await new Promise((r) => setTimeout(r, 30));
          }

          const skusMissing = allCsvSkus.filter((s) => !existingSkus.has(s));

          const rowsToUpdate = rows.filter((r) => existingSkus.has(r.sku));

          const batches = chunkArray(rowsToUpdate, BATCH_SIZE);
          let totalUpdated = 0;

          for (let i = 0; i < batches.length; i++) {
               const affected = await updateBatch(batches[i]);
               totalUpdated += affected;

               if (i < batches.length - 1) {
                    await new Promise((r) => setTimeout(r, 50));
               }
          }

          const duration = Date.now() - startTime;

          const logEntry = {
               status: "success",
               csv_rows: rows.length,
               in_site: existingSkus.size,
               updated: totalUpdated,
               not_in_site_count: skusMissing.length,
               skus_missing: skusMissing,
               duration_ms: duration,
          };
          writeLog(logEntry);

          return res.status(200).json(logEntry);

     } catch (err) {
          console.error("[stock_avex] Eroare:", err);

          const errorEntry = {
               status: "error",
               error: err.message,
               duration_ms: Date.now() - startTime,
          };
          writeLog(errorEntry);

          return res.status(500).json(errorEntry);
     }
}