const express = require("express");
const router = express.Router();
const db = require("../db");

// CREATE
router.post("/", async (req, res) => {
  const {
    log_id,
    user_id,
    employee_name,
    division_name,
    role_name,
    project_names,
    action,
    remarks,
    created_at,
  } = req.body;

  try {
    // Validate required fields
    if (!log_id || !user_id || !employee_name || !action) {
      return res.status(400).json({
        error:
          "Missing required fields: log_id, user_id, employee_name, action",
      });
    }

    const [result] = await db.execute(
      "INSERT INTO employee_log (log_id, user_id, employee_name, division_name, role_name, project_names, action, remarks, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        log_id,
        user_id,
        employee_name,
        division_name || null,
        role_name || null,
        project_names || null,
        action,
        remarks || null,
        created_at || new Date().toISOString().slice(0, 19).replace("T", " "),
      ]
    );

    res.status(201).json({
      id: result.insertId,
      log_id: log_id,
      message: "Employee log created successfully",
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// READ ALL with filtering and pagination
router.get("/", async (req, res) => {
  try {
    const {
      page = 1,
      limit = 50,
      employee_name,
      action,
      division_name,
      role_name,
      start_date,
      end_date,
      user_id,
      sort_by = "created_at",
      sort_order = "DESC",
    } = req.query;

    let query = "SELECT * FROM employee_log WHERE 1=1";
    const queryParams = [];

    // Add filters
    if (employee_name) {
      query += " AND employee_name LIKE ?";
      queryParams.push(`%${employee_name}%`);
    }

    if (action) {
      query += " AND action LIKE ?";
      queryParams.push(`%${action}%`);
    }

    if (division_name) {
      query += " AND division_name LIKE ?";
      queryParams.push(`%${division_name}%`);
    }

    if (role_name) {
      query += " AND role_name LIKE ?";
      queryParams.push(`%${role_name}%`);
    }

    if (user_id) {
      query += " AND user_id = ?";
      queryParams.push(user_id);
    }

    if (start_date) {
      query += " AND created_at >= ?";
      queryParams.push(start_date);
    }

    if (end_date) {
      query += " AND created_at <= ?";
      queryParams.push(end_date);
    }

    // Add sorting
    const allowedSortFields = [
      "log_id",
      "employee_name",
      "action",
      "division_name",
      "role_name",
      "created_at",
    ];
    const allowedSortOrders = ["ASC", "DESC"];

    if (
      allowedSortFields.includes(sort_by) &&
      allowedSortOrders.includes(sort_order.toUpperCase())
    ) {
      query += ` ORDER BY ${sort_by} ${sort_order.toUpperCase()}`;
    } else {
      query += " ORDER BY created_at DESC";
    }

    // Add pagination
    const offset = (page - 1) * limit;
    query += " LIMIT ? OFFSET ?";
    queryParams.push(parseInt(limit), offset);

    const [rows] = await db.execute(query, queryParams);

    // Get total count for pagination
    let countQuery = "SELECT COUNT(*) as total FROM employee_log WHERE 1=1";
    const countParams = queryParams.slice(0, -2); // Remove limit and offset

    const [countResult] = await db.execute(countQuery, countParams);
    const total = countResult[0].total;

    res.json({
      logs: rows,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total,
        total_pages: Math.ceil(total / limit),
      },
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});
// Route: Get employee logs by username
router.get("/employee_logs_by_username", async (req, res) => {
  const { name } = req.query;



  if (!name || name.trim() === "") {
    return res.status(400).json({ error: "Username is required" });
  }

  try {
    // Step 1: Get user_id from users table
    const [userResult] = await db.execute(
      "SELECT user_id FROM users WHERE username = ?",
      [name]
    );

    if (userResult.length === 0) {
      return res.status(404).json({ error: "User not found" });
    }

    const user_id = userResult[0].user_id;

    // Step 2: Fetch logs from employee_log using user_id
    const [logs] = await db.execute(
      `SELECT *
       FROM employee_log
       WHERE user_id = ?
       ORDER BY created_at DESC`,
      [user_id]
    );

    if (logs.length === 0) {
      return res
        .status(404)
        .json({ message: "No employee logs found for this user" });
    }

    res.status(200).json(logs);
  } catch (err) {
    res
      .status(500)
      .json({ error: "Internal server error", details: err.message });
  }
});
// GET logs for a specific employee
router.get("/employee_logs_by_name", async (req, res) => {
  const { name } = req.query;



  if (!name || name.trim() === "") {
    return res.status(400).json({ error: "Employee name is required" });
  }

  try {
    const query = `
      SELECT *
      FROM employee_log
      WHERE employee_name = ?
      ORDER BY created_at DESC
    `;

    const [results] = await db.execute(query, [name]);



    if (results.length === 0) {
      return res
        .status(404)
        .json({ message: "No logs found for this employee" });
    }

    res.status(200).json(results);
  } catch (err) {
    res
      .status(500)
      .json({ error: "Internal server error", details: err.message });
  }
});
// READ ONE
router.get("/:id", async (req, res) => {
  try {
    const [rows] = await db.execute(
      "SELECT * FROM employee_log WHERE log_id = ?",
      [req.params.id]
    );
    if (rows.length === 0)
      return res.status(404).json({ error: "Log entry not found" });
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET logs by employee
router.get("/employee/:employee_id", async (req, res) => {
  try {
    const { employee_id } = req.params;
    const { limit = 20 } = req.query;

    const [rows] = await db.execute(
      "SELECT * FROM employee_log WHERE employee_name LIKE ? ORDER BY created_at DESC LIMIT ?",
      [`%${employee_id}%`, parseInt(limit)]
    );

    res.json({
      employee_id,
      logs: rows,
      total: rows.length,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET logs by action type
router.get("/action/:action", async (req, res) => {
  try {
    const { action } = req.params;
    const { limit = 50, page = 1 } = req.query;
    const offset = (page - 1) * limit;

    const [rows] = await db.execute(
      "SELECT * FROM employee_log WHERE action LIKE ? ORDER BY created_at DESC LIMIT ? OFFSET ?",
      [`%${action}%`, parseInt(limit), offset]
    );

    const [countResult] = await db.execute(
      "SELECT COUNT(*) as total FROM employee_log WHERE action LIKE ?",
      [`%${action}%`]
    );

    res.json({
      action,
      logs: rows,
      pagination: {
        page: parseInt(page),
        limit: parseInt(limit),
        total: countResult[0].total,
        total_pages: Math.ceil(countResult[0].total / limit),
      },
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET recent activity summary
router.get("/summary/recent", async (req, res) => {
  try {
    const { days = 7 } = req.query;

    const [rows] = await db.execute(
      `SELECT 
        action,
        COUNT(*) as count,
        MAX(created_at) as last_occurrence
       FROM employee_log 
       WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
       GROUP BY action
       ORDER BY count DESC`,
      [parseInt(days)]
    );

    const [totalLogs] = await db.execute(
      "SELECT COUNT(*) as total FROM employee_log WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)",
      [parseInt(days)]
    );

    res.json({
      period_days: parseInt(days),
      total_logs: totalLogs[0].total,
      activity_summary: rows,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// UPDATE
router.put("/:id", async (req, res) => {
  const {
    user_id,
    employee_id,
    employee_name,
    division_name,
    role_name,
    project_names,
    action,
    remarks,
    created_at,
  } = req.body;

  try {
    const [result] = await db.execute(
      "UPDATE employee_log SET user_id = ?, employee_id = ?, employee_name = ?, division_name = ?, role_name = ?, project_names = ?, action = ?, remarks = ?, created_at = ? WHERE log_id = ?",
      [
        user_id,
        employee_id,
        employee_name,
        division_name,
        role_name,
        project_names,
        action,
        remarks,
        created_at,
        req.params.id,
      ]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Log entry not found" });
    }

    res.json({
      id: req.params.id,
      message: "Employee log updated successfully",
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE
router.delete("/:id", async (req, res) => {
  try {
    const [result] = await db.execute(
      "DELETE FROM employee_log WHERE log_id = ?",
      [req.params.id]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: "Log entry not found" });
    }

    res.json({
      message: "Employee log deleted successfully",
      deleted_id: req.params.id,
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE logs older than specified days
router.delete("/cleanup/old", async (req, res) => {
  try {
    const { days = 365 } = req.query; // Default to 1 year

    const [result] = await db.execute(
      "DELETE FROM employee_log WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)",
      [parseInt(days)]
    );

    res.json({
      message: `Cleaned up ${result.affectedRows} old employee logs`,
      deleted_count: result.affectedRows,
      older_than_days: parseInt(days),
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
