CREATE TABLE `stat_type` (
  `stat_type_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stat_name` varchar(255) NOT NULL DEFAULT '',
  `constant` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`stat_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `stats` (
  `stat_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stat_type_id` int(11) unsigned NOT NULL,
  `stat_date` date NOT NULL,
  `stat_hour` tinyint(2) NOT NULL,
  `page_id` int(11) unsigned DEFAULT NULL,
  `document_id` int(11) unsigned DEFAULT NULL,
  `module_instance_id` int(11) unsigned DEFAULT NULL,
  `shop_category_id` int(11) unsigned DEFAULT NULL,
  `shop_product_id` int(11) unsigned DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stat_id`),
  KEY `stat_type_id` (`stat_type_id`),
  KEY `page_id` (`page_id`),
  KEY `document_id` (`document_id`),
  KEY `module_instance_id` (`module_instance_id`),
  KEY `shop_category_id` (`shop_category_id`),
  KEY `shop_product_id` (`shop_product_id`),
  KEY `stat_date` (`stat_date`),
  CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`stat_type_id`) REFERENCES `stat_type` (`stat_type_id`) ON UPDATE CASCADE,
  CONSTRAINT `stats_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `pages` (`page_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stats_ibfk_3` FOREIGN KEY (`document_id`) REFERENCES `document` (`document_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stats_ibfk_4` FOREIGN KEY (`module_instance_id`) REFERENCES `module_instances` (`module_instance_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stats_ibfk_5` FOREIGN KEY (`shop_category_id`) REFERENCES `shop_categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `stats_ibfk_6` FOREIGN KEY (`shop_product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `stat_type` (`stat_type_id`, `stat_name`, `constant`)
VALUES
	(1, 'Page View', 'PAGE'),
	(2, 'Contact Form Submission', 'CONTACT'),
	(3, 'Document Download', 'DOCUMENT'),
	(4, 'Shop Category View', 'SHOP_CATEGORY'),
	(5, 'Shop Product View', 'SHOP_PRODUCT');
