var express = require('express'); // require Express
var router = express.Router(); // setup usage of the Express router engine
const bodyparser = require('body-parser');

/* PostgreSQL and PostGIS module and connection setup */
const { Client, Query } = require('pg');
var pg = require("pg");



// Setup connection
var username = "tiagocarvalhido" // sandbox username
var password = "techdech12" // read only privileges on our table
var host = "localhost:5432"
var database = "is" // database name
var conString = "postgres://"+username+":"+password+"@"+host+"/"+database; // Your Database Connection

// Set up your database query to display GeoJSON
var caop = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((gid, lug11desig)) As properties FROM caop As lg) As f) As fc";


router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

module.exports = router;





/* GET Postgres JSON data */
router.get('/data', function (req, res) {
    var client = new Client(conString);
    client.connect();
    var query = client.query(new Query(caop));
    query.on("row", function (row, result) {
        result.addRow(row);
    });
    query.on("end", function (result) {
        res.send(result.rows[0].row_to_json);
        res.end();
    });
});


/* GET the map page */
router.get('/map', function(req, res) {
    var client = new Client(conString); // Setup our Postgres Client
    client.connect(); // connect to the client

    var query = client.query(new Query(caop)); // Run our Query

    query.on("row", function (row, result) {

        result.addRow(row);
    });

    // Pass the result to the map page

    query.on("end", function (result) {
        var data = result.rows[0].row_to_json // Save the JSON as variable data
        res.render('map', {
            title: "NodeJs + Leafleat", // Give a title to our page
            jsonData: data // Pass data to the View
        });
    });


});





var inserir = "select row_to_json(ponto) from points";

router.get('/ponto', function (req, res) {
    var client = new Client(conString);
    client.connect();
    var query = client.query(new Query(inserir));
    query.on("row", function (row, result) {
        result.addRow(row);
    });
    query.on("end", function (result) {
        res.send(result.rows[0].row_to_json);
        res.end();
    });
});