-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net

CREATE TABLE IF NOT EXISTS `factbook_versions` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `factbook_news` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `date` char(10) COLLATE utf8_bin NOT NULL,
  `text` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  CONSTRAINT `factbook_news_ibfk_1` FOREIGN KEY (`version`) REFERENCES `factbook_versions` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `factbook_definitions` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `content` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`name`),
  CONSTRAINT `factbook_definitions_ibfk_1` FOREIGN KEY (`version`) REFERENCES `factbook_versions` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `factbook_faq` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `category` varchar(255) COLLATE utf8_bin NOT NULL,
  `question` text COLLATE utf8_bin NOT NULL,
  `answer` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`question`(255)),
  CONSTRAINT `factbook_faq_ibfk_1` FOREIGN KEY (`version`) REFERENCES `factbook_versions` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `factbook_categories` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `description` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`name`),
  CONSTRAINT `factbook_categories_ibfk_1` FOREIGN KEY (`version`) REFERENCES `factbook_versions` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_fields` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `categoryid` int(11) NOT NULL,
  `xmlid` char(5) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `description` text COLLATE utf8_bin NOT NULL,
  `rankorder` int(11) NOT NULL,
  `unit` varchar(50) COLLATE utf8_bin NOT NULL,
  `dollars` tinyint(4) NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`xmlid`),
  CONSTRAINT `factbook_fields_ibfk_1` FOREIGN KEY (`version`, `categoryid`) REFERENCES `factbook_categories` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_ranks` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `fieldid` int(11) NOT NULL,
  `country` varchar(255) COLLATE utf8_bin NOT NULL,
  `datetext` varchar(255) COLLATE utf8_bin NOT NULL,
  `dateearliest` char(10) COLLATE utf8_bin NOT NULL,
  `datelatest` char(10) COLLATE utf8_bin NOT NULL,
  `dateestimated` tinyint(4) NOT NULL,
  `number` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  CONSTRAINT `factbook_ranks_ibfk_1` FOREIGN KEY (`version`, `fieldid`) REFERENCES `factbook_fields` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `factbook_regions` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `shortid` char(3) COLLATE utf8_bin NOT NULL,
  `longid` char(3) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique1` (`version`,`shortid`),
  UNIQUE KEY `unique2` (`version`,`longid`),
  CONSTRAINT `factbook_regions_ibfk_1` FOREIGN KEY (`version`) REFERENCES `factbook_versions` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_countries` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `regionid` int(11) NOT NULL,
  `xmlid` char(2) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `affiliation` varchar(255) COLLATE utf8_bin NOT NULL,
  `lastupdate` char(10) COLLATE utf8_bin NOT NULL,
  `photocount` int(11) NOT NULL,
  `flag` tinyint(4) NOT NULL,
  `locator` tinyint(4) NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`xmlid`),
  CONSTRAINT `factbook_countries_ibfk_2` FOREIGN KEY (`version`, `regionid`) REFERENCES `factbook_regions` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_countryaliases` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `countryid` int(11) NOT NULL,
  `alias` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  CONSTRAINT `factbook_countryaliases_ibfk_1` FOREIGN KEY (`version`, `countryid`) REFERENCES `factbook_countries` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_values` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `countryid` int(11) NOT NULL,
  `fieldid` int(11) NOT NULL,
  `value` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`countryid`,`fieldid`),
  CONSTRAINT `factbook_values_ibfk_1` FOREIGN KEY (`version`, `fieldid`) REFERENCES `factbook_fields` (`version`, `id`),
  CONSTRAINT `factbook_values_ibfk_2` FOREIGN KEY (`version`, `countryid`) REFERENCES `factbook_countries` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE IF NOT EXISTS `factbook_appendices` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `letter` char(1) COLLATE utf8_bin NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`letter`),
  CONSTRAINT `factbook_appendices_ibfk_1` FOREIGN KEY (`version`) REFERENCES `factbook_versions` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_appendixtables` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `appendixid` int(11) NOT NULL,
  `lettergrouped` tinyint(4) NOT NULL,
  `title` varchar(255) COLLATE utf8_bin NOT NULL,
  `columns` int(11) NOT NULL,
  PRIMARY KEY (`version`,`id`),
  CONSTRAINT `factbook_appendixtables_ibfk_1` FOREIGN KEY (`version`, `appendixid`) REFERENCES `factbook_appendices` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `factbook_appendixcells` (
  `version` char(10) COLLATE utf8_bin NOT NULL,
  `id` int(11) NOT NULL,
  `tableid` int(11) NOT NULL,
  `row` int(11) NOT NULL,
  `column` int(11) NOT NULL,
  `value` text COLLATE utf8_bin NOT NULL,
  `center` tinyint(4) NOT NULL,
  `country` char(2) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`version`,`id`),
  UNIQUE KEY `unique` (`version`,`tableid`,`row`,`column`),
  CONSTRAINT `factbook_appendixcells_ibfk_1` FOREIGN KEY (`version`, `tableid`) REFERENCES `factbook_appendixtables` (`version`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
