

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

/* ===========================
   HELPER: Get file path
   ✅ FIX: Moved to module scope so both createAsset and updateAsset can use it
=========================== */
const getSingleFilePath = (file, existing = null) => {
  if (!file) return existing;
  const fullPath = file.path.replace(/\\/g, "/");
  const relativePath = fullPath.split("uploads/")[1];
  return relativePath ? "uploads/" + relativePath : existing;
};

/* ===========================
   ✅ CREATE ASSET
=========================== */
const createAsset = async (req, res) => {
  try {
    console.log("=== CREATE ASSET DEBUG ===");
    console.log("req.body:", req.body);
    console.log("req.file:", req.file);
    console.log("req.session.user:", req.session.user);

    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;
    const created_by = req.session.user.id;

    let {
      asset_name,
      s_no,
      category,
      asset_type,
      brand,
      model,
      warranty_expiry,
      description,
      status,
      employee_id,
    } = req.body;

    if (!asset_name || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Asset name and Organization ID are required",
      });
    }

    // ✅ FIX: Uses module-level getSingleFilePath
    const asset_image = getSingleFilePath(req.file);

    // Generate serial number if not provided
    if (!s_no || s_no === "") {
      const countResult = await pool.query(
        `SELECT COUNT(*) as count FROM assets WHERE org_id = $1`,
        [org_id]
      );
      const count = parseInt(countResult.rows[0].count) + 1;
      const orgShort = String(org_id).substring(0, 8);
      s_no = `AST-${orgShort}-${String(count).padStart(4, "0")}`;
    } else {
      const existingAsset = await pool.query(
        `SELECT id FROM assets WHERE s_no = $1 AND is_deleted = false`,
        [s_no]
      );
      if (existingAsset.rows.length > 0) {
        return res.status(400).json({
          success: false,
          message: "Asset with this serial number already exists",
        });
      }
    }

    let employee_name = null;
    let assigned_date = null;

    if (
      employee_id &&
      employee_id !== "" &&
      employee_id !== "null" &&
      employee_id !== "undefined"
    ) {
      const employeeResult = await pool.query(
        `SELECT CONCAT(first_name, ' ', last_name) as full_name 
         FROM employees 
         WHERE employee_id = $1 AND is_deleted = false`,
        [employee_id]
      );

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

      employee_name = employeeResult.rows[0].full_name;
      assigned_date = new Date();

      if (!status || status === "Available") {
        status = "Assigned";
      }
    }

    if (!status || status === "") {
      status = "Available";
    }

    const result = await pool.query(
      `INSERT INTO assets (
        id, asset_name, asset_image, s_no, category, asset_type,
        brand, model, warranty_expiry, description, status,
        employee_id, employee_name, assigned_date, org_id, created_by, created_at, updated_at
      )
      VALUES (DEFAULT, $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NOW(), NOW())
      RETURNING *`,
      [
        asset_name,
        asset_image,
        s_no,
        category || null,
        asset_type || null,
        brand || null,
        model || null,
        warranty_expiry || null,
        description || null,
        status,
        employee_id || null,
        employee_name,
        assigned_date,
        org_id,
        created_by,
      ]
    );

    res.status(201).json({
      success: true,
      message: "Asset created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE ASSET ERROR:", error);
    if (error.code === "23505") {
      return res.status(400).json({ success: false, message: "Duplicate entry. Please try again." });
    }
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET ALL ASSETS
=========================== */
const getAllAssets = async (req, res) => {
  try {
    const { page = 1, limit = 10, status, search } = req.query;
    const offset = (page - 1) * limit;

    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;

    // Base WHERE conditions
    let whereConditions = "WHERE a.is_deleted = false";
    const queryParams = [];
    let paramCount = 1;

    if (status && status !== "undefined" && status !== "") {
      whereConditions += ` AND a.status = $${paramCount}`;
      queryParams.push(status);
      paramCount++;
    }

    if (org_id) {
      whereConditions += ` AND a.org_id = $${paramCount}`;
      queryParams.push(org_id);
      paramCount++;
    }

    if (search && search !== "undefined" && search !== "") {
      whereConditions += ` AND (a.asset_name ILIKE $${paramCount} OR a.s_no ILIKE $${paramCount} OR a.employee_name ILIKE $${paramCount})`;
      queryParams.push(`%${search}%`);
      paramCount++;
    }

    // Separate COUNT query
    const countQuery = `
      SELECT COUNT(*) as total
      FROM assets a
      ${whereConditions}
    `;
    
    const countResult = await pool.query(countQuery, queryParams);
    const totalRecords = parseInt(countResult.rows[0]?.total || 0);

    // Main data query - FIXED: Removed e.department and e.designation
    const dataQuery = `
      SELECT 
        a.*,
        e.employee_code,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email as employee_email
      FROM assets a
      LEFT JOIN employees e ON a.employee_id = e.employee_id
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      ${whereConditions}
      ORDER BY a.created_at DESC
      LIMIT $${paramCount} OFFSET $${paramCount + 1}
    `;
    
    queryParams.push(parseInt(limit), offset);
    const result = await pool.query(dataQuery, queryParams);

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / limit),
        totalRecords,
        recordsPerPage: parseInt(limit),
      },
    });
  } catch (err) {
    console.error("FETCH ASSETS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ===========================
   ✅ GET ASSET BY ID
=========================== */
const getAssetById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `SELECT 
        a.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email as employee_email,
        e.phone as employee_phone
      FROM assets a
      LEFT JOIN employees e ON a.employee_id = e.employee_id
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE a.id = $1 AND a.is_deleted = false`,
      [id]
    );

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

    res.status(200).json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET ASSET BY ID ERROR:", err);
    res.status(500).json({ success: false, message: "Fetch failed" });
  }
};

/* ===========================
   ✅ UPDATE ASSET
=========================== */
const updateAsset = async (req, res) => {
  try {
    const { id } = req.params;
    const { asset_name, employee_id, status, s_no, assigned_date } = req.body;

    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const updated_by = req.session.user.id;

    const existingAsset = await pool.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false`,
      [id]
    );

    if (!existingAsset.rows.length) {
      return res.status(404).json({ success: false, message: "Asset not found" });
    }

    const oldAsset = existingAsset.rows[0];

    // ✅ FIX: getSingleFilePath now accessible from module scope
    const asset_image = getSingleFilePath(req.file, oldAsset.asset_image);

    let employee_name = oldAsset.employee_name;
    let finalStatus = status || oldAsset.status;
    let finalAssignedDate = assigned_date || oldAsset.assigned_date;

    if (
      employee_id &&
      employee_id !== oldAsset.employee_id &&
      employee_id !== "" &&
      employee_id !== "null"
    ) {
      const employeeResult = await pool.query(
        `SELECT CONCAT(first_name, ' ', last_name) as full_name 
         FROM employees 
         WHERE employee_id = $1 AND is_deleted = false`,
        [employee_id]
      );

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

      employee_name = employeeResult.rows[0].full_name;
      finalAssignedDate = new Date();

      if (!status) {
        finalStatus = "Assigned";
      }
    } else if (
      (!employee_id || employee_id === "" || employee_id === "null") &&
      !status &&
      oldAsset.status === "Assigned"
    ) {
      finalStatus = "Available";
      employee_name = null;
      finalAssignedDate = null;
    }

    const result = await pool.query(
      `UPDATE assets
      SET 
        asset_name = COALESCE($1, asset_name),
        asset_image = $2,
        s_no = COALESCE($3, s_no),
        status = $4,
        employee_id = $5,
        employee_name = $6,
        assigned_date = $7,
        updated_by = $8,
        updated_at = NOW()
      WHERE id = $9
      RETURNING *`,
      [
        asset_name || oldAsset.asset_name,
        asset_image,
        s_no || oldAsset.s_no,
        finalStatus,
        employee_id || null,
        employee_name,
        finalAssignedDate,
        updated_by,
        id,
      ]
    );

    res.status(200).json({
      success: true,
      message: "Asset updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE ASSET ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ DELETE ASSET (SOFT DELETE)
=========================== */
const deleteAsset = async (req, res) => {
  try {
    const { id } = req.params;

    const existingAsset = await pool.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false`,
      [id]
    );

    if (!existingAsset.rows.length) {
      return res.status(404).json({ success: false, message: "Asset not found" });
    }

    await pool.query(
      `UPDATE assets SET is_deleted = true, updated_at = NOW() WHERE id = $1`,
      [id]
    );

    res.json({ success: true, message: "Asset deleted successfully ✅" });
  } catch (err) {
    console.error("DELETE ASSET ERROR:", err);
    res.status(500).json({ success: false, message: "Delete failed" });
  }
};

/* ===========================
   ✅ ASSIGN ASSET TO EMPLOYEE
=========================== */
const assignAssetToEmployee = async (req, res) => {
  try {
    const { id } = req.params;
    const { employee_id } = req.body;

    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const updated_by = req.session.user.id;

    if (!employee_id) {
      return res.status(400).json({ success: false, message: "Employee ID is required" });
    }

    const employeeResult = await pool.query(
      `SELECT CONCAT(first_name, ' ', last_name) as full_name 
       FROM employees 
       WHERE employee_id = $1 AND is_deleted = false`,
      [employee_id]
    );

    if (!employeeResult.rows.length) {
      return res.status(404).json({ success: false, message: "Employee not found" });
    }

    const assetResult = await pool.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false`,
      [id]
    );

    if (!assetResult.rows.length) {
      return res.status(404).json({ success: false, message: "Asset not found" });
    }

    const asset = assetResult.rows[0];

    if (asset.status === "Assigned" && asset.employee_id !== employee_id) {
      return res.status(400).json({
        success: false,
        message: "Asset is already assigned to another employee",
      });
    }

    const result = await pool.query(
      `UPDATE assets
      SET 
        employee_id = $1,
        employee_name = $2,
        status = 'Assigned',
        assigned_date = NOW(),
        updated_by = $3,
        updated_at = NOW()
      WHERE id = $4
      RETURNING *`,
      [employee_id, employeeResult.rows[0].full_name, updated_by, id]
    );

    res.status(200).json({
      success: true,
      message: "Asset assigned successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("ASSIGN ASSET ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};


// /* ===========================
//    ✅ BULK ASSIGN MULTIPLE ASSETS TO ONE EMPLOYEE
//    POST /assets/bulk-assign
//    Body: { employee_id: string, asset_ids: number[] }
// =========================== */
// const bulkAssignAssets = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { employee_id, asset_ids } = req.body;

//     // Check authentication
//     if (!req.session || !req.session.user) {
//       return res.status(401).json({ 
//         success: false, 
//         message: "User not authenticated" 
//       });
//     }

//     const updated_by = req.session.user.id;
//     const org_id = req.session.user.orgId;

//     // Validate inputs
//     if (!employee_id) {
//       return res.status(400).json({ 
//         success: false, 
//         message: "Employee ID is required" 
//       });
//     }
    
//     if (!Array.isArray(asset_ids) || asset_ids.length === 0) {
//       return res.status(400).json({ 
//         success: false, 
//         message: "At least one asset must be selected" 
//       });
//     }

//     // ✅ FIXED: Removed department and designation columns, using department_id and designation_id instead
//     const employeeResult = await client.query(
//       `SELECT 
//          e.employee_id, 
//          CONCAT(e.first_name, ' ', e.last_name) as full_name,
//          e.first_name, 
//          e.last_name, 
//          e.employee_code,
//          e.department_id,
//          e.designation_id,
//          d.dept_name as department,
//          des.designation_name as designation
//        FROM employees e
//        LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
//        LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
//        WHERE e.employee_id = $1 
//          AND e.is_deleted = false 
//          AND e.org_id = $2`,
//       [employee_id, org_id]
//     );
    
//     if (employeeResult.rows.length === 0) {
//       return res.status(404).json({ 
//         success: false, 
//         message: "Employee not found in your organization" 
//       });
//     }
    
//     const employee = employeeResult.rows[0];

//     // Check all requested assets exist, are not deleted, and belong to same org
//     const assetsCheck = await client.query(
//       `SELECT id, asset_name, status, employee_id 
//        FROM assets
//        WHERE id = ANY($1::int[]) 
//          AND is_deleted = false 
//          AND org_id = $2`,
//       [asset_ids, org_id]
//     );

//     // Find any assets that are already assigned to a DIFFERENT employee
//     const alreadyAssigned = assetsCheck.rows.filter(
//       (a) => a.status === "Assigned" && a.employee_id !== employee_id
//     );

//     if (alreadyAssigned.length > 0) {
//       return res.status(400).json({
//         success: false,
//         message: `${alreadyAssigned.length} asset(s) are already assigned to other employees`,
//         conflicted_assets: alreadyAssigned.map(a => ({
//           id: a.id,
//           asset_name: a.asset_name,
//           current_employee_id: a.employee_id
//         })),
//       });
//     }

//     // Find assets not found (invalid IDs or wrong org)
//     const foundIds = assetsCheck.rows.map((a) => a.id);
//     const notFound = asset_ids.filter((id) => !foundIds.includes(id));
    
//     if (notFound.length > 0) {
//       return res.status(404).json({
//         success: false,
//         message: `Some assets were not found: IDs ${notFound.join(", ")}`,
//       });
//     }

//     // Begin transaction — update all assets at once
//     await client.query("BEGIN");

//     const result = await client.query(
//       `UPDATE assets SET
//         employee_id   = $1,
//         employee_name = $2,
//         status        = 'Assigned',
//         assigned_date = NOW(),
//         updated_by    = $3,
//         updated_at    = NOW()
//        WHERE id = ANY($4::int[]) 
//          AND is_deleted = false
//          AND org_id = $5
//        RETURNING *`,
//       [employee_id, employee.full_name, updated_by, asset_ids, org_id]
//     );

//     await client.query("COMMIT");

//     res.status(200).json({
//       success: true,
//       message: `${result.rows.length} asset(s) successfully assigned to ${employee.full_name}`,
//       assigned_to: {
//         employee_id: employee.employee_id,
//         full_name: employee.full_name,
//         employee_code: employee.employee_code,
//         department: employee.department,
//         designation: employee.designation,
//       },
//       data: result.rows,
//     });
    
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("BULK ASSIGN ASSETS ERROR:", error);
//     res.status(500).json({ 
//       success: false, 
//       message: error.message || "Internal server error" 
//     });
//   } finally {
//     client.release();
//   }
// };

/* ===========================
   ✅ BULK ASSIGN MULTIPLE ASSETS TO ONE EMPLOYEE
   POST /assets/bulk-assign
   Body: { employee_id: string, asset_ids: number[] }
=========================== */
const bulkAssignAssets = async (req, res) => {
  const client = await pool.connect();
  try {
    const { employee_id, asset_ids } = req.body;

    // Check authentication
    if (!req.session || !req.session.user) {
      return res.status(401).json({ 
        success: false, 
        message: "User not authenticated" 
      });
    }

    const updated_by = req.session.user.id;
    const org_id = req.session.user.orgId;

    // Validate inputs
    if (!employee_id) {
      return res.status(400).json({ 
        success: false, 
        message: "Employee ID is required" 
      });
    }
    
    if (!Array.isArray(asset_ids) || asset_ids.length === 0) {
      return res.status(400).json({ 
        success: false, 
        message: "At least one asset must be selected" 
      });
    }

    // ✅ FIXED: Removed status condition, only check is_deleted
   const employeeResult = await client.query(
  `SELECT 
     e.employee_id, 
     CONCAT(e.first_name, ' ', e.last_name) as full_name,
     e.first_name, 
     e.last_name, 
     e.employee_code,
     e.department_id,
     e.designation_id,
     d.dept_name as department,
     des.designation_name as designation
   FROM employees e
   LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
   LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
   WHERE (e.employee_id = $1 OR e.id::text = $1)
     AND e.is_deleted = false 
     AND e.org_id = $2`,
  [employee_id, org_id]
);
    
    if (employeeResult.rows.length === 0) {
      return res.status(404).json({ 
        success: false, 
        message: `Employee not found with ID: ${employee_id}` 
      });
    }
    
    const employee = employeeResult.rows[0];

    // Check all requested assets exist
    const assetsCheck = await client.query(
      `SELECT id, asset_name, status, employee_id 
       FROM assets
       WHERE id = ANY($1::int[]) 
         AND is_deleted = false 
         AND org_id = $2`,
      [asset_ids, org_id]
    );

    // Find assets already assigned to a DIFFERENT employee
    const alreadyAssigned = assetsCheck.rows.filter(
      (a) => a.status === "Assigned" && a.employee_id !== employee_id
    );

    if (alreadyAssigned.length > 0) {
      return res.status(400).json({
        success: false,
        message: `${alreadyAssigned.length} asset(s) are already assigned to other employees`,
        conflicted_assets: alreadyAssigned.map(a => ({
          id: a.id,
          asset_name: a.asset_name,
          current_employee_id: a.employee_id
        })),
      });
    }

    const foundIds = assetsCheck.rows.map((a) => a.id);
    const notFound = asset_ids.filter((id) => !foundIds.includes(id));
    
    if (notFound.length > 0) {
      return res.status(404).json({
        success: false,
        message: `Some assets were not found: IDs ${notFound.join(", ")}`,
      });
    }

    await client.query("BEGIN");

    const result = await client.query(
      `UPDATE assets SET
        employee_id   = $1,
        employee_name = $2,
        status        = 'Assigned',
        assigned_date = NOW(),
        updated_by    = $3,
        updated_at    = NOW()
       WHERE id = ANY($4::int[]) 
         AND is_deleted = false
         AND org_id = $5
       RETURNING *`,
      [employee_id, employee.full_name, updated_by, asset_ids, org_id]
    );

    await client.query("COMMIT");

    res.status(200).json({
      success: true,
      message: `${result.rows.length} asset(s) successfully assigned to ${employee.full_name}`,
      assigned_to: {
        employee_id: employee.employee_id,
        full_name: employee.full_name,
        employee_code: employee.employee_code,
        department: employee.department,
        designation: employee.designation,
      },
      data: result.rows,
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("BULK ASSIGN ASSETS ERROR:", error);
    res.status(500).json({ 
      success: false, 
      message: error.message || "Internal server error" 
    });
  } finally {
    client.release();
  }
};
/* ===========================
   ✅ GET AVAILABLE ASSETS
=========================== */
// const getAvailableAssets = async (req, res) => {
//   try {
//     if (!req.session || !req.session.user) {
//       return res.status(401).json({ success: false, message: "User not authenticated" });
//     }

//     const org_id = req.session.user.orgId;

//     let query = `
//       SELECT id, asset_name, s_no, asset_image
//       FROM assets
//       WHERE status = 'Available' AND is_deleted = false
//     `;

//     const queryParams = [];

//     if (org_id) {
//       query += ` AND org_id = $1`;
//       queryParams.push(org_id);
//     }

//     query += ` ORDER BY asset_name`;

//     const result = await pool.query(query, queryParams);

//     res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET AVAILABLE ASSETS ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
/* ===========================
   ✅ GET AVAILABLE ASSETS (UPDATED with more fields)
=========================== */
const getAvailableAssets = async (req, res) => {
  try {
    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;

    let query = `
      SELECT 
        id, 
        asset_name, 
        s_no, 
        asset_image,
        category,
        asset_type,
        brand,
        model,
        description
      FROM assets
      WHERE status = 'Available' AND is_deleted = false
    `;

    const queryParams = [];

    if (org_id) {
      query += ` AND org_id = $1`;
      queryParams.push(org_id);
    }

    query += ` ORDER BY asset_name`;

    const result = await pool.query(query, queryParams);

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET AVAILABLE ASSETS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ===========================
   ✅ GET ASSETS BY EMPLOYEE
=========================== */
const getAssetsByEmployee = async (req, res) => {
  try {
    const { employee_id } = req.params;

    if (!employee_id) {
      return res.status(400).json({ success: false, message: "Employee ID is required" });
    }

    const result = await pool.query(
      `SELECT 
        a.id, 
        a.asset_name, 
        a.asset_image, 
        a.s_no, 
        a.status, 
        a.created_at,
        a.category,
        a.asset_type,
        a.brand,
        a.model,
        a.warranty_expiry,
        a.description
      FROM assets a
      WHERE a.employee_id = $1 
        AND a.is_deleted = false
        AND a.status = 'Assigned'
      ORDER BY a.created_at DESC`,
      [employee_id]
    );

    res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET ASSETS BY EMPLOYEE ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   ✅ UPDATE ASSET STATUS
=========================== */
const updateAssetStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;

    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const updated_by = req.session.user.id;

    const validStatuses = ["Available", "Assigned", "Under Maintenance", "Retired"];

    if (!validStatuses.includes(status)) {
      return res.status(400).json({ success: false, message: "Invalid status value" });
    }

    const result = await pool.query(
      `UPDATE assets
      SET 
        status = $1,
        updated_by = $2,
        updated_at = NOW()
      WHERE id = $3 AND is_deleted = false
      RETURNING *`,
      [status, updated_by, id]
    );

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

    res.status(200).json({
      success: true,
      message: "Asset status updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE ASSET STATUS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* ===========================
   ✅ GET EMPLOYEES WITH THEIR ASSIGNED ASSETS (FIXED)
=========================== */
const getEmployeesWithAssets = async (req, res) => {
  try {
    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;

    const query = `
      SELECT 
        e.employee_id, 
        e.employee_code, 
        e.first_name, 
        e.last_name,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email,
        e.phone,
        COALESCE(
          (
            SELECT JSON_AGG(
              JSON_BUILD_OBJECT(
                'id', a.id,
                'asset_name', a.asset_name,
                'asset_image', a.asset_image,
                's_no', a.s_no,
                'status', a.status,
                'assigned_date', a.assigned_date,
                'category', a.category,
                'asset_type', a.asset_type,
                'brand', a.brand,
                'model', a.model,
                'warranty_expiry', a.warranty_expiry,
                'description', a.description
              ) ORDER BY a.assigned_date DESC
            )
            FROM assets a
            WHERE a.employee_id = e.employee_id 
              AND a.is_deleted = false 
              AND a.status = 'Assigned'
          ),
          '[]'::json
        ) as assets,
        (
          SELECT COUNT(*)
          FROM assets a
          WHERE a.employee_id = e.employee_id 
            AND a.is_deleted = false 
            AND a.status = 'Assigned'
        ) as asset_count
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE e.is_deleted = false 
      
        AND e.org_id = $1
        AND EXISTS (
          SELECT 1 FROM assets a 
          WHERE a.employee_id = e.employee_id 
            AND a.is_deleted = false 
            AND a.status = 'Assigned'
        )
      ORDER BY e.first_name, e.last_name
    `;

    const result = await pool.query(query, [org_id]);

    res.json({ 
      success: true, 
      data: result.rows 
    });
  } catch (err) {
    console.error("GET EMPLOYEES WITH ASSETS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};



// /* ===========================
//    ✅ GET ALL EMPLOYEES FOR ASSIGNMENT
// =========================== */
// const getAllEmployeesForAssign = async (req, res) => {
//   try {
//     if (!req.session || !req.session.user) {
//       return res.status(401).json({ success: false, message: "User not authenticated" });
//     }

//     const org_id = req.session.user.orgId;
//     const { search } = req.query;

//     console.log("=== GET ALL EMPLOYEES ===");
//     console.log("Org ID:", org_id);
//     console.log("Search:", search);

//     let query = `
//       SELECT 
//         e.employee_id, 
//         e.employee_code, 
//         e.first_name, 
//         e.last_name,
//         COALESCE(d.dept_name, 'Unknown') AS department,
//         COALESCE(des.designation_name, 'Unknown') AS designation,
//         e.email,
//         e.phone,
//         e.status,
//         e.joining_date
//       FROM employees e
//       LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
//       LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
//       WHERE e.is_deleted = false 
       
//         AND e.org_id = $1
//     `;
    
//     const queryParams = [org_id];
//     let paramCount = 2;
    
//     if (search && search.trim()) {
//       query += ` AND (
//         e.first_name ILIKE $${paramCount} OR 
//         e.last_name ILIKE $${paramCount} OR 
//         e.employee_code ILIKE $${paramCount} OR
//         e.department ILIKE $${paramCount}
//       )`;
//       queryParams.push(`%${search}%`);
//       paramCount++;
//     }
    
//     query += ` ORDER BY e.first_name, e.last_name`;

//     const result = await pool.query(query, queryParams);
    
//     console.log(`Found ${result.rows.length} employees`);
    
//     // Get asset count for each employee
//     const employeesWithCount = await Promise.all(result.rows.map(async (emp) => {
//       const assetCountResult = await pool.query(
//         `SELECT COUNT(*) as count FROM assets 
//          WHERE employee_id = $1 AND is_deleted = false AND status = 'Assigned'`,
//         [emp.employee_id]
//       );
//       return {
//         ...emp,
//         asset_count: parseInt(assetCountResult.rows[0].count)
//       };
//     }));

//     res.json({ 
//       success: true, 
//       data: employeesWithCount,
//       total: employeesWithCount.length 
//     });
//   } catch (err) {
//     console.error("GET ALL EMPLOYEES ERROR:", err);
//     res.status(500).json({ success: false, message: err.message });
//   }
// };
/* ===========================
   ✅ GET ALL EMPLOYEES FOR ASSIGNMENT (without status condition)
=========================== */
const getAllEmployeesForAssign = async (req, res) => {
  try {
    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;
    const { search } = req.query;

    let query = `
      SELECT 
        e.employee_id, 
        e.employee_code, 
        e.first_name, 
        e.last_name,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email,
        e.phone,
        e.joining_date
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      WHERE e.is_deleted = false 
        AND e.org_id = $1
    `;
    
    const queryParams = [org_id];
    let paramCount = 2;
    
    if (search && search.trim()) {
      query += ` AND (
        e.first_name ILIKE $${paramCount} OR 
        e.last_name ILIKE $${paramCount} OR 
        e.employee_code ILIKE $${paramCount}
      )`;
      queryParams.push(`%${search}%`);
      paramCount++;
    }
    
    query += ` ORDER BY e.first_name, e.last_name`;

    const result = await pool.query(query, queryParams);
    
    // Get asset count for each employee
    const employeesWithCount = await Promise.all(result.rows.map(async (emp) => {
      const assetCountResult = await pool.query(
        `SELECT COUNT(*) as count FROM assets 
         WHERE employee_id = $1 AND is_deleted = false AND status = 'Assigned'`,
        [emp.employee_id]
      );
      return {
        ...emp,
        asset_count: parseInt(assetCountResult.rows[0].count)
      };
    }));

    res.json({ 
      success: true, 
      data: employeesWithCount,
      total: employeesWithCount.length 
    });
  } catch (err) {
    console.error("GET ALL EMPLOYEES ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};
/* ===========================
   ✅ GET EMPLOYEES WITH NO ASSETS
=========================== */
const getEmployeesWithNoAssets = async (req, res) => {
  try {
    if (!req.session || !req.session.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;
    const { search } = req.query;

    console.log("=== GET EMPLOYEES WITH NO ASSETS ===");
    console.log("Org ID:", org_id);
    console.log("Search:", search);

    let query = `
      SELECT 
        e.employee_id, 
        e.employee_code, 
        e.first_name, 
        e.last_name,
        COALESCE(d.dept_name, 'Unknown') AS department,
        COALESCE(des.designation_name, 'Unknown') AS designation,
        e.email,
        e.phone,
        e.status,
        e.joining_date,
        COALESCE(a.asset_count, 0) as asset_count
      FROM employees e
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      LEFT JOIN (
        SELECT employee_id, COUNT(*) as asset_count
        FROM assets
        WHERE is_deleted = false AND status = 'Assigned'
        GROUP BY employee_id
      ) a ON e.employee_id = a.employee_id
      WHERE e.is_deleted = false 
      
        AND e.org_id = $1
        AND (a.asset_count IS NULL OR a.asset_count = 0)
    `;
    
    const queryParams = [org_id];
    let paramCount = 2;
    
    if (search && search.trim()) {
      query += ` AND (
        e.first_name ILIKE $${paramCount} OR 
        e.last_name ILIKE $${paramCount} OR 
        e.employee_code ILIKE $${paramCount} OR
        e.department ILIKE $${paramCount}
      )`;
      queryParams.push(`%${search}%`);
      paramCount++;
    }
    
    query += ` ORDER BY e.first_name, e.last_name`;

    const result = await pool.query(query, queryParams);
    
    console.log(`Found ${result.rows.length} employees with no assets`);

    res.json({ 
      success: true, 
      data: result.rows,
      total: result.rows.length 
    });
  } catch (err) {
    console.error("GET EMPLOYEES WITH NO ASSETS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/**
 * ============================================================
 *  ASSET RETURN — Controller Functions
 *  Add these to your existing assetsController.js
 * ============================================================
 *
 *  Prerequisites — run this migration once in your DB:
 *
 *  CREATE TABLE IF NOT EXISTS asset_return_logs (
 *    id            SERIAL PRIMARY KEY,
 *    asset_id      INTEGER NOT NULL REFERENCES assets(id),
 *    asset_name    VARCHAR(255),
 *    s_no          VARCHAR(255),
 *    employee_id   VARCHAR(255),
 *    employee_name VARCHAR(255),
 *    return_date   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 *    condition     VARCHAR(50)  NOT NULL DEFAULT 'Good',
 *    notes         TEXT,
 *    returned_by   INTEGER REFERENCES users(id),
 *    org_id        VARCHAR(255),
 *    created_at    TIMESTAMPTZ DEFAULT NOW()
 *  );
 *
 *  ALTER TABLE assets
 *    ADD COLUMN IF NOT EXISTS condition VARCHAR(50) DEFAULT 'Good',
 *    ADD COLUMN IF NOT EXISTS last_returned_at TIMESTAMPTZ,
 *    ADD COLUMN IF NOT EXISTS return_count INTEGER DEFAULT 0;
 *
 * ============================================================
 */



/* ===========================
   RETURN A SINGLE ASSET
   PATCH /assets/:id/return
   Body: { condition, notes }
=========================== */
const returnAsset = async (req, res) => {
  const client = await pool.connect();
  try {
    const { id } = req.params;
    const { condition = "Good", notes = "" } = req.body;

    if (!req.session?.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const returned_by = req.session.user.id;
    const org_id = req.session.user.orgId;

    // Validate condition value
    const validConditions = ["Good", "Damaged", "Lost"];
    if (!validConditions.includes(condition)) {
      return res.status(400).json({
        success: false,
        message: `Invalid condition. Must be one of: ${validConditions.join(", ")}`,
      });
    }

    // Fetch asset
    const assetResult = await client.query(
      `SELECT * FROM assets WHERE id = $1 AND is_deleted = false AND org_id = $2`,
      [id, org_id]
    );

    if (!assetResult.rows.length) {
      return res.status(404).json({ success: false, message: "Asset not found" });
    }

    const asset = assetResult.rows[0];

    if (asset.status !== "Assigned") {
      return res.status(400).json({
        success: false,
        message: `Asset cannot be returned — current status is "${asset.status}"`,
      });
    }

    // Determine new status based on condition
    const newStatus = condition === "Damaged"
      ? "Under Maintenance"
      : condition === "Lost"
      ? "Retired"
      : "Available";

    await client.query("BEGIN");

    // Update the asset
    const updatedAsset = await client.query(
      `UPDATE assets
       SET
         status            = $1,
         employee_id       = NULL,
         employee_name     = NULL,
         assigned_date     = NULL,
         condition         = $2,
         last_returned_at  = NOW(),
         return_count      = COALESCE(return_count, 0) + 1,
         updated_by        = $3,
         updated_at        = NOW()
       WHERE id = $4
       RETURNING *`,
      [newStatus, condition, returned_by, id]
    );

    // Write to return log
    await client.query(
      `INSERT INTO asset_return_logs
         (asset_id, asset_name, s_no, employee_id, employee_name,
          return_date, condition, notes, returned_by, org_id)
       VALUES ($1, $2, $3, $4, $5, NOW(), $6, $7, $8, $9)`,
      [
        asset.id,
        asset.asset_name,
        asset.s_no,
        asset.employee_id,
        asset.employee_name,
        condition,
        notes || null,
        returned_by,
        org_id,
      ]
    );

    await client.query("COMMIT");

    res.status(200).json({
      success: true,
      message: `Asset "${asset.asset_name}" returned successfully. New status: ${newStatus}`,
      data: updatedAsset.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("RETURN ASSET ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

/* ===========================
   BULK RETURN MULTIPLE ASSETS FROM ONE EMPLOYEE
   POST /assets/bulk-return
   Body: { employee_id, asset_ids: number[], returns: [{ asset_id, condition, notes }] }
=========================== */
const bulkReturnAssets = async (req, res) => {
  const client = await pool.connect();
  try {
    const { employee_id, returns } = req.body;
    // returns = [{ asset_id, condition, notes }, ...]

    if (!req.session?.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const returned_by = req.session.user.id;
    const org_id = req.session.user.orgId;

    if (!employee_id) {
      return res.status(400).json({ success: false, message: "employee_id is required" });
    }

    if (!Array.isArray(returns) || returns.length === 0) {
      return res.status(400).json({ success: false, message: "At least one asset must be selected for return" });
    }

    const validConditions = ["Good", "Damaged", "Lost"];
    for (const r of returns) {
      if (!validConditions.includes(r.condition)) {
        return res.status(400).json({
          success: false,
          message: `Invalid condition "${r.condition}" for asset ID ${r.asset_id}`,
        });
      }
    }

    const asset_ids = returns.map((r) => r.asset_id);

    // Validate all assets belong to this employee and org
    const assetsCheck = await client.query(
      `SELECT id, asset_name, s_no, employee_id, employee_name, status
       FROM assets
       WHERE id = ANY($1::int[])
         AND is_deleted = false
         AND org_id = $2`,
      [asset_ids, org_id]
    );

    const foundIds = assetsCheck.rows.map((a) => a.id);
    const notFound = asset_ids.filter((id) => !foundIds.includes(id));
    if (notFound.length > 0) {
      return res.status(404).json({
        success: false,
        message: `Assets not found: IDs ${notFound.join(", ")}`,
      });
    }

    const notAssigned = assetsCheck.rows.filter((a) => a.status !== "Assigned");
    if (notAssigned.length > 0) {
      return res.status(400).json({
        success: false,
        message: `Some assets are not currently assigned: ${notAssigned.map((a) => a.asset_name).join(", ")}`,
      });
    }

    const wrongEmployee = assetsCheck.rows.filter(
      (a) => a.employee_id !== employee_id
    );
    if (wrongEmployee.length > 0) {
      return res.status(400).json({
        success: false,
        message: `Some assets are not assigned to this employee: ${wrongEmployee.map((a) => a.asset_name).join(", ")}`,
      });
    }

    await client.query("BEGIN");

    const results = [];

    for (const ret of returns) {
      const asset = assetsCheck.rows.find((a) => a.id === ret.asset_id);
      const condition = ret.condition || "Good";
      const notes = ret.notes || null;

      const newStatus =
        condition === "Damaged" ? "Under Maintenance"
        : condition === "Lost"   ? "Retired"
        : "Available";

      const updated = await client.query(
        `UPDATE assets
         SET
           status           = $1,
           employee_id      = NULL,
           employee_name    = NULL,
           assigned_date    = NULL,
           condition        = $2,
           last_returned_at = NOW(),
           return_count     = COALESCE(return_count, 0) + 1,
           updated_by       = $3,
           updated_at       = NOW()
         WHERE id = $4
         RETURNING *`,
        [newStatus, condition, returned_by, ret.asset_id]
      );

      await client.query(
        `INSERT INTO asset_return_logs
           (asset_id, asset_name, s_no, employee_id, employee_name,
            return_date, condition, notes, returned_by, org_id)
         VALUES ($1, $2, $3, $4, $5, NOW(), $6, $7, $8, $9)`,
        [
          asset.id,
          asset.asset_name,
          asset.s_no,
          employee_id,
          asset.employee_name,
          condition,
          notes,
          returned_by,
          org_id,
        ]
      );

      results.push({
        asset_id: ret.asset_id,
        asset_name: asset.asset_name,
        condition,
        new_status: newStatus,
      });
    }

    await client.query("COMMIT");

    res.status(200).json({
      success: true,
      message: `${results.length} asset(s) returned successfully`,
      data: results,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("BULK RETURN ASSETS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

/* ===========================
   GET RETURN HISTORY
   GET /assets/return-logs
   Query: ?page, limit, search, condition, from_date, to_date, employee_id
=========================== */
const getReturnLogs = async (req, res) => {
  try {
    if (!req.session?.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;
    const {
      page = 1,
      limit = 15,
      search = "",
      condition = "",
      from_date = "",
      to_date = "",
      employee_id = "",
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const params = [org_id];
    let paramIdx = 2;
    let where = `WHERE arl.org_id = $1`;

    if (search) {
      where += ` AND (arl.asset_name ILIKE $${paramIdx} OR arl.employee_name ILIKE $${paramIdx} OR arl.s_no ILIKE $${paramIdx})`;
      params.push(`%${search}%`);
      paramIdx++;
    }

    if (condition) {
      where += ` AND arl.condition = $${paramIdx}`;
      params.push(condition);
      paramIdx++;
    }

    if (employee_id) {
      where += ` AND arl.employee_id = $${paramIdx}`;
      params.push(employee_id);
      paramIdx++;
    }

    if (from_date) {
      where += ` AND arl.return_date >= $${paramIdx}`;
      params.push(from_date);
      paramIdx++;
    }

    if (to_date) {
      where += ` AND arl.return_date <= $${paramIdx}::date + INTERVAL '1 day'`;
      params.push(to_date);
      paramIdx++;
    }

    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM asset_return_logs arl ${where}`,
      params
    );

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

    const dataResult = await pool.query(
      `SELECT
         arl.*,
         u.first_name  AS returned_by_first_name,
         u.last_name   AS returned_by_last_name,
         a.asset_image,
         a.category,
         a.brand,
         a.model,
         a.status      AS current_status
       FROM asset_return_logs arl
       LEFT JOIN users u   ON arl.returned_by = u.id
       LEFT JOIN assets a  ON arl.asset_id = a.id
       ${where}
       ORDER BY arl.return_date DESC
       LIMIT $${paramIdx} OFFSET $${paramIdx + 1}`,
      [...params, parseInt(limit), offset]
    );

    res.json({
      success: true,
      data: dataResult.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalRecords: total,
        recordsPerPage: parseInt(limit),
      },
    });
  } catch (err) {
    console.error("GET RETURN LOGS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ===========================
   GET RETURN STATS (dashboard summary)
   GET /assets/return-stats
=========================== */
const getReturnStats = async (req, res) => {
  try {
    if (!req.session?.user) {
      return res.status(401).json({ success: false, message: "User not authenticated" });
    }

    const org_id = req.session.user.orgId;

    const result = await pool.query(
      `SELECT
         COUNT(*)                                             AS total_returns,
         COUNT(*) FILTER (WHERE condition = 'Good')          AS good_condition,
         COUNT(*) FILTER (WHERE condition = 'Damaged')       AS damaged,
         COUNT(*) FILTER (WHERE condition = 'Lost')          AS lost,
         COUNT(*) FILTER (WHERE return_date >= NOW() - INTERVAL '30 days') AS last_30_days
       FROM asset_return_logs
       WHERE org_id = $1`,
      [org_id]
    );

    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET RETURN STATS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};


module.exports = {
  createAsset,
  getAllAssets,
  getAssetById,
  updateAsset,
  deleteAsset,
  assignAssetToEmployee,
  getAvailableAssets,
  getAssetsByEmployee,
  updateAssetStatus,
  getEmployeesWithAssets,
    bulkAssignAssets,  // ✅ NEW
    getAllEmployeesForAssign,    // ✅ ADD THIS
  getEmployeesWithNoAssets,
   returnAsset,
  bulkReturnAssets,
  getReturnLogs,
  getReturnStats, 
};