 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
 SET time_zone = "+00:00";
-
-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
-/*!40101 SET NAMES utf8 */;
-
 CREATE DATABASE IF NOT EXISTS `acfe` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 USE `acfe`;
 

ALTER TABLE `place_facts`
ADD CONSTRAINT `place_facts_ibfk_1` FOREIGN KEY (`place_id`) REFERENCES `places` (`id`),
ADD CONSTRAINT `place_facts_ibfk_2` FOREIGN KEY (`fact_id`) REFERENCES `facts` (`id`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

  `lat` decimal(10,5) NOT NULL,
  `lng` decimal(10,5) NOT NULL,
  `place_category_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `place_categories`;
CREATE TABLE IF NOT EXISTS `place_categories` (
`id` int(11) NOT NULL,
  `title` varchar(40) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `place_facts`;
CREATE TABLE IF NOT EXISTS `place_facts` (
`place_fact_id` int(11) NOT NULL,
  `place_id` int(11) NOT NULL,
  `fact_id` int(11) NOT NULL,
  `detail_text` varchar(1000) DEFAULT NULL,
  `detail_value` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=944 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `regions`;
CREATE TABLE IF NOT EXISTS `regions` (
`id` int(11) NOT NULL,
  `title` varchar(20) NOT NULL,
  `colour` varchar(7) NOT NULL DEFAULT '#00ff00'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


ALTER TABLE `areas`
 ADD PRIMARY KEY (`id`), ADD KEY `region_id` (`region_id`);

ALTER TABLE `area_facts`
 ADD PRIMARY KEY (`area_fact_id`), ADD UNIQUE KEY `area_id` (`area_id`,`fact_id`), ADD KEY `fact_id` (`fact_id`);

ALTER TABLE `facts`
 ADD PRIMARY KEY (`id`), ADD KEY `fact_category_id` (`fact_category_id`);

ALTER TABLE `fact_categories`
 ADD PRIMARY KEY (`id`);
