const { pool } = require("../../config/db");

// ─────────────────────────────────────────────
// GENERATE GROUP ID
// ─────────────────────────────────────────────
const generateGroupId = async (orgId) => {
  // Use fixed prefix for all groups
  const prefix = "GRP";
  
  // Get the next sequence number for this org
  const result = await pool.query(
    `SELECT COALESCE(MAX(CAST(SUBSTRING(group_id FROM LENGTH($1) + 1) AS INT)), 0) + 1 AS next_num
     FROM group_master
     WHERE org_id = $2 AND group_id LIKE $1 || '%'`,
    [prefix, orgId]
  );
  
  const nextNum = parseInt(result.rows[0].next_num, 10);
  return `${prefix}${String(nextNum).padStart(3, "0")}`;
};

// ─────────────────────────────────────────────
// CREATE GROUP
// POST /api/group-master
// ─────────────────────────────────────────────
const createGroup = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId || req.user?.email;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const { group_name } = req.body;

    if (!group_name || !group_name.trim()) {
      return res.status(400).json({ success: false, message: "Group name is required" });
    }

    // Auto-generate group ID
    const group_id = await generateGroupId(orgId);

    // Check if group name already exists for this org
    const existingGroup = await pool.query(
      `SELECT id FROM group_master 
       WHERE group_name = $1 AND org_id = $2 AND is_deleted = false`,
      [group_name.trim(), orgId]
    );

    if (existingGroup.rows.length > 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Group name already exists for this organization" 
      });
    }

    const result = await pool.query(
      `INSERT INTO group_master (group_name, group_id, org_id, created_by)
       VALUES ($1, $2, $3, $4)
       RETURNING id, group_name, group_id, created_at`,
      [group_name.trim(), group_id, orgId, created_by || null]
    );

    res.status(201).json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in createGroup:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET ALL GROUPS (with pagination)
// GET /api/group-master?search=&page=1&limit=10
// ─────────────────────────────────────────────
const getAllGroups = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const { search = "", page = 1, limit = 10 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM group_master
       WHERE is_deleted = false AND org_id = $1
         AND (group_name ILIKE $2 OR group_id ILIKE $2)`,
      [orgId, searchTerm]
    );

    const total = parseInt(countResult.rows[0].total);

    const result = await pool.query(
      `SELECT id, group_name, group_id, created_at, updated_at
       FROM group_master
       WHERE is_deleted = false AND org_id = $1
         AND (group_name ILIKE $2 OR group_id ILIKE $2)
       ORDER BY created_at DESC
       LIMIT $3 OFFSET $4`,
      [orgId, searchTerm, parseInt(limit), offset]
    );

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit),
      },
    });

  } catch (err) {
    console.error("Error in getAllGroups:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET ALL GROUPS (without pagination - for dropdowns)
// GET /api/group-master/all
// ─────────────────────────────────────────────
const getAllGroupsWithoutPagination = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `SELECT id, group_name, group_id
       FROM group_master
       WHERE is_deleted = false AND org_id = $1
       ORDER BY group_name ASC`,
      [orgId]
    );

    res.json({ success: true, data: result.rows });

  } catch (err) {
    console.error("Error in getAllGroupsWithoutPagination:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// GET GROUP BY ID
// GET /api/group-master/:id
// ─────────────────────────────────────────────
const getGroupById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

    const groupId = parseInt(id);
    if (isNaN(groupId)) {
      return res.status(400).json({ success: false, message: "Invalid group ID format" });
    }

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `SELECT id, group_name, group_id, created_at, updated_at, created_by, updated_by
       FROM group_master
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [groupId, orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Group not found" });
    }

    res.json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in getGroupById:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// UPDATE GROUP
// PUT /api/group-master/:id
// ─────────────────────────────────────────────
const updateGroup = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;

    const groupId = parseInt(id);
    if (isNaN(groupId)) {
      return res.status(400).json({ success: false, message: "Invalid group ID format" });
    }

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const { group_name } = req.body;

    if (!group_name || !group_name.trim()) {
      return res.status(400).json({ success: false, message: "Group name is required" });
    }

    // Check if group exists
    const existingGroup = await pool.query(
      `SELECT id FROM group_master
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [groupId, orgId]
    );

    if (existingGroup.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Group not found" });
    }

    // Check if another group has the same name
    const duplicateCheck = await pool.query(
      `SELECT id FROM group_master 
       WHERE group_name = $1 AND org_id = $2 AND id != $3 AND is_deleted = false`,
      [group_name.trim(), orgId, groupId]
    );

    if (duplicateCheck.rows.length > 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Group name already exists for this organization" 
      });
    }

    const result = await pool.query(
      `UPDATE group_master SET
        group_name = $1,
        updated_by = $2,
        updated_at = NOW()
       WHERE id = $3 AND org_id = $4 AND is_deleted = false
       RETURNING id, group_name, group_id`,
      [group_name.trim(), updated_by || null, groupId, orgId]
    );

    res.json({ success: true, data: result.rows[0] });

  } catch (err) {
    console.error("Error in updateGroup:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// SOFT DELETE GROUP
// DELETE /api/group-master/:id
// ─────────────────────────────────────────────
const deleteGroup = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId || req.user?.email;

    const groupId = parseInt(id);
    if (isNaN(groupId)) {
      return res.status(400).json({ success: false, message: "Invalid group ID format" });
    }

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    const result = await pool.query(
      `UPDATE group_master
       SET is_deleted = true,
           updated_by = $1,
           updated_at = NOW(),
           deleted_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, group_name, group_id`,
      [updated_by || null, groupId, orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Group not found or already deleted" });
    }

    res.json({ success: true, message: "Group deleted successfully", data: result.rows[0] });

  } catch (err) {
    console.error("Error in deleteGroup:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// HARD DELETE GROUP (optional - careful with foreign keys)
// DELETE /api/group-master/hard/:id
// ─────────────────────────────────────────────
const hardDeleteGroup = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

    const groupId = parseInt(id);
    if (isNaN(groupId)) {
      return res.status(400).json({ success: false, message: "Invalid group ID format" });
    }

    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    }

    // Check if group is being used by any items
    const usageCheck = await pool.query(
      `SELECT COUNT(*) as count FROM procurement_itemmaster 
       WHERE group_id = $1 AND org_id = $2 AND is_deleted = false`,
      [groupId, orgId]
    );

    if (parseInt(usageCheck.rows[0].count) > 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Cannot delete group as it is being used by items" 
      });
    }

    const result = await pool.query(
      `DELETE FROM group_master
       WHERE id = $1 AND org_id = $2
       RETURNING id, group_name, group_id`,
      [groupId, orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Group not found" });
    }

    res.json({ success: true, message: "Group permanently deleted", data: result.rows[0] });

  } catch (err) {
    console.error("Error in hardDeleteGroup:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

// ─────────────────────────────────────────────
// MODULE EXPORTS
// ─────────────────────────────────────────────
module.exports = {
  createGroup,
  getAllGroups,
  getAllGroupsWithoutPagination,
  getGroupById,
  updateGroup,
  deleteGroup,
  hardDeleteGroup,
};