-- DECIMALで正確な金額を保存
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance DECIMAL(12, 2)  -- 最大12桁、小数2桁
);

-- FLOATで近似的に保存
CREATE TABLE measurements (
    id INTEGER PRIMARY KEY,
    temperature REAL  -- 近似値、小さな誤差がある可能性
);

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title VARCHAR(200),    -- タイトル、最大200文字
    status CHAR(1),         -- ステータス：D=下書き、P=公開済み
    content TEXT             -- 本文、無制限
);

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_name TEXT,
    event_date DATE,              -- 日付のみ
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 作成時刻を自動記録
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    is_active BOOLEAN DEFAULT 1  -- 1=有効、0=無効
);

-- ブール値を挿入
INSERT INTO users (name, is_active) VALUES ('John', TRUE);   -- 1として保存
INSERT INTO users (name, is_active) VALUES ('Jane', FALSE);  -- 0として保存

-- ブール値でクエリ
SELECT * FROM users WHERE is_active = 1;

-- NULLを含むすべての演算の結果はNULL
SELECT NULL + 1;        -- 結果：NULL
SELECT NULL || 'hello'; -- 結果：NULL

-- NULLを確認するには、IS / IS NOTを使用
SELECT * FROM employees WHERE department_id IS NULL;     -- ✅ 正しい
SELECT * FROM employees WHERE department_id = NULL;      -- ❌ 常に空を返す
SELECT * FROM employees WHERE department_id IS NOT NULL; -- ✅ 正しい

-- 指定された列型で商品テーブルを作成
CREATE TABLE products_demo (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    created_at DATE
);

-- さまざまな型のデータを挿入
INSERT INTO products_demo (name, price, stock, description, is_active, created_at)
VALUES ('メカニカルキーボード', 299.50, 100, 'ブルースイッチ87キー、タイピングに最適', TRUE, '2024-01-15');

INSERT INTO products_demo (name, price, stock, description, is_active, created_at)
VALUES ('ワイヤレスマウス', 89.00, 200, NULL, TRUE, '2024-02-20');

-- データと型の動作を確認
SELECT name, price, stock, description, is_active, created_at
FROM products_demo;

name              price    stock  description                                   is_active  created_at
----------------  -------  -----  ---------------------------------------------  ---------  ----------
メカニカルキーボード 299.50 100    ブルースイッチ87キー、タイピングに...       1          2024-01-15
ワイヤレスマウス    89.00    200    (NULL)                                         1          2024-02-20

-- テストテーブルを作成
CREATE TABLE null_test (
    id INTEGER PRIMARY KEY,
    value INTEGER
);

INSERT INTO null_test VALUES (1, 10);
INSERT INTO null_test VALUES (2, NULL);
INSERT INTO null_test VALUES (3, 20);

-- 算術演算でのNULL
SELECT id, value, value + 100 AS add_result
FROM null_test;

id  value  add_result
--  -----  ----------
1   10     110
2   (NULL) (NULL)
3   20     120

-- NULLの確認：IS vs =
SELECT id, value,
    CASE WHEN value IS NULL THEN 'NULLです' ELSE 'NULLではありません' END AS check_result
FROM null_test;

id  value  check_result
--  -----  ------------
1   10     NULLではありません
2   (NULL) NULLです
3   20     NULLではありません

-- NULLと集計関数：COUNTはNULLを除外、SUM/AVGはNULLを無視
SELECT COUNT(value) AS count_val,      -- 2（NULLをカウントしない）
       COUNT(*) AS count_all,          -- 3（すべての行をカウント）
       SUM(value) AS sum_val,          -- 30（NULLを無視）
       AVG(value) AS avg_val           -- 15（30/2、30/3ではない）
FROM null_test;

count_val  count_all  sum_val  avg_val
---------  ---------  -------  -------
2          3          30       15.0

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,     -- ユーザー名、一意でNULL不可
    email VARCHAR(100) NOT NULL UNIQUE,       -- メールアドレス
    password_hash CHAR(64) NOT NULL,          -- SHA256ハッシュ、固定64文字
    age INTEGER CHECK (age >= 0 AND age <= 150),  -- 年齢、妥当な範囲チェック
    balance DECIMAL(12, 2) DEFAULT 0.00,      -- 口座残高、セントまで正確
    bio TEXT,                                  -- 自己紹介、無制限
    is_verified BOOLEAN DEFAULT 0,            // メール認証済みかどうか
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 従業員情報をクエリし、部署がNULLの場合は「未割り当て」と表示
SELECT e.name,
       COALESCE(d.name, '未割り当て') AS department,
       COALESCE(e.salary, 0) AS salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;