import { getProjectConnection } 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';

// Get survey data for map display
export async function getMapData(req, res) {
  try {
    const { treelogy_id } = req.params;
    const { 
      verified_only = false,
      species_type,
      tree_condition,
      is_heritage,
      is_rare,
      is_indigenous,
      is_exotic
    } = req.query;

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

    // 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 (is_heritage !== undefined) {
      where.is_heritage = is_heritage === 'true';
    }
    
    if (is_rare !== undefined) {
      where.is_rare = is_rare === 'true';
    }
    
    if (is_indigenous !== undefined) {
      where.is_indigenous = is_indigenous === 'true';
    }
    
    if (is_exotic !== undefined) {
      where.is_exotic = is_exotic === 'true';
    }

    // Get surveys for map display
    const surveys = await Survey.findAll({
      where,
      attributes: [
        'survey_id',
        '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',
        'is_rare',
        'is_indigenous',
        'is_exotic',
        'host_plants',
        'images',
        'survey_date',
        'verified',
        'verified_at'
      ],
      order: [['survey_date', 'DESC']]
    });

    // Format data for map display
    const mapData = surveys.map(survey => ({
      id: survey.survey_id,
      name: survey.tree_name,
      localName: survey.local_name,
      otherName: survey.other_name,
      position: {
        lat: parseFloat(survey.latitude),
        lng: parseFloat(survey.longitude)
      },
      details: {
        height: survey.tree_height,
        girth: survey.tree_girth,
        age: survey.tree_age,
        condition: survey.tree_condition,
        ownership: survey.tree_ownership,
        economicImportance: survey.economic_importance,
        remarks: survey.remarks,
        speciesType: survey.species_type,
        isHeritage: survey.is_heritage,
        isRare: survey.is_rare,
        isIndigenous: survey.is_indigenous,
        isExotic: survey.is_exotic,
        hostPlants: survey.host_plants,
        images: survey.images,
        surveyDate: survey.survey_date,
        verified: survey.verified,
        verifiedAt: survey.verified_at
      }
    }));

    return successResponse(res, 200, 'Map data retrieved successfully', {
      project: {
        id: project.treelogy_id,
        name: project.treelogy_name,
        landmark: project.landmark
      },
      surveys: mapData,
      total: mapData.length
    });
  } catch (error) {
    console.error('Error getting map data:', error);
    return errorResponse(res, 500, 'Error retrieving map data', error.message);
  }
}

// Get survey statistics for dashboard cards
export async function getDashboardStats(req, res) {
  try {
    const { treelogy_id } = req.params;

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

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

    // Get statistics
    const [
      totalTrees,
      verifiedTrees,
      speciesCount,
      heritageTrees,
      rareTrees,
      indigenousTrees,
      exoticTrees,
      mostDominantSpecies
    ] = 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.findOne({
        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']],
        raw: true
      })
    ]);

    // Calculate averages
    const avgHeight = await Survey.findOne({
      attributes: [
        [projectConnection.fn('AVG', projectConnection.col('tree_height')), 'avg_height']
      ],
      where: { 
        status: 'ACTIVE',
        tree_height: { [Op.ne]: null }
      },
      raw: true
    });

    const avgGirth = await Survey.findOne({
      attributes: [
        [projectConnection.fn('AVG', projectConnection.col('tree_girth')), 'avg_girth']
      ],
      where: { 
        status: 'ACTIVE',
        tree_girth: { [Op.ne]: null }
      },
      raw: true
    });

    const avgAge = await Survey.findOne({
      attributes: [
        [projectConnection.fn('AVG', projectConnection.col('tree_age')), 'avg_age']
      ],
      where: { 
        status: 'ACTIVE',
        tree_age: { [Op.ne]: null }
      },
      raw: true
    });

    return successResponse(res, 200, 'Dashboard statistics retrieved successfully', {
      totalTrees,
      verifiedTrees,
      speciesCount,
      heritageTrees,
      rareTrees,
      indigenousTrees,
      exoticTrees,
      mostDominantSpecies: mostDominantSpecies?.species_type || 'N/A',
      avgHeight: avgHeight?.avg_height ? parseFloat(avgHeight.avg_height).toFixed(2) : 0,
      avgGirth: avgGirth?.avg_girth ? parseFloat(avgGirth.avg_girth).toFixed(2) : 0,
      avgAge: avgAge?.avg_age ? parseFloat(avgAge.avg_age).toFixed(1) : 0
    });
  } catch (error) {
    console.error('Error getting dashboard statistics:', error);
    return errorResponse(res, 500, 'Error retrieving dashboard statistics', error.message);
  }
}

// Get chart data for dashboard
export async function getChartData(req, res) {
  try {
    const { treelogy_id } = req.params;
    const { chart_type } = req.query;

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

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

    let chartData = {};

    switch (chart_type) {
      case 'condition':
        chartData = await Survey.findAll({
          attributes: [
            'tree_condition',
            [projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'count']
          ],
          where: { status: 'ACTIVE' },
          group: ['tree_condition'],
          raw: true
        });
        break;

      case 'ownership':
        chartData = await Survey.findAll({
          attributes: [
            'tree_ownership',
            [projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'count']
          ],
          where: { status: 'ACTIVE' },
          group: ['tree_ownership'],
          raw: true
        });
        break;

      case 'species':
        chartData = await 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
        });
        break;

      case 'economic_importance':
        chartData = await Survey.findAll({
          attributes: [
            'economic_importance',
            [projectConnection.fn('COUNT', projectConnection.col('survey_id')), 'count']
          ],
          where: { status: 'ACTIVE' },
          group: ['economic_importance'],
          raw: true
        });
        break;

      default:
        return errorResponse(res, 400, 'Invalid chart type');
    }

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