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

/* ===========================
   ✅ CREATE EXPENSE REIMBURSEMENT
=========================== */

// const createExpenseReimbursement = async (req, res) => {
//   try {
//     console.log("=== EXPENSE REIMBURSEMENT DEBUG ===");
//     console.log("req.body:", req.body);
//     console.log("req.files:", req.files);

//     let {
//       employee_id: employeeCode,
//       expense_date,
//       expense_type,
//       expense_amount,
//       currency,
//       description,
//       org_id,
//       created_by
//     } = req.body;

//     // Validation
//     if (!employeeCode || !expense_date || !expense_type || !expense_amount || !org_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Employee, expense date, expense type, expense amount, and org_id are required",
//       });
//     }

//     // Fetch real employee id from employees table
//     const employeeResult = await pool.query(
//       `SELECT id 
//        FROM employees 
//        WHERE employee_code = $1 AND is_deleted = false`,
//       [employeeCode]
//     );

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


//     // Add this after fetching the employee, before inserting
// // Check for duplicate expense (same employee on same day with same type)
// const duplicateCheck = await pool.query(
//   `SELECT id, expense_date, expense_type, expense_amount 
//    FROM expense_reimbursement 
//    WHERE employee_id = $1 
//      AND expense_date = $2 
//      AND expense_type = $3 
//      AND is_deleted = false`,
//   [realEmployeeId, expense_date, expense_type]
// );

// if (duplicateCheck.rows.length > 0) {
//   return res.status(409).json({
//     success: false,
//     message: `An expense of type "${expense_type}" already exists for this employee on ${expense_date}. Please check or modify the existing expense.`,
//     duplicate: duplicateCheck.rows[0]
//   });
// }

//     const realEmployeeId = employeeResult.rows[0].id;

//     // Handle receipt upload - store original filename
//     const receipt_file = req.files?.receipt_upload?.[0]?.originalname || null;

//     console.log("Receipt file:", receipt_file);

//     // Insert expense record
//     const result = await pool.query(
//       `
//       INSERT INTO expense_reimbursement (
//         employee_id,
//         expense_date,
//         expense_type,
//         expense_amount,
//         currency,
//         receipt_upload,
//         description,
//         org_id,
//         created_by,
//         created_at,
//         updated_at
//       )
//       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
//       RETURNING *
//       `,
//       [
//         realEmployeeId,
//         expense_date,
//         expense_type,
//         expense_amount,
//         currency || "USD",
//         receipt_file,
//         description || null,
//         org_id,
//         created_by || null
//       ]
//     );

//     res.status(201).json({
//       success: true,
//       message: "Expense reimbursement created successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     console.error("CREATE EXPENSE REIMBURSEMENT ERROR:", error);
//     res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   }
// };
const createExpenseReimbursement = async (req, res) => {
  try {
    console.log("=== EXPENSE REIMBURSEMENT DEBUG ===");
    console.log("req.body:", req.body);
    console.log("req.files:", req.files);

    let {
      employee_id: employeeCode,
      expense_date,
      expense_type,
      expense_amount,
      currency,
      description,
      org_id,
      created_by
    } = req.body;

    // Validation
    if (!employeeCode || !expense_date || !expense_type || !expense_amount || !org_id) {
      return res.status(400).json({
        success: false,
        message: "Employee, expense date, expense type, expense amount, and org_id are required",
      });
    }

    // Fetch real employee id from employees table
    const employeeResult = await pool.query(
      `SELECT id 
       FROM employees 
       WHERE employee_code = $1 AND is_deleted = false`,
      [employeeCode]
    );

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

    // ✅ Initialize realEmployeeId HERE before using it
    const realEmployeeId = employeeResult.rows[0].id;

    // ✅ DUPLICATE CHECK - Now realEmployeeId is defined
    const duplicateCheck = await pool.query(
      `SELECT id, expense_date, expense_type, expense_amount 
       FROM expense_reimbursement 
       WHERE employee_id = $1 
         AND expense_date = $2 
         AND expense_type = $3 
         AND is_deleted = false`,
      [realEmployeeId, expense_date, expense_type]
    );

    if (duplicateCheck.rows.length > 0) {
      return res.status(409).json({
        success: false,
        message: `An expense of type "${expense_type}" already exists for this employee on ${expense_date}. Amount: ${duplicateCheck.rows[0].expense_amount} ${currency}`,
        duplicate: duplicateCheck.rows[0]
      });
    }

    // Handle receipt upload - store original filename
    const receipt_file = req.files?.receipt_upload?.[0]?.originalname || null;

    console.log("Receipt file:", receipt_file);

    // Insert expense record
    const result = await pool.query(
      `
      INSERT INTO expense_reimbursement (
        employee_id,
        expense_date,
        expense_type,
        expense_amount,
        currency,
        receipt_upload,
        description,
        org_id,
        created_by,
        created_at,
        updated_at
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW())
      RETURNING *
      `,
      [
        realEmployeeId,
        expense_date,
        expense_type,
        expense_amount,
        currency || "USD",
        receipt_file,
        description || null,
        org_id,
        created_by || null
      ]
    );

    res.status(201).json({
      success: true,
      message: "Expense reimbursement created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE EXPENSE REIMBURSEMENT ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};
/* ===========================
   ✅ GET ALL EXPENSES (with filters)
=========================== */

const getExpenseReimbursements = async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 10, 
      employee_id,
      expense_type,
      start_date,
      end_date,
      org_id 
    } = req.query;
    
    const offset = (page - 1) * limit;
    let query = `
      SELECT
        er.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation
      FROM expense_reimbursement er
      JOIN employees e ON er.employee_id = e.id
      WHERE er.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    // Apply filters
    if (employee_id) {
      query += ` AND e.employee_code = $${paramIndex}`;
      queryParams.push(employee_id);
      paramIndex++;
    }
    
    if (expense_type) {
      query += ` AND er.expense_type ILIKE $${paramIndex}`;
      queryParams.push(`%${expense_type}%`);
      paramIndex++;
    }
    
    if (start_date) {
      query += ` AND er.expense_date >= $${paramIndex}`;
      queryParams.push(start_date);
      paramIndex++;
    }
    
    if (end_date) {
      query += ` AND er.expense_date <= $${paramIndex}`;
      queryParams.push(end_date);
      paramIndex++;
    }
    
    if (org_id) {
      query += ` AND er.org_id = $${paramIndex}`;
      queryParams.push(org_id);
      paramIndex++;
    }
    
    // Get total count
    const countQuery = query.replace(
      /SELECT.*FROM/,
      "SELECT COUNT(*) as total FROM"
    );
    const countResult = await pool.query(countQuery, queryParams);
    const totalRecords = parseInt(countResult.rows[0].total);
    
    // Add pagination
    query += ` ORDER BY er.expense_date DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    queryParams.push(limit, offset);
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(totalRecords / limit),
        totalRecords: totalRecords,
        recordsPerPage: parseInt(limit)
      }
    });
  } catch (err) {
    console.error("FETCH EXPENSES ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET EXPENSE BY ID
=========================== */

const getExpenseReimbursementById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      SELECT
        er.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        e.designation,
        e.email,
        e.phone
      FROM expense_reimbursement er
      LEFT JOIN employees e ON er.employee_id = e.id
      WHERE er.id = $1 AND er.is_deleted = false
      `,
      [id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Expense record not found",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET EXPENSE BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ UPDATE EXPENSE REIMBURSEMENT
=========================== */

const updateExpenseReimbursement = async (req, res) => {
  try {
    const { id } = req.params;
    const {
      expense_date,
      expense_type,
      expense_amount,
      currency,
      description,
      updated_by
    } = req.body;
    
    // Check if expense exists and not deleted
    const existing = await pool.query(
      `SELECT * FROM expense_reimbursement WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    
    if (!existing.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Expense record not found",
      });
    }
    
    const old = existing.rows[0];
    
    // Handle new receipt upload
    let receipt_file = old.receipt_upload;
    if (req.files?.receipt_upload?.[0]) {
      receipt_file = req.files.receipt_upload[0].originalname;
    }
    
    const result = await pool.query(
      `
      UPDATE expense_reimbursement
      SET
        expense_date = COALESCE($1, expense_date),
        expense_type = COALESCE($2, expense_type),
        expense_amount = COALESCE($3, expense_amount),
        currency = COALESCE($4, currency),
        receipt_upload = COALESCE($5, receipt_upload),
        description = COALESCE($6, description),
        updated_by = COALESCE($7, updated_by),
        updated_at = NOW()
      WHERE id = $8 AND is_deleted = false
      RETURNING *
      `,
      [
        expense_date || old.expense_date,
        expense_type || old.expense_type,
        expense_amount || old.expense_amount,
        currency || old.currency,
        receipt_file,
        description || old.description,
        updated_by || old.updated_by,
        id
      ]
    );
    
    res.status(200).json({
      success: true,
      message: "Expense updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE EXPENSE ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

/* ===========================
   ✅ DELETE EXPENSE (Soft Delete)
=========================== */

// const deleteExpenseReimbursement = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const { updated_by } = req.body;
    
//     const result = await pool.query(
//       `
//       UPDATE expense_reimbursement
//       SET is_deleted = true,
//           updated_by = COALESCE($1, updated_by),
//           updated_at = NOW()
//       WHERE id = $2 AND is_deleted = false
//       RETURNING id
//       `,
//       [updated_by || null, id]
//     );
    
//     if (result.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Expense record not found",
//       });
//     }
    
//     res.json({
//       success: true,
//       message: "Expense deleted successfully ✅",
//     });
//   } catch (err) {
//     console.error("DELETE EXPENSE ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: "Delete failed",
//     });
//   }
// };
/* ===========================
   ✅ DELETE EXPENSE (Soft Delete)
=========================== */


const deleteExpenseReimbursement = async (req, res) => {
  try {
    const { id } = req.params;
    
    // Get updated_by from query params or body (make it optional)
    const updated_by = req.query.updated_by || req.body?.updated_by || null;
    
    console.log("Deleting expense with ID:", id);
    console.log("Updated by:", updated_by);
    
    // Validate ID
    if (!id || isNaN(parseInt(id))) {
      return res.status(400).json({
        success: false,
        message: "Valid expense ID is required",
      });
    }
    
    // First check if expense exists and is not already deleted
    const checkResult = await pool.query(
      `SELECT id FROM expense_reimbursement 
       WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    
    if (checkResult.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Expense record not found or already deleted",
      });
    }
    
    // Perform soft delete
    const result = await pool.query(
      `
      UPDATE expense_reimbursement
      SET is_deleted = true,
          updated_by = COALESCE($1, updated_by),
          updated_at = NOW()
      WHERE id = $2 AND is_deleted = false
      RETURNING id
      `,
      [updated_by, id]
    );
    
    if (result.rowCount === 0) {
      return res.status(404).json({
        success: false,
        message: "Expense record not found",
      });
    }
    
    console.log("Expense deleted successfully");
    
    res.json({
      success: true,
      message: "Expense deleted successfully",
    });
  } catch (err) {
    console.error("DELETE EXPENSE ERROR DETAILS:", err);
    console.error("Error stack:", err.stack);
    res.status(500).json({
      success: false,
      message: err.message || "Delete failed",
    });
  }
};
/* ===========================
   ✅ GET EXPENSE SUMMARY BY EMPLOYEE
=========================== */

const getExpenseSummary = async (req, res) => {
  try {
    const { employee_code, year, month, org_id } = req.query;
    
    let query = `
      SELECT
        e.employee_code,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.department,
        COUNT(er.id) as total_expenses,
        SUM(er.expense_amount) as total_amount,
        er.currency,
        MIN(er.expense_date) as first_expense_date,
        MAX(er.expense_date) as last_expense_date
      FROM employees e
      LEFT JOIN expense_reimbursement er ON e.id = er.employee_id AND er.is_deleted = false
      WHERE e.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    if (employee_code) {
      query += ` AND e.employee_code = $${paramIndex}`;
      queryParams.push(employee_code);
      paramIndex++;
    }
    
    if (org_id) {
      query += ` AND er.org_id = $${paramIndex}`;
      queryParams.push(org_id);
      paramIndex++;
    }
    
    if (year) {
      query += ` AND EXTRACT(YEAR FROM er.expense_date) = $${paramIndex}`;
      queryParams.push(year);
      paramIndex++;
    }
    
    if (month) {
      query += ` AND EXTRACT(MONTH FROM er.expense_date) = $${paramIndex}`;
      queryParams.push(month);
      paramIndex++;
    }
    
    query += ` GROUP BY e.id, e.employee_code, e.first_name, e.last_name, e.department, er.currency
               ORDER BY total_amount DESC NULLS LAST`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
    });
  } catch (err) {
    console.error("GET EXPENSE SUMMARY ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET EXPENSE TYPES (Fixed version)
=========================== */

const getExpenseTypes = async (req, res) => {
  try {
    const { org_id } = req.query;
    
    console.log("Fetching expense types for org_id:", org_id);
    
    // First, check if the expense_reimbursement table exists
    const tableCheck = await pool.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_name = 'expense_reimbursement'
      );
    `);
    
    console.log("Table exists:", tableCheck.rows[0].exists);
    
    if (!tableCheck.rows[0].exists) {
      // Table doesn't exist yet, return default types
      console.log("Table doesn't exist, returning default types");
      return res.json({
        success: true,
        data: ["Travel", "Meals", "Office Supplies", "Accommodation", "Transportation", "Communication", "Training", "Entertainment", "Medical", "Other"],
      });
    }
    
    // Build query to get distinct expense types
    let query = `
      SELECT DISTINCT expense_type 
      FROM expense_reimbursement 
      WHERE is_deleted = false
    `;
    
    const queryParams = [];
    
    if (org_id) {
      query += ` AND org_id = $1`;
      queryParams.push(org_id);
    }
    
    query += ` ORDER BY expense_type`;
    
    console.log("Executing query:", query);
    
    const result = await pool.query(query, queryParams);
    console.log("Query result rows:", result.rows.length);
    
    const expenseTypes = result.rows.map(row => row.expense_type);
    
    // If no types found, return default list
    if (expenseTypes.length === 0) {
      console.log("No types found, returning default types");
      return res.json({
        success: true,
        data: ["Travel", "Meals", "Office Supplies", "Accommodation", "Transportation", "Communication", "Training", "Entertainment", "Medical", "Other"],
      });
    }
    
    res.json({
      success: true,
      data: expenseTypes,
    });
  } catch (err) {
    console.error("GET EXPENSE TYPES ERROR DETAILS:", err);
    console.error("Error stack:", err.stack);
    
    // Return default types instead of error
    res.json({
      success: true,
      data: ["Travel", "Meals", "Office Supplies", "Accommodation", "Transportation", "Communication", "Training", "Entertainment", "Medical", "Other"],
    });
  }
};

/* ===========================
   ✅ GET EXPENSES BY ORG_ID
=========================== */

const getExpensesByOrgId = async (req, res) => {
  try {
    const { org_id } = req.params;
    const { page = 1, limit = 10 } = req.query;
    const offset = (page - 1) * limit;
    
    const result = await pool.query(
      `
      SELECT
        er.*,
        e.employee_code,
        e.first_name,
        e.last_name,
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name
      FROM expense_reimbursement er
      JOIN employees e ON er.employee_id = e.id
      WHERE er.org_id = $1 AND er.is_deleted = false
      ORDER BY er.expense_date DESC
      LIMIT $2 OFFSET $3
      `,
      [org_id, limit, offset]
    );
    
    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM expense_reimbursement WHERE org_id = $1 AND is_deleted = false`,
      [org_id]
    );
    
    res.json({
      success: true,
      data: result.rows,
      pagination: {
        currentPage: parseInt(page),
        totalRecords: parseInt(countResult.rows[0].total),
        recordsPerPage: parseInt(limit)
      }
    });
  } catch (err) {
    console.error("GET EXPENSES BY ORG ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

module.exports = {
  createExpenseReimbursement,
  getExpenseReimbursements,
  getExpenseReimbursementById,
  updateExpenseReimbursement,
  deleteExpenseReimbursement,
  getExpenseSummary,
  getExpenseTypes,
  getExpensesByOrgId
};