-- =============================================================================
-- kakeibo サンプルデータベース
--
-- 家計簿を題材にした学習用データベース。
-- 絞り込み・式と関数・集計とグループ化・結合・サブクエリ・テーブル設計まで、
-- SQL の基本〜中級の演習に使用する。
--
-- テーブル:
--   [基本]
--   家計簿             日々の収支記録
--   家計簿アーカイブ   過去分の家計簿 (UNION・結合の演習で使用)
--   費目               費目マスタ (結合・サブクエリの演習で使用)
--
--   [テーブル設計 (正規化) 用]
--   accounts           口座
--   expenses           費目マスタ
--   transactions       取引
--   transaction_items  取引明細 (transactions × expenses の関連)
--   tags               タグ
--   taggings           取引とタグの関連 (transactions × tags)
-- =============================================================================


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

-- =============================================================================
-- 基本テーブル
-- =============================================================================

-- 家計簿
CREATE TABLE 家計簿 (
  日付   DATE,
  費目   VARCHAR(20),
  メモ   VARCHAR(100),
  入金額 INT,
  出金額 INT
);

-- 家計簿アーカイブ (家計簿と同じスキーマ、過去分)
CREATE TABLE 家計簿アーカイブ (
  日付   DATE,
  費目   VARCHAR(20),
  メモ   VARCHAR(100),
  入金額 INT,
  出金額 INT
);

-- 費目 (マスタ)
CREATE TABLE 費目 (
  ID   INT          NOT NULL PRIMARY KEY,
  名前 VARCHAR(40)  NOT NULL
);

-- =============================================================================
-- テーブル設計 (正規化) 用テーブル
-- =============================================================================

CREATE TABLE accounts (
  account_id   INT          NOT NULL PRIMARY KEY,
  account_name VARCHAR(30)  NOT NULL
);

CREATE TABLE expenses (
  expense_id   CHAR(3)      NOT NULL PRIMARY KEY,
  expense_name VARCHAR(30)  NOT NULL UNIQUE,
  category     CHAR(1)      NOT NULL CHECK (category IN ('I', 'O'))
);

CREATE TABLE transactions (
  transaction_id   CHAR(5)      NOT NULL PRIMARY KEY,
  transaction_date DATE         NOT NULL,
  account_id       INT          NOT NULL,
  note             VARCHAR(100),
  FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

CREATE TABLE transaction_items (
  transaction_id   CHAR(5)      NOT NULL,
  expense_id       CHAR(3)      NOT NULL,
  amount           INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (transaction_id, expense_id),
  FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id),
  FOREIGN KEY (expense_id)     REFERENCES expenses(expense_id)
);

CREATE TABLE tags (
  tag_id    INT          NOT NULL PRIMARY KEY,
  note      VARCHAR(100),
  author_id INT          NOT NULL,
  FOREIGN KEY (author_id) REFERENCES accounts(account_id)
);

CREATE TABLE taggings (
  tag_id         INT          NOT NULL,
  transaction_id CHAR(5)      NOT NULL,
  PRIMARY KEY (tag_id, transaction_id),
  FOREIGN KEY (tag_id)         REFERENCES tags(tag_id),
  FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id)
);

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

-- 家計簿
INSERT INTO 家計簿 (日付, 費目, メモ, 入金額, 出金額) VALUES
  ('2024-02-03', '食費',       'コーヒーを購入',      0,    380),
  ('2024-02-10', '給料',       '1月の給料',      280000,      0),
  ('2024-02-11', '教養娯楽費', '書籍を購入',          0,   2800),
  ('2024-02-14', '交際費',     '同期会の会費',        0,   5000),
  ('2024-02-18', '水道光熱費', '1月の電気代',         0,   7560);

-- 家計簿アーカイブ
INSERT INTO 家計簿アーカイブ (日付, 費目, メモ, 入金額, 出金額) VALUES
  ('2023-12-10', '給料',       '11月の給料',       280000,     0),
  ('2023-12-18', '水道光熱費', '水道代',                0,  4200),
  ('2023-12-24', '食費',       'レストランみやび',      0,  5000),
  ('2023-12-25', '居住費',     '1月の家賃支払い',       0, 80000),
  ('2024-01-10', '給料',       '12月の給料',       280000,     0),
  ('2024-01-13', '教養娯楽費', 'スッキリシネマズ',      0,  1800),
  ('2024-01-13', '食費',       '新年会',                0,  5000),
  ('2024-01-25', '居住費',     '2月の家賃支払い',       0, 80000);

-- 費目
INSERT INTO 費目 (ID, 名前) VALUES
  (1, '給料'),
  (2, '食費'),
  (3, '水道光熱費'),
  (4, '居住費'),
  (5, '教養娯楽費'),
  (6, '交際費');

-- accounts
INSERT INTO accounts (account_id, account_name) VALUES
  (1, '立花いずみ'),
  (2, '立花コウジ');

-- expenses
INSERT INTO expenses (expense_id, expense_name, category) VALUES
  ('H01', '住居費',     'O'),
  ('H03', '飲食費',     'O'),
  ('H17', '振込手数料', 'O'),
  ('H19', '図書費',     'O');

-- transactions
INSERT INTO transactions (transaction_id, transaction_date, account_id, note) VALUES
  ('41001', '2024-04-10', 1, '家賃を振り込んだ'),
  ('41201', '2024-04-12', 2, '『スッキリわかるJava入門』'),
  ('41202', '2024-04-12', 2, '2次会で後輩におごった');

-- transaction_items
INSERT INTO transaction_items (transaction_id, expense_id, amount) VALUES
  ('41001', 'H01', 65000),
  ('41001', 'H17',   525),
  ('41201', 'H19',  2730),
  ('41202', 'H03', 11000);
