<?php

class ARSchemaMsSql {
    protected $adb;

    protected $tables = array();


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

    /**
     * SQL Server actually prohibits the latter SET command, meaning
     * any following SET ON's within the same script and session would fail
     *
     * At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
     */
    public function lock($tableName) {
        $this->execute("SET IDENTITY_INSERT {$tableName} ON");
    }

    /**
     * SQL Server actually prohibits the latter SET command, meaning
     * any following SET ON's within the same script and session would fail
     *
     * At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
     */
    public function unlock($tableName) {
        $this->execute("SET IDENTITY_INSERT {$tableName} OFF");
    }

    public function create() {

        $this->tables['advancedreports'] = "CREATE TABLE advancedreports (
            id int IDENTITY(1,1) PRIMARY KEY,
            category_id int NOT NULL,
            sequence int NOT NULL,
            module varchar(255) NOT NULL,
            related_data varbinary(max) NOT NULL,
            fields varbinary(max) NOT NULL,
            filters varbinary(max) NOT NULL,
            grouping varbinary(max) NOT NULL,
            aggregates varbinary(max) NOT NULL,
            totalAggregates varbinary(max) NOT NULL,
            title varchar(255) NOT NULL,
            description text NULL,
            options varbinary(max) NOT NULL,
            labels varbinary(max) NOT NULL,
            chart varbinary(max) NOT NULL,
            owner varchar(36) NOT NULL,
            shared int NOT NULL,
            sharedlevel int NOT NULL DEFAULT '0',
            iscombined int NOT NULL DEFAULT '0',
            combinedfields varbinary(max),
            visible int NOT NULL DEFAULT '1',
            calcFields varbinary(max),
            assigned_user_id varchar(36) DEFAULT NULL,
            modified_user_id varchar(36) DEFAULT NULL,
            deleted int DEFAULT '0',
            columnstate varbinary(max) NULL DEFAULT NULL,
            calculatedColumns varbinary(max) NULL DEFAULT NULL
        )";

        $this->tables['advancedreports_categories'] = "CREATE TABLE advancedreports_categories (
            id int IDENTITY(1,1) PRIMARY KEY,
            parent_id int DEFAULT NULL,
            sequence int NOT NULL,
            title varchar(255) DEFAULT NULL,
            description varchar(255) DEFAULT NULL,
            visible int NOT NULL DEFAULT '1'
        )";

        $this->tables['advancedreports_schedule'] = "CREATE TABLE advancedreports_schedule (
            object_id varchar(36) NOT NULL,
            interval_id int NULL DEFAULT NULL,
            interval int NOT NULL,
            interval_options varchar(5) NOT NULL,
            time varchar(5) NOT NULL,
            status varchar(10) NULL DEFAULT NULL,
            starttime datetime NOT NULL,
            endtime datetime NOT NULL,
            nexttime datetime NOT NULL,
            formats varbinary(max) NOT NULL,
            emails varbinary(max) NULL DEFAULT NULL,
            object_type char(1) NOT NULL DEFAULT 'R'
        )";

        $this->tables['advancedreports_schedule_g'] = "CREATE TABLE advancedreports_schedule_g (
            [object_id] varchar(36) NOT NULL,
            [group] varchar(36) NOT NULL
        )";

        $this->tables['advancedreports_schedule_r'] = "CREATE TABLE advancedreports_schedule_r (
            [object_id] varchar(36) NOT NULL,
            role varchar(36) NOT NULL
        )";

        $this->tables['advancedreports_schedule_u'] = "CREATE TABLE advancedreports_schedule_u (
            [object_id] varchar(36) NOT NULL,
            [user] varchar(36) NOT NULL
        )";

        $this->tables['advancedreports_sharedgroups'] = "CREATE TABLE advancedreports_sharedgroups (
            object_id varchar(36) NOT NULL,
            group_id varchar(36) NOT NULL,
            level int NOT NULL
        )";

        $this->tables['advancedreports_sharedusers'] = "CREATE TABLE advancedreports_sharedusers (
            object_id varchar(36) NOT NULL,
            user_id varchar(36) NOT NULL,
            level int NOT NULL
        )";

        $this->tables['advancedreports_logs'] = "CREATE TABLE advancedreports_logs (
            id int IDENTITY(1,1) PRIMARY KEY,
            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 varbinary(max) NULL DEFAULT NULL
        )";

        $this->tables['advancedreports_templates'] = "CREATE TABLE advancedreports_templates (
            id int IDENTITY(1,1) PRIMARY KEY,
            name varchar(255) NOT NULL,
            [file] varbinary(max) NULL DEFAULT NULL,
            file_checksum varchar(40) DEFAULT NULL,
            worksheets text NULL DEFAULT NULL
        )";

        $this->tables['advancedreports_jobs'] = "CREATE TABLE advancedreports_jobs (
            id int IDENTITY(1,1) PRIMARY KEY,
            name varchar(255) NOT NULL,
            description varchar(255) NOT NULL,
            active int NOT NULL,
            type varchar(255) NOT NULL,
            descriptor varbinary(max) NOT NULL,
            state varbinary(max) NOT NULL,
            [status] VARCHAR(10) NULL DEFAULT NULL,
            max_time VARCHAR(15) NULL DEFAULT NULL,
            max_memory VARCHAR(15) NULL DEFAULT NULL
        )";

        $this->tables['advancedreports_config'] = "CREATE TABLE advancedreports_config (
            id varchar(32) DEFAULT NULL,
            value text
        )";

        $this->tables['advancedreports_widgets'] = "CREATE TABLE advancedreports_widgets (
            id varchar(36) NOT NULL,
            dashboard_id varchar(36) NOT NULL,
            report_id int NOT NULL,
            type varchar(255) NOT NULL,
            positionx int NOT NULL,
            positiony int NOT NULL,
            width int NOT NULL,
            height int NOT NULL,
            [settings] text NULL DEFAULT NULL,
        )";

        $this->tables['advancedreports_dashboards'] = "CREATE TABLE advancedreports_dashboards (
            id varchar(36) NOT NULL,
            category_id int NOT NULL,
            visible int NOT NULL,
            sequence int NOT NULL,
            title varchar(255) NOT NULL,
            description varchar(255) NOT NULL,
            filters varbinary(max) NOT NULL,
            owner varchar(36) NOT NULL,
            shared int NOT NULL,
            sharedlevel int NOT NULL DEFAULT '0'
        )";

        $this->tables['advancedreports_table_mapping'] = "CREATE TABLE advancedreports_table_mapping (
            id int IDENTITY(1,1) PRIMARY KEY,
            original_name varchar(255) NOT NULL,
            hashed_name varchar(255) NOT NULL,
            INDEX mapping_orgin_name_idx (original_name)
        )";

        $this->tables['advancedreports_intervals'] = "CREATE TABLE advancedreports_intervals (
            id int IDENTITY(1,1) PRIMARY KEY,
            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
        )";

        $this->tables['advancedreports_modules'] = "CREATE TABLE advancedreports_modules (
            id int IDENTITY(1,1) PRIMARY KEY,
            name varchar(255) NOT NULL,
            [label] varchar(255) NOT NULL,
            [table] varchar(255) NOT NULL,
            created datetime NOT NULL,
            modified datetime NOT NULL
        )";

        $this->tables['advancedreports_blocks'] = "CREATE TABLE advancedreports_blocks (
            id int IDENTITY(1,1) PRIMARY KEY,
            module_id int(11) NOT NULL,
            name varchar(255) NOT NULL,
            [label] varchar(255) NOT NULL,
            created datetime NOT NULL,
            modified datetime NOT NULL
        )";

        $this->tables['advancedreports_fields'] = "CREATE TABLE advancedreports_fields (
            id int IDENTITY(1,1) PRIMARY KEY,
            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) NULL DEFAULT NULL,
            created datetime NOT NULL,
            modified datetime NOT NULL
        )";

        foreach($this->tables as $table => $sql) {
            // Wrap CREATE TABLE in if not exists and execute
            $this->execute($this->ifNotExists($table, $sql));
        }

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

    protected function ifNotExists($tableName, $sql) {
        return "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{$tableName}')
            BEGIN
            {$sql}
            END";
    }

    public function ifTableExistsAndNotEmpty($tableName) {
        $tableExists = $this->adb->tableExists($tableName);
        if($tableExists) {
            // Check for record existence too, maybe there is empty table
            $result = $this->execute("SELECT COUNT(*) AS count FROM [{$tableName}];");
            $count = $this->adb->fetchByAssoc($result);
            return !empty($count['count']);
        }

        return FALSE;
    }

    public function getTablePrimaryKeyIndex($tableName)
    {
        $sql = "SELECT [name] FROM sys.objects WHERE type = 'PK' AND  parent_object_id = OBJECT_ID ('{$tableName}')";
        $result = $this->execute($sql);
        $row = $this->adb->fetchByAssoc($result);
        return $row['name'];
    }

    protected function update() {
        // Optional constraints
        $commands[] = "ALTER TABLE advancedreports ADD calculatedColumns varbinary(max) NULL DEFAULT NULL;"; // #5098
        $commands[] = "ALTER TABLE advancedreports_widgets ADD [settings] text NULL DEFAULT NULL";

        // Add permissions to dashboard table
        $commands[] = "ALTER TABLE advancedreports_dashboards ADD owner varchar(36) NOT NULL;";
        $commands[] = "ALTER TABLE advancedreports_dashboards ADD shared int NOT NULL;";
        $commands[] = "ALTER TABLE advancedreports_dashboards ADD sharedlevel int NOT NULL DEFAULT '0';";

        // Change reportId from integer to varchar(36)
        $commands[] = "EXEC sp_RENAME 'advancedreports_sharedgroups.report_id' , 'object_id';";
        $commands[] = "ALTER TABLE advancedreports_sharedgroups ALTER COLUMN object_id VARCHAR(36) NOT NULL;";

        $commands[] = "EXEC sp_RENAME 'advancedreports_sharedusers.report_id' , 'object_id';";
        $commands[] = "ALTER TABLE advancedreports_sharedusers ALTER COLUMN object_id VARCHAR(36) NOT NULL;";

        $commands[] = "EXEC sp_RENAME 'advancedreports_schedule.id' , 'object_id';";
        $commands[] = "ALTER TABLE advancedreports_schedule ALTER COLUMN object_id VARCHAR(36) NOT NULL;";

        $commands[] = "EXEC sp_RENAME 'advancedreports_schedule_u.id' , 'object_id';";
        $commands[] = "ALTER TABLE advancedreports_schedule_u ALTER COLUMN object_id VARCHAR(36) NOT NULL;";

        $commands[] = "EXEC sp_RENAME 'advancedreports_schedule_r.id' , 'object_id';";
        $commands[] = "ALTER TABLE advancedreports_schedule_r ALTER COLUMN object_id VARCHAR(36) NOT NULL;";

        $commands[] = "EXEC sp_RENAME 'advancedreports_schedule_g.id' , 'object_id';";
        $commands[] = "ALTER TABLE advancedreports_schedule_g ALTER COLUMN object_id VARCHAR(36) NOT NULL;";

        $commands[] = "ALTER TABLE advancedreports_schedule ADD object_type CHAR(1) NOT NULL DEFAULT 'R';";

        // #6897 - Add two fields to log table
        $commands[] = "ALTER TABLE advancedreports_logs ADD 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;";

        // #6897 - Create advancedreports_intervals table
        $commands[] = $this->tables["advancedreports_intervals"];

        // #6897 - Added status, max_time, max_memory to jobs table
        $commands[] = "ALTER TABLE advancedreports_jobs ADD status VARCHAR(10) NULL DEFAULT NULL, max_time VARCHAR(15) NULL DEFAULT NULL, max_memory VARCHAR(15) NULL DEFAULT NULL;";

        // #6905 - Added SHA1 checksum and JSON worksheet column for XLS templates
        $commands[] = "ALTER TABLE advancedreports_templates ADD file_checksum varchar(40) DEFAULT NULL, worksheets text NULL DEFAULT NULL;";

        /**
         * Fix issue when table id columns was incorrectly created as INT with PK and INDENTITY
         * We need to get PK index name and remove this constraint
         * Then drop ID column
         * After recreate ID column with right data type
         */
        $dashboardPrimaryKey = $this->getTablePrimaryKeyIndex("advancedreports_dashboards");
        if($dashboardPrimaryKey) {
            $commands[] = "ALTER TABLE advancedreports_dashboards DROP CONSTRAINT {$dashboardPrimaryKey};"; // Drop primary key index
            $commands[] = "ALTER TABLE advancedreports_dashboards DROP COLUMN id;"; // Drop column with IDENTITY index
            $commands[] = "ALTER TABLE advancedreports_dashboards ADD id VARCHAR(36) NOT NULL;";
        }

        $widgetsPrimaryKey = $this->getTablePrimaryKeyIndex("advancedreports_widgets");
        if($dashboardPrimaryKey) {
            $commands[] = "ALTER TABLE advancedreports_widgets DROP CONSTRAINT {$widgetsPrimaryKey};"; // Drop primary key index
            $commands[] = "ALTER TABLE advancedreports_widgets DROP COLUMN id;"; // Drop column with IDENTITY index
            $commands[] = "ALTER TABLE advancedreports_widgets ADD id VARCHAR(36) NOT NULL;";
        }

        // Add status, starttime and endtime to scheduler
        $commands[] = "ALTER TABLE advancedreports_schedule ADD status VARCHAR(10) NULL DEFAULT NULL, ADD starttime datetime NULL DEFAULT NULL, ADD endtime datetime NULL DEFAULT NULL;";

        // Add interval_id
        $commands[] = "ALTER TABLE advancedreports_schedule ADD interval_id INT NULL DEFAULT NULL;";

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

    protected function execute($sql) {
        return $this->adb->query($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;";
        // var_dump(get_class_methods($this->adb));
        $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 aggregates
                    foreach(array("cnt", "count", "countDistinct", "avg", "min", "max", "sum") as $aggregate) {
                        $item = str_replace("\"{$aggregate}_{$i}_", "\"{$aggregate}_m{$i}_", $item);
                    }
                }
            }
        }

        return $object;
    }
}
