var express = require('express');
    var router = express.Router();

    /* PostgreSQL and PostGIS module and connection setup */
    const { Client, Query } = 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 coffee_query = "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";
    /* GET home page. */
    router.get('/', function(req, res, next) {
    res.render('index', { title: 'Express' });
    });

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





    router.post('/marker', function (req, res) {
        var marker = req.body;
        console.log(marker);
        var client = new Client(conString);
        client.connect();
        var query = `INSERT INTO mypoints(geom) VALUES(  ST_GeomFromText('POINT(${marker.marker.lng} ${marker.marker.lat})', 4326))`
        client.query(query, function(err, result){
            if(err){
                console.log(err);
            } else {
                console.log(result);
            }
        });
    });




    module.exports = router;