const express = require("express");
const sqlite3 = require("sqlite3").verbose();
const path = require("path");
const app = express();

// Middleware
app.use(express.json());
app.use(express.static("public"));

// CORS middleware
app.use((req, res, next) => {
  res.header("Access-Control-Allow-Origin", "*");
  res.header("Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS");
  res.header("Access-Control-Allow-Headers", "Content-Type");
  if (req.method === "OPTIONS") {
    return res.sendStatus(200);
  }
  next();
});

// Initialize SQLite database
const db = new sqlite3.Database("./products.db", (err) => {
  if (err) console.error(err.message);
  console.log("Connected to products database.");
});

function runInitAsync(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.run(sql, params, (err) => {
      if (err) reject(err);
      else resolve();
    });
  });
}

function allInitAsync(sql, params = []) {
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => {
      if (err) reject(err);
      else resolve(rows);
    });
  });
}

async function initializeDatabase() {
  await runInitAsync(`CREATE TABLE IF NOT EXISTS batches (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_name TEXT NOT NULL UNIQUE,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    status INTEGER NOT NULL DEFAULT 0
  )`);

  // Migration for existing databases: add status column if missing
  const columns = await allInitAsync(`PRAGMA table_info(batches)`);
  const hasStatusColumn = columns.some((col) => col.name === "status");
  if (!hasStatusColumn) {
    await runInitAsync(
      `ALTER TABLE batches ADD COLUMN status INTEGER NOT NULL DEFAULT 0`,
    );
    console.log("Added status column to batches table.");
  }

  await runInitAsync(`CREATE TABLE IF NOT EXISTS items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_id INTEGER NOT NULL,
    brand TEXT,
    mpn TEXT NOT NULL,
    mpn_custom TEXT,
    sn TEXT NOT NULL,
    sn_custom TEXT,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE CASCADE
  )`);

  await runInitAsync(`CREATE TABLE IF NOT EXISTS items_mix (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_id INTEGER NOT NULL,
    brand TEXT,
    mpn TEXT NOT NULL,
    mpn_custom TEXT,
    sn TEXT NOT NULL,
    sn_custom TEXT,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE CASCADE
  )`);

  // Migration for existing databases: add sn_custom column if missing
  const itemsColumns = await allInitAsync(`PRAGMA table_info(items)`);
  if (!itemsColumns.some((col) => col.name === "sn_custom")) {
    await runInitAsync(`ALTER TABLE items ADD COLUMN sn_custom TEXT`);
    console.log("Added sn_custom column to items table.");
  }

  const itemsMixColumns = await allInitAsync(`PRAGMA table_info(items_mix)`);
  if (!itemsMixColumns.some((col) => col.name === "sn_custom")) {
    await runInitAsync(`ALTER TABLE items_mix ADD COLUMN sn_custom TEXT`);
    console.log("Added sn_custom column to items_mix table.");
  }

  // Create indexes for better performance
  await runInitAsync(
    `CREATE INDEX IF NOT EXISTS idx_items_batch_id ON items(batch_id)`,
  );
  await runInitAsync(
    `CREATE INDEX IF NOT EXISTS idx_items_mix_batch_id ON items_mix(batch_id)`,
  );
  await runInitAsync(
    `CREATE INDEX IF NOT EXISTS idx_batches_status ON batches(status)`,
  );
  await runInitAsync(`CREATE INDEX IF NOT EXISTS idx_items_mpn ON items(mpn)`);
  await runInitAsync(`CREATE INDEX IF NOT EXISTS idx_items_sn ON items(sn)`);
  await runInitAsync(
    `CREATE INDEX IF NOT EXISTS idx_items_brand ON items(brand)`,
  );
  await runInitAsync(
    `CREATE INDEX IF NOT EXISTS idx_items_mix_brand ON items_mix(brand)`,
  );
}

// ==================== BATCH API ROUTES ====================

function runAsync(db, sql, params = []) {
  return new Promise((resolve, reject) => {
    db.run(sql, params, function (err) {
      if (err) reject(err);
      else resolve(this); // this.lastID, this.changes
    });
  });
}

function prepareRunAsync(stmt, params = []) {
  return new Promise((resolve, reject) => {
    stmt.run(params, function (err) {
      if (err) reject(err);
      else resolve();
    });
  });
}

function finalizeAsync(stmt) {
  return new Promise((resolve, reject) => {
    stmt.finalize((err) => (err ? reject(err) : resolve()));
  });
}

// Save batch with items and items_mix
app.post("/api/batch/save", async (req, res) => {
  const { batch_name, items, items_mix } = req.body;

  if (!batch_name) {
    return res.status(400).json({ error: "batch_name is required" });
  }

  if (!Array.isArray(items) || items.length === 0) {
    return res
      .status(400)
      .json({ error: "items array is required and must not be empty" });
  }

  let insertedItems = 0;
  let insertedMixItems = 0;
  let errors = [];

  try {
    // BEGIN
    await runAsync(db, "BEGIN TRANSACTION");

    // Insert batch
    const batchResult = await runAsync(
      db,
      "INSERT INTO batches (batch_name) VALUES (?)",
      [batch_name],
    );

    const batchId = batchResult.lastID;

    // ===== Insert items =====
    const itemStmt = db.prepare(
      "INSERT INTO items (batch_id, brand, mpn, mpn_custom, sn, sn_custom) VALUES (?, ?, ?, ?, ?, ?)",
    );

    for (let i = 0; i < items.length; i++) {
      const item = items[i];

      if (!item.mpn || !item.sn) {
        errors.push(`Item at index ${i} is missing mpn or sn`);
        continue;
      }

      try {
        await prepareRunAsync(itemStmt, [
          batchId,
          item.brand || null,
          item.mpn,
          item.mpn_custom || null,
          item.sn,
          item.sn_custom || null,
        ]);
        insertedItems++;
      } catch (err) {
        errors.push(`Error inserting item at index ${i}: ${err.message}`);
      }
    }

    await finalizeAsync(itemStmt);

    // ===== Insert items_mix (optional) =====
    if (Array.isArray(items_mix) && items_mix.length > 0) {
      const mixStmt = db.prepare(
        "INSERT INTO items_mix (batch_id, brand, mpn, mpn_custom, sn, sn_custom) VALUES (?, ?, ?, ?, ?, ?)",
      );

      for (let i = 0; i < items_mix.length; i++) {
        const item = items_mix[i];

        if (!item.mpn || !item.sn) {
          errors.push(`Mixed item at index ${i} is missing mpn or sn`);
          continue;
        }

        try {
          await prepareRunAsync(mixStmt, [
            batchId,
            item.brand || null,
            item.mpn,
            item.mpn_custom || null,
            item.sn,
            item.sn_custom || null,
          ]);
          insertedMixItems++;
        } catch (err) {
          errors.push(
            `Error inserting mixed item at index ${i}: ${err.message}`,
          );
        }
      }

      await finalizeAsync(mixStmt);
    }

    // COMMIT
    await runAsync(db, "COMMIT");

    return res.json({
      success: true,
      batch_id: batchId,
      batch_name,
      inserted_items: insertedItems,
      inserted_mix_items: insertedMixItems,
      errors: errors.length ? errors : undefined,
    });
  } catch (err) {
    // ROLLBACK nếu có lỗi nghiêm trọng
    try {
      await runAsync(db, "ROLLBACK");
    } catch (_) {}

    return res.status(500).json({
      error: err.message,
    });
  }
});

// Get all batches with their items and items_mix
app.get("/api/batch/get-all", (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 50;
  const search = req.query.search || "";
  const sortBy = req.query.sortBy || "id";
  const sortOrder = req.query.sortOrder || "DESC";
  const offset = (page - 1) * limit;

  // Validate sortBy
  const validColumns = ["id", "batch_name", "createdAt"];
  const column = validColumns.includes(sortBy) ? sortBy : "id";
  const order = sortOrder.toUpperCase() === "ASC" ? "ASC" : "DESC";

  const whereConditions = ["status = 0"];
  let searchParams = [];

  if (search) {
    const searchCondition = `
      (
        batch_name LIKE ?
        OR id LIKE ?
        OR EXISTS (
          SELECT 1 FROM items 
          WHERE items.batch_id = batches.id 
          AND sn LIKE ?
        )
        OR EXISTS (
          SELECT 1 FROM items_mix 
          WHERE items_mix.batch_id = batches.id 
          AND sn LIKE ?
        )
      )
    `;
    whereConditions.push(searchCondition);

    const searchParam = `%${search}%`;
    searchParams = [searchParam, searchParam, searchParam, searchParam];
  }

  const whereClause = ` WHERE ${whereConditions.join(" AND ")}`;
  const countQuery = `SELECT COUNT(*) as total FROM batches${whereClause}`;
  const query = `SELECT * FROM batches${whereClause} ORDER BY ${column} ${order} LIMIT ? OFFSET ?`;
  const countParams = [...searchParams];
  const queryParams = [...searchParams, limit, offset];

  db.get(countQuery, countParams, (err, countRow) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }

    db.all(query, queryParams, (err, batches) => {
      if (err) {
        return res.status(500).json({ error: err.message });
      }

      if (batches.length === 0) {
        return res.json({
          batches: [],
          total: 0,
          page,
          limit,
          totalPages: 0,
        });
      }

      // Get items for each batch
      let processed = 0;
      const batchesWithItems = [];

      batches.forEach((batch) => {
        // Get items
        db.all(
          "SELECT brand, mpn, mpn_custom, sn, sn_custom, createdAt FROM items WHERE batch_id = ?",
          [batch.id],
          (err, items) => {
            if (err) {
              console.error("Error fetching items:", err);
              items = [];
            }

            // Get items_mix
            db.all(
              "SELECT brand, mpn, mpn_custom, sn, sn_custom, createdAt FROM items_mix WHERE batch_id = ?",
              [batch.id],
              (err, items_mix) => {
                if (err) {
                  console.error("Error fetching items_mix:", err);
                  items_mix = [];
                }

                batchesWithItems.push({
                  id: batch.id,
                  batch_name: batch.batch_name,
                  createdAt: batch.createdAt,
                  items: items || [],
                  items_mix: items_mix || [],
                });

                processed++;

                if (processed === batches.length) {
                  res.json({
                    batches: batchesWithItems,
                    total: countRow.total,
                    page,
                    limit,
                    totalPages: Math.ceil(countRow.total / limit),
                  });
                }
              },
            );
          },
        );
      });
    });
  });
});

// Get single batch by ID
app.get("/api/batch/get/:id", (req, res) => {
  const id = req.params.id;

  db.get(
    "SELECT * FROM batches WHERE id = ? AND status = 0",
    [id],
    (err, batch) => {
      if (err) {
        return res.status(500).json({ error: err.message });
      }

      if (!batch) {
        return res.status(404).json({ error: "Batch not found" });
      }

      // Get items
      db.all(
        "SELECT brand, mpn, mpn_custom, sn, sn_custom, createdAt FROM items WHERE batch_id = ?",
        [id],
        (err, items) => {
          if (err) {
            return res.status(500).json({ error: err.message });
          }

          // Get items_mix
          db.all(
            "SELECT brand, mpn, mpn_custom, sn, sn_custom, createdAt FROM items_mix WHERE batch_id = ?",
            [id],
            (err, items_mix) => {
              if (err) {
                return res.status(500).json({ error: err.message });
              }

              res.json({
                id: batch.id,
                batch_name: batch.batch_name,
                createdAt: batch.createdAt,
                items: items || [],
                items_mix: items_mix || [],
              });
            },
          );
        },
      );
    },
  );
});

// Soft delete batch (update status from 0 to 1)
app.delete("/api/batch/delete/:id", (req, res) => {
  const id = req.params.id;

  db.run(
    "UPDATE batches SET status = 1 WHERE id = ? AND status = 0",
    [id],
    function (err) {
      if (err) {
        return res.status(500).json({ error: err.message });
      }

      if (this.changes === 0) {
        return res.status(404).json({ error: "Batch not found" });
      }

      res.json({ success: true, updated: this.changes, deleted: this.changes });
    },
  );
});

// Search items across all batches
app.get("/api/items/search", (req, res) => {
  const search = req.query.q || "";

  if (!search) {
    return res.status(400).json({ error: "Search query is required" });
  }

  const searchParam = `%${search}%`;

  const query = `
    SELECT
      b.id as batch_id,
      b.batch_name,
      i.brand,
      i.mpn,
      i.mpn_custom,
      i.sn,
      i.sn_custom,
      i.createdAt,
      'items' as type
    FROM items i
    JOIN batches b ON i.batch_id = b.id
    WHERE i.mpn LIKE ? OR i.sn LIKE ? OR i.brand LIKE ? OR i.mpn_custom LIKE ? OR i.sn_custom LIKE ?
    UNION ALL
    SELECT
      b.id as batch_id,
      b.batch_name,
      im.brand,
      im.mpn,
      im.mpn_custom,
      im.sn,
      im.sn_custom,
      im.createdAt,
      'items_mix' as type
    FROM items_mix im
    JOIN batches b ON im.batch_id = b.id
    WHERE im.mpn LIKE ? OR im.sn LIKE ? OR im.brand LIKE ? OR im.mpn_custom LIKE ? OR im.sn_custom LIKE ?
    ORDER BY createdAt DESC
    LIMIT 100
  `;

  db.all(
    query,
    [
      searchParam,
      searchParam,
      searchParam,
      searchParam,
      searchParam,
      searchParam,
      searchParam,
      searchParam,
      searchParam,
      searchParam,
    ],
    (err, results) => {
      if (err) {
        return res.status(500).json({ error: err.message });
      }

      res.json({ results });
    },
  );
});

// Serve HTML UI
app.get("/", (req, res) => {
  res.sendFile(path.join(__dirname, "public", "index.html"));
});

const PORT = process.env.PORT || 4444;
initializeDatabase()
  .then(() => {
    app.listen(PORT, () => {
      console.log(`Server is running on http://localhost:${PORT}`);
    });
  })
  .catch((err) => {
    console.error("Database initialization failed:", err.message);
    process.exit(1);
  });