-- 验证存储类
SELECT typeof(42);          -- integer
SELECT typeof(3.14);        -- real
SELECT typeof('hello');     -- text
SELECT typeof(NULL);        -- null
SELECT typeof(X'DEADBEEF'); -- blob

-- 同一列可以存储不同类型的值
CREATE TABLE demo (value);
INSERT INTO demo VALUES (42);
INSERT INTO demo VALUES ('hello');
INSERT INTO demo VALUES (3.14);
INSERT INTO demo VALUES (NULL);
INSERT INTO demo VALUES (X'CAFE');

SELECT value, typeof(value) FROM demo;

-- 验证整数存储
CREATE TABLE int_test (val INTEGER);
INSERT INTO int_test VALUES (0), (127), (128), (32768), (2147483648);

SELECT val, typeof(val), length(CAST(val AS TEXT)) AS text_len
FROM int_test;

CREATE TABLE affinity_demo (
    a  TEXT,          -- TEXT 亲和性
    b  NUMERIC,       -- NUMERIC 亲和性
    c  INTEGER,       -- INTEGER 亲和性
    d  REAL,          -- REAL 亲和性
    e  BLOB,          -- BLOB 亲和性
    f                 -- BLOB 亲和性（未指定）
);

-- 测试 1：插入整数值
INSERT INTO affinity_demo (a,b,c,d,e,f) VALUES (42, 42, 42, 42, 42, 42);
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e), typeof(f)
FROM affinity_demo WHERE rowid = 1;
-- text|integer|integer|real|integer|integer

-- 测试 2：插入实数值
DELETE FROM affinity_demo;
INSERT INTO affinity_demo (a,b,c,d,e,f) VALUES (3.14, 3.14, 3.14, 3.14, 3.14, 3.14);
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e), typeof(f)
FROM affinity_demo WHERE rowid = 1;
-- text|real|real|real|real|real

-- 测试 3：插入文本
DELETE FROM affinity_demo;
INSERT INTO affinity_demo (a,b,c,d,e,f) VALUES ('hello', 'hello', 'hello', 'hello', 'hello', 'hello');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e), typeof(f)
FROM affinity_demo WHERE rowid = 1;
-- text|text|text|text|text|text

CREATE TABLE numeric_demo (val NUMERIC);
INSERT INTO numeric_demo VALUES ('3.14');      -- 存储为 REAL
INSERT INTO numeric_demo VALUES ('3');          -- 存储为 INTEGER
INSERT INTO numeric_demo VALUES ('hello');      -- 存储为 TEXT
INSERT INTO numeric_demo VALUES (3.0);          -- 存储为 INTEGER（值为 3）

SELECT val, typeof(val) FROM numeric_demo;

CREATE TABLE settings (
    key TEXT PRIMARY KEY,
    enabled INTEGER DEFAULT 0 CHECK(enabled IN (0, 1))
);

INSERT INTO settings VALUES ('dark_mode', 1);
INSERT INTO settings VALUES ('notifications', 0);

SELECT key, CASE enabled WHEN 1 THEN '开启' ELSE '关闭' END AS status
FROM settings;

-- 方式 1：TEXT（ISO 8601，推荐）
CREATE TABLE events_v1 (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    event_date TEXT NOT NULL  -- '2025-06-15T14:30:00+08:00'
);

-- 可以直接比较和排序
SELECT * FROM events_v1 WHERE event_date > '2025-06-01';

-- 方式 2：INTEGER（Unix 时间戳）
CREATE TABLE events_v2 (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    event_time INTEGER NOT NULL  -- Unix 秒级时间戳
);

-- 方式 3：REAL（Julian Day Number）
CREATE TABLE events_v3 (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    event_jd REAL NOT NULL  -- Julian Day Number
);

-- ⚠️ 错误方式：使用 REAL（浮点精度问题）
CREATE TABLE orders_bad (price REAL);
INSERT INTO orders_bad VALUES (0.1 + 0.2);
SELECT price, price = 0.3 FROM orders_bad;  -- 可能返回 0！

-- ✅ 正确方式 1：INTEGER（以分为单位）
CREATE TABLE orders_int (price_cents INTEGER);
INSERT INTO orders_int VALUES (1999);  -- 19.99 元
SELECT price_cents / 100.0 AS price FROM orders_int;

-- ✅ 正确方式 2：TEXT（字符串存储）
CREATE TABLE orders_text (price TEXT);
INSERT INTO orders_text VALUES ('19.99');

-- 创建存储文件的表
CREATE TABLE files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    mime_type TEXT NOT NULL,
    data BLOB NOT NULL,
    size INTEGER NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

-- 插入 BLOB 数据
-- 方式 1：十六进制字面量
INSERT INTO files (filename, mime_type, data, size)
VALUES ('test.bin', 'application/octet-stream', X'DEADBEEF', 4);

-- 方式 2：使用 zeroblob 预分配空间（适合大文件）
INSERT INTO files (filename, mime_type, data, size)
VALUES ('large.bin', 'application/octet-stream', zeroblob(1024*1024), 1048576);

-- 方式 3：使用 randomblob 生成随机数据
INSERT INTO files (filename, mime_type, data, size)
VALUES ('random.bin', 'application/octet-stream', randomblob(256), 256);

-- 默认最大 BLOB 大小：1 GB
PRAGMA max_blob_size;  -- 1073741824

-- 设置最大 BLOB 大小
PRAGMA max_blob_size = 104857600;  -- 100 MB

CREATE TABLE strict_demo (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    score REAL,
    data BLOB
) STRICT;

-- 以下操作会报错：
INSERT INTO strict_demo VALUES (1, '张三', '25', 95.5, NULL);
-- Error: cannot store TEXT value in INTEGER column

-- 必须使用正确的类型
INSERT INTO strict_demo VALUES (1, '张三', 25, 95.5, NULL);  -- OK

-- STRICT 表允许的类型关键字
-- INTEGER, REAL, TEXT, BLOB, ANY

-- 混合使用 STRICT 和 ANY
CREATE TABLE mixed (
    id INTEGER PRIMARY KEY STRICT,  -- 严格整数
    metadata ANY                    -- 任意类型
) STRICT;

INSERT INTO mixed VALUES (1, '{"key": "value"}');  -- OK
INSERT INTO mixed VALUES (2, 42);                   -- OK
INSERT INTO mixed VALUES (3, X'DEAD');              -- OK

-- TEXT → INTEGER/REAL（比较时自动转换）
SELECT '42' + 8;        -- 50
SELECT '3.14' * 2;      -- 6.28

-- INTEGER → REAL
SELECT typeof(42 + 0.5);  -- real

-- 不能转换的保持原样
SELECT typeof('hello' + 1);  -- integer（'hello' 转为 0）
SELECT 'hello' + 1;           -- 1

SELECT CAST('42' AS INTEGER);          -- 42
SELECT CAST('3.14' AS REAL);           -- 3.14
SELECT CAST(42 AS TEXT);               -- '42'
SELECT CAST('not a number' AS INTEGER);-- 0
SELECT CAST(NULL AS TEXT);             -- NULL

-- CAST 在比较中的应用
SELECT * FROM users WHERE CAST(age AS REAL) > 18.5;

SELECT 1 < '2';        -- 1（TEXT 与 INTEGER 比较时 TEXT 转为数字）
SELECT '2' < '10';     -- 0（TEXT 比较，'2' > '1'）
SELECT 1 < 'hello';    -- 1（不能转为数字，INTEGER < TEXT）
SELECT X'01' < 'a';    -- 1（BLOB > TEXT 的规则，但实际取决于具体实现）

-- typeof() 返回值的存储类
SELECT typeof(42);          -- integer
SELECT typeof(42.0);        -- real
SELECT typeof('42');        -- text
SELECT typeof(NULL);        -- null
SELECT typeof(X'42');       -- blob

-- CASE 表达式的类型
SELECT typeof(CASE WHEN 1 THEN 42 ELSE 'hello' END);  -- integer
SELECT typeof(CASE WHEN 0 THEN 42 ELSE 'hello' END);  -- text

-- 聚合函数的返回类型
SELECT typeof(SUM(1, 2, 3));     -- integer
SELECT typeof(SUM(1, 2.5, 3));   -- real
SELECT typeof(GROUP_CONCAT('a','b'));  -- text

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,  -- 以分为单位
    attributes TEXT          -- JSON
);

INSERT INTO products VALUES
    (1, '笔记本电脑', 599900, '{"cpu":"i7","ram":"16GB","weight":"1.5kg"}'),
    (2, '机械键盘',   39900,  '{"switch":"青轴","layout":"87键"}');

SELECT name, json_extract(attributes, '$.cpu') AS cpu
FROM products WHERE json_extract(attributes, '$.cpu') IS NOT NULL;

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT NOT NULL,
    entity TEXT NOT NULL,
    entity_id INTEGER,
    old_value TEXT,
    new_value TEXT,
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
);

INSERT INTO audit_log (action, entity, entity_id, old_value, new_value)
VALUES ('UPDATE', 'users', 1, '{"name":"张三"}', '{"name":"张三丰"}');