CREATE TABLE IF NOT EXISTS `page_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `priority` tinyint(2) NOT NULL DEFAULT '1',
  `keyword` varchar(250) NOT NULL,
  `status` enum('Enabled','Disabled') NOT NULL DEFAULT 'Enabled',
  `type` enum('System','Regular','Module') NOT NULL DEFAULT 'Regular',
  `newwindow` enum('Yes','No') NOT NULL DEFAULT 'No',
  `clickable` enum('Yes','No') NOT NULL DEFAULT 'Yes',
  `route` varchar(50) DEFAULT NULL,
  `visibility` enum('all','anonymous','users','hide') NOT NULL DEFAULT 'all',
  `template` varchar(250) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `keyword` (`keyword`),
  KEY `parentId` (`parentId`),
  KEY `status` (`status`),
  KEY `route` (`route`),
  KEY `type` (`type`),
  KEY `path` (`path`),
  KEY `priority` (`priority`),
  KEY `parentId_2` (`parentId`,`path`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `page_pages_i18n` (
  `id` int(11) NOT NULL,
  `label` varchar(250) NOT NULL,
  `url` VARCHAR(250) DEFAULT NULL,
  `content` text NOT NULL,
  `header` text DEFAULT NULL,
  `metaTitle` varchar(250) NOT NULL,
  `metaDescr` varchar(250) NOT NULL,
  `metaKeywords` varchar(250) NOT NULL,
  `dublinCoreMeta` text NOT NULL,
  `geoTaggingMeta` text NOT NULL,
  `locale` varchar(5) NOT NULL DEFAULT 'en_US',
  KEY `id` (`id`),
  KEY `locale` (`locale`),
  KEY `label_2` (`label`),
  FULLTEXT KEY `label` (`label`,`content`,`metaTitle`,`metaDescr`,`metaKeywords`),
  FULLTEXT KEY `dublinCoreMeta` (`dublinCoreMeta`,`geoTaggingMeta`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS `pathEnumeration`;
#--- If execute in manual, need set delimiter
#--- DELIMITER //
CREATE PROCEDURE pathEnumeration()
    BEGIN
        DECLARE _id        INT DEFAULT 0;
        DECLARE _parentId  INT DEFAULT 0;
        DECLARE _path      VARCHAR(255);
        DECLARE pathParent VARCHAR(255);

        DECLARE getItems CURSOR FOR (
            SELECT page.id, page.parentId, page.path 
            FROM page_pages as page 
            WHERE 1 ORDER BY page.parentId ASC, page.id ASC
            );

        OPEN getItems;
        cycle: LOOP
            FETCH getItems INTO _id, _parentId, _path;
            IF NOT _id THEN
                LEAVE cycle;
            END IF;

            IF _parentId = 0 THEN
                UPDATE `page_pages` as p SET p.path=CONCAT(_id,'/') WHERE p.id=_id;
            ELSE
                SET pathParent:=(SELECT p.path FROM page_pages as p WHERE p.id=_parentId);
                UPDATE page_pages as p SET p.path=CONCAT(pathParent, _id, '/') WHERE p.id=_id;
            END IF;

            ITERATE cycle;
        END LOOP cycle;
        CLOSE getItems;
    END #--- //
#--- DELIMITER ;
