<?php

/**
 * OpenMage
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available at https://opensource.org/license/osl-3-0-php
 *
 * @category   Mage
 * @package    Mage_Core
 * @copyright  Copyright (c) 2006-2020 Magento, Inc. (https://www.magento.com)
 * @copyright  Copyright (c) 2020-2024 The OpenMage Contributors (https://www.openmage.org)
 * @license    https://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */

/** @var Mage_Core_Model_Resource_Setup $installer */
$installer = $this;
$installer->startSetup();

$usedDatabaseStorage = $installer->getConnection()->isTableExists(
    $installer->getTable('core/file_storage'),
);

/**
 * Drop foreign keys
 */
$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/layout_link'),
    'FK_CORE_LAYOUT_LINK_STORE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/layout_link'),
    'FK_CORE_LAYOUT_LINK_UPDATE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/store'),
    'FK_STORE_GROUP_STORE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/store'),
    'FK_STORE_WEBSITE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/session'),
    'FK_SESSION_WEBSITE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/store_group'),
    'FK_STORE_GROUP_WEBSITE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/translate'),
    'FK_CORE_TRANSLATE_STORE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/url_rewrite'),
    'CORE_URL_REWRITE_IBFK_1',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/url_rewrite'),
    'FK_CORE_URL_REWRITE_CATEGORY',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/url_rewrite'),
    'FK_CORE_URL_REWRITE_PRODUCT',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/url_rewrite'),
    'FK_CORE_URL_REWRITE_STORE',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/variable_value'),
    'FK_CORE_VARIABLE_VALUE_STORE_ID',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/variable_value'),
    'FK_CORE_VARIABLE_VALUE_VARIABLE_ID',
);

$installer->getConnection()->dropForeignKey(
    $installer->getTable('core/design_change'),
    'FK_DESIGN_CHANGE_STORE',
);

if ($usedDatabaseStorage) {
    $installer->getConnection()->dropForeignKey(
        $installer->getTable('core/file_storage'),
        'FK_FILE_DIRECTORY',
    );

    $installer->getConnection()->dropForeignKey(
        $installer->getTable('core/directory_storage'),
        'FK_DIRECTORY_PARENT_ID',
    );
}

/**
 * Drop indexes
 */
$installer->getConnection()->dropIndex(
    $installer->getTable('core/cache'),
    'IDX_EXPIRE_TIME',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/cache_tag'),
    'IDX_CACHE_ID',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/config_data'),
    'CONFIG_SCOPE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/email_template'),
    'TEMPLATE_CODE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/email_template'),
    'ADDED_AT',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/email_template'),
    'MODIFIED_AT',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/flag'),
    'LAST_UPDATE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/layout_link'),
    'STORE_ID',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/layout_link'),
    'FK_CORE_LAYOUT_LINK_UPDATE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/layout_update'),
    'HANDLE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/store'),
    'CODE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/store'),
    'FK_STORE_WEBSITE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/store'),
    'IS_ACTIVE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/store'),
    'FK_STORE_GROUP',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/store_group'),
    'FK_STORE_GROUP_WEBSITE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/store_group'),
    'DEFAULT_STORE_ID',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/translate'),
    'IDX_CODE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/translate'),
    'FK_CORE_TRANSLATE_STORE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/session'),
    'FK_SESSION_WEBSITE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'UNQ_REQUEST_PATH',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'UNQ_PATH',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'FK_CORE_URL_REWRITE_STORE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'FK_CORE_URL_REWRITE_CATEGORY',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'FK_CORE_URL_REWRITE_PRODUCT',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'IDX_ID_PATH',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'IDX_CATEGORY_REWRITE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/url_rewrite'),
    'IDX_TARGET_PATH',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/variable'),
    'IDX_CODE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/variable_value'),
    'IDX_VARIABLE_STORE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/variable_value'),
    'IDX_VARIABLE_ID',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/variable_value'),
    'IDX_STORE_ID',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/website'),
    'CODE',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/website'),
    'SORT_ORDER',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/website'),
    'DEFAULT_GROUP_ID',
);

$installer->getConnection()->dropIndex(
    $installer->getTable('core/design_change'),
    'FK_DESIGN_CHANGE_STORE',
);

if ($usedDatabaseStorage) {
    $installer->getConnection()->dropIndex(
        $installer->getTable('core/file_storage'),
        'IDX_FILENAME',
    );

    $installer->getConnection()->dropIndex(
        $installer->getTable('core/file_storage'),
        'directory_id',
    );

    $installer->getConnection()->dropIndex(
        $installer->getTable('core/directory_storage'),
        'IDX_DIRECTORY_PATH',
    );

    $installer->getConnection()->dropIndex(
        $installer->getTable('core/directory_storage'),
        'parent_id',
    );
}

/*
 * Change columns
 */
$tables = [
    $installer->getTable('core/config_data') => [
        'columns' => [
            'config_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Config Id',
            ],
            'scope' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 8,
                'nullable'  => false,
                'default'   => 'default',
                'comment'   => 'Config Scope',
            ],
            'scope_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Config Scope Id',
            ],
            'path' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'nullable'  => false,
                'default'   => 'general',
                'comment'   => 'Config Path',
            ],
            'value' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Config Value',
            ],
        ],
        'comment' => 'Config Data',
    ],
    $installer->getTable('core/website') => [
        'columns' => [
            'website_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Website Id',
            ],
            'code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 32,
                'comment'   => 'Code',
            ],
            'name' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 64,
                'comment'   => 'Website Name',
            ],
            'sort_order' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Sort Order',
            ],
            'default_group_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Default Group Id',
            ],
            'is_default' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'default'   => '0',
                'comment'   => 'Defines Is Website Default',
            ],
        ],
        'comment' => 'Websites',
    ],
    $installer->getTable('core/store') => [
        'columns' => [
            'store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Store Id',
            ],
            'code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 32,
                'comment'   => 'Code',
            ],
            'website_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Website Id',
            ],
            'group_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Group Id',
            ],
            'name' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'nullable'  => false,
                'comment'   => 'Store Name',
            ],
            'sort_order' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Sort Order',
            ],
            'is_active' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Activity',
            ],
        ],
        'comment' => 'Stores',
    ],
    $installer->getTable('core/resource') => [
        'columns' => [
            'code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 50,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Resource Code',
            ],
            'version' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 50,
                'comment'   => 'Resource Version',
            ],
            'data_version' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 50,
                'comment'   => 'Data Version',
            ],
        ],
        'comment' => 'Resources',
    ],
    $installer->getTable('core/cache') => [
        'columns' => [
            'id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 200,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Cache Id',
            ],
            'data' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_BLOB,
                'length'    => '2M',
                'comment'   => 'Cache Data',
            ],
            'create_time' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'comment'   => 'Cache Creation Time',
            ],
            'update_time' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'comment'   => 'Time of Cache Updating',
            ],
            'expire_time' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'comment'   => 'Cache Expiration Time',
            ],
        ],
        'comment' => 'Caches',
    ],
    $installer->getTable('core/cache_tag') => [
        'columns' => [
            'tag' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 100,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Tag',
            ],
            'cache_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 200,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Cache Id',
            ],
        ],
        'comment' => 'Tag Caches',
    ],
    $installer->getTable('core/cache_option') => [
        'columns' => [
            'code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 32,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Code',
            ],
            'value' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'comment'   => 'Value',
            ],
        ],
        'comment' => 'Cache Options',
    ],
    $installer->getTable('core/store_group') => [
        'columns' => [
            'group_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Group Id',
            ],
            'website_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Website Id',
            ],
            'name' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'nullable'  => false,
                'comment'   => 'Store Group Name',
            ],
            'root_category_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Root Category Id',
            ],
            'default_store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Default Store Id',
            ],
        ],
        'comment' => 'Store Groups',
    ],
    $installer->getTable('core/email_template') => [
        'columns' => [
            'template_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Template Id',
            ],
            'template_code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 150,
                'nullable'  => false,
                'comment'   => 'Template Name',
            ],
            'template_text' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'nullable'  => false,
                'comment'   => 'Template Content',
            ],
            'template_styles' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Templste Styles',
            ],
            'template_type' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'unsigned'  => true,
                'comment'   => 'Template Type',
            ],
            'template_subject' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 200,
                'nullable'  => false,
                'comment'   => 'Template Subject',
            ],
            'template_sender_name' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 200,
                'comment'   => 'Template Sender Name',
            ],
            'template_sender_email' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 200,
                'comment'   => 'Template Sender Email',
            ],
            'added_at' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TIMESTAMP,
                'comment'   => 'Date of Template Creation',
            ],
            'modified_at' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TIMESTAMP,
                'comment'   => 'Date of Template Modification',
            ],
            'orig_template_code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 200,
                'comment'   => 'Original Template Code',
            ],
            'orig_template_variables' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Original Template Variables',
            ],
        ],
        'comment' => 'Email Templates',
    ],
    $installer->getTable('core/variable') => [
        'columns' => [
            'variable_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Variable Id',
            ],
            'code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Variable Code',
            ],
            'name' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Variable Name',
            ],
        ],
        'comment' => 'Variables',
    ],
    $installer->getTable('core/variable_value') => [
        'columns' => [
            'value_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Variable Value Id',
            ],
            'variable_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Variable Id',
            ],
            'store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Id',
            ],
            'plain_value' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Plain Text Value',
            ],
            'html_value' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Html Value',
            ],
        ],
        'comment' => 'Variable Value',
    ],
    $installer->getTable('core/translate') => [
        'columns' => [
            'key_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Key Id of Translation',
            ],
            'string' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'nullable'  => false,
                'default'   => Mage_Core_Model_Translate::DEFAULT_STRING,
                'comment'   => 'Translation String',
            ],
            'store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Id',
            ],
            'translate' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Translate',
            ],
            'locale' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 20,
                'nullable'  => false,
                'default'   => 'en_US',
                'comment'   => 'Locale',
            ],
        ],
        'comment' => 'Translations',
    ],
    $installer->getTable('core/session') => [
        'columns' => [
            'session_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Session Id',
            ],
            'session_expires' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Date of Session Expiration',
            ],
            'session_data' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_BLOB,
                'length'    => '2M',
                'nullable'  => false,
                'comment'   => 'Session Data',
            ],
        ],
        'comment' => 'Database Sessions Storage',
    ],
    $installer->getTable('core/layout_update') => [
        'columns' => [
            'layout_update_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Layout Update Id',
            ],
            'handle' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Handle',
            ],
            'xml' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Xml',
            ],
            'sort_order' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Sort Order',
            ],
        ],
        'comment' => 'Layout Updates',
    ],
    $installer->getTable('core/layout_link') => [
        'columns' => [
            'layout_link_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Link Id',
            ],
            'store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Id',
            ],
            'area' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 64,
                'comment'   => 'Area',
            ],
            'package' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 64,
                'comment'   => 'Package',
            ],
            'theme' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 64,
                'comment'   => 'Theme',
            ],
            'layout_update_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Layout Update Id',
            ],
        ],
        'comment' => 'Layout Link',
    ],
    $installer->getTable('core/url_rewrite') => [
        'columns' => [
            'url_rewrite_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Rewrite Id',
            ],
            'store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Id',
            ],
            'id_path' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Id Path',
            ],
            'request_path' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Request Path',
            ],
            'target_path' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Target Path',
            ],
            'is_system' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'default'   => '1',
                'comment'   => 'Defines is Rewrite System',
            ],
            'options' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Options',
            ],
            'description' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Deascription',
            ],
        ],
        'comment' => 'Url Rewrites',
    ],
    $installer->getTable('core/design_change') => [
        'columns' => [
            'design_change_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Design Change Id',
            ],
            'store_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Store Id',
            ],
            'design' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'comment'   => 'Design',
            ],
            'date_from' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_DATE,
                'comment'   => 'First Date of Design Activity',
            ],
            'date_to' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_DATE,
                'comment'   => 'Last Date of Design Activity',
            ],
        ],
        'comment' => 'Design Changes',
    ],
    $installer->getTable('core/flag') => [
        'columns' => [
            'flag_id' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'identity'  => true,
                'unsigned'  => true,
                'nullable'  => false,
                'primary'   => true,
                'comment'   => 'Flag Id',
            ],
            'flag_code' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => 255,
                'nullable'  => false,
                'comment'   => 'Flag Code',
            ],
            'state' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_SMALLINT,
                'unsigned'  => true,
                'nullable'  => false,
                'default'   => '0',
                'comment'   => 'Flag State',
            ],
            'flag_data' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                'length'    => '64K',
                'comment'   => 'Flag Data',
            ],
            'last_update' => [
                'type'      => Varien_Db_Ddl_Table::TYPE_TIMESTAMP,
                'nullable'  => false,
                'default'   => Varien_Db_Ddl_Table::TIMESTAMP_INIT_UPDATE,
                'comment'   => 'Date of Last Flag Update',
            ],
        ],
        'comment' => 'Flag',
    ],
];

if ($usedDatabaseStorage) {
    $storageTables = [
        $installer->getTable('core/file_storage') => [
            'columns' => [
                'file_id' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                    'identity'  => true,
                    'unsigned'  => true,
                    'nullable'  => false,
                    'primary'   => true,
                    'comment'   => 'File Id',
                ],
                'content' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_VARBINARY,
                    'length'    => Varien_Db_Ddl_Table::MAX_VARBINARY_SIZE,
                    'nullable'  => false,
                    'comment'   => 'File Content',
                ],
                'upload_time' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_TIMESTAMP,
                    'nullable'  => false,
                    'default'   => Varien_Db_Ddl_Table::TIMESTAMP_INIT,
                    'comment'   => 'Upload Timestamp',
                ],
                'filename' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                    'length'    => 100,
                    'nullable'  => false,
                    'comment'   => 'Filename',
                ],
                'directory_id' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                    'unsigned'  => true,
                    'default'   => null,
                    'comment'   => 'Identifier of Directory where File is Located',
                ],
                'directory' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                    'length'    => 255,
                    'default'   => null,
                    'comment'   => 'Directory Path',
                ],
            ],
            'comment' => 'File Storage',
        ],
        $installer->getTable('core/directory_storage') => [
            'columns' => [
                'directory_id' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                    'identity'  => true,
                    'unsigned'  => true,
                    'nullable'  => false,
                    'primary'   => true,
                    'comment'   => 'Directory Id',
                ],
                'name' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                    'length'    => 100,
                    'nullable'  => false,
                    'comment'   => 'Directory Name',
                ],
                'path' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
                    'length'    => 255,
                    'default'   => null,
                    'comment'   => 'Path to the Directory',
                ],
                'upload_time' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_TIMESTAMP,
                    'nullable'  => false,
                    'default'   => Varien_Db_Ddl_Table::TIMESTAMP_INIT,
                    'comment'   => 'Upload Timestamp',
                ],
                'parent_id' => [
                    'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                    'unsigned'  => true,
                    'default'   => null,
                    'comment'   => 'Parent Directory Id',
                ],
            ],
            'comment' => 'Directory Storage',
        ],
    ];
    $tables = array_merge($tables, $storageTables);
}

$installer->getConnection()->modifyTables($tables);

$installer->getConnection()->dropColumn(
    $installer->getTable('core/session'),
    'website_id',
);

/**
 * Add indexes
 */
$installer->getConnection()->addIndex(
    $installer->getTable('core/variable'),
    $installer->getIdxName(
        'core/variable',
        ['code'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['code'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/variable_value'),
    $installer->getIdxName(
        'core/variable_value',
        ['variable_id', 'store_id'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['variable_id', 'store_id'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/variable_value'),
    $installer->getIdxName('core/variable_value', ['variable_id']),
    ['variable_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/variable_value'),
    $installer->getIdxName('core/variable_value', ['store_id']),
    ['store_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/cache'),
    $installer->getIdxName('core/cache', ['expire_time']),
    ['expire_time'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/cache_tag'),
    $installer->getIdxName('core/cache_tag', ['cache_id']),
    ['cache_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/config_data'),
    $installer->getIdxName(
        'core/config_data',
        ['scope', 'scope_id', 'path'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['scope', 'scope_id', 'path'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/email_template'),
    $installer->getIdxName(
        'core/email_template',
        ['template_code'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['template_code'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/email_template'),
    $installer->getIdxName('core/email_template', ['added_at']),
    ['added_at'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/email_template'),
    $installer->getIdxName('core/email_template', ['modified_at']),
    ['modified_at'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/flag'),
    $installer->getIdxName('core/flag', ['last_update']),
    ['last_update'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/layout_link'),
    $installer->getIdxName(
        'core/layout_link',
        ['store_id', 'package', 'theme', 'layout_update_id'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['store_id', 'package', 'theme', 'layout_update_id'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/layout_link'),
    $installer->getIdxName('core/layout_link', ['layout_update_id']),
    ['layout_update_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/layout_update'),
    $installer->getIdxName('core/layout_update', ['handle']),
    ['handle'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/store'),
    $installer->getIdxName(
        'core/store',
        ['code'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['code'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/store'),
    $installer->getIdxName('core/store', ['website_id']),
    ['website_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/store'),
    $installer->getIdxName('core/store', ['is_active', 'sort_order']),
    ['is_active', 'sort_order'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/store'),
    $installer->getIdxName('core/store', ['group_id']),
    ['group_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/store_group'),
    $installer->getIdxName('core/store_group', ['website_id']),
    ['website_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/store_group'),
    $installer->getIdxName('core/store_group', ['default_store_id']),
    ['default_store_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/translate'),
    $installer->getIdxName(
        'core/translate',
        ['store_id', 'locale', 'string'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['store_id', 'locale', 'string'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/translate'),
    $installer->getIdxName('core/translate', ['store_id']),
    ['store_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/url_rewrite'),
    $installer->getIdxName(
        'core/url_rewrite',
        ['request_path', 'store_id'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['request_path', 'store_id'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/url_rewrite'),
    $installer->getIdxName(
        'core/url_rewrite',
        ['id_path', 'is_system', 'store_id'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['id_path', 'is_system', 'store_id'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/url_rewrite'),
    $installer->getIdxName('core/url_rewrite', ['target_path', 'store_id']),
    ['target_path', 'store_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/url_rewrite'),
    $installer->getIdxName('core/url_rewrite', ['id_path']),
    ['id_path'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/url_rewrite'),
    $installer->getIdxName('core/url_rewrite', ['store_id']),
    ['store_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/website'),
    $installer->getIdxName(
        'core/website',
        ['code'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    ),
    ['code'],
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/website'),
    $installer->getIdxName('core/website', ['sort_order']),
    ['sort_order'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/website'),
    $installer->getIdxName('core/website', ['default_group_id']),
    ['default_group_id'],
);

$installer->getConnection()->addIndex(
    $installer->getTable('core/design_change'),
    $installer->getIdxName('core/design_change', ['store_id']),
    ['store_id'],
);

if ($usedDatabaseStorage) {
    $installer->getConnection()->addIndex(
        $installer->getTable('core/file_storage'),
        $installer->getIdxName(
            'core/file_storage',
            ['filename', 'directory_id'],
            Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
        ),
        ['filename', 'directory_id'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    );

    $installer->getConnection()->addIndex(
        $installer->getTable('core/file_storage'),
        $installer->getIdxName('core/file_storage', ['directory_id']),
        ['directory_id'],
    );

    $installer->getConnection()->addIndex(
        $installer->getTable('core/directory_storage'),
        $installer->getIdxName(
            'core/directory_storage',
            ['name', 'parent_id'],
            Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
        ),
        ['name', 'parent_id'],
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE,
    );

    $installer->getConnection()->addIndex(
        $installer->getTable('core/directory_storage'),
        $installer->getIdxName('core/directory_storage', ['parent_id']),
        ['parent_id'],
    );
}

/**
 * Add foreign keys
 */

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/layout_link', 'store_id', 'core/store', 'store_id'),
    $installer->getTable('core/layout_link'),
    'store_id',
    $installer->getTable('core/store'),
    'store_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/layout_link', 'layout_update_id', 'core/layout_update', 'layout_update_id'),
    $installer->getTable('core/layout_link'),
    'layout_update_id',
    $installer->getTable('core/layout_update'),
    'layout_update_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/store', 'group_id', 'core/store_group', 'group_id'),
    $installer->getTable('core/store'),
    'group_id',
    $installer->getTable('core/store_group'),
    'group_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/store', 'website_id', 'core/website', 'website_id'),
    $installer->getTable('core/store'),
    'website_id',
    $installer->getTable('core/website'),
    'website_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/store_group', 'website_id', 'core/website', 'website_id'),
    $installer->getTable('core/store_group'),
    'website_id',
    $installer->getTable('core/website'),
    'website_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/translate', 'store_id', 'core/store', 'store_id'),
    $installer->getTable('core/translate'),
    'store_id',
    $installer->getTable('core/store'),
    'store_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/url_rewrite', 'product_id', 'catalog/product', 'entity_id'),
    $installer->getTable('core/url_rewrite'),
    'product_id',
    $installer->getTable('catalog/product'),
    'entity_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/url_rewrite', 'category_id', 'catalog/category', 'entity_id'),
    $installer->getTable('core/url_rewrite'),
    'category_id',
    $installer->getTable('catalog/category'),
    'entity_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/url_rewrite', 'store_id', 'core/store', 'store_id'),
    $installer->getTable('core/url_rewrite'),
    'store_id',
    $installer->getTable('core/store'),
    'store_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/variable_value', 'store_id', 'core/store', 'store_id'),
    $installer->getTable('core/variable_value'),
    'store_id',
    $installer->getTable('core/store'),
    'store_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/variable_value', 'variable_id', 'core/variable', 'variable_id'),
    $installer->getTable('core/variable_value'),
    'variable_id',
    $installer->getTable('core/variable'),
    'variable_id',
);

$installer->getConnection()->addForeignKey(
    $installer->getFkName('core/design_change', 'store_id', 'core/store', 'store_id'),
    $installer->getTable('core/design_change'),
    'store_id',
    $installer->getTable('core/store'),
    'store_id',
);

if ($usedDatabaseStorage) {
    $installer->getConnection()->addForeignKey(
        $installer->getFkName('core/file_storage', 'directory_id', 'core/directory_storage', 'directory_id'),
        $installer->getTable('core/file_storage'),
        'directory_id',
        $installer->getTable('core/directory_storage'),
        'directory_id',
    );

    $installer->getConnection()->addForeignKey(
        $installer->getFkName('core/directory_storage', 'parent_id', 'core/directory_storage', 'directory_id'),
        $installer->getTable('core/directory_storage'),
        'parent_id',
        $installer->getTable('core/directory_storage'),
        'directory_id',
    );
}

$installer->endSetup();
