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/zones-de-rattachement.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.arrondisse == 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;