-- =============================================================================
-- bookstore サンプルデータベース
--
-- 書店の販売履歴を題材にした学習用データベース。
-- JOIN・集計・サブクエリなど、実務で頻出する SELECT を練習できる規模で構成。
--
-- テーブル:
--   authors      著者
--   categories   書籍カテゴリ
--   books        書籍 (authors, categories を参照)
--   customers    顧客
--   sales        販売記録 (books, customers を参照)
--   tags         タグ
--   book_tags    書籍とタグの中間テーブル
-- =============================================================================


DROP DATABASE IF EXISTS bookstore;
CREATE DATABASE bookstore DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE bookstore;

-- 著者
CREATE TABLE authors (
  id         INT              NOT NULL AUTO_INCREMENT,
  name       VARCHAR(100)     NOT NULL,
  birth_year SMALLINT         DEFAULT NULL,
  country    VARCHAR(50)      NOT NULL,
  created_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- カテゴリ
CREATE TABLE categories (
  id         INT              NOT NULL AUTO_INCREMENT,
  name       VARCHAR(50)      NOT NULL,
  created_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- 書籍
CREATE TABLE books (
  id             INT          NOT NULL AUTO_INCREMENT,
  isbn           CHAR(13)     NOT NULL UNIQUE,
  title          VARCHAR(100) NOT NULL,
  author_id      INT          NOT NULL,
  category_id    INT          NOT NULL,
  price          INT UNSIGNED NOT NULL,
  published_at   DATE         NOT NULL,
  stock          INT UNSIGNED NOT NULL DEFAULT 0,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (author_id)   REFERENCES authors(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 顧客
CREATE TABLE customers (
  id         INT              NOT NULL AUTO_INCREMENT,
  name       VARCHAR(100)     NOT NULL,
  email      VARCHAR(255)     NOT NULL UNIQUE,
  prefecture VARCHAR(20)      NOT NULL,
  created_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- 販売記録
CREATE TABLE sales (
  id           INT            NOT NULL AUTO_INCREMENT,
  book_id      INT            NOT NULL,
  customer_id  INT            NOT NULL,
  quantity     INT UNSIGNED   NOT NULL,
  total_price  INT UNSIGNED   NOT NULL,
  sold_at      DATETIME       NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (book_id)     REFERENCES books(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- タグ
CREATE TABLE tags (
  id         INT              NOT NULL AUTO_INCREMENT,
  name       VARCHAR(30)      NOT NULL UNIQUE,
  created_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- 書籍とタグの関連 (中間テーブル)
CREATE TABLE book_tags (
  book_id    INT              NOT NULL,
  tag_id     INT              NOT NULL,
  created_at TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (book_id, tag_id),
  FOREIGN KEY (book_id) REFERENCES books(id),
  FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

-- =============================================================================
-- サンプルデータ投入
-- =============================================================================

-- 著者
INSERT INTO authors (id, name, birth_year, country) VALUES
  (1, '夏目漱石', 1867, '日本'),
  (2, '村上春樹', 1949, '日本'),
  (3, '東野圭吾', 1958, '日本'),
  (4, '伊坂幸太郎', 1971, '日本'),
  (5, '又吉直樹', 1980, '日本'),
  (6, 'ヘミングウェイ', 1899, 'アメリカ'),
  (7, 'アガサ・クリスティー', 1890, 'イギリス'),
  (8, '松尾芭蕉', 1644, '日本'),
  (9, 'ピーター・ドラッカー', 1909, 'アメリカ'),
  (10, '稲盛和夫', 1932, '日本'),
  (11, 'エリヤフ・ゴールドラット', 1947, 'イスラエル'),
  (12, 'ジム・コリンズ', 1958, 'アメリカ'),
  (13, '安宅和人', 1968, '日本'),
  (14, 'リンダ・グラットン', 1955, 'イギリス'),
  (15, 'ダスティン・ボスウェル', NULL, 'アメリカ'),
  (16, 'ミック', NULL, '日本'),
  (17, 'マーチン・ファウラー', 1963, 'イギリス'),
  (18, 'アンドリュー・ハント', 1964, 'アメリカ'),
  (19, 'ロバート・C・マーティン', 1952, 'アメリカ'),
  (20, 'フレデリック・P・ブルックス Jr.', 1931, 'アメリカ'),
  (21, 'アラン・ドノバン', NULL, 'アメリカ'),
  (22, '石田保輝', 1988, '日本'),
  (23, 'J.K.ローリング', 1965, 'イギリス'),
  (24, 'ミヒャエル・エンデ', 1929, 'ドイツ'),
  (25, 'スティーブン・R・コヴィー', 1932, 'アメリカ'),
  (26, '岸見一郎', 1956, '日本'),
  (27, 'ユヴァル・ノア・ハラリ', 1976, 'イスラエル'),
  (28, 'ジャレド・ダイアモンド', 1937, 'アメリカ'),
  (29, '塩野七生', 1937, '日本'),
  (30, '尾田栄一郎', 1975, '日本'),
  (31, '諫山創', 1986, '日本'),
  (32, '鳥山明', 1955, '日本'),
  (33, 'D・カーネギー', 1888, 'アメリカ'),
  (34, 'ナポレオン・ヒル', 1883, 'アメリカ'),
  (35, 'マーティン・クレップマン', NULL, 'イギリス'),
  (36, 'タイタス・ウィンタース', NULL, 'アメリカ'),
  (37, 'リチャード・P・ファインマン', 1918, 'アメリカ'),
  (38, 'ベッツィ・ベイヤー', NULL, 'アメリカ');

-- カテゴリ
INSERT INTO categories (id, name) VALUES
  (1, '文学'),
  (2, 'ビジネス'),
  (3, '技術書'),
  (4, '児童書'),
  (5, '自己啓発'),
  (6, '歴史'),
  (7, '漫画');

-- 書籍
INSERT INTO books (id, isbn, title, author_id, category_id, price, published_at, stock) VALUES
  (1, '9784101010137', 'こころ', 1, 1, 473, '1952-03-04', 45),
  (2, '9784101010014', '吾輩は猫である', 1, 1, 693, '1961-09-07', 32),
  (3, '9784062748681', 'ノルウェイの森 上', 2, 1, 715, '2004-09-15', 58),
  (4, '9784062748698', 'ノルウェイの森 下', 2, 1, 715, '2004-09-15', 55),
  (5, '9784101001548', '海辺のカフカ 上', 2, 1, 1045, '2005-03-01', 40),
  (6, '9784101001555', '海辺のカフカ 下', 2, 1, 1100, '2005-03-01', 38),
  (7, '9784101001593', '1Q84 BOOK1 前編', 2, 1, 880, '2012-03-28', 22),
  (8, '9784167110123', '容疑者Xの献身', 3, 1, 858, '2008-08-10', 65),
  (9, '9784087474398', '白夜行', 3, 1, 1430, '2002-05-17', 48),
  (10, '9784167110130', 'ガリレオの苦悩', 3, 1, 825, '2011-10-07', 52),
  (11, '9784101250267', 'ゴールデンスランバー', 4, 1, 1155, '2010-11-29', 35),
  (12, '9784101250236', '重力ピエロ', 4, 1, 935, '2006-06-28', 28),
  (13, '9784167907822', '火花', 5, 1, 715, '2017-02-10', 41),
  (14, '9784102100189', '老人と海', 6, 1, 572, '2020-06-24', 20),
  (15, '9784102100141', '武器よさらば', 6, 1, 990, '2006-05-30', 15),
  (16, '9784151310089', 'オリエント急行の殺人', 7, 1, 1034, '2011-04-21', 30),
  (17, '9784151310805', 'そして誰もいなくなった', 7, 1, 1034, '2010-11-10', 42),
  (18, '9784003020623', 'おくのほそ道', 8, 1, 1001, '1979-01-16', 12),
  (19, '9784478410233', 'マネジメント[エッセンシャル版]', 9, 2, 2090, '2001-12-14', 25),
  (20, '9784478300596', 'プロフェッショナルの条件', 9, 2, 1980, '2000-06-29', 30),
  (21, '9784763195432', '生き方', 10, 2, 1870, '2004-08-01', 55),
  (22, '9784532312954', 'アメーバ経営', 10, 2, 2020, '2006-09-01', 18),
  (23, '9784478420409', 'ザ・ゴール', 11, 2, 1760, '2001-05-18', 38),
  (24, '9784822242633', 'ビジョナリー・カンパニー2', 12, 2, 2420, '2001-12-21', 22),
  (25, '9784862760852', 'イシューからはじめよ', 13, 2, 1980, '2010-11-24', 45),
  (26, '9784492533871', 'LIFE SHIFT', 14, 2, 1980, '2016-10-21', 38),
  (27, '9784873115658', 'リーダブルコード', 15, 3, 2640, '2012-06-23', 42),
  (28, '9784798144450', 'SQL 第2版 ゼロからはじめるデータベース操作', 16, 3, 2068, '2016-06-16', 35),
  (29, '9784274224546', 'リファクタリング(第2版)', 17, 3, 4840, '2019-12-01', 18),
  (30, '9784274226298', '達人プログラマー(第2版)', 18, 3, 3520, '2020-11-21', 25),
  (31, '9784048930598', 'Clean Code', 19, 3, 4104, '2017-12-18', 15),
  (32, '9784621066089', '人月の神話 新装版', 20, 3, 3520, '2014-04-22', 12),
  (33, '9784621300251', 'プログラミング言語Go', 21, 3, 4180, '2016-06-20', 16),
  (34, '9784798149776', 'アルゴリズム図鑑', 22, 3, 2618, '2017-06-05', 30),
  (35, '9784915512377', 'ハリー・ポッターと賢者の石', 23, 4, 2420, '1999-12-01', 50),
  (36, '9784915512391', 'ハリー・ポッターと秘密の部屋', 23, 4, 2090, '2000-09-19', 42),
  (37, '9784915512407', 'ハリー・ポッターとアズカバンの囚人', 23, 4, 2310, '2001-07-16', 38),
  (38, '9784001141276', 'モモ', 24, 4, 880, '2005-06-16', 28),
  (39, '9784863940246', '完訳 7つの習慣', 25, 5, 2376, '2013-08-30', 62),
  (40, '9784478025819', '嫌われる勇気', 26, 5, 1760, '2013-12-13', 58),
  (41, '9784478066119', '幸せになる勇気', 26, 5, 1760, '2016-02-26', 42),
  (42, '9784309226712', 'サピエンス全史 上', 27, 6, 2090, '2016-09-09', 38),
  (43, '9784309226729', 'サピエンス全史 下', 27, 6, 2090, '2016-09-09', 35),
  (44, '9784794218780', '銃・病原菌・鉄 上', 28, 6, 1210, '2012-02-10', 22),
  (45, '9784794218797', '銃・病原菌・鉄 下', 28, 6, 1210, '2012-02-10', 20),
  (46, '9784101181516', 'ローマ人の物語 1 ローマは一日にして成らず(上)', 29, 6, 605, '2002-06-01', 15),
  (47, '9784101181523', 'ローマ人の物語 2 ローマは一日にして成らず(下)', 29, 6, 649, '2002-06-28', 12),
  (48, '9784309227887', '21 Lessons', 27, 6, 2640, '2019-11-20', 28),
  (49, '9784088725093', 'ONE PIECE 1', 30, 7, 440, '1997-12-24', 120),
  (50, '9784088725444', 'ONE PIECE 2', 30, 7, 440, '1998-04-03', 100),
  (51, '9784063842760', '進撃の巨人 1', 31, 7, 550, '2010-03-17', 85),
  (52, '9784088518312', 'DRAGON BALL 1', 32, 7, 420, '1985-09-10', 70),
  (53, '9784422100982', '人を動かす 文庫版', 33, 5, 715, '2016-01-26', 45),
  (54, '9784877710514', '思考は現実化する', 34, 5, 2420, '1999-04-01', 32),
  (55, '9784873118703', 'データ指向アプリケーションデザイン', 35, 3, 5060, '2019-07-18', 3),
  (56, '9784873119656', 'Googleのソフトウェアエンジニアリング', 36, 3, 4840, '2021-11-29', 5),
  (57, '9784000077118', 'ファインマン物理学 I 力学', 37, 3, 3740, '1986-01-08', 4),
  (58, '9784873117911', 'SRE サイトリライアビリティエンジニアリング', 38, 3, 5280, '2017-08-12', 2);

-- 顧客
INSERT INTO customers (id, name, email, prefecture, created_at) VALUES
  (1, '田中 健', 'tanaka@example.com', '東京都', '2023-01-15 10:30:00'),
  (2, '鈴木 美咲', 'suzuki@example.com', '東京都', '2023-02-03 14:15:00'),
  (3, '佐藤 雅子', 'sato@example.com', '東京都', '2023-02-20 09:45:00'),
  (4, '高橋 翔', 'takahashi@example.com', '東京都', '2023-03-08 16:30:00'),
  (5, '伊藤 優奈', 'ito@example.com', '大阪府', '2023-03-22 11:20:00'),
  (6, '渡辺 直樹', 'watanabe@example.com', '神奈川県', '2023-04-10 13:05:00'),
  (7, '山本 玲奈', 'yamamoto@example.com', '愛知県', '2023-04-25 15:40:00'),
  (8, '中村 拓海', 'nakamura@example.com', '福岡県', '2023-05-12 10:15:00'),
  (9, '小林 彩花', 'kobayashi@example.com', '東京都', '2023-05-28 17:00:00'),
  (10, '加藤 誠', 'kato@example.com', '北海道', '2023-06-14 12:30:00'),
  (11, '吉田 真理', 'yoshida@example.com', '神奈川県', '2023-06-30 14:45:00'),
  (12, '山田 悠斗', 'yamada@example.com', '大阪府', '2023-07-08 16:00:00'),
  (13, '佐々木 恵', 'sasaki@example.com', '京都府', '2023-07-25 10:50:00'),
  (14, '松本 陸', 'matsumoto@example.com', '東京都', '2023-08-11 13:35:00'),
  (15, '井上 千尋', 'inoue@example.com', '兵庫県', '2023-08-27 11:10:00'),
  (16, '木村 健一', 'kimura@example.com', '東京都', '2023-09-14 15:25:00'),
  (17, '林 美穂', 'hayashi@example.com', '神奈川県', '2023-09-30 09:40:00'),
  (18, '清水 太郎', 'shimizu@example.com', '千葉県', '2023-10-18 14:55:00'),
  (19, '山崎 明日香', 'yamazaki@example.com', '埼玉県', '2023-11-05 10:20:00'),
  (20, '森 健太郎', 'mori@example.com', '東京都', '2023-11-22 16:45:00'),
  (21, '池田 理沙', 'ikeda@example.com', '大阪府', '2023-12-09 12:15:00'),
  (22, '橋本 涼', 'hashimoto@example.com', '東京都', '2023-12-26 15:30:00'),
  (23, '石川 由美', 'ishikawa@example.com', '愛知県', '2024-01-12 11:00:00'),
  (24, '前田 大輔', 'maeda@example.com', '神奈川県', '2024-01-28 13:45:00'),
  (25, '藤田 花音', 'fujita@example.com', '福岡県', '2024-02-15 09:55:00'),
  (26, '岡田 雄太', 'okada@example.com', '東京都', '2024-03-03 16:20:00'),
  (27, '長谷川 葵', 'hasegawa@example.com', '京都府', '2024-03-20 14:10:00'),
  (28, '近藤 圭', 'kondo@example.com', '大阪府', '2024-04-07 10:35:00'),
  (29, '石井 さくら', 'ishii@example.com', '東京都', '2024-04-23 12:50:00'),
  (30, '坂本 健司', 'sakamoto@example.com', '神奈川県', '2024-05-11 15:05:00'),
  (31, '遠藤 美香', 'endo@example.com', '宮城県', '2024-05-28 11:25:00'),
  (32, '青木 蓮', 'aoki@example.com', '東京都', '2024-06-14 13:40:00'),
  (33, '藤井 智子', 'fujii@example.com', '広島県', '2024-07-02 16:55:00'),
  (34, '西村 優作', 'nishimura@example.com', '兵庫県', '2024-07-20 10:10:00'),
  (35, '福田 結衣', 'fukuda@example.com', '東京都', '2024-08-06 14:25:00'),
  (36, '太田 一樹', 'ota@example.com', '神奈川県', '2024-08-23 12:40:00'),
  (37, '三浦 裕子', 'miura@example.com', '大阪府', '2024-09-09 16:15:00'),
  (38, '村上 剛志', 'murakami@example.com', '東京都', '2024-09-10 15:35:00'),
  (39, '岩田 まお', 'iwata@example.com', '愛知県', '2024-09-25 10:45:00'),
  (40, '野村 雄介', 'nomura@example.com', '東京都', '2024-10-10 13:00:00');

-- 販売記録
INSERT INTO sales (id, book_id, customer_id, quantity, total_price, sold_at) VALUES
  (1, 8, 4, 2, 1716, '2024-01-02 18:45:00'),
  (2, 3, 15, 2, 1430, '2024-01-08 09:30:00'),
  (3, 50, 34, 2, 880, '2024-01-08 15:00:00'),
  (4, 53, 33, 2, 1430, '2024-01-08 20:45:00'),
  (5, 39, 2, 1, 2376, '2024-01-10 14:30:00'),
  (6, 37, 15, 3, 6930, '2024-01-11 20:45:00'),
  (7, 52, 30, 1, 420, '2024-01-12 13:05:00'),
  (8, 21, 37, 1, 1870, '2024-01-12 13:35:00'),
  (9, 43, 35, 2, 4180, '2024-01-13 13:15:00'),
  (10, 31, 36, 1, 4104, '2024-01-14 16:45:00'),
  (11, 9, 30, 1, 1430, '2024-01-15 18:05:00'),
  (12, 46, 30, 2, 1210, '2024-01-16 15:35:00'),
  (13, 50, 19, 1, 440, '2024-01-16 20:35:00'),
  (14, 23, 2, 1, 1760, '2024-01-20 11:15:00'),
  (15, 3, 13, 2, 1430, '2024-01-22 12:20:00'),
  (16, 43, 40, 1, 2090, '2024-01-23 14:15:00'),
  (17, 36, 29, 2, 4180, '2024-02-01 19:15:00'),
  (18, 11, 28, 2, 2310, '2024-02-03 20:10:00'),
  (19, 4, 6, 3, 2145, '2024-02-05 19:10:00'),
  (20, 26, 21, 3, 5940, '2024-02-09 16:15:00'),
  (21, 35, 36, 1, 2420, '2024-02-11 13:30:00'),
  (22, 3, 6, 3, 2145, '2024-02-13 13:45:00'),
  (23, 7, 18, 1, 880, '2024-02-15 17:05:00'),
  (24, 2, 35, 1, 693, '2024-02-18 13:30:00'),
  (25, 24, 26, 2, 4840, '2024-02-19 20:15:00'),
  (26, 44, 35, 1, 1210, '2024-02-22 09:10:00'),
  (27, 43, 8, 3, 6270, '2024-02-22 12:00:00'),
  (28, 5, 6, 2, 2090, '2024-02-25 17:10:00'),
  (29, 37, 33, 2, 4620, '2024-02-28 16:05:00'),
  (30, 18, 15, 3, 3003, '2024-03-01 09:15:00'),
  (31, 23, 9, 3, 5280, '2024-03-02 13:35:00'),
  (32, 9, 20, 2, 2860, '2024-03-09 10:05:00'),
  (33, 46, 39, 1, 605, '2024-03-09 18:50:00'),
  (34, 49, 23, 1, 440, '2024-03-19 19:35:00'),
  (35, 23, 17, 3, 5280, '2024-03-20 18:05:00'),
  (36, 9, 16, 1, 1430, '2024-03-22 19:20:00'),
  (37, 2, 18, 3, 2079, '2024-03-23 15:30:00'),
  (38, 5, 24, 3, 3135, '2024-04-02 12:05:00'),
  (39, 4, 38, 1, 715, '2024-04-05 17:45:00'),
  (40, 43, 4, 3, 6270, '2024-04-05 18:15:00'),
  (41, 19, 25, 1, 2090, '2024-04-05 19:00:00'),
  (42, 19, 7, 3, 6270, '2024-04-06 20:50:00'),
  (43, 1, 3, 1, 473, '2024-04-08 17:00:00'),
  (44, 35, 29, 3, 7260, '2024-04-09 17:10:00'),
  (45, 28, 39, 1, 2068, '2024-04-10 12:30:00'),
  (46, 51, 20, 2, 1100, '2024-04-11 11:05:00'),
  (47, 26, 21, 1, 1980, '2024-04-12 13:30:00'),
  (48, 40, 17, 1, 1760, '2024-04-13 16:05:00'),
  (49, 47, 14, 1, 649, '2024-04-14 14:45:00'),
  (50, 39, 32, 2, 4752, '2024-04-15 19:20:00'),
  (51, 35, 30, 1, 2420, '2024-04-17 14:45:00'),
  (52, 3, 26, 1, 715, '2024-04-19 20:30:00'),
  (53, 37, 25, 1, 2310, '2024-04-23 12:35:00'),
  (54, 50, 1, 1, 440, '2024-04-25 11:20:00'),
  (55, 14, 25, 2, 1144, '2024-05-02 20:35:00'),
  (56, 9, 5, 1, 1430, '2024-05-06 16:50:00'),
  (57, 8, 32, 1, 858, '2024-05-07 09:50:00'),
  (58, 10, 36, 1, 825, '2024-05-20 12:30:00'),
  (59, 33, 5, 1, 4180, '2024-05-20 20:20:00'),
  (60, 3, 11, 1, 715, '2024-05-21 20:50:00'),
  (61, 40, 39, 2, 3520, '2024-05-25 12:30:00'),
  (62, 32, 5, 2, 7040, '2024-05-26 19:15:00'),
  (63, 41, 18, 3, 5280, '2024-05-27 16:20:00'),
  (64, 12, 13, 1, 935, '2024-05-28 10:20:00'),
  (65, 2, 18, 1, 693, '2024-06-04 10:35:00'),
  (66, 48, 19, 2, 5280, '2024-06-04 18:50:00'),
  (67, 43, 29, 1, 2090, '2024-06-04 19:30:00'),
  (68, 3, 24, 1, 715, '2024-06-07 19:20:00'),
  (69, 11, 15, 1, 1155, '2024-06-10 12:15:00'),
  (70, 23, 21, 1, 1760, '2024-06-11 15:20:00'),
  (71, 37, 29, 2, 4620, '2024-06-11 17:50:00'),
  (72, 35, 31, 1, 2420, '2024-06-11 19:05:00'),
  (73, 52, 18, 2, 840, '2024-06-17 10:20:00'),
  (74, 21, 16, 1, 1870, '2024-06-18 10:10:00'),
  (75, 21, 5, 1, 1870, '2024-06-18 16:35:00'),
  (76, 51, 2, 1, 550, '2024-06-19 15:35:00'),
  (77, 38, 5, 1, 880, '2024-06-20 20:35:00'),
  (78, 5, 39, 1, 1045, '2024-06-25 11:50:00'),
  (79, 11, 9, 1, 1155, '2024-06-26 09:30:00'),
  (80, 51, 27, 1, 550, '2024-07-01 14:10:00'),
  (81, 8, 32, 1, 858, '2024-07-02 11:35:00'),
  (82, 50, 33, 1, 440, '2024-07-04 13:45:00'),
  (83, 25, 30, 2, 3960, '2024-07-06 20:50:00'),
  (84, 6, 16, 3, 3300, '2024-07-07 10:05:00'),
  (85, 4, 28, 2, 1430, '2024-07-13 18:45:00'),
  (86, 6, 28, 1, 1100, '2024-07-15 20:00:00'),
  (87, 16, 40, 2, 2068, '2024-07-18 09:20:00'),
  (88, 3, 14, 2, 1430, '2024-07-21 16:10:00'),
  (89, 16, 36, 1, 1034, '2024-07-22 15:10:00'),
  (90, 21, 4, 1, 1870, '2024-07-25 18:00:00'),
  (91, 22, 15, 1, 2020, '2024-08-01 15:30:00'),
  (92, 23, 27, 1, 1760, '2024-08-01 20:50:00'),
  (93, 41, 22, 3, 5280, '2024-08-03 11:15:00'),
  (94, 34, 33, 2, 5236, '2024-08-03 18:00:00'),
  (95, 42, 3, 1, 2090, '2024-08-04 10:15:00'),
  (96, 29, 12, 1, 4840, '2024-08-04 10:35:00'),
  (97, 53, 33, 1, 715, '2024-08-04 14:05:00'),
  (98, 25, 22, 1, 1980, '2024-08-04 15:30:00'),
  (99, 36, 15, 3, 6270, '2024-08-04 19:10:00'),
  (100, 22, 26, 2, 4040, '2024-08-05 18:50:00'),
  (101, 20, 11, 2, 3960, '2024-08-09 12:20:00'),
  (102, 8, 1, 1, 858, '2024-08-10 15:50:00'),
  (103, 30, 34, 1, 3520, '2024-08-10 18:35:00'),
  (104, 49, 14, 1, 440, '2024-08-11 11:30:00'),
  (105, 39, 35, 1, 2376, '2024-08-12 18:20:00'),
  (106, 41, 17, 2, 3520, '2024-08-12 19:20:00'),
  (107, 42, 35, 1, 2090, '2024-08-12 19:50:00'),
  (108, 18, 31, 2, 2002, '2024-08-14 19:15:00'),
  (109, 52, 10, 1, 420, '2024-08-15 19:20:00'),
  (110, 29, 1, 2, 9680, '2024-08-16 16:30:00'),
  (111, 35, 3, 3, 7260, '2024-08-17 19:45:00'),
  (112, 36, 31, 1, 2090, '2024-08-18 11:35:00'),
  (113, 5, 9, 2, 2090, '2024-09-06 13:50:00'),
  (114, 9, 5, 2, 2860, '2024-09-09 11:30:00'),
  (115, 41, 12, 1, 1760, '2024-09-12 17:50:00'),
  (116, 39, 28, 1, 2376, '2024-09-14 18:10:00'),
  (117, 51, 33, 2, 1100, '2024-09-17 12:30:00'),
  (118, 45, 31, 1, 1210, '2024-09-19 10:45:00'),
  (119, 54, 5, 1, 2420, '2024-09-22 15:20:00'),
  (120, 50, 12, 1, 440, '2024-09-23 12:50:00'),
  (121, 36, 32, 2, 4180, '2024-09-23 13:35:00'),
  (122, 30, 6, 1, 3520, '2024-09-25 15:15:00'),
  (123, 35, 8, 3, 7260, '2024-09-27 09:50:00'),
  (124, 43, 22, 3, 6270, '2024-10-01 13:10:00'),
  (125, 49, 24, 1, 440, '2024-10-08 15:50:00'),
  (126, 13, 8, 1, 715, '2024-10-09 09:05:00'),
  (127, 34, 28, 2, 5236, '2024-10-09 09:05:00'),
  (128, 2, 7, 1, 693, '2024-10-09 09:45:00'),
  (129, 40, 15, 2, 3520, '2024-10-09 19:10:00'),
  (130, 44, 1, 1, 1210, '2024-10-11 09:10:00'),
  (131, 11, 35, 1, 1155, '2024-10-18 11:35:00'),
  (132, 6, 5, 1, 1100, '2024-10-18 12:15:00'),
  (133, 8, 8, 2, 1716, '2024-10-20 09:05:00'),
  (134, 49, 20, 2, 880, '2024-10-20 10:00:00'),
  (135, 31, 37, 2, 8208, '2024-10-24 10:15:00'),
  (136, 39, 11, 1, 2376, '2024-10-28 15:15:00'),
  (137, 23, 4, 1, 1760, '2024-11-03 19:00:00'),
  (138, 11, 14, 2, 2310, '2024-11-04 14:50:00'),
  (139, 33, 8, 2, 8360, '2024-11-05 13:10:00'),
  (140, 10, 1, 1, 825, '2024-11-09 11:45:00'),
  (141, 5, 12, 2, 2090, '2024-11-10 19:50:00'),
  (142, 4, 35, 3, 2145, '2024-11-11 10:20:00'),
  (143, 25, 23, 2, 3960, '2024-11-12 10:50:00'),
  (144, 42, 30, 2, 4180, '2024-11-14 10:10:00'),
  (145, 19, 9, 3, 6270, '2024-11-17 17:30:00'),
  (146, 54, 25, 1, 2420, '2024-11-20 09:30:00'),
  (147, 16, 22, 2, 2068, '2024-11-28 10:20:00'),
  (148, 38, 6, 3, 2640, '2024-11-28 17:35:00'),
  (149, 47, 39, 1, 649, '2024-12-04 19:50:00'),
  (150, 21, 10, 1, 1870, '2024-12-05 20:15:00'),
  (151, 27, 19, 1, 2640, '2024-12-08 13:30:00'),
  (152, 33, 29, 1, 4180, '2024-12-10 20:35:00'),
  (153, 32, 3, 1, 3520, '2024-12-10 20:45:00'),
  (154, 11, 27, 1, 1155, '2024-12-11 15:15:00'),
  (155, 26, 13, 1, 1980, '2024-12-14 16:50:00'),
  (156, 35, 11, 1, 2420, '2024-12-16 13:35:00'),
  (157, 28, 12, 1, 2068, '2024-12-16 13:50:00'),
  (158, 28, 12, 1, 2068, '2024-12-18 11:20:00'),
  (159, 6, 14, 2, 2200, '2024-12-19 18:45:00'),
  (160, 5, 11, 1, 1045, '2024-12-24 13:50:00'),
  (161, 48, 6, 1, 2640, '2024-12-24 14:10:00'),
  (162, 49, 2, 1, 440, '2024-12-25 13:30:00'),
  (163, 13, 39, 1, 715, '2024-12-26 14:35:00'),
  (164, 7, 4, 2, 1760, '2025-01-03 12:10:00'),
  (165, 49, 5, 1, 440, '2025-01-03 20:50:00'),
  (166, 30, 5, 2, 7040, '2025-01-05 11:20:00'),
  (167, 6, 1, 1, 1100, '2025-01-08 10:00:00'),
  (168, 40, 7, 2, 3520, '2025-01-10 16:00:00'),
  (169, 24, 20, 1, 2420, '2025-01-10 18:35:00'),
  (170, 36, 11, 1, 2090, '2025-01-18 15:05:00'),
  (171, 49, 10, 1, 440, '2025-01-22 10:20:00'),
  (172, 4, 34, 1, 715, '2025-01-22 20:05:00'),
  (173, 45, 33, 1, 1210, '2025-01-24 19:50:00'),
  (174, 39, 30, 1, 2376, '2025-01-26 16:20:00'),
  (175, 31, 14, 1, 4104, '2025-02-06 12:10:00'),
  (176, 47, 26, 1, 649, '2025-02-11 15:30:00'),
  (177, 11, 18, 1, 1155, '2025-02-13 09:45:00'),
  (178, 39, 28, 1, 2376, '2025-02-15 11:20:00'),
  (179, 23, 9, 1, 1760, '2025-02-19 12:45:00'),
  (180, 8, 12, 1, 858, '2025-02-22 12:35:00'),
  (181, 8, 33, 3, 2574, '2025-03-02 17:05:00'),
  (182, 40, 38, 1, 1760, '2025-03-03 13:30:00'),
  (183, 51, 23, 2, 1100, '2025-03-08 10:10:00'),
  (184, 11, 27, 2, 2310, '2025-03-08 11:10:00'),
  (185, 54, 34, 2, 4840, '2025-03-12 14:20:00'),
  (186, 42, 27, 2, 4180, '2025-03-14 19:05:00'),
  (187, 38, 30, 1, 880, '2025-03-14 19:10:00'),
  (188, 37, 25, 1, 2310, '2025-03-16 09:10:00'),
  (189, 3, 5, 2, 1430, '2025-03-18 20:15:00'),
  (190, 52, 13, 1, 420, '2025-03-25 19:05:00'),
  (191, 36, 13, 2, 4180, '2025-03-28 10:20:00'),
  (192, 20, 34, 1, 1980, '2025-04-09 16:05:00'),
  (193, 6, 22, 1, 1100, '2025-04-09 19:45:00'),
  (194, 27, 15, 1, 2640, '2025-04-11 10:50:00'),
  (195, 53, 3, 1, 715, '2025-04-11 18:45:00'),
  (196, 15, 34, 3, 2970, '2025-04-12 15:05:00'),
  (197, 17, 29, 1, 1034, '2025-04-17 16:10:00'),
  (198, 43, 5, 3, 6270, '2025-04-19 12:15:00'),
  (199, 6, 28, 1, 1100, '2025-04-23 13:35:00'),
  (200, 19, 30, 1, 2090, '2025-04-28 10:45:00'),
  (201, 1, 8, 1, 473, '2025-05-08 12:10:00'),
  (202, 39, 5, 1, 2376, '2025-05-08 20:05:00'),
  (203, 10, 14, 2, 1650, '2025-05-17 16:20:00'),
  (204, 26, 18, 2, 3960, '2025-05-18 09:50:00'),
  (205, 28, 3, 1, 2068, '2025-05-23 11:45:00'),
  (206, 37, 22, 3, 6930, '2025-05-27 09:20:00'),
  (207, 41, 26, 3, 5280, '2025-06-03 15:00:00'),
  (208, 50, 36, 3, 1320, '2025-06-03 15:00:00'),
  (209, 51, 40, 1, 550, '2025-06-03 15:05:00'),
  (210, 41, 38, 1, 1760, '2025-06-04 19:15:00'),
  (211, 9, 38, 2, 2860, '2025-06-09 12:30:00'),
  (212, 27, 31, 2, 5280, '2025-06-14 20:50:00'),
  (213, 5, 34, 1, 1045, '2025-06-16 09:05:00'),
  (214, 40, 16, 1, 1760, '2025-06-19 13:20:00'),
  (215, 7, 4, 1, 880, '2025-06-26 10:15:00'),
  (216, 40, 2, 3, 5280, '2025-07-09 09:00:00'),
  (217, 26, 30, 2, 3960, '2025-07-11 09:45:00'),
  (218, 16, 33, 1, 1034, '2025-07-11 15:20:00'),
  (219, 53, 40, 2, 1430, '2025-07-15 18:50:00'),
  (220, 45, 4, 1, 1210, '2025-07-17 10:05:00'),
  (221, 51, 16, 1, 550, '2025-07-17 10:35:00'),
  (222, 35, 18, 1, 2420, '2025-07-21 16:05:00'),
  (223, 12, 23, 2, 1870, '2025-07-22 17:30:00'),
  (224, 10, 4, 1, 825, '2025-07-27 13:00:00'),
  (225, 48, 27, 1, 2640, '2025-07-28 20:00:00'),
  (226, 48, 24, 1, 2640, '2025-08-03 10:05:00'),
  (227, 46, 7, 1, 605, '2025-08-03 10:30:00'),
  (228, 6, 24, 1, 1100, '2025-08-04 12:15:00'),
  (229, 7, 13, 1, 880, '2025-08-05 15:10:00'),
  (230, 48, 14, 1, 2640, '2025-08-08 10:30:00'),
  (231, 27, 22, 1, 2640, '2025-08-09 13:20:00'),
  (232, 50, 28, 1, 440, '2025-08-14 13:15:00'),
  (233, 27, 31, 2, 5280, '2025-08-15 09:05:00'),
  (234, 26, 40, 3, 5940, '2025-08-15 15:20:00'),
  (235, 52, 12, 3, 1260, '2025-08-15 18:45:00'),
  (236, 38, 21, 2, 1760, '2025-08-28 17:00:00'),
  (237, 49, 40, 2, 880, '2025-09-01 18:20:00'),
  (238, 9, 20, 2, 2860, '2025-09-01 19:50:00'),
  (239, 1, 7, 2, 946, '2025-09-03 18:35:00'),
  (240, 47, 21, 1, 649, '2025-09-10 16:50:00'),
  (241, 41, 31, 1, 1760, '2025-09-11 18:15:00'),
  (242, 1, 36, 1, 473, '2025-09-14 12:45:00'),
  (243, 20, 40, 3, 5940, '2025-09-14 17:45:00'),
  (244, 4, 9, 1, 715, '2025-09-18 13:35:00'),
  (245, 48, 36, 1, 2640, '2025-09-18 14:05:00'),
  (246, 21, 27, 2, 3740, '2025-09-26 18:15:00'),
  (247, 34, 15, 2, 5236, '2025-10-01 19:20:00'),
  (248, 17, 6, 1, 1034, '2025-10-11 10:15:00'),
  (249, 52, 37, 2, 840, '2025-10-11 19:30:00'),
  (250, 17, 14, 1, 1034, '2025-10-21 14:45:00'),
  (251, 22, 2, 1, 2020, '2025-10-22 09:05:00'),
  (252, 37, 20, 3, 6930, '2025-10-22 16:10:00'),
  (253, 25, 20, 3, 5940, '2025-10-26 10:00:00');

-- タグ
INSERT INTO tags (id, name) VALUES
  (1, 'ベストセラー'),
  (2, '話題作'),
  (3, '名著'),
  (4, '初心者向け'),
  (5, '実用的');

-- 書籍とタグの関連（一部の本にのみ付与）
INSERT INTO book_tags (book_id, tag_id) VALUES
  (3, 1), (3, 2),     -- ノルウェイの森 上
  (4, 1), (4, 2),     -- ノルウェイの森 下
  (8, 1), (8, 3),     -- 容疑者Xの献身
  (35, 1), (35, 2),   -- ハリー・ポッターと賢者の石
  (39, 1), (39, 5),   -- 7つの習慣
  (40, 1), (40, 5),   -- 嫌われる勇気
  (42, 1), (42, 3),   -- サピエンス全史 上
  (43, 1), (43, 3),   -- サピエンス全史 下
  (27, 5), (27, 4),   -- リーダブルコード
  (28, 4), (28, 5);   -- SQL 第2版