<?php # encoding:utf-8
/**
  * Met à jour la base de données en fonction de la version courante de celle ci.
  * Si des tables n'existes pas elles sont automatiquement créées.
  */
  
include_once("connexion.php");

$connexion = new Connexion();
  
function creer_db()
{
   mysql_query("   
      CREATE TABLE IF NOT EXISTS config (
         nom varchar(50) NOT NULL,
         valeur varchar(255) NOT NULL,
         PRIMARY KEY (nom)
      ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
   ");
   mysql_query("
      CREATE TABLE pizzas (
        id mediumint(3) unsigned NOT NULL auto_increment,
        nom varchar(40) NOT NULL,
        composition varchar(255) NOT NULL,
        prix tinyint(3) unsigned default '0',
        PRIMARY KEY  (id)
      ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
   ");
   mysql_query("
      CREATE TABLE IF NOT EXISTS jeux (
        id mediumint(3) unsigned NOT NULL auto_increment,
        nom varchar(200) default '0',
        PRIMARY KEY (id),
        UNIQUE KEY nom_unique (nom)
      ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
   ");
   mysql_query("
      CREATE TABLE IF NOT EXISTS participants (
        id mediumint(3) unsigned NOT NULL auto_increment,
        pseudo varchar(50) default NULL,
        clan_nom varchar(30) default NULL,
        clan_tag varchar(10) default NULL,
        password varchar(10) default NULL,
        nom varchar(30) default NULL,
        prenom varchar(30) default NULL,
        age varchar(30) default NULL,
        e_mail varchar(50) default NULL,
        remarques varchar(255) default NULL,
        admin tinyint(1) unsigned NOT NULL default '0',
        a_paye tinyint(1) unsigned NOT NULL default '0',
        pizza mediumint(3) unsigned default NULL,
        pizza_paye tinyint(1) NOT NULL default '0',
        PRIMARY KEY  (id),
        KEY FK_pizza (pizza),
        CONSTRAINT FK_pizza FOREIGN KEY (pizza) REFERENCES pizzas (id) ON DELETE SET NULL ON UPDATE SET NULL
      ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
   ");
   mysql_query("
      CREATE TABLE IF NOT EXISTS jeux_choisis (
        participant_id mediumint(3) unsigned NOT NULL,
        jeu_id mediumint(3) unsigned NOT NULL,
        PRIMARY KEY USING BTREE (participant_id,jeu_id),
        KEY FK_jeu (jeu_id),
        CONSTRAINT FK_participant FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT FK_jeu FOREIGN KEY (jeu_id) REFERENCES jeux (id) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
   ");
}

function initialiser_db()
{
   mysql_query("INSERT INTO config (nom, valeur) VALUES ('version', 1)");
}

function update_db()
{
   # si la table 'config' n'existe pas alors on suppose qu'aucune table n'existe
   $version = 0;
   $res = @mysql_fetch_object(mysql_query("SELECT valeur FROM config WHERE nom = 'version'"));
   if (!mysql_error())      
      $version = $res->valeur;
      
   if($version == 0)
   {
      mysql_query("BEGIN TRANSACTION");
      creer_db();
      initialiser_db();
      mysql_query("COMMIT");
      $version = 1;
   }
   
   # version 1 -> 2
   if ($version == 1)
   {
      mysql_query("BEGIN TRANSACTION");
      mysql_query("ALTER TABLE participants ADD CONSTRAINT UNIQUE INDEX pseudo_unique (pseudo);");
      mysql_query("
         CREATE TABLE IF NOT EXISTS periodes (
           id mediumint(3) unsigned NOT NULL auto_increment,
           nom varchar(200) NOT NULL,
           nom_abrege varchar(20) NOT NULL DEFAULT '',
           PRIMARY KEY (id)
         ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
      ");
      mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('vendredi soir à samedi', 'v->s')");
      mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('samedi à dimanche', 's->d')");
      mysql_query("INSERT INTO periodes (nom, nom_abrege) VALUES ('dimanche à lundi', 'd->l')");
      mysql_query("
         CREATE TABLE IF NOT EXISTS participations (
           participant_id mediumint(3) unsigned NOT NULL,
           periode_id mediumint(3) unsigned NOT NULL,
           PRIMARY KEY USING BTREE (participant_id, periode_id),
           KEY FK_periode (periode_id),
           CONSTRAINT FK_participant_participations FOREIGN KEY (participant_id) REFERENCES participants (id) ON DELETE CASCADE ON UPDATE CASCADE,
           CONSTRAINT FK_periode_participations FOREIGN KEY (periode_id) REFERENCES periodes (id) ON DELETE CASCADE ON UPDATE CASCADE
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
      ");
      $participations = mysql_query("SELECT participants.id as participant_id, periodes.id as periode_id FROM participants, periodes");
      while ($participation = mysql_fetch_object($participations))
      {
         mysql_query("
            INSERT INTO participations (participant_id, periode_id)
            VALUES (".$participation->participant_id.",".$participation->periode_id.")"
         );
      }
      mysql_query("UPDATE config SET valeur = '2' WHERE nom = 'version'");
      
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_periode', '15')");
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('cout_total', '40')");
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_max_participant', '25')");
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('nb_votes_jeux', '3')");
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('inscription_terminees', 'FALSE')");
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_visible', 'FALSE')");
      mysql_query("INSERT INTO config (nom, valeur) VALUES ('pizza_peut_commander', 'TRUE')");
      
      mysql_query("COMMIT");
   }
}

update_db();

?>