// controllers/securityops/outwardController.js
const { pool } = require("../../config/db");

// Create new outward/dispatch record
const createOutward = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

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

    const {
      vehicleNo,
      vehicleType,
      driverName,
      driverPhone,
      itemName,
      quantity,
      quantityUnit = 'kg',
      outWeight,
      outWeightUnit = 'kg',
      approvedBy,
      approvedByName,
      poNumber,
      warehouseId,
      destination,
      remarks
    } = req.body;

    // Generate outward number
    const timestamp = new Date();
    const year = timestamp.getFullYear();
    const month = String(timestamp.getMonth() + 1).padStart(2, '0');
    const sequenceQuery = await pool.query(
      `SELECT COUNT(*) as count FROM outward_dispatches 
       WHERE EXTRACT(YEAR FROM created_at) = $1 AND org_id = $2`,
      [year, orgId]
    );
    const sequence = parseInt(sequenceQuery.rows[0].count) + 1;
    const outwardNo = `OUT-${year}${month}${String(sequence).padStart(4, '0')}`;

    const query = `
      INSERT INTO outward_dispatches (
        outward_no, vehicle_no, vehicle_type, driver_name, driver_phone,
        item_name, quantity, quantity_unit, out_weight, out_weight_unit,
        approved_by, approved_by_name, po_number, warehouse_id, destination,
        remarks, status, created_by, org_id
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, 'Ready', $17, $18)
      RETURNING *
    `;

    const values = [
      outwardNo, vehicleNo, vehicleType, driverName, driverPhone,
      itemName, quantity, quantityUnit, outWeight, outWeightUnit,
      approvedBy || userId, approvedByName, poNumber, warehouseId,
      destination, remarks, userId, orgId
    ];

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

    return res.status(201).json({
      success: true,
      message: "Outward created successfully",
      data: result.rows[0]
    });

  } catch (error) {
    console.error("Create Outward Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to create outward record",
      error: error.message
    });
  }
};

// Get all outward records with pagination and filters
const getAllOutward = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const {
      page = 1,
      limit = 10,
      search = '',
      status,
      startDate,
      endDate,
      sortBy = 'created_at',
      sortOrder = 'DESC'
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    
    let whereConditions = ['o.is_deleted = false', 'o.org_id = $1'];
    let values = [orgId];
    let paramIndex = 2;

    if (search) {
      whereConditions.push(`(o.vehicle_no ILIKE $${paramIndex} OR o.driver_name ILIKE $${paramIndex} OR o.vehicle_type ILIKE $${paramIndex})`);
      values.push(`%${search}%`);
      paramIndex++;
    }

    if (status) {
      whereConditions.push(`o.status = $${paramIndex}`);
      values.push(status);
      paramIndex++;
    }

    if (startDate) {
      whereConditions.push(`DATE(o.created_at) >= $${paramIndex}`);
      values.push(startDate);
      paramIndex++;
    }

    if (endDate) {
      whereConditions.push(`DATE(o.created_at) <= $${paramIndex}`);
      values.push(endDate);
      paramIndex++;
    }

    const whereClause = whereConditions.length > 0 
      ? `WHERE ${whereConditions.join(' AND ')}`
      : '';

    // Get total count
    const countQuery = `
      SELECT COUNT(*) as total 
      FROM outward_dispatches o
      ${whereClause}
    `;
    const countResult = await pool.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);

    // Get paginated data
    const dataQuery = `
      SELECT 
        o.id,
        o.outward_no,
        o.vehicle_no,
        o.vehicle_type,
        o.driver_name,
        o.driver_phone,
        o.item_name,
        o.quantity,
        o.quantity_unit,
        o.out_weight,
        o.out_weight_unit,
        o.approved_by_name,
        o.time_out,
        o.status,
        o.po_number,
        o.destination,
        o.remarks,
        o.created_at,
        o.updated_at,
        w.warehouse_name,
        CONCAT(u.first_name, ' ', u.last_name) as created_by_name
      FROM outward_dispatches o
      LEFT JOIN warehouses w ON w.id = o.warehouse_id AND w.is_deleted = false
      LEFT JOIN users u ON u.id = o.created_by
      ${whereClause}
      ORDER BY ${sortBy} ${sortOrder}
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    values.push(parseInt(limit), offset);
    const result = await pool.query(dataQuery, values);

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

  } catch (error) {
    console.error("Get All Outward Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch outward records",
      error: error.message
    });
  }
};

// Get outward by ID
const getOutwardById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session?.user?.orgId;

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

    const query = `
      SELECT 
        o.id,
        o.outward_no,
        o.vehicle_no,
        o.vehicle_type,
        o.driver_name,
        o.driver_phone,
        o.item_name,
        o.quantity,
        o.quantity_unit,
        o.out_weight,
        o.out_weight_unit,
        o.approved_by,
        o.approved_by_name,
        o.time_out,
        o.status,
        o.po_number,
        o.warehouse_id,
        o.destination,
        o.remarks,
        o.created_at,
        o.updated_at,
        w.warehouse_name,
        CONCAT(u.first_name, ' ', u.last_name) as created_by_name
      FROM outward_dispatches o
      LEFT JOIN warehouses w ON w.id = o.warehouse_id AND w.is_deleted = false
      LEFT JOIN users u ON u.id = o.created_by
      WHERE o.id = $1 AND o.org_id = $2 AND o.is_deleted = false
    `;

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

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

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

  } catch (error) {
    console.error("Get Outward By ID Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch outward record",
      error: error.message
    });
  }
};

// Mark dispatch (update status and time_out)
const markDispatch = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

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

    const now = new Date();

    const query = `
      UPDATE outward_dispatches 
      SET 
        status = 'Dispatched',
        time_out = $1,
        updated_at = CURRENT_TIMESTAMP,
        updated_by = $2
      WHERE id = $3 AND org_id = $4 AND is_deleted = false
      RETURNING *
    `;

    const result = await pool.query(query, [now, userId, id, orgId]);

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

    // Update stock quantity if needed (reduce from inventory)
    // This would depend on your inventory management logic

    return res.status(200).json({
      success: true,
      message: "Vehicle dispatched successfully",
      data: result.rows[0]
    });

  } catch (error) {
    console.error("Mark Dispatch Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to mark dispatch",
      error: error.message
    });
  }
};

// Update outward record
const updateOutward = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

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

    const {
      vehicleNo,
      vehicleType,
      driverName,
      driverPhone,
      itemName,
      quantity,
      quantityUnit,
      outWeight,
      outWeightUnit,
      approvedBy,
      approvedByName,
      poNumber,
      warehouseId,
      destination,
      remarks,
      status
    } = req.body;

    const updateFields = [];
    const values = [];
    let paramIndex = 1;

    if (vehicleNo !== undefined) {
      updateFields.push(`vehicle_no = $${paramIndex++}`);
      values.push(vehicleNo);
    }
    if (vehicleType !== undefined) {
      updateFields.push(`vehicle_type = $${paramIndex++}`);
      values.push(vehicleType);
    }
    if (driverName !== undefined) {
      updateFields.push(`driver_name = $${paramIndex++}`);
      values.push(driverName);
    }
    if (driverPhone !== undefined) {
      updateFields.push(`driver_phone = $${paramIndex++}`);
      values.push(driverPhone);
    }
    if (itemName !== undefined) {
      updateFields.push(`item_name = $${paramIndex++}`);
      values.push(itemName);
    }
    if (quantity !== undefined) {
      updateFields.push(`quantity = $${paramIndex++}`);
      values.push(quantity);
    }
    if (quantityUnit !== undefined) {
      updateFields.push(`quantity_unit = $${paramIndex++}`);
      values.push(quantityUnit);
    }
    if (outWeight !== undefined) {
      updateFields.push(`out_weight = $${paramIndex++}`);
      values.push(outWeight);
    }
    if (outWeightUnit !== undefined) {
      updateFields.push(`out_weight_unit = $${paramIndex++}`);
      values.push(outWeightUnit);
    }
    if (approvedBy !== undefined) {
      updateFields.push(`approved_by = $${paramIndex++}`);
      values.push(approvedBy);
    }
    if (approvedByName !== undefined) {
      updateFields.push(`approved_by_name = $${paramIndex++}`);
      values.push(approvedByName);
    }
    if (poNumber !== undefined) {
      updateFields.push(`po_number = $${paramIndex++}`);
      values.push(poNumber);
    }
    if (warehouseId !== undefined) {
      updateFields.push(`warehouse_id = $${paramIndex++}`);
      values.push(warehouseId);
    }
    if (destination !== undefined) {
      updateFields.push(`destination = $${paramIndex++}`);
      values.push(destination);
    }
    if (remarks !== undefined) {
      updateFields.push(`remarks = $${paramIndex++}`);
      values.push(remarks);
    }
    if (status !== undefined) {
      updateFields.push(`status = $${paramIndex++}`);
      values.push(status);
    }

    if (updateFields.length === 0) {
      return res.status(400).json({
        success: false,
        message: "No fields to update"
      });
    }

    updateFields.push(`updated_at = CURRENT_TIMESTAMP`);
    updateFields.push(`updated_by = $${paramIndex++}`);
    values.push(userId);
    values.push(id, orgId);

    const query = `
      UPDATE outward_dispatches 
      SET ${updateFields.join(', ')}
      WHERE id = $${paramIndex} AND org_id = $${paramIndex + 1} AND is_deleted = false
      RETURNING *
    `;

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

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

    return res.status(200).json({
      success: true,
      message: "Outward record updated successfully",
      data: result.rows[0]
    });

  } catch (error) {
    console.error("Update Outward Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update outward record",
      error: error.message
    });
  }
};

// Soft delete outward record
const deleteOutward = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;

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

    const query = `
      UPDATE outward_dispatches 
      SET 
        is_deleted = true,
        deleted_at = CURRENT_TIMESTAMP,
        deleted_by = $1
      WHERE id = $2 AND org_id = $3 AND is_deleted = false
      RETURNING id
    `;

    const result = await pool.query(query, [userId, id, orgId]);

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

    return res.status(200).json({
      success: true,
      message: "Outward record deleted successfully"
    });

  } catch (error) {
    console.error("Delete Outward Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete outward record",
      error: error.message
    });
  }
};

// Get outward statistics/dashboard
const getOutwardStats = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

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

    const query = `
      SELECT 
        COUNT(*) as total_outward,
        SUM(CASE WHEN status = 'Ready' THEN 1 ELSE 0 END) as ready_count,
        SUM(CASE WHEN status = 'Dispatched' THEN 1 ELSE 0 END) as dispatched_count,
        COUNT(DISTINCT vehicle_no) as unique_vehicles,
        COUNT(DISTINCT driver_name) as unique_drivers,
        COALESCE(SUM(quantity), 0) as total_quantity,
        COUNT(CASE WHEN DATE(created_at) = CURRENT_DATE THEN 1 END) as today_outward
      FROM outward_dispatches
      WHERE org_id = $1 AND is_deleted = false
    `;

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

    // Get recent outward records
    const recentQuery = `
      SELECT 
        id,
        outward_no,
        vehicle_no,
        driver_name,
        item_name,
        status,
        time_out,
        created_at
      FROM outward_dispatches
      WHERE org_id = $1 AND is_deleted = false
      ORDER BY created_at DESC
      LIMIT 10
    `;
    const recentResult = await pool.query(recentQuery, [orgId]);

    return res.status(200).json({
      success: true,
      data: {
        statistics: result.rows[0],
        recentOutward: recentResult.rows
      }
    });

  } catch (error) {
    console.error("Get Outward Stats Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch outward statistics",
      error: error.message
    });
  }
};

// Generate outward number (helper endpoint)
const generateOutwardNumber = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

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

    const timestamp = new Date();
    const year = timestamp.getFullYear();
    const month = String(timestamp.getMonth() + 1).padStart(2, '0');
    
    const sequenceQuery = await pool.query(
      `SELECT COUNT(*) as count FROM outward_dispatches 
       WHERE EXTRACT(YEAR FROM created_at) = $1 AND org_id = $2`,
      [year, orgId]
    );
    
    const sequence = parseInt(sequenceQuery.rows[0].count) + 1;
    const outwardNo = `OUT-${year}${month}${String(sequence).padStart(4, '0')}`;

    return res.status(200).json({
      success: true,
      outwardNumber: outwardNo
    });

  } catch (error) {
    console.error("Generate Outward Number Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to generate outward number",
      error: error.message
    });
  }
};

module.exports = {
  createOutward,
  getAllOutward,
  getOutwardById,
  markDispatch,
  updateOutward,
  deleteOutward,
  getOutwardStats,
  generateOutwardNumber
};