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

// ==================== 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 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 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) {
      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) {
      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) {
      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)]
      );
    }

    await client.query("COMMIT");

    res.json({
      success: true,
      message: isLastStep ? "PO fully approved!" : `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();
  }
};


module.exports = {
  getModuleTypes,
  getUsers,
  getWorkflowByModule,
  saveWorkflow,
  deleteWorkflow,
  getWorkflowById,
  updateWorkflowOrder,
  getMyApprovalTasks,
  checkIsApprover,
  getPOApprovals,
  approvePO,
  rejectPO,
  backfillApprovalProgress,
  getPOApprovalProgress,
  createApprovalProgressTable,
  getGRNApprovals,
  approveGRN,
  rejectGRN,
  approvePurchaseInvoice
};