/*
MySQL Backup
Database: lqmkupmv_sukusapo
Backup Time: 2026-05-22 13:40:35
*/

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `lqmkupmv_sukusapo`.`assignments`;
DROP TABLE IF EXISTS `lqmkupmv_sukusapo`.`children`;
DROP TABLE IF EXISTS `lqmkupmv_sukusapo`.`events`;
DROP TABLE IF EXISTS `lqmkupmv_sukusapo`.`line_members`;
DROP TABLE IF EXISTS `lqmkupmv_sukusapo`.`schools`;
DROP TABLE IF EXISTS `lqmkupmv_sukusapo`.`slots`;
CREATE TABLE `assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `event_slot_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `assignment_date` date DEFAULT NULL,
  `parent_name` varchar(255) NOT NULL,
  `brought_items` varchar(255) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `remind_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_assignments_event_slot_child` (`event_id`,`event_slot_id`,`person_id`),
  UNIQUE KEY `uq_assignments_event_slot_person` (`event_id`,`event_slot_id`,`person_id`),
  KEY `idx_assignments_child_id` (`person_id`),
  CONSTRAINT `fk_assignments_child_id` FOREIGN KEY (`person_id`) REFERENCES `children` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
CREATE TABLE `children` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `grade_class` varchar(50) DEFAULT NULL,
  `last_active_at` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_children_member_name` (`member_id`,`name`),
  KEY `idx_children_last_active_at` (`last_active_at`),
  KEY `idx_children_member_id` (`member_id`),
  CONSTRAINT `fk_children_member_id` FOREIGN KEY (`member_id`) REFERENCES `line_members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `date_type` varchar(10) NOT NULL DEFAULT 'single',
  `event_date` date DEFAULT NULL,
  `event_start_date` date DEFAULT NULL,
  `event_end_date` date DEFAULT NULL,
  `backup_date1` date DEFAULT NULL,
  `backup_date2` date DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `allow_duplicate_registration` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_events_school_date` (`school_id`,`event_date`),
  KEY `idx_events_school_created` (`school_id`,`created_at`),
  KEY `idx_events_school_start_date` (`school_id`,`event_start_date`),
  CONSTRAINT `fk_events_school_id` FOREIGN KEY (`school_id`) REFERENCES `schools` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
CREATE TABLE `line_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `line_user_id` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `picture_url` varchar(1024) DEFAULT NULL,
  `last_login_at` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_line_members_line_user_id` (`line_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
CREATE TABLE `schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `admin_password_hash` varchar(255) NOT NULL,
  `plan_status` varchar(50) NOT NULL DEFAULT 'free',
  `max_notification_limit` int(11) NOT NULL DEFAULT 0,
  `current_notification_count` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
CREATE TABLE `slots` (
  `event_id` int(11) NOT NULL,
  `event_slot_id` int(11) NOT NULL,
  `role_name` varchar(255) NOT NULL,
  `date_type` varchar(10) NOT NULL DEFAULT 'single',
  `slot_date` date DEFAULT NULL,
  `slot_start_date` date DEFAULT NULL,
  `slot_end_date` date DEFAULT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `sf_display_type` varchar(20) NOT NULL DEFAULT '1',
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `capacity` int(11) NOT NULL,
  `bring_items_options` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`event_id`,`event_slot_id`) USING BTREE,
  KEY `idx_slots_event_start` (`event_id`,`start_at`),
  KEY `idx_slots_event_start_date_time` (`event_id`,`slot_start_date`,`start_time`),
  CONSTRAINT `slots_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
DELETE FROM `lqmkupmv_sukusapo`.`assignments`;
INSERT INTO `lqmkupmv_sukusapo`.`assignments` (`id`,`event_id`,`event_slot_id`,`person_id`,`assignment_date`,`parent_name`,`brought_items`,`message`,`remind_enabled`,`created_at`) VALUES (1, 1, 1, 7, '2026-05-23', '杉山１', NULL, NULL, 1, '2026-05-22 12:25:46'),(2, 1, 1, 8, '2026-05-23', '杉山２', NULL, NULL, 1, '2026-05-22 12:25:46');
DELETE FROM `lqmkupmv_sukusapo`.`children`;
INSERT INTO `lqmkupmv_sukusapo`.`children` (`id`,`member_id`,`name`,`grade_class`,`last_active_at`,`created_at`,`updated_at`) VALUES (3, 7, '杉山幸奈', '3年2組', '2026-05-20 10:15:18', '2026-05-20 10:15:18', '2026-05-20 10:15:18'),(7, 12, 'お子様_17794203462065_0_2527', NULL, '2026-05-22 12:25:46', '2026-05-22 12:25:46', '2026-05-22 12:25:46'),(8, 13, 'お子様_17794203462066_1_7130', NULL, '2026-05-22 12:25:46', '2026-05-22 12:25:46', '2026-05-22 12:25:46');
DELETE FROM `lqmkupmv_sukusapo`.`events`;
INSERT INTO `lqmkupmv_sukusapo`.`events` (`id`,`school_id`,`title`,`date_type`,`event_date`,`event_start_date`,`event_end_date`,`backup_date1`,`backup_date2`,`location`,`allow_duplicate_registration`,`created_at`,`updated_at`) VALUES (1, 1, 'プール清掃', 'single', '2026-05-23', '2026-05-23', NULL, '2026-05-24', '2026-05-30', '安東小学校プール', 0, '2026-05-19 14:36:23', '2026-05-20 15:19:42'),(2, 1, 'ベルマーク集計員募集！', 'single', '2026-05-29', '2026-05-29', NULL, '2026-06-05', NULL, '安東小学校　中央校舎1F', 0, '2026-05-19 15:01:32', '2026-05-20 12:32:01'),(3, 1, '旗振り当番', 'range', '2026-06-01', '2026-06-01', '2026-07-31', NULL, NULL, '複数地点', 0, '2026-05-20 11:40:06', '2026-05-21 15:47:42');
DELETE FROM `lqmkupmv_sukusapo`.`line_members`;
INSERT INTO `lqmkupmv_sukusapo`.`line_members` (`id`,`line_user_id`,`display_name`,`picture_url`,`last_login_at`,`created_at`) VALUES (7, 'U0755a78348218c40b2c06f4ed44c7c02', '杉山幸弘', 'https://profile.line-scdn.net/0hrfHNJ3-ILUllNwLoVpZTNhVnLiNGRnRbTVExKFg0IClbVW8aQFkyfQA-cy4PVDhKTlM1KlE2cX1pJFove2HRfWIHcHhYBmodSldqqA', '2026-05-20 19:42:28', '2026-05-20 10:15:07'),(12, 'admin_1_17794203462087_0_2498', '杉山１', NULL, '2026-05-22 12:25:46', '2026-05-22 12:25:46'),(13, 'admin_1_17794203462099_1_1717', '杉山２', NULL, '2026-05-22 12:25:46', '2026-05-22 12:25:46');
DELETE FROM `lqmkupmv_sukusapo`.`schools`;
INSERT INTO `lqmkupmv_sukusapo`.`schools` (`id`,`name`,`admin_password_hash`,`plan_status`,`max_notification_limit`,`current_notification_count`,`created_at`,`updated_at`) VALUES (1, '安東小学校', '$2y$10$fDSGJyBYcn4yxfBRf2g0.Oykl4omagwAVZAa.5UOo7o3DZS4Mymx2', 'free', 0, 0, '2026-05-19 14:30:31', '2026-05-19 14:33:02');
DELETE FROM `lqmkupmv_sukusapo`.`slots`;
INSERT INTO `lqmkupmv_sukusapo`.`slots` (`event_id`,`event_slot_id`,`role_name`,`date_type`,`slot_date`,`slot_start_date`,`slot_end_date`,`start_time`,`end_time`,`sf_display_type`,`start_at`,`end_at`,`capacity`,`bring_items_options`,`created_at`,`updated_at`) VALUES (1, 1, '清掃員', 'single', '2026-05-23', '2026-05-23', NULL, '10:00:00', '12:00:00', '1', '2026-05-23 10:00:00', '2026-05-23 12:00:00', 10, 'デッキブラシ・サンダル・タオル・水筒（飲み物）', '2026-05-19 14:49:39', '2026-05-21 11:37:11'),(1, 2, '監視員', 'single', '2026-05-23', '2026-05-23', NULL, '10:00:00', '12:00:00', '1', '2026-05-23 10:00:00', '2026-05-23 12:00:00', 2, '笛', '2026-05-21 11:15:00', '2026-05-21 11:37:12'),(2, 1, '【前半】集計員', 'single', '2026-05-29', '2026-05-29', NULL, '10:00:00', '11:00:00', '1', '2026-05-29 10:00:00', '2026-05-29 11:00:00', 5, 'スリッパ', '2026-05-19 15:03:07', '2026-05-21 11:37:12'),(2, 2, '【後半】集計員', 'single', '2026-05-29', '2026-05-29', NULL, '11:00:00', '12:00:00', '1', '2026-05-29 11:00:00', '2026-05-29 12:00:00', 5, 'スリッパ', '2026-05-19 15:03:35', '2026-05-21 11:37:12'),(3, 1, '正門前', 'range', '2026-06-01', '2026-06-01', '2026-07-31', '08:00:00', '08:30:00', '2', '2026-06-01 08:00:00', '2026-07-31 08:30:00', 1, '横断旗', '2026-05-21 15:46:23', '2026-05-21 15:50:50'),(3, 2, '南門', 'range', '2026-06-01', '2026-06-01', '2026-07-31', '08:00:00', '08:30:00', '2', '2026-06-01 08:00:00', '2026-07-31 08:30:00', 1, '横断旗', '2026-05-21 16:20:57', '2026-05-21 16:20:57'),(3, 3, 'ウェルシア前', 'range', '2026-06-01', '2026-06-01', '2026-07-31', '08:00:00', '08:30:00', '2', '2026-06-01 08:00:00', '2026-07-31 08:30:00', 1, '横断旗', '2026-05-21 16:21:31', '2026-05-21 16:21:31');