--
-- Merge some concepts of the functionalities and site model.
-- Created a common concept for both functionalities and sections and items. Merged tables.
--

START TRANSACTION;

-- prepare table for the entire hierarchy
ALTER TABLE `FUNCTIONALITY` RENAME TO `ACCESSIBLE_ITEM`;

ALTER TABLE `AVAILABILITY_POLICY` CHANGE COLUMN `KEY_FUNCTIONALITY` `KEY_ACCESSIBLE_ITEM` INTEGER  NOT NULL;

ALTER TABLE `ACCESSIBLE_ITEM` 
	ADD COLUMN `KEY_SUPERIOR_SECTION` INTEGER,
	ADD COLUMN `SECTION_ORDER` INTEGER,
	ADD COLUMN `KEY_SITE` INTEGER,
	ADD COLUMN `LAST_MODIFIED_DATE_YEAR_MONTH_DAY` DATE,
	ADD COLUMN `KEY_SECTION` INTEGER,
	ADD COLUMN `ITEM_ORDER` INTEGER,
	ADD COLUMN `INFORMATION` LONGTEXT;

ALTER TABLE `ACCESSIBLE_ITEM` MODIFY COLUMN `UUID` VARCHAR(255);

ALTER TABLE `ACCESSIBLE_ITEM` 
	ADD INDEX `KEY_SUPERIOR_SECTION` (`KEY_SUPERIOR_SECTION`),
	ADD INDEX `KEY_SECTION` (`KEY_SECTION`);

-- add temporary fields to ease merge
ALTER TABLE `ACCESSIBLE_ITEM` 
	ADD COLUMN `SECTION_ID_INTERNAL` INTEGER,
	ADD COLUMN `ITEM_ID_INTERNAL` INTEGER;

ALTER TABLE `ACCESSIBLE_ITEM` 
	ADD INDEX `SECTION_ID_INTERNAL` (`SECTION_ID_INTERNAL`),
	ADD INDEX `ITEM_ID_INTERNAL` (`ITEM_ID_INTERNAL`);
 
-- import sections and items
INSERT INTO `ACCESSIBLE_ITEM` (`OJB_CONCRETE_CLASS`, `VISIBLE`, `ENABLED`, `KEY_ROOT_DOMAIN_OBJECT`, `SECTION_ID_INTERNAL`, `NAME`, `KEY_SUPERIOR_SECTION`, `SECTION_ORDER`, `KEY_SITE`, `LAST_MODIFIED_DATE_YEAR_MONTH_DAY`) 
	SELECT 'net.sourceforge.fenixedu.domain.Section', 1, 1, `KEY_ROOT_DOMAIN_OBJECT`, `ID_INTERNAL`, `NAME`, `KEY_SUPERIOR_SECTION`, `SECTION_ORDER`, `KEY_SITE`, `LAST_MODIFIED_DATE_YEAR_MONTH_DAY`
	FROM `SECTION`;

INSERT INTO `ACCESSIBLE_ITEM` (`OJB_CONCRETE_CLASS`, `VISIBLE`, `ENABLED`, `KEY_ROOT_DOMAIN_OBJECT`, `ITEM_ID_INTERNAL`, `NAME`, `INFORMATION`, `KEY_SECTION`, `ITEM_ORDER`)
	SELECT 'net.sourceforge.fenixedu.domain.Item', 1, 1, `KEY_ROOT_DOMAIN_OBJECT`, `ID_INTERNAL`, `NAME`, `INFORMATION`, `KEY_SECTION`, `ITEM_ORDER`
	FROM `ITEM`;

-- update KEY_SUPERIOR_SECTION based on SECTION_ID_INTERNAL
UPDATE `ACCESSIBLE_ITEM` AS a1, `ACCESSIBLE_ITEM` AS a2
	SET a1.`KEY_SUPERIOR_SECTION` = a2.`ID_INTERNAL` 
	WHERE a1.`KEY_SUPERIOR_SECTION` = a2.`SECTION_ID_INTERNAL`;

-- update KEY_SECTION based on SECTION_ID_INTERNAL
UPDATE `ACCESSIBLE_ITEM` AS a1, `ACCESSIBLE_ITEM` AS a2
	SET a1.`KEY_SECTION` = a2.`ID_INTERNAL` 
	WHERE a1.`KEY_SECTION` = a2.`SECTION_ID_INTERNAL`;

-- update ITEM-FILE relation
UPDATE `ITEM_FILE_ITEM` AS ifi, `ACCESSIBLE_ITEM` as a, `ITEM` as i
	SET ifi.`KEY_ITEM` = a.`ID_INTERNAL`
	WHERE ifi.`KEY_ITEM` = i.`ID_INTERNAL` AND i.`ID_INTERNAL` = a.`ITEM_ID_INTERNAL`;

-- drop temporary columns and indexes
ALTER TABLE `ACCESSIBLE_ITEM`
	DROP INDEX `SECTION_ID_INTERNAL`, 
	DROP INDEX `ITEM_ID_INTERNAL`;
	
ALTER TABLE `ACCESSIBLE_ITEM`
	DROP COLUMN `SECTION_ID_INTERNAL`,
	DROP COLUMN `ITEM_ID_INTERNAL`;

-- drop old section and item tables
DROP TABLE `ITEM`;
DROP TABLE `SECTION`;

COMMIT;
