<?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と定義を比較して，差分のテーブル名を配列で返す
     *
     * @param  null $now
     * @param  null $def
     * @return array
     */
    public function compareTables($now = null, $def = null)
    {
        $now = empty($now) ? $this->schema : $now;
        $def = empty($def) ? $this->define : $def;

        $now_tbs = Schema::listUp($now);
        $def_tbs = Schema::listUp($def);

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

        return $haystack;
    }

    /**
     * createTables テーブルを作成する
     *
     * @param  $tbs
     * @param  bool $idx
     * @return void
     */
    public function createTables($tbs, $idx = null)
    {
        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;

                // Example: field_name var_type(11) NOT NULL default HOGEHOGE,\r\n
                $q .= $row['Field'] . ' ' . $row['Type'] . ' ' . $row['Null'] . ' ' . $row['Default'] . ",\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;";
            }

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

            if ($res == false) {
                $this->errLog();
            }
        }

        $this->reloadSchema();
    }

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

        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)) {
                    $val = $def[$tb][$v];
                    $this->rename($tb, $k, $val, $v);
                }
            }
        }

        $this->reloadSchema();
    }

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

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

        $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;
            }
        }

        return true;
    }

    /**
     * resolveColumns compareColumns走査済みのすべてのカラムを追加・変更する
     *
     * @param  string $tb
     * @param  null   $now
     * @param  null   $def
     * @return void
     */
    public function resolveColumns($tb, $now = null, $def = null)
    {
        if (isset($def[$tb])) {
            $def = $def[$tb];
        } elseif (empty($def) && isset($this->define[$tb])) {
            $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)));
                $after = end($after);
                $this->add($tb, $key, $def[$key], $after);
            }
            $this->addRam = array();
        }

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

        $this->reloadSchema();
    }

    /**
     * disusedColumns 定義外の未使用カラムを走査して，dropRamに保存する
     *
     * @param  string $tb
     * @param  null   $now
     * @param  null   $def
     * @return string[]
     */
    public function disusedColumns($tb, $now = null, $def = null)
    {
        if (isset($now[$tb])) {
            $now = $now[$tb];
        } elseif (empty($now) && isset($this->schema[$tb])) {
            $now = $this->schema[$tb];
        }
        if (isset($def[$tb])) {
            $def = $def[$tb];
        } elseif (empty($def) && isset($this->define[$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(string $tb, array $columns = [])
    {
        /**
         * DROP
         */
        foreach ($columns as $column) {
            $this->drop($tb, $column);
        }

        $this->reloadSchema();
    }

    /**
     * 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) !== 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 = array();
        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;
    }

    /**
     * makeIndex $aryからインデックスを作成する
     *
     * @param  array $ary
     * @return bool
     */
    public function makeIndex($ary)
    {
        foreach ($ary as $tb => $qs) {
            if (!is_array($qs)) {
                continue;
            }
            foreach ($qs as $key) {
                $DB = DB::singleton($this->dsn);
                $q = "ALTER TABLE `$tb` ADD $key";
                $DB->query($q, 'exec');
            }
        }

        return true;
    }

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

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

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

    /**
     * alterTable カラム定義の変更を適用する
     *
     * @param  string     $method
     * @param  string     $tb
     * @param  string     $left
     * @param  array|null $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)
    {
        return $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)
    {
        return $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)
    {
        return $this->alterTable('rename', $tb, $left, $def, $right);
    }

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

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

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

        $def = array();
        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 = array();
        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 = array();
        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 = array();
        while ($fd = $DB->fetch($q)) {
            $fds[] = $fd;
        }

        return $fds;
    }
}
