class Schema
{
    const ENABLE_ITEM_OPTIONS_DEFAULT = true;
    const ENABLE_MULTIPRICE_DEFAULT = false;

    /**
     * Adds tables
     *
     * Note that we need to update some MyISAM tables to InnoDB so we can take
     * advantage of foreign key constraints.
     *
     * Tables use InnoDB by default as of MySQL 5.7 but there are
     * still sites out there that installed WordPress with an older
     * version, resulting in all tables (posts, postmeta, etc.) being
     * MyISAM
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return void
     */
    public static function addTables() {
        global $wpdb;

        // Get Welcart tables first. This call will throw a fatal error if Welcart
        // isn't activated, which is why we do it first
        $skust = usces_get_tablename('usces_skus');
        $ordercartt = usces_get_tablename('usces_ordercart');

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        $charset_collate = $wpdb->get_charset_collate();
        $cst = self::getComboSetsTable();
        $csgt = self::getComboSetGroupsTable();
        $csgit = self::getComboSetGroupItemsTable();
        $csgioct = self::getComboSetGroupItemsOrderCartTable();
        $csgiocmt = self::getComboSetGroupItemsOrderCartMetaTable();

        /**
         * Change engine for Welcart tables to InnoDB
         */
        foreach ([$wpdb->posts, $skust, $ordercartt] as $table) {
            $sql = 'SELECT ENGINE
                    FROM INFORMATION_SCHEMA.TABLES
                    WHERE TABLE_SCHEMA = ' . '\'' . DB_NAME . '\'' . " AND TABLE_NAME = '{$table}'";
            $engine = $wpdb->get_var($sql);
            if ($engine === 'MyISAM') {
                $wpdb->query("ALTER TABLE {$table} ENGINE = InnoDB;");
            }
        }

        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', [$cst])) !== $cst) {
            $enableItemOptions = (int)self::ENABLE_ITEM_OPTIONS_DEFAULT;
            $enableMultiprice = (int)self::ENABLE_MULTIPRICE_DEFAULT;
            $sql = "CREATE TABLE {$cst} (
                ID BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                post_id BIGINT(20) UNSIGNED NOT NULL,
                sku_meta_id BIGINT(20) UNSIGNED NOT NULL UNIQUE,
                enable_item_options TINYINT(1) NOT NULL DEFAULT {$enableItemOptions},
                enable_multiprice TINYINT(1) NOT NULL DEFAULT {$enableMultiprice},
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                FOREIGN KEY (post_id)
                    REFERENCES {$wpdb->posts}(ID)
                    ON DELETE CASCADE
            ) ENGINE=InnoDB {$charset_collate};";
            dbDelta($sql);
        }

        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', [$csgt])) !== $csgt) {
            $sql = "CREATE TABLE {$csgt} (
                ID BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                combo_set_id BIGINT(20) UNSIGNED NOT NULL,
                group_label VARCHAR(200),
                optional TINYINT(1) NOT NULL DEFAULT 0,
                allow_multiple_selections TINYINT(1) NOT NULL DEFAULT 0,
                position INTEGER UNSIGNED NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                FOREIGN KEY (combo_set_id)
                    REFERENCES {$cst}(ID)
                    ON DELETE CASCADE
            ) ENGINE=InnoDB {$charset_collate};";
            dbDelta($sql);
        }

        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', [$csgit])) !== $csgit) {
            $sql = "CREATE TABLE {$csgit} (
                ID BIGINT(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                group_id BIGINT(20) UNSIGNED NOT NULL,
                welitem_sku_meta_id BIGINT(20) UNSIGNED NOT NULL,
                item_label VARCHAR(200),
                item_quantity SMALLINT NOT NULL DEFAULT 1,
                price_modifier INTEGER NOT NULL DEFAULT 0,
                position INTEGER UNSIGNED NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL,
                updated_at DATETIME NOT NULL,
                FOREIGN KEY (group_id)
                    REFERENCES {$csgt}(ID)
                    ON DELETE CASCADE
            ) ENGINE=InnoDB {$charset_collate};";
            dbDelta($sql);
        }

        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', [$csgioct])) !== $csgioct) {
            $sql = "CREATE TABLE {$csgioct} (
                cart_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                combo_set_cart_id BIGINT(20) UNSIGNED NOT NULL,
                order_id BIGINT(20) NOT NULL,
                group_id INT(3) NOT NULL DEFAULT '0',
                row_index INT(3) NOT NULL,
                post_id BIGINT(20) NOT NULL,
                item_code VARCHAR(100) NOT NULL,
                item_name VARCHAR(250) NOT NULL,
                cprice DECIMAL(12, 0) DEFAULT NULL,
                sku_code VARCHAR(100) NOT NULL,
                sku_name VARCHAR(250) DEFAULT NULL,
                price DECIMAL(12, 0) NOT NULL,
                quantity FLOAT NOT NULL,
                unit VARCHAR(50) DEFAULT NULL,
                tax DECIMAL(10, 0) DEFAULT NULL,
                destination_id INT(10) DEFAULT NULL,
                cart_serial TEXT,
                PRIMARY KEY (cart_id),
                KEY order_id (order_id),
                KEY post_id (post_id),
                KEY item_code (item_code),
                KEY item_name (item_name(191)),
                KEY sku_code (sku_code),
                KEY sku_name (sku_name(191)),
                FOREIGN KEY (combo_set_cart_id)
                    REFERENCES {$ordercartt}(cart_id)
                    ON DELETE CASCADE
            ) AUTO_INCREMENT=1000 ENGINE=InnoDB {$charset_collate};";
            dbDelta($sql);
        }

        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', [$csgiocmt])) !== $csgiocmt) {
            $sql = "CREATE TABLE {$csgiocmt} (
                cartmeta_id BIGINT(20) NOT NULL AUTO_INCREMENT,
                cart_id BIGINT(20) UNSIGNED NOT NULL,
                meta_type VARCHAR(100) NOT NULL,
                meta_key VARCHAR(255) DEFAULT NULL,
                meta_value LONGTEXT,
                PRIMARY KEY (cartmeta_id),
                KEY cart_id (cart_id),
                KEY meta_key (meta_key(191)),
                FOREIGN KEY (cart_id)
                    REFERENCES {$csgioct}(cart_id)
                    ON DELETE CASCADE
            ) ENGINE=InnoDB {$charset_collate};";
            dbDelta($sql);
        }
    }

    /**
     * Migrations for v1.0.1
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return void
     */
    public static function v101() {
        global $wpdb;

        $csgioct = self::getComboSetGroupItemsOrderCartTable();
        $rowIndexExists = (bool)(int)$wpdb->get_var('SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = ' . '\'' . DB_NAME . '\'' . " AND TABLE_NAME = '{$csgioct}' AND INDEX_NAME = 'row';
        ");
        if ($rowIndexExists === true) {
            $wpdb->query("ALTER TABLE {$csgioct} DROP INDEX row;");
        }
    }

    /**
     * Migrations for v1.1.0
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return void
     */
    public static function v110() {
        global $wpdb;

        $db = DB_NAME;
        $cst = self::getComboSetsTable();

        // drop wp_usces_item post ID foreign key
        $foreignKeyExists = (bool)(int)$wpdb->get_var("SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE
                CONSTRAINT_SCHEMA = '{$db}' AND
                TABLE_NAME        = '{$cst}' AND
                CONSTRAINT_NAME   = '{$cst}_ibfk_1' AND
                CONSTRAINT_TYPE   = 'FOREIGN KEY'
        ");
        if ($foreignKeyExists === true) {
            $wpdb->query("ALTER TABLE {$cst} DROP FOREIGN KEY `{$cst}_ibfk_1`");
        }

        // drop wp_usces_skus meta ID foreign key
        $foreignKeyExists = (bool)(int)$wpdb->get_var("SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE
                CONSTRAINT_SCHEMA = '{$db}' AND
                TABLE_NAME        = '{$cst}' AND
                CONSTRAINT_NAME   = '{$cst}_ibfk_2' AND
                CONSTRAINT_TYPE   = 'FOREIGN KEY'
        ");
        if ($foreignKeyExists === true) {
            $wpdb->query("ALTER TABLE {$cst} DROP FOREIGN KEY `{$cst}_ibfk_2`");
        }
    }

    /**
     * Returns combo sets table
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return string
     */
    public static function getComboSetsTable() {
        global $wpdb;
        return $wpdb->prefix . 'wcexics_combo_sets';
    }

    /**
     * Returns combo set groups table name
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return string
     */
    public static function getComboSetGroupsTable() {
        global $wpdb;
        return $wpdb->prefix . 'wcexics_combo_set_groups';
    }

    /**
     * Returns combo set group items table name
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return string
     */
    public static function getComboSetGroupItemsTable() {
        global $wpdb;
        return $wpdb->prefix . 'wcexics_combo_set_group_items';
    }

    /**
     * Returns combo set group items ordercart table name
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return string
     */
    public static function getComboSetGroupItemsOrderCartTable() {
        global $wpdb;
        return $wpdb->prefix . 'wcexics_combo_set_group_items_ordercart';
    }

    /**
     * Returns combo set group items ordercart meta table name
     *
     * @author Evan D Shaw <evandanielshaw@gmail.com>
     * @global \wpdb $wpdb
     * @return string
     */
    public static function getComboSetGroupItemsOrderCartMetaTable() {
        global $wpdb;
        return $wpdb->prefix . 'wcexics_combo_set_group_items_ordercart_meta';
    }
}