var express = require('express');

var router = express.Router();

var sqlite3 = require('sqlite3');

var fs = require('fs');

var db = new sqlite3.Database('./public/elections.db');

var zonesfull = JSON.parse(fs.readFileSync("./public/secteurs-des-bureaux-de-vote.json"));

var elections = ["Élections"];

var sql_election = [""];

var sections = ["Sections"];

var sql_section = [""];

var route_name = "/cartes-elections";

var url = "https://cloud.paris12.pcf.fr" + route_name;

function resultat_par_bureau (from_where, numerateur, denominateur, gradient, next) {

    db.all('select bureau,((1.00 * ' + numerateur + ') / (1.00 * ' + denominateur + ')) as data' + from_where, function (err, rows) {

        if (err) {

            console.log(err);

        }

        else {

            gradient.min = 9999;

            gradient.max = 0 - gradient.min;

            for (var row in rows) {

                gradient[rows[row].bureau] = rows[row].data;

                gradient.max = (rows[row].data > gradient.max) ? rows[row].data : gradient.max;

                gradient.min = (rows[row].data < gradient.min) ? rows[row].data : gradient.min;

            }

            next();

        }

    });

}

var requetes = {

    "Requêtes" : "",

    "Scores candidat-e (ou liste de) soutenu-e (en %)" : function (req, next) {

        var gradient = { 

            titre : req.requete + '<br>' + elections[req.election],

            lightness : {

                intitule : 'résultat',

                type     : '%'

            }

        };

        var from_where = ' from scrutins where soutien is 1 and ' + sql_election[req.election] + ' and ' + sql_section[req.section];

        resultat_par_bureau(from_where, 'voix', 'exprimes', gradient.lightness, function () {

            next(req.section, gradient);

        });

    },

    "Voix candidat-e (ou liste de de) soutenu-e": function (req, next) {

        var gradient = { 

            titre : req.requete + '<br>' + elections[req.election],

            lightness : {

                intitule : 'voix',

                type    : 'absolues'

            },

            saturation : {

                intitule : 'inscrits',

                type    : 'absolus'

            }

        };

        var from_where = ' from scrutins where soutien is 1 and ' + sql_election[req.election] + ' and ' + sql_section[req.section];

        resultat_par_bureau(from_where, 'voix', 1, gradient.lightness, function () {

            resultat_par_bureau(from_where, 'inscrits', 1, gradient.saturation, function () {

                next(req.section, gradient);

            });

        });

    },

    "Abstention seule (%)" : function (req, next) {

        var gradient = { 

            titre : req.requete + '<br>' + elections[req.election],

            lightness : {

                intitule : 'abstention',

                type     : '%'

            }

        };

        var from_where = ' from scrutins where soutien is 1 and ' + sql_election[req.election] + ' and ' + sql_section[req.section];

        resultat_par_bureau(from_where, 'inscrits - exprimes', 'inscrits', gradient.lightness, function () {

            next(req.section, gradient);

        });

    },

    "Score soutien (%) + abstention (%)" : function (req, next) {

        var gradient = {

            titre : req.requete + '<br>' + elections[req.election],

            lightness : {

                intitule : 'résultat',

                type     : '%'

            },

            saturation : {

                intitule : 'abstention',

                type     : '%'

            }

        };

        var from_where = ' from scrutins where soutien is 1 and ' + sql_election[req.election] + ' and ' + sql_section[req.section];

        resultat_par_bureau(from_where, 'voix', 'exprimes', gradient.lightness, function () {

            resultat_par_bureau(from_where, 'inscrits - exprimes', 'inscrits', gradient.saturation, function () {

                next(req.section, gradient);

            });

        });

    },

    "Evolution avec autre scrutin (pas encore disponible)" : function () { return; }

}

function pad(n, width, z) {

    z = z || '0';

    n = n + '';

    return n.length >= width ? n : new Array(width - n.length + 1).join(z) + n;

}

function zones_to_geoJSON (arrondissement, resultats, next) {

    var geoJSON = {

        "type": "FeatureCollection",

        "features": []

    };

    for (var bureau in zonesfull) {

        if (zonesfull[bureau].fields.arrondissement == arrondissement) {

            var num_bv = zonesfull[bureau].fields.num_bv;

            geoJSON.features.push({

                "type" : "Feature",

                "id"   : num_bv,

                "properties": {"num_bv": num_bv },

                "geometry": zonesfull[bureau].fields.geo_shape

            });

        }

    }

    next(resultats, geoJSON);

}

function format_first_second (v) {

    return v + (v === 1 && "er" || "ème");

}

function set_elections_arrays (err, row) {

    if (!err) {

        elections.push(row.annee + " - " + row.election + " - " + format_first_second(row.tour) + " tour" );

        sql_election.push("election is '" + row.election + "' and annee is " + row.annee + " and tour is " + row.tour);

    }

    else {

        console.log(err);

    }

}

function get_elections_choices () {

    db.each("select distinct election,annee,tour from scrutins order by annee, election, tour asc", set_elections_arrays);

}

get_elections_choices();

for (var section = 1; section <= 20; section++) {

    sections.push(format_first_second(section) + " arrondissement");

    sql_section.push("arrondissement is " + section);

}

router

    .route(route_name)

    .get(function (req, res, next) {

        res.render('choix', {

            url : url,

            elections : elections, 

            sections : sections, 

            requetes : requetes

        });

    })

    .post(function(req, res, next) {

        if (!req.body) return res.sendStatus(400);

        requetes[req.body.requete](req.body, function (arrondissement, resultats) {

            function testresultats () {

                for (var hsla in resultats) {

                    if (resultats[hsla][1]) { 

                        return true; 

                    }

                }

                return false;

            }

            if (testresultats()) {

                zones_to_geoJSON(arrondissement, resultats, function (resultats, zones) {

                    if (!zones.features[0].geometry) {

                        throw("pas de découpage...");

                    }

                    else {

                        res.render('paris', { 

                            'url' : url,

                            'gradient' : resultats,

                            'zones'    : zones

                        })

                    }

                });

            }

            else {

                var noresult = "Pas de résultat sur le " + req.body.section + " pour '" + req.body.election + "'...";

                res.simpleText(200, noresult);

            }

        });

    });

module.exports = router;