const { pool } = require("../../config/db");
const fs = require('fs');
const path = require('path');
const axios = require("axios"); 
const FormData = require("form-data");
const cheerio = require("cheerio");

const lookupGstin = async (req, res) => {
  try {
    const { gstin } = req.params;

    if (!gstin || gstin.length !== 15) {
      return res.status(400).json({
        success: false,
        message: "Invalid GSTIN. Must be 15 characters.",
      });
    }

    // Try RapidAPI first (more reliable, returns JSON)
    try {
      const rapidApiKey = process.env.RAPIDAPI_KEY;
      
      if (rapidApiKey) {
        const response = await axios.get(
          `https://gst-return-status.p.rapidapi.com/free/gstin/${gstin}`,
          {
            headers: {
              'x-rapidapi-key': rapidApiKey,
              'x-rapidapi-host': 'gst-return-status.p.rapidapi.com',
            },
            timeout: 10000,
          }
        );

        if (response.data?.success && response.data?.data) {
          const apiData = response.data.data;
          
          // Parse address
          const fullAddress = apiData.adr || "";
          const pincode = apiData.pincode || "";
          const addressParts = fullAddress.split(',').map(part => part.trim());
          
          const result = {
            gstin: apiData.gstin || gstin,
            vendor_name: apiData.tradeName || "",
            trade_name: apiData.lgnm || "",
            pan: apiData.pan || gstin.substring(2, 12),
            gst_status: apiData.sts || "",
            constitution: apiData.ctb || "",
            taxpayer_type: apiData.dty || "",
            registration_date: apiData.rgdt || "",
            state: addressParts[addressParts.length - 2] || "",
            center_jurisdiction: "",
            address: fullAddress,
            city: addressParts[addressParts.length - 3] || "",
            pincode: pincode,
          };

          return res.status(200).json({
            success: true,
            data: result,
            source: "rapidapi",
          });
        }
      }
    } catch (rapidApiError) {
      console.log("RapidAPI failed, falling back to primary source...");
    }

    // Fallback to original GST search
    console.log("Using fallback GST search...");
    const form = new FormData();
    form.append("gstn", gstin);
    form.append("captcha", "434324");
    form.append("verigst", "999999");

    const response = await axios.post(
      "https://www.gstsearch.in/process.php",
      form,
      {
        headers: {
          ...form.getHeaders(),
          "User-Agent": "Mozilla/5.0",
        },
        responseType: "text",
        timeout: 15000,
      }
    );

    const html = response.data;
    const $ = cheerio.load(html);
    const extracted = {};

    $("table tr").each((i, el) => {
      const tds = $(el).find("td");
      if (tds.length >= 2) {
        const key = $(tds[0]).text().trim().replace(/\s+/g, " ");
        const value = $(tds[1]).text().trim().replace(/\s+/g, " ");
        extracted[key] = value;
      }
    });

    const addressHtml = $("td:contains('Address')").next().html();
    const cleanAddress = addressHtml
      ? addressHtml.replace(/<[^>]*>/g, " ").replace(/\s+/g, " ").trim()
      : "";

    const result = {
      gstin: extracted["GSTIN/UIN Number"] || gstin,
      vendor_name: extracted["Trade Name"] || "",
      trade_name: extracted["Trade Name"] || "",
      pan: gstin.substring(2, 12),
      gst_status: extracted["GSTIN / UIN Status"] || "",
      constitution: extracted["Constitution of Business"] || "",
      taxpayer_type: extracted["Taxpayer Type"] || "",
      registration_date: extracted["Registration Date"] || "",
      state: extracted["State Jurisdiction"] || "",
      center_jurisdiction: extracted["Centre Jurisdiction"] || "",
      address: cleanAddress,
    };

    return res.status(200).json({
      success: true,
      data: result,
      source: "fallback",
    });

  } catch (error) {
    console.error("GST Lookup Error:", error);
    return res.status(500).json({
      success: false,
      message: "GST lookup failed",
      error: error.message,
    });
  }
};

const getRelativePath = (filePath) => {
  if (!filePath) return null;
  const normalized = filePath.replace(/\\/g, '/');
  const uploadsIndex = normalized.indexOf('uploads/');
  return uploadsIndex !== -1 ? normalized.substring(uploadsIndex) : filePath;
};

const generateVendorCode = async () => {
  try {
    const result = await pool.query(
      `UPDATE public.prefixes 
       SET last_number = last_number + 1 
       WHERE module_name = 'vendor' 
         AND status = 'ACTIVE' 
         AND is_deleted = false
       RETURNING prefix_code, last_number`
    );
    
    if (result.rows.length === 0) {
      throw new Error('No prefix configuration found');
    }
    
    const prefix = result.rows[0].prefix_code;
    const nextNumber = result.rows[0].last_number;
    const formattedNumber = nextNumber.toString().padStart(4, '0');
    return `${prefix}-${formattedNumber}`;
    
  } catch (error) {
    console.error("Error generating vendor code:", error);
    return `VND-${Date.now()}`;
  }
};

const deleteFileIfExists = (filePath) => {
  if (filePath && fs.existsSync(filePath)) {
    try {
      fs.unlinkSync(filePath);
      console.log(`Deleted file: ${filePath}`);
    } catch (error) {
      console.error(`Error deleting file ${filePath}:`, error);
    }
  }
};

const parseVendorType = (vendorType) => {
  if (!vendorType) return '[]';
  
  try {
    const parsed = typeof vendorType === 'string' 
      ? JSON.parse(vendorType) 
      : vendorType;
    
    const vendorArray = Array.isArray(parsed) ? parsed : [parsed];
    return JSON.stringify(vendorArray);
  } catch (e) {
    return JSON.stringify([vendorType]);
  }
};

const parseJSONBFields = (row) => {
  if (row.vendor_type) {
    try {
      row.vendor_type = typeof row.vendor_type === 'string'
        ? JSON.parse(row.vendor_type)
        : row.vendor_type;
    } catch (e) {
      row.vendor_type = [];
    }
  } else {
    row.vendor_type = [];
  }

  if (row.shipping_addresses) {
    try {
      row.shipping_addresses = typeof row.shipping_addresses === 'string'
        ? JSON.parse(row.shipping_addresses)
        : row.shipping_addresses;
    } catch (e) {
      row.shipping_addresses = [];
    }
  } else {
    row.shipping_addresses = [];
  }
  
  if (row.items) {
    try {
      row.items = typeof row.items === 'string'
        ? JSON.parse(row.items)
        : row.items;
      // Ensure items is always an array
      if (!Array.isArray(row.items)) row.items = [];
    } catch (e) {
      row.items = [];
    }
  } else {
    row.items = [];
  }
  
  return row;
};

const checkExistingGST = async (gstin, orgId, excludeId = null) => {
  if (!gstin) return null;
  
  let query = `
    SELECT id, vendor_name, vendor_code, gstin, status
    FROM procurement_vendors
    WHERE gstin = $1 
      AND org_id = $2 
      AND is_deleted = false
  `;
  
  const params = [gstin, orgId];
  
  if (excludeId) {
    query += ` AND id != $3`;
    params.push(excludeId);
  }
  
  const result = await pool.query(query, params);
  return result.rows[0] || null;
};

const createVendor = async (req, res) => {
  try {
    console.log("=== CREATE VENDOR DEBUG ===");
    console.log("req.body:", req.body);
    console.log("req.files:", req.files);
    
    const orgId = req.user?.orgId;
    const created_by = req.user?.userId;

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

    if (!req.body || Object.keys(req.body).length === 0) {
      return res.status(400).json({ 
        success: false, 
        message: "No data received. Please check your form submission." 
      });
    }

    const panDocument = req.files?.panDocument?.[0];
    const licenseDocument = req.files?.licenseDocument?.[0];
    const bankDocument = req.files?.bankDocument?.[0];

    const {
      vendor_name,
      vendor_type,
      contact_person,
      phone,
      email,
      gstin,
      pan,
      address,
      city,
      state,
      country,
      pincode,
      payment_terms,
      bank_name,
      account_holder_name,
      account_number,
      ifsc_code,
      branch,
      status,
      documents,
      shipping_addresses,
      license_number,
      items,
    } = req.body;

    if (!vendor_name || !phone) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: vendor_name and phone are required",
      });
    }

    if (gstin && gstin.trim() !== '') {
      const existingVendor = await checkExistingGST(gstin.trim(), orgId);
      
      if (existingVendor) {
        return res.status(409).json({
          success: false,
          message: `Vendor with GSTIN ${gstin} already exists.`,
          data: {
            existing_vendor: {
              id: existingVendor.id,
              vendor_name: existingVendor.vendor_name,
              vendor_code: existingVendor.vendor_code,
              gstin: existingVendor.gstin,
              status: existingVendor.status
            }
          }
        });
      }
    }

    const vendor_code = await generateVendorCode();

    const panDocumentUrl = panDocument ? getRelativePath(panDocument.path) : null;
    const licenseDocumentUrl = licenseDocument ? getRelativePath(licenseDocument.path) : null;
    const bankDocumentUrl = bankDocument ? getRelativePath(bankDocument.path) : null;

    let shippingAddressesJSON = '[]';
    if (shipping_addresses) {
      try {
        const parsed = typeof shipping_addresses === 'string' 
          ? JSON.parse(shipping_addresses) 
          : shipping_addresses;
        shippingAddressesJSON = JSON.stringify(parsed);
      } catch (e) {
        console.error("Error parsing shipping_addresses:", e);
        shippingAddressesJSON = '[]';
      }
    }

    const vendorTypeJSON = parseVendorType(vendor_type);
    
    let itemsJSON = '[]';
    if (items) {
      try {
        const parsed = typeof items === 'string' ? JSON.parse(items) : items;
        itemsJSON = JSON.stringify(parsed);
      } catch (e) {
        console.error("Error parsing items:", e);
        itemsJSON = '[]';
      }
    }

    const result = await pool.query(
      `INSERT INTO procurement_vendors (
        vendor_code, vendor_name, vendor_type, contact_person,
        phone, email, gstin, pan,
        address, city, state, country, pincode,
        payment_terms, bank_name, account_holder_name,
        account_number, ifsc_code, branch,
        status, documents, shipping_addresses, license_number,
        pan_document_url, license_document_url, bank_document_url,
        items, org_id, created_by
      ) VALUES (
        $1, $2, $3::jsonb, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
        $14, $15, $16, $17, $18, $19, $20, $21, $22, $23,
        $24, $25, $26, $27::jsonb, $28, $29
      ) RETURNING *`,
      [
        vendor_code,
        vendor_name,
        vendorTypeJSON,
        contact_person || null,
        phone,
        email || null,
        gstin || null,
        pan || null,
        address || null,
        city || null,
        state || null,
        country || "India",
        pincode || null,
        payment_terms || null,
        bank_name || null,
        account_holder_name || null,
        account_number || null,
        ifsc_code || null,
        branch || null,
        status || "Pending",
        documents === 'true' || documents === true || false,
        shippingAddressesJSON,
        license_number || null,
        panDocumentUrl,
        licenseDocumentUrl,
        bankDocumentUrl,
        itemsJSON,
        orgId,
        created_by || null,
      ]
    );

    const vendor = parseJSONBFields(result.rows[0]);

    res.status(201).json({ success: true, data: vendor });

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

const getAllVendorsWithoutPagination = 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, vendor_code, vendor_name, vendor_type, contact_person,
              phone, email, gstin, pan, status, items,
              address, city, state, country, pincode,
              payment_terms, bank_name, account_holder_name,
              account_number, ifsc_code, branch,
              shipping_addresses, license_number,
              pan_document_url, license_document_url, bank_document_url
       FROM procurement_vendors
       WHERE is_deleted = false AND org_id = $1
       ORDER BY vendor_name ASC`,
      [orgId]
    );

    const vendors = result.rows.map(row => parseJSONBFields(row));

    res.json({ success: true, data: vendors });

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

const getAllVendors = async (req, res) => {
  try {
    const orgId = req.user?.orgId;

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

    const { search = "", page = 1, limit = 10 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const searchTerm = `%${search}%`;

    const countResult = await pool.query(
      `SELECT COUNT(*) AS total
       FROM procurement_vendors
       WHERE is_deleted = false AND org_id = $1
         AND (
           vendor_name ILIKE $2 OR
           vendor_code ILIKE $2 OR
           phone       ILIKE $2 OR
           email       ILIKE $2 OR
           gstin       ILIKE $2
         )`,
      [orgId, searchTerm]
    );

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

    const result = await pool.query(
      `SELECT id, vendor_code, vendor_name, vendor_type, contact_person,
              phone, email, gstin, pan, address, city, state, country, pincode,
              payment_terms, bank_name, account_holder_name, account_number,
              ifsc_code, branch, status, documents,
              shipping_addresses, license_number, items,
              pan_document_url, license_document_url, bank_document_url,
              created_at, updated_at, created_by, updated_by
       FROM procurement_vendors
       WHERE is_deleted = false AND org_id = $1
         AND (
           vendor_name ILIKE $2 OR
           vendor_code ILIKE $2 OR
           phone       ILIKE $2 OR
           email       ILIKE $2 OR
           gstin       ILIKE $2
         )
       ORDER BY created_at DESC
       LIMIT $3 OFFSET $4`,
      [orgId, searchTerm, parseInt(limit), offset]
    );

    const vendors = result.rows.map(row => parseJSONBFields(row));

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

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

const getVendorById = 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 id, vendor_code, vendor_name, vendor_type, contact_person,
              phone, email, gstin, pan, address, city, state, country, pincode,
              payment_terms, bank_name, account_holder_name, account_number,
              ifsc_code, branch, status, documents, items,
              shipping_addresses, license_number, 
              pan_document_url, license_document_url, bank_document_url,
              created_at, updated_at, created_by, updated_by
       FROM procurement_vendors
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const vendor = parseJSONBFields(result.rows[0]);

    res.json({ success: true, data: vendor });

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

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

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

    const panDocument = req.files?.panDocument?.[0];
    const licenseDocument = req.files?.licenseDocument?.[0];
    const bankDocument = req.files?.bankDocument?.[0];
    
    const removePanDocument = req.body.remove_pan_document === 'true' || req.body.remove_pan_document === true;
    const removeLicenseDocument = req.body.remove_license_document === 'true' || req.body.remove_license_document === true;
    const removeBankDocument = req.body.remove_bank_document === 'true' || req.body.remove_bank_document === true;

    const {
      vendor_name,
      vendor_type,
      contact_person,
      phone,
      email,
      gstin,
      pan,
      address,
      city,
      state,
      country,
      pincode,
      payment_terms,
      bank_name,
      account_holder_name,
      account_number,
      ifsc_code,
      branch,
      status,
      documents,
      shipping_addresses,
      license_number,
      items,
    } = req.body;

    if (!vendor_name || !phone) {
      return res.status(400).json({
        success: false,
        message: "Missing required fields: vendor_name and phone are required",
      });
    }

    // Get current vendor data
    const currentVendor = await pool.query(
      `SELECT pan_document_url, license_document_url, bank_document_url, pan, license_number
       FROM procurement_vendors
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const currentPanUrl = currentVendor.rows[0].pan_document_url;
    const currentLicenseUrl = currentVendor.rows[0].license_document_url;
    const currentBankUrl = currentVendor.rows[0].bank_document_url;

    // Handle PAN document
    let panDocumentUrl = currentPanUrl;
    if (panDocument) {
      if (currentPanUrl) deleteFileIfExists(currentPanUrl);
      panDocumentUrl = getRelativePath(panDocument.path);
    } else if (removePanDocument) {
      if (currentPanUrl) deleteFileIfExists(currentPanUrl);
      panDocumentUrl = null;
    }

    // Handle License document
    let licenseDocumentUrl = currentLicenseUrl;
    if (licenseDocument) {
      if (currentLicenseUrl) deleteFileIfExists(currentLicenseUrl);
      licenseDocumentUrl = getRelativePath(licenseDocument.path);
    } else if (removeLicenseDocument) {
      if (currentLicenseUrl) deleteFileIfExists(currentLicenseUrl);
      licenseDocumentUrl = null;
    }

    // Handle Bank document
    let bankDocumentUrl = currentBankUrl;
    if (bankDocument) {
      if (currentBankUrl) deleteFileIfExists(currentBankUrl);
      bankDocumentUrl = getRelativePath(bankDocument.path);
    } else if (removeBankDocument) {
      if (currentBankUrl) deleteFileIfExists(currentBankUrl);
      bankDocumentUrl = null;
    }

    // Parse shipping_addresses
    let shippingAddressesJSON = '[]';
    if (shipping_addresses) {
      try {
        const parsed = typeof shipping_addresses === 'string' 
          ? JSON.parse(shipping_addresses) 
          : shipping_addresses;
        shippingAddressesJSON = JSON.stringify(parsed);
      } catch (e) {
        console.error("Error parsing shipping_addresses:", e);
        shippingAddressesJSON = '[]';
      }
    }

    // Parse vendor_type to JSONB array
    const vendorTypeJSON = parseVendorType(vendor_type);

    // Parse items to JSONB
    let itemsJSON = '[]';
    if (items) {
      try {
        const parsed = typeof items === 'string' ? JSON.parse(items) : items;
        itemsJSON = JSON.stringify(parsed);
      } catch (e) {
        console.error("Error parsing items:", e);
        itemsJSON = '[]';
      }
    }

    const result = await pool.query(
      `UPDATE procurement_vendors SET 
        vendor_name = $1,
        vendor_type = $2::jsonb,
        contact_person = $3,
        phone = $4,
        email = $5,
        gstin = $6,
        pan = $7,
        address = $8,
        city = $9,
        state = $10,
        country = $11,
        pincode = $12,
        payment_terms = $13,
        bank_name = $14,
        account_holder_name = $15,
        account_number = $16,
        ifsc_code = $17,
        branch = $18,
        status = $19,
        documents = $20,
        shipping_addresses = $21::jsonb,
        license_number = $22,
        pan_document_url = $23,
        license_document_url = $24,
        bank_document_url = $25,
        items = $26::jsonb,
        updated_by = $27,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $28 AND org_id = $29
      RETURNING *`,
      [
        vendor_name,
        vendorTypeJSON,
        contact_person || null,
        phone,
        email || null,
        gstin || null,
        pan || null,
        address || null,
        city || null,
        state || null,
        country || "India",
        pincode || null,
        payment_terms || null,
        bank_name || null,
        account_holder_name || null,
        account_number || null,
        ifsc_code || null,
        branch || null,
        status || "Pending",
        documents === 'true' || documents === true || false,
        shippingAddressesJSON,
        license_number || null,
        panDocumentUrl,
        licenseDocumentUrl,
        bankDocumentUrl,
        itemsJSON,
        updated_by || null,
        parseInt(id),
        orgId
      ]
    );

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

    const vendor = parseJSONBFields(result.rows[0]);

    res.json({ success: true, data: vendor });

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

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

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

    const result = await pool.query(
      `UPDATE procurement_vendors
       SET is_deleted = true,
           updated_by = $1,
           updated_at = CURRENT_TIMESTAMP
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, vendor_code, vendor_name`,
      [updated_by || null, parseInt(id), orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Vendor not found or already deleted" });
    }

    res.json({ success: true, message: "Vendor deleted successfully", data: result.rows[0] });

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

const hardDeleteVendor = 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 vendor = await pool.query(
      `SELECT pan_document_url, license_document_url, bank_document_url 
       FROM procurement_vendors
       WHERE id = $1 AND org_id = $2`,
      [parseInt(id), orgId]
    );

    if (vendor.rows.length > 0) {
      if (vendor.rows[0].pan_document_url) deleteFileIfExists(vendor.rows[0].pan_document_url);
      if (vendor.rows[0].license_document_url) deleteFileIfExists(vendor.rows[0].license_document_url);
      if (vendor.rows[0].bank_document_url) deleteFileIfExists(vendor.rows[0].bank_document_url);
    }

    const result = await pool.query(
      `DELETE FROM procurement_vendors
       WHERE id = $1 AND org_id = $2
       RETURNING id, vendor_code, vendor_name`,
      [parseInt(id), orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Vendor not found" });
    }

    res.json({ success: true, message: "Vendor permanently deleted", data: result.rows[0] });

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

const updateVendorStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    const orgId = req.user?.orgId;
    const updated_by = req.user?.userId;

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

    const validStatuses = ["Approved", "Pending", "Blocked"];
    if (!status || !validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: `Invalid status. Must be one of: ${validStatuses.join(", ")}`,
      });
    }

    const result = await pool.query(
      `UPDATE procurement_vendors
       SET status     = $1,
           updated_by = $2,
           updated_at = CURRENT_TIMESTAMP
       WHERE id = $3 AND org_id = $4 AND is_deleted = false
       RETURNING id, vendor_code, vendor_name, status`,
      [status, updated_by || null, parseInt(id), orgId]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ success: false, message: "Vendor not found" });
    }

    res.json({ success: true, message: "Vendor status updated", data: result.rows[0] });

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

const debugVendor = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    
    const result = await pool.query(
      `SELECT id, vendor_name, vendor_type, shipping_addresses, license_number, 
              pan_document_url, license_document_url, bank_document_url, items
       FROM procurement_vendors 
       WHERE id = $1 AND org_id = $2`,
      [id, orgId]
    );
    
    const vendor = result.rows[0];
    if (vendor) {
      vendor.vendor_type_parsed = parseVendorType(vendor.vendor_type);
      vendor.shipping_addresses_parsed = vendor.shipping_addresses;
      vendor.items_parsed = vendor.items;
    }
    
    res.json({
      raw_vendor_type: vendor?.vendor_type,
      vendor_type_type: typeof vendor?.vendor_type,
      parsed_vendor_type: vendor?.vendor_type_parsed,
      raw_items: vendor?.items,
      parsed_items: vendor?.items_parsed,
      vendor: vendor
    });
  } catch (err) {
    res.json({ error: err.message });
  }
};



const getVendorsByItemCode = async (req, res) => {
  try {
    const { item_code } = req.query;
    const orgId = req.user?.orgId;

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

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

    const result = await pool.query(
      `SELECT id, vendor_code, vendor_name, contact_person, phone, email,
              gstin, address, city, state, country, pincode,
              shipping_addresses, items, is_active
       FROM procurement_vendors
       WHERE is_deleted = false
         AND is_active = true
         AND org_id = $1
         AND items @> $2::jsonb
       ORDER BY vendor_name ASC`,
      [orgId, JSON.stringify([{ item_code }])]
    );

    const vendors = result.rows.map(row => parseJSONBFields(row));

    res.json({ success: true, data: vendors });
  } catch (err) {
    console.error("Error in getVendorsByItemCode:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};



module.exports = {
  createVendor,
  getAllVendors,
  getAllVendorsWithoutPagination,
  getVendorById,
  updateVendor,
  deleteVendor,
  hardDeleteVendor,
  updateVendorStatus,
  debugVendor,
  lookupGstin,
  getVendorsByItemCode,
};