const express = require("express");
const router = express.Router();
const db = require("../db");
const multer = require("multer");
const csvParser = require("csv-parser");
const fs = require("fs");
const { body, validationResult } = require("express-validator");

// Middleware for CSV uploads
const upload = multer({ dest: "uploads/" });

// Helper function for validation errors

router.post("/users", async (req, res) => {
  const {
    firstname,
    lastname,
    email,
    phone,
    nationality,
    location,
    state,
    coursetype,
    coursename,
  } = req.body;

  try {
    const result = await db.query(
      `INSERT INTO users (firstname, lastname, email, phone, nationality, location, state, courseType, courseName)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING *`,
      [
        firstname,
        lastname,
        email,
        phone,
        nationality,
        location,
        state,
        coursetype,
        coursename,
      ]
    );
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error("Error inserting user:", err);
    res.status(500).json({ error: "Failed to add user" });
  }
});

// Get all users
router.get("/users", async (req, res) => {
  try {
    const result = await db.query("SELECT * FROM users ORDER BY id DESC");
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching users:", err);
    res.status(500).json({ error: "Failed to fetch users" });
  }
});

router.post("/courses", async (req, res) => {
  const { course_name, course_level } = req.body;

  // Validation for missing fields
  if (!course_name || !course_level) {
    return res
      .status(400)
      .json({ error: "Course name and level are required" });
  }

  try {
    // Insert course into the database
    const result = await db.query(
      "INSERT INTO courses (course_name, course_level) VALUES ($1, $2) RETURNING *",
      [course_name, course_level]
    );

    // Respond with the inserted course
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Internal Server Error" });
  }
});

router.get("/courses", async (req, res) => {
  try {
    const result = await db.query("SELECT * FROM courses");
    res.json(result.rows); // Ensure data is in the expected format
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch courses" });
  }
});

router.post("/colleges", async (req, res) => {
  const {
    collegeName,
    location,
    city,
    website,
    orgType,
    registrationDate,
    formFees,
  } = req.body;

  const query = `
    INSERT INTO colleges (college_name, location, city, website, org_type, registration_date, form_fees)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING *;
  `;

  try {
    const result = await db.query(query, [
      collegeName,
      location,
      city,
      website,
      orgType,
      registrationDate,
      formFees,
    ]);
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error("Error:", err);
    res.status(500).json({ error: "Internal Server Error" });
  }
});

router.get("/colleges", async (req, res) => {
  try {
    const result = await db.query("SELECT * FROM colleges");
    res.json(result.rows); // Ensure data is in the expected format
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch colleges" });
  }
});

router.post("/group", async (req, res) => {
  const { selectedStudent, selectedCourse, selectedCollege } = req.body;

  try {
    // Using the PostgreSQL array literal syntax
    const query = `
      INSERT INTO groups (students, course, colleges)
      VALUES ($1, $2, $3) RETURNING *;
    `;
    const values = [
      `{${selectedStudent.join(",")}}`, // Format students as a PostgreSQL array
      selectedCourse,
      `{${selectedCollege.join(",")}}`, // Format colleges as a PostgreSQL array
    ];

    const result = await db.query(query, values);
    res.status(200).json(result.rows[0]);
  } catch (error) {
    console.error("Error creating group:", error);
    res.status(500).json({ message: "Error creating group" });
  }
});

router.get("/group/:groupId", async (req, res) => {
  const groupId = req.params.groupId;

  try {
    const query = `
      SELECT g.id, g.course, 
             array_agg(s.firstname || ' ' || s.lastname) AS students,
             array_agg(c.college_name) AS colleges
      FROM groups g
      JOIN students s ON s.id = ANY(g.students)  -- Join with students table using IDs
      JOIN colleges c ON c.id = ANY(g.colleges)  -- Join with colleges table using IDs
      WHERE g.id = $1  -- Filter by the specific group ID
      GROUP BY g.id, g.course;  -- Group by the group ID and course
    `;

    const result = await db.query(query, [groupId]);

    if (result.rows.length > 0) {
      res.json(result.rows[0]);
    } else {
      res.status(404).json({ error: "Group not found" });
    }
  } catch (error) {
    console.error("Error fetching group data:", error);
    res.status(500).json({ error: "Failed to fetch group data" });
  }
});

const { Pool } = require("pg");
const pool = new Pool({
  user: "postgres",
  host: "localhost",
  database: "bmc",
  password: "admin",
  port: 5432,
});

router.post("/upload-csv", upload.single("file"), (req, res) => {
  const filePath = req.file.path;

  const rows = [];
  fs.createReadStream(filePath)
    .pipe(csvParser())
    .on("data", (row) => rows.push(row))
    .on("end", async () => {
      try {
        // Use the pool to acquire a new client
        const client = await pool.connect();

        // console.log("Parsed Rows:", rows); // Debugging: Log rows to verify data

        // Loop through CSV rows and insert them into the database
        for (const row of rows) {
          await client.query(
            `INSERT INTO email_logs_report (createdAt, requestId, subject, domain, senderEmail, recipientEmail, templateName, status, statusUpdatedAt, description, opened, clicked, campaignName)
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)`,
            [
              row.createdAt,
              row.requestId,
              row.subject,
              row.domain,
              row.senderEmail,
              row.recipientEmail,
              row.templateName,
              row.status,
              row.statusUpdatedAt,
              row.description,
              row.opened === "true",
              row.clicked === "true",
              row.campaignName,
            ]
          );
        }

        // Release the client after finishing operations
        client.release();

        res.status(200).send("CSV data imported successfully!");
      } catch (err) {
        console.error("Error:", err.message);
        res.status(500).send("Error importing CSV data: " + err.message);
      } finally {
        // Always clean up the file after processing
        fs.unlinkSync(filePath);
      }
    });
});

module.exports = router;
