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

// ─────────────────────────────────────────────
// MULTER STORAGE CONFIG
// ─────────────────────────────────────────────
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    const uploadDir = path.join(__dirname, "../../uploads/organizations");
    if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    const unique = `${Date.now()}-${Math.round(Math.random() * 1e9)}`;
    cb(null, `${file.fieldname}-${unique}${path.extname(file.originalname)}`);
  },
});

const fileFilter = (req, file, cb) => {
  const allowed = /jpeg|jpg|png|gif|pdf/;
  const ext = allowed.test(path.extname(file.originalname).toLowerCase());
  const mime = allowed.test(file.mimetype);
  if (ext && mime) cb(null, true);
  else cb(new Error("Only images and PDFs are allowed"));
};

const upload = multer({
  storage,
  fileFilter,
  limits: { fileSize: 5 * 1024 * 1024 }, // 5MB
}).fields([
  { name: "logo", maxCount: 1 },
  { name: "signatureFile", maxCount: 1 },
  { name: "stampFile", maxCount: 1 },
]);

// Document upload (separate for documents tab)
const documentStorage = multer.diskStorage({
  destination: (req, file, cb) => {
    const uploadDir = path.join(__dirname, "../../uploads/organization-documents");
    if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    const unique = `${Date.now()}-${Math.round(Math.random() * 1e9)}`;
    cb(null, `${file.fieldname}-${unique}${path.extname(file.originalname)}`);
  },
});

const documentUpload = multer({
  storage: documentStorage,
  fileFilter,
  limits: { fileSize: 10 * 1024 * 1024 }, // 10MB
}).single("file");

// ─────────────────────────────────────────────
// HELPER — build file path or null
// ─────────────────────────────────────────────
const filePath = (files, field) =>
  files?.[field]?.[0]?.filename
    ? `/uploads/organizations/${files[field][0].filename}`
    : null;

const getSession = (req) => ({
  orgId: req.session?.user?.orgId,
  userId: req.session?.user?.userId,
});

// In createOrganization function, change pin_code to pincode
const createOrganization = async (req, res) => {
  try {
    const userId = req.user?.id;
    const b = req.body;
    const files = req.files;

    const statusBool = b.status !== undefined
      ? b.status === true || b.status === "true" || b.status === "Active"
      : true;

    const query = `
      INSERT INTO public.organizations (
        org_name, org_code, org_type, industry, founded_year, website, description,
        email, phone, alternate_phone, fax,
        address_line1, address_line2, city, state, country, pincode,  // ✅ Changed to pincode
        gst_number, pan_number, cin_number, tan_number, license_number, registration_number,
        bank_name, account_holder_name, account_number, ifsc_code, branch, bank_city,
        status, currency, timezone, date_format, financial_year_start,
        logo, signature_file, stamp_file,
        created_by, updated_by
      ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,
        $8,$9,$10,$11,
        $12,$13,$14,$15,$16,$17,
        $18,$19,$20,$21,$22,$23,
        $24,$25,$26,$27,$28,$29,
        $30,$31,$32,$33,$34,
        $35,$36,$37,
        $38,$38
      )
      RETURNING *`;

    const values = [
      b.org_name,
      b.org_code?.toUpperCase() || null,
      b.org_type || null,
      b.industry || null,
      b.founded_year ? parseInt(b.founded_year) : null,
      b.website || null,
      b.description || null,
      b.email || null,
      b.phone || null,
      b.alternate_phone || null,
      b.fax || null,
      b.address_line1 || null,
      b.address_line2 || null,
      b.city || null,
      b.state || null,
      b.country || null,
      b.pincode || b.pin_code || null,  // ✅ Accept both
      b.gst_number?.toUpperCase() || null,
      b.pan_number?.toUpperCase() || null,
      b.cin_number?.toUpperCase() || null,
      b.tan_number?.toUpperCase() || null,
      b.license_number || null,
      b.registration_number || null,
      b.bank_name || null,
      b.account_holder_name || null,
      b.account_number || null,
      b.ifsc_code?.toUpperCase() || null,
      b.branch || null,
      b.bank_city || null,
      statusBool,
      b.currency || "INR",
      b.timezone || "Asia/Kolkata",
      b.date_format || "DD/MM/YYYY",
      b.financial_year_start || "April",
      filePath(files, "logo"),
      filePath(files, "signatureFile"),
      filePath(files, "stampFile"),
      userId,
    ];

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

    return res.status(201).json({
      success: true,
      message: "Organization created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE ORGANIZATION ERROR:", error);
    // ... rest of error handling
  }
};

// Helper to create default deduction rules
const createDefaultDeductionRules = async (orgId, userId) => {
  const rules = [
    { rule_name: 'Provident Fund (Employee)', rule_type: 'pf', calculation_type: 'percentage', percentage: 12, deduction_category: 'statutory' },
    { rule_name: 'Provident Fund (Employer)', rule_type: 'pf_er', calculation_type: 'percentage', percentage: 12, deduction_category: 'statutory' },
    { rule_name: 'ESI (Employee)', rule_type: 'esi', calculation_type: 'percentage', percentage: 0.75, ceiling_limit: 21000, deduction_category: 'statutory' },
    { rule_name: 'ESI (Employer)', rule_type: 'esi_er', calculation_type: 'percentage', percentage: 3.25, ceiling_limit: 21000, deduction_category: 'statutory' },
    { rule_name: 'Professional Tax', rule_type: 'pt', calculation_type: 'fixed', fixed_amount: 200, deduction_category: 'statutory' },
    { rule_name: 'TDS', rule_type: 'tds', calculation_type: 'percentage', deduction_category: 'tax' },
    { rule_name: 'Late Deduction', rule_type: 'late', calculation_type: 'percentage', percentage: 0.25, threshold_minutes: 15, max_per_day: 1, deduction_category: 'attendance' },
    { rule_name: 'Absent Deduction', rule_type: 'absent', calculation_type: 'percentage', percentage: 100, deduction_category: 'attendance' },
    { rule_name: 'Half Day Deduction', rule_type: 'halfday', calculation_type: 'percentage', percentage: 50, deduction_category: 'attendance' },
  ];

  for (const rule of rules) {
    await pool.query(
      `INSERT INTO public.deduction_rules (org_id, rule_name, rule_type, calculation_type, percentage, fixed_amount, ceiling_limit, threshold_minutes, max_per_day, deduction_category, is_active, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, true, $11, $11)
       ON CONFLICT DO NOTHING`,
      [orgId, rule.rule_name, rule.rule_type, rule.calculation_type, rule.percentage || null, rule.fixed_amount || null, rule.ceiling_limit || null, rule.threshold_minutes || null, rule.max_per_day || null, rule.deduction_category, userId]
    );
  }
};

// ─────────────────────────────────────────────
// GET ALL ORGANIZATIONS
// GET /api/organization
// ─────────────────────────────────────────────
const getAllOrganizations = async (req, res) => {
  try {
    const {
      page = 1,
      limit = 20,
      search = "",
      status,
      industry,
      org_type,
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const params = [];
    const conditions = ["is_deleted = FALSE"];

    if (search) {
      params.push(`%${search}%`);
      conditions.push(
        `(org_name ILIKE $${params.length} OR email ILIKE $${params.length} OR org_code ILIKE $${params.length})`
      );
    }

    if (status !== undefined && status !== "") {
      const statusBool = status === "true" || status === "Active";
      params.push(statusBool);
      conditions.push(`status = $${params.length}`);
    }

    if (industry) {
      params.push(industry);
      conditions.push(`industry = $${params.length}`);
    }

    if (org_type) {
      params.push(org_type);
      conditions.push(`org_type = $${params.length}`);
    }

    const where = `WHERE ${conditions.join(" AND ")}`;

    const countResult = await pool.query(
      `SELECT COUNT(*) FROM public.organizations ${where}`,
      params
    );
    const total = parseInt(countResult.rows[0].count);

    params.push(parseInt(limit), offset);
    const dataResult = await pool.query(
      `SELECT
        id, org_name, org_code, org_type, industry, founded_year,
        website, email, phone, city, state, country,
        gst_number, pan_number, status, currency, logo,
        created_at, updated_at
       FROM public.organizations
       ${where}
       ORDER BY created_at DESC
       LIMIT $${params.length - 1} OFFSET $${params.length}`,
      params
    );

    const rows = dataResult.rows.map((org) => ({
      ...org,
      status: org.status ? "Active" : "Inactive",
    }));

    return res.status(200).json({
      success: true,
      data: rows,
      pagination: {
        total,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(total / parseInt(limit)),
      },
    });
  } catch (error) {
    console.error("GET ALL ORGANIZATIONS ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch organizations",
      error: error.message,
    });
  }
};

// ─────────────────────────────────────────────
// GET ORGANIZATION DASHBOARD STATS
// GET /api/organization/:id/stats
// ─────────────────────────────────────────────
const getOrganizationStats = async (req, res) => {
  try {
    const { id } = req.params;

    const stats = await pool.query(`
      SELECT 
        (SELECT COUNT(*) FROM employees WHERE org_id = $1 AND status = 'Active' AND is_deleted = false) AS total_employees,
        (SELECT COUNT(*) FROM departments WHERE org_id = $1 AND is_deleted = false) AS total_departments,
        (SELECT COUNT(*) FROM health_insurance WHERE org_id = $1 AND is_deleted = false AND status = 'Active') AS active_policies,
        (SELECT COUNT(*) FROM organization_provident_fund WHERE org_id = $1 AND is_deleted = false AND status = 'Active') AS pf_active
    `, [id]);

    return res.status(200).json({
      success: true,
      data: {
        total_employees: parseInt(stats.rows[0]?.total_employees || 0),
        total_departments: parseInt(stats.rows[0]?.total_departments || 0),
        active_policies: parseInt(stats.rows[0]?.active_policies || 0),
        pf_active: parseInt(stats.rows[0]?.pf_active || 0) > 0,
      }
    });
  } catch (error) {
    console.error("GET ORGANIZATION STATS ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch organization stats",
      error: error.message,
    });
  }
};

// ─────────────────────────────────────────────
// GET SINGLE ORGANIZATION
// GET /api/organization/:id
// ─────────────────────────────────────────────
const getOrganizationById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `SELECT * FROM public.organizations WHERE id = $1 AND is_deleted = FALSE`,
      [id]
    );

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

    const org = result.rows[0];
    org.status = org.status ? "Active" : "Inactive";

    return res.status(200).json({ success: true, data: org });
  } catch (error) {
    console.error("GET ORGANIZATION ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch organization",
      error: error.message,
    });
  }
};

// ─────────────────────────────────────────────
// UPDATE ORGANIZATION
// PUT /api/organization/:id
// ─────────────────────────────────────────────
const updateOrganization = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.user?.id;
    const b = req.body;
    const files = req.files;

    const existing = await pool.query(
      `SELECT * FROM public.organizations WHERE id = $1 AND is_deleted = FALSE`,
      [id]
    );
    if (existing.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Organization not found" });
    }

    const current = existing.rows[0];

    const deleteOldFile = (oldPath) => {
      if (oldPath) {
        const fullPath = path.join(__dirname, "../", oldPath);
        if (fs.existsSync(fullPath)) fs.unlinkSync(fullPath);
      }
    };

    const newLogo = filePath(files, "logo");
    const newSignature = filePath(files, "signatureFile");
    const newStamp = filePath(files, "stampFile");

    if (newLogo) deleteOldFile(current.logo);
    if (newSignature) deleteOldFile(current.signature_file);
    if (newStamp) deleteOldFile(current.stamp_file);

    const statusBool = b.status !== undefined
      ? b.status === true || b.status === "true" || b.status === "Active"
      : current.status;

    // ✅ FIX: Use "pincode" instead of "pin_code" (no underscore)
    const query = `
      UPDATE public.organizations SET
         org_name             = COALESCE($1, org_name),
    org_code             = COALESCE($2, org_code),
    org_type             = COALESCE($3, org_type),
    industry             = COALESCE($4, industry),
    founded_year         = COALESCE($5, founded_year),
    website              = COALESCE($6, website),
    description          = COALESCE($7, description),
    email                = COALESCE($8, email),
    phone                = COALESCE($9, phone),
    alternate_phone      = COALESCE($10, alternate_phone),
    fax                  = COALESCE($11, fax),
    address_line1        = COALESCE($12, address_line1),
    address_line2        = COALESCE($13, address_line2),
    city                 = COALESCE($14, city),
    state                = COALESCE($15, state),
    country              = COALESCE($16, country),
    pincode              = COALESCE($17, pincode),     -- Changed from pin_code to pincode
    gst_number           = COALESCE($18, gst_number),
        pan_number           = COALESCE($19, pan_number),
        cin_number           = COALESCE($20, cin_number),
        tan_number           = COALESCE($21, tan_number),
        license_number       = COALESCE($22, license_number),
        registration_number  = COALESCE($23, registration_number),
        bank_name            = COALESCE($24, bank_name),
        account_holder_name  = COALESCE($25, account_holder_name),
        account_number       = COALESCE($26, account_number),
        ifsc_code            = COALESCE($27, ifsc_code),
        branch               = COALESCE($28, branch),
        bank_city            = COALESCE($29, bank_city),
        status               = COALESCE($30, status),
        currency             = COALESCE($31, currency),
        timezone             = COALESCE($32, timezone),
        date_format          = COALESCE($33, date_format),
        financial_year_start = COALESCE($34, financial_year_start),
        logo                 = COALESCE($35, logo),
        signature_file       = COALESCE($36, signature_file),
        stamp_file           = COALESCE($37, stamp_file),
        updated_by           = $38,
        updated_at           = CURRENT_TIMESTAMP
      WHERE id = $39
      RETURNING *`;

    const values = [
      b.org_name || null,
      b.org_code?.toUpperCase() || null,
      b.org_type || null,
      b.industry || null,
      b.founded_year ? parseInt(b.founded_year) : null,
      b.website || null,
      b.description || null,
      b.email || null,
      b.phone || null,
      b.alternate_phone || null,
      b.fax || null,
      b.address_line1 || null,
      b.address_line2 || null,
      b.city || null,
      b.state || null,
      b.country || null,
      b.pincode || b.pin_code || null,  
      b.gst_number?.toUpperCase() || null,
      b.pan_number?.toUpperCase() || null,
      b.cin_number?.toUpperCase() || null,
      b.tan_number?.toUpperCase() || null,
      b.license_number || null,
      b.registration_number || null,
      b.bank_name || null,
      b.account_holder_name || null,
      b.account_number || null,
      b.ifsc_code?.toUpperCase() || null,
      b.branch || null,
      b.bank_city || null,
      statusBool,
      b.currency || "INR",
      b.timezone || "Asia/Kolkata",
      b.date_format || "DD/MM/YYYY",
      b.financial_year_start || "April",
      newLogo,
      newSignature,
      newStamp,
      userId,
      id,
    ];

    const result = await pool.query(query, values);
    const org = result.rows[0];
    org.status = org.status ? "Active" : "Inactive";

    return res.status(200).json({
      success: true,
      message: "Organization updated successfully",
      data: org,
    });
  } catch (error) {
    console.error("UPDATE ORGANIZATION ERROR:", error);

    if (error.code === "23505") {
      const field = error.constraint?.includes("gst")
        ? "GST Number"
        : error.constraint?.includes("pan")
        ? "PAN Number"
        : error.constraint?.includes("cin")
        ? "CIN Number"
        : error.constraint?.includes("tan")
        ? "TAN Number"
        : error.constraint?.includes("org_code")
        ? "Org Code"
        : "A unique field";
      return res.status(409).json({ success: false, message: `${field} already exists` });
    }

    return res.status(500).json({
      success: false,
      message: "Failed to update organization",
      error: error.message,
    });
  }
};

// ─────────────────────────────────────────────
// SOFT DELETE ORGANIZATION
// DELETE /api/organization/:id
// ─────────────────────────────────────────────
const deleteOrganization = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.user?.id;

    const result = await pool.query(
      `UPDATE public.organizations
       SET is_deleted = TRUE, updated_by = $1, updated_at = CURRENT_TIMESTAMP
       WHERE id = $2 AND is_deleted = FALSE
       RETURNING id, org_name`,
      [userId, id]
    );

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

    return res.status(200).json({
      success: true,
      message: `Organization "${result.rows[0].org_name}" deleted successfully`,
    });
  } catch (error) {
    console.error("DELETE ORGANIZATION ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete organization",
      error: error.message,
    });
  }
};

// ─────────────────────────────────────────────
// TOGGLE STATUS
// PATCH /api/organization/:id/status
// ─────────────────────────────────────────────
const toggleOrganizationStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    const userId = req.user?.id;

    if (status === undefined || status === null) {
      return res.status(400).json({
        success: false,
        message: "status is required (Active | Inactive or true | false)",
      });
    }

    const statusBool = status === true || status === "true" || status === "Active";

    const result = await pool.query(
      `UPDATE public.organizations
       SET status = $1, updated_by = $2, updated_at = CURRENT_TIMESTAMP
       WHERE id = $3 AND is_deleted = FALSE
       RETURNING id, org_name, status`,
      [statusBool, userId, id]
    );

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

    const org = result.rows[0];
    return res.status(200).json({
      success: true,
      message: `Status updated to ${org.status ? "Active" : "Inactive"}`,
      data: { ...org, status: org.status ? "Active" : "Inactive" },
    });
  } catch (error) {
    console.error("TOGGLE STATUS ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update status",
      error: error.message,
    });
  }
};

// =====================================================
// ADDRESS MANAGEMENT
// =====================================================

// Get all addresses for an organization
const getOrganizationAddresses = async (req, res) => {
  try {
    const { orgId } = req.params;
    const result = await pool.query(
      `SELECT * FROM public.organization_addresses 
       WHERE org_id = $1 AND is_deleted = false 
       ORDER BY is_primary DESC, created_at DESC`,
      [orgId]
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("GET ADDRESSES ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// Add new address
const addOrganizationAddress = async (req, res) => {
  try {
    const { orgId } = req.params;
    const userId = req.user?.id;
    const { address_type, address_line1, address_line2, city, state, pin_code, country, is_primary } = req.body;

    if (is_primary) {
      await pool.query(
        `UPDATE public.organization_addresses SET is_primary = false WHERE org_id = $1`,
        [orgId]
      );
    }

    const result = await pool.query(
      `INSERT INTO public.organization_addresses 
       (org_id, address_type, address_line1, address_line2, city, state, pin_code, country, is_primary, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
       RETURNING *`,
      [orgId, address_type, address_line1, address_line2, city, state, pin_code, country, is_primary || false, userId, userId]
    );
    res.status(201).json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("ADD ADDRESS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// Update address
const updateOrganizationAddress = async (req, res) => {
  try {
    const { orgId, addressId } = req.params;
    const userId = req.user?.id;
    const { address_type, address_line1, address_line2, city, state, pin_code, country, is_primary } = req.body;

    if (is_primary) {
      await pool.query(
        `UPDATE public.organization_addresses SET is_primary = false WHERE org_id = $1 AND id != $2`,
        [orgId, addressId]
      );
    }

    const result = await pool.query(
      `UPDATE public.organization_addresses SET
        address_type = $1, address_line1 = $2, address_line2 = $3,
        city = $4, state = $5, pin_code = $6, country = $7,
        is_primary = $8, updated_by = $9, updated_at = CURRENT_TIMESTAMP
       WHERE id = $10 AND org_id = $11 AND is_deleted = false
       RETURNING *`,
      [address_type, address_line1, address_line2, city, state, pin_code, country, is_primary, userId, addressId, orgId]
    );
    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("UPDATE ADDRESS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// Delete address
const deleteOrganizationAddress = async (req, res) => {
  try {
    const { orgId, addressId } = req.params;
    await pool.query(
      `UPDATE public.organization_addresses SET is_deleted = true WHERE id = $1 AND org_id = $2`,
      [addressId, orgId]
    );
    res.json({ success: true, message: "Address deleted successfully" });
  } catch (error) {
    console.error("DELETE ADDRESS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};


// ─────────────────────────────────────────────
// GET ORGANIZATION WITH FULL DETAILS (Addresses, Bank, etc.)
// GET /api/organization/:id/full
// ─────────────────────────────────────────────
const getOrganizationFullDetails = async (req, res) => {
  try {
    const { id } = req.params;

    // Fetch organization data
    const orgResult = await pool.query(
      `SELECT * FROM public.organizations WHERE id = $1 AND is_deleted = FALSE`,
      [id]
    );

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

    const org = orgResult.rows[0];
    
    // Fetch all addresses
    const addressesResult = await pool.query(
      `SELECT * FROM public.organization_addresses 
       WHERE org_id = $1 AND is_deleted = false 
       ORDER BY is_primary DESC, created_at DESC`,
      [id]
    );
    
    // Fetch bank details
    const bankResult = await pool.query(
      `SELECT * FROM public.organization_bank_details 
       WHERE org_id = $1 AND is_deleted = false 
       ORDER BY is_primary DESC, created_at DESC`,
      [id]
    );
    
    // Find primary address
    const primaryAddress = addressesResult.rows.find(addr => addr.is_primary === true) || addressesResult.rows[0];
    
    // Get primary address as formatted string
    let formattedAddress = "";
    if (primaryAddress) {
      const parts = [];
      if (primaryAddress.address_line1) parts.push(primaryAddress.address_line1);
      if (primaryAddress.address_line2) parts.push(primaryAddress.address_line2);
      if (primaryAddress.city) parts.push(primaryAddress.city);
      if (primaryAddress.state) parts.push(primaryAddress.state);
      if (primaryAddress.pin_code) parts.push(primaryAddress.pin_code);
      if (primaryAddress.country) parts.push(primaryAddress.country);
      formattedAddress = parts.join(", ");
    } else if (org.address_line1) {
      const parts = [];
      if (org.address_line1) parts.push(org.address_line1);
      if (org.address_line2) parts.push(org.address_line2);
      if (org.city) parts.push(org.city);
      if (org.state) parts.push(org.state);
      if (org.pin_code) parts.push(org.pin_code);
      if (org.country) parts.push(org.country);
      formattedAddress = parts.join(", ");
    }
    
    org.status = org.status ? "Active" : "Inactive";
    
    // Return enriched data
    return res.status(200).json({
      success: true,
      data: {
        ...org,
        formatted_address: formattedAddress,
        addresses: addressesResult.rows,
        bank_details: bankResult.rows,
        primary_address: primaryAddress
      }
    });
  } catch (error) {
    console.error("GET ORGANIZATION FULL DETAILS ERROR:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch organization details",
      error: error.message,
    });
  }
};
// =====================================================
// DOCUMENT MANAGEMENT
// =====================================================

// Upload document
const uploadOrganizationDocument = async (req, res) => {
  documentUpload(req, res, async (err) => {
    try {
      const { orgId } = req.params;
      const userId = req.user?.id;
      const { doc_type, doc_name } = req.body;

      if (err) {
        return res.status(400).json({ success: false, message: err.message });
      }

      if (!req.file) {
        return res.status(400).json({ success: false, message: "No file uploaded" });
      }

      const filePath = `/uploads/organization-documents/${req.file.filename}`;

      const result = await pool.query(
        `INSERT INTO organization_documents (org_id, doc_name, doc_type, file_path, created_by, updated_by)
         VALUES ($1, $2, $3, $4, $5, $6)
         RETURNING *`,
        [orgId, doc_name || doc_type, doc_type, filePath, userId, userId]
      );

      res.status(201).json({ success: true, data: result.rows[0] });
    } catch (error) {
      console.error("UPLOAD DOCUMENT ERROR:", error);
      res.status(500).json({ success: false, message: error.message });
    }
  });
};

// Get all documents
const getOrganizationDocuments = async (req, res) => {
  try {
    const { orgId } = req.params;
    const result = await pool.query(
      `SELECT * FROM organization_documents 
       WHERE org_id = $1 AND is_deleted = false 
       ORDER BY created_at DESC`,
      [orgId]
    );
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("GET DOCUMENTS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// Delete document
const deleteOrganizationDocument = async (req, res) => {
  try {
    const { orgId, docId } = req.params;
    
    // Get file path to delete from storage
    const docResult = await pool.query(
      `SELECT file_path FROM organization_documents WHERE id = $1 AND org_id = $2`,
      [docId, orgId]
    );
    
    if (docResult.rows.length > 0 && docResult.rows[0].file_path) {
      const fullPath = path.join(__dirname, "../", docResult.rows[0].file_path);
      if (fs.existsSync(fullPath)) fs.unlinkSync(fullPath);
    }
    
    await pool.query(
      `UPDATE organization_documents SET is_deleted = true WHERE id = $1 AND org_id = $2`,
      [docId, orgId]
    );
    res.json({ success: true, message: "Document deleted successfully" });
  } catch (error) {
    console.error("DELETE DOCUMENT ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

// Replace document
const replaceOrganizationDocument = async (req, res) => {
  documentUpload(req, res, async (err) => {
    try {
      const { orgId, docId } = req.params;
      const userId = req.user?.id;

      if (err) {
        return res.status(400).json({ success: false, message: err.message });
      }

      if (!req.file) {
        return res.status(400).json({ success: false, message: "No file uploaded" });
      }

      // Get old file path
      const oldDoc = await pool.query(
        `SELECT file_path FROM organization_documents WHERE id = $1 AND org_id = $2`,
        [docId, orgId]
      );

      if (oldDoc.rows.length > 0 && oldDoc.rows[0].file_path) {
        const oldPath = path.join(__dirname, "../", oldDoc.rows[0].file_path);
        if (fs.existsSync(oldPath)) fs.unlinkSync(oldPath);
      }

      const newFilePath = `/uploads/organization-documents/${req.file.filename}`;

      const result = await pool.query(
        `UPDATE organization_documents SET
          file_path = $1, updated_by = $2, updated_at = CURRENT_TIMESTAMP
         WHERE id = $3 AND org_id = $4
         RETURNING *`,
        [newFilePath, userId, docId, orgId]
      );

      res.json({ success: true, data: result.rows[0] });
    } catch (error) {
      console.error("REPLACE DOCUMENT ERROR:", error);
      res.status(500).json({ success: false, message: error.message });
    }
  });
};

// =====================================================
// BANK DETAILS MANAGEMENT
// =====================================================

/* Get all bank details for an organization */
const getOrganizationBankDetails = async (req, res) => {
  try {
    const { orgId } = req.params;
    
    // Check if organization_bank_details table exists
    const tableCheck = await pool.query(`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_name = 'organization_bank_details'
      );
    `);
    
    if (!tableCheck.rows[0].exists) {
      // Create table if it doesn't exist
      await pool.query(`
        CREATE TABLE IF NOT EXISTS public.organization_bank_details (
          id SERIAL PRIMARY KEY,
          org_id INTEGER NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
          bank_name VARCHAR(100),
          account_holder_name VARCHAR(100),
          account_number VARCHAR(30),
          ifsc_code VARCHAR(15),
          branch VARCHAR(100),
          bank_city VARCHAR(100),
          account_type VARCHAR(50) DEFAULT 'Current',
          is_primary BOOLEAN DEFAULT false,
          notes TEXT,
          is_deleted BOOLEAN DEFAULT false,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
          created_by INTEGER,
          updated_by INTEGER
        );
      `);
    }
    
    const result = await pool.query(
      `SELECT * FROM public.organization_bank_details 
       WHERE org_id = $1 AND is_deleted = false 
       ORDER BY is_primary DESC, created_at DESC`,
      [orgId]
    );
    
    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("GET BANK DETAILS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Add new bank detail */
const addOrganizationBankDetail = async (req, res) => {
  try {
    const { orgId } = req.params;
    const userId = req.user?.id;
    const { 
      bank_name, account_holder_name, account_number, ifsc_code, 
      branch, bank_city, account_type, is_primary, notes 
    } = req.body;

    if (!bank_name || !account_number) {
      return res.status(400).json({ 
        success: false, 
        message: "Bank name and account number are required" 
      });
    }

    // If this is primary, unset other primary
    if (is_primary) {
      await pool.query(
        `UPDATE public.organization_bank_details 
         SET is_primary = false WHERE org_id = $1`,
        [orgId]
      );
    }

    // ✅ FIX: Correct number of parameters (11 values for 11 placeholders)
    const result = await pool.query(
      `INSERT INTO public.organization_bank_details 
       (org_id, bank_name, account_holder_name, account_number, ifsc_code, 
        branch, bank_city, account_type, is_primary, notes, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
       RETURNING *`,
      [
        orgId,                                    // $1
        bank_name,                                // $2
        account_holder_name,                      // $3
        account_number,                           // $4
        ifsc_code,                                // $5
        branch,                                   // $6
        bank_city,                                // $7
        account_type || 'Current',                // $8
        is_primary || false,                      // $9
        notes || null,                            // $10
        userId,                                   // $11
        userId                                    // $12
      ]
    );

    res.status(201).json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("ADD BANK DETAIL ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Update bank detail */
const updateOrganizationBankDetail = async (req, res) => {
  try {
    const { orgId, bankId } = req.params;
    const userId = req.user?.id;
    const { 
      bank_name, account_holder_name, account_number, ifsc_code, 
      branch, bank_city, account_type, is_primary, notes 
    } = req.body;

    // If this is primary, unset other primary
    if (is_primary) {
      await pool.query(
        `UPDATE public.organization_bank_details 
         SET is_primary = false WHERE org_id = $1 AND id != $2`,
        [orgId, bankId]
      );
    }

    const result = await pool.query(
      `UPDATE public.organization_bank_details SET
        bank_name = COALESCE($1, bank_name),
        account_holder_name = COALESCE($2, account_holder_name),
        account_number = COALESCE($3, account_number),
        ifsc_code = COALESCE($4, ifsc_code),
        branch = COALESCE($5, branch),
        bank_city = COALESCE($6, bank_city),
        account_type = COALESCE($7, account_type),
        is_primary = COALESCE($8, is_primary),
        notes = COALESCE($9, notes),
        updated_by = $10,
        updated_at = NOW()
      WHERE id = $11 AND org_id = $12 AND is_deleted = false
      RETURNING *`,
      [
        bank_name,                    // $1
        account_holder_name,          // $2
        account_number,               // $3
        ifsc_code,                    // $4
        branch,                       // $5
        bank_city,                    // $6
        account_type,                 // $7
        is_primary,                   // $8
        notes,                        // $9
        userId,                       // $10
        bankId,                       // $11
        orgId                         // $12
      ]
    );

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

    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("UPDATE BANK DETAIL ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Delete bank detail (soft delete) */
const deleteOrganizationBankDetail = async (req, res) => {
  try {
    const { orgId, bankId } = req.params;
    const userId = req.user?.id;

    const result = await pool.query(
      `UPDATE public.organization_bank_details 
       SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id`,
      [userId, bankId, orgId]
    );

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

    res.json({ success: true, message: "Bank detail deleted successfully" });
  } catch (error) {
    console.error("DELETE BANK DETAIL ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};


// =====================================================
// API MANAGEMENT
// =====================================================

/* Get all APIs for an organization */
const getOrganizationApis = async (req, res) => {
  try {
    const { orgId } = req.params;

    const result = await pool.query(
      `SELECT * FROM public.organization_apis
       WHERE org_id = $1 AND is_deleted = false
       ORDER BY created_at DESC`,
      [orgId]
    );

    res.json({ success: true, data: result.rows });
  } catch (error) {
    console.error("GET APIS ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Add new API */
const addOrganizationApi = async (req, res) => {
  try {
    const { orgId } = req.params;
    const userId = req.user?.id;
    const { api_name, base_url, description, custom_headers, status } = req.body;

    if (!api_name || !base_url) {
      return res.status(400).json({
        success: false,
        message: "API name and base URL are required",
      });
    }

    const result = await pool.query(
      `INSERT INTO public.organization_apis
       (org_id, api_name, base_url, description, custom_headers, status, created_by, updated_by)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
       RETURNING *`,
      [
        orgId,
        api_name,
        base_url,
        description || null,
        custom_headers ? JSON.stringify(custom_headers) : null,
        status || "Active",
        userId,
        userId,
      ]
    );

    res.status(201).json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("ADD API ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Update API */
const updateOrganizationApi = async (req, res) => {
  try {
    const { orgId, apiId } = req.params;
    const userId = req.user?.id;
    const { api_name, base_url, description, custom_headers, status } = req.body;

    const result = await pool.query(
      `UPDATE public.organization_apis SET
        api_name       = COALESCE($1, api_name),
        base_url       = COALESCE($2, base_url),
        description    = COALESCE($3, description),
        custom_headers = COALESCE($4, custom_headers),
        status         = COALESCE($5, status),
        updated_by     = $6,
        updated_at     = CURRENT_TIMESTAMP
       WHERE id = $7 AND org_id = $8 AND is_deleted = false
       RETURNING *`,
      [
        api_name || null,
        base_url || null,
        description || null,
        custom_headers ? JSON.stringify(custom_headers) : null,
        status || null,
        userId,
        apiId,
        orgId,
      ]
    );

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

    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("UPDATE API ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Delete API (soft delete) */
const deleteOrganizationApi = async (req, res) => {
  try {
    const { orgId, apiId } = req.params;
    const userId = req.user?.id;

    const result = await pool.query(
      `UPDATE public.organization_apis
       SET is_deleted = true, updated_by = $1, updated_at = CURRENT_TIMESTAMP
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id, api_name`,
      [userId, apiId, orgId]
    );

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

    res.json({
      success: true,
      message: `API "${result.rows[0].api_name}" deleted successfully`,
    });
  } catch (error) {
    console.error("DELETE API ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

/* Test API connection */
const testOrganizationApi = async (req, res) => {
  try {
    const { orgId, apiId } = req.params;

    const result = await pool.query(
      `SELECT * FROM public.organization_apis
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [apiId, orgId]
    );

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

    const api = result.rows[0];
    const headers = api.custom_headers || {};
    const startTime = Date.now();

    try {
      const https = require("https");
      const http = require("http");
      const url = new URL(api.base_url);
      const client = url.protocol === "https:" ? https : http;

      await new Promise((resolve, reject) => {
        const options = {
          hostname: url.hostname,
          port: url.port || (url.protocol === "https:" ? 443 : 80),
          path: url.pathname || "/",
          method: "GET",
          headers: { "User-Agent": "OrgAPI-Test/1.0", ...headers },
          timeout: 8000,
        };

        const request = client.request(options, (response) => {
          resolve(response.statusCode);
        });

        request.on("timeout", () => { request.destroy(); reject(new Error("Request timed out")); });
        request.on("error", reject);
        request.end();
      });

      const responseTime = Date.now() - startTime;

      return res.json({
        success: true,
        data: {
          status: "ok",
          response_time_ms: responseTime,
          message: "API endpoint reachable",
        },
      });
    } catch (testError) {
      return res.status(200).json({
        success: false,
        message: `Connection failed: ${testError.message}`,
        data: { error: testError.message },
      });
    }
  } catch (error) {
    console.error("TEST API ERROR:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};


module.exports = {
  upload,
  createOrganization,
  getAllOrganizations,
  getOrganizationById,
  getOrganizationStats,
  updateOrganization,
  deleteOrganization,
  toggleOrganizationStatus,
  // Addresses
  getOrganizationAddresses,
  addOrganizationAddress,
  updateOrganizationAddress,
  deleteOrganizationAddress,
  getOrganizationFullDetails,
  // Documents
  uploadOrganizationDocument,
  getOrganizationDocuments,
  deleteOrganizationDocument,
  replaceOrganizationDocument,
  // Bank Details - ✅ Add these
  getOrganizationBankDetails,
  addOrganizationBankDetail,
  updateOrganizationBankDetail,
  deleteOrganizationBankDetail,

  // APIs
  getOrganizationApis,
  addOrganizationApi,
  updateOrganizationApi,
  deleteOrganizationApi,
  testOrganizationApi,
};