#!/usr/bin/php
<?php
//   This file is a part of CodingTeam. Take a look at <http://codingteam.org>.
//   Copyright © 2007-2014 Erwan Briand <erwan@codingteam.net>
//
//   This program is free software: you can redistribute it and/or modify it
//   under the terms of the GNU Affero General Public License as published by
//   the Free Software Foundation, version 3 only.
//
//   This program is distributed in the hope that it will be useful, but
//   WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
//   or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public
//   License for more details.
//
//   You should have received a copy of the GNU Affero General Public License
//   along with this program. If not, see <http://www.gnu.org/licenses/>.

/* Upgrade from 0.9.4 to 1.0RC1
 *
 * This script updates the SQL tables schema.
 *
 * http://codingteam.net/project/codingteam/doc/HowToUpgrade
 */

// But where are we?
$base = dirname(__FILE__);
$whereis = '/scripts/upgrade';
$length = -mb_strlen($whereis);
$basedir = mb_substr($base, 0, $length);
define('CT_BASEDIR', $basedir);

// Check if the configuration file exist
if (!file_exists($basedir.'/inc/codingteam.cfg'))
    die ('There are no configuration file. CodingTeam cannot start.');

// Read the configuration and connect to the database layer
$xml = simplexml_load_file($basedir.'/inc/codingteam.cfg');
$db_type = $xml->db->type;
$db_hostname = $xml->db->hostname;
$db_database = $xml->db->database;
$db_username = $xml->db->username;
$db_password = $xml->db->password;

// Database connection
require($basedir.'/inc/classes/db.php');
$ct_db = new Database($db_type, $db_hostname, $db_database, $db_username,
                                                            $db_password);

// New SQL configuration keys

// Update table schemas
$rules = array(
  'ALTER TABLE `projects_bugs` ADD `id_projectunique` int(11) DEFAULT NULL AFTER `id`',
  'ALTER TABLE `projects_bugs` CHANGE `assignedid` `assignedid` int(11) DEFAULT NULL',

  'DELIMITER $$
CREATE TRIGGER `unique_project_id` BEFORE INSERT ON `projects_bugs` FOR EACH ROW BEGIN

DECLARE last_project_unique_id INT;

SELECT id_projectunique INTO last_project_unique_id FROM projects_bugs WHERE projectid = NEW.projectid ORDER BY id DESC LIMIT 1;

IF ISNULL(last_project_unique_id) THEN
SET last_project_unique_id = 0;
END IF;

SET NEW.id_projectunique = last_project_unique_id + 1;

END
$$
DELIMITER ;'
);

foreach ($rules as $rule)
{
    $rs = $ct_db->dbclass->prepare($rule);
    $rs->execute();
}

// Create unique project's id for bug reports
$projects_identifier = array();
$rq = $ct_db->dbclass->prepare('SELECT id, id_projectunique, projectid FROM projects_bugs ORDER BY id ASC');
$rq->execute();
$data = $rq->fetchAll(PDO::FETCH_ASSOC);

foreach ($data as $item)
{
    if (!array_key_exists($item['projectid'], $projects_identifier))
        $projects_identifier[$item['projectid']] = 0;

    $new_project_identifier = $projects_identifier[$item['projectid']] + 1;

    // Set the new project's unique identifier
    $rs = $ct_db->dbclass->prepare('UPDATE `projects_bugs` SET `id_projectunique` = '.$new_project_identifier.' WHERE `id` = '.$item['id']);
    $rs->execute();

    $projects_identifier[$item['projectid']] = $new_project_identifier;
}
?>