-- create a database and some tables.
DROP DATABASE IF EXISTS test1
;
CREATE DATABASE test1
;
use test1
;
CREATE TABLE `facility2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Facility` varchar(80) DEFAULT NULL,
  `prefecture_id` tinyint(4) DEFAULT NULL,
  `address_1` varchar(120) DEFAULT NULL,
  `address_2` text,
  `address_3` text,
  `longitude` double(12,6) DEFAULT NULL,
  `latitude` double(12,6) DEFAULT NULL,
  `altitude` double(12,6) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `index_facility2_on_prefecture_id_address_1` (`prefecture_id`,`address_1`),
  KEY `index_facility2_on_longitude_latitude` (`longitude`,`latitude`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `facility2_damages` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Facility_ID` int(11) NOT NULL,
  `damage_type` int(11) NOT NULL,
  `comment` text,
  `contact` text,
  `regist_date` datetime DEFAULT NULL,
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Facility_ID` (`Facility_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ADD INDEX
CREATE INDEX `for_latest_sort` ON `facility2_damages` (regist_date DESC, update_date DESC, ID DESC)
;
