#!/usr/bin/php
<?php
/**
 * php-database-migration is a database migration script
 * management inspired by rake and myBatis
 * 
 * @category Database_Migration
 * @package  php-database-migration
 * @author   Alexandre GUIDET <alwex@free.fr>
 * @license  http://opensource.org/licenses/gpl-license.php GNU Public License
 * @link     https://github.com/alwex/php-database-migration
 *
 *
 */
const UP = "up";
const DOWN = "down";
const ENV_PATH = "environments";
const MIGRATIONS_PATH = "migrations";
const DEFAULT_ENVIRONMENT = "development";
const DEFAULT_DRIVER = "pgsql";
const DEFAULT_DATABASE = "mybdd";
const DEFAULT_HOST = "localhost";
const DEFAULT_LOGIN = "login";
const DEFAULT_PASSWORD = "password";
const DEFAULT_CHANGELOG_TABLE = "changelog";
const DEFAULT_NOTIFICATION_EMAIL_SUBJECT = "SQL scripts applied";

/**
 * Main class
 * Handle parameters and do all the work
 */
class MigrationManager
{

    /**
     * default classic environment list
     * @var array
     */
    private $_environments = array(
        "development",
        "preproduction",
        "production",
    );

    /**
     * default changelog table
     * @var string
     */
    private $_changelogTable = "changelog";

    /**
     * migrations file list
     * @var type
     */
    private $_migrationFileList = array();

    /**
     * migrations id list
     * @var type
     */
    private $_migrationIdList = array();

    /**
     * allowed options
     * @var type
     */
    private $_longOpts = array(
        // init required options
        "init::",
        "driver:",
        "database:",
        "host:",
        "login:",
        "password:",
        "changelog:",
        // other actions
        "status::",
        "generate:",
        "up::",
        "down::",
        "env:",
        "force::",
        "transactional::",
    );
    private $_shortOpts = "";
    private $_config;
    private $_options = array();

    /**
     * @var PDO
     */
    private $_db;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->_options = getopt($this->_shortOpts, $this->_longOpts);

        if (!$this->_validate()) {
            $this->_showUsage();
        } else {
            $this->_process();
        }
    }

    /**
     * Destructor
     */
    public function __destruct()
    {

    }

    /**
     * initialize environments configuration files
     * and migration directory
     *
     * @return void
     */
    private function _init()
    {

        if (is_dir(ENV_PATH)) {
            $this->_showWarning("Already initialized");
        } else if (!isset($this->_options["driver"])
                || !isset($this->_options["database"])
                || !isset($this->_options["host"])
                || !isset($this->_options["login"])
                || !isset($this->_options["password"])
                || !isset($this->_options["changelog"])) {
            $initUsage = <<< INIT_USAGE
Example:
    
./migrate --init --driver=<driver> --database=<database_name> --host=<host> --login=<database_login> --password=<database_password> --changelog=<changelog_table>

INIT_USAGE;
            $this->_showNotice($initUsage);
        } else {
            $configurationTemplate = <<< CONFIGURATION_TEMPLATE
; BDD %s configuration
url="%s:dbname=%s;host=%s"
username=%s
password=%s

; notification email for SQL migrations
; uncomment the line below for activation
; notification_email=
; notification_email_subject=

; name of the changelog table
; id | applied_at | description
; create table %s (id numeric(20,0), applied_at character varying(25), description character varying(255));
changelog=%s
CONFIGURATION_TEMPLATE;

            if (!is_dir(MIGRATIONS_PATH)) {
                mkdir(MIGRATIONS_PATH);
            }

            if (!is_dir(ENV_PATH)) {
                mkdir(ENV_PATH);
                foreach ($this->_environments as $environment) {
                    file_put_contents(ENV_PATH . "/" . $environment . ".ini", sprintf(
                                    $configurationTemplate, $environment, $this->_options["driver"], $this->_options["database"], $this->_options["host"], $this->_options["login"], $this->_options["password"], $this->_options["changelog"], $this->_options["changelog"]
                            )
                    );
                }
            }

            $initializationDone = <<<INIT_DONE
======= Initialization =======

Configuration files created:
  ./environments
         |----development.ini
         |----preproduction.ini
         `----production.ini

Migration directory created:
   ./migrations

You may modify the environemnts configuration files
and create the changelog table %s

create table %s (id numeric(20,0), applied_at character varying(25), description character varying(255));

INIT_DONE;
            $this->_showNotice(sprintf($initializationDone, $this->_options["changelog"], $this->_options["changelog"]));
        }
    }

    /**
     * validate command line parameters
     * 
     * @return boolean
     */
    private function _validate()
    {
        if (isset($this->_options['init'])) {
            return true;
        }

        if (isset($this->_options["generate"])) {
            return true;
        }

        if (isset($this->_options["up"])) {
            return true;
        }

        if (isset($this->_options["down"])) {
            return true;
        }

        if (isset($this->_options["status"])) {
            return true;
        }

        return false;
    }

    /**
     * parse parameters and return the
     * correct action name to be processed
     *
     * @return string
     */
    private function _getAction()
    {
        $action = "";
        if (isset($this->_options["init"])) {
            $action = "init";
        } else if (isset($this->_options["generate"])) {
            $action = "generate";
        } else if (isset($this->_options["up"])) {
            $action = "up";
        } else if (isset($this->_options["down"])) {
            $action = "down";
        } else if (isset($this->_options["status"])) {
            $action = "status";
        }

        return $action;
    }

    /**
     * initialize the database connection
     * in order to process requested action
     *
     * @return void
     */
    private function _connect()
    {
        try {
            if (isset($this->_config["password"]) && $this->_config["password"] != null && $this->_config["password"] != "") {
                $this->_db = new PDO($this->_config["url"], $this->_config["username"], $this->_config["password"]);
            }
            else {
                $this->_db = new PDO($this->_config["url"], $this->_config["username"]);
            }
        } catch (PDOException $e) {
            $this->_showError($e->getMessage());
            exit();
        }
    }

    /**
     * load configuration file
     * 
     * @return void
     */
    private function _config()
    {
        $env = DEFAULT_ENVIRONMENT;
        if (isset($this->_options["env"])) {
            if (!file_exists(ENV_PATH . "/" . $this->_options["env"] . ".ini")) {
                exit(ENV_PATH . "/" . $this->_options["env"] . ".ini not found\n");
            } else {
                $env = $this->_options["env"];
            }
        }

        $this->_options["env"] = $env;
        $this->_config = parse_ini_file(ENV_PATH . "/" . $env . ".ini");
        $this->_changelogTable = $this->_config["changelog"];
    }

    /**
     * show some help
     *
     * @return void
     */
    private function _showUsage()
    {
        $usage = <<<USAGE
No command specified.

Usage: ./migrate command [parameters] [--env=<environment>]

Commands:
  --env=<environment>       Environment to configure. Default environment is 'dev'.
  --generate <description>  Creates a new migration with the provided description.
  --up                      Run unapplied migrations, ALL by default.
  --up=<version>            Run unapplied migrations up to version (included).
  --down                    Undoes migrations applied to the database. ONE by default.
  --down=<version>          Undoes migrations applied to the database. Down to version (included).
  --force                   Run or undoes only specified migration (not recommended).
  --transactional           Rollback all applied migration up or down on error.
  --status                  Show migrations status (applied, unapplied ect...).

Examples:
./migrate [--generate <migration_name>]
./migrate [--up | --up=<version> | --down | --down=<version>] [--transactional] [--force] [--env=<environment>]
./migrate [--status] [--env=<environment>]

USAGE;

        $this->_showNotice($usage);
    }

    /**
     * process the requested action
     * handle all possibility, if an action
     * must be added, it may be done here
     *
     * @return void
     */
    private function _process()
    {
        switch ($this->_getAction($this->_options)) {
            case "init":
                $this->_init();
                break;
            case "generate":
                $this->_processActionGenerate($this->_options["generate"]);
                break;
            case "up":
                $this->_config();
                $this->_loadMigrationList();
                $this->_connect();
                $this->_processActionUp();
                break;
            case "down":
                $this->_config();
                $this->_loadMigrationList();
                $this->_connect();
                $this->_processActionDown();
                break;
            case "status":
                $this->_config();
                $this->_loadMigrationList();
                $this->_connect();
                $this->_processActionStatus();
                break;
            default:
                $this->_showError("unknown action");
                exit(2);
        }
    }

    /**
     * return the migration status at the
     * time action is requested
     *
     * @return void
     */
    private function _processActionStatus()
    {
        $migrationsPending = array();
        foreach ($this->_migrationFileList as $migrationId => $aMigration) {
            $migrationsPending[$migrationId]["id"] = $migrationId;
            $migrationsPending[$migrationId]["applied_at"] = "Pending...         ";
            $migrationDesctiption = str_replace($migrationId . "_", "", $aMigration);
            $migrationDesctiption = str_replace("_", " ", $migrationDesctiption);
            $migrationDesctiption = str_replace(".sql", "", $migrationDesctiption);
            $migrationsPending[$migrationId]["description"] = $migrationDesctiption;
        }


        $migrationsDone = $this->_db->query("SELECT id, applied_at, description FROM " . $this->_changelogTable . " ORDER BY id");
        if (false === $migrationsDone) {
            $migrationsDone = array();
        }

        foreach ($migrationsDone as $aMigrationDone) {
            $migrationsPending[$aMigrationDone["id"]] = $aMigrationDone;
        }
        ksort($migrationsPending);

        echo "\n";
        echo "ID              Applied At           description\n";
        echo "=====================================================================================\n";
        foreach ($migrationsPending as $row) {
            echo $row["id"] . "  ";
            echo $row["applied_at"] . "  ";
            echo $row["description"] . "\n";
        }
        echo "\n";
    }

    /**
     * generate migration script
     * 
     * @param type $fileName script
     * 
     * @return void
     */
    private function _processActionGenerate($fileName)
    {
        $template = <<<TEMPLATE
--// %s
-- Migration SQL that makes the change goes here.

-- @UNDO
-- SQL to undo the change goes here.

TEMPLATE;
        $timestamp = date("YmdHis");
        $realFileName = "migrations/" . $timestamp . "_" . $fileName . ".sql";
        file_put_contents($realFileName, sprintf($template, str_replace("_", " ", $fileName)));

        $this->_showSuccess("migration: " . $realFileName . "\n");
    }

    /**
     * handle the up action
     *
     * @return void
     */
    private function _processActionUp()
    {
        $upTo = $this->_options["up"];

        $result = $this->_db->query("SELECT id FROM " . $this->_changelogTable . " ORDER BY id");

        $alreadyDone = array();
        if ($result) {
            foreach ($result as $row) {
                $alreadyDone[$row["id"]] = $row["id"];
            }
        }

        // on controle que la migration indiqué n'a pas déjà été jouée
        // si c'est le cas, on sort du programme
        if (false !== $upTo && in_array($upTo, $alreadyDone)) {
            $this->_showWarning("migration already done");
            exit(2);
        }

        $toMigrate = array_diff_key($this->_migrationFileList, $alreadyDone);
        ksort($toMigrate);

        // si le paramètre force à été fournis, il ne faut alors
        // jouer QUE la migration indiquée
        if (false !== $upTo && isset($this->_options["force"])) {
            $this->_migrate($upTo, $toMigrate[$upTo], UP);
        } else {
            if (isset($this->_options["transactional"])) {
                $this->_db->beginTransaction();
            }

            try {
                $mailBody = "";
                foreach ($toMigrate as $migrationId => $aMigrationFile) {

                    $this->_migrate($migrationId, $aMigrationFile, UP);
                    // si un numéro de migration à été fournis
                    // il faut arrêter la migration à ce numéro de version
                    if (false !== $upTo && $migrationId === $upTo) {
                        break;
                    }

                    $mailBody .= "\n-- MIGRATION ID " . $migrationId . "\n\n";         
                    $mailBody .= $this->_getUpScriptForMigration($aMigrationFile);

                }
                
                

                if (isset($this->_options["transactional"])) {
                    $this->_db->commit();
                }
                
                // envois de l'email de notification si configuré dans .ini
                if (isset($this->_config["notification_email"]) && $this->_config["notification_email"] != null) {
                    $notificationEmailSubject = DEFAULT_NOTIFICATION_EMAIL_SUBJECT;
                    if (isset($this->_config["notification_email_subject"]) && $this->_config["notification_email_subject"] != null) {
                        $notificationEmailSubject = $this->_config["notification_email_subject"];
                    }
                    mail($this->_config["notification_email"], "[" . $this->_options["env"] . "] " . $notificationEmailSubject, $mailBody);
                }
                
            } catch (Exception $e) {
                if (isset($this->_options["transactional"])) {
                    $this->_db->rollBack();
                    $this->_showError("FAILURE !!! rollback all migrations");
                }
            }
        }
    }

    /**
     * handle the down action
     * 
     * @return void
     */
    private function _processActionDown()
    {
        $downTo = $this->_options["down"];
        $result = $this->_db->query("SELECT id FROM " . $this->_changelogTable . " ORDER BY id DESC");

        $alreadyDone = array();
        foreach ($result as $row) {
            $alreadyDone[$row["id"]] = $row["id"];
        }

        // on controle que la migration indiqué n'a pas déjà été jouée
        // si c'est le cas, on sort du programme
        if (false !== $downTo && !in_array($downTo, $alreadyDone)) {
            $this->_showWarning("migration already pending");
            exit(2);
        }


        // si le paramètre force à été fournis, il faut alors
        // annuler QUE la migration indiquée
        if (false !== $downTo && isset($this->_options["force"])) {
            $this->_migrate($downTo, $this->_migrationFileList[$downTo], DOWN);
        } else {
            if (isset($this->_options["transactional"])) {
                $this->_db->beginTransaction();
            }

            try {
                foreach ($alreadyDone as $migrationId => $migration) {
                    $toRollback = $this->_migrationFileList[$migrationId];
                    $this->_migrate($migrationId, $toRollback, DOWN);
                    if (false === $downTo || $migrationId === $downTo) {
                        break;
                    }
                }

                if (isset($this->_options["transactional"])) {
                    $this->_db->commit();
                }
            } catch (Exception $e) {
                if (isset($this->_options["transactional"])) {
                    $this->_db->rollBack();
                    $this->_showError($e->getMessage());
                    $this->_showError("FAILURE !!! rollback all migrations");
                } else {
                    $this->_showError($e->getMessage());
                    exit();
                }
            }
        }
    }

    /**
     * process the migration id up or down
     * 
     * @param type $migrationId   id of the migration
     * @param type $migrationFile migration file name
     * @param type $upOrDown      is it up or down?
     *
     * @return void
     */
    private function _migrate($migrationId, $migrationFile, $upOrDown)
    {
        $fileContent = file_get_contents(MIGRATIONS_PATH . "/" . $migrationFile);
        $upDown = explode("-- @UNDO", $fileContent);
        try {
            if (count($upDown) != 2) {
                throw new Exception("INVALID MIGRATION FILE: " . $migrationFile);
            }

            $sql = "";
            if (!isset($this->_options["transactional"])) {
                $this->_db->beginTransaction();
            }
            switch ($upOrDown) {
                case UP:
                    $migrationDesctiption = str_replace($migrationId . "_", "", $migrationFile);
                    $migrationDesctiption = str_replace("_", " ", $migrationDesctiption);
                    $migrationDesctiption = str_replace(".sql", "", $migrationDesctiption);

                    $this->_showNotice("============= UP " . $migrationId . " =============");
                    $sql = trim($upDown[0]);
                    $this->_showNotice($sql);

                    if (false === $this->_db->exec($sql)) {
                        $errorInfo = "";
                        $pdoError = $this->_db->errorInfo();
                        foreach ($pdoError as $errorLine) {
                            $errorInfo .= $errorLine . "\n";
                        }
                        throw new Exception("UP FAILURE !!!!!\n\n" . $errorInfo);
                    }
                    $this->_db->query("INSERT INTO " . $this->_changelogTable . " (id, applied_at, description) values (" . $migrationId . ", '" . date("Y-m-d H:m:s") . "', '" . $migrationDesctiption . "')");
                    $this->_showSuccess("UP SUCCESS");
                    break;
                case DOWN:
                    $this->_showNotice("============= DOWN " . $migrationId . " =============");
                    $sql = trim($upDown[1]);
                    $this->_showNotice($sql);

                    if (false === $this->_db->exec($sql)) {
                        throw new Exception("DOWN FAILURE !!!!!");
                    }

                    $this->_db->query("DELETE FROM " . $this->_changelogTable . " WHERE id = " . $migrationId);
                    $this->_showSuccess("DOWN SUCCESS");
                    break;
            }

            if (!isset($this->_options["transactional"])) {
                $this->_db->commit();
            }
        } catch (Exception $e) {
            if (!isset($this->_options["transactional"])) {
                $this->_showError($e->getMessage());
                $this->_db->rollBack();
            } else {
                throw $e;
            }
        }
    }
    
    public function _getUpScriptForMigration($migrationFile)  {
        $fileContent = file_get_contents(MIGRATIONS_PATH . "/" . $migrationFile);
        $upDown = explode("-- @UNDO", $fileContent);
        return $upDown[0];
    }
    
    public function _getDownScriptForMigration($migrationFile)  {
        $fileContent = file_get_contents(MIGRATIONS_PATH . "/" . $migrationFile);
        $upDown = explode("-- @UNDO", $fileContent);
        return $upDown[1];
    }

    /**
     * load all available migrations files
     * 
     * @return void
     */
    private function _loadMigrationList()
    {
        $migrationList = array();
        $migrationIdList = array();
        $migrationFileList = array();

        if ($handle = opendir(MIGRATIONS_PATH)) {
            while (false !== ($file = readdir($handle))) {
                if (!is_dir($file)) {
                    $tempId = explode("_", $file);
                    $migrationIdList[$tempId[0]] = $tempId[0];
                    $migrationFileList[$tempId[0]] = $file;
                }
            }
        }

        if (!empty($migrationFileList)) {
            ksort($migrationFileList);
            ksort($migrationIdList);
        }
        $this->_migrationFileList = $migrationFileList;
        $this->_migrationIdList = $migrationIdList;
    }

    /**
     * echo error message
     *
     * @param type $message the message to show
     *
     * @return void
     */
    private function _showError($message)
    {
        echo "\033[1;31m";
        echo "\n" . $message . "\n";
        echo "\033[0m";
    }

    /**
     * echo success message
     *
     * @param type $message the message to show
     *
     * @return void
     */
    private function _showSuccess($message)
    {
        echo "\033[1;32m";
        echo "\n" . $message . "\n";
        echo "\033[0m";
    }

    /**
     * echo warning message
     *
     * @param type $message the message to show
     *
     * @return void
     */
    private function _showWarning($message)
    {
        echo "\033[1;33m";
        echo "\n" . $message . "\n";
        echo "\033[0m";
    }

    /**
     * echo notice message
     *
     * @param type $message the message to show
     * 
     * @return void
     */
    private function _showNotice($message)
    {
        echo "\n" . $message . "\n";
    }

}

$migrationManager = new MigrationManager($argv, $argc);

