CREATE DATABASE `nurude_tozan`
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
/*!80016 DEFAULT ENCRYPTION='N' */

CREATE TABLE `geo` (
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `act` tinyint(1) NOT NULL COMMENT '0:無効，1:有効',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  `alt` SMALLINT NOT NULL COMMENT '標高[m]',
  `lat` mediumint NOT NULL COMMENT '緯度（dms）',
  `lon` mediumint NOT NULL COMMENT '経度（dms）',
  `auth` tinyint UNSIGNED DEFAULT '0' COMMENT '出典',
  `note` text COMMENT '記事',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `sanmei` (
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `type` tinyint NOT NULL COMMENT '0:総称，1:山名，2:別名',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  UNIQUE KEY `idx_sanmei` (`id`,`kana`,`name`),
  KEY `name` (`name`) USING BTREE,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `meizan` (
  `cat` tinyint UNSIGNED NOT NULL COMMENT 'カテゴリ',
  `seqno` SMALLINT UNSIGNED NOT NULL COMMENT 'カテゴリ内の順序',
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  UNIQUE KEY `idx_meizan` (`cat`,`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `record` (
  `rec` SMALLINT UNSIGNED NOT NULL COMMENT '山行記録ID',
  `start` DATE NOT NULL COMMENT '開始日',
  `end` DATE NOT NULL COMMENT '終了日',
  `issue` DATE DEFAULT NULL COMMENT '公開日',
  `title` VARCHAR(255) NOT NULL COMMENT 'タイトル',
  `summary` VARCHAR(255) DEFAULT NULL COMMENT '概略',
  `link` VARCHAR(255) DEFAULT NULL COMMENT '山行記録URL',
  `image` VARCHAR(255) DEFAULT NULL COMMENT '画像URL',
  PRIMARY KEY (`rec`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `explored` (
  `rec` SMALLINT UNSIGNED NOT NULL COMMENT '山行記録ID',
  `start` DATE NOT NULL COMMENT '開始日',
  `summit` DATE NOT NULL COMMENT '登頂日',
  `id` SMALLINT UNSIGNED NOT NULL COMMENT 'ID',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  UNIQUE KEY `idx_explored` (`rec`,`id`),
  KEY `id` (`id`) USING BTREE,
  KEY `rec` (`rec`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `poi` (
  `ptid` mediumint UNSIGNED NOT NULL COMMENT 'PTID',
  `act` tinyint(1) NOT NULL COMMENT '0:無効, 1:有効',
  `kana` VARCHAR(255) NOT NULL COMMENT 'よみ',
  `name` VARCHAR(255) NOT NULL COMMENT '山名',
  `alt` SMALLINT NOT NULL COMMENT '標高[m]',
  `lat` mediumint NOT NULL COMMENT '緯度（dms)',
  `lon` mediumint NOT NULL COMMENT '経度（dms）',
  `id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'ID',
  `c` tinyint NOT NULL DEFAULT '-1' COMMENT '一致度',
  PRIMARY KEY (`ptid`),
  KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `gyosei` (
  `code` smallint unsigned NOT NULL,
  `area` geometry NOT NULL /*!80003 SRID 4326 */,
  SPATIAL KEY `area` (`area`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `city` (
  `code` smallint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci