<?php

class Schema
{
    /**
     * variables description
     *
     * @dsn      dbConnectionResource
     * @dbName   databaseName
     * @tbPrefix tablesPrefix
     *
     * @schema existing database schema
     * @define defined database schema
     */
    private $dsn = null;
    private $dbName = null;
    private $tbPrefix = null;

    public $schema;
    public $define;

    public $addRam = [];
    public $changeRam = [];
    public $dropRam = [];

    /**
     * __construct
     *
     * @param array  $dsn
     * @param string $dbName
     * @param string $tbPrefix
     */
    public function __construct($dsn, $dbName, $tbPrefix)
    {
        $this->dsn = $dsn;
        $this->dbName = $dbName;
        $this->tbPrefix = $tbPrefix;

        $this->schema = $this->getDefDB();
    }

    /**
     * defLoadYaml データベース定義をYAMLからロードする
     *
     * @param  $type
     * @return array
     */
    public function defLoadYaml($type)
    {
        return Config::getDataBaseSchemaInfo($type);
    }

    /**
     * defSetYaml データベース定義をYAMLからセットする
     *
     * @param  $type
     * @return void
     */
    public function defSetYaml($type)
    {
        $this->define = Config::getDataBaseSchemaInfo($type);
        if (!empty($this->define[0])) {
            unset($this->define[0]);
        }
    }

    /**
     * compareTables 現在のDBと定義を比較して，差分のテーブル名を配列で返す
     *
     * @return array
     */
    public function compareTables()
    {
        $now_tbs = Schema::listUp($this->schema);
        $def_tbs = Schema::listUp($this->define);

        $haystack = [];
        foreach ($def_tbs as $tb) {
            if (array_search($tb, $now_tbs, true) === false) {
                $haystack[] = $tb;
            }
        }

        return $haystack;
    }

    /**
     * createTables テーブルを作成する
     *
     * @param  $tbs
     * @param  array $idx
     * @return array
     */
    public function createTables($tbs, $idx = null)
    {
        $createTableRsult = [];
        $DB = DB::singleton($this->dsn);
        $DB->setThrowException(true);

        foreach ($tbs as $tb) {
            $def = $this->define[$tb];

            $q = "CREATE TABLE $tb (\r\n";
            foreach ($def as $row) {
                $row['Null'] = (isset($row['Null']) && $row['Null'] == 'NO') ? 'NOT NULL' : 'NULL';
                $row['Default'] = !empty($row['Default']) ? "default '" . $row['Default'] . "'" : null;
                $row['Extra'] = isset($row['Extra']) ? $row['Extra'] : '';

                // Example: field_name var_type(11) NOT NULL default HOGEHOGE,\r\n
                $q .= $row['Field'] . ' ' . $row['Type'] . ' ' . $row['Null'] . ' ' . $row['Default'] . ' ' . $row['Extra'] . ",\r\n";
            }

            /**
             * if $idx is exists Generate KEYs
             */
            if (is_array($idx) && !empty($idx) && isset($idx[$tb])) {
                $keys = $idx[$tb];
                if (is_array($keys) && !empty($keys)) {
                    foreach ($keys as $key) {
                        $q .= $key . ",\r\n";
                    }
                }
            }
            $q = preg_replace('@,(\r\n)$@', '$1', $q);
            if (preg_match('/(fulltext|geo)$/', $tb)) {
                $q .= ")  ENGINE=MyISAM;";
            } else {
                $q .= ")  ENGINE=InnoDB;";
            }
            try {
                $res = $DB->query($q, 'exec');
                if ($res == false) {
                    throw new \RuntimeException($tb . 'テーブルの作成に失敗しました');
                }
                $createTableRsult[$tb] = true;
            } catch (\Exception $e) {
                $createTableRsult[$tb] = false;
            }
        }
        $this->reloadSchema();
        $DB->setThrowException(false);

        return $createTableRsult;
    }

    /**
     * resolveRenames 名前に変更のあったフィールドを解決する
     *
     * @param array $hist
     * @return void
     */
    public function resolveRenames($hist)
    {
        $now = $this->schema;
        $def = $this->define;

        foreach ($hist as $tb => $fds) {
            if (empty($fds) || empty($tb)) {
                continue;
            }

            $sein = Schema::listUp($now[$tb]);

            foreach ($fds as $k => $v) {
                if (in_array($k, $sein, true)) {
                    $val = $def[$tb][$v];
                    $this->rename($tb, $k, $val, $v);
                }
            }
        }

        $this->reloadSchema();
    }

    /**
     * compareColumns カラム定義の違いを走査して，addRamとchangeRamに記録する
     *
     * @param string $tb
     * @return void
     */
    public function compareColumns($tb)
    {
        if (!isset($this->schema[$tb])) {
            return;
        }
        if (!isset($this->define[$tb])) {
            return;
        }

        $now = $this->schema[$tb];
        $def = $this->define[$tb];

        if (empty($def)) {
            return;
        }

        $def_fds = Schema::listUp($def);
        foreach ($def_fds as $key) {
            /**
             * ALTER TABLE ADD
             * is not exists living list
             */
            if (empty($now[$key])) {
                $this->addRam[] = $key;
                continue;
            }

            /**
             * ALTER TABLE CHANGE
             * is not equal column
             *   *
             * EXCEPT INDEX KEY
             */
            unset($now[$key]['Key']);
            unset($def[$key]['Key']);

            if ($now[$key] != $def[$key]) {
                $this->changeRam[] = $key;
                continue;
            }
        }
    }

    /**
     * resolveColumns compareColumns走査済みのすべてのカラムを追加・変更する
     *
     * @param  string $tb
     * @return void
     */
    public function resolveColumns($tb)
    {
        if (!isset($this->define[$tb])) {
            return;
        }

        $def = $this->define[$tb];
        $lis = Schema::listUp($def);

        /**
         * ADD
         */
        if (!empty($this->addRam)) {
            foreach ($this->addRam as $key) {
                $after = array_slice($lis, 0, (array_search($key, $lis, true)));
                $after = end($after);
                $this->add($tb, $key, $def[$key], $after);
            }
            $this->addRam = [];
        }

        /**
         * CHANGE
         */
        if (!empty($this->changeRam)) {
            foreach ($this->changeRam as $key) {
                $this->change($tb, $key, $def[$key]);
            }
            $this->changeRam = [];
        }

        $this->reloadSchema();
    }

    /**
     * disusedColumns 定義外の未使用カラムを走査して，dropRamに保存する
     *
     * @param  string $tb
     * @return string[]
     */
    public function disusedColumns($tb)
    {
        if (!isset($this->schema[$tb])) {
            return [];
        }
        if (!isset($this->define[$tb])) {
            return [];
        }

        $now = $this->schema[$tb];
        $def = $this->define[$tb];

        $now_fds = Schema::listUp($now);
        $dropRam = [];
        foreach ($now_fds as $key) {
            /**
             * ALTER TABLE DROP ( TEMP )
             * is not exists defined list
             */
            if (empty($def[$key])) {
                $dropRam[] = $key;
                continue;
            }
        }
        return $dropRam;
    }

    /**
     * dropColumns 指定したテーブルのカラムを削除する
     *
     * @param string $tb
     * @param string[] $columns
     * @return void
     */
    public function dropColumns($tb, $columns = [])
    {
        if (isset($this->schema[$tb]) && count($this->schema[$tb]) === count($columns)) {
            // drop table
            $this->dropTable($tb);
        } else {
            // drop columns
            foreach ($columns as $column) {
                $this->drop($tb, $column);
            }
        }
        $this->reloadSchema();
    }

    /**
     * テーブルを削除
     *
     * @param string $tb
     * @return void
     */
    public function dropTable($tb)
    {
        $DB = DB::singleton($this->dsn);
        $q = "DROP TABLE IF EXISTS `$tb`";
        $DB->query($q, 'exec');
    }

    /**
     * existsDefinedTable
     *
     * @return bool
     */
    public function existsDefinedTable()
    {
        $now = Schema::listUp($this->schema);
        $def = Schema::listUp($this->define);

        if (empty($now)) {
            return false;
        }

        foreach ($now as $tb) {
            if (array_search($tb, $def, true) !== false) {
                return true;
            }
        }
        return false;
    }

    /**
     * clearIndex 指定されたテーブルのインデックスをすべて削除する
     *
     * @param  string $tb
     * @return bool
     */
    public function clearIndex($tb)
    {
        $idx = $this->showIndex($tb);
        if (empty($idx)) {
            return true;
        }

        $ary = [];
        foreach ($idx as $i) {
            $ary[] = $i['Key_name'];
        }

        foreach ($ary as $key) {
            $DB = DB::singleton($this->dsn);
            $q = "ALTER TABLE `$tb` DROP INDEX `$key`";
            $DB->query($q, 'exec');
        }

        return true;
    }

    /**
     * 現在のデータベースインデックス定義を取得
     *
     * @return array
     */
    public function getDatabaseIndexCurrent()
    {
        $tables = $this->getTables();
        if (!is_array($tables)) {
            return [];
        }

        $def = [];
        foreach ($tables as $table) {
            $columns = $this->getIndex($table);
            $def[$table] = $columns;
        }
        return $def;
    }

    /**
     * テーブルのインデックスの取得
     *
     * @param $table string
     * @return array
     */
    public function getIndex($table)
    {
        $DB = DB::singleton($this->dsn);
        $q = "SHOW INDEX FROM `{$table}`";
        $DB->query($q, 'fetch');

        $index = [];
        while ($fd = $DB->fetch($q)) {
            $index[] = $fd['Key_name'];
        }
        $index = array_values(array_unique($index));
        return $index;
    }

    /**
     * テーブル一覧の取得
     *
     * @return array
     */
    public function getTables()
    {
        $DB = DB::singleton($this->dsn);
        $q = "SHOW TABLES FROM `" . $this->dsn['name'] . "` LIKE '" . $this->dsn['prefix'] . "%'";
        $DB->query($q, 'fetch');

        $tables = [];
        while ($tb = $DB->fetch($q)) {
            $tables[] = implode($tb);
        }
        return $tables;
    }

    /**
     * インデックス定義の違いを走査
     *
     * @param string $table
     *
     * @return mixed
     */
    public function compareIndex($table, $dbIndex, $newIndex)
    {
        $currentIndex = [];
        $updateIndex = [];
        $resultIndex = [];
        if (isset($dbIndex[$table])) {
            $currentIndex = $dbIndex[$table];
        }
        if (isset($newIndex[$table])) {
            $updateIndex = $newIndex[$table];
        }
        foreach ($updateIndex as $new) {
            foreach ($currentIndex as $old) {
                $key = "/KEY\s+$old(\s|\()/i";
                if (preg_match($key, $new)) {
                    continue 2;
                }
                if ($old === 'PRIMARY' && preg_match('/PRIMARY/i', $new)) {
                    continue 2;
                }
            }
            $resultIndex[] = $new;
        }
        return $resultIndex;
    }

    /**
     * インデックスを作成する
     *
     * @param string $table
     * @param array $res
     */
    public function makeIndex($table, $res)
    {
        if (empty($res)) {
            return;
        }
        $DB = DB::singleton($this->dsn);
        foreach ($res as $index) {
            if (!preg_match('/^PRIMARY\sKEY/', $index)) {
                $q = "ALTER TABLE `$table` ADD $index";
                $DB->query($q, 'exec');
            }
        }
    }

    /**
     * listUp 配列のキーを返す・空配列は除かれる
     *
     * @param $ary
     * @return array
     */
    public static function listUp($ary)
    {
        if (empty($ary)) {
            return [];
        }

        return array_merge(array_diff(array_keys($ary), ['']));
    }

    /**
     * errLog エラーをダンプする
     *
     * @return void
     */
    public function errLog()
    {
        die(DB::errorCode() . ': ' . DB::errorInfo());
    }

    /**
     * alterTable カラム定義の変更を適用する
     *
     * @param string $method
     * @param string $tb
     * @param string $left
     * @param array $def カラム定義
     * @param string $right
     * @return void
     */
    private function alterTable($method, $tb, $left, $def = [], $right = null)
    {
        $q = "ALTER TABLE `$tb`";

        $def['Null'] = (!empty($def['Null']) && $def['Null'] == 'NO') ? 'NOT NULL' : 'NULL';
        $def['Default'] = !empty($def['Default']) ? "default '" . $def['Default'] . "'" : null;

        switch ($method) {
            case 'add':
                $q .= " ADD";
                $q .= " `" . $left . "` " . $def['Type'] . " " . $def['Null'] . " " . $def['Default'] . " AFTER " . " `" . $right . "`";
                break;
            case 'change':
                // カラムのサイズ変更で現行サイズより小さい場合は処理をスキップ
                if (preg_match('/^[a-z]+\((\d+)\)/', $def['Type'], $match)) {
                    $cq = "SHOW COLUMNS FROM " . $tb . " LIKE '" . $left . "'";
                    $DB = DB::singleton($this->dsn);
                    $DB->query($cq, 'fetch');
                    $size = $match[1];

                    if ($row = $DB->fetch($cq)) {
                        $type = $row['Type'];
                        if (preg_match('/^[a-z]+\((\d+)\)/', $type, $match)) {
                            $csize = $match[1];
                            if (intval($size) < intval($csize)) {
                                break;
                            }
                        }
                    }
                }
                $q .= " CHANGE";
                $q .= " `" . $left . "` `" . $left . "` " . $def['Type'] . " " . $def['Null'] . " " . $def['Default'];
                break;
            case 'rename':
                $q .= " CHANGE";
                $q .= " `" . $left . "` `" . $right . "` " . $def['Type'] . " " . $def['Null'] . " " . $def['Default'];
                break;
            case 'drop':
                $q .= " DROP";
                $q .= " `" . $left . "`";
        }

        $DB = DB::singleton($this->dsn);
        $DB->query($q, 'exec');
    }

    /**
     * add
     *
     * @param  string $tb
     * @param  string $left
     * @param  array  $def
     * @param  string $after
     * @return void
     */
    private function add($tb, $left, $def, $after)
    {
        $this->alterTable('add', $tb, $left, $def, $after);
    }

    /**
     * change
     *
     * @param  string $tb
     * @param  string $left
     * @param  array  $def
     * @return void
     */
    private function change($tb, $left, $def)
    {
        $this->alterTable('change', $tb, $left, $def);
    }

    /**
     * rename
     *
     * @param  string $tb
     * @param  string $left
     * @param  array  $def
     * @param  string $right
     * @return void
     */
    private function rename($tb, $left, $def, $right)
    {
        $this->alterTable('rename', $tb, $left, $def, $right);
    }

    /**
     * drop
     *
     * @param  string $tb
     * @param  string $left
     * @return void
     */
    private function drop($tb, $left)
    {
        $this->alterTable('drop', $tb, $left);
    }

    /**
     * reloadSchema
     *
     * @return void
     */
    private function reloadSchema()
    {
        $this->schema = $this->getDefDB();
    }

    /**
     * getDefDB
     *
     * @return array
     */
    private function getDefDB()
    {
        $tbs = $this->showTables();
        if (!is_array($tbs)) {
            return [];
        }

        $def = [];
        foreach ($tbs as $tb) {
            $fds = $this->showColumns($tb);
            $def[$tb] = $fds;
        }

        return $def;
    }

    /**
     * showTables
     *
     * @return array
     */
    private function showTables()
    {
        $DB = DB::singleton($this->dsn);
        $q = "SHOW TABLES FROM `$this->dbName` LIKE '$this->tbPrefix%'";
        $DB->query($q, 'fetch');

        $tbs = [];
        while ($tb = $DB->fetch($q)) {
            $tbs[] = implode($tb);
        }
        return $tbs;
    }

    /**
     * showColumns
     *
     * @param  string $tb
     * @return array
     */
    private function showColumns($tb)
    {
        $DB = DB::singleton($this->dsn);
        $q = "SHOW COLUMNS FROM `$tb`";
        $DB->query($q, 'fetch');

        $fds = [];
        while ($fd = $DB->fetch($q)) {
            $fds[$fd['Field']] = $fd;
        }

        return $fds;
    }

    /**
     * showIndex
     *
     * @param string $tb
     * @return array
     */
    private function showIndex($tb)
    {
        $DB = DB::singleton($this->dsn);
        $q = "SHOW INDEX FROM `$tb`";
        $DB->query($q, 'fetch');

        $fds = [];
        while ($fd = $DB->fetch($q)) {
            $fds[] = $fd;
        }

        return $fds;
    }
}
