import { getProjectConnection, createProjectDatabase, createProjectTables } from '../utils/dbManager.js';
import { createSurveyModel } from '../models/survey.js';
import { successResponse, errorResponse } from '../utils/response.js';
import Treelogy from '../models/treelogy.js';
import { Op } from 'sequelize';
import jwt from 'jsonwebtoken'
import getSubDbConnection from '../config/subDb.js';

// Submit survey data from mobile app
export async function submitSurvey(req, res) {
  try {
    const {
      tree_name,
      local_name,
      other_name,
      latitude,
      longitude,
      tree_height,
      tree_girth,
      tree_age,
      tree_condition,
      tree_ownership,
      economic_importance,
      remarks,
      species_type,
      is_heritage = false,
      is_rare = false,
      is_indigenous = true,
      is_exotic = false,
      host_plants,
      images = [],
      user_id,
      client_id,
      treelogy_id,
      survey_date
    } = req.body;

    // Validate required fields
    if (!tree_name || !latitude || !longitude || !user_id || !client_id || !treelogy_id) {
      return errorResponse(res, 400, 'Missing required fields');
    }

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get or create project database connection
    let projectConnection;
    try {
      projectConnection = await getProjectConnection(project.dbname);
    } catch (error) {
      // If database doesn't exist, create it
      if (error.message.includes('does not exist')) {
        projectConnection = await createProjectDatabase(project.dbname);
      } else {
        throw error;
      }
    }

    // Create survey model for this project
    const Survey = createSurveyModel(projectConnection);

    // Create survey record
    const survey = await Survey.create({
      tree_name,
      local_name,
      other_name,
      latitude: parseFloat(latitude),
      longitude: parseFloat(longitude),
      tree_height: tree_height ? parseFloat(tree_height) : null,
      tree_girth: tree_girth ? parseFloat(tree_girth) : null,
      tree_age: tree_age ? parseInt(tree_age) : null,
      tree_condition,
      tree_ownership,
      economic_importance,
      remarks,
      species_type,
      is_heritage,
      is_rare,
      is_indigenous,
      is_exotic,
      host_plants,
      images,
      user_id,
      client_id,
      treelogy_id,
      survey_date: survey_date ? new Date(survey_date) : new Date(),
      entered_time: new Date(),
      status: 'ACTIVE',
      verified: false
    });

    return successResponse(res, 201, 'Survey submitted successfully', survey);
  } catch (error) {
    console.error('Error submitting survey:', error);
    return errorResponse(res, 500, 'Error submitting survey', error.message);
  }
}

// Get surveys for a project (for dashboard/map display)
export async function getProjectSurveys(req, res) {
  try {
    const { treelogy_id } = req.params;
    const {
      page = 1,
      limit = 100,
      verified_only = false,
      species_type,
      tree_condition,
      start_date,
      end_date
    } = req.query;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);
    const Survey = createSurveyModel(projectConnection);

    // Build where clause
    let where = { status: 'ACTIVE' };

    if (verified_only === 'true') {
      where.verified = true;
    }

    if (species_type) {
      where.species_type = species_type;
    }

    if (tree_condition) {
      where.tree_condition = tree_condition;
    }

    if (start_date || end_date) {
      where.survey_date = {};
      if (start_date) where.survey_date[Op.gte] = new Date(start_date);
      if (end_date) where.survey_date[Op.lte] = new Date(end_date);
    }

    // Get surveys with pagination
    const offset = (page - 1) * limit;
    const surveys = await Survey.findAndCountAll({
      where,
      order: [['survey_date', 'DESC']],
      limit: parseInt(limit),
      offset: parseInt(offset)
    });

    return successResponse(res, 200, 'Surveys retrieved successfully', {
      surveys: surveys.rows,
      total: surveys.count,
      page: parseInt(page),
      limit: parseInt(limit),
      totalPages: Math.ceil(surveys.count / limit)
    });
  } catch (error) {
    console.error('Error getting project surveys:', error);
    return errorResponse(res, 500, 'Error retrieving surveys', error.message);
  }
}

// Additional Dashboard Cards API
export async function getAdditionalDashboardCards(req, res) {
  try {
    const { treelogy_id } = req.params;
    console.log(`📊 Fetching additional dashboard cards for project: ${treelogy_id}`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // QC Status Cards
    // QC Completed = qc_done = 3 (Verified/Approved)
    const qcCompletedResult = await projectConnection.query(`
      SELECT COUNT(*) as qc_completed 
      FROM treedetailsentered 
      WHERE qc_done = 3
    `);
    const qcCompleted = qcCompletedResult[0][0]?.qc_completed || 0;
    console.log('✅ QC Completed:', qcCompleted);

    // QC Pending = qc_done = 1 or qc_done = 2
    const qcPendingResult = await projectConnection.query(`
      SELECT COUNT(*) as qc_pending 
      FROM treedetailsentered 
      WHERE qc_done = 1 OR qc_done = 2
    `);
    const qcPending = qcPendingResult[0][0]?.qc_pending || 0;
    console.log('✅ QC Pending:', qcPending);

    // Tree Size Metrics
    const avgGirthResult = await projectConnection.query(`
      SELECT AVG(grith_cm) as avg_girth 
      FROM treedetailsentered 
      WHERE grith_cm IS NOT NULL AND grith_cm > 0
    `);
    const avgGirth = Math.round(avgGirthResult[0][0]?.avg_girth || 0);

    const avgHeightResult = await projectConnection.query(`
      SELECT AVG(height_mtr) as avg_height 
      FROM treedetailsentered 
      WHERE height_mtr IS NOT NULL AND height_mtr > 0
    `);
    const avgHeight = Math.round(avgHeightResult[0][0]?.avg_height || 0);

    const largeTreesResult = await projectConnection.query(`
      SELECT COUNT(*) as large_trees 
      FROM treedetailsentered 
      WHERE grith_cm > 100
    `);
    const largeTrees = largeTreesResult[0][0]?.large_trees || 0;

    // Temporal Metrics
    const todayEntriesResult = await projectConnection.query(`
      SELECT COUNT(*) as today_entries 
      FROM treedetailsentered 
      WHERE entered_date = CURRENT_DATE
    `);
    const todayEntries = todayEntriesResult[0][0]?.today_entries || 0;

    const thisWeekEntriesResult = await projectConnection.query(`
      SELECT COUNT(*) as this_week_entries 
      FROM treedetailsentered 
      WHERE entered_date >= DATE_TRUNC('week', CURRENT_DATE)
    `);
    const thisWeekEntries = thisWeekEntriesResult[0][0]?.this_week_entries || 0;

    const thisMonthEntriesResult = await projectConnection.query(`
      SELECT COUNT(*) as this_month_entries 
      FROM treedetailsentered 
      WHERE entered_date >= DATE_TRUNC('month', CURRENT_DATE)
    `);
    const thisMonthEntries = thisMonthEntriesResult[0][0]?.this_month_entries || 0;

    // Geographic Metrics
    const wardsCoveredResult = await projectConnection.query(`
      SELECT COUNT(DISTINCT societyname) as wards_covered 
      FROM treedetailsentered 
      WHERE societyname IS NOT NULL AND societyname != ''
    `);
    const wardsCovered = wardsCoveredResult[0][0]?.wards_covered || 0;

    const roadsCoveredResult = await projectConnection.query(`
      SELECT COUNT(DISTINCT roadname) as roads_covered 
      FROM treedetailsentered 
      WHERE roadname IS NOT NULL AND roadname != ''
    `);
    const roadsCovered = roadsCoveredResult[0][0]?.roads_covered || 0;

    const responseData = {
      // QC Status
      qcCompleted,
      qcPending,

      // Tree Size Metrics
      avgGirth,
      avgHeight,
      largeTrees,

      // Temporal Metrics
      todayEntries,
      thisWeekEntries,
      thisMonthEntries,

      // Geographic Metrics
      wardsCovered,
      roadsCovered
    };

    console.log('✅ Additional dashboard cards data:', responseData);
    return successResponse(res, 200, 'Additional dashboard cards data retrieved successfully', responseData);
  } catch (error) {
    console.error('Error getting additional dashboard cards:', error);
    return errorResponse(res, 'Error retrieving additional dashboard cards', 500, error.message);
  }
}

// Get dashboard cards data using existing tables
export async function getDashboardCards(req, res) {
  try {
    const { treelogy_id } = req.params;
    console.log(`📊 Fetching dashboard cards for project: ${treelogy_id}`);

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Execute the exact queries from PHP file using existing tables
    console.log('📊 Executing queries from existing tables...');

    // Query 1: Total Verified - "select count(id) as total_verified from trees"
    const totalVerifiedResult = await projectConnection.query("select count(id) as total_verified from trees");
    const totalVerified = totalVerifiedResult[0][0]?.total_verified || 0;
    console.log('✅ Total Verified:', totalVerified);

    // Query 2: Total Trees Surveyed - "select count(tree_details_id) as total_tree_count from treedetailsentered"
    const totalTreesResult = await projectConnection.query("select count(tree_details_id) as total_tree_count from treedetailsentered");
    const totalTreesSurveyed = totalTreesResult[0][0]?.total_tree_count || 0;
    console.log('✅ Total Trees Surveyed:', totalTreesSurveyed);

    // Query 3: Total Species - "select count(distinct local_name) as species_count from trees"
    const speciesResult = await projectConnection.query("select count(distinct local_name) as species_count from trees");
    const speciesCount = speciesResult[0][0]?.species_count || 0;
    console.log('✅ Species Count:', speciesCount);

    // Query 4: Most Common Tree - "select treename,count(treename) from treedetailsentered group by treename order by count(treename) desc limit 1"
    const mostCommonResult = await projectConnection.query("select treename,count(treename) from treedetailsentered group by treename order by count(treename) desc limit 1");
    const mostCommonTree = mostCommonResult[0][0]?.treename || 'N/A';
    console.log('✅ Most Common Tree:', mostCommonTree);

    const responseData = {
      totalTreesSurveyed,
      totalVerified,
      speciesCount,
      mostCommonTree
    };

    console.log('✅ Dashboard cards data:', responseData);
    return successResponse(res, 200, 'Dashboard cards data retrieved successfully', responseData);
  } catch (error) {
    console.error('Error getting dashboard cards:', error);
    return errorResponse(res, 'Error retrieving dashboard cards', 500, error.message);
  }
}

// Get condition wise donut chart data using existing tables
export async function getConditionChart(req, res) {
  try {
    const { treelogy_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);

    // Query condition data from trees table
    const conditionResult = await projectConnection.query(`
      SELECT c.condition_name as condition, COUNT(*) as count  , ARRAY_AGG(tree_details_id) as tree_ids
      FROM treedetailsentered t
      LEFT JOIN condition c ON t.condition_id = c.id 
      WHERE condition_id IS NOT NULL 
      GROUP BY condition 
      ORDER BY count DESC
    `);

    const conditionStats = conditionResult[0].map(row => ({
      tree_condition: row.condition,
      count: row.count.toString(),
      tree_ids: row.tree_ids
    }));

    return successResponse(res, 200, 'Condition chart data retrieved successfully', {
      chartType: 'condition',
      data: conditionStats
    });
  } catch (error) {
    console.error('Error getting condition chart:', error);
    return errorResponse(res, 'Error retrieving condition chart', 500, error.message);
  }
}

// Get remarks wise tree count chart data using existing tables
export async function getRemarksChart(req, res) {
  try {
    const { treelogy_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);

    // Query remarks data from trees table
    const remarksResult = await projectConnection.query(`
      SELECT 
        r.remark_name as remarks, 
        COUNT(*) as count,
        array_agg(td.tree_details_id) AS tree_ids
      FROM treedetailsentered td 
      LEFT JOIN remarks r on td.remarks_id = r.id
      GROUP BY r.remark_name
      ORDER BY count DESC
    `);

    // Map the results to include remarks, count, and tree_ids
    const remarksStats = remarksResult[0].map(row => ({
      remarks: row.remarks || "N/A",        // Default to "N/A" if remarks is null
      count: row.count.toString(),         // Convert the count to a string
      tree_ids: row.tree_ids || []         // Default to an empty array if tree_ids is null
    }));

    return successResponse(res, 200, 'Remarks chart data retrieved successfully', {
      chartType: 'remarks',
      data: remarksStats
    });

  } catch (error) {
    console.error('Error getting remarks chart:', error);
    return errorResponse(res, 'Error retrieving remarks chart', 500, error.message);
  }
}


// Get ownership wise pie chart data using existing tables
export async function getOwnershipChart(req, res) {
  try {
    const { treelogy_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);

    // Query ownership data from trees table
    const ownershipResult = await projectConnection.query(`
      SELECT ownership, COUNT(*) as count 
      FROM trees 
      WHERE ownership IS NOT NULL 
      GROUP BY ownership 
      ORDER BY count DESC
    `);

    const ownershipStats = ownershipResult[0].map(row => ({
      tree_ownership: row.ownership,
      count: row.count.toString()
    }));

    return successResponse(res, 200, 'Ownership chart data retrieved successfully', {
      chartType: 'ownership',
      data: ownershipStats
    });
  } catch (error) {
    console.error('Error getting ownership chart:', error);
    return errorResponse(res, 'Error retrieving ownership chart', 500, error.message);
  }
}

// Get species wise tree count (Top 15) bar chart data
export async function getSpeciesChart(req, res) {
  try {
    const { treelogy_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);

    // Query species data from treedetailsentered table (Top 15)
    const speciesResult = await projectConnection.query(`
      SELECT treename, COUNT(*) as count 
      FROM treedetailsentered 
      WHERE treename IS NOT NULL 
      GROUP BY treename 
      ORDER BY count DESC 
      LIMIT 15
    `);

    const speciesStats = speciesResult[0].map(row => ({
      local_name: row.treename,
      count: row.count.toString()
    }));

    return successResponse(res, 200, 'Species chart data retrieved successfully', {
      chartType: 'species',
      data: speciesStats
    });
  } catch (error) {
    console.error('Error getting species chart:', error);
    return errorResponse(res, 'Error retrieving species chart', 500, error.message);
  }
}

// Get economic importance wise tree count bar chart data
export async function getEconomicImportanceChart(req, res) {
  try {
    const { treelogy_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);

    // Query economic importance data from trees table
    const economicResult = await projectConnection.query(`
      SELECT economic_i, COUNT(*) as count 
      FROM trees 
      WHERE economic_i IS NOT NULL 
      GROUP BY economic_i 
      ORDER BY count DESC
    `);

    const economicStats = economicResult[0].map(row => ({
      economic_importance: row.economic_i,
      count: row.count.toString()
    }));

    return successResponse(res, 200, 'Economic importance chart data retrieved successfully', {
      chartType: 'economic_importance',
      data: economicStats
    });
  } catch (error) {
    console.error('Error getting economic importance chart:', error);
    return errorResponse(res, 'Error retrieving economic importance chart', 500, error.message);
  }
}

// Get survey statistics for dashboard (legacy function - keeping for backward compatibility)
export async function getSurveyStatistics(req, res) {
  try {
    const { treelogy_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);
    const Survey = createSurveyModel(projectConnection);

    // Get statistics
    const [
      totalTrees,
      verifiedTrees,
      speciesCount,
      heritageTrees,
      rareTrees,
      indigenousTrees,
      exoticTrees,
      conditionStats,
      ownershipStats,
      speciesStats
    ] = await Promise.all([
      Survey.count({ where: { status: 'ACTIVE' } }),
      Survey.count({ where: { status: 'ACTIVE', verified: true } }),
      Survey.count({
        where: { status: 'ACTIVE' },
        distinct: true,
        col: 'species_type'
      }),
      Survey.count({ where: { status: 'ACTIVE', is_heritage: true } }),
      Survey.count({ where: { status: 'ACTIVE', is_rare: true } }),
      Survey.count({ where: { status: 'ACTIVE', is_indigenous: true } }),
      Survey.count({ where: { status: 'ACTIVE', is_exotic: true } }),
      Survey.findAll({
        attributes: [
          'tree_condition',
          [projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'count']
        ],
        where: { status: 'ACTIVE' },
        group: ['tree_condition'],
        raw: true
      }),
      Survey.findAll({
        attributes: [
          'tree_ownership',
          [projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'count']
        ],
        where: { status: 'ACTIVE' },
        group: ['tree_ownership'],
        raw: true
      }),
      Survey.findAll({
        attributes: [
          'species_type',
          [projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'count']
        ],
        where: { status: 'ACTIVE' },
        group: ['species_type'],
        order: [[projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'DESC']],
        limit: 10,
        raw: true
      })
    ]);

    return successResponse(res, 200, 'Statistics retrieved successfully', {
      totalTrees,
      verifiedTrees,
      speciesCount,
      heritageTrees,
      rareTrees,
      indigenousTrees,
      exoticTrees,
      conditionDistribution: conditionStats,
      ownershipDistribution: ownershipStats,
      topSpecies: speciesStats
    });
  } catch (error) {
    console.error('Error getting survey statistics:', error);
    return errorResponse(res, 500, 'Error retrieving statistics', error.message);
  }
}

// Get survey by ID
export async function getSurveyById(req, res) {
  try {
    const { treelogy_id, survey_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);
    const Survey = createSurveyModel(projectConnection);

    const survey = await Survey.findByPk(survey_id);
    if (!survey) {
      return errorResponse(res, 404, 'Survey not found');
    }

    return successResponse(res, 200, 'Survey retrieved successfully', survey);
  } catch (error) {
    console.error('Error getting survey:', error);
    return errorResponse(res, 500, 'Error retrieving survey', error.message);
  }
}

// Update survey (for verification, etc.)
export async function updateSurvey(req, res) {
  try {
    const { treelogy_id, survey_id } = req.params;
    const { verified, verified_by, remarks, tree_condition, tree_ownership } = req.body;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);
    const Survey = createSurveyModel(projectConnection);

    const survey = await Survey.findByPk(survey_id);
    if (!survey) {
      return errorResponse(res, 404, 'Survey not found');
    }

    // Update survey
    const updateData = {};
    if (verified !== undefined) {
      updateData.verified = verified;
      if (verified) {
        updateData.verified_by = verified_by;
        updateData.verified_at = new Date();
      }
    }
    if (remarks !== undefined) updateData.remarks = remarks;
    if (tree_condition !== undefined) updateData.tree_condition = tree_condition;
    if (tree_ownership !== undefined) updateData.tree_ownership = tree_ownership;

    await survey.update(updateData);

    return successResponse(res, 200, 'Survey updated successfully', survey);
  } catch (error) {
    console.error('Error updating survey:', error);
    return errorResponse(res, 500, 'Error updating survey', error.message);
  }
}

// Delete survey
export async function softDeleteSurvey(req, res) {
  try {
    const { treelogy_id, survey_id } = req.params;
    const { status } = req.query;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const conn = getSubDbConnection(project.dbname);

    // Soft delete in treedetailsentered table
    await conn.query(
      'UPDATE treedetailsentered SET is_deleted = $1 WHERE tree_details_id = $2',
      [status, survey_id] // <-- pass as array for parameterized query
    );

    // If using a Survey model in the main DB
    // const survey = await Survey.findByPk(survey_id);
    // if (!survey) {
    //   return errorResponse(res, 'Survey not found', 404);
    // }

    // // Soft delete in Survey table
    // await survey.update({ is_deleted: true });

    return successResponse(res, 200, 'Survey deleted successfully');
  } catch (err) {
    console.error(err);
    return errorResponse(res, 'Internal server error', 500);
  }

}

// Permanently delete survey
export async function permanentDeleteSurvey(req, res) {
  try {
    const { treelogy_id, survey_id } = req.params;

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      return errorResponse(res, 'Project not found', 404);
    }

    // Get project database connection
    const conn = getSubDbConnection(project.dbname);

    // Permanently delete the record from treedetailsentered
    const result = await conn.query(
      'DELETE FROM treedetailsentered WHERE tree_details_id = $1',
      [survey_id] // Parameterized query
    );

    if (result.rowCount === 0) {
      return errorResponse(res, 'Survey not found', 404);
    }

    // If you also maintain a main Survey table (optional)
    // const survey = await Survey.findByPk(survey_id);
    // if (survey) await survey.destroy();

    return successResponse(res, 200, 'Survey permanently deleted');
  } catch (err) {
    console.error(err);
    return errorResponse(res, 'Internal server error', 500);
  }
}


// QC Dashboard Functions
export async function getQCPendingSurveys(req, res) {
  try {
    const { treelogy_id } = req.params;
    let filters = {};

    if (req.query.filters) {
      try {
        filters = JSON.parse(req.query.filters);
      } catch (err) {
        console.error("Error parsing filters:", err);
      }
    }
    console.log("filters.date", filters);
    console.log("req.query", req.query);

    const { tab } = req.query;
    console.log("activetab", tab);

    const authHeader = req.headers['authorization'];
    if (!authHeader) {
      return res.status(401).json({ error: 'Authorization header missing' });
    }

    // Split and get token part only
    const token = authHeader.split(' ')[1];
    console.log("token:", token);

    const tokenDecoded = jwt.decode(token);
    console.log("tokenDecoded:", tokenDecoded);
    const logged_in_user_id = tokenDecoded.login_id;
    const {
      page = 1,
      limit = 50,
      search = '',
      qc_status = '',
      ward = '',
      survey_id = '',
      user_id = '',
      date = ''
    } = req.query;

    console.log(`📊 Fetching QC surveys for project: ${treelogy_id}`);
    console.log(`   Filters - Page: ${page}, Limit: ${limit}, Search: "${search}", QC Status: "${qc_status || 'all'}", Ward: "${ward}", Survey ID: "${survey_id}", User ID: "${user_id}", Date: "${date}"`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Calculate offset
    const offset = (parseInt(page) - 1) * parseInt(limit);

    // Check if ward_name column exists
    let hasWardColumn = false;
    try {
      const columnCheck = await projectConnection.query(`
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'treedetailsentered' 
        AND column_name = 'ward_name'
      `, {
        type: projectConnection.QueryTypes.SELECT
      });

      hasWardColumn = columnCheck && columnCheck.length > 0;
      console.log(`Ward column exists: ${hasWardColumn}`);
    } catch (err) {
      console.log('Ward column check failed:', err.message);
    }
    console.log("role_name_token", tokenDecoded.role_name);

    let whereConditions = [];

    // Build WHERE clause with all filters
    if (tokenDecoded.role_name == 'QC_USER') {
      if (tab === 'pending') {
        whereConditions = [`t.qc_done = 0`, `t.is_deleted != true`];

      }
      // else if (tab === 'done') {
      //   whereConditions = [`t.qc_done = 1`];
      // }
      else if (tab === 'accept') {
        whereConditions = [`t.qc_done = 1`];
      }
      else if (tab === 'reject') {
        // whereConditions = [`t.is_rejected = true`, `t.rejected_at = 4`];
        whereConditions = [ `t.qc_done = 2`];

      } else if (tab === 'trash') {
        whereConditions = [`t.is_deleted = true`];
      } else if (tab === 'all') {
        whereConditions = [`t.qc_done = 0`, `t.is_deleted != true`];

      }
    } else if (tokenDecoded.role_name == 'QC_USER_2') {
      // console.log("user 2");

      if (tab === 'pending') {
        console.log("tabp");

        whereConditions = [`t.qc_done = 1`];
      }
      // else if (tab === 'done') {
      //   whereConditions = [`t.qc_done = 3`];

      // }
      else if (tab === 'accept') {
        whereConditions = [`t.qc_done = 3`];
      } else if (tab === 'reject') {
        // whereConditions = [`t.is_rejected = true`, `t.rejected_at = 7`];
        whereConditions = [`t.qc_done = 4`];
      } else if (tab === 'trash') {
        whereConditions = [`t.is_deleted = true`];
      }
    } else if (tokenDecoded.role_name == 'SUPER_ADMIN') {
      if (tab === 'pending') {
        console.log("tabp");

        whereConditions = [`t.qc_done = 1`];
      } else if (tab === 'done') {
        whereConditions = [`t.qc_done = 3`];

      } else if (tab === 'accept') {
        whereConditions = [`t.qc_done IN (3)`];
      } else if (tab === 'reject') {
        whereConditions = [`t.qc_done = 4`];
      } else if (tab === 'trash') {
        whereConditions = [`t.is_deleted = true`];
      }
    } else if (tokenDecoded.role_name == 'SURVEYOR') {

      if (tab === 'pending') {
        console.log("tabp", tokenDecoded);

        whereConditions = [`t.qc_done = 0`, `t.user_id = ${tokenDecoded.login_id}`];
      }
      // else if (tab === 'done') {
      //   whereConditions = [`t.qc_done = 3`];

      // }
      else if (tab === 'accept') {
        whereConditions = [`t.qc_done IN (3,1)`, `t.user_id = ${tokenDecoded.login_id}`];
      } else if (tab === 'reject') {
        // whereConditions = [`t.is_rejected = true`, `t.user_id = ${tokenDecoded.login_id}`];
        whereConditions = [`t.qc_done IN (2,4)`];
      } else if (tab === 'trash') {
        whereConditions = [`t.is_deleted = true`, `t.user_id = ${tokenDecoded.login_id}`];
      }
    }
    //  else if (tokenDecoded.role_name == 'CLIENT_ADMIN') {
    //   if (tab === 'pending') {
    //     whereConditions = [`t.qc_done = 2`];
    //   } else if (tab === 'done') {
    //     whereConditions = [`t.qc_done = 3`];
    //   } else if (tab === 'reject') {
    //     whereConditions = [`t.qc_done = 4`];
    //   }
    // }
    let bindParams = [];
    let paramIndex = 1;

    // QC Status filter
    if (qc_status && qc_status !== 'all' && qc_status !== '') {
      whereConditions.push(`t.qc_done = $${paramIndex}`);
      bindParams.push(parseInt(qc_status));
      paramIndex++;
    }
    // If qc_status is 'all' or empty, don't add any QC status filter (show all surveys)

    // Survey ID filter (exact match)
    if (survey_id && survey_id.trim() !== '') {
      whereConditions.push(`t.tree_details_id = $${paramIndex}`);
      bindParams.push(parseInt(survey_id));
      paramIndex++;
    }

    // Ward filter (only if column exists)
    if (filters.fromDate && filters.toDate) {
      whereConditions.push(`DATE(t.entered_date) BETWEEN $${paramIndex} AND $${paramIndex + 1}`);
      bindParams.push(filters.fromDate);
      bindParams.push(filters.toDate);
      paramIndex += 2;
    }


    // User ID filter
    if (user_id && user_id.trim() !== '') {
      whereConditions.push(`t.user_id = $${paramIndex}`);
      bindParams.push(parseInt(user_id));
      paramIndex++;
    }

    // // Date filter
    // if (filters.fromDate && filters.toDate.trim() !== '') {
    //   whereConditions.push(`DATE(t.entered_date) = $${paramIndex}`);
    //   bindParams.push(filters.date);
    //   paramIndex++;
    // }

    // General search (tree name, other name, address, tree_details_id, master tree names)
    if (search && search.trim() !== '') {
      whereConditions.push(`(t.treename ILIKE $${paramIndex} OR t.other_name ILIKE $${paramIndex} OR t.googleaddress ILIKE $${paramIndex} OR t.tree_details_id::text ILIKE $${paramIndex} OR tl.tree_name ILIKE $${paramIndex} OR tl.comman_name ILIKE $${paramIndex})`);
      bindParams.push(`%${search}%`);
      paramIndex++;
    }

    const whereClause = whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : '';

    // Get total count first
    const countQuery = `SELECT COUNT(*) as total FROM treedetailsentered t LEFT JOIN treelist tl ON t.tree_id = tl.tree_id ${whereClause}`;
    console.log('Count Query:', countQuery);
    console.log('Bind Params:', bindParams);

    const countResult = await projectConnection.query(countQuery, {
      bind: bindParams,
      type: projectConnection.QueryTypes.SELECT
    });
    const totalCount = parseInt(countResult[0]?.total || 0);

    // Build SELECT clause with ward column if it exists
    const wardColumn = hasWardColumn ? 'ward_name as ward,' : '';

    // Get paginated surveys with tree master data
    const surveysQuery = `
      SELECT 
        t.tree_details_id as id,
        t.user_id,
        t.tree_id,
        t.entered_date as timestamp,
        t.latitude,
        t.longitude,
        t.treename as local_name,
        t.other_name,
        t.grith_cm as girth,
        t.height_mtr as height,
        t.canopy_diameter as canopy,
        t.other_remarks as remarks,
        t.ownership_name as ownership,
        t.googleaddress as address,
        ${wardColumn}
        t.qc_done,
        t.qc_status,
        t.condition_id,
        t.ownership_id,
        t.societyname,
        t.roadname,
        t.entered_time,
        t.updated_date,
        t.qc_updated_time,
        t.updated_by,
        t.qc_final_submitted,
        t.qc_updated_date,
        t.version2,
        t.user_seq_id,
        t.old_latitude,
        t.old_longitude,
        t.drag_distance,
        t.qc_old_latitude,
        t.qc_old_longitude,
        t.qc_drag_distance,
        t.allocated_ward,
        t.within_boundary,
        t.new_or_old,
        t.grid,
        t.lat2,
        t.lon2,
        t.dist,
        t.bla_imbal_id,
        t.remarks_id,
        t.dist_from_prev_tree,
        t.special_comment_id,
        t.bird_nest,
        t.resurvey_done,
        t.un_submit_date,
        t.un_submit_time,
        t.un_updated_by,
        t.comment_un,
        t.bo_qc_submit_date,
        t.bo_qc_submit_time,
        t.bo_qc_done_by,
        t.comment_bo_qc,
        t.bo_rare_qc_date,
        t.bo_rare_qc_time,
        t.bo_rare_qc_doneby,
        t.bo_rare_qc_comment,
        t.age,
        t.date_of_plantation,
        t.client_name,
        t.contractor_name,
        t.ownership_contact_no,
        t.manual_tree_details_id,
        t.is_rejected,
        t.rejected_at,
        tl.tree_name,
        tl.comman_name,
        tl.family,
        tl.phenology,
        tl.economic,
        tl.flowering,
        tl.color,
        tl.botanical,
        tl.is_rare,
        tl.priority,
        tl.wood_density,
        tl.growth_factor,
        tl.gujarati_name,
        tu.email,
        tu.first_name,
        tu.last_name
      FROM treedetailsentered t
      LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
      LEFT JOIN treeusers tu ON t.user_id = tu.uid
      ${whereClause}
      ORDER BY t.tree_details_id DESC
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    const surveyBindParams = [...bindParams, parseInt(limit), offset];
    console.log('Surveys Query:', surveysQuery);
    console.log('Survey Bind Params:', surveyBindParams);

    const pendingSurveys = await projectConnection.query(surveysQuery, {
      bind: surveyBindParams,
      type: projectConnection.QueryTypes.SELECT
    });

    const surveys = pendingSurveys || [];
    const totalPages = Math.ceil(totalCount / parseInt(limit));

    console.log(`✅ Found ${surveys.length} surveys (Page ${page}/${totalPages}, Total: ${totalCount})`);

    return successResponse(res, 200, 'QC surveys retrieved successfully', {
      surveys,
      pagination: {
        currentPage: parseInt(page),
        limit: parseInt(limit),
        totalItems: totalCount,
        totalPages,
        hasNextPage: parseInt(page) < totalPages,
        hasPreviousPage: parseInt(page) > 1
      }
    });
  } catch (error) {
    console.error('Error getting QC surveys:', error);
    return errorResponse(res, 'Error retrieving QC surveys', 500, error.message);
  }
}

export async function addQcLogs(req, res) {
  try {
    console.log(req.body);

    const { treelogy_id } = req.params; // ✅ no parentheses
    const { tree_details_id, updated_by, updated_at_lvl, current_status, updated_status } = req.body;

    // Fetch treelogy details
    const treelogy = await Treelogy.findByPk(treelogy_id); // ✅ await
    if (!treelogy) {
      return res.status(404).json({ error: "Treelogy not found" });
    }

    const dbName = treelogy.dbname;
    const conn = getSubDbConnection(dbName); // custom DB connection

    // Insert qc log
    const query = `
      INSERT INTO qc_logs (tree_details_id, updated_by, updated_at_lvl,current_status,updated_status)
      VALUES ($1, $2, $3,$4,$5)
      RETURNING *;
    `;

    const values = [tree_details_id, updated_by, updated_at_lvl, current_status, updated_status];

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

    return res.status(200).json({
      message: "QC Log added successfully",
      data: result.rows[0]
    });

  } catch (error) {
    console.error("Error adding QC logs:", error);
    return res.status(500).json({ error: "Internal Server Error" });
  }
}

export async function getQcLogs(req, res) {
  try {
    console.log(req.body);

    const { treelogy_id } = req.params; // ✅ no parentheses
    const { tree_details_id } = req.query;

    // Fetch treelogy details
    const treelogy = await Treelogy.findByPk(treelogy_id); // ✅ await
    if (!treelogy) {
      return res.status(404).json({ error: "Treelogy not found" });
    }

    const dbName = treelogy.dbname;
    const conn = getSubDbConnection(dbName); // custom DB connection

    // Insert qc log
    const query = `
      SELECT * FROM QC_LOGS WHERE tree_details_id = $1;
    `;

    const values = [tree_details_id];

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

    return res.status(200).json({
      data: result.rows
    });

  } catch (error) {
    console.error("Error adding QC logs:", error);
    return res.status(500).json({ error: "Internal Server Error" });
  }
}



export async function getTreeDataById(req, res) {
  const { id, project_id } = req.query;
  console.log("id, project_id", id, project_id);
  const project = await Treelogy.findByPk(project_id)
  // console.log("Treelogy", project);

  const SubDb = getSubDbConnection(project.dbname)
  // SubDb.query('select * from treedetailsentered where tree_details_id = $1')

  const query = 'SELECT * FROM treedetailsentered WHERE tree_details_id = $1';
  const values = [id];
  const result = await SubDb.query(query, values);
  console.log("result", result);
  return res.json({ data: result.rows });
}


// working
// export async function getQCPendingSurveys(req, res) {
//   try {
//     const { treelogy_id } = req.params;
//     const { tab } = req.query;
//     console.log("activetab", tab);

//     const authHeader = req.headers['authorization'];
//     if (!authHeader) {
//       return res.status(401).json({ error: 'Authorization header missing' });
//     }

//     // Split and get token part only
//     const token = authHeader.split(' ')[1];
//     console.log("token:", token);

//     const tokenDecoded = jwt.decode(token);
//     console.log("tokenDecoded:", tokenDecoded);
//     const logged_in_user_id = tokenDecoded.login_id;
//     const {
//       page = 1,
//       limit = 50,
//       search = '',
//       qc_status = '',
//       ward = '',
//       survey_id = '',
//       user_id = '',
//       date = ''
//     } = req.query;

//     console.log(`📊 Fetching QC surveys for project: ${treelogy_id}`);
//     console.log(`   Filters - Page: ${page}, Limit: ${limit}, Search: "${search}", QC Status: "${qc_status || 'all'}", Ward: "${ward}", Survey ID: "${survey_id}", User ID: "${user_id}", Date: "${date}"`);

//     const project = await Treelogy.findByPk(treelogy_id);
//     if (!project) {
//       console.log('❌ Project not found:', treelogy_id);
//       return errorResponse(res, 'Project not found', 404);
//     }

//     console.log('✅ Project found:', project.project_name);
//     const projectConnection = await getProjectConnection(project.dbname);
//     console.log('✅ Database connection established');

//     // Calculate offset
//     const offset = (parseInt(page) - 1) * parseInt(limit);

//     // Check if ward_name column exists
//     let hasWardColumn = false;
//     try {
//       const columnCheck = await projectConnection.query(`
//         SELECT column_name 
//         FROM information_schema.columns 
//         WHERE table_name = 'treedetailsentered' 
//         AND column_name = 'ward_name'
//       `, {
//         type: projectConnection.QueryTypes.SELECT
//       });

//       hasWardColumn = columnCheck && columnCheck.length > 0;
//       console.log(`Ward column exists: ${hasWardColumn}`);
//     } catch (err) {
//       console.log('Ward column check failed:', err.message);
//     }
//     console.log("role_name_token", tokenDecoded.role_name);

//     let whereConditions = [];

//     // Build WHERE clause with all filters
//     if (tokenDecoded.role_name == 'QC_USER') {
//       whereConditions = [`t.user_id =${logged_in_user_id}`, `t.qc_done != 1`];
//     } else if (tokenDecoded.role_name == 'QC_USER_2') {
//       whereConditions = [`t.qc_done != 2`];
//     }
//     let bindParams = [];
//     let paramIndex = 1;

//     // QC Status filter
//     if (qc_status && qc_status !== 'all' && qc_status !== '') {
//       whereConditions.push(`t.qc_done = $${paramIndex}`);
//       bindParams.push(parseInt(qc_status));
//       paramIndex++;
//     }
//     // If qc_status is 'all' or empty, don't add any QC status filter (show all surveys)

//     // Survey ID filter (exact match)
//     if (survey_id && survey_id.trim() !== '') {
//       whereConditions.push(`t.tree_details_id = $${paramIndex}`);
//       bindParams.push(parseInt(survey_id));
//       paramIndex++;
//     }

//     // Ward filter (only if column exists)
//     if (hasWardColumn && ward && ward.trim() !== '' && ward !== 'all') {
//       whereConditions.push(`t.ward_name ILIKE $${paramIndex}`);
//       bindParams.push(`%${ward}%`);
//       paramIndex++;
//     }

//     // User ID filter
//     if (user_id && user_id.trim() !== '') {
//       whereConditions.push(`t.user_id = $${paramIndex}`);
//       bindParams.push(parseInt(user_id));
//       paramIndex++;
//     }

//     // Date filter
//     if (date && date.trim() !== '') {
//       whereConditions.push(`DATE(t.entered_date) = $${paramIndex}`);
//       bindParams.push(date);
//       paramIndex++;
//     }

//     // General search (tree name, other name, address, tree_details_id, master tree names)
//     if (search && search.trim() !== '') {
//       whereConditions.push(`(t.treename ILIKE $${paramIndex} OR t.other_name ILIKE $${paramIndex} OR t.googleaddress ILIKE $${paramIndex} OR t.tree_details_id::text ILIKE $${paramIndex} OR tl.tree_name ILIKE $${paramIndex} OR tl.comman_name ILIKE $${paramIndex})`);
//       bindParams.push(`%${search}%`);
//       paramIndex++;
//     }

//     const whereClause = whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : '';

//     // Get total count first
//     const countQuery = `SELECT COUNT(*) as total FROM treedetailsentered t LEFT JOIN treelist tl ON t.tree_id = tl.tree_id ${whereClause}`;
//     console.log('Count Query:', countQuery);
//     console.log('Bind Params:', bindParams);

//     const countResult = await projectConnection.query(countQuery, {
//       bind: bindParams,
//       type: projectConnection.QueryTypes.SELECT
//     });
//     const totalCount = parseInt(countResult[0]?.total || 0);

//     // Build SELECT clause with ward column if it exists
//     const wardColumn = hasWardColumn ? 'ward_name as ward,' : '';

//     // Get paginated surveys with tree master data
//     const surveysQuery = `
//       SELECT 
//         t.tree_details_id as id,
//         t.user_id,
//         t.tree_id,
//         t.entered_date as timestamp,
//         t.latitude,
//         t.longitude,
//         t.treename as local_name,
//         t.other_name,
//         t.grith_cm as girth,
//         t.height_mtr as height,
//         t.canopy_diameter as canopy,
//         t.other_remarks as remarks,
//         t.ownership_name as ownership,
//         t.googleaddress as address,
//         ${wardColumn}
//         t.qc_done,
//         t.qc_status,
//         t.condition_id,
//         t.ownership_id,
//         t.societyname,
//         t.roadname,
//         t.entered_time,
//         t.updated_date,
//         t.qc_updated_time,
//         t.updated_by,
//         t.qc_final_submitted,
//         t.qc_updated_date,
//         t.version2,
//         t.user_seq_id,
//         t.old_latitude,
//         t.old_longitude,
//         t.drag_distance,
//         t.qc_old_latitude,
//         t.qc_old_longitude,
//         t.qc_drag_distance,
//         t.allocated_ward,
//         t.within_boundary,
//         t.new_or_old,
//         t.grid,
//         t.lat2,
//         t.lon2,
//         t.dist,
//         t.bla_imbal_id,
//         t.remarks_id,
//         t.dist_from_prev_tree,
//         t.special_comment_id,
//         t.bird_nest,
//         t.resurvey_done,
//         t.un_submit_date,
//         t.un_submit_time,
//         t.un_updated_by,
//         t.comment_un,
//         t.bo_qc_submit_date,
//         t.bo_qc_submit_time,
//         t.bo_qc_done_by,
//         t.comment_bo_qc,
//         t.bo_rare_qc_date,
//         t.bo_rare_qc_time,
//         t.bo_rare_qc_doneby,
//         t.bo_rare_qc_comment,
//         t.age,
//         t.date_of_plantation,
//         t.client_name,
//         t.contractor_name,
//         t.ownership_contact_no,
//         t.manual_tree_details_id,
//         tl.tree_name,
//         tl.comman_name,
//         tl.family,
//         tl.phenology,
//         tl.economic,
//         tl.flowering,
//         tl.color,
//         tl.botanical,
//         tl.is_rare,
//         tl.priority,
//         tl.wood_density,
//         tl.growth_factor,
//         tl.gujarati_name
//       FROM treedetailsentered t
//       LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
//       ${whereClause}
//       ORDER BY t.entered_date DESC
//       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
//     `;

//     const surveyBindParams = [...bindParams, parseInt(limit), offset];
//     console.log('Surveys Query:', surveysQuery);
//     console.log('Survey Bind Params:', surveyBindParams);

//     const pendingSurveys = await projectConnection.query(surveysQuery, {
//       bind: surveyBindParams,
//       type: projectConnection.QueryTypes.SELECT
//     });

//     const surveys = pendingSurveys || [];
//     const totalPages = Math.ceil(totalCount / parseInt(limit));

//     console.log(`✅ Found ${surveys.length} surveys (Page ${page}/${totalPages}, Total: ${totalCount})`);

//     return successResponse(res, 200, 'QC surveys retrieved successfully', {
//       surveys,
//       pagination: {
//         currentPage: parseInt(page),
//         limit: parseInt(limit),
//         totalItems: totalCount,
//         totalPages,
//         hasNextPage: parseInt(page) < totalPages,
//         hasPreviousPage: parseInt(page) > 1
//       }
//     });
//   } catch (error) {
//     console.error('Error getting QC surveys:', error);
//     return errorResponse(res, 'Error retrieving QC surveys', 500, error.message);
//   }
// }

export async function getQCSurveyById(req, res) {
  try {
    const { treelogy_id, survey_id } = req.params;
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];
    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    console.log(decoded);

    console.log(`📊 Fetching single QC survey for project: ${treelogy_id}, survey ID: ${survey_id}`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Check if ward_name column exists
    let hasWardColumn = false;
    try {
      const columnCheck = await projectConnection.query(`
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'treedetailsentered' 
        AND column_name = 'ward_name'
      `, {
        type: projectConnection.QueryTypes.SELECT
      });

      hasWardColumn = columnCheck && columnCheck.length > 0;
      console.log(`Ward column exists: ${hasWardColumn}`);
    } catch (err) {
      console.log('Ward column check failed:', err.message);
    }

    // Build SELECT clause with ward column if it exists
    const wardColumn = hasWardColumn ? 'ward_name as ward,' : '';
    let surveyQuery = ``;

    if (decoded.role_name === 'QC_USER') {
      console.log('1');

      surveyQuery = `
      SELECT 
        t.tree_details_id as id,
        t.user_id,
        t.tree_id,
        t.entered_date as timestamp,
        t.latitude,
        t.longitude,
        t.treename as local_name,
        t.other_name,
        t.grith_cm as girth,
        t.height_mtr as height,
        t.canopy_diameter as canopy,
        t.other_remarks as remarks,
        t.ownership_name as ownership,
        t.googleaddress as address,
        ${wardColumn}
        t.qc_done,
        t.qc_status,
        t.condition_id,
        t.ownership_id,
        t.societyname,
        t.roadname,
        t.entered_time,
        t.updated_date,
        t.qc_updated_time,
        t.updated_by,
        t.qc_final_submitted,
        t.qc_updated_date,
        t.version2,
        t.user_seq_id,
        t.old_latitude,
        t.old_longitude,
        t.drag_distance,
        t.qc_old_latitude,
        t.qc_old_longitude,
        t.qc_drag_distance,
        t.allocated_ward,
        t.within_boundary,
        t.new_or_old,
        t.grid,
        t.lat2,
        t.lon2,
        t.dist,
        t.bla_imbal_id,
        t.remarks_id,
        t.dist_from_prev_tree,
        t.special_comment_id,
        t.bird_nest,
        t.resurvey_done,
        t.un_submit_date,
        t.un_submit_time,
        t.un_updated_by,
        t.comment_un,
        t.bo_qc_submit_date,
        t.bo_qc_submit_time,
        t.bo_qc_done_by,
        t.comment_bo_qc,
        t.bo_rare_qc_date,
        t.bo_rare_qc_time,
        t.bo_rare_qc_doneby,
        t.bo_rare_qc_comment,
        t.age,
        t.date_of_plantation,
        t.client_name,
        t.contractor_name,
        t.ownership_contact_no,
        t.manual_tree_details_id,
        t.is_rejected,
        t.rejected_at,
        t.rejected_by,
        tl.tree_name,
        tl.comman_name,
        tl.family,
        tl.phenology,
        tl.economic,
        tl.flowering,
        tl.color,
        tl.botanical,
        tl.is_rare,
        tl.priority,
        tl.wood_density,
        tl.growth_factor,
        tl.gujarati_name
      FROM treedetailsentered t
      LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
      WHERE t.tree_details_id = $1
      AND t.qc_done != 1
    `;
    } else if (decoded.role_name === 'QC_USER_2') {
      console.log('2');

      surveyQuery = `
      SELECT 
        t.tree_details_id as id,
        t.user_id,
        t.tree_id,
        t.entered_date as timestamp,
        t.latitude,
        t.longitude,
        t.treename as local_name,
        t.other_name,
        t.grith_cm as girth,
        t.height_mtr as height,
        t.canopy_diameter as canopy,
        t.other_remarks as remarks,
        t.ownership_name as ownership,
        t.googleaddress as address,
        ${wardColumn}
        t.qc_done,
        t.qc_status,
        t.condition_id,
        t.ownership_id,
        t.societyname,
        t.roadname,
        t.entered_time,
        t.updated_date,
        t.qc_updated_time,
        t.updated_by,
        t.qc_final_submitted,
        t.qc_updated_date,
        t.version2,
        t.user_seq_id,
        t.old_latitude,
        t.old_longitude,
        t.drag_distance,
        t.qc_old_latitude,
        t.qc_old_longitude,
        t.qc_drag_distance,
        t.allocated_ward,
        t.within_boundary,
        t.new_or_old,
        t.grid,
        t.lat2,
        t.lon2,
        t.dist,
        t.bla_imbal_id,
        t.remarks_id,
        t.dist_from_prev_tree,
        t.special_comment_id,
        t.bird_nest,
        t.resurvey_done,
        t.un_submit_date,
        t.un_submit_time,
        t.un_updated_by,
        t.comment_un,
        t.bo_qc_submit_date,
        t.bo_qc_submit_time,
        t.bo_qc_done_by,
        t.comment_bo_qc,
        t.bo_rare_qc_date,
        t.bo_rare_qc_time,
        t.bo_rare_qc_doneby,
        t.bo_rare_qc_comment,
        t.age,
        t.date_of_plantation,
        t.client_name,
        t.contractor_name,
        t.ownership_contact_no,
        t.manual_tree_details_id,
        tl.tree_name,
        tl.comman_name,
        tl.family,
        tl.phenology,
        tl.economic,
        tl.flowering,
        tl.color,
        tl.botanical,
        tl.is_rare,
        tl.priority,
        tl.wood_density,
        tl.growth_factor,
        tl.gujarati_name
      FROM treedetailsentered t
      LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
      WHERE t.tree_details_id = $1
      AND t.qc_done != 2
    `;
    } else {
      // root user 
      // Get single survey with tree master data
      surveyQuery = `
        SELECT 
          t.tree_details_id as id,
          t.user_id,
          t.tree_id,
          t.entered_date as timestamp,
          t.latitude,
          t.longitude,
          t.treename as local_name,
          t.other_name,
          t.grith_cm as girth,
          t.height_mtr as height,
          t.canopy_diameter as canopy,
          t.other_remarks as remarks,
          t.ownership_name as ownership,
          t.googleaddress as address,
          ${wardColumn}
          t.qc_done,
          t.qc_status,
          t.condition_id,
          t.ownership_id,
          t.societyname,
          t.roadname,
          t.entered_time,
          t.updated_date,
          t.qc_updated_time,
          t.updated_by,
          t.qc_final_submitted,
          t.qc_updated_date,
          t.version2,
          t.user_seq_id,
          t.old_latitude,
          t.old_longitude,
          t.drag_distance,
          t.qc_old_latitude,
          t.qc_old_longitude,
          t.qc_drag_distance,
          t.allocated_ward,
          t.within_boundary,
          t.new_or_old,
          t.grid,
          t.lat2,
          t.lon2,
          t.dist,
          t.bla_imbal_id,
          t.remarks_id,
          t.dist_from_prev_tree,
          t.special_comment_id,
          t.bird_nest,
          t.resurvey_done,
          t.un_submit_date,
          t.un_submit_time,
          t.un_updated_by,
          t.comment_un,
          t.bo_qc_submit_date,
          t.bo_qc_submit_time,
          t.bo_qc_done_by,
          t.comment_bo_qc,
          t.bo_rare_qc_date,
          t.bo_rare_qc_time,
          t.bo_rare_qc_doneby,
          t.bo_rare_qc_comment,
          t.age,
          t.date_of_plantation,
          t.client_name,
          t.contractor_name,
          t.ownership_contact_no,
          t.manual_tree_details_id,
          tl.tree_name,
          tl.comman_name,
          tl.family,
          tl.phenology,
          tl.economic,
          tl.flowering,
          tl.color,
          tl.botanical,
          tl.is_rare,
          tl.priority,
          tl.wood_density,
          tl.growth_factor,
          tl.gujarati_name
        FROM treedetailsentered t
        LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
        WHERE t.tree_details_id = $1
      `;
    }


    console.log('Survey Query:', surveyQuery);
    console.log('Survey ID:', survey_id);

    const surveyResult = await projectConnection.query(surveyQuery, {
      bind: [parseInt(survey_id)],
      type: projectConnection.QueryTypes.SELECT
    });

    if (!surveyResult || surveyResult.length === 0) {
      console.log('❌ Survey not found:', survey_id);
      return errorResponse(res, 'Survey not found', 404);
    }

    const survey = surveyResult[0];
    console.log(`✅ Found survey: ${survey.id}`);

    return successResponse(res, 200, 'Survey retrieved successfully', survey);
  } catch (error) {
    console.error('Error getting survey by ID:', error);
    return errorResponse(res, 'Error retrieving survey', 500, error.message);
  }
}

export async function getSpeciesList(req, res) {
  try {
    const { treelogy_id } = req.params;
    console.log(`📊 Fetching species list for project: ${treelogy_id}`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Get species list from trees table
    const speciesResult = await projectConnection.query(`
      SELECT DISTINCT 
        local_name,
        common_name,
        botanical_name
      FROM trees 
      WHERE local_name IS NOT NULL AND local_name != ''
      ORDER BY local_name
    `);

    const species = speciesResult[0] || [];
    console.log(`✅ Found ${species.length} species`);

    return successResponse(res, 200, 'Species list retrieved successfully', species);
  } catch (error) {
    console.error('Error getting species list:', error);
    return errorResponse(res, 'Error retrieving species list', 500, error.message);
  }
}

export async function getWardsList(req, res) {
  try {
    const { treelogy_id } = req.params;
    console.log(`📊 Fetching wards list for project: ${treelogy_id}`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Check if ward_name column exists
    const columnCheck = await projectConnection.query(`
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = 'treedetailsentered' 
      AND column_name = 'ward_name'
    `, {
      type: projectConnection.QueryTypes.SELECT
    });

    if (!columnCheck || columnCheck.length === 0) {
      console.log('⚠️ Ward column does not exist in this project');
      return successResponse(res, 200, 'Wards list retrieved successfully (no ward column)', []);
    }

    // Get distinct wards from treedetailsentered table
    const wardsResult = await projectConnection.query(`
      SELECT DISTINCT ward_name
      FROM treedetailsentered 
      WHERE ward_name IS NOT NULL AND ward_name != ''
      ORDER BY ward_name
    `, {
      type: projectConnection.QueryTypes.SELECT
    });

    const wards = wardsResult || [];
    console.log(`✅ Found ${wards.length} wards`);

    return successResponse(res, 200, 'Wards list retrieved successfully', wards);
  } catch (error) {
    console.error('Error getting wards list:', error);
    return errorResponse(res, 'Error retrieving wards list', 500, error.message);
  }
}

export async function getProjectUsers(req, res) {
  try {
    const { treelogy_id } = req.params;

    console.log(`📊 Fetching project users for: ${treelogy_id}`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    const projectConnection = await getProjectConnection(project.dbname);
    console.log(`✅ Connected to project database: ${project.dbname}`);

    // Get all unique users from treedetailsentered table
    const usersResult = await projectConnection.query(`
      SELECT DISTINCT user_id, COUNT(*) as survey_count
      FROM treedetailsentered 
      WHERE user_id IS NOT NULL
      GROUP BY user_id
      ORDER BY user_id
    `, { type: projectConnection.QueryTypes.SELECT });

    const users = usersResult || [];
    console.log(`✅ Found ${users.length} project users`);

    return successResponse(res, 200, 'Project users retrieved successfully', users);
  } catch (error) {
    console.error('Error getting project users:', error);
    return errorResponse(res, 500, 'Error fetching project users', error.message);
  }
}

export async function getTreeList(req, res) {
  try {
    const { treelogy_id } = req.params;
    const authHeader = req.headers['authorization'];
    if (!authHeader) {
      return res.status(401).json({ error: 'Authorization header missing' });
    }

    // Split and get token part only
    const token = authHeader.split(' ')[1];
    console.log("token:", token);

    const tokenDecoded = jwt.decode(token);
    console.log("tokenDecoded:", tokenDecoded);




    console.log(`📊 Fetching tree list for: ${treelogy_id}`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    const projectConnection = await getProjectConnection(project.dbname);
    console.log(`✅ Connected to project database: ${project.dbname}`);

    // Get all trees from treelist master table
    const treesResult = await projectConnection.query(`
      SELECT 
         tree_id,
        tree_name, 
        comman_name, 
        botanical,
        family, 
        phenology, 
        economic, 
        flowering
      FROM treelist 
      ORDER BY tree_name
    `, { type: projectConnection.QueryTypes.SELECT });

    const trees = treesResult || [];
    console.log(`✅ Found ${trees.length} trees in master list`);

    return successResponse(res, 200, 'Tree list retrieved successfully', trees);
  } catch (error) {
    console.error('Error getting tree list:', error);
    return errorResponse(res, 500, 'Error fetching tree list', error.message);
  }
}

export async function submitQCReview(req, res) {
  try {
    const { treelogy_id, survey_id } = req.params;
    req.headers
    const qcData = req.body;
    console.log(`📊 Submitting QC review for survey: ${survey_id} in project: ${treelogy_id}`);
    console.log('QC Data received:', qcData);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Validate qc_done status
    // 0 = unverified, 1 = level 1 approved, 2 = level 2 hold, 3 = verified, 4 = rejected
    const validStatuses = [0, 1, 2, 3, 4];
    const qcStatus = parseInt(qcData.qc_done);
    if (!validStatuses.includes(qcStatus)) {
      return errorResponse(res, 'Invalid QC status. Must be 0-4', 400);
    }

    // Validate survey_id
    const surveyId = parseInt(survey_id);
    if (!surveyId || isNaN(surveyId)) {
      return errorResponse(res, 'Invalid survey ID', 400);
    }

    console.log('🔍 Updating survey with ID:', surveyId);

    // Update the survey with QC data - matching addTree API column names exactly
    const updateResult = await projectConnection.query(`
      UPDATE treedetailsentered 
      SET 
        qc_done = $1,
        latitude = $2,
        longitude = $3,
        grith_cm = $4,
        height_mtr = $5,
        canopy_diameter = $6,
        condition_id = $7,
        remarks_id = $8,
        dist_from_prev_tree = $9,
        ownership_id = $10,
        special_comment_id = $11,
        bird_nest = $12,
        bla_imbal_id = $13,
        roadname = $14,
        tree_id = $15,
        societyname = $16,
        googleAddress = $17,
        updated_by=$18,
        is_rejected=$19,
        rejected_at=$20,
        rejected_by=$21
      WHERE tree_details_id = $22
    `, {
      bind: [
        qcStatus, // $1 - qc_done
        parseFloat(qcData.latitude) || null, // $2 - latitude
        parseFloat(qcData.longitude) || null, // $3 - longitude
        parseFloat(qcData.grith_cm) || null, // $4 - grith_cm
        parseInt(qcData.height_mtr) || null, // $5 - height_mtr
        parseFloat(qcData.canopy_diameter) || null, // $6 - canopy_diameter
        qcData.condition_id || null, // $7 - condition_id
        qcData.remarks_id || null, // $8 - remarks_id
        parseFloat(qcData.dist_from_prev_tree) || null, // $9 - dist_from_prev_tree
        qcData.ownership_id || null, // $10 - ownership_id
        qcData.special_comment_id || null, // $11 - special_comment_id
        qcData.bird_nest || null, // $12 - bird_nest
        qcData.balImbalance_id || null, // $13 - bla_imbal_id
        qcData.roadname || null, // $14 - roadname
        parseInt(qcData.tree_id) || null, // $15 - tree_id
        qcData.societyname || null, // $16 - societyname
        qcData.googleaddress || 0, // $17 - googleAddress
        qcData.updated_by || null,//18
        qcData.is_rejected || false,//19
        qcData.rejected_at || null,//20
        qcData.rejected_by || null,//21
        surveyId // $22 - WHERE clause
      ],
      type: projectConnection.QueryTypes.UPDATE
    });

    console.log('✅ QC review submitted successfully');

    // If QC status is verified (3), also add to trees table
    if (qcStatus === 3) {
      console.log('🌳 Survey verified - adding to trees table');

      try {
        // Get the survey data to insert into trees table
        const surveyData = await projectConnection.query(`
          SELECT 
            t.tree_details_id,
            t.latitude,
            t.longitude,
            t.treename as local_name,
            t.other_name,
            t.grith_cm as girth_cm,
            t.height_mtr as height_m,
            t.canopy_diameter as canopy_dia_m,
            t.condition_id,
            t.other_remarks,
            t.dist_from_prev_tree as dist_frm_p,
            t.ownership_name as ownership,
            t.societyname as society_name,
            t.roadname as road_name,
            t.bird_nest,
            t.entered_date,
            t.entered_time,
            t.allocated_ward as ward_name,
            t.age,
            t.date_of_plantation,
            t.ownership_contact_no,
            t.client_name,
            t.contractor_name,
            tl.tree_name as botanical_name,
            tl.comman_name as common_name,
            tl.phenology,
            tl.economic as economic_i,
            tl.family as family_g,
            tl.color as colour_of,
            tl.flowering,
            tl.is_rare,
            t.qc_updated_date as qc_entered_date,
            t.qc_updated_time as qc_entered_time
          FROM treedetailsentered t
          LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
          WHERE t.tree_details_id = $1
        `, {
          bind: [surveyId],
          type: projectConnection.QueryTypes.SELECT
        });

        if (surveyData && surveyData.length > 0) {
          const survey = surveyData[0];

          // Insert into trees table
          const insertResult = await projectConnection.query(`
            INSERT INTO trees (
              oid,
              sr_no,
              gps_no,
              user_name,
              local_name,
              other_name,
              girth_cm,
              height_m,
              canopy_dia_m,
              condition,
              other_remarks,
              dist_frm_p,
              ownership,
              society_name,
              road_name,
              bird_nest,
              date_time,
              feat_name,
              ward_name,
              qc_comment,
              botanical_name,
              common_name,
              saar_uid,
              phenology,
              economic_i,
              family_g,
              colour_of,
              flowering,
              is_rare,
              age,
              entered_date,
              entered_time,
              is_plantation,
              geom
            ) 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, ST_SetSRID(ST_MakePoint($34, $35), 32643)
            )
          `, {
            bind: [
              survey.tree_details_id, // oid - $1
              survey.tree_details_id.toString(), // sr_no - $2
              survey.tree_details_id.toString(), // gps_no - $3
              'QC_VERIFIED', // user_name - $4
              survey.local_name, // local_name - $5
              survey.other_name, // other_name - $6
              survey.girth_cm, // girth_cm - $7
              survey.height_m, // height_m - $8
              survey.canopy_dia_m, // canopy_dia_m - $9
              survey.condition_id?.toString() || null, // condition - $10
              survey.other_remarks, // other_remarks - $11
              survey.dist_frm_p, // dist_frm_p - $12
              survey.ownership, // ownership - $13
              survey.society_name, // society_name - $14
              survey.road_name, // road_name - $15
              survey.bird_nest === 1, // bird_nest - $16
              new Date().toISOString(), // date_time - $17
              'QC_VERIFIED_TREE', // feat_name - $18
              survey.ward_name, // ward_name - $19
              'QC Verified Survey', // qc_comment - $20
              survey.botanical_name, // botanical_name - $21
              survey.common_name, // common_name - $22
              survey.tree_details_id.toString(), // saar_uid - $23
              survey.phenology, // phenology - $24
              survey.economic_i, // economic_i - $25
              survey.family_g, // family_g - $26
              survey.colour_of, // colour_of - $27
              survey.flowering, // flowering - $28
              survey.is_rare === 1, // is_rare - $29
              survey.age, // age - $30
              survey.qc_entered_date, // entered_date - $31
              survey.qc_entered_time, // entered_time - $32
              survey.date_of_plantation ? 1 : 0, // is_plantation - $33
              survey.longitude, // longitude for geom - $34
              survey.latitude  // latitude for geom - $35
            ],
            type: projectConnection.QueryTypes.INSERT
          });

          console.log('✅ Survey added to trees table successfully');
        } else {
          console.log('⚠️ Survey data not found for trees table insertion');
        }
      } catch (treesError) {
        console.error('❌ Error adding survey to trees table:', treesError);
        // Don't fail the QC submission if trees table insertion fails
        console.log('⚠️ QC submission succeeded but trees table insertion failed');
      }
    }

    return successResponse(res, 200, 'QC review submitted successfully', {
      survey_id,
      updated: true,
      qc_status: qcStatus,
      added_to_trees: qcStatus === 3
    });
  } catch (error) {
    console.error('Error submitting QC review:', error);
    return errorResponse(res, 'Error submitting QC review', 500, error.message);
  }
}

// Survey Inventory API - Get surveys with tab-based filtering
export async function getSurveyInventory(req, res) {
  try {
    const { treelogy_id } = req.params;
    const {
      page = 1,
      limit = 50,
      search = '',
      tab = 'all', // 'all', 'approved', 'pending', 'rejected'
      ward = '',
      survey_id = '',
      user_id = '',
      date = ''
    } = req.query;

    console.log(`📊 Fetching survey inventory for project: ${treelogy_id}`);
    console.log(`   Filters - Page: ${page}, Limit: ${limit}, Search: "${search}", Tab: "${tab}", Ward: "${ward}", Survey ID: "${survey_id}", User ID: "${user_id}", Date: "${date}"`);

    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.log('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.project_name);
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    // Calculate offset
    const offset = (parseInt(page) - 1) * parseInt(limit);

    // Check if ward_name column exists
    let hasWardColumn = false;
    try {
      const columnCheck = await projectConnection.query(`
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'treedetailsentered' 
        AND column_name = 'ward_name'
      `, {
        type: projectConnection.QueryTypes.SELECT
      });

      hasWardColumn = columnCheck && columnCheck.length > 0;
      console.log(`Ward column exists: ${hasWardColumn}`);
    } catch (err) {
      console.log('Ward column check failed:', err.message);
    }

    // Build WHERE clause with tab-based filtering
    let whereConditions = [];
    let bindParams = [];
    let paramIndex = 1;

    // Tab-based QC status filtering
    if (tab === 'approved') {
      // Approved: qc_done = 3
      whereConditions.push(`t.qc_done = $${paramIndex}`);
      bindParams.push(3);
      paramIndex++;
    } else if (tab === 'pending') {
      // QC Pending: qc_done = 1 or 2
      whereConditions.push(`(t.qc_done = $${paramIndex} OR t.qc_done = $${paramIndex + 1})`);
      bindParams.push(1, 2);
      paramIndex += 2;
    } else if (tab === 'rejected') {
      // Rejected: qc_done = 4
      whereConditions.push(`t.qc_done = $${paramIndex}`);
      bindParams.push(4);
      paramIndex++;
    }
    // 'all' tab shows all surveys regardless of QC status

    // Survey ID filter (exact match)
    if (survey_id && survey_id.trim() !== '') {
      whereConditions.push(`t.tree_details_id = $${paramIndex}`);
      bindParams.push(parseInt(survey_id));
      paramIndex++;
    }

    // Ward filter (only if column exists)
    if (hasWardColumn && ward && ward.trim() !== '' && ward !== 'all') {
      whereConditions.push(`t.ward_name ILIKE $${paramIndex}`);
      bindParams.push(`%${ward}%`);
      paramIndex++;
    }

    // User ID filter
    if (user_id && user_id.trim() !== '') {
      whereConditions.push(`t.user_id = $${paramIndex}`);
      bindParams.push(parseInt(user_id));
      paramIndex++;
    }

    // Date filter
    if (date && date.trim() !== '') {
      whereConditions.push(`DATE(t.entered_date) = $${paramIndex}`);
      bindParams.push(date);
      paramIndex++;
    }

    // General search (tree name, other name, address, tree_details_id, master tree names)
    if (search && search.trim() !== '') {
      whereConditions.push(`(t.treename ILIKE $${paramIndex} OR t.other_name ILIKE $${paramIndex} OR t.googleaddress ILIKE $${paramIndex} OR t.tree_details_id::text ILIKE $${paramIndex} OR tl.tree_name ILIKE $${paramIndex} OR tl.comman_name ILIKE $${paramIndex})`);
      bindParams.push(`%${search}%`);
      paramIndex++;
    }

    const whereClause = whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : '';

    // Get total count first
    const countQuery = `SELECT COUNT(*) as total FROM treedetailsentered t LEFT JOIN treelist tl ON t.tree_id = tl.tree_id ${whereClause}`;
    console.log('Count Query:', countQuery);
    console.log('Bind Params:', bindParams);

    const countResult = await projectConnection.query(countQuery, {
      bind: bindParams,
      type: projectConnection.QueryTypes.SELECT
    });
    const totalCount = parseInt(countResult[0]?.total || 0);

    // Build SELECT clause with ward column if it exists
    const wardColumn = hasWardColumn ? 'ward_name as ward,' : '';

    // Get paginated surveys with tree master data
    const surveysQuery = `
      SELECT 
        t.tree_details_id as id,
        t.user_id,
        t.tree_id,
        t.entered_date as timestamp,
        t.latitude,
        t.longitude,
        t.treename as local_name,
        t.other_name,
        t.grith_cm as girth,
        t.height_mtr as height,
        t.canopy_diameter as canopy,
        t.other_remarks as remarks,
        t.ownership_name as ownership,
        t.googleaddress as address,
        ${wardColumn}
        t.qc_done,
        t.qc_status,
        t.condition_id,
        t.ownership_id,
        t.societyname,
        t.roadname,
        t.entered_time,
        t.updated_date,
        t.qc_updated_time,
        t.updated_by,
        t.qc_final_submitted,
        t.qc_updated_date,
        t.version2,
        t.user_seq_id,
        t.old_latitude,
        t.old_longitude,
        t.drag_distance,
        t.qc_old_latitude,
        t.qc_old_longitude,
        t.qc_drag_distance,
        t.allocated_ward,
        t.within_boundary,
        t.new_or_old,
        t.grid,
        t.lat2,
        t.lon2,
        t.dist,
        t.bla_imbal_id,
        t.remarks_id,
        t.dist_from_prev_tree,
        t.special_comment_id,
        t.bird_nest,
        t.resurvey_done,
        t.un_submit_date,
        t.un_submit_time,
        t.un_updated_by,
        t.comment_un,
        t.bo_qc_submit_date,
        t.bo_qc_submit_time,
        t.bo_qc_done_by,
        t.comment_bo_qc,
        t.bo_rare_qc_date,
        t.bo_rare_qc_time,
        t.bo_rare_qc_doneby,
        t.bo_rare_qc_comment,
        t.age,
        t.date_of_plantation,
        t.client_name,
        t.contractor_name,
        t.ownership_contact_no,
        t.manual_tree_details_id,
        tl.tree_name,
        tl.comman_name,
        tl.family,
        tl.phenology,
        tl.economic,
        tl.flowering,
        tl.color,
        tl.botanical,
        tl.is_rare,
        tl.priority,
        tl.wood_density,
        tl.growth_factor,
        tl.gujarati_name
      FROM treedetailsentered t
      LEFT JOIN treelist tl ON t.tree_id = tl.tree_id
      ${whereClause}
      ORDER BY t.entered_date DESC
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    const surveyBindParams = [...bindParams, parseInt(limit), offset];
    console.log('Surveys Query:', surveysQuery);
    console.log('Survey Bind Params:', surveyBindParams);

    const surveys = await projectConnection.query(surveysQuery, {
      bind: surveyBindParams,
      type: projectConnection.QueryTypes.SELECT
    });

    const surveyData = surveys || [];
    const totalPages = Math.ceil(totalCount / parseInt(limit));

    console.log(`✅ Found ${surveyData.length} surveys (Page ${page}/${totalPages}, Total: ${totalCount})`);

    return successResponse(res, 200, 'Survey inventory retrieved successfully', {
      surveys: surveyData,
      pagination: {
        currentPage: parseInt(page),
        limit: parseInt(limit),
        totalItems: totalCount,
        totalPages,
        hasNextPage: parseInt(page) < totalPages,
        hasPreviousPage: parseInt(page) > 1
      },
      tab: tab
    });
  } catch (error) {
    console.error('Error getting survey inventory:', error);
    return errorResponse(res, 'Error retrieving survey inventory', 500, error.message);
  }
}

// Get detailed data for dashboard cards
export async function getCardDetails(req, res) {
  try {
    const { treelogy_id, card_id } = req.params;
    const { page = 1, limit = 100 } = req.query;

    console.log(`🔍 Fetching card details for project: ${treelogy_id}, card: ${card_id}`);

    // Get project details
    const project = await Treelogy.findByPk(treelogy_id);
    if (!project) {
      console.error('❌ Project not found:', treelogy_id);
      return errorResponse(res, 'Project not found', 404);
    }

    console.log('✅ Project found:', project.treelogy_name);

    // Get project database connection
    const projectConnection = await getProjectConnection(project.dbname);
    console.log('✅ Database connection established');

    let data = [];
    let totalCount = 0;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    // Build query based on card type using raw SQL
    let query = '';
    let countQuery = ''; // For calculating total count

    switch (card_id) {
      case 'total_trees':
        // Use the exact same approach as the working dashboard cards API
        query = `select id, tree_details_id, treename from treedetailsentered limit ${parseInt(limit)} offset ${offset}`;
        break;

      case 'total_verified':
      case 'qc_completed':
        query = `
          SELECT 
            id,
            tree_details_id as tree_id,
            treename as local_name,
            other_name,
            user_id,
            entered_date as timestamp,
            qc_done,
            tree_girth,
            tree_height,
            address,
            ward
          FROM treedetailsentered
          WHERE qc_done = 3
          ORDER BY entered_date DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'qc_pending':
        query = `
          SELECT 
            id,
            tree_details_id as tree_id,
            treename as local_name,
            other_name,
            user_id,
            entered_date as timestamp,
            qc_done,
            tree_girth,
            tree_height,
            address,
            ward
          FROM treedetailsentered
          WHERE qc_done IN (1, 2)
          ORDER BY entered_date DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'total_species':
        query = `
          SELECT 
            local_name,
            COUNT(*) as count,
            ROUND((COUNT(*) * 100.0 / (SELECT COUNT(*) FROM treedetailsentered)), 2) as percentage
          FROM treedetailsentered 
          WHERE local_name IS NOT NULL AND local_name != ''
          GROUP BY local_name 
          ORDER BY count DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'avg_girth':
      case 'avg_height':
      case 'large_trees':
        query = `
          SELECT 
            id,
            tree_details_id as tree_id,
            treename as local_name,
            other_name,
            user_id,
            entered_date as timestamp,
            qc_done,
            tree_girth,
            tree_height,
            address,
            ward
          FROM treedetailsentered
          ORDER BY entered_date DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'today_entries':
        countQuery = `SELECT COUNT(*) as total FROM treedetailsentered WHERE DATE(entered_date) = CURRENT_DATE`;
        query = `
          SELECT 
    tree_details_id AS id,
    tree_details_id AS tree_id,
    treename AS local_name,
    other_name,
    user_id,
    entered_date AS timestamp,
    qc_done,
    grith_cm AS tree_girth,
    height_mtr AS tree_height,
    googleaddress AS address
FROM treedetailsentered
WHERE DATE(entered_date) = CURRENT_DATE
ORDER BY entered_date DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'this_week_entries':
        countQuery = `SELECT COUNT(*) as total FROM treedetailsentered WHERE date_trunc('week', entered_date) = date_trunc('week', CURRENT_DATE)`;
        query = `
           SELECT 
    tree_details_id AS id,
    tree_details_id AS tree_id,
    treename AS local_name,
    other_name,
    user_id,
    entered_date AS timestamp,
    qc_done,
    grith_cm AS tree_girth,
    height_mtr AS tree_height,
    googleaddress AS address
FROM treedetailsentered
WHERE date_trunc('week', entered_date) = date_trunc('week', CURRENT_DATE)
ORDER BY entered_date DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'this_month_entries':
        countQuery = `SELECT COUNT(*) as total FROM treedetailsentered WHERE date_trunc('month', entered_date) = date_trunc('month', CURRENT_DATE)`;
        query = `
          SELECT 
    tree_details_id AS id,
    tree_details_id AS tree_id,
    treename AS local_name,
    other_name,
    user_id,
    entered_date AS timestamp,
    qc_done,
    grith_cm AS tree_girth,
    height_mtr AS tree_height,
    googleaddress AS address
FROM treedetailsentered
WHERE date_trunc('month', entered_date) = date_trunc('month', CURRENT_DATE)
ORDER BY entered_date DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'wards_covered':
        query = `
          SELECT 
            ward,
            COUNT(*) as tree_count,
            COUNT(DISTINCT treename) as species_count,
            MAX(entered_date) as last_survey_date
          FROM treedetailsentered 
          WHERE ward IS NOT NULL AND ward != ''
          GROUP BY ward 
          ORDER BY tree_count DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      case 'roads_covered':
        query = `
          SELECT 
            address,
            COUNT(*) as tree_count,
            COUNT(DISTINCT treename) as species_count,
            MAX(entered_date) as last_survey_date
          FROM treedetailsentered 
          WHERE address IS NOT NULL AND address != ''
          GROUP BY address 
          ORDER BY tree_count DESC
          LIMIT ${parseInt(limit)} OFFSET ${offset}
        `;
        break;

      default:
        console.error('❌ Invalid card ID:', card_id);
        return errorResponse(res, 'Invalid card ID', 400);
    }

    if (query) {
      console.log(`🔍 Executing query for ${card_id}:`, query);

      try {
        // Execute count query first if it exists (for date-based cards)
        if (countQuery) {
          console.log(`🔍 Executing count query for ${card_id}:`, countQuery);
          const countResult = await projectConnection.query(countQuery);
          // Access result using same pattern as dashboard cards: result[0][0].field
          totalCount = parseInt(countResult[0]?.[0]?.total || countResult[0]?.total || 0);
          console.log(`✅ Total count for ${card_id}:`, totalCount);
        }

        // Execute query - use QueryTypes.SELECT for date-based cards (like inventory API)
        // This returns data directly as an array, not nested
        let resultData = [];
        if (card_id === 'today_entries' || card_id === 'this_week_entries' || card_id === 'this_month_entries') {
          try {
            // Use QueryTypes.SELECT to get results directly as array (like inventory API)
            const result = await projectConnection.query(query, {
              type: projectConnection.QueryTypes.SELECT
            });
            resultData = Array.isArray(result) ? result : [];
            console.log(`✅ QueryTypes.SELECT result: ${resultData.length} rows`);

            // If still empty, try without QueryTypes.SELECT (fallback)
            if (resultData.length === 0) {
              console.log(`⚠️ QueryTypes.SELECT returned empty, trying direct query...`);
              const fallbackResult = await projectConnection.query(query);
              console.log(`🔍 Fallback result type:`, Array.isArray(fallbackResult) ? 'Array' : typeof fallbackResult);
              console.log(`🔍 Fallback result[0] type:`, Array.isArray(fallbackResult?.[0]) ? 'Array' : typeof fallbackResult?.[0]);
              if (fallbackResult && Array.isArray(fallbackResult) && fallbackResult.length > 0) {
                if (Array.isArray(fallbackResult[0])) {
                  resultData = fallbackResult[0];
                  console.log(`✅ Using fallback result[0]: ${resultData.length} rows`);
                } else if (fallbackResult[0] && typeof fallbackResult[0] === 'object') {
                  resultData = Array.isArray(fallbackResult) ? fallbackResult : [fallbackResult[0]];
                  console.log(`✅ Using fallback result: ${resultData.length} rows`);
                }
              }
            }
          } catch (queryErr) {
            console.error(`❌ Query execution error for ${card_id}:`, queryErr);
            throw queryErr;
          }
        } else {
          // Use direct query for other cards (like dashboard cards API)
          const result = await projectConnection.query(query);
          // Handle nested structure for non-SELECT queries
          if (result && Array.isArray(result) && result.length > 0) {
            if (Array.isArray(result[0])) {
              resultData = result[0];
            } else if (result[0] && (result[0].id !== undefined || result[0].tree_id !== undefined)) {
              resultData = [result[0]];
            }
          }
        }

        console.log(`🔍 Final resultData length: ${resultData.length}`);
        if (resultData.length > 0) {
          console.log(`🔍 Sample row keys:`, Object.keys(resultData[0]));
        }

        // Map the data based on card type
        if (card_id === 'total_trees') {
          // For total_trees, map the simplified query result
          data = (Array.isArray(resultData) ? resultData : []).map(row => ({
            id: row.id,
            tree_id: row.tree_details_id,
            local_name: row.treename,
            other_name: null,
            user_id: null,
            timestamp: null,
            qc_done: null,
            tree_girth: null,
            tree_height: null,
            address: null,
            ward: null
          }));
        } else if (card_id === 'total_species') {
          data = (Array.isArray(resultData) ? resultData : []).map(row => ({
            local_name: row.local_name,
            count: parseInt(row.count),
            percentage: parseFloat(row.percentage)
          }));
        } else if (card_id === 'wards_covered') {
          data = (Array.isArray(resultData) ? resultData : []).map(row => ({
            ward_name: row.ward,
            tree_count: parseInt(row.tree_count),
            species_count: parseInt(row.species_count),
            last_survey_date: row.last_survey_date
          }));
        } else if (card_id === 'roads_covered') {
          data = (Array.isArray(resultData) ? resultData : []).map(row => ({
            road_name: row.address,
            tree_count: parseInt(row.tree_count),
            species_count: parseInt(row.species_count),
            last_survey_date: row.last_survey_date
          }));
        } else {
          // For regular survey data (including date-based cards)
          data = (Array.isArray(resultData) ? resultData : []).map(row => ({
            id: row.id,
            tree_id: row.tree_id,
            local_name: row.local_name,
            other_name: row.other_name,
            user_id: row.user_id,
            timestamp: row.timestamp,
            qc_done: row.qc_done,
            tree_girth: row.tree_girth,
            tree_height: row.tree_height,
            address: row.address,
            ward: row.ward
          }));
        }

        console.log(`✅ Data fetched for ${card_id}: ${data.length} records`);
      } catch (queryError) {
        console.error('❌ Error executing query:', queryError);
        data = [];
      }
    }

    console.log(`✅ Returning data for card ${card_id}: ${data.length} records`);

    // Use totalCount if calculated (for date-based cards), otherwise use data.length
    const finalTotal = totalCount > 0 ? totalCount : data.length;

    return successResponse(res, 200, 'Card details retrieved successfully', {
      card_id,
      data,
      total: finalTotal
    });

  } catch (error) {
    console.error('❌ Error fetching card details:', error);
    return errorResponse(res, 'Failed to fetch card details', 500, error.message);
  }
}
