<?php

class ARSchemaMySql {
    protected $adb;

    protected $tables = array();


    public function __construct($adb) {
        $this->adb = $adb;
    }

    public function lock($tableName) {
        $this->execute("SET foreign_key_checks = 0;");
    }

    public function unlock($tableName) {
        $this->execute("SET foreign_key_checks = 1;");
    }

    /**
     * NOTE: All tables should be with InnoDB engine (MyISAM is not supported on Sugar Cloud)
     */
    public function create()
    {
        $this->tables['advancedreports'] = "CREATE TABLE IF NOT EXISTS `advancedreports` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `category_id` int(19) NOT NULL,
            `sequence` int(10) NOT NULL,
            `module` varchar(100) NOT NULL,
            `related_data` blob NOT NULL,
            `fields` blob NOT NULL,
            `filters` mediumblob NOT NULL,
            `grouping` mediumblob NOT NULL,
            `aggregates` mediumblob NOT NULL,
            `totalAggregates` blob NOT NULL,
            `title` text NOT NULL,
            `description` text,
            `options` blob NOT NULL,
            `labels` mediumblob NOT NULL,
            `chart` mediumblob NOT NULL,
            `owner` varchar(36) NOT NULL,
            `shared` int(1) NOT NULL,
            `sharedlevel` int(1) NOT NULL DEFAULT '0',
            `iscombined` int(1) NOT NULL DEFAULT '0',
            `combinedfields` blob,
            `visible` int(1) NOT NULL DEFAULT '1',
            `calcFields` mediumblob,
            `assigned_user_id` varchar(36) DEFAULT NULL,
            `modified_user_id` varchar(36) DEFAULT NULL,
            `deleted` int(1) DEFAULT '0',
            `columnstate` mediumblob,
            `calculatedColumns` blob,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_categories']="CREATE TABLE IF NOT EXISTS `advancedreports_categories` (
            `id` int(19) NOT NULL AUTO_INCREMENT,
            `parent_id` int(19) DEFAULT NULL,
            `sequence` int(10) NOT NULL,
            `title` varchar(255) DEFAULT NULL,
            `description` varchar(255) DEFAULT NULL,
            `visible` int(1) NOT NULL DEFAULT '1',
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_schedule']="CREATE TABLE IF NOT EXISTS `advancedreports_schedule` (
            `object_id` varchar(36) NOT NULL,
            `interval_id` int NULL DEFAULT NULL,
            `interval` int(11) NOT NULL,
            `interval_options` varchar(5) NOT NULL,
            `time` varchar(5) NOT NULL,
            `status` VARCHAR(10) NULL DEFAULT NULL,
            `starttime` datetime NULL DEFAULT NULL,
            `endtime` datetime NULL DEFAULT NULL,
            `nexttime` datetime NOT NULL,
            `formats` blob NOT NULL,
            `emails` blob NULL DEFAULT NULL,
            `object_type` char(1) NOT NULL DEFAULT 'R',
            UNIQUE KEY `uniqui` (`object_id`, `object_type`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_schedule_g']="CREATE TABLE IF NOT EXISTS `advancedreports_schedule_g` (
            `object_id` varchar(36) NOT NULL,
            `group` varchar(36) NOT NULL,
            UNIQUE KEY `report_idx` (`object_id`,`group`),
            KEY `group` (`group`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_schedule_r']="CREATE TABLE IF NOT EXISTS `advancedreports_schedule_r` (
            `object_id` varchar(36) NOT NULL,
            `role` varchar(36) NOT NULL,
            UNIQUE KEY `report_idx` (`object_id`,`role`),
            KEY `role` (`role`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_schedule_u']="CREATE TABLE IF NOT EXISTS `advancedreports_schedule_u` (
            `object_id` varchar(36) NOT NULL,
            `user` varchar(36) NOT NULL,
            UNIQUE KEY `report_idx` (`object_id`,`user`),
            KEY `user` (`user`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_sharedgroups']="CREATE TABLE IF NOT EXISTS `advancedreports_sharedgroups` (
            `object_id` varchar(36) NOT NULL,
            `group_id` varchar(36) NOT NULL,
            `level` int(11) NOT NULL,
            UNIQUE KEY `idx` (`object_id`,`group_id`),
            KEY `object_id` (`object_id`),
            KEY `user_id` (`group_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_sharedusers']="CREATE TABLE IF NOT EXISTS `advancedreports_sharedusers` (
            `object_id` varchar(36) NOT NULL,
            `user_id` varchar(36) NOT NULL,
            `level` int(11) NOT NULL,
            UNIQUE KEY `idx` (`object_id`,`user_id`),
            KEY `object_id` (`object_id`),
            KEY `user_id` (`user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_logs']="CREATE TABLE IF NOT EXISTS `advancedreports_logs` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `created` datetime NOT NULL,
            `level` varchar(10) NOT NULL,
            `memory_usage` varchar(30) NOT NULL,
            `time_taken` varchar(30) NOT NULL,
            `class_name` varchar(200) NULL DEFAULT NULL,
            `method_name` varchar(100) NULL DEFAULT NULL,
            `message` text NOT NULL,
            `vars` longblob,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_templates']="CREATE TABLE IF NOT EXISTS `advancedreports_templates` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL,
            `file` LONGBLOB NULL DEFAULT NULL,
            `file_checksum` varchar(40) DEFAULT NULL,
            `worksheets` text NULL DEFAULT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_jobs']="CREATE TABLE IF NOT EXISTS `advancedreports_jobs` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL,
            `description` varchar(255) NOT NULL,
            `active` tinyint(1) NOT NULL,
            `type` varchar(255) NOT NULL,
            `descriptor` blob NOT NULL,
            `state` blob NOT NULL,
            `status` VARCHAR(10) NULL DEFAULT NULL,
            `max_time` VARCHAR(15) NULL DEFAULT NULL,
            `max_memory` VARCHAR(15) NULL DEFAULT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_config']="CREATE TABLE IF NOT EXISTS advancedreports_config (
            id varchar(32) DEFAULT NULL,
            value text,
            UNIQUE KEY id (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_widgets']="CREATE TABLE IF NOT EXISTS advancedreports_widgets (
            `id` char(36) NOT NULL,
            `dashboard_id` char(36) NOT NULL,
            `report_id` int(11) NOT NULL,
            `type` varchar(50) NOT NULL,
            `positionx` int(5) NOT NULL,
            `positiony` int(5) NOT NULL,
            `width` int(5) NOT NULL,
            `height` int(5) NOT NULL,
            `settings` TEXT,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_dashboards']="CREATE TABLE IF NOT EXISTS advancedreports_dashboards (
             `id` char(36) NOT NULL,
             `category_id` int(11) NOT NULL,
             `visible` int(1) NOT NULL DEFAULT '1',
             `sequence` int(10) NOT NULL,
             `title` varchar(255) NOT NULL,
             `description` text NOT NULL,
             `filters` blob,
             `owner` varchar(36) NOT NULL,
             `shared` int(1) NOT NULL,
             `sharedlevel` int(1) NOT NULL DEFAULT '0',
             PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_table_mapping'] = "CREATE TABLE IF NOT EXISTS advancedreports_table_mapping (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `original_name` varchar(255) NOT NULL,
            `hashed_name` varchar(255) NOT NULL,
            PRIMARY KEY (`id`),
            INDEX `mapping_orgin_name_idx` (`original_name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_intervals'] = "CREATE TABLE IF NOT EXISTS advancedreports_intervals (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `object_id` varchar(36) NOT NULL,
            `object_type` varchar(15) NOT NULL,
            `interval_type` varchar(15) NOT NULL,
            `interval_options` varchar(15) NOT NULL,
            `interval_hours` varchar(2) NOT NULL,
            `interval_minutes` varchar(2) NOT NULL,
            `nexttime` datetime NOT NULL,
            `created` datetime NOT NULL,
            `modified` datetime NOT NULL, 
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_modules'] = "CREATE TABLE IF NOT EXISTS advancedreports_modules (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `name` varchar(255) NOT NULL,
            `label` varchar(255) NOT NULL,
            `table` varchar(255) NOT NULL,
            `created` datetime NOT NULL,
            `modified` datetime NOT NULL, 
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_blocks'] = "CREATE TABLE IF NOT EXISTS advancedreports_blocks (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `module_id` int(11) NOT NULL,
            `name` varchar(255) NOT NULL,
            `label` varchar(255) NOT NULL,
            `created` datetime NOT NULL,
            `modified` datetime NOT NULL, 
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        $this->tables['advancedreports_fields'] = "CREATE TABLE IF NOT EXISTS advancedreports_fields (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `module_id` int(11) NOT NULL,
            `block_id` int(11) NOT NULL,
            `table` varchar(255) NOT NULL,
            `column` varchar(255) NOT NULL,
            `name` varchar(255) NOT NULL,
            `label` varchar(255) NOT NULL,
            `type` varchar(255) NOT NULL,
            `values_source` varchar(255) DEFAULT NULL,
            `related_module` varchar(255) DEFAULT NULL,
            `created` datetime NOT NULL,
            `modified` datetime NOT NULL, 
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

        foreach($this->tables as $table => $sql) {
            $this->execute($sql);
        }

        // Update table schema if needed
        $this->update();
    }

    protected function execute($sql)
    {
        return $this->adb->query($sql);
    }

    protected function fetchOne($sql)
    {
        $result = $this->execute($sql);
        return $this->adb->fetchRow($result);
    }

    public function ifTableExists($tableName)
    {
        return $this->adb->tableExists($tableName);
    }

    public function ifTableExistsAndNotEmpty($tableName)
    {
        $tableExists = $this->ifTableExists($tableName);
        if($tableExists) {
            // Check for record existence too, maybe there is empty table
            $count = $this->fetchOne('SELECT COUNT(*) as cnt FROM `'.$tableName.'`;');
            return !empty($count['cnt']) && (int)$count['cnt'] > 0;
        }

        return false;
    }

    /**
     * Should return true if table column exists or false if not
     *
     * @param $tableName
     * @param $columnName
     * @return bool
     */
    public function ifColumnExists($tableName, $columnName)
    {
        $sql = "SHOW COLUMNS FROM `{$tableName}` LIKE '{$columnName}'";
        $result = $this->fetchOne($sql);
        return !empty($result);
    }

    /**
     * Check if foreign key / constraint exists in database table
     *
     * @param $tableName
     * @param $foreignKeyName
     * @return bool
     */
    public function ifForeignKeyExists($tableName, $foreignKeyName)
    {
        $sql = "SELECT true FROM information_schema.TABLE_CONSTRAINTS
                WHERE
                  CONSTRAINT_SCHEMA = DATABASE()
                  AND TABLE_NAME = '".$tableName."'
                  AND CONSTRAINT_NAME = '".$foreignKeyName."'
                  AND CONSTRAINT_TYPE = 'FOREIGN KEY'";
        $result = $this->fetchOne($sql);
        if($result) {
           return true;
        }

        return false;
    }

    public function indexExists($table, $indexName)
    {
        /**
         * OR ' LIMIT ' is hack for Sugar MysqlManager.php::fetchOne which checks if there is
         * ' LIMIT ' substring in string and if not - adds it's own LIMIT 0,1 which leads to
         * incorrect SQL and therefore - error.
         */
        $sql = "SHOW INDEX FROM {$table} WHERE key_name = '{$indexName}' OR ' LIMIT '";

        $result = $this->fetchOne($sql);

        return $result;
    }

    protected function update()
    {
//        if(!$this->ifColumnExists('advancedreports', 'columnstate')) {
//            $commands[] = "ALTER TABLE `advancedreports` ADD `columnstate` BLOB NULL DEFAULT NULL;"; // #5098
//        }
//
//        if(!$this->ifColumnExists('advancedreports_schedule', 'emails')) {
//            $commands[] = "ALTER TABLE `advancedreports_schedule` ADD `emails` BLOB NULL DEFAULT NULL"; // #5872
//        }
//
//        // Set deleted field to int (1) instead of int (36) to decrease row size
//        $commands[] = "ALTER TABLE `advancedreports` CHANGE `deleted` `deleted` INT(1) NULL DEFAULT '0';";
//        // Set module field to varchar (100) instead of varchar (255) to decrease row size
//        $commands[] = "ALTER TABLE `advancedreports` CHANGE `module` `module` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
//
//        // Set fields to int (1) instead of int (11) to decrease row size
//        $commands[] = "ALTER TABLE `advancedreports` CHANGE `iscombined` `iscombined` INT(1) NOT NULL DEFAULT '0';";
//        $commands[] = "ALTER TABLE `advancedreports` CHANGE `shared` `shared` INT(1) NOT NULL;";
//        $commands[] = "ALTER TABLE `advancedreports` CHANGE `sharedlevel` `sharedlevel` INT(1) NOT NULL DEFAULT  '0';";
//        // Set field from varchar(255) to text. This can PROBABLY decrease the row size
//        $commands[] = "ALTER TABLE `advancedreports` CHANGE `title` `title` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
//
//        $commands[] = "ALTER TABLE `advancedreports` MODIFY `filters` MEDIUMBLOB NOT NULL;";
//        $commands[] = "ALTER TABLE `advancedreports` MODIFY `grouping` MEDIUMBLOB NOT NULL;";
//        $commands[] = "ALTER TABLE `advancedreports` MODIFY `aggregates` MEDIUMBLOB NOT NULL;";
//        $commands[] = "ALTER TABLE `advancedreports` MODIFY `labels` MEDIUMBLOB NOT NULL;";
//        $commands[] = "ALTER TABLE `advancedreports` MODIFY `calcFields` MEDIUMBLOB;";
//
//        /**
//         * Remove rudiment constraints and foreign keys
//         * Added in version 2.1.90
//         */
//        if($this->ifForeignKeyExists("advancedreports_schedule", "advancedreports_schedule_ibfk_1")) {
//            $commands[] = "ALTER TABLE advancedreports_schedule DROP FOREIGN KEY advancedreports_schedule_ibfk_1;";
//        }
//        if($this->ifForeignKeyExists("advancedreports_schedule_g", "advancedreports_schedule_g_ibfk_1")) {
//            $commands[] = "ALTER TABLE advancedreports_schedule_g DROP FOREIGN KEY advancedreports_schedule_g_ibfk_1;";
//        }
//        if($this->ifForeignKeyExists("advancedreports_schedule_r", "advancedreports_schedule_r_ibfk_1")) {
//            $commands[] = "ALTER TABLE advancedreports_schedule_r DROP FOREIGN KEY advancedreports_schedule_r_ibfk_1;";
//        }
//        if($this->ifForeignKeyExists("advancedreports_schedule_u", "advancedreports_schedule_u_ibfk_1")) {
//            $commands[] = "ALTER TABLE advancedreports_schedule_u DROP FOREIGN KEY advancedreports_schedule_u_ibfk_1;";
//        }
//
        $commands[] = "ALTER TABLE advancedreports_templates MODIFY file LONGBLOB;";

        if(!$this->ifColumnExists('advancedreports_config', 'id')) {
            $commands[] = "ALTER TABLE advancedreports_config CHANGE `key` id VARCHAR(32) DEFAULT NULL";
        }

        if(!$this->ifColumnExists('advancedreports', 'calculatedColumns')) {
            $commands[] = "ALTER TABLE `advancedreports` ADD `calculatedColumns` BLOB NULL DEFAULT NULL;"; // #5098
        }

        if(!$this->ifColumnExists('advancedreports_widgets', 'settings')) {
            $commands[] = "ALTER TABLE `advancedreports_widgets` ADD `settings` TEXT DEFAULT NULL";
        }

        if(!$this->ifColumnExists('advancedreports_dashboards', 'owner')) {
            $commands[] = "ALTER TABLE advancedreports_dashboards ADD owner varchar(36) NOT NULL;";
        }

        // Add permissions to dashboard table
        if(!$this->ifColumnExists('advancedreports_dashboards', 'shared')) {
            $commands[] = "ALTER TABLE advancedreports_dashboards ADD shared int(1) NOT NULL;";
        }

        if(!$this->ifColumnExists('advancedreports_dashboards', 'sharedlevel')) {
            $commands[] = "ALTER TABLE advancedreports_dashboards ADD sharedlevel int(1) NOT NULL DEFAULT '0';";
        }

        // Change report_id from integer to varchar(36) and name to object_id
        if(!$this->ifColumnExists('advancedreports_sharedgroups', 'object_id')) {
            $commands[] = "ALTER TABLE advancedreports_sharedgroups CHANGE report_id object_id VARCHAR(36) NOT NULL;";
        }

        if(!$this->ifColumnExists('advancedreports_sharedusers', 'object_id')) {
            $commands[] = "ALTER TABLE advancedreports_sharedusers CHANGE report_id object_id VARCHAR(36) NOT NULL;";
        }

        /**
         * Changed report columnstate from blob to mediumblob, because of large report many columns doesn't fit
         * and produced MySQL error: MySQL error 1406: Data too long for column 'columnstate' at row 1
         */
        $commands[] = "ALTER TABLE advancedreports CHANGE columnstate columnstate MEDIUMBLOB NULL DEFAULT NULL;";

        if(!$this->ifColumnExists('advancedreports_schedule', 'object_id')) {
            $commands[] = "ALTER TABLE `advancedreports_schedule` CHANGE id object_id VARCHAR(36) NOT NULL;";
        }

        if(!$this->ifColumnExists('advancedreports_schedule_u', 'object_id')) {
            $commands[] = "ALTER TABLE `advancedreports_schedule_u` CHANGE id object_id VARCHAR(36) NOT NULL;";
        }

        if(!$this->ifColumnExists('advancedreports_schedule_r', 'object_id')) {
            $commands[] = "ALTER TABLE `advancedreports_schedule_r` CHANGE id object_id VARCHAR(36) NOT NULL;";
        }

        if(!$this->ifColumnExists('advancedreports_schedule_g', 'object_id')) {
            $commands[] = "ALTER TABLE `advancedreports_schedule_g` CHANGE id object_id VARCHAR(36) NOT NULL;";
        }

        if(!$this->ifColumnExists('advancedreports_schedule', 'object_type')) {
            $commands[] = "ALTER TABLE `advancedreports_schedule` ADD object_type CHAR(1) NOT NULL DEFAULT 'R';";
        }

        // #6897 - Add two fields to log table
        if(!$this->ifColumnExists('advancedreports_logs', 'method_name')) {
            $commands[] = "ALTER TABLE advancedreports_logs ADD memory_usage varchar(30) NOT NULL, ADD time_taken VARCHAR(30) NOT NULL, ADD class_name varchar(200) NULL DEFAULT NULL, ADD method_name varchar(100)  NULL DEFAULT NULL;";
        }

        // #6897 - Create advancedreports_intervals table
        if(!$this->ifTableExists('advancedreports_intervals')) {
            $commands[] = $this->tables["advancedreports_intervals"];
        }

        // #6897 - Added status, max_time, max_memory to jobs table
        if(!$this->ifColumnExists('advancedreports_jobs', 'max_memory')) {
            $commands[] = "ALTER TABLE advancedreports_jobs ADD status VARCHAR(10) NULL DEFAULT NULL, ADD `max_time` VARCHAR(15) NULL DEFAULT NULL, ADD `max_memory` VARCHAR(15) NULL DEFAULT NULL;";
        }
        // #6905 - Added SHA1 checksum and JSON worksheet column for XLS templates
        if(!$this->ifColumnExists('advancedreports_templates', 'worksheets')) {
            $commands[] = "ALTER TABLE advancedreports_templates ADD `file_checksum` varchar(40) DEFAULT NULL, ADD `worksheets` text NULL DEFAULT NULL;";
        }
        // Add status, starttime and endtime to scheduler
        if(!$this->ifColumnExists('advancedreports_schedule', 'endtime')) {
            $commands[] = "ALTER TABLE advancedreports_schedule ADD status VARCHAR(10) NULL DEFAULT NULL, ADD starttime datetime NULL DEFAULT NULL, ADD endtime datetime NULL DEFAULT NULL;";
        }
        // performance penalty should now be high
        $commands[] = "ALTER TABLE advancedreports_schedule
                MODIFY COLUMN starttime DATETIME NULL DEFAULT NULL,
                MODIFY COLUMN endtime DATETIME NULL DEFAULT NULL;";

        // Added values_source column to fields table, so we can mark from which option list options should be received
        if(!$this->ifColumnExists('advancedreports_fields', 'values_source')) {
            $commands[] = "ALTER TABLE advancedreports_fields ADD values_source VARCHAR(255) NULL AFTER type;";
        }
        // Added module_id column to blocks table, so we can know to which module block belongs
        if(!$this->ifColumnExists('advancedreports_blocks', 'module_id')) {
            $commands[] = "ALTER TABLE advancedreports_blocks ADD module_id int(11) NOT NULL AFTER id;";
        }

        if(!$this->ifColumnExists('advancedreports_fields', 'related_module')) {
            $commands[] = "ALTER TABLE advancedreports_fields ADD related_module varchar(255) NOT NULL AFTER values_source;";
        }

        if(!$this->ifColumnExists('advancedreports_schedule', 'interval_id')) {
            $commands[] = "ALTER TABLE advancedreports_schedule ADD interval_id INT NULL DEFAULT NULL AFTER object_id;";
        }

        // #7408 - increased log vars column size
        if($this->ifColumnExists('advancedreports_logs', 'vars')) {
            $commands[] = "ALTER TABLE advancedreports_logs CHANGE vars vars longblob;";
        }

        if($this->ifColumnExists('advancedreports', 'chart')) {
            $commands[] = "ALTER TABLE advancedreports CHANGE chart chart MEDIUMBLOB NOT NULL;";
        }

        foreach($commands as $sql) {
            $this->execute($sql);
        }

        $indexes = array(
            array(
                'table' => 'advancedreports_logs',
                'name' => 'ar_log_time_taken_idx',
                'sql' => 'CREATE INDEX ar_log_time_taken_idx ON advancedreports_logs (`time_taken`)',
            )
        );

        foreach ($indexes as $index) {
            if ($this->indexExists($index['table'], $index['name'])) {
                continue;
            }

            $this->execute($index['sql']);
        }
    }

    // Add 'm' prefixes to all field defintions
    public function patchReports()
    {
        // Check if pre-installed version has already patch included
        $sql = "SELECT * FROM advancedreports_config WHERE id = 'moduleVersion';";
        $result = $this->execute($sql);
        // Current version or false on fresh install
        $version = ($result) ? $this->adb->fetchRow($result) : false;
        if (!$version || version_compare($version["value"], "2.1.95") >= 0) {
            return;
        }
        $sql = "SELECT * FROM advancedreports;";
        $result = $this->execute($sql);
        $binaryFields = array(
            "calculatedColumns",
            "columnstate",
            "calcFields",
            "combinedfields",
            "chart",
            "labels",
            "options",
            "totalAggregates",
            "aggregates",
            "grouping",
            "filters",
            "fields",
            "related_data"
        );

        while($row = $this->adb->fetchRow($result)) {
            $row = self::addPrefixToColumns($row);
            $id = $row["id"];
            $values = array();
            foreach ($row as $key => $value) {
                if(in_array($key, $binaryFields) && !empty($value)){
                    $value = self::toBinary($value);
                    $values[] = "{$key} = {$value}";
                }
            }
            $values = implode(", ", $values);
            $sql = "UPDATE advancedreports SET {$values} WHERE id = {$id}";
            $this->execute($sql);
        }
    }

    /**
     * Increment/decrement shared level, because added "Can view and export"
     */
    public function patchPermissions()
    {
        // Check if pre-installed version has already patch included
        $sql = "SELECT * FROM advancedreports_config WHERE id = 'moduleVersion';";
        $result = $this->execute($sql);
        // Current version or false on fresh install
        $version = ($result) ? $this->adb->fetchRow($result) : false;
        if (!$version || version_compare($version["value"], "3.0.115") >= 0) {
            return;
        }

        $sql = "SELECT id, options, shared, sharedlevel FROM advancedreports;";
        $result = $this->execute($sql);

        while($row = $this->adb->fetchRow($result)) {
            $sharedLevel = (int) $row['sharedlevel'];
            $options = json_decode($row['options'], true);

            $this->patchPermissionsReportGlobal($row['id'], $this->calculateSharedLevelByOptions($options, $sharedLevel));
            $this->patchPermissionsReportUsers($row['id'], $options);
            $this->patchPermissionsReportGroups($row['id'], $options);
        }
    }

    protected function calculateSharedLevelByOptions($options, $sharedLevel)
    {
        if(!$options || empty($options['allowExportReadOnly'])) {
            // Decrement viewing shared level to no access
            if($sharedLevel == 1) {
                $sharedLevel = 0;
            }
        }

        $sharedLevel = $sharedLevel + 1; // Increment shared level at least to view
        return $sharedLevel;
    }

    protected function patchPermissionsReportGlobal($reportId, $sharedLevel)
    {
        $sql = "UPDATE advancedreports SET sharedlevel = {$sharedLevel} WHERE id = '{$reportId}'";
        $this->execute($sql);
    }

    protected function patchPermissionsReportUsers($reportId, $options)
    {
        $sql = "SELECT object_id, user_id, level FROM advancedreports_sharedusers;";
        $result = $this->execute($sql);
        while($row = $this->adb->fetchRow($result)) {
            $sharedLevel = $this->calculateSharedLevelByOptions($options, $row['level']);
            $sql = "UPDATE advancedreports_sharedusers SET level = {$sharedLevel} WHERE user_id = '{$row['user_id']}' AND object_id = '{$reportId}'";
            $this->execute($sql);
        }
    }

    protected function patchPermissionsReportGroups($reportId, $options)
    {
        $sql = "SELECT object_id, group_id, level FROM advancedreports_sharedgroups;";
        $result = $this->execute($sql);
        while($row = $this->adb->fetchRow($result)) {
            $sharedLevel = $this->calculateSharedLevelByOptions($options, $row['level']);
            $sql = "UPDATE advancedreports_sharedgroups SET level = {$sharedLevel} WHERE group_id = '{$row['group_id']}' AND object_id = '{$reportId}'";
            $this->execute($sql);
        }
    }


    static public function toBinary($data)
    {
        return '0x'.strtoupper(bin2hex($data));
    }

    protected static function addPrefixToColumns($object)
    {
        foreach($object as $index => &$item) {
            if($index === 'calculatedColumns'){
                continue;
            }
            if(is_string($item)) {
                // Special case for combined reports
                $item = str_replace('"00_', '"m00_', $item);

                foreach(range(0,9) as $i) {
                    // Replace "9_ to "m9_
                    $item = str_replace("\"{$i}_", "\"m{$i}_", $item);
                    // Replace #9_ to #m9_ (calculated fields customSQL formula)
                    $item = str_replace("#{$i}_", "#m{$i}_", $item);

                    // Replace aggregates
                    foreach(array("cnt", "count", "countDistinct", "avg", "min", "max", "sum") as $aggregate) {
                        $item = str_replace("\"{$aggregate}_{$i}_", "\"{$aggregate}_m{$i}_", $item);
                    }
                }
            }
        }

        return $object;
    }
}
