import getSubDbConnection from '../config/subDb.js'; // Ensure this is working
import QueryStream from 'pg-query-stream'; // Import the pg-query-stream module
import path from 'path';
import fs from 'fs';
import { fileURLToPath } from 'url';
import Treelogy from '../models/treelogy.js';
import sequelize from '../config/config.js'; // Ensure this is working


const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const schemaPath = path.join(__dirname, '../config/schema.sql');



import { Sequelize } from 'sequelize';
import { Client } from 'pg';


import { Pool } from 'pg'; // Ensure you're using pg's Pool for DB connection


function filterSchema(schemaContent) {
  // Add more patterns if needed
  const unsupportedPatterns = [
    /^SET /i, // any line starting with SET
    /transaction_timeout/i, // specific line containing transaction_timeout
    /search_path/i, // optional: Postgres search_path lines
  ];

  return schemaContent
    .split('\n')
    .filter(line => !unsupportedPatterns.some(pattern => pattern.test(line)))
    .join('\n');
}



export const getAllTreeLocations = async (req, res) => {
  try {
    const { dbName } = req.body; // Ensure the correct DB name is passed in the body
    const subDb = getSubDbConnection(dbName); // Get the DB connection pool

    const query = 'SELECT tree_details_id, latitude, longitude FROM treedetailsentered';

    const queryStream = new QueryStream(query); // Wrap your query into a stream
    const client = await subDb.connect(); // Connect to the DB

    const stream = client.query(queryStream); // Start the query as a stream

    res.setHeader('Content-Type', 'application/json');
    res.write('['); // Start the JSON array

    let isFirstRow = true;

    // Handle incoming data from the stream
    stream.on('data', (row) => {
      if (!isFirstRow) {
        res.write(','); // Add a comma between rows
      }
      isFirstRow = false;

      // Write the current row as JSON to the response
      res.write(JSON.stringify(row));
    });

    // Handle when the stream ends
    stream.on('end', () => {
      res.write(']'); // Close the JSON array
      res.end(); // End the response
      client.release(); // Release the DB client back to the pool
    });

    // Handle any stream errors
    stream.on('error', (err) => {
      console.error('Error in stream:', err);
      // Send the error message and stop the response
      if (!res.headersSent) {
        res.status(500).json({ error: 'Error streaming tree locations' });
      }
      client.release(); // Release the DB client even on error
    });

  } catch (err) {
    console.error('Error fetching tree locations:', err);
    // Prevent headers from being set multiple times
    if (!res.headersSent) {
      res.status(500).json({ error: 'Error fetching tree locations' });
    }
  }
};


export const getAllTreeById = async (req, res) => {
  try {
    const { ids, treelogy_id } = req.body
    const treelogy = await Treelogy.findByPk(treelogy_id)
    const db = treelogy.dataValues.dbname
    console.log("db", db);

    const subDb = getSubDbConnection(db)
    // const result = await subDb.query(`select td.* , tu.tree_name as species from treedetailsentered td join treelist tl on td.tree_id = tl.tree_id   where tree_details_id = ANY($1)`, [ids])
    const result = await subDb.query(
      `select td.*, tl.tree_name as species
   from treedetailsentered td
   join treelist tl on td.tree_id = tl.tree_id
   where td.tree_details_id = ANY($1)`,
      [ids]
    );

    return res.json({ result })

  } catch (error) {
    console.error('Server error:', error);
    res.status(500).json({ success: false, message: 'Server error' });
  }
};




export const getTreeClusters = async (req, res) => {
  try {
    const { dbName, bounds, zoom } = req.body;
    const subDb = getSubDbConnection(dbName);

    // Calculate grid cell size based on zoom level
    // Higher zoom = smaller cells = more detail
    const gridSize = Math.max(0.0001, 0.1 / Math.pow(2, zoom - 10));

    const query = `
      WITH clustered_trees AS (
        SELECT 
          FLOOR(latitude / $1) * $1 + $1/2 as cluster_lat,
          FLOOR(longitude / $1) * $1 + $1/2 as cluster_lng,
          COUNT(*) as tree_count,
          ARRAY_AGG(tree_details_id ORDER BY tree_details_id LIMIT 5) as sample_ids,
          MIN(latitude) as min_lat,
          MAX(latitude) as max_lat,
          MIN(longitude) as min_lng,
          MAX(longitude) as max_lng
        FROM treedetailsentered
        WHERE latitude IS NOT NULL 
          AND longitude IS NOT NULL
          ${bounds ? `
            AND latitude BETWEEN $2 AND $3
            AND longitude BETWEEN $4 AND $5
          ` : ''}
        GROUP BY 
          FLOOR(latitude / $1),
          FLOOR(longitude / $1)
      )
      SELECT 
        cluster_lat as latitude,
        cluster_lng as longitude,
        tree_count,
        sample_ids,
        CASE 
          WHEN tree_count = 1 THEN 'single'
          ELSE 'cluster'
        END as type
      FROM clustered_trees
      ORDER BY tree_count DESC
      LIMIT 50000
    `;

    const params = bounds
      ? [gridSize, bounds.south, bounds.north, bounds.west, bounds.east]
      : [gridSize];

    const client = await subDb.connect();
    const result = await client.query(query, params);

    res.json({
      clusters: result.rows,
      gridSize: gridSize,
      zoom: zoom,
      count: result.rows.length
    });

    client.release();

  } catch (err) {
    console.error('Error fetching tree clusters:', err);
    res.status(500).json({ error: 'Error fetching clusters' });
  }
};

// Get total count and bounds
export const getTreeStats = async (req, res) => {
  try {
    const { dbName } = req.body;
    const subDb = getSubDbConnection(dbName);

    const query = `
      SELECT 
        COUNT(*) as total_trees,
        MIN(latitude) as min_lat,
        MAX(latitude) as max_lat,
        MIN(longitude) as min_lng,
        MAX(longitude) as max_lng,
        AVG(latitude) as center_lat,
        AVG(longitude) as center_lng
      FROM treedetailsentered
      WHERE latitude IS NOT NULL 
        AND longitude IS NOT NULL
    `;

    const client = await subDb.connect();
    const result = await client.query(query);

    res.json(result.rows[0]);

    client.release();

  } catch (err) {
    console.error('Error fetching tree stats:', err);
    res.status(500).json({ error: 'Error fetching stats' });
  }
};


export const getTreesCount = async (req, res) => {
  try {
    const { bbox, zoom, dbName } = req.query;

    if (!bbox || !zoom) {
      return res.status(400).send('bbox and zoom required');
    }

    if (!dbName) {
      return res.status(400).send('dbName is required');
    }

    const [minLng, minLat, maxLng, maxLat] = bbox.split(',').map(Number);

    const gridSize = 10;
    const lngStep = (maxLng - minLng) / gridSize;
    const latStep = (maxLat - minLat) / gridSize;

    const subDb = getSubDbConnection(dbName);

    // ✅ Test the connection first
    try {
      const testClient = await subDb.connect();
      testClient.release();
    } catch (connErr) {
      console.error('Connection test failed:', connErr);
      return res.status(500).json({
        error: 'Database connection failed',
        details: connErr.message,
        dbName: dbName
      });
    }

    const promises = [];

    for (let i = 0; i < gridSize; i++) {
      for (let j = 0; j < gridSize; j++) {
        const cellMinLng = minLng + i * lngStep;
        const cellMinLat = minLat + j * latStep;
        const cellMaxLng = cellMinLng + lngStep;
        const cellMaxLat = cellMinLat + latStep;

        const sql = `
          SELECT COUNT(*)::int as count,
                 ARRAY_AGG(tree_details_id) AS tree_ids,
                 AVG(longitude::DOUBLE PRECISION) AS lon,
                 AVG(latitude::DOUBLE PRECISION) AS lat
          FROM treedetailsentered
          WHERE latitude BETWEEN $1 AND $2
            AND longitude BETWEEN $3 AND $4
        `;

        // ✅ FIXED - Wrap in async function with proper error handling
        const promise = (async () => {
          try {
            const result = await subDb.query(sql, [cellMinLat, cellMaxLat, cellMinLng, cellMaxLng]);
            const row = result.rows[0];
            if (row.count > 0) {
              return {
                count: row.count,
                center: [row.lon, row.lat],
                bbox: [cellMinLng, cellMinLat, cellMaxLng, cellMaxLat],
                tree_ids: row.tree_ids || []
              };
            }
            return null;
          } catch (err) {
            console.error('Query error for cell:', { cellMinLat, cellMaxLat, cellMinLng, cellMaxLng });
            console.error('Error:', err.message);
            throw err; // Re-throw to be caught by Promise.all
          }
        })();

        promises.push(promise);
      }
    }

    const counts = (await Promise.all(promises)).filter(Boolean);
    res.json(counts);

  } catch (err) {
    console.error('Error in getTreesCount:', err);
    res.status(500).json({
      error: 'Error fetching tree counts',
      details: err.message
    });
  }
};





export const getTreesInArea = async (req, res) => {
  try {
    const { bbox, dbName } = req.query; // ✅ Add dbName

    if (!bbox) return res.status(400).send('bbox required');
    if (!dbName) return res.status(400).send('dbName required');

    const [minLng, minLat, maxLng, maxLat] = bbox.split(',').map(Number);

    const subDb = getSubDbConnection(dbName); // ✅ Get the connection

    const sql = `
      SELECT tree_details_id, latitude AS lat, longitude AS lon
      FROM treedetailsentered
      WHERE latitude BETWEEN $2 AND $4
        AND longitude BETWEEN $1 AND $3
      LIMIT 1000
    `;

    const { rows } = await subDb.query(sql, [minLng, minLat, maxLng, maxLat]);
    res.json(rows);
  } catch (err) {
    console.error('Error fetching trees in area:', err);
    res.status(500).json({ error: err.message });
  }
};



export const getNearByTreeLocations = async (req, res) => {
  const { northLat,
    southLat,
    westLon,
    eastLon,
    treelogy_id } = req.body

  // req.header;

  // const user = req.user

  const treelogyData = await Treelogy.findByPk(treelogy_id)
  // console.log(treelogyData.dataValues.dbname)
  const dbName = treelogyData.dataValues.dbname
  getSubDbConnection(dbName)
  const subDb = getSubDbConnection(dbName);

  const query = `
      SELECT * FROM treedetailsentered
      WHERE latitude BETWEEN $1 AND $2
        AND longitude BETWEEN $3 AND $4
    `;

  const values = [southLat, northLat, westLon, eastLon];

  const result = await subDb.query(query, values);
  if (!result || !Array.isArray(result.rows)) {
    return res.status(500).json({ error: 'Unexpected database response.' });
  }

  if (result.rows.length === 0) {
    return res.status(404).json({ message: 'No trees found in the specified area.' });
  }

  res.status(200).json(result.rows);
}


export const addTree = async (req, res) => {
  try {
    const {
      tag, user_id, sq_id, latitude, longitude, othername, grith_cm,
      height_mtr, canopy_dia_mtr, condition_id, remarks_id, dist_frm_prev_tree,
      ownership_id, spatial_comment_id, bird_nest_id, balImbalance_id, roadname,
      tree_id, societyname, entered_date, entered_time, updated_date, googleAddress,
      ward, is_within_boundary, version, dbName, image, image_leaves, image3, image4
    } = req.body;

    console.log(req.body);

    const savedImages = {};
    const __filename = fileURLToPath(import.meta.url);
    const __dirname = path.dirname(__filename);

    const subDb = getSubDbConnection(dbName);  // Connect to the correct DB

    // 🟢 Include the geom column — constructed using longitude & latitude
    const query = `
      INSERT INTO treedetailsentered (
        user_id, latitude, longitude, grith_cm,
        height_mtr, canopy_diameter, condition_id, remarks_id, dist_from_prev_tree,
        ownership_id, special_comment_id, bird_nest, bla_imbal_id, roadname,
        tree_id, societyname, entered_date, entered_time, updated_date, googleAddress,
        geom  -- <=== added column
      )
      VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
        $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
        ST_SetSRID(ST_MakePoint($3, $2), 4326)  -- <=== make point from (lon, lat)
      )
      RETURNING tree_details_id;
    `;

    const values = [
      user_id, latitude, longitude, grith_cm,
      height_mtr, canopy_dia_mtr, condition_id, remarks_id, dist_frm_prev_tree,
      ownership_id, spatial_comment_id, bird_nest_id, balImbalance_id, roadname,
      tree_id, societyname, entered_date, entered_time, updated_date, googleAddress
    ];

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

    const treeDetailsId = result.rows[0].tree_details_id;

    // Handle image uploads
    const files = req.files || {};
    for (const field in files) {
      const file = files[field][0];
      const oldPath = file.path;
      const ext = path.extname(file.originalname);
      const newFileName = `${treeDetailsId}_${field}${ext}`;
      const newDir = path.join(__dirname, '..', 'uploads', 'tree_images', field);

      if (!fs.existsSync(newDir)) {
        fs.mkdirSync(newDir, { recursive: true });
      }

      const newPath = path.join(newDir, newFileName);
      fs.renameSync(oldPath, newPath);

      savedImages[field] = path.relative(path.join(__dirname, '..', 'uploads'), newPath);
    }

    if (result.rows.length > 0) {
      return res.status(201).json({
        message: 'Tree added successfully',
        treeDetailsId: treeDetailsId
      });
    } else {
      return res.status(500).json({ error: 'Failed to add tree' });
    }
  } catch (err) {
    console.error('Error adding tree:', err);
    return res.status(500).json({ error: err.message });
  }
};


export const getTreeDetailsById = async (req, res) => {
  try {
    const tree_details_id = req.query.id
    const dbName = req.query.dbName

    getSubDbConnection(dbName)
    const subDb = getSubDbConnection(dbName);  // Get the DB connection (replace with actual DB name)
    // subDb.query()
    const query = `
        select * from treedetailsentered
        where tree_details_id = $1
      `;

    const values = [
      tree_details_id
    ];

    // console.log("DEBUG SQL:", query.replace(/\$\d+/g, (_, i) => `'${values[i]}'`));
    // console.log("tree_details_id", tree_details_id)
    const result = await subDb.query(query, values);
    if (!result || !Array.isArray(result.rows)) {
      return res.status(500).json({ error: 'Invalid response from database.' });
    }

    if (result.rows.length === 0) {
      return res.status(404).json({ message: 'No records found.' });
    }

    res.status(200).json(result.rows)
    // console.log("result", result)
  } catch (error) {
    // console.error('Error fetching tree details:', error);
    res.status(500).json({ error: error.message || 'Internal server error' });
  }

}

export const getAllClusterDetails = async (req, res) => {
  const { ids, dbName } = req.body;

  if (!ids || !Array.isArray(ids) || ids.length === 0) {
    return res.status(400).json({ error: 'No tree IDs provided' });
  }

  if (!dbName) {
    return res.status(400).json({ error: 'dbName is required' });
  }

  try {
    const subDb = getSubDbConnection(dbName);

    const placeholders = ids.map((_, index) => `$${index + 1}`).join(',');
    const sql = `
      SELECT *
      FROM treedetailsentered
      WHERE tree_details_id  IN (${placeholders})
    `;

    const result = await subDb.query(sql, ids);

    res.status(200).json(result.rows);
  } catch (error) {
    console.error('Error in getAllClusterDetails:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
};

export const masterData = async (req, res) => {
  try {
    const { treelogy_id } = req.body;
    if (!treelogy_id) return res.status(400).json({ error: 'treelogy_id is required' });

    const treelogyData = await Treelogy.findByPk(treelogy_id);
    if (!treelogyData) return res.status(404).json({ error: 'Treelogy not found' });

    const dbName = treelogyData.dataValues.dbname;
    const subDb = getSubDbConnection(dbName);

    const [societies, treelist, roads, balances, conditions, ownerships, remarks, specialComments, birdNests] = await Promise.all([
      subDb.query(`SELECT DISTINCT societyname AS society_name FROM treedetailsentered WHERE entered_date > CURRENT_DATE - INTERVAL '1 month'`),
      subDb.query(`SELECT DISTINCT tree_id ,tree_name ,comman_name ,botanical ,is_rare,gujarati_name FROM treelist`),
      subDb.query(`SELECT DISTINCT roadname AS road_name FROM treedetailsentered WHERE entered_date > CURRENT_DATE - INTERVAL '1 month'`),
      subDb.query(`SELECT balance_id, isbalance FROM treebal_imbalance ORDER BY 1`),
      subDb.query(`SELECT id AS condition_id, condition_name FROM condition ORDER BY 2`),
      subDb.query(`SELECT id AS ownership_id, ownership_name FROM ownership ORDER BY 1`),
      subDb.query(`SELECT id AS remark_id, remark_name FROM remarks ORDER BY 1`),
      subDb.query(`SELECT special_comment_id AS comment_id, special_comment AS comment_name FROM spacial_comment ORDER BY 1`),
      subDb.query(`SELECT bird_nest_id AS nest_id, nest_value FROM bird_nest ORDER BY 1`),
    ]);

    res.status(200).json({
      societies: societies.rows,
      treelist: treelist.rows,
      roads: roads.rows,
      balances: balances.rows,
      conditions: conditions.rows,
      ownerships: ownerships.rows,
      remarks: remarks.rows,
      special_comments: specialComments.rows,
      bird_nests: birdNests.rows,
    });

  } catch (err) {
    console.error('masterData error:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
};



export const createAndApplySchema = async (dbName) => {
  // const { dbName } = req.body;
  if (!dbName) return res.status(400).json({ error: 'dbName is required' });
  const rootSequelize = sequelize
  try {
    // Step 1: Create the database
    await rootSequelize.query(`CREATE DATABASE "${dbName}";`);
    console.log(`✅ Database '${dbName}' created successfully!`);

    // Step 2: Read and filter schema
    const originalSchema = fs.readFileSync(schemaPath, 'utf8');
    const filteredSchema = filterSchema(originalSchema);

    // Step 3: Apply schema using native pg client
    // const client = new Client({
    //   user: 'postgres',
    //   password: '123456',
    //   host: 'localhost',
    //   database: dbName,
    //   port: 5432,
    // });
    // await rootSequelize.connect();

    await rootSequelize.query(filteredSchema);
    console.log(`✅ Schema applied successfully to '${dbName}'`);

    // await rootSequelize.end();
    // res.status(200).json({ message: `Database '${dbName}' created & schema applied successfully!` });
  } catch (err) {
    console.error('❌ Error:', err.message);
    res.status(500).json({ error: err.message });
  } finally {
    await rootSequelize.close();
  }
};
