<?php

use plugins\help\Consts;
use bff\db\migrations\Migration;
use Phinx\Db\Adapter\MysqlAdapter;

class ExtP042aa73e283427790ca34ebb876307633e06cb2V1x0x0 extends Migration  implements Consts
{
    public function migrate()
    {
        $this->tables();
    }

    public function rollback()
    {
    }

    public function tables()
    {
        $name = static::TABLE_CATEGORIES;
        if (! $this->hasTable($name)) {
            $table = $this->table($name, ['engine' => 'InnoDB', 'collation' => 'utf8mb4_unicode_ci', 'id' => false, 'primary_key' => ['id']])
                ->addColumn('id', 'integer', ['signed' => false, 'identity' => true])
                ->addColumn('pid', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('keyword', 'string', ['limit'=>300])
                ->addColumn('keyword_edit', 'string', ['limit'=>100])
                ->addColumn('enabled', 'integer',  ['limit' => MysqlAdapter::INT_TINY, 'signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('created', 'timestamp', ['null' => false, 'default'=>'CURRENT_TIMESTAMP'])
                ->addColumn('modified', 'timestamp', ['null' => false, 'default'=>0])
                ->addColumn('numleft', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('numright', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('numlevel', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('mtemplate', 'integer',  ['limit' => MysqlAdapter::INT_TINY, 'signed' => false, 'null' => false, 'default' => 0])
                ->addIndex(['id', 'numleft', 'numright'],  ['name' => 'id', 'unique' => true])
            ;
            $table->create();

            $this->db->insert($name, [
                'id' => 1,
                'pid' => 0,
                'numleft' => 1,
                'numright' => 2,
                'numlevel' => 0,
                'keyword' => 'root',
                'enabled' => 1,
                'created' => $this->db->now(),
                'modified' => $this->db->now(),
                'mtemplate' => 1,
            ]);
        }

        $name = static::TABLE_CATEGORIES_LANG;
        if (! $this->hasTable($name)) {
            $table = $this->table($name, ['engine' => 'InnoDB', 'collation' => 'utf8mb4_unicode_ci', 'id' => false])
                ->addColumn('id', 'integer', ['signed' => false])
                ->addColumn('lang', 'char', ['limit' => 2, 'default' => ''])
                ->addColumn('title', 'string', ['limit'=>200])
                ->addColumn('mtitle', 'string', ['limit'=>200])
                ->addColumn('mkeywords', 'text', ['limit' => MysqlAdapter::TEXT_MEDIUM, 'null' => true, 'default' => null])
                ->addColumn('mdescription', 'text', ['limit' => MysqlAdapter::TEXT_MEDIUM, 'null' => true, 'default' => null])
                ->addIndex(['id', 'lang'], ['unique' => true, 'name' => 'lang'])
                ->addForeignKey('id', static::TABLE_CATEGORIES, 'id', ['delete' => 'CASCADE'])
            ;
            $table->create();

            foreach(bff::locale()->getLanguages() as $v) {
                $this->db->insert($name, [
                    'id' => 1,
                    'lang' => $v,
                    'title' => 'Root section',
                ]);
            }
        }

        $name = static::TABLE_QUESTIONS;
        if (! $this->hasTable($name)) {
            $table = $this->table($name, ['engine' => 'InnoDB', 'collation' => 'utf8mb4_unicode_ci', 'id' => false, 'primary_key' => ['id']])
                ->addColumn('id', 'integer', ['signed' => false, 'identity' => true])
                ->addColumn('cat_id1', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('cat_id2', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('cat_id', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('link', 'text', ['limit' => MysqlAdapter::TEXT_MEDIUM, 'null' => false, 'default' => ''])
                ->addColumn('content', 'text', ['limit' => MysqlAdapter::TEXT_LONG, 'null' => true, 'default' => null])
                ->addColumn('content_no', 'integer',  ['limit' => MysqlAdapter::INT_TINY, 'signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('enabled', 'integer',  ['limit' => MysqlAdapter::INT_TINY, 'signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('fav', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('created', 'timestamp', ['null' => false, 'default'=>'CURRENT_TIMESTAMP'])
                ->addColumn('modified', 'timestamp', ['null' => false, 'default'=>0])
                ->addColumn('user_id', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('mtemplate', 'integer',  ['limit' => MysqlAdapter::INT_TINY, 'signed' => false, 'null' => false, 'default' => 0])
                ->addColumn('num', 'integer', ['signed' => false, 'null' => false, 'default' => 0])
                ->addIndex(['cat_id'],  ['name' => 'questions_cat'])
            ;
            $table->create();
        } else {
            $dbName = $this->db->dbname;
            $data = $this->db->select('SELECT TABLE_NAME, ENGINE  FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = :name AND ' . $this->db->prepareIN('TABLE_NAME', [$name]), [':name' => $dbName]);
            foreach ($data as $v) {
                if (mb_strtolower($v['ENGINE']) != 'innodb') {
                    $this->db->exec('ALTER TABLE `' . $v['TABLE_NAME'] . '` ENGINE=InnoDB');
                }
            }
        }

        $name = static::TABLE_QUESTIONS_LANG;
        if (! $this->hasTable($name)) {
            $table = $this->table($name, ['engine' => 'InnoDB', 'collation' => 'utf8mb4_unicode_ci', 'id' => false])
                ->addColumn('id', 'integer', ['signed' => false])
                ->addColumn('lang', 'char', ['limit' => 2, 'default' => ''])
                ->addColumn('title', 'string', ['limit'=>200])
                ->addColumn('textshort', 'text', ['limit' => MysqlAdapter::TEXT_MEDIUM, 'null' => true, 'default' => null])
                ->addColumn('mtitle', 'string', ['limit'=>200])
                ->addColumn('mkeywords', 'text', ['limit' => MysqlAdapter::TEXT_MEDIUM, 'null' => true, 'default' => null])
                ->addColumn('mdescription', 'text', ['limit' => MysqlAdapter::TEXT_MEDIUM, 'null' => true, 'default' => null])
                ->addColumn('content_search', 'text', ['limit' => MysqlAdapter::TEXT_LONG, 'null' => true, 'default' => null])
                ->addIndex(['id', 'lang'], ['unique' => true, 'name' => 'lang'])
                ->addForeignKey('id', static::TABLE_QUESTIONS, 'id', ['delete' => 'CASCADE'])
            ;
            $table->create();
        }
    }
}