# name: test/sql/functions/file_util.test
# description: duckfn::duck_vfs 便捷读写（Hutool FileUtil 风格）：UTF-8 字符串 / 字节数组的读与写、覆盖策略 Replace / FailIfExists / Append、size / exists / read_lines，含覆盖更长旧文件（C API 没有 truncate）与非法 UTF-8
# group: [functions]
require duckfn
# 这一组测 duckfn::duck_vfs 这层便利 API（调用方只表达意图）；底层 VFS 形态（with_file_system、
# 聚合里读文件）在 file_system.test 里。比对一律用 DuckDB 内置的 read_text / read_blob，
# 读写走同一套文件系统；长度用 octet_length / length 现推，不写死平台相关字节数。
# __TEST_DIR__ 是测试运行器提供的目录（就是 test/sql）。
# ============================================================================
# 基本写入与读回
# ============================================================================
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_a.txt', 'hello 世界');
# DuckDB 自己读回来：内容与长度都对（size 是文件字节数，不是字符数）
query I
SELECT CASE WHEN content = 'hello 世界' AND size = strlen('hello 世界') THEN 1 ELSE 0 END
FROM read_text('__TEST_DIR__/dfn_file_a.txt');
----
1
# 我们自己的四个入口互相一致：文本、字节、长度、是否存在。
# 字节那一列用 encode(...) 把文本变成 BLOB 再比（BLOB 比 BLOB）；
# 不能拿 BLOB::VARCHAR 去比文本 —— DuckDB 的 BLOB→VARCHAR 会把非 ASCII 字节转义成 \xNN。
query IIII
SELECT (dfn_file_read_text(p) = 'hello 世界')::INT,
(dfn_file_read_bytes(p) = encode('hello 世界'))::INT,
(dfn_file_size(p) = strlen('hello 世界'))::INT,
dfn_file_exists(p)::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_a.txt')) t(p);
----
1 1 1 1
# 写函数返回写入的字节数
query I
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_a.txt', 'hello 世界');
----
12
# ============================================================================
# 不存在的文件：exists 为 false，读与取长度报错（错误文案带 duckfn::duck_vfs:: 前缀）
# ============================================================================
query I
SELECT dfn_file_exists(p)::INT FROM (VALUES ('__TEST_DIR__/dfn_file_missing.txt')) t(p);
----
0
statement error
SELECT dfn_file_read_text(p) FROM (VALUES ('__TEST_DIR__/dfn_file_missing.txt')) t(p);
----
duckfn::duck_vfs::read
statement error
SELECT dfn_file_size(p) FROM (VALUES ('__TEST_DIR__/dfn_file_missing.txt')) t(p);
----
duckfn::duck_vfs::size
# 路径含 NUL 字节：转 C 字符串时就挡住，exists 也直接给 false
statement error
SELECT dfn_file_read_text(p) FROM (VALUES ('a' || chr(0) || 'b')) t(p);
----
duckfn::duck_vfs::read: the path contains a NUL byte
query I
SELECT dfn_file_exists(p)::INT FROM (VALUES ('a' || chr(0) || 'b')) t(p);
----
0
# ============================================================================
# 覆盖：旧文件更长（C API 没有 truncate，内部靠一条 COPY 把旧文件清零）
# ============================================================================
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_long.txt', repeat('x', 200000));
query I
SELECT (dfn_file_size(p) = 200000)::INT FROM (VALUES ('__TEST_DIR__/dfn_file_long.txt')) t(p);
----
1
# 覆盖成短内容：长度必须精确等于新内容，尾部不能留下旧内容的残渣
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_long.txt', 'short');
query III
SELECT (dfn_file_read_text(p) = 'short')::INT,
(dfn_file_size(p) = 5)::INT,
(SELECT CASE WHEN size = 5 AND content = 'short' THEN 1 ELSE 0 END
FROM read_text('__TEST_DIR__/dfn_file_long.txt'))::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_long.txt')) t(p);
----
1 1 1
# 覆盖成空内容：文件仍在（exists 为 true），但 0 字节
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_long.txt', '');
query II
SELECT dfn_file_size(p)::INT, dfn_file_exists(p)::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_long.txt')) t(p);
----
0 1
# 覆盖成更长的内容（不需要清零的那条路径），内容逐字节一致
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_long.txt', repeat('y', 200000));
query I
SELECT (md5(dfn_file_read_text(p)) = md5(repeat('y', 200000)))::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_long.txt')) t(p);
----
1
# 覆盖写字节也同样处理（BLOB 进、BLOB 出）
statement ok
SELECT dfn_file_write_bytes('__TEST_DIR__/dfn_file_long.bin', repeat('z', 100000)::BLOB);
query I
SELECT (dfn_file_size(p) = 100000)::INT FROM (VALUES ('__TEST_DIR__/dfn_file_long.bin')) t(p);
----
1
statement ok
SELECT dfn_file_write_bytes('__TEST_DIR__/dfn_file_long.bin', '\x01\x02'::BLOB);
query II
SELECT (dfn_file_read_bytes(p) = '\x01\x02'::BLOB)::INT,
(dfn_file_size(p) = 2)::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_long.bin')) t(p);
----
1 1
# ============================================================================
# 覆盖策略：FailIfExists（已存在就报错且不改动原文件；不存在则正常创建）
#
# __TEST_DIR__ 就是 test/sql，本地重复跑测试时文件会累积下来，所以「要求文件不存在」的用例
# 一律用「本轮唯一后缀」拼路径：后缀在下面这张表里取一次（微秒时间戳），
# 同一次运行内稳定、不同轮次不会撞。
# ============================================================================
statement ok
CREATE OR REPLACE TABLE dfn_file_round AS SELECT strftime(now(), '%Y%m%d%H%M%S%f') AS id;
# 已有文件（固定名字，覆盖写 → 重复跑也是已知状态）
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_existing.txt', 'first');
statement error
SELECT dfn_file_write_text_new('__TEST_DIR__/dfn_file_existing.txt', 'second');
----
duckfn::duck_vfs::write
# 报错之后原文件必须原封不动
query I
SELECT (dfn_file_read_text(p) = 'first')::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_existing.txt')) t(p);
----
1
# 不存在的文件：正常创建（路径带本轮唯一后缀）
statement ok
SELECT dfn_file_write_text_new(
'__TEST_DIR__/dfn_file_fresh_' || (SELECT id FROM dfn_file_round) || '.txt',
'created'
);
query I
SELECT (dfn_file_read_text(p) = 'created')::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_fresh_' || (SELECT id FROM dfn_file_round) || '.txt')) t(p);
----
1
# ============================================================================
# 覆盖策略：Append（存在则接在末尾，不存在则创建）
# ============================================================================
statement ok
SELECT dfn_file_append_text('__TEST_DIR__/dfn_file_existing.txt', '-tail');
query I
SELECT (dfn_file_read_text(p) = 'first-tail')::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_existing.txt')) t(p);
----
1
# 追加不会破坏已有内容：长度是两段之和
query I
SELECT (dfn_file_size(p) = strlen('first') + strlen('-tail'))::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_existing.txt')) t(p);
----
1
# 不存在的文件：追加会创建它（同样用本轮唯一后缀）
statement ok
SELECT dfn_file_append_text(
'__TEST_DIR__/dfn_file_appended_' || (SELECT id FROM dfn_file_round) || '.txt',
'born-by-append'
);
query I
SELECT (dfn_file_read_text(p) = 'born-by-append')::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_appended_' || (SELECT id FROM dfn_file_round) || '.txt')) t(p);
----
1
# ============================================================================
# 字节数组与非法 UTF-8
# ============================================================================
statement ok
SELECT dfn_file_write_bytes('__TEST_DIR__/dfn_file_bytes.bin', '\xFF\xFE'::BLOB);
query III
SELECT (dfn_file_read_bytes(p) = '\xFF\xFE'::BLOB)::INT,
(dfn_file_size(p) = 2)::INT,
(SELECT CASE WHEN size = 2 THEN 1 ELSE 0 END
FROM read_blob('__TEST_DIR__/dfn_file_bytes.bin'))::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_bytes.bin')) t(p);
----
1 1 1
# 严格读：非法 UTF-8 报错，错误里给出第一个非法字节的位置
statement error
SELECT dfn_file_read_text(p) FROM (VALUES ('__TEST_DIR__/dfn_file_bytes.bin')) t(p);
----
not valid UTF-8
# lossy 读：每个脏字节换成一个 U+FFFD，字符数是 2
query I
SELECT (length(dfn_file_read_text_lossy(p)) = 2)::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_bytes.bin')) t(p);
----
1
# ============================================================================
# 按行读：\n 分行、行尾 \r 去掉、末尾换行不产生空行
# ============================================================================
statement ok
SELECT dfn_file_write_text(
'__TEST_DIR__/dfn_file_lines.txt',
'a' || chr(10) || 'b' || chr(13) || chr(10) || 'c' || chr(10)
);
query I
SELECT (array_to_string(dfn_file_read_lines(p), '|') = 'a|b|c')::INT
FROM (VALUES ('__TEST_DIR__/dfn_file_lines.txt')) t(p);
----
1
# 空文件读成空列表
statement ok
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_empty.txt', '');
query I
SELECT (len(dfn_file_read_lines(p)) = 0)::INT FROM (VALUES ('__TEST_DIR__/dfn_file_empty.txt')) t(p);
----
1
# ============================================================================
# 在 DDL 里调用写函数：覆盖更长旧文件时内部会跑一条 COPY，这里确认 DDL 场景下不会卡住
# （普通 SELECT 之外的另一种可嵌套场景）
# ============================================================================
statement ok
CREATE TABLE dfn_file_ctas AS
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_ctas.txt', repeat('z', 300000)) AS written;
query II
SELECT t.written, dfn_file_size(v.p)
FROM dfn_file_ctas t, (VALUES ('__TEST_DIR__/dfn_file_ctas.txt')) v(p);
----
300000 300000
# 再覆盖成短内容（CTAS 里触发 COPY 清零）
statement ok
CREATE OR REPLACE TABLE dfn_file_ctas AS
SELECT dfn_file_write_text('__TEST_DIR__/dfn_file_ctas.txt', 'tiny') AS written;
query I
SELECT (dfn_file_read_text(p) = 'tiny')::INT FROM (VALUES ('__TEST_DIR__/dfn_file_ctas.txt')) t(p);
----
1