#!/usr/bin/perl -w

use strict;
use warnings;
use locale;
use POSIX qw(locale_h);
use feature 'unicode_strings';
use DateTime;
use DateTime::Format::Strptime;
use GenerateurDate;

use Data::Dumper;

use Data::Faker;
use Data::Faker::Couleur;
use Data::Faker::Nom;
use Data::Faker::Prix;
use Data::Faker::Telephone_FR;
use File::Basename;
use Getopt::Long qw(:config bundling no_ignore_case_always);
use Text::Lorem;

use Digest::MD5 qw(md5_hex);

my %args = (
    'database'      => 'sql',
    'help'          => undef
);

setlocale(LC_NUMERIC, 'C') or die "Oops";

# ATTENTION à ne pas changer scale_factor.
# Le générateur de dates de commandes est calquée sur scale_factor=100.
my $scale_factor = 100;
my $n_clients = 1000 * $scale_factor;
my $n_commandes = 10000 * $scale_factor;
my $n_articles = 2000;
my $n_fournisseurs = 100 * $scale_factor;

my $faker = Data::Faker->new();
my $text = Text::Lorem->new();

###############################################################################

### Utility functions ###
sub help {
    print basename($0) . " utilisation :
    --help | -h         Afficher ce message
    --database | -d     Spécifier la base de données
";
    exit 0;
}

sub query_db {
  my $query = shift;
  my @res;
  my $psqlcmd = "psql --set \"ON_ERROR_STOP=1\" -qXAt ". $args{'database'}
              . " -c \"". $query . "\"";
  open(FROMPSQL, "$psqlcmd |");
  while (<FROMPSQL>) {
    chomp;
    push (@res, $_);
  }
  close(FROMPSQL);
  return @res;
}

sub insert_db {
  my $table_name = shift;
  my $table_cols = shift;
  my $dataproc = shift;
  my $psqlcmd = "psql --set \"ON_ERROR_STOP=1\" -qXAt ". $args{'database'}
              . " -c \"COPY ".$table_name." (". $table_cols .") FROM STDIN WITH DELIMITER ';'\"";
  open(TOPSQL, "| $psqlcmd");
  select TOPSQL;
  $dataproc->();
  select STDOUT;
  close(TOPSQL);
}

sub submit_queries_db {
  my $dataproc = shift;

  my $psqlcmd = "psql --set \"ON_ERROR_STOP=1\" -qXAt ". $args{'database'};

  open(TOPSQL, "| $psqlcmd");
  select TOPSQL;
  print "BEGIN TRANSACTION;";
  $dataproc->();
  print "COMMIT;";
  select STDOUT;
  close(TOPSQL);
}

### Generators ###

sub gen_contacts {
  my $nom;
  my $prenom;
  my $login;
  my $email;
  my $telephone;
  my $adresse;
  my $ville;
  my $code_postal;
  my $password;
  my $code_pays;

  # generation contact pour clients, fournisseurs et transporteurs
  for (my $i = 0; $i < ($n_clients + $n_fournisseurs + 5); $i++) {
    $nom = $faker->last_name;
    $prenom = $faker->prenom;
    $login = $nom."_".$prenom;
    $email = $faker->email;
    $telephone = $faker->telephone;
    $adresse = $faker->street_address;
    $ville = $faker->city;
    $code_postal = $faker->code_postal;
    $password=md5_hex($faker->last_name);
    $code_pays = $faker->code_pays_client;
    printf ("%s;%s;%s;%s;%s;{%s};%s;%s;%s;%s\n", $nom, $prenom, $login, $password, $email, $telephone, $adresse, $code_postal, $ville, $code_pays);
  }
}

sub gen_fournisseurs {
  my @ids;
  my $contact_id;
  my $sql = "SELECT contact_id FROM contacts ORDER BY contact_id LIMIT ".$n_fournisseurs;
  my $comment;

  @ids = query_db($sql);
  foreach $contact_id (@ids) {
    $comment = $text->words(5);
    printf ("%d;%s\n", $contact_id, $comment);
  }
}

sub gen_clients {
  my @ids;
  my $contact_id;
  my $type_client;
  my $comment;

  my $sql = "SELECT contact_id FROM contacts ORDER BY contact_id LIMIT ".$n_clients." OFFSET ".($n_fournisseurs);
  @ids = query_db($sql);
  foreach $contact_id (@ids) {
    $comment = $text->words(5);
    $type_client = $faker->type_client;
    printf ("%d;%s;%s\n", $contact_id, $type_client, $comment);
  }
}

sub gen_articles {
  my $nom;
  my $marque;
  my $fabriquant;
  my $longueur;
  my $largeur;
  my $hauteur;
  my $poids;
  my $prix;
  my $comment;

  for (my $i = 0; $i < $n_articles; $i++) {
    $nom = $text->words(2);
    $marque = $faker->company;
    $fabriquant = $faker->company;
    $longueur = 30 + (rand(700));
    $largeur = $longueur - (rand($longueur));
    $hauteur = $longueur - (rand($longueur));
    $poids = int(rand(100));
    $prix = $faker->prix;
    $comment = $text->words(5);
    printf("%s;%s;%s;%f;%f;%f;%f;%f;%s\n", $nom, $marque, $fabriquant, $longueur, $largeur, $hauteur, $poids, $prix, $comment);
  }
}

sub gen_lien_articles_fournisseurs {
  my @fournisseurs_ids;
  my @articles_ids;
  my $fournisseur_id;
  my $article_id;
  my $comment;
  my $temp;

  my $sql = "SELECT produit_id FROM produits ORDER BY produit_id LIMIT ".$n_articles;
  @articles_ids = query_db($sql);
  $sql = "SELECT fournisseur_id FROM fournisseurs ORDER BY fournisseur_id LIMIT ".$n_fournisseurs;
  @fournisseurs_ids = query_db($sql);

  foreach $article_id (@articles_ids) {
    $comment = $text->words(4);
    $fournisseur_id = $fournisseurs_ids[rand($#fournisseurs_ids)];
    printf ("%d;%s;%s\n", $article_id, $fournisseur_id, $comment);
    if ($fournisseur_id < 5) {
      $temp = $fournisseur_id;
      $fournisseur_id = $fournisseurs_ids[rand($#fournisseurs_ids)];
      if ($temp != $fournisseur_id) {
        printf ("%d;%s;%s\n", $article_id, $fournisseur_id, $comment);
      }
    }
  }
}

sub gen_commandes {
  my $comment;
  my @clients_ids;
  my $client_id;
  my $num_com;
  my $date_commande;
  my $mode_exp;

  my $sql = "SELECT client_id FROM clients ORDER BY client_id LIMIT ".$n_clients;
  @clients_ids = query_db($sql);
  for (my $num_com = 0; $num_com < $n_commandes; $num_com++) {
    $client_id = $clients_ids[rand($#clients_ids)];
    $comment = $text->words(4);
    $date_commande = GenerateurDate::get_next_date();
    $mode_exp = $faker->mode_expedition;
    printf("%d;%d;%s;%d;%s\n", $num_com, $client_id, $date_commande, $mode_exp, $comment);
  }
}

sub gen_lignes_commandes {
  my @commandes_nums;
  my @articles_fournisseurs_ids;
  my $sql;
  my $article_id;
  my $fournisseur_id;
  my $num_com;
  my $comment;
  my $nombre_articles;
  my $quantite;
  my $temp;

  $sql = "SELECT produit_id, fournisseur_id FROM produit_fournisseurs ORDER BY produit_id LIMIT ".$n_articles;
  @articles_fournisseurs_ids = query_db($sql);
  $sql = "SELECT numero_commande FROM commandes ORDER BY numero_commande LIMIT ".$n_commandes;
  @commandes_nums = query_db($sql);

  foreach $num_com (@commandes_nums) {
    $nombre_articles = int(rand(7));
    $nombre_articles = ($nombre_articles == 0) ? 1 : $nombre_articles;
    for (my $i = 0; $i < $nombre_articles; $i++) {
      $temp = $articles_fournisseurs_ids[rand($#articles_fournisseurs_ids)];
      ($article_id, $fournisseur_id) = split(/\|/, $temp);
      $quantite = int(rand(10));
      $comment = $text->words(2);
      printf ("%d;%d;%d;%d;%d;%d;%d;%s\n", $num_com, $article_id, $fournisseur_id, $quantite, 0, 0, 0, $comment);
    }
  }
}

sub gen_transporteurs {
  my @ids;
  my $contact_id;
  my $comment;
  my @transporteurs;
  my $transporteur;

  @transporteurs = ('Eul Poste', 'Mondial Delay', 'Dynamite', 'BHL Livraison', 'Royal Air Drone');

  my $sql = "SELECT contact_id FROM contacts ORDER BY contact_id LIMIT 5 OFFSET ".($n_fournisseurs + $n_clients);
  @ids = query_db($sql);
  foreach $transporteur (@transporteurs) {
    $comment = $text->words(3);
    $contact_id = pop(@ids);
    printf ("%s;%d;%s\n", $transporteur, $contact_id, $comment);
  }
}

sub gen_lots_livraison {
  my $sql = qq{
SET work_mem = '512MB';
WITH lg_nlots_comptage AS (
SELECT numero_commande,
       numero_ligne_commande,
       CASE WHEN (first_value(random) OVER (PARTITION BY numero_commande)) < 10
            THEN 2
            ELSE 1
        END AS n_lots,
       count(*) OVER (PARTITION BY numero_commande) AS compte
  FROM (SELECT numero_commande, numero_ligne_commande,
               round(random()*500)::integer random
          FROM lignes_commandes) q
 ORDER BY 1, 2
), localisation_ruptures AS (
SELECT numero_commande,
       numero_ligne_commande,
       CASE WHEN n_lots = 2 AND compte > 3
            THEN round(random()*(compte - 2)) + 1
            ELSE NULL
        END AS rupture
 FROM lg_nlots_comptage
), ruptures AS (
SELECT numero_commande,
       numero_ligne_commande,
       row_number() OVER (PARTITION BY numero_commande ORDER BY numero_ligne_commande) AS row_num,
       first_value(rupture) OVER (PARTITION BY numero_commande) AS rupture
  FROM localisation_ruptures
), numero_lots AS (
SELECT numero_commande,
       numero_ligne_commande,
       row_num, rupture,
       CASE WHEN row_num = rupture then nextval('lots_numero_lot_seq'::regclass)
            WHEN row_num = 1 then nextval('lots_numero_lot_seq'::regclass)
            ELSE currval('lots_numero_lot_seq'::regclass)
        END AS numero_lot
  FROM ruptures
 ORDER BY numero_commande, numero_ligne_commande
), update_lignes_commandes AS (
UPDATE lignes_commandes
   SET numero_lot_expedition = numero_lots.numero_lot
  FROM numero_lots
 WHERE lignes_commandes.numero_commande = numero_lots.numero_commande
   AND lignes_commandes.numero_ligne_commande = numero_lots.numero_ligne_commande
 RETURNING numero_lot_expedition
), distinct_num_lots AS (
SELECT numero_lot_expedition
  FROM update_lignes_commandes
 GROUP BY numero_lot_expedition
)
INSERT INTO lots (numero_lot, transporteur_id, numero_suivi)
SELECT numero_lot_expedition,
       1+(round(random()*4))::integer AS transporteur_id,
       substr((md5((random()*numero_lot_expedition)::text)), 1, 14) AS numero_suivi
  FROM distinct_num_lots };
  query_db($sql);
}

sub alter_lots_livraison {
  my $sql = qq{
WITH date_depot AS (
SELECT l.numero_commande,
       l.numero_lot_expedition,
       c.date_commande,
       c.date_commande + (interval '1 day' * round(random()*5)) AS date_depot
  FROM commandes c
  JOIN lignes_commandes l
     USING (numero_commande)
), date_expedition AS (
SELECT numero_lot_expedition,
       date_depot,
       date_depot + (interval '1 day' * round(random()*5)) AS date_expedition
  FROM date_depot
), date_reception AS (
SELECT numero_lot_expedition,
       date_depot,
       date_expedition,
       date_expedition + (interval '1 day' * round(random()*5)) + interval '1 day' AS date_reception
  FROM date_expedition
)
UPDATE lots
   SET date_expedition = dr.date_expedition,
       date_depot = dr.date_depot,
       date_reception = dr.date_reception
  FROM date_reception dr
 WHERE dr.numero_lot_expedition = lots.numero_lot
  };
  query_db($sql);
}

sub gen_factures {
  my $sql = qq{
CREATE TEMPORARY SEQUENCE temp_numero_facture;
WITH commandes_a_facturer AS (
SELECT 'FAC' || trim(to_char(nextval('temp_numero_facture'::regclass), '0000000000')) AS numero_facture,
       c.numero_commande,
       c.date_commande AS date_facturation,
       CASE cl.type_client
            WHEN 'P' -- particuliers
                 THEN c.date_commande
            WHEN 'E' -- entreprises
                 THEN c.date_commande + interval '30 days'
            WHEN 'A' -- administrations
                 THEN c.date_commande + interval '30 days'
            ELSE NULL
        END AS date_paiement,
       1 AS condition_reglement
  FROM commandes c
  JOIN clients cl USING (client_id)
)
INSERT INTO factures
(numero_facture, numero_commande,
 date_facturation, date_paiement, condition_reglement)
SELECT * FROM commandes_a_facturer;
UPDATE numeros_sequence
   SET numero_sequence = currval('temp_numero_facture'::regclass)
 WHERE type_sequence = 'facture';
DROP SEQUENCE temp_numero_facture;
 };
  print $sql."\n";
}

sub gen_paiements {
  my $sql = qq{
SET work_mem = '512MB';
WITH commandes_a_payer AS (
SELECT fac.numero_facture,
       fac.date_paiement,
       cl.type_client,
       SUM (p.prix) AS total_facture
  FROM commandes cmd
  JOIN lignes_commandes ligcmd ON (cmd.numero_commande = ligcmd.numero_commande)
  JOIN produits p ON (ligcmd.produit_id = p.produit_id)
  JOIN clients cl ON (cmd.client_id = cl.client_id)
  JOIN factures fac ON (cmd.numero_commande = fac.numero_commande)
 GROUP BY fac.numero_facture, fac.date_paiement, cl.type_client
), reglements_temp_1 AS (
SELECT numero_facture,
       date_paiement,
       total_facture,
       CASE type_client
            WHEN 'E'
              THEN CASE
                        WHEN total_facture > 1000
                          THEN round(1 + random()*3)
                        ELSE 1
                    END
            ELSE 1
        END AS nombre_paiements,
       CASE type_client
            -- administrations: virement aussi
            WHEN 'A'
              THEN 1
            -- entreprises: virement ou chèque
            WHEN 'E'
              THEN CASE
                        WHEN (1 + (random()*100)::integer < 4)
                          THEN 3 -- chèque
                        ELSE 1 -- virement
                   END
            -- particuliers: n'importe quel moyen de règlement
            WHEN 'P'
              THEN CASE
                        WHEN (1 + (random()*100)::integer < 4)
                          THEN 3 -- chèque
                        WHEN (1 + (random()*100)::integer < 4)
                          THEN 1 -- virement
                        WHEN (1 + (random()*100)::integer < 4)
                          THEN 4 -- contre-remboursement
                        ELSE 2 -- carte bancaire
                    END

            ELSE NULL -- autre cas pas supporté
        END AS mode_reglement

  FROM commandes_a_payer
), reglements_temp_2 AS (
SELECT numero_facture,
       date_paiement,
       total_facture,
       mode_reglement,
       nombre_paiements,
       generate_series(1, nombre_paiements::integer) AS num_reglement
  FROM reglements_temp_1
), reglements_temp_3 AS (
SELECT numero_facture,
       date_paiement,
       total_facture,
       total_facture / nombre_paiements AS montant,
       mode_reglement,
       nombre_paiements,
       num_reglement,
       CASE WHEN num_reglement > 1
              THEN date_paiement + interval '1 month'*num_reglement - interval '1 month'
              ELSE date_paiement
        END AS date_reglement
  FROM reglements_temp_2
), reglements_temp_4 AS (
SELECT numero_facture,
       date_reglement,
       total_facture,
       num_reglement,
       mode_reglement,
       round((total_facture / nombre_paiements)::numeric, 2) AS montant,
       MAX(nombre_paiements) OVER (PARTITION BY numero_facture
                                     ORDER BY date_reglement) AS max_num_regl
  FROM reglements_temp_3
), reglements_temp_5 AS (
SELECT *,
       SUM (montant) OVER (PARTITION BY numero_facture
                               ORDER BY date_reglement) AS somme_glissante
  FROM reglements_temp_4
)
INSERT INTO reglements (numero_facture, montant, mode_reglement, date_reglement)
SELECT numero_facture,
       CASE
        WHEN num_reglement = max_num_regl AND num_reglement <> 1
         THEN total_facture - lag(somme_glissante) OVER (PARTITION BY numero_facture
                                                              ORDER BY date_reglement)
            ELSE montant
        END AS montant_a_regler,
       mode_reglement,
       date_reglement
  FROM reglements_temp_5
 ORDER BY date_reglement, numero_facture
  };
  query_db($sql);
}

sub alter_lignes_commandes {
  my $sql = qq{
UPDATE lignes_commandes lc
   SET prix_unitaire = p.prix
  FROM produits p
 WHERE p.produit_id = lc.produit_id;
  };
  query_db($sql);
}

sub alter_produit_fournisseurs {
  my $sql = qq{
UPDATE produit_fournisseurs pf
   SET quantite_disponible = round(random()*150),
       cout_piece = round(prix * (0.50 - (random()*0.01))::numeric, 2)
  FROM produits p
 WHERE p.produit_id = pf.produit_id;
  };
  query_db($sql);
}

sub vacuum_full {
  my $sql = qq{
VACUUM FULL lignes_commandes;
VACUUM FULL lots;
VACUUM FULL produit_fournisseurs;
  };
}

### handle command line options ###

Getopt::Long::Configure('bundling');
GetOptions (
    \%args,
    'database|d=s',
    'help|h!'
);

help() if $args{'help'};


### Data generation ###

printf "Génération de %d contacts\n", $n_clients + $n_fournisseurs + 5;
insert_db( 'contacts',
           'nom, prenom, login, passwd, email, telephone, adresse1, code_postal, ville, code_pays',
           \&gen_contacts );
printf "Génération de %d fournisseurs\n", $n_fournisseurs;
insert_db( 'fournisseurs',
            'contact_id, commentaire',
            \&gen_fournisseurs );
printf "Génération de %d clients\n", $n_clients;
insert_db( 'clients',
            'contact_id, type_client, commentaire',
            \&gen_clients);
printf "Génération de %d articles\n", $n_articles;
insert_db( 'produits',
            'nom, marque, fabriquant, longueur, largeur, hauteur, poids, prix, commentaire',
            \&gen_articles);
printf "Lien entre articles et fournisseurs\n";
insert_db( 'produit_fournisseurs',
           'produit_id, fournisseur_id, commentaire',
           \&gen_lien_articles_fournisseurs);
printf "Génération de %d commandes\n", $n_commandes;
insert_db( 'commandes',
           'numero_commande, client_id, date_commande, mode_expedition, commentaire',
           \&gen_commandes);
query_db("UPDATE numeros_sequence
   SET numero_sequence = (SELECT max(numero_commande) FROM commandes)
 WHERE type_sequence = 'commande'");
printf "Génération des lignes de commandes\n";
insert_db( 'lignes_commandes',
           'numero_commande, produit_id, fournisseur_id, quantite, prix_unitaire, remise, numero_lot_expedition, commentaire',
           \&gen_lignes_commandes);
printf "Génération de 5 transporteurs\n";
insert_db( 'transporteurs',
           'nom, contact_id, commentaire',
           \&gen_transporteurs);
print "Génération des factures\n";
submit_queries_db(\&gen_factures);
print "Génération des paiements\n";
gen_paiements();
print "Génération des lots de livraison\n";
print "Étape 1 ... ";
gen_lots_livraison();
print "ok\nÉtape 2 ... ";
alter_lots_livraison();
print "ok\n";
print "Ajustement des prix unitaires sur lignes_commandes\n";
alter_lignes_commandes();
print "ok\n";
print "Ajustement de la table produit_fournisseurs\n";
alter_produit_fournisseurs();
print "ok\n";
print "VACUUM FULL final\n";
vacuum_full();
print "ok\n";
## EOF ##

