<?php

  class mtweb_sql_version_0_11_2 extends mw_data_version{

    function version(){
      return "0.11.2";
    }

    function do_upgrade(){

      $env = $this->env();
      $data = $env->data();
      $sgbd = $data->sgbd();

      if(($env->bdd("sgbd") == "pdo_mysql") || ($env->bdd("sgbd") == "mysql")){
        if(!$data->set_sgbd_param("SQL_MODE", "NO_AUTO_VALUE_ON_ZERO")){
          return "imposible de parametrer SQL_MODE en NO_AUTO_VALUE_ON_ZERO";
        }
      }

      /* ----------------------------------------------------------------------
                                                                  action_status
                                                          devient actions_roles
                                              element id_status devient id_role
      */

      if(!$sgbd->table_exists("#--actions_roles")){
        if(!$sgbd->table_exists("#--action_status")){
         return "impossible de trouver la table action_status";
        }
        if(
          !$data->data_create_table(
            array(
              "table_name" => "actions_roles",
              "fields" => array(
                "id" => array("type" => "int", "autoincrement" => true),
                "action" => array("type" => "varchar"),
                "id_role" => array("type" => "int")
              ),
              "keys" => array(
                array(
                  "fields" => array("id"),
                  "primary" => true
                ),
                array(
                  "fields" => array("id_role")
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table actions_roles";
        }

        $sql =
         "INSERT INTO #--actions_roles(action, id_role)"
        ." SELECT action, id_status"
        ." FROM #--action_status";
        try{
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible d'importer action_status dans actions_roles";
        }
        $sql =
         "INSERT INTO #--actions_roles(action, id_role) VALUES('config', 3)";
        try{
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible d'ajouter la protection d'accès webmaster dans actions_roles";
        }
        try{
          $sgbd->query("DROP TABLE #--action_status");
        }
        catch(Exception $e){
          return "impossible de supprimer la table action_status";
        }
      }

      /* ----------------------------------------------------------------------
                                                                    user_status
                                                                  devient roles
                                                plus d'element creation_default
                                                        nouvel element intitule
      */

      if(!$sgbd->table_exists("#--roles")){
        if(!$sgbd->table_exists("#--user_status")){
         return "impossible de trouver la table user_status";
        }
        if(
          !$data->data_create_table(
            array(
              "table_name" => "roles",
              "fields" => array(
                "id" => array("type" => "int", "autoincrement" => true),
                "nom" => array("type" => "varchar"),
                "intitule" => array("type" => "varchar")
              ),
              "keys" => array(
                array(
                  "fields" => array("id"),
                  "primary" => true
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table roles";
        }
        try{
          $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (0, 'guest', 'invité')";
          $sgbd->query($sql);
          $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (1, 'admin', 'administrateur')";
          $sgbd->query($sql);
          $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (2, 'membre', 'membre')";
          $sgbd->query($sql);
          $sql = "INSERT INTO #--roles(id, nom, intitule) VALUES (3, 'webmaster', 'webmaster')";
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible de remplir la table roles";
        }
        try{
          $sgbd->query("DROP TABLE #--user_status");
        }
        catch(Exception $e){
          return "impossible de supprimer la table user_status";
        }
      }

      /* ----------------------------------------------------------------------
                                                     nouvelle table users_roles
                                 importer users.status dans users_roles.id_role
      */

      if(!$sgbd->table_exists("#--users_roles")){
        if(
          !$data->data_create_table(
            array(
              "table_name" => "users_roles",
              "fields" => array(
                "id_user" => array("type" => "int"),
                "id_role" => array("type" => "int")
              ),
              "keys" => array(
                array(
                  "fields" => array("id_user", "id_role"),
                  "primary" => true
                ),
                array(
                  "fields" => array("id_role")
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table users_roles";
        }
        if(($users = $data->data_list(array("table_name" => "users"))) === false){
          return "impossible de lire la table des utilisateurs";
        }
        foreach($users["list"] as $user){
          if(
            !$data->data_insert(
              array(
                "table_name" => "users_roles",
                "values" => array(
                  "id_user" => $user["id"],
                  "id_role" => $user["status"]
                )
              )
            )
          ){
            return "erreur lors de l'insertion des roles dans users_roles";
          }
        }
        $sql = "ALTER TABLE #--users RENAME TO #--users_tmp";
        try{
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible de renommer la table users en users_tmp";
        }
        if(
          !$data->data_create_table(
            array(
              "table_name" => "users",
              "fields" => array(
                "id" => array("type" => "int", "autoincrement" => true),
                "login" => array("type" => "varchar"),
                "password" => array("type" => "varchar"),
                "email" => array("type" => "varchar")
              ),
              "keys" => array(
                array(
                  "fields" => array("id"),
                  "primary" => true
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table users";
        }
        $sql =
         "INSERT INTO #--users(id, login, password, email)"
        ." SELECT id, login, password, email"
        ." FROM #--users_tmp";
        try{
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible d'importer users_tmp dans users";
        }
        try{
          $sgbd->query("DROP TABLE #--users_tmp");
        }
        catch(Exception $e){
          return "impossible de supprimer la table users_tmp";
        }
      }

      /* ----------------------------------------------------------------------
                                                        nouvelle table versions
      */

      if(!$sgbd->table_exists("#--versions")){
        if(
          !$data->data_create_table(
            array(
              "table_name" => "versions",
              "fields" => array(
                "id" => array("type" => "int", "autoincrement" => true),
                "application" => array("type" => "varchar"),
                "version" => array("type" => "varchar")
              ),
              "keys" => array(
                array(
                  "fields" => array("id"),
                  "primary" => true
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table versions";
        }
      }

      return true;
    }

  }