const { pool } = require("../../config/db");
// const { sendPOEmailOnFullApproval } = require("./purchaseOrderController");
// Helper function to call approveInvoice

const salesInvoiceController = require("../../controllers/sales/salesInvoiceController");
const approveInvoice = salesInvoiceController.approveInvoice;


const callApproveInvoice = async (invoiceId, userId, orgId) => {
  const mockReq = {
    params: { id: invoiceId },
    session: { user: { id: userId, orgId: orgId } }
  };
  
  let responseData = null;
  const mockRes = {
    status: (code) => ({
      json: (data) => {
        responseData = data;
        return data;
      }
    })
  };
  
  await approveInvoice(mockReq, mockRes);
  return responseData;
};

// ==================== GET MODULE TYPES ====================
const getModuleTypes = 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, module_name, module_code, description, is_active
       FROM public.module_types
       WHERE is_active = true
       ORDER BY id`
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("Error in getModuleTypes:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== GET ALL USERS ====================
const getUsers = 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, first_name, last_name, email, role, department, is_active
       FROM public.users
       WHERE is_active = true
       ORDER BY first_name, last_name`
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("Error in getUsers:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== GET WORKFLOW BY MODULE TYPE ====================
const getWorkflowByModule = async (req, res) => {
  try {
    const { moduleTypeId } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    if (!moduleTypeId) return res.status(400).json({ success: false, message: "Module Type ID is required" });

    const result = await pool.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              aw.is_mandatory, aw.max_approval_days,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.users u ON aw.user_id = u.id
       WHERE aw.module_type_id = $1
       ORDER BY aw.approval_order`,
      [parseInt(moduleTypeId)]
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("Error in getWorkflowByModule:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== SAVE WORKFLOW ====================
const saveWorkflow = async (req, res) => {
  const client = await pool.connect();
  try {
    const { moduleTypeId, userIds } = req.body;
    const orgId = req.user?.orgId;
    const createdBy = req.user?.userId || req.user?.email || req.user?.id;

    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    if (!moduleTypeId) return res.status(400).json({ success: false, message: "Module Type ID is required" });
    if (!userIds || userIds.length === 0) return res.status(400).json({ success: false, message: "At least one user is required for approval workflow" });

    await client.query("BEGIN");
    await client.query(`DELETE FROM public.approval_workflows WHERE module_type_id = $1`, [parseInt(moduleTypeId)]);

    for (let i = 0; i < userIds.length; i++) {
      await client.query(
        `INSERT INTO public.approval_workflows (module_type_id, user_id, approval_order, created_by, created_at, updated_at)
         VALUES ($1, $2, $3, $4, NOW(), NOW())`,
        [parseInt(moduleTypeId), parseInt(userIds[i]), i + 1, createdBy]
      );
    }
    await client.query("COMMIT");

    const savedWorkflow = await client.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.users u ON aw.user_id = u.id
       WHERE aw.module_type_id = $1
       ORDER BY aw.approval_order`,
      [parseInt(moduleTypeId)]
    );

    const moduleResult = await pool.query(
      `SELECT module_name FROM public.module_types WHERE id = $1`,
      [parseInt(moduleTypeId)]
    );
    const moduleName = moduleResult.rows[0]?.module_name || "";

    res.json({
      success: true,
      message: `Approval workflow saved for ${moduleName}`,
      data: savedWorkflow.rows,
      totalApprovers: userIds.length
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in saveWorkflow:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== DELETE WORKFLOW ====================
const deleteWorkflow = async (req, res) => {
  try {
    const { moduleTypeId } = req.params;
    const orgId = req.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    if (!moduleTypeId) return res.status(400).json({ success: false, message: "Module Type ID is required" });

    const result = await pool.query(
      `DELETE FROM public.approval_workflows WHERE module_type_id = $1 RETURNING id`,
      [parseInt(moduleTypeId)]
    );
    res.json({ success: true, message: "Workflow deleted successfully", deletedCount: result.rowCount });
  } catch (error) {
    console.error("Error in deleteWorkflow:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== GET SINGLE WORKFLOW ENTRY ====================
const getWorkflowById = async (req, res) => {
  try {
    const { id } = req.params;
    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 aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              aw.is_mandatory, aw.max_approval_days,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.users u ON aw.user_id = u.id
       WHERE aw.id = $1`,
      [parseInt(id)]
    );
    if (result.rows.length === 0) return res.status(404).json({ success: false, message: "Workflow entry not found" });
    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("Error in getWorkflowById:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== UPDATE WORKFLOW ORDER ====================
const updateWorkflowOrder = async (req, res) => {
  const client = await pool.connect();
  try {
    const { moduleTypeId } = req.params;
    const { workflowOrders } = req.body;
    const orgId = req.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized - Organization ID missing" });
    if (!workflowOrders || workflowOrders.length === 0) return res.status(400).json({ success: false, message: "Workflow orders array is required" });

    await client.query("BEGIN");
    for (const item of workflowOrders) {
      await client.query(
        `UPDATE public.approval_workflows SET approval_order = $1, updated_at = NOW() WHERE id = $2 AND module_type_id = $3`,
        [item.approval_order, item.id, parseInt(moduleTypeId)]
      );
    }
    await client.query("COMMIT");

    const updatedWorkflow = await client.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.users u ON aw.user_id = u.id
       WHERE aw.module_type_id = $1
       ORDER BY aw.approval_order`,
      [parseInt(moduleTypeId)]
    );
    res.json({ success: true, message: "Workflow order updated successfully", data: updatedWorkflow.rows });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in updateWorkflowOrder:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== GET MY APPROVAL TASKS ====================
const getMyApprovalTasks = async (req, res) => {
  try {
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;
    if (!orgId || !userId) return res.status(401).json({ success: false, message: "Unauthorized - User or Organization ID missing" });

    const result = await pool.query(
      `SELECT aw.module_type_id, aw.approval_order, aw.is_mandatory,
              mt.module_name, mt.module_code
       FROM public.approval_workflows aw
       JOIN public.module_types mt ON aw.module_type_id = mt.id
       WHERE aw.user_id = $1
       ORDER BY aw.approval_order`,
      [parseInt(userId)]
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("Error in getMyApprovalTasks:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== CHECK IF USER IS APPROVER FOR MODULE ====================
const checkIsApprover = async (req, res) => {
  try {
    const userId = req.user?.userId || req.user?.id;
    const { moduleTypeId } = req.params;
    if (!userId) return res.status(401).json({ success: false, message: "Unauthorized - User ID missing" });

    const result = await pool.query(
      `SELECT EXISTS(
         SELECT 1 FROM public.approval_workflows WHERE user_id = $1 AND module_type_id = $2
       ) as is_approver`,
      [parseInt(userId), parseInt(moduleTypeId)]
    );
    res.json({ success: true, data: { is_approver: result.rows[0].is_approver } });
  } catch (error) {
    console.error("Error in checkIsApprover:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== GET PO APPROVALS ====================
const getPOApprovals = async (req, res) => {
  try {
    const { poId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Get PO details
    const poResult = await pool.query(
      `SELECT po.created_by, po.approval_status, po.po_number,
              u.first_name as creator_first_name, u.last_name as creator_last_name,
              u.role as creator_role
       FROM public.purchase_orders po
       LEFT JOIN public.users u ON po.created_by::text = u.id::text
       WHERE po.id = $1 AND po.org_id = $2`,
      [parseInt(poId), orgId]
    );

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

    const po = poResult.rows[0];
    
    // If PO is already fully approved or rejected, return that status
    // IMPORTANT: Show the workflow with all steps as approved
    if (po.approval_status === 'approved') {
      const workflowResult = await pool.query(
        `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
                aw.is_mandatory, aw.max_approval_days,
                u.first_name, u.last_name, u.email, u.role
         FROM public.approval_workflows aw
         JOIN public.users u ON aw.user_id = u.id
         WHERE aw.module_type_id = 2
         ORDER BY aw.approval_order`,
        []
      );
      
      // Mark all steps as approved
      const workflowWithStatus = workflowResult.rows.map(step => ({
        ...step,
        status: 'approved',
        approved_at: null,
        comments: null
      }));
      
      return res.json({
        success: true,
        data: workflowWithStatus,
        approvalStatus: 'approved',
        totalSteps: workflowResult.rows.length,
        creatorInfo: {
          creator_first_name: po.creator_first_name,
          creator_last_name: po.creator_last_name,
          creator_role: po.creator_role
        },
        currentUserId: userId
      });
    }
    
    if (po.approval_status === 'rejected') {
      const workflowResult = await pool.query(
        `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
                aw.is_mandatory, aw.max_approval_days,
                u.first_name, u.last_name, u.email, u.role
         FROM public.approval_workflows aw
         JOIN public.users u ON aw.user_id = u.id
         WHERE aw.module_type_id = 2
         ORDER BY aw.approval_order`,
        []
      );
      
      // Mark all steps as rejected
      const workflowWithStatus = workflowResult.rows.map(step => ({
        ...step,
        status: 'rejected',
        approved_at: null,
        comments: null
      }));
      
      return res.json({
        success: true,
        data: workflowWithStatus,
        approvalStatus: 'rejected',
        totalSteps: workflowResult.rows.length,
        creatorInfo: {
          creator_first_name: po.creator_first_name,
          creator_last_name: po.creator_last_name,
          creator_role: po.creator_role
        },
        currentUserId: userId
      });
    }

    // Get module type ID for PO
    const moduleResult = await pool.query(
      `SELECT id FROM public.module_types WHERE module_code = 'PO' AND is_active = true LIMIT 1`
    );

    if (moduleResult.rows.length === 0) {
      return res.json({
        success: true,
        data: [],
        approvalStatus: 'no_workflow',
        creatorInfo: {
          creator_first_name: po.creator_first_name,
          creator_last_name: po.creator_last_name,
          creator_role: po.creator_role
        },
        currentUserId: userId,
        message: "PO module not found"
      });
    }

    const moduleTypeId = moduleResult.rows[0].id;

    // Get workflow from approval_workflows
    const workflowResult = await pool.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              aw.is_mandatory, aw.max_approval_days,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.users u ON aw.user_id = u.id
       WHERE aw.module_type_id = $1
       ORDER BY aw.approval_order`,
      [moduleTypeId]
    );

    if (workflowResult.rows.length === 0) {
      return res.json({
        success: true,
        data: [],
        approvalStatus: 'no_workflow',
        creatorInfo: {
          creator_first_name: po.creator_first_name,
          creator_last_name: po.creator_last_name,
          creator_role: po.creator_role
        },
        currentUserId: userId,
        message: "No workflow configured"
      });
    }

    // Get current approval progress
    const progressResult = await pool.query(
      `SELECT current_step, status FROM public.po_approval_progress
       WHERE po_id = $1`,
      [parseInt(poId)]
    );

    let currentStep = 1;
    if (progressResult.rows.length > 0) {
      currentStep = progressResult.rows[0].current_step;
    }

    // Build workflow with statuses
    const workflowWithStatus = workflowResult.rows.map(step => {
      let status = 'pending';
      
      if (step.approval_order < currentStep) {
        status = 'approved';
      } else if (step.approval_order === currentStep) {
        status = 'pending';
      }

      return {
        ...step,
        status,
        approved_at: null,
        comments: null
      };
    });

    res.json({
      success: true,
      data: workflowWithStatus,
      approvalStatus: 'pending',
      currentStep: currentStep,
      totalSteps: workflowResult.rows.length,
      creatorInfo: {
        creator_first_name: po.creator_first_name,
        creator_last_name: po.creator_last_name,
        creator_role: po.creator_role
      },
      currentUserId: userId
    });

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

// ==================== APPROVE PO ====================
const approvePO = async (req, res) => {
  const client = await pool.connect();
  try {
    const { poId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const { comments } = req.body;

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

    await client.query("BEGIN");

    // Get PO details
    const poCheck = await client.query(
      `SELECT id, approval_status, po_number, vendor_id, vendor_name, org_id 
       FROM public.purchase_orders 
       WHERE id = $1 AND is_deleted = false`,
      [parseInt(poId)]
    );

    if (poCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "PO not found" });
    }

    const poData = poCheck.rows[0];

    if (poData.approval_status === 'approved') {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "PO is already fully approved" });
    }

    // Get workflow
    const workflow = await client.query(
      `SELECT user_id, approval_order FROM public.approval_workflows
       WHERE module_type_id = 2
       ORDER BY approval_order`,
      []
    );

    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured" });
    }

    // Check if user is an approver
    const userStep = workflow.rows.find(row => row.user_id == userId);
    
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not an approver" });
    }

    // Get current progress
    let progress = await client.query(
      `SELECT current_step FROM public.po_approval_progress WHERE po_id = $1`,
      [parseInt(poId)]
    );

    let currentStep = 1;
    if (progress.rows.length > 0) {
      currentStep = progress.rows[0].current_step;
    }

    // Check if it's this user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ 
        success: false, 
        message: `Not your turn. Current step is ${currentStep}.`
      });
    }

    const nextStep = currentStep + 1;
    const isLastStep = nextStep > workflow.rows.length;

    // Update progress
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.po_approval_progress (po_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, $3, $4, NOW(), $5)`,
        [parseInt(poId), nextStep, isLastStep ? 'approved' : 'pending', userId, comments || null]
      );
    } else {
      await client.query(
        `UPDATE public.po_approval_progress 
         SET current_step = $1, 
             status = $2,
             approved_by = $3,
             approved_at = NOW(),
             comments = $4,
             updated_at = NOW()
         WHERE po_id = $5`,
        [nextStep, isLastStep ? 'approved' : 'pending', userId, comments || null, parseInt(poId)]
      );
    }

    // Update PO approval status if fully approved
    if (isLastStep) {
      await client.query(
        `UPDATE public.purchase_orders 
         SET approval_status = 'approved', updated_at = NOW()
         WHERE id = $1`,
        [parseInt(poId)]
      );

      // ==================== SEND EMAIL TO VENDOR AFTER FULL APPROVAL ====================
      // try {
      //   // Import the sendPOEmailOnFullApproval function from purchaseOrderController
      //   // You need to require it at the top of the file
      //   const { sendPOEmailOnFullApproval } = require("./purchaseOrderController");
        
      //   // Create a mock request and response for the internal call
      //   const mockReq = {
      //     params: { poId: poId },
      //     user: req.user
      //   };
        
      //   const mockRes = {
      //     json: (data) => console.log("Email send response:", data),
      //     status: (code) => ({ json: (data) => console.log("Email send error:", data) })
      //   };
        
      //   await sendPOEmailOnFullApproval(mockReq, mockRes);
      //   console.log(`📧 Email triggered for PO ${poData.po_number} after full approval`);
      // } catch (emailError) {
      //   // Log error but don't fail the approval
      //   console.error(`❌ Failed to send email for PO ${poData.po_number}:`, emailError.message);
      // }
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: isLastStep ? "PO fully approved! Vendor email has been sent." : `Approved successfully. Step ${currentStep} completed.`,
      allApproved: isLastStep,
      currentStep: currentStep,
      nextStep: isLastStep ? null : nextStep
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in approvePO:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};


// ==================== REJECT PO ====================
const rejectPO = async (req, res) => {
  const client = await pool.connect();
  try {
    const { poId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const { comments } = req.body;

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

    if (!comments || !comments.trim()) {
      return res.status(400).json({ success: false, message: "Rejection reason is required" });
    }

    await client.query("BEGIN");

    // Get PO details
    const poCheck = await client.query(
      `SELECT id, approval_status FROM public.purchase_orders WHERE id = $1 AND is_deleted = false`,
      [parseInt(poId)]
    );

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

    if (poCheck.rows[0].approval_status === 'approved') {
      return res.status(400).json({ success: false, message: "PO is already approved and cannot be rejected" });
    }

    if (poCheck.rows[0].approval_status === 'rejected') {
      return res.status(400).json({ success: false, message: "PO is already rejected" });
    }

    // Get module type ID for PO
    const moduleResult = await client.query(
      `SELECT id FROM public.module_types WHERE module_code = 'PO' AND is_active = true LIMIT 1`
    );

    if (moduleResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "PO module not configured" });
    }

    const moduleTypeId = moduleResult.rows[0].id;

    // Get workflow to check if user is an approver
    const workflow = await client.query(
      `SELECT user_id, approval_order FROM public.approval_workflows
       WHERE module_type_id = $1
       ORDER BY approval_order`,
      [moduleTypeId]
    );

    if (workflow.rows.length === 0) {
      return res.status(404).json({ success: false, message: "No workflow configured for PO module" });
    }

    // Check if user is an approver
    const userStep = workflow.rows.find(row => row.user_id == userId);
    
    if (!userStep) {
      return res.status(403).json({ success: false, message: "You are not an approver for this PO" });
    }

    // Get current progress
    let progress = await client.query(
      `SELECT current_step FROM public.po_approval_progress WHERE po_id = $1`,
      [parseInt(poId)]
    );

    let currentStep = 1;
    if (progress.rows.length > 0) {
      currentStep = progress.rows[0].current_step;
    }

    // Check if it's this user's turn (can reject current or any previous pending step? Usually only current)
    if (userStep.approval_order !== currentStep) {
      return res.status(403).json({ 
        success: false, 
        message: `You cannot reject at this time. Current step is ${currentStep}.`
      });
    }

    // Update progress as rejected
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.po_approval_progress (po_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, 'rejected', $3, NOW(), $4)`,
        [parseInt(poId), currentStep, userId, comments.trim()]
      );
    } else {
      await client.query(
        `UPDATE public.po_approval_progress 
         SET status = 'rejected',
             approved_by = $1,
             approved_at = NOW(),
             comments = $2,
             updated_at = NOW()
         WHERE po_id = $3`,
        [userId, comments.trim(), parseInt(poId)]
      );
    }

    // Mark PO as rejected
    await client.query(
      `UPDATE public.purchase_orders 
       SET approval_status = 'rejected', updated_at = NOW()
       WHERE id = $1`,
      [parseInt(poId)]
    );

    await client.query("COMMIT");

    res.json({ 
      success: true, 
      message: "PO rejected successfully",
      rejectedBy: userId,
      rejectedAt: new Date()
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in rejectPO:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== BACKFILL APPROVAL PROGRESS FOR EXISTING POs ====================
const backfillApprovalProgress = async (req, res) => {
  const client = await pool.connect();
  try {
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // Get all POs without progress records
    const poResult = await client.query(
      `SELECT po.id 
       FROM public.purchase_orders po
       LEFT JOIN public.po_approval_progress pap ON po.id = pap.po_id
       WHERE pap.id IS NULL 
         AND po.is_deleted = false
         AND po.org_id = $1`,
      [orgId]
    );

    let insertedCount = 0;
    for (const po of poResult.rows) {
      await client.query(
        `INSERT INTO public.po_approval_progress (po_id, current_step, status, created_at, updated_at)
         VALUES ($1, 1, 'pending', NOW(), NOW())`,
        [po.id]
      );
      insertedCount++;
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Successfully backfilled approval progress for ${insertedCount} PO(s)`,
      data: { backfilledCount: insertedCount }
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in backfillApprovalProgress:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== GET PO APPROVAL PROGRESS ====================
const getPOApprovalProgress = async (req, res) => {
  try {
    const { poId } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT pap.*, 
              u.first_name as approved_by_first_name, 
              u.last_name as approved_by_last_name
       FROM public.po_approval_progress pap
       LEFT JOIN public.users u ON pap.approved_by = u.id
       WHERE pap.po_id = $1`,
      [parseInt(poId)]
    );

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

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

// ==================== CREATE APPROVAL PROGRESS TABLE (RUN ONCE) ====================
const createApprovalProgressTable = async (req, res) => {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS public.po_approval_progress (
        id SERIAL PRIMARY KEY,
        po_id INTEGER NOT NULL REFERENCES public.purchase_orders(id) ON DELETE CASCADE,
        current_step INTEGER DEFAULT 1,
        status VARCHAR(20) DEFAULT 'pending',
        approved_by INTEGER,
        approved_at TIMESTAMP,
        comments TEXT,
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(po_id)
      )
    `);

    // Create index for better performance
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_po_approval_progress_po_id ON public.po_approval_progress(po_id);
      CREATE INDEX IF NOT EXISTS idx_po_approval_progress_status ON public.po_approval_progress(status);
    `);

    res.json({
      success: true,
      message: "po_approval_progress table created successfully"
    });

  } catch (error) {
    console.error("Error creating approval progress table:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

const getGRNApprovals = async (req, res) => {
  try {
    const { grnId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

    // Get GRN details + creator info
    const grnResult = await pool.query(
      `SELECT grn.created_by, grn.grn_status, grn.grn_number,
              u.first_name as creator_first_name, u.last_name as creator_last_name,
              u.role as creator_role
       FROM public.grn grn
       LEFT JOIN public.users u ON grn.created_by::text = u.id::text
       WHERE grn.id = $1 AND grn.org_id = $2`,
      [parseInt(grnId), orgId]
    );

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

    const grn = grnResult.rows[0];

    // If GRN is already fully approved, return all steps as approved
    if (grn.grn_status === 'Approved') {
      const workflowResult = await pool.query(
        `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
                aw.is_mandatory, aw.max_approval_days,
                u.first_name, u.last_name, u.email, u.role
         FROM public.approval_workflows aw
         JOIN public.users u ON aw.user_id = u.id
         WHERE aw.module_type_id = 1
         ORDER BY aw.approval_order`,
        []
      );
      const workflowWithStatus = workflowResult.rows.map(step => ({
        ...step,
        status: 'approved',
        approved_at: null,
        comments: null
      }));
      return res.json({
        success: true,
        data: workflowWithStatus,
        approvalStatus: 'approved',
        currentStep: workflowResult.rows.length + 1,
        totalSteps: workflowResult.rows.length,
        creatorInfo: {
          creator_first_name: grn.creator_first_name,
          creator_last_name: grn.creator_last_name,
          creator_role: grn.creator_role,
          creator_id: grn.created_by
        },
        currentUserId: userId
      });
    }

    // If GRN is rejected, return all steps as rejected
    if (grn.grn_status === 'rejected') {
      const workflowResult = await pool.query(
        `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
                aw.is_mandatory, aw.max_approval_days,
                u.first_name, u.last_name, u.email, u.role
         FROM public.approval_workflows aw
         JOIN public.users u ON aw.user_id = u.id
         WHERE aw.module_type_id = 1
         ORDER BY aw.approval_order`,
        []
      );
      const workflowWithStatus = workflowResult.rows.map(step => ({
        ...step,
        status: 'rejected',
        approved_at: null,
        comments: null
      }));
      return res.json({
        success: true,
        data: workflowWithStatus,
        approvalStatus: 'rejected',
        currentStep: 1,
        totalSteps: workflowResult.rows.length,
        creatorInfo: {
          creator_first_name: grn.creator_first_name,
          creator_last_name: grn.creator_last_name,
          creator_role: grn.creator_role,
          creator_id: grn.created_by
        },
        currentUserId: userId
      });
    }

    // Get workflow steps from shared config (no per-GRN approvals table)
    const workflowResult = await pool.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              aw.is_mandatory, aw.max_approval_days,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.users u ON aw.user_id = u.id
       WHERE aw.module_type_id = 1
       ORDER BY aw.approval_order`,
      []
    );

    if (workflowResult.rows.length === 0) {
      return res.json({
        success: true,
        data: [],
        approvalStatus: 'no_workflow',
        creatorInfo: {
          creator_first_name: grn.creator_first_name,
          creator_last_name: grn.creator_last_name,
          creator_role: grn.creator_role,
          creator_id: grn.created_by
        },
        currentUserId: userId,
        message: "No workflow configured for GRN module"
      });
    }

    // Get current step from grn_approval_progress (same pattern as PO)
    const progressResult = await pool.query(
      `SELECT current_step, status FROM public.grn_approval_progress WHERE grn_id = $1`,
      [parseInt(grnId)]
    );

    let currentStep = 1;
    if (progressResult.rows.length > 0) {
      currentStep = progressResult.rows[0].current_step;
    }

    // Derive step status from currentStep — same logic as PO
    const workflowWithStatus = workflowResult.rows.map(step => {
      let status = 'pending';
      if (step.approval_order < currentStep) {
        status = 'approved';
      } else if (step.approval_order === currentStep) {
        status = 'pending';
      }
      return {
        ...step,
        status,
        approved_at: null,
        comments: null
      };
    });

    res.json({
      success: true,
      data: workflowWithStatus,
      approvalStatus: 'pending',
      currentStep,
      totalSteps: workflowResult.rows.length,
      creatorInfo: {
        creator_first_name: grn.creator_first_name,
        creator_last_name: grn.creator_last_name,
        creator_role: grn.creator_role,
        creator_id: grn.created_by
      },
      currentUserId: userId
    });

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

// ==================== APPROVE GRN ====================
const approveGRN = async (req, res) => {
  const client = await pool.connect();
  try {
    const { grnId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const { comments } = req.body;

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

    await client.query("BEGIN");

    // Verify GRN exists and check current status
    const grnCheck = await client.query(
      `SELECT id, grn_status FROM public.grn WHERE id = $1 AND is_deleted = false`,
      [parseInt(grnId)]
    );

    if (grnCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "GRN not found" });
    }

    if (grnCheck.rows[0].grn_status === 'Approved') {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "GRN is already approved" });
    }

    if (grnCheck.rows[0].grn_status === 'rejected') {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "GRN has been rejected" });
    }

    // Get workflow for GRN module
    const workflow = await client.query(
      `SELECT user_id, approval_order FROM public.approval_workflows
       WHERE module_type_id = 1
       ORDER BY approval_order`,
      []
    );

    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured for GRN module" });
    }

    // Check if user is an approver in the workflow
    const userStep = workflow.rows.find(row => row.user_id == userId);
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not an approver for GRN" });
    }

    // Get current progress for this specific GRN
    let progress = await client.query(
      `SELECT current_step FROM public.grn_approval_progress WHERE grn_id = $1`,
      [parseInt(grnId)]
    );

    let currentStep = 1;
    if (progress.rows.length > 0) {
      currentStep = progress.rows[0].current_step;
    }

    // Enforce sequential approval — must be this user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({
        success: false,
        message: `Not your turn. Current step is ${currentStep}.`
      });
    }

    const nextStep = currentStep + 1;
    const isLastStep = nextStep > workflow.rows.length;

    // Upsert grn_approval_progress
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.grn_approval_progress (grn_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, $3, $4, NOW(), $5)`,
        [parseInt(grnId), nextStep, isLastStep ? 'approved' : 'pending', userId, comments || null]
      );
    } else {
      await client.query(
        `UPDATE public.grn_approval_progress
         SET current_step = $1,
             status = $2,
             approved_by = $3,
             approved_at = NOW(),
             comments = $4,
             updated_at = NOW()
         WHERE grn_id = $5`,
        [nextStep, isLastStep ? 'approved' : 'pending', userId, comments || null, parseInt(grnId)]
      );
    }

    // If all steps done, mark GRN as Approved
    if (isLastStep) {
      await client.query(
        `UPDATE public.grn
         SET grn_status = 'Approved', updated_at = NOW(), updated_by = $1
         WHERE id = $2`,
        [userId, parseInt(grnId)]
      );
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: isLastStep ? "GRN fully approved!" : `Approved successfully. Step ${currentStep} completed.`,
      allApproved: isLastStep,
      currentStep,
      nextStep: isLastStep ? null : nextStep
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in approveGRN:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};




// ==================== REJECT GRN ====================
const rejectGRN = async (req, res) => {
  const client = await pool.connect();
  try {
    const { grnId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const { comments } = req.body;

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

    if (!comments || !comments.trim()) {
      return res.status(400).json({ success: false, message: "Rejection reason is required" });
    }

    await client.query("BEGIN");

    const grnCheck = await client.query(
      `SELECT id, grn_status FROM public.grn WHERE id = $1 AND is_deleted = false`,
      [parseInt(grnId)]
    );

    if (grnCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "GRN not found" });
    }

    if (grnCheck.rows[0].grn_status === 'Approved') {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "GRN is already approved and cannot be rejected" });
    }

    if (grnCheck.rows[0].grn_status === 'rejected') {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "GRN is already rejected" });
    }

    // Get workflow to check if user is an approver
    const workflow = await client.query(
      `SELECT user_id, approval_order FROM public.approval_workflows
       WHERE module_type_id = 1
       ORDER BY approval_order`,
      []
    );

    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured for GRN module" });
    }

    const userStep = workflow.rows.find(row => row.user_id == userId);
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not an approver for GRN" });
    }

    // Get current progress for this specific GRN
    let progress = await client.query(
      `SELECT current_step FROM public.grn_approval_progress WHERE grn_id = $1`,
      [parseInt(grnId)]
    );

    let currentStep = 1;
    if (progress.rows.length > 0) {
      currentStep = progress.rows[0].current_step;
    }

    // Must be this user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({
        success: false,
        message: `You cannot reject at this time. Current step is ${currentStep}.`
      });
    }

    // Upsert grn_approval_progress as rejected
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.grn_approval_progress (grn_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, 'rejected', $3, NOW(), $4)`,
        [parseInt(grnId), currentStep, userId, comments.trim()]
      );
    } else {
      await client.query(
        `UPDATE public.grn_approval_progress
         SET status = 'rejected',
             approved_by = $1,
             approved_at = NOW(),
             comments = $2,
             updated_at = NOW()
         WHERE grn_id = $3`,
        [userId, comments.trim(), parseInt(grnId)]
      );
    }

    // Mark GRN as rejected
    await client.query(
      `UPDATE public.grn
       SET grn_status = 'rejected', updated_at = NOW(), updated_by = $1
       WHERE id = $2`,
      [userId, parseInt(grnId)]
    );

    await client.query("COMMIT");

    res.json({
      success: true,
      message: "GRN rejected successfully",
      rejectedBy: userId,
      rejectedAt: new Date()
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in rejectGRN:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};



// Approve Purchase Invoice and approve GRN
const approvePurchaseInvoice = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const { comments } = req.body;  // Get comments from request body
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Get purchase invoice details
    const invoiceResult = await client.query(
      `SELECT id, grn_id, status FROM purchase_invoices 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const invoice = invoiceResult.rows[0];

    if (invoice.status === "Approved") {
      return res.status(400).json({ success: false, message: "Invoice is already approved" });
    }

    // Check if GRN is approved
    const grnCheck = await client.query(
      `SELECT grn_status FROM grn WHERE id = $1 AND is_deleted = false`,
      [invoice.grn_id]
    );

    if (grnCheck.rows.length > 0 && grnCheck.rows[0].grn_status !== 'approved') {
      return res.status(400).json({ 
        success: false, 
        message: "GRN workflow approvals not completed yet. Please wait for all approvers." 
      });
    }

    await client.query("BEGIN");

    // Update purchase invoice status to Approved with comments
    await client.query(
      `UPDATE purchase_invoices 
       SET status = 'Approved', 
           approved_by = $1, 
           approved_at = NOW(),
           approval_comments = $2,
           updated_at = NOW()
       WHERE id = $3`,
      [userId, comments || null, id]
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Purchase invoice approved successfully",
      data: {
        invoice_id: invoice.id,
        grn_id: invoice.grn_id,
        status: "Approved"
      }
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Approve Purchase Invoice Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to approve purchase invoice",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  } finally {
    client.release();
  }
};


// ============================================================
//  SALES INVOICE APPROVAL — add these functions to approvalController.js
//  and export them at the bottom.
//
//  module_type_id used below is looked up dynamically via module_code
//  = 'SALES_INVOICE', so no magic numbers are hard-coded.
//
//  Table required: public.sales_invoice_approval_progress
//  (see sales_invoice_approval_progress.sql)
// ============================================================

// ==================== GET SALES INVOICE APPROVALS ====================
const getSalesInvoiceApprovals = async (req, res) => {
  try {
    const { invoiceId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Get invoice details
    const invoiceResult = await pool.query(
      `SELECT si.created_by, si.status, si.invoice_number,
              u.first_name AS creator_first_name, u.last_name AS creator_last_name,
              u.role AS creator_role
       FROM public.sales_invoices si
       LEFT JOIN public.users u ON si.created_by::text = u.id::text
       WHERE si.id = $1 AND si.org_id = $2 AND si.is_deleted = false`,
      [parseInt(invoiceId), orgId]
    );

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

    const invoice = invoiceResult.rows[0];
    const invoiceStatus = invoice.status?.toLowerCase();
    
    // Get workflow
    const workflowResult = await pool.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              aw.is_mandatory, aw.max_approval_days,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.module_types mt ON aw.module_type_id = mt.id
       JOIN public.users u ON aw.user_id = u.id
       WHERE mt.module_code = 'SALES_INVOICE'
       ORDER BY aw.approval_order`,
      []
    );

    const creatorInfo = {
      creator_first_name: invoice.creator_first_name,
      creator_last_name: invoice.creator_last_name,
      creator_role: invoice.creator_role,
      creator_id: invoice.created_by,
    };

    if (workflowResult.rows.length === 0) {
      return res.json({
        success: true,
        data: [],
        approvalStatus: "no_workflow",
        creatorInfo,
        currentUserId: userId,
        currentStep: 1,
        totalSteps: 0,
      });
    }

    // Get progress
    const progressResult = await pool.query(
      `SELECT current_step, status, approved_by, approved_at, comments
       FROM public.sales_invoice_approval_progress 
       WHERE invoice_id = $1`,
      [parseInt(invoiceId)]
    );

    let currentStep = 1;
    if (progressResult.rows.length > 0) {
      currentStep = progressResult.rows[0].current_step;
    }

    // ✅ CRITICAL: Check if invoice is approved
    const isInvoiceApproved = invoiceStatus === "approved";
    
    console.log(`Invoice ${invoiceId} - Status: ${invoiceStatus}, Current Step: ${currentStep}, Is Approved: ${isInvoiceApproved}`);

    // Build workflow with statuses
    const workflowWithStatus = workflowResult.rows.map(step => {
      let status = "pending";
      
      // ✅ If invoice is approved, ALL steps are approved
      if (isInvoiceApproved) {
        status = "approved";
      } 
      // Otherwise check step order
      else if (step.approval_order < currentStep) {
        status = "approved";
      } else if (step.approval_order === currentStep) {
        status = "pending";
      }
      
      return {
        ...step,
        status,
        approved_at: null,
        comments: null
      };
    });

    return res.json({
      success: true,
      data: workflowWithStatus,
      approvalStatus: isInvoiceApproved ? "approved" : "pending",
      currentStep: currentStep,
      totalSteps: workflowResult.rows.length,
      creatorInfo,
      currentUserId: userId,
    });
    
  } catch (error) {
    console.error("Error in getSalesInvoiceApprovals:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};


  // ==================== APPROVE SALES INVOICE (step-by-step) ====================
const approveSalesInvoice = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { invoiceId } = req.params;
    const { comments } = req.body;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

    if (!userId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // 1. Check invoice exists
    const invoiceCheck = await client.query(
      `SELECT id, status FROM public.sales_invoices WHERE id = $1 AND is_deleted = false`,
      [parseInt(invoiceId)]
    );
    
    if (invoiceCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Sales invoice not found" });
    }

    const { status } = invoiceCheck.rows[0];
    
    if (status === "approved") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Invoice already approved" });
    }
    
    if (status === "rejected") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Invoice already rejected" });
    }

    // 2. Get workflow
    const workflow = await client.query(
      `SELECT user_id, approval_order 
       FROM public.approval_workflows 
       WHERE module_type_id = 3 
       ORDER BY approval_order`,
      []
    );
    
    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured" });
    }

    // 3. Check if user is an approver
    const userStep = workflow.rows.find(row => row.user_id == userId);
    
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not authorized" });
    }

    // 4. Get current progress
    let progress = await client.query(
      `SELECT current_step FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
      [parseInt(invoiceId)]
    );
    
    let currentStep = 1;
    
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_invoice_approval_progress (invoice_id, current_step, status)
         VALUES ($1, 1, 'pending')`,
        [parseInt(invoiceId)]
      );
      currentStep = 1;
    } else {
      currentStep = progress.rows[0].current_step;
    }

    // 5. Check if it's user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ 
        success: false, 
        message: `Not your turn. Current step is ${currentStep}.` 
      });
    }

    // 6. Calculate next step
    const nextStep = currentStep + 1;
    const isLastStep = nextStep > workflow.rows.length;

    // 7. Update progress
    await client.query(
      `UPDATE public.sales_invoice_approval_progress 
       SET current_step = $1, 
           status = $2, 
           approved_by = $3, 
           approved_at = NOW(), 
           comments = $4,
           updated_at = NOW()
       WHERE invoice_id = $5`,
      [nextStep, isLastStep ? "approved" : "pending", userId, comments || null, parseInt(invoiceId)]
    );

    // 8. If last step, mark invoice as approved AND trigger PDF/email
    if (isLastStep) {
      await client.query(
        `UPDATE public.sales_invoices 
         SET status = 'approved', updated_at = NOW() 
         WHERE id = $1`,
        [parseInt(invoiceId)]
      );

      await client.query("COMMIT");
      
      console.log(`✅ Invoice ${invoiceId} fully approved. Generating PDF and sending email...`);
      
      // ✅ Call the approveInvoice function from salesInvoiceController
      try {
        const mockReq = {
          params: { id: String(invoiceId) },
          session: { user: { id: userId, orgId: orgId } }
        };
        
        let responseData = null;
        const mockRes = {
          status: (code) => ({
            json: (data) => {
              responseData = data;
              return data;
            }
          })
        };
        
        // Check if approveInvoice is a function
        if (typeof approveInvoice === 'function') {
          await approveInvoice(mockReq, mockRes);
          console.log(`✅ PDF generated and email sent for invoice ${invoiceId}`);
        } else {
          console.error(`❌ approveInvoice is not a function! Type: ${typeof approveInvoice}`);
        }
      } catch (emailError) {
        console.error("❌ Error generating PDF/email:", emailError);
      }
      
      return res.json({
        success: true,
        message: "Invoice fully approved! PDF generated and email sent.",
        allApproved: true,
        currentStep,
        nextStep: null
      });
    }

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: `Step ${currentStep} approved successfully. Waiting for next approver.`,
      allApproved: false,
      currentStep,
      nextStep: nextStep
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Approve Error:", error);
    return res.status(500).json({ 
      success: false, 
      message: error.message || "Approval failed" 
    });
  } finally {
    client.release();
  }
};

  // ==================== REJECT SALES INVOICE ====================
const rejectSalesInvoice = async (req, res) => {
  const client = await pool.connect();
  try {
    const { invoiceId } = req.params;
    const { comments } = req.body; // Remove resetToStep for now
    const userId = req.user?.userId || req.user?.id;

    if (!userId) {
      return res.status(401).json({ success: false, message: "Unauthorized - User ID missing" });
    }
    if (!comments || !comments.trim()) {
      return res.status(400).json({ success: false, message: "Rejection reason is required" });
    }

    await client.query("BEGIN");

    // Check invoice
    const invoiceCheck = await client.query(
      `SELECT id, status FROM public.sales_invoices WHERE id = $1 AND is_deleted = false`,
      [parseInt(invoiceId)]
    );
    
    if (invoiceCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Sales invoice not found" });
    }
    
    const { status } = invoiceCheck.rows[0];
    
    if (status === "approved" || status === "Approved") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Sales invoice is already approved and cannot be rejected" });
    }
    
    if (status === "rejected") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Sales invoice is already rejected" });
    }

    // Get workflow - SIMPLIFIED: Hardcode module_type_id = 3
    const workflow = await client.query(
      `SELECT aw.user_id, aw.approval_order
       FROM public.approval_workflows aw
       WHERE aw.module_type_id = 3
       ORDER BY aw.approval_order`,
      []
    );
    
    console.log("Workflow found:", workflow.rows);
    
    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured for Sales Invoice" });
    }

    const userStep = workflow.rows.find((row) => row.user_id == userId);
    console.log("User step:", userStep);
    
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not an approver for Sales Invoice" });
    }

    // Get current progress
    let progress = await client.query(
      `SELECT current_step FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
      [parseInt(invoiceId)]
    );
    
    let currentStep = 1;
    
    if (progress.rows.length === 0) {
      // Create progress record if doesn't exist
      await client.query(
        `INSERT INTO public.sales_invoice_approval_progress (invoice_id, current_step, status)
         VALUES ($1, 1, 'pending')`,
        [parseInt(invoiceId)]
      );
      currentStep = 1;
    } else {
      currentStep = progress.rows[0].current_step;
    }

    console.log("Current step:", currentStep);
    console.log("User approval order:", userStep.approval_order);

    // Check if it's user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({
        success: false,
        message: `You cannot reject at this time. Current step is ${currentStep}.`,
      });
    }

    // Update progress as rejected - SIMPLE VERSION without extra columns
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_invoice_approval_progress
          (invoice_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, 'rejected', $3, NOW(), $4)`,
        [parseInt(invoiceId), currentStep, userId, comments.trim()]
      );
    } else {
      await client.query(
        `UPDATE public.sales_invoice_approval_progress
         SET status = 'rejected', 
             approved_by = $1,
             approved_at = NOW(), 
             comments = $2, 
             updated_at = NOW()
         WHERE invoice_id = $3`,
        [userId, comments.trim(), parseInt(invoiceId)]
      );
    }

    // Mark invoice as rejected
    await client.query(
      `UPDATE public.sales_invoices
       SET status = 'rejected', updated_at = NOW()
       WHERE id = $1`,
      [parseInt(invoiceId)]
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Sales invoice rejected successfully",
      rejectedBy: userId,
      rejectedAt: new Date(),
      rejectedStep: currentStep
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in rejectSalesInvoice:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};


// Import at the top of approvalController.js
// const { approveInvoice } = require("./salesInvoiceController"); // Adjust path as needed

// ==================== SUPER-APPROVE SALES INVOICE ====================

// const superApproveSalesInvoice = async (req, res) => {
//   const client = await pool.connect();
  
//   try {
//     const { invoiceId } = req.params;
//     const { comments } = req.body;
//     const userId = req.user?.userId || req.user?.id;
//     const orgId = req.user?.orgId;

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

//     // Optional: Add role check for super approval
//     const userRole = req.user?.role?.toLowerCase();
//     if (userRole !== 'admin' && userRole !== 'superadmin' && userRole !== 'ceo') {
//       return res.status(403).json({ success: false, message: "Unauthorized for super approval" });
//     }

//     const invoiceCheck = await client.query(
//       `SELECT id, status FROM public.sales_invoices
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [parseInt(invoiceId), orgId]
//     );
    
//     if (invoiceCheck.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "Sales invoice not found" });
//     }
    
//     if (invoiceCheck.rows[0].status === "approved" || invoiceCheck.rows[0].status === "Approved") {
//       return res.status(400).json({ success: false, message: "Sales invoice is already approved" });
//     }

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

//     // Get total workflow steps
//     const workflow = await client.query(
//       `SELECT COUNT(*) AS total
//        FROM public.approval_workflows aw
//        JOIN public.module_types mt ON aw.module_type_id = mt.id
//        WHERE mt.module_code = 'SALES_INVOICE'`,
//       []
//     );
    
//     const totalSteps = parseInt(workflow.rows[0].total) || 1;
//     const finalStep = totalSteps + 1;

//     console.log(`📌 Super approving invoice ${invoiceId}. Total steps: ${totalSteps}`);

//     // Check if progress exists
//     const existing = await client.query(
//       `SELECT id FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
//       [parseInt(invoiceId)]
//     );
    
//     if (existing.rows.length === 0) {
//       await client.query(
//         `INSERT INTO public.sales_invoice_approval_progress
//           (invoice_id, current_step, status, approved_by, approved_at, comments)
//          VALUES ($1, $2, 'approved', $3, NOW(), $4)`,
//         [parseInt(invoiceId), finalStep, userId, comments || "Super approved by admin"]
//       );
//       console.log(`   ✅ Created progress record with step ${finalStep}`);
//     } else {
//       await client.query(
//         `UPDATE public.sales_invoice_approval_progress
//          SET current_step = $1, 
//              status = 'approved', 
//              approved_by = $2,
//              approved_at = NOW(), 
//              comments = $3, 
//              updated_at = NOW()
//          WHERE invoice_id = $4`,
//         [finalStep, userId, comments || "Super approved by admin", parseInt(invoiceId)]
//       );
//       console.log(`   ✅ Updated progress record to step ${finalStep}`);
//     }

//     // Mark invoice as approved
//     await client.query(
//       `UPDATE public.sales_invoices
//        SET status = 'approved', updated_at = NOW()
//        WHERE id = $1`,
//       [parseInt(invoiceId)]
//     );
//     console.log(`   ✅ Updated invoice status to 'approved'`);

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

//     // ==========================================
//     // ✅ Call approveInvoice to generate PDF and send email
//     // ==========================================
//     console.log(`📧 Calling approveInvoice to generate PDF and send email...`);
//     console.log(`   approveInvoice function exists: ${typeof approveInvoice === 'function'}`);
    
//     // Check if approveInvoice is available
//     if (typeof approveInvoice !== 'function') {
//       console.error(`❌ approveInvoice is not a function! Please check the import.`);
//       // Still return success for the approval, but log the error
//       return res.json({
//         success: true,
//         message: "Sales invoice super-approved successfully! (PDF generation skipped - function not found)",
//         data: {
//           currentStep: finalStep,
//           totalSteps: totalSteps,
//           allApproved: true,
//           invoiceStatus: 'approved'
//         }
//       });
//     }
    
//     try {
//       // Create a proper request object for approveInvoice
//       const approveReq = {
//         params: { id: String(invoiceId) },
//         session: { user: { id: userId, orgId: orgId } }
//       };
      
//       // Create a response object to capture the result
//       let approveResult = null;
//       const approveRes = {
//         status: (code) => ({
//           json: (data) => {
//             approveResult = { code, data };
//             console.log(`   approveInvoice response code: ${code}, success: ${data?.success}`);
//             return data;
//           }
//         })
//       };
      
//       // Call the approveInvoice function
//       await approveInvoice(approveReq, approveRes);
      
//       if (approveResult && approveResult.data && approveResult.data.success) {
//         console.log(`✅ PDF generated, gate pass created, and email sent for invoice ${invoiceId}`);
//         if (approveResult.data.gatePass) {
//           console.log(`✅ Gate pass: ${approveResult.data.gatePass.pass_number}`);
//         }
//       } else {
//         console.error(`❌ approveInvoice failed:`, approveResult?.data?.message);
//       }
//     } catch (emailError) {
//       console.error("❌ Error in approveInvoice call:", emailError.message);
//       console.error("❌ Full error:", emailError);
//       // Don't fail the super-approval if PDF/email fails
//     }

//     return res.json({
//       success: true,
//       message: "Sales invoice super-approved successfully! PDF generated and email sent to customer.",
//       data: {
//         currentStep: finalStep,
//         totalSteps: totalSteps,
//         allApproved: true,
//         invoiceStatus: 'approved'
//       }
//     });
    
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Error in superApproveSalesInvoice:", error);
//     res.status(500).json({ success: false, message: error.message });
//   } finally {
//     client.release();
//   }
// };

// const superApproveSalesInvoice = async (req, res) => {
//   const client = await pool.connect();
  
//   try {
//     const { invoiceId } = req.params;
//     const { comments } = req.body;
//     const userId = req.user?.userId || req.user?.id;
//     const orgId = req.user?.orgId;

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

//     // Optional: Add role check for super approval
//     const userRole = req.user?.role?.toLowerCase();
//     if (userRole !== 'admin' && userRole !== 'superadmin' && userRole !== 'ceo') {
//       return res.status(403).json({ success: false, message: "Unauthorized for super approval" });
//     }

//     const invoiceCheck = await client.query(
//       `SELECT id, status FROM public.sales_invoices
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [parseInt(invoiceId), orgId]
//     );
    
//     if (invoiceCheck.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "Sales invoice not found" });
//     }
    
//     if (invoiceCheck.rows[0].status === "approved" || invoiceCheck.rows[0].status === "Approved") {
//       return res.status(400).json({ success: false, message: "Sales invoice is already approved" });
//     }

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

//     // Get total workflow steps
//     const workflow = await client.query(
//       `SELECT COUNT(*) AS total
//        FROM public.approval_workflows aw
//        JOIN public.module_types mt ON aw.module_type_id = mt.id
//        WHERE mt.module_code = 'SALES_INVOICE'`,
//       []
//     );
    
//     const totalSteps = parseInt(workflow.rows[0].total) || 1;
//     const finalStep = totalSteps + 1;

//     console.log(`📌 Super approving invoice ${invoiceId}. Total steps: ${totalSteps}`);

//     // Check if progress exists
//     const existing = await client.query(
//       `SELECT id FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
//       [parseInt(invoiceId)]
//     );
    
//     if (existing.rows.length === 0) {
//       await client.query(
//         `INSERT INTO public.sales_invoice_approval_progress
//           (invoice_id, current_step, status, approved_by, approved_at, comments)
//          VALUES ($1, $2, 'approved', $3, NOW(), $4)`,
//         [parseInt(invoiceId), finalStep, userId, comments || "Super approved by admin"]
//       );
//       console.log(`   ✅ Created progress record with step ${finalStep}`);
//     } else {
//       await client.query(
//         `UPDATE public.sales_invoice_approval_progress
//          SET current_step = $1, 
//              status = 'approved', 
//              approved_by = $2,
//              approved_at = NOW(), 
//              comments = $3, 
//              updated_at = NOW()
//          WHERE invoice_id = $4`,
//         [finalStep, userId, comments || "Super approved by admin", parseInt(invoiceId)]
//       );
//       console.log(`   ✅ Updated progress record to step ${finalStep}`);
//     }

//     // Mark invoice as approved
//     await client.query(
//       `UPDATE public.sales_invoices
//        SET status = 'approved', updated_at = NOW()
//        WHERE id = $1`,
//       [parseInt(invoiceId)]
//     );
//     console.log(`   ✅ Updated invoice status to 'approved'`);

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

//     // ==========================================
//     // ✅ Call approveInvoice to generate PDF and send email
//     // ==========================================
//     console.log(`📧 Calling approveInvoice to generate PDF and send email...`);
    
//     try {
//       // Create a proper request object for approveInvoice
//       const approveReq = {
//         params: { id: String(invoiceId) },
//         session: { user: { id: userId, orgId: orgId } }
//       };
      
//       // Create a response object to capture the result
//       let approveResult = null;
//       let approveStatusCode = null;
//       const approveRes = {
//         status: (code) => {
//           approveStatusCode = code;
//           return {
//             json: (data) => {
//               approveResult = data;
//               console.log(`   approveInvoice response code: ${code}, success: ${data?.success}`);
//               return data;
//             }
//           };
//         },
//         json: (data) => {
//           approveResult = data;
//           console.log(`   approveInvoice response (direct json):`, data);
//           return data;
//         }
//       };
      
//       // Call the approveInvoice function
//       await approveInvoice(approveReq, approveRes);
      
//       // Wait a bit to ensure any async operations complete
//       await new Promise(resolve => setTimeout(resolve, 2000));
      
//       if (approveResult && approveResult.success) {
//         console.log(`✅ PDF generated, gate pass created, and email sent for invoice ${invoiceId}`);
//         if (approveResult.gatePass) {
//           console.log(`✅ Gate pass: ${approveResult.gatePass.pass_number}`);
//         }
//       } else {
//         console.error(`❌ approveInvoice failed or returned unexpected response:`, approveResult);
//       }
//     } catch (emailError) {
//       console.error("❌ Error in approveInvoice call:", emailError.message);
//       console.error("❌ Full error:", emailError);
//       // Don't fail the super-approval if PDF/email fails
//     }

//     return res.json({
//       success: true,
//       message: "Sales invoice super-approved successfully! PDF generated and email sent to customer.",
//       data: {
//         currentStep: finalStep,
//         totalSteps: totalSteps,
//         allApproved: true,
//         invoiceStatus: 'approved'
//       }
//     });
    
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Error in superApproveSalesInvoice:", error);
//     res.status(500).json({ success: false, message: error.message });
//   } finally {
//     client.release();
//   }
// };
const superApproveSalesInvoice = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { invoiceId } = req.params;
    const { comments } = req.body;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Role check for super approval
    const userRole = req.user?.role?.toLowerCase();
    if (userRole !== 'admin' && userRole !== 'superadmin' && userRole !== 'ceo') {
      return res.status(403).json({ success: false, message: "Unauthorized for super approval" });
    }

    const invoiceCheck = await client.query(
      `SELECT id, status FROM public.sales_invoices
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parseInt(invoiceId), orgId]
    );
    
    if (invoiceCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Sales invoice not found" });
    }
    
    if (invoiceCheck.rows[0].status === "approved" || invoiceCheck.rows[0].status === "Approved") {
      return res.status(400).json({ success: false, message: "Sales invoice is already approved" });
    }

    await client.query("BEGIN");

    // Get total workflow steps
    const workflow = await client.query(
      `SELECT COUNT(*) AS total
       FROM public.approval_workflows aw
       JOIN public.module_types mt ON aw.module_type_id = mt.id
       WHERE mt.module_code = 'SALES_INVOICE'`,
      []
    );
    
    const totalSteps = parseInt(workflow.rows[0].total) || 1;
    const finalStep = totalSteps + 1;

    console.log(`📌 Super approving invoice ${invoiceId}. Total steps: ${totalSteps}`);

    // Update approval progress
    const existing = await client.query(
      `SELECT id FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
      [parseInt(invoiceId)]
    );
    
    if (existing.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_invoice_approval_progress
          (invoice_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, 'approved', $3, NOW(), $4)`,
        [parseInt(invoiceId), finalStep, userId, comments || "Super approved by admin"]
      );
    } else {
      await client.query(
        `UPDATE public.sales_invoice_approval_progress
         SET current_step = $1, 
             status = 'approved', 
             approved_by = $2,
             approved_at = NOW(), 
             comments = $3, 
             updated_at = NOW()
         WHERE invoice_id = $4`,
        [finalStep, userId, comments || "Super approved by admin", parseInt(invoiceId)]
      );
    }

    // Mark invoice as approved
    await client.query(
      `UPDATE public.sales_invoices
       SET status = 'approved', updated_at = NOW()
       WHERE id = $1`,
      [parseInt(invoiceId)]
    );

    await client.query("COMMIT");

    // ==========================================
    // ✅ Now call approveInvoice logic directly (not as separate API)
    // ==========================================
    console.log(`📧 Generating PDF, gate pass, and sending email...`);
    
    try {
      // Call the complete approval process
      await processCompleteApproval(invoiceId, userId, orgId, client);
      console.log(`✅ PDF generated, gate pass created, and email sent for invoice ${invoiceId}`);
    } catch (processError) {
      console.error("❌ Error in approval process:", processError.message);
      // Don't fail the super-approval if PDF/email fails
    }

    return res.json({
      success: true,
      message: "Sales invoice super-approved successfully! PDF generated and email sent to customer.",
      data: {
        currentStep: finalStep,
        totalSteps: totalSteps,
        allApproved: true,
        invoiceStatus: 'approved'
      }
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in superApproveSalesInvoice:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// Helper function to process complete approval (PDF, Gate Pass, Email)
const processCompleteApproval = async (invoiceId, userId, orgId, client) => {
  // Get invoice details
  const invoiceRes = await client.query(
    `SELECT * FROM sales_invoices WHERE id = $1 AND org_id = $2`,
    [invoiceId, orgId]
  );
  
  if (!invoiceRes.rows.length) {
    throw new Error("Invoice not found");
  }
  
  const invoice = invoiceRes.rows[0];
  
  // Get items
  const itemsRes = await client.query(
    `SELECT * FROM sales_invoice_items WHERE invoice_id = $1`,
    [invoiceId]
  );
  const dbItems = itemsRes.rows;
  
  // Get company details
  const companyRes = await client.query(
    `SELECT org_name, address, gst_number AS gstin, cin_number AS cin,
            phone, email, bank_details, bank_name, account_number, ifsc_code, branch
     FROM organizations WHERE id = $1`,
    [orgId]
  );
  const company = companyRes.rows[0] || {};
  
  // Get creator and approver names
  const creatorRes = await client.query(
    `SELECT u.name as created_by_name
     FROM sales_invoices si
     LEFT JOIN users u ON si.created_by = u.id
     WHERE si.id = $1`,
    [invoiceId]
  );
  const creatorName = creatorRes.rows[0]?.created_by_name || "-";
  
  const approverRes = await client.query(
    `SELECT name FROM users WHERE id = $1`,
    [userId]
  );
  const approverName = approverRes.rows[0]?.name || "-";
  
  // Get transport details and create gate pass
  let transportDetails = {
    transporter_name: "-",
    vehicle_number: "-",
    from_location: "-",
    to_location: "-"
  };
  let gatePassData = null;
  
  if (invoice.sales_order_id) {
    const vehicleRes = await client.query(
      `SELECT 
        cv.id as vehicle_id, cv.vehicle_number, cv.vehicle_type,
        cv.driver_name, cv.driver_number, cv.transporter_name as agency_name,
        cv.sales_order_number, cv.gate_status, sd.shipment_number,
        ta.agency_name as transporter_name, so.so_number
       FROM sales_orders so
       LEFT JOIN shipment_dispatches sd ON sd.sales_order_id = so.id
       LEFT JOIN commercial_vehicles cv ON cv.id = sd.vehicle_id
       LEFT JOIN transport_agencies ta ON ta.id = sd.transport_agency_id
       WHERE so.id = $1`,
      [invoice.sales_order_id]
    );
    
    if (vehicleRes.rows[0]) {
      const vehicleData = vehicleRes.rows[0];
      transportDetails = {
        transporter_name: vehicleData.transporter_name || vehicleData.agency_name || "-",
        vehicle_number: vehicleData.vehicle_number || "-",
        from_location: vehicleData.shipping_address || "-",
        to_location: vehicleData.delivery_address || "-"
      };
      
      // Create gate pass if vehicle exists
      const year = new Date().getFullYear();
      const prefix = `GP-${year}-`;
      
      const lastGatePass = await client.query(
        `SELECT pass_number FROM gate_passes WHERE pass_number LIKE $1 ORDER BY id DESC LIMIT 1`,
        [`${prefix}%`]
      );
      
      let nextNumber = 1;
      if (lastGatePass.rows.length > 0) {
        const lastNum = parseInt(lastGatePass.rows[0].pass_number.replace(prefix, ''), 10);
        if (!isNaN(lastNum)) nextNumber = lastNum + 1;
      }
      
      const gatePassNo = `${prefix}${String(nextNumber).padStart(4, '0')}`;
      
      const soRes = await client.query(
        `SELECT so_number FROM sales_orders WHERE id = $1`,
        [invoice.sales_order_id]
      );
      
      const insertResult = await client.query(
        `INSERT INTO gate_passes (
          commercial_vehicle_id, pass_number, pass_type, pass_status,
          generated_at, generated_by, org_id, sales_order_number, created_at, updated_at
        ) VALUES ($1, $2, 'ENTRY', 'ACTIVE', NOW(), $3, $4, $5, NOW(), NOW())
        RETURNING id, pass_number, generated_at`,
        [vehicleData.vehicle_id, gatePassNo, userId, orgId, soRes.rows[0]?.so_number || invoice.sales_order_number]
      );
      
      gatePassData = insertResult.rows[0];
    }
  }
  
  // Get sales order details
  const soRes = await client.query(
    `SELECT so_number, po_reference, created_at FROM sales_orders WHERE id = $1`,
    [invoice.sales_order_id]
  );
  const salesOrder = soRes.rows[0] || {};
  
  // Generate PDFs
  const eWayPdf = await generateEWayPDF();
  const eInvoicePdf = await generateEInvoicePDF();
  
  let gatePassPdf = null;
  if (gatePassData) {
    try {
      gatePassPdf = await generateGatePassPDF(gatePassData);
    } catch (err) {
      console.error("Gate pass PDF generation failed:", err.message);
    }
  }
  
  // Generate Invoice PDF
  const pdfBuffer = await generateInvoicePDF(
    {
      invoice_number: invoice.invoice_number,
      invoice_date: invoice.invoice_date,
      due_date: invoice.due_date,
      advance_adjusted_amount: Number(invoice.advance_adjusted_amount || 0),
      balance_payable_amount: Number(invoice.balance_payable_amount || 0),
      proforma_invoice_id: invoice.proforma_invoice_id || null,
      customer_name: invoice.customer_name,
      customer_address: invoice.customer_address,
      customer_gst: invoice.customer_gst,
      order_no: salesOrder.so_number || "",
      po_no: salesOrder.po_reference || "",
      po_date: salesOrder.created_at || "",
      gross: Number(invoice.amount || 0),
      discount: Number(invoice.total_discount || 0),
      net_value: Number(invoice.amount || 0) - Number(invoice.total_discount || 0),
      freight: Number(invoice.freight || 0),
      cgst: Number(invoice.total_tax || 0) / 2,
      sgst: Number(invoice.total_tax || 0) / 2,
      round_off: Number(invoice.round_off || 0),
      total: Number(invoice.total_amount || 0),
      prepared_by: creatorName,
      verified_by: creatorName,
      approved_by: approverName,
      transporter_name: transportDetails.transporter_name,
      vehicle_number: transportDetails.vehicle_number,
      authorized_signatory: company.org_name || "-",
    },
    dbItems,
    company
  );
  
  // Merge PDFs
  const pdfBuffers = [pdfBuffer, eWayPdf, eInvoicePdf, gatePassPdf].filter(pdf => pdf);
  const finalMergedPdf = await mergePDFBuffers(pdfBuffers);
  
  // Save PDF
  const uploadDir = path.join(process.cwd(), "uploads", "invoices");
  if (!fs.existsSync(uploadDir)) {
    fs.mkdirSync(uploadDir, { recursive: true });
  }
  
  const fileName = `Invoice-${invoice.invoice_number}.pdf`;
  const filePath = path.join(uploadDir, fileName);
  fs.writeFileSync(filePath, finalMergedPdf);
  
  // Save PDF path in DB
  await client.query(
    `UPDATE sales_invoices SET pdf_path = $1 WHERE id = $2`,
    [`uploads/invoices/${fileName}`, invoiceId]
  );
  
  // Send email
  const customerRes = await client.query(
    `SELECT email FROM customers WHERE customer_code = $1`,
    [invoice.customer_code]
  );
  const customerEmail = customerRes.rows[0]?.email;
  
  if (customerEmail) {
    const transporter = getTransporter();
    const emailHtml = `...`; // Your email HTML here
    
    await transporter.sendMail({
      from: `"Accounts Team" <${process.env.MAIL_USER}>`,
      to: customerEmail,
      subject: `Approved Invoice ${invoice.invoice_number}`,
      html: emailHtml,
      attachments: [{ filename: fileName, content: finalMergedPdf }],
    });
  }
  
  return { success: true };
};

// const superApproveSalesInvoice = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { invoiceId } = req.params;
//     const { comments } = req.body;
//     const userId = req.user?.userId || req.user?.id;
//     const orgId = req.user?.orgId;

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

//     const invoiceCheck = await client.query(
//       `SELECT id, status FROM public.sales_invoices
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [parseInt(invoiceId), orgId]
//     );
    
//     if (invoiceCheck.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "Sales invoice not found" });
//     }
    
//     if (invoiceCheck.rows[0].status === "approved" || invoiceCheck.rows[0].status === "Approved") {
//       return res.status(400).json({ success: false, message: "Sales invoice is already approved" });
//     }

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

//     // Get total workflow steps
//     const workflow = await client.query(
//       `SELECT COUNT(*) AS total
//        FROM public.approval_workflows aw
//        JOIN public.module_types mt ON aw.module_type_id = mt.id
//        WHERE mt.module_code = 'SALES_INVOICE'`,
//       []
//     );
    
//     const totalSteps = parseInt(workflow.rows[0].total) || 1;
//     const finalStep = totalSteps + 1;

//     console.log(`📌 Super approving invoice ${invoiceId}. Total steps: ${totalSteps}`);

//     // Check if progress exists
//     const existing = await client.query(
//       `SELECT id FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
//       [parseInt(invoiceId)]
//     );
    
//     if (existing.rows.length === 0) {
//       await client.query(
//         `INSERT INTO public.sales_invoice_approval_progress
//           (invoice_id, current_step, status, approved_by, approved_at, comments)
//          VALUES ($1, $2, 'approved', $3, NOW(), $4)`,
//         [parseInt(invoiceId), finalStep, userId, comments || "Super approved by admin"]
//       );
//       console.log(`   ✅ Created progress record with step ${finalStep}`);
//     } else {
//       await client.query(
//         `UPDATE public.sales_invoice_approval_progress
//          SET current_step = $1, 
//              status = 'approved', 
//              approved_by = $2,
//              approved_at = NOW(), 
//              comments = $3, 
//              updated_at = NOW()
//          WHERE invoice_id = $4`,
//         [finalStep, userId, comments || "Super approved by admin", parseInt(invoiceId)]
//       );
//       console.log(`   ✅ Updated progress record to step ${finalStep}`);
//     }

//     // Mark invoice as approved
//     await client.query(
//       `UPDATE public.sales_invoices
//        SET status = 'approved', updated_at = NOW()
//        WHERE id = $1`,
//       [parseInt(invoiceId)]
//     );
//     console.log(`   ✅ Updated invoice status to 'approved'`);

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

//     // ==========================================
//     // ✅ CRITICAL: Call approveInvoice to generate PDF and send email
//     // ==========================================
//     console.log(`📧 Calling approveInvoice to generate PDF and send email...`);
    
//     try {
//       // Create a proper request object for approveInvoice
//       const approveReq = {
//         params: { id: String(invoiceId) },
//         session: { user: { id: userId, orgId: orgId } }
//       };
      
//       // Create a response object to capture the result
//       let approveResult = null;
//       const approveRes = {
//         status: (code) => ({
//           json: (data) => {
//             approveResult = { code, data };
//             return data;
//           }
//         })
//       };
      
//       // Call the approveInvoice function
//       await approveInvoice(approveReq, approveRes);
      
//       if (approveResult && approveResult.data && approveResult.data.success) {
//         console.log(`✅ PDF generated, gate pass created, and email sent for invoice ${invoiceId}`);
//         if (approveResult.data.gatePass) {
//           console.log(`✅ Gate pass: ${approveResult.data.gatePass.pass_number}`);
//         }
//       } else {
//         console.error(`❌ approveInvoice failed:`, approveResult?.data?.message);
//       }
//     } catch (emailError) {
//       console.error("❌ Error in approveInvoice call:", emailError.message);
//       // Don't fail the super-approval if PDF/email fails
//     }

//     return res.json({
//       success: true,
//       message: "Sales invoice super-approved successfully! PDF generated and email sent to customer.",
//       data: {
//         currentStep: finalStep,
//         totalSteps: totalSteps,
//         allApproved: true,
//         invoiceStatus: 'approved'
//       }
//     });
    
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Error in superApproveSalesInvoice:", error);
//     res.status(500).json({ success: false, message: error.message });
//   } finally {
//     client.release();
//   }
// };


  // ==================== SUPER-APPROVE SALES INVOICE (admin/CEO bypass) ====================
// const superApproveSalesInvoice = async (req, res) => {
//   const client = await pool.connect();
//   try {
//     const { invoiceId } = req.params;
//     const { comments } = req.body;
//     const userId = req.user?.userId || req.user?.id;
//     const orgId = req.user?.orgId;

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

//     const invoiceCheck = await client.query(
//       `SELECT id, status FROM public.sales_invoices
//        WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
//       [parseInt(invoiceId), orgId]
//     );
    
//     if (invoiceCheck.rows.length === 0) {
//       return res.status(404).json({ success: false, message: "Sales invoice not found" });
//     }
    
//     if (invoiceCheck.rows[0].status === "approved" || invoiceCheck.rows[0].status === "Approved") {
//       return res.status(400).json({ success: false, message: "Sales invoice is already approved" });
//     }

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

//     // Get total workflow steps
//     const workflow = await client.query(
//       `SELECT COUNT(*) AS total
//        FROM public.approval_workflows aw
//        JOIN public.module_types mt ON aw.module_type_id = mt.id
//        WHERE mt.module_code = 'SALES_INVOICE'`,
//       []
//     );
    
//     const totalSteps = parseInt(workflow.rows[0].total) || 1;
//     const finalStep = totalSteps + 1;

//     // Update progress
//     const existing = await client.query(
//       `SELECT id FROM public.sales_invoice_approval_progress WHERE invoice_id = $1`,
//       [parseInt(invoiceId)]
//     );
    
//     if (existing.rows.length === 0) {
//       await client.query(
//         `INSERT INTO public.sales_invoice_approval_progress
//           (invoice_id, current_step, status, approved_by, approved_at, comments)
//          VALUES ($1, $2, 'approved', $3, NOW(), $4)`,
//         [parseInt(invoiceId), finalStep, userId, comments || "Super approved by admin"]
//       );
//     } else {
//       await client.query(
//         `UPDATE public.sales_invoice_approval_progress
//          SET current_step = $1, 
//              status = 'approved', 
//              approved_by = $2,
//              approved_at = NOW(), 
//              comments = $3, 
//              updated_at = NOW()
//          WHERE invoice_id = $4`,
//         [finalStep, userId, comments || "Super approved by admin", parseInt(invoiceId)]
//       );
//     }

//     // Mark invoice as approved
//     await client.query(
//       `UPDATE public.sales_invoices
//        SET status = 'approved', updated_at = NOW()
//        WHERE id = $1`,
//       [parseInt(invoiceId)]
//     );

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

//     // ✅ Call the approveInvoice API to send email and generate documents
//     try {
//       const mockReq = {
//         params: { id: invoiceId },
//         session: { user: { id: userId, orgId: orgId } }
//       };
      
//       const mockRes = {
//         status: (code) => ({
//           json: (data) => {
//             if (data.success) {
//               console.log("✅ Email and documents generated successfully for super-approval");
//             } else {
//               console.error("❌ Failed to generate documents:", data.message);
//             }
//           }
//         })
//       };
      
//       await approveInvoice(mockReq, mockRes);
//       console.log(`✅ Email sent and PDF generated for super-approved invoice ${invoiceId}`);
//     } catch (emailError) {
//       console.error("❌ Error in super-approval processing:", emailError);
//       // Don't fail the super-approval if email fails, just log it
//     }

//     return res.json({
//       success: true,
//       message: "Sales invoice super-approved successfully. Email sent to customer.",
//       data: {
//         currentStep: finalStep,
//         totalSteps: totalSteps,
//         allApproved: true,
//         invoiceStatus: 'approved'
//       }
//     });
    
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Error in superApproveSalesInvoice:", error);
//     res.status(500).json({ success: false, message: error.message });
//   } finally {
//     client.release();
//   }
// };


  // ==================== GET SALES INVOICE APPROVAL PROGRESS ====================
const getSalesInvoiceApprovalProgress = async (req, res) => {
  try {
    const { invoiceId } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT siap.*, 
              u.first_name as approved_by_first_name, 
              u.last_name as approved_by_last_name
       FROM public.sales_invoice_approval_progress siap
       LEFT JOIN public.users u ON siap.approved_by = u.id
       WHERE siap.invoice_id = $1`,
      [parseInt(invoiceId)]
    );

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

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

// ==================== BACKFILL SALES INVOICE APPROVAL PROGRESS ====================
const backfillSalesInvoiceApprovalProgress = async (req, res) => {
  const client = await pool.connect();
  try {
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // Get all sales invoices without progress records
    const invoiceResult = await client.query(
      `SELECT si.id 
       FROM public.sales_invoices si
       LEFT JOIN public.sales_invoice_approval_progress siap ON si.id = siap.invoice_id
       WHERE siap.id IS NULL 
         AND si.is_deleted = false
         AND si.org_id = $1`,
      [orgId]
    );

    let insertedCount = 0;
    for (const invoice of invoiceResult.rows) {
      await client.query(
        `INSERT INTO public.sales_invoice_approval_progress (invoice_id, current_step, status, created_at, updated_at)
         VALUES ($1, 1, 'pending', NOW(), NOW())`,
        [invoice.id]
      );
      insertedCount++;
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Successfully backfilled approval progress for ${insertedCount} Sales Invoice(s)`,
      data: { backfilledCount: insertedCount }
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in backfillSalesInvoiceApprovalProgress:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== CREATE SALES INVOICE APPROVAL PROGRESS TABLE ====================
const createSalesInvoiceApprovalProgressTable = async (req, res) => {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS public.sales_invoice_approval_progress (
        id SERIAL PRIMARY KEY,
        invoice_id INTEGER NOT NULL REFERENCES public.sales_invoices(id) ON DELETE CASCADE,
        current_step INTEGER DEFAULT 1,
        status VARCHAR(20) DEFAULT 'pending',
        approved_by INTEGER,
        approved_at TIMESTAMP,
        comments TEXT,
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(invoice_id)
      )
    `);

    // Create indexes for better performance
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_siap_invoice_id ON public.sales_invoice_approval_progress(invoice_id);
      CREATE INDEX IF NOT EXISTS idx_siap_status ON public.sales_invoice_approval_progress(status);
    `);

    res.json({
      success: true,
      message: "sales_invoice_approval_progress table created successfully"
    });

  } catch (error) {
    console.error("Error creating sales invoice approval progress table:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};



// ==================== GET SALES ORDER APPROVALS ====================
const getSalesOrderApprovals = async (req, res) => {
  try {
    const { orderId } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Get order details
    const orderResult = await pool.query(
      `SELECT so.created_by, so.approval_status, so.so_number,
              u.first_name AS creator_first_name, u.last_name AS creator_last_name,
              u.role AS creator_role
       FROM public.sales_orders so
       LEFT JOIN public.users u ON so.created_by::text = u.id::text
       WHERE so.id = $1 AND so.org_id = $2 AND so.is_deleted = false`,
      [parseInt(orderId), orgId]
    );

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

    const order = orderResult.rows[0];
    const orderStatus = order.approval_status?.toLowerCase();
    
    // Get workflow for Sales Order module (module_type_id = 4 or get dynamically)
    const workflowResult = await pool.query(
      `SELECT aw.id, aw.module_type_id, aw.user_id, aw.approval_order,
              aw.is_mandatory, aw.max_approval_days,
              u.first_name, u.last_name, u.email, u.role
       FROM public.approval_workflows aw
       JOIN public.module_types mt ON aw.module_type_id = mt.id
       JOIN public.users u ON aw.user_id = u.id
       WHERE mt.module_code = 'SALES_ORDER'
       ORDER BY aw.approval_order`,
      []
    );

    const creatorInfo = {
      creator_first_name: order.creator_first_name,
      creator_last_name: order.creator_last_name,
      creator_role: order.creator_role,
      creator_id: order.created_by,
    };

    if (workflowResult.rows.length === 0) {
      return res.json({
        success: true,
        data: [],
        approvalStatus: "no_workflow",
        creatorInfo,
        currentUserId: userId,
        currentStep: 1,
        totalSteps: 0,
      });
    }

    // Get progress
    const progressResult = await pool.query(
      `SELECT current_step, status, approved_by, approved_at, comments
       FROM public.sales_order_approval_progress 
       WHERE sales_order_id = $1`,
      [parseInt(orderId)]
    );

    let currentStep = 1;
    if (progressResult.rows.length > 0) {
      currentStep = progressResult.rows[0].current_step;
    }

    // Check if order is approved
    const isOrderApproved = orderStatus === "approved";
    
    console.log(`Order ${orderId} - Status: ${orderStatus}, Current Step: ${currentStep}, Is Approved: ${isOrderApproved}`);

    // Build workflow with statuses
    const workflowWithStatus = workflowResult.rows.map(step => {
      let status = "pending";
      
      if (isOrderApproved) {
        status = "approved";
      } 
      else if (step.approval_order < currentStep) {
        status = "approved";
      } else if (step.approval_order === currentStep) {
        status = "pending";
      }
      
      return {
        ...step,
        status,
        approved_at: null,
        comments: null
      };
    });

    return res.json({
      success: true,
      data: workflowWithStatus,
      approvalStatus: isOrderApproved ? "approved" : "pending",
      currentStep: currentStep,
      totalSteps: workflowResult.rows.length,
      creatorInfo,
      currentUserId: userId,
    });
    
  } catch (error) {
    console.error("Error in getSalesOrderApprovals:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// ==================== APPROVE SALES ORDER (step-by-step) ====================
const approveSalesOrderStep = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { orderId } = req.params;
    const { comments } = req.body;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

    if (!userId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // 1. Check order exists
    const orderCheck = await client.query(
      `SELECT id, approval_status, so_number FROM public.sales_orders 
       WHERE id = $1 AND is_deleted = false`,
      [parseInt(orderId)]
    );
    
    if (orderCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Sales Order not found" });
    }

    const { approval_status, so_number } = orderCheck.rows[0];
    
    if (approval_status === "Approved") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Order already approved" });
    }
    
    if (approval_status === "Rejected") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Order already rejected" });
    }

    // 2. Get workflow for Sales Order module
    const workflow = await client.query(
      `SELECT user_id, approval_order 
       FROM public.approval_workflows 
       WHERE module_type_id = (SELECT id FROM module_types WHERE module_code = 'SALES_ORDER')
       ORDER BY approval_order`,
      []
    );
    
    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured" });
    }

    // 3. Check if user is an approver
    const userStep = workflow.rows.find(row => row.user_id == userId);
    
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not authorized" });
    }

    // 4. Get current progress
    let progress = await client.query(
      `SELECT current_step FROM public.sales_order_approval_progress WHERE sales_order_id = $1`,
      [parseInt(orderId)]
    );
    
    let currentStep = 1;
    
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_order_approval_progress (sales_order_id, current_step, status)
         VALUES ($1, 1, 'pending')`,
        [parseInt(orderId)]
      );
      currentStep = 1;
    } else {
      currentStep = progress.rows[0].current_step;
    }

    // 5. Check if it's user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ 
        success: false, 
        message: `Not your turn. Current step is ${currentStep}.` 
      });
    }

    // 6. Calculate next step
    const nextStep = currentStep + 1;
    const isLastStep = nextStep > workflow.rows.length;

    // 7. Update progress
    await client.query(
      `UPDATE public.sales_order_approval_progress 
       SET current_step = $1, 
           status = $2, 
           approved_by = $3, 
           approved_at = NOW(), 
           comments = $4,
           updated_at = NOW()
       WHERE sales_order_id = $5`,
      [nextStep, isLastStep ? "approved" : "pending", userId, comments || null, parseInt(orderId)]
    );

    // 8. If last step, mark order as approved
    if (isLastStep) {
      await client.query(
        `UPDATE public.sales_orders 
         SET approval_status = 'Approved', 
             approved_by = $1,
             approved_at = NOW(),
             updated_at = NOW()
         WHERE id = $2`,
        [userId, parseInt(orderId)]
      );

      await client.query("COMMIT");
      
      console.log(`✅ Sales Order ${so_number} fully approved. Sending email...`);
      
      // Send approval email to customer
      try {
        // Get order details with customer email
        const orderDetails = await client.query(
          `SELECT so.*, c.email as customer_email 
           FROM sales_orders so
           LEFT JOIN customers c ON c.customer_code = so.customer_id
           WHERE so.id = $1`,
          [parseInt(orderId)]
        );
        
        if (orderDetails.rows[0]?.customer_email) {
          await sendSalesOrderApprovalEmail(orderDetails.rows[0]);
        }
      } catch (emailError) {
        console.error("❌ Error sending approval email:", emailError);
      }
      
      return res.json({
        success: true,
        message: "Sales Order fully approved! Email sent to customer.",
        allApproved: true,
        currentStep,
        nextStep: null
      });
    }

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: `Step ${currentStep} approved successfully. Waiting for next approver.`,
      allApproved: false,
      currentStep,
      nextStep: nextStep
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Approve Sales Order Error:", error);
    return res.status(500).json({ 
      success: false, 
      message: error.message || "Approval failed" 
    });
  } finally {
    client.release();
  }
};

// Helper function to send approval email
const sendSalesOrderApprovalEmail = async (order) => {
  const transporter = getTransporter();
  
  const emailHtml = `
    <div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">
      <table width="100%" cellpadding="0" cellspacing="0" style="max-width:750px;margin:auto;background:#ffffff;border-radius:8px;border:1px solid #e5e7eb;">
        <tr style="background:#16a34a;color:#ffffff;">
          <td style="padding:16px 20px;font-size:18px;font-weight:600;">
            Sales Order Approved
          </td>
        </tr>
        <tr>
          <td style="padding:20px;color:#374151;font-size:14px;line-height:1.6;">
            <p>Dear <strong>${order.customer_name}</strong>,</p>
            <p>Your Sales Order <strong>${order.so_number}</strong> has been approved successfully.</p>
            <p>Total Amount: <strong>₹${Number(order.total_amount || 0).toFixed(2)}</strong></p>
            <p>Regards,<br/><strong>Sales Team</strong></p>
          </td>
        </tr>
      </table>
    </div>
  `;
  
  await transporter.sendMail({
    from: `"Sales Team" <${process.env.MAIL_USER}>`,
    to: order.customer_email,
    subject: `Sales Order ${order.so_number} Approved`,
    html: emailHtml,
  });
};

// ==================== REJECT SALES ORDER ====================
const rejectSalesOrder = async (req, res) => {
  const client = await pool.connect();
  try {
    const { orderId } = req.params;
    const { comments } = req.body;
    const userId = req.user?.userId || req.user?.id;

    if (!userId) {
      return res.status(401).json({ success: false, message: "Unauthorized - User ID missing" });
    }
    if (!comments || !comments.trim()) {
      return res.status(400).json({ success: false, message: "Rejection reason is required" });
    }

    await client.query("BEGIN");

    // Check order
    const orderCheck = await client.query(
      `SELECT id, approval_status FROM public.sales_orders WHERE id = $1 AND is_deleted = false`,
      [parseInt(orderId)]
    );
    
    if (orderCheck.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "Sales Order not found" });
    }
    
    const { approval_status } = orderCheck.rows[0];
    
    if (approval_status === "Approved") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Sales Order is already approved and cannot be rejected" });
    }
    
    if (approval_status === "Rejected") {
      await client.query("ROLLBACK");
      return res.status(400).json({ success: false, message: "Sales Order is already rejected" });
    }

    // Get workflow
    const workflow = await client.query(
      `SELECT aw.user_id, aw.approval_order
       FROM public.approval_workflows aw
       WHERE aw.module_type_id = (SELECT id FROM module_types WHERE module_code = 'SALES_ORDER')
       ORDER BY aw.approval_order`,
      []
    );
    
    if (workflow.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ success: false, message: "No workflow configured for Sales Order" });
    }

    const userStep = workflow.rows.find((row) => row.user_id == userId);
    
    if (!userStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({ success: false, message: "You are not an approver for Sales Order" });
    }

    // Get current progress
    let progress = await client.query(
      `SELECT current_step FROM public.sales_order_approval_progress WHERE sales_order_id = $1`,
      [parseInt(orderId)]
    );
    
    let currentStep = 1;
    
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_order_approval_progress (sales_order_id, current_step, status)
         VALUES ($1, 1, 'pending')`,
        [parseInt(orderId)]
      );
      currentStep = 1;
    } else {
      currentStep = progress.rows[0].current_step;
    }

    // Check if it's user's turn
    if (userStep.approval_order !== currentStep) {
      await client.query("ROLLBACK");
      return res.status(403).json({
        success: false,
        message: `You cannot reject at this time. Current step is ${currentStep}.`,
      });
    }

    // Update progress as rejected
    if (progress.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_order_approval_progress
          (sales_order_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, 'rejected', $3, NOW(), $4)`,
        [parseInt(orderId), currentStep, userId, comments.trim()]
      );
    } else {
      await client.query(
        `UPDATE public.sales_order_approval_progress
         SET status = 'rejected', 
             approved_by = $1,
             approved_at = NOW(), 
             comments = $2, 
             updated_at = NOW()
         WHERE sales_order_id = $3`,
        [userId, comments.trim(), parseInt(orderId)]
      );
    }

    // Mark order as rejected
    await client.query(
      `UPDATE public.sales_orders
       SET approval_status = 'Rejected', updated_at = NOW()
       WHERE id = $1`,
      [parseInt(orderId)]
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Sales Order rejected successfully",
      rejectedBy: userId,
      rejectedAt: new Date(),
      rejectedStep: currentStep
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in rejectSalesOrder:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== SUPER-APPROVE SALES ORDER ====================
const superApproveSalesOrder = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { orderId } = req.params;
    const { comments } = req.body;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Role check for super approval
    const userRole = req.user?.role?.toLowerCase();
    if (userRole !== 'admin' && userRole !== 'superadmin' && userRole !== 'ceo') {
      return res.status(403).json({ success: false, message: "Unauthorized for super approval" });
    }

    const orderCheck = await client.query(
      `SELECT id, approval_status, so_number FROM public.sales_orders
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [parseInt(orderId), orgId]
    );
    
    if (orderCheck.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Sales Order not found" });
    }
    
    if (orderCheck.rows[0].approval_status === "Approved") {
      return res.status(400).json({ success: false, message: "Sales Order is already approved" });
    }

    await client.query("BEGIN");

    // Get total workflow steps
    const workflow = await client.query(
      `SELECT COUNT(*) AS total
       FROM public.approval_workflows aw
       JOIN public.module_types mt ON aw.module_type_id = mt.id
       WHERE mt.module_code = 'SALES_ORDER'`,
      []
    );
    
    const totalSteps = parseInt(workflow.rows[0].total) || 1;
    const finalStep = totalSteps + 1;

    console.log(`📌 Super approving order ${orderId}. Total steps: ${totalSteps}`);

    // Update approval progress
    const existing = await client.query(
      `SELECT id FROM public.sales_order_approval_progress WHERE sales_order_id = $1`,
      [parseInt(orderId)]
    );
    
    if (existing.rows.length === 0) {
      await client.query(
        `INSERT INTO public.sales_order_approval_progress
          (sales_order_id, current_step, status, approved_by, approved_at, comments)
         VALUES ($1, $2, 'approved', $3, NOW(), $4)`,
        [parseInt(orderId), finalStep, userId, comments || "Super approved by admin"]
      );
    } else {
      await client.query(
        `UPDATE public.sales_order_approval_progress
         SET current_step = $1, 
             status = 'approved', 
             approved_by = $2,
             approved_at = NOW(), 
             comments = $3, 
             updated_at = NOW()
         WHERE sales_order_id = $4`,
        [finalStep, userId, comments || "Super approved by admin", parseInt(orderId)]
      );
    }

    // Mark order as approved
    await client.query(
      `UPDATE public.sales_orders
       SET approval_status = 'Approved', 
           approved_by = $1,
           approved_at = NOW(),
           updated_at = NOW()
       WHERE id = $2`,
      [userId, parseInt(orderId)]
    );

    await client.query("COMMIT");

    // Send approval email
    try {
      const orderDetails = await client.query(
        `SELECT so.*, c.email as customer_email 
         FROM sales_orders so
         LEFT JOIN customers c ON c.customer_code = so.customer_id
         WHERE so.id = $1`,
        [parseInt(orderId)]
      );
      
      if (orderDetails.rows[0]?.customer_email) {
        await sendSalesOrderApprovalEmail(orderDetails.rows[0]);
      }
    } catch (emailError) {
      console.error("❌ Error sending approval email:", emailError);
    }

    return res.json({
      success: true,
      message: "Sales Order super-approved successfully! Email sent to customer.",
      data: {
        currentStep: finalStep,
        totalSteps: totalSteps,
        allApproved: true,
        orderStatus: 'approved'
      }
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in superApproveSalesOrder:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== GET SALES ORDER APPROVAL PROGRESS ====================
const getSalesOrderApprovalProgress = async (req, res) => {
  try {
    const { orderId } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT soap.*, 
              u.first_name as approved_by_first_name, 
              u.last_name as approved_by_last_name
       FROM public.sales_order_approval_progress soap
       LEFT JOIN public.users u ON soap.approved_by = u.id
       WHERE soap.sales_order_id = $1`,
      [parseInt(orderId)]
    );

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

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

// ==================== BACKFILL SALES ORDER APPROVAL PROGRESS ====================
const backfillSalesOrderApprovalProgress = async (req, res) => {
  const client = await pool.connect();
  try {
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    await client.query("BEGIN");

    // Get all sales orders without progress records
    const orderResult = await client.query(
      `SELECT so.id 
       FROM public.sales_orders so
       LEFT JOIN public.sales_order_approval_progress soap ON so.id = soap.sales_order_id
       WHERE soap.id IS NULL 
         AND so.is_deleted = false
         AND so.org_id = $1`,
      [orgId]
    );

    let insertedCount = 0;
    for (const order of orderResult.rows) {
      await client.query(
        `INSERT INTO public.sales_order_approval_progress (sales_order_id, current_step, status, created_at, updated_at)
         VALUES ($1, 1, 'pending', NOW(), NOW())`,
        [order.id]
      );
      insertedCount++;
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: `Successfully backfilled approval progress for ${insertedCount} Sales Order(s)`,
      data: { backfilledCount: insertedCount }
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Error in backfillSalesOrderApprovalProgress:", error);
    res.status(500).json({ success: false, message: error.message });
  } finally {
    client.release();
  }
};

// ==================== CREATE SALES ORDER APPROVAL PROGRESS TABLE ====================
const createSalesOrderApprovalProgressTable = async (req, res) => {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS public.sales_order_approval_progress (
        id SERIAL PRIMARY KEY,
        sales_order_id INTEGER NOT NULL REFERENCES public.sales_orders(id) ON DELETE CASCADE,
        current_step INTEGER DEFAULT 1,
        status VARCHAR(20) DEFAULT 'pending',
        approved_by INTEGER,
        approved_at TIMESTAMP,
        comments TEXT,
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(sales_order_id)
      )
    `);

    // Create indexes for better performance
    await pool.query(`
      CREATE INDEX IF NOT EXISTS idx_soap_sales_order_id ON public.sales_order_approval_progress(sales_order_id);
      CREATE INDEX IF NOT EXISTS idx_soap_status ON public.sales_order_approval_progress(status);
    `);

    res.json({
      success: true,
      message: "sales_order_approval_progress table created successfully"
    });

  } catch (error) {
    console.error("Error creating sales order approval progress table:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

module.exports = {
  getModuleTypes,
  getUsers,
  getWorkflowByModule,
  saveWorkflow,
  deleteWorkflow,
  getWorkflowById,
  updateWorkflowOrder,
  getMyApprovalTasks,
  checkIsApprover,
  getPOApprovals,
  approvePO,
  rejectPO,
  backfillApprovalProgress,
  getPOApprovalProgress,
  createApprovalProgressTable,
  getGRNApprovals,
  approveGRN,
  rejectGRN,
  approvePurchaseInvoice,
  superApproveSalesInvoice,
  rejectSalesInvoice,
  approveSalesInvoice,
  getSalesInvoiceApprovals,
  getSalesInvoiceApprovalProgress,  // Add this
  backfillSalesInvoiceApprovalProgress,  // Add this
  createSalesInvoiceApprovalProgressTable,  // Add this
    getSalesOrderApprovals,
  approveSalesOrderStep,
  rejectSalesOrder,
  superApproveSalesOrder,
  getSalesOrderApprovalProgress,
  backfillSalesOrderApprovalProgress,
  createSalesOrderApprovalProgressTable,
};