CREATE TABLE IF NOT EXISTS `directory_categories` (
  `category_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned DEFAULT NULL,
  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
  `category_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `seo_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `photo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`category_id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `directory_companies` (
  `company_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
  `company` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `seo_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `contact` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `province` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `postalcode` char(6) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `fax` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `website` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  `logo` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `photo` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `added` datetime NOT NULL,
  `latitude` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `longitude` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sv_heading` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sv_pitch` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sv_zoom` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `directory_companies_categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(11) unsigned NOT NULL,
  `category_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `directory_coupons` (
  `coupon_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
  `company_id` int(11) unsigned NOT NULL,
  `valid_from` date DEFAULT NULL,
  `valid_to` date DEFAULT NULL,
  `coupon` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`coupon_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `directory_categories` ADD CONSTRAINT `directory_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `directory_categories` (`category_id`) ON UPDATE CASCADE;
ALTER TABLE `directory_companies_categories` ADD CONSTRAINT `directory_companies_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `directory_companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `directory_companies_categories` ADD CONSTRAINT `directory_companies_categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `directory_categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `directory_coupons` ADD CONSTRAINT `directory_coupons_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `directory_companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `events` ADD IF NOT EXISTS `parent_id` int(11) unsigned DEFAULT NULL AFTER `site_id`;
ALTER TABLE `events` ADD IF NOT EXISTS `is_parent` tinyint(1) NOT NULL DEFAULT '0' AFTER `parent_id`;
ALTER TABLE `events` ADD IF NOT EXISTS `seo_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT '' AFTER `headline`;
ALTER TABLE `events` ADD IF NOT EXISTS `banner_image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL AFTER `seo_name`;
ALTER TABLE `events` ADD IF NOT EXISTS `preview` text COLLATE utf8_unicode_ci AFTER `banner_image`;
ALTER TABLE `events` ADD IF NOT EXISTS `galleries` text COLLATE utf8_unicode_ci AFTER `description`;

CREATE TABLE IF NOT EXISTS `events_forms` (
  `form_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_id` int(11) unsigned NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `registration_start` date NOT NULL,
  `registration_end` date NOT NULL,
  `fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`form_id`),
  KEY `event_id` (`event_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `events` ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `events` (`id`) ON UPDATE CASCADE;
ALTER TABLE `events_forms` ADD CONSTRAINT `events_forms_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
