<?php

  class mw_sourceml_sql_version_1_1_2 extends mw_data_version{

    function version(){
      return "1.1.2";
    }

    function do_upgrade(){

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

      if($sgbd->table_exists("#--sources")){

        /* ----------------------------------------------------------------------
                                                     nouvelle table groupe_status
        */

        if(!$sgbd->table_exists("#--groupe_status")){
          if(
            !$data->data_create_table(
              array(
                "table_name" => "groupe_status",
                "fields" => array(
                  "id" => array("type" => "int", "autoincrement" => true),
                  "nom" => array("type" => "varchar")
                ),
                "keys" => array(
                  array(
                    "fields" => array("id"),
                    "primary" => true
                  )
                ),
                "options" => array("default_charset" => "utf8")
              )
            )
          ){
            return "impossible de créer la table groupe_status";
          }
          try{
            $sql = "INSERT INTO #--groupe_status(id, nom) VALUES (1, 'admin')";
            $sgbd->query($sql);
            $sql = "INSERT INTO #--groupe_status(id, nom) VALUES (2, 'editeur')";
            $sgbd->query($sql);
            $sql = "INSERT INTO #--groupe_status(id, nom) VALUES (3, 'contributeur')";
            $sgbd->query($sql);
          }
          catch(Exception $e){
            return "impossible de remplir la table groupe_status";
          }
        }

        /* ----------------------------------------------------------------------
                                                    nouvelle table source_groupes
                            sources.id_groupe passe dans source_groupes.id_groupe
        */

        if(!$sgbd->table_exists("#--source_groupes")){
          if(
            !$data->data_create_table(
              array(
                "table_name" => "source_groupes",
                "fields" => array(
                  "id" => array("type" => "int", "autoincrement" => true),
                  "id_source" => array("type" => "int"),
                  "id_groupe" => array("type" => "int"),
                  "id_groupe_status" => array("type" => "int")
                ),
                "keys" => array(
                  array(
                    "fields" => array("id"),
                    "primary" => true
                  )
                ),
                "options" => array("default_charset" => "utf8")
              )
            )
          ){
            return "impossible de créer la table source_groupes";
          }
          $sql =
           "INSERT INTO #--source_groupes(id_source, id_groupe, id_groupe_status)"
          ." SELECT"
          ."  id"
          .", id_groupe"
          .", 1"
          ." FROM #--sources";
          try{
            $sgbd->query($sql);
          }
          catch(Exception $e){
            return "impossible d'importer les id_groupes dans source_groupes";
          }
        }

        /* ----------------------------------------------------------------------
                                                nouvelle table source_derivations
                         sources.derivation devient source_derivations.derivation
        */

        if(!$sgbd->table_exists("#--source_derivations")){
          if(
            !$data->data_create_table(
              array(
                "table_name" => "source_derivations",
                "fields" => array(
                  "id" => array("type" => "int", "autoincrement" => true),
                  "id_source" => array("type" => "int"),
                  "derivation" => array("type" => "varchar")
                ),
                "keys" => array(
                  array(
                    "fields" => array("id"),
                    "primary" => true
                  )
                ),
                "options" => array("default_charset" => "utf8")
              )
            )
          ){
            return "impossible de créer la table source_derivations";
          }
          $sql =
           "INSERT INTO #--source_derivations(id_source, derivation)"
          ." SELECT"
          ."  id"
          .", derivation"
          ." FROM #--sources WHERE derivation IS NOT NULL";
          try{
            $sgbd->query($sql);
          }
          catch(Exception $e){
            return "impossible d'importer les derivation dans source_derivations";
          }
        }

        /* ----------------------------------------------------------------------
                                                                          sources
                                            maj source_infos : image avec id user
        */

        $SOURCE_HAS_ID_GROUPE = false;
        try{
          $sources_desc = $sgbd->desc_table("#--sources");
          if(isset($sources_desc["attributs"]["id_groupe"])){
            $SOURCE_HAS_ID_GROUPE = true;
          }
        }
        catch(Exception $e){
          return "impossible de lire les champs de la table sources";
        }
        if($SOURCE_HAS_ID_GROUPE){
          try{
            $sql =
             "SELECT #--sources.id as id_source, #--sources.id_groupe"
            .", #--source_infos.id as id_source_info, #--source_infos.`value` as image"
            ." FROM #--sources"
            ." LEFT JOIN #--source_infos"
            ." ON (#--source_infos.id_source=#--sources.id AND #--source_infos.`key`='image')";
            $rst = $sgbd->query($sql);
            while($v_rst = $sgbd->fetch_assoc($rst)){
              if($v_rst["image"] && !preg_match("/^[0-9]+\/.*$/", $v_rst["image"])){
                if(
                  (
                    $groupe = $data->data_read(
                      array(
                        "table_name" => "groupes",
                        "index_name" => "id",
                        "index_value" => $v_rst["id_groupe"]
                      )
                    )
                  ) === false
                ){
                  return "erreur lors de la lecture de l'utilisateur du groupe";
                }
                if(
                  !$data->data_update(
                    array(
                      "table_name" => "source_infos",
                      "index_name" => "id",
                      "index_value" => $v_rst["id_source_info"],
                      "values" => array(
                        "value" => $groupe["id_user"]."/".$v_rst["image"]
                      )
                    )
                  )
                ){
                  return "impossible de mettre a jour l'image des sources dans source_infos";
                }
              }
            }
            $sgbd->free_result($rst);
          }
          catch(Exception $e){
            return "impossible de mettre a jour les images des sources";
          }
        }


        /* ----------------------------------------------------------------------
                                                                          groupes
                                                           maj image avec id user
        */

        try{
          $sql ="SELECT * FROM #--groupes";
          $rst = $sgbd->query($sql);
          while($v_rst = $sgbd->fetch_assoc($rst)){
            if($v_rst["image"] && !preg_match("/^[0-9]+\/.*$/", $v_rst["image"])){
              if(
                !$data->data_update(
                  array(
                    "table_name" => "groupes",
                    "index_name" => "id",
                    "index_value" => $v_rst["id"],
                    "values" => array(
                      "image" => $v_rst["id_user"]."/".$v_rst["image"]
                    )
                  )
                )
              ){
                return "impossible de mettre a jour l'image des groupes";
              }
            }
          }
          $sgbd->free_result($rst);
        }
        catch(Exception $e){
          return "impossible de mettre a jour les images des groupes";
        }

        /* ----------------------------------------------------------------------
                                                                          sources
                                                sources.nom devient sources.titre
                                          sources.status devient sources.id_class
                                                 suppression de sources.id_groupe
                                                suppression de sources.derivation
        */

        try{
          $sources_desc = $sgbd->desc_table("#--sources");
        }
        catch(Exception $e){
          return "impossible de lire les champs de la table sources";
        }
        if(
             !isset($sources_desc["attributs"]["titre"])
          || isset($sources_desc["attributs"]["nom"])
          || isset($sources_desc["attributs"]["status"])
          || isset($sources_desc["attributs"]["id_groupe"])
          || isset($sources_desc["attributs"]["derivation"])
        ){
          $sql = "ALTER TABLE #--sources RENAME TO #--sources_tmp";
          try{
            $sgbd->query($sql);
          }
          catch(Exception $e){
            return "impossible de renommer la table sources en sources_tmp";
          }
          if(
            !$data->data_create_table(
              array(
                "table_name" => "sources",
                "fields" => array(
                  "id" => array("type" => "int", "autoincrement" => true),
                  "id_class" => array("type" => "int"),
                  "reference" => array("type" => "varchar", "null" => true),
                  "titre" => array("type" => "varchar", "null" => true),
                  "licence" => array("type" => "int", "null" => true),
                  "date_creation" => array("type" => "date", "null" => true),
                  "date_inscription" => array("type" => "datetime")
                ),
                "keys" => array(
                  array(
                    "fields" => array("id"),
                    "primary" => true
                  ),
                  array(
                    "fields" => array("id_class")
                  ),
                  array(
                    "fields" => array("licence")
                  )
                ),
                "options" => array("default_charset" => "utf8")
              )
            )
          ){
            return "impossible de créer la table sources";
          }
          $attribut_names = array(
            "id_class" => isset($sources_desc["attributs"]["status"]) ? "status" : "id_class",
            "titre" => isset($sources_desc["attributs"]["nom"]) ? "nom" : "titre",
          );
          $sql =
           "INSERT INTO #--sources(id, id_class, reference, titre, licence, date_creation, date_inscription)"
          ." SELECT"
          ."  id"
          .", ".$attribut_names["id_class"]
          .", reference"
          .", ".$attribut_names["titre"]
          .", licence"
          .", date_creation"
          .", date_inscription"
          ." FROM #--sources_tmp";
          try{
            $sgbd->query($sql);
          }
          catch(Exception $e){
            return "impossible d'importer sources_tmp dans sources";
          }
          try{
            $sgbd->query("DROP TABLE #--sources_tmp");
          }
          catch(Exception $e){
            return "impossible de supprimer la table sources_tmp";
          }
        }

        /* ----------------------------------------------------------------------
                                suppression de la table source_status_composition
        */

        if($sgbd->table_exists("#--source_status_composition")){
          try{
            $sgbd->query("DROP TABLE #--source_status_composition");
          }
          catch(Exception $e){
            return "impossible de supprimer la table source_status_composition";
          }
        }

      } // if($sgbd->data_exists("sources"))


      /* ###################################################################### */
      /* ###################################################################### */
      /* ----------------------------------------------------------------------
                   migration vers les tables en sml_(...) du plugin mw_sourceml
      */

      /* ----------------------------------------------------------------------
                                                    sources devient sml_sources
      */

      if($sgbd->table_exists("#--sources")){
        try{
          $sgbd->query("ALTER TABLE #--sources RENAME TO #--sml_sources");
        }
        catch(Exception $e){
          return "impossible de renommer sources en sml_sources";
        }
      }


      /* ----------------------------------------------------------------------
                                     source_groupes devient sml_sources_authors
                                                elt id_groupe devient id_author
                                 elt id_groupe_status devient id_sources_access
      */

      if($sgbd->table_exists("#--source_groupes")){
        if(
          !$data->data_create_table(
            array(
              "table_name" => "sml_sources_authors",
              "fields" => array(
                "id" => array("type" => "int", "autoincrement" => true),
                "id_source" => array("type" => "int"),
                "id_author" => array("type" => "int"),
                "id_sources_access" => array("type" => "int"),
              ),
              "keys" => array(
                array(
                  "fields" => array("id"),
                  "primary" => true
                ),
                array(
                  "fields" => array("id_source")
                ),
                array(
                  "fields" => array("id_author")
                ),
                array(
                  "fields" => array("id_sources_access")
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table sources";
        }
        $sql =
         "INSERT INTO #--sml_sources_authors(id_source, id_author, id_sources_access)"
        ." SELECT"
        ."  id_source"
        .", id_groupe"
        .", id_groupe_status"
        ." FROM #--source_groupes";
        try{
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible d'importer les derivation dans source_derivations";
        }
        try{
          $sgbd->query("DROP TABLE #--source_groupes");
        }
        catch(Exception $e){
          return "impossible de supprimer la table source_groupes";
        }
      }


      /* ----------------------------------------------------------------------
                                       groupe_status devient sml_sources_access
                                                            nouvel elt intitule
      */

      if($sgbd->table_exists("#--groupe_status")){
        if(
          !$data->data_create_table(
            array(
              "table_name" => "sml_sources_access",
              "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 sources";
        }
        try{
          $sql = "INSERT INTO #--sml_sources_access(id, nom, intitule) VALUES (1, 'admin', 'administrateur')";
          $sgbd->query($sql);
          $sql = "INSERT INTO #--sml_sources_access(id, nom, intitule) VALUES (2, 'editeur', 'éditeur')";
          $sgbd->query($sql);
          $sql = "INSERT INTO #--sml_sources_access(id, nom, intitule) VALUES (3, 'contributeur', 'contributeur')";
          $sgbd->query($sql);
        }
        catch(Exception $e){
          return "impossible de remplir la table sml_sources_access";
        }
        try{
          $sgbd->query("DROP TABLE #--groupe_status");
        }
        catch(Exception $e){
          return "impossible de supprimer la table groupe_status";
        }
      }

      /* ----------------------------------------------------------------------
                                                    groupes devient sml_authors
      */

      if($sgbd->table_exists("#--groupes")){
        try{
          $sgbd->query("ALTER TABLE #--groupes RENAME TO #--sml_authors");
        }
        catch(Exception $e){
          return "impossible de renommer groupes en sml_authors";
        }
      }

      /* ----------------------------------------------------------------------
                                                  licences devient sml_licences
      */

      if($sgbd->table_exists("#--licences")){
        try{
          $sgbd->query("ALTER TABLE #--licences RENAME TO #--sml_licences");
        }
        catch(Exception $e){
          return "impossible de renommer licences en sml_licences";
        }
      }

      /* ----------------------------------------------------------------------
                            source_compositions devient sml_source_compositions
      */

      if($sgbd->table_exists("#--source_compositions")){
        try{
          $sgbd->query("ALTER TABLE #--source_compositions RENAME TO #--sml_source_compositions");
        }
        catch(Exception $e){
          return "impossible de renommer source_compositions en sml_source_compositions";
        }
      }

      /* ----------------------------------------------------------------------
                              source_derivations devient sml_source_derivations
      */

      if($sgbd->table_exists("#--source_derivations")){
        try{
          $sgbd->query("ALTER TABLE #--source_derivations RENAME TO #--sml_source_derivations");
        }
        catch(Exception $e){
          return "impossible de renommer source_derivations en sml_source_derivations";
        }
      }

      /* ----------------------------------------------------------------------
                                          source_cache devient sml_source_cache
      */

      if($sgbd->table_exists("#--source_cache")){
        try{
          $sgbd->query("ALTER TABLE #--source_cache RENAME TO #--sml_source_cache");
        }
        catch(Exception $e){
          return "impossible de renommer source_cache en sml_source_cache";
        }
      }

      /* ----------------------------------------------------------------------
                                              source_status devient sml_classes
      */

      if($sgbd->table_exists("#--source_status")){
        try{
          $sgbd->query("ALTER TABLE #--source_status RENAME TO #--sml_classes");
        }
        catch(Exception $e){
          return "impossible de renommer source_status en sml_classes";
        }
      }

      /* ----------------------------------------------------------------------
                                         source_infos devient sml_sources_infos
      */

      if($sgbd->table_exists("#--source_infos")){
        try{
          $sgbd->query("ALTER TABLE #--source_infos RENAME TO #--sml_sources_infos");
        }
        catch(Exception $e){
          return "impossible de renommer source_infos en sml_sources_infos";
        }
      }

      /* ----------------------------------------------------------------------
                                              source_documents devient sml_source_documents
      */

      if($sgbd->table_exists("#--source_documents")){
        try{
          $sgbd->query("ALTER TABLE #--source_documents RENAME TO #--sml_source_documents");
        }
        catch(Exception $e){
          return "impossible de renommer source_documents en sml_source_documents";
        }
      }


      /* ----------------------------------------------------------------------
                                         nouvelle table sml_sources_invitations
      */

      if(!$sgbd->table_exists("#--sml_sources_invitations")){
        if(
          !$data->data_create_table(
            array(
              "table_name" => "sml_sources_invitations",
              "fields" => array(
                "id" => array("type" => "int", "autoincrement" => true),
                "id_user" => array("type" => "int"),
                "id_source" => array("type" => "int"),
                "id_author" => array("type" => "int"),
                "id_sources_access" => array("type" => "int"),
                "date_invitation" => array("type" => "datetime")
              ),
              "keys" => array(
                array(
                  "fields" => array("id"),
                  "primary" => true
                )
              ),
              "options" => array("default_charset" => "utf8")
            )
          )
        ){
          return "impossible de créer la table sml_sources_invitations";
        }
      }

      return true;
    }

  }