-- 模拟电商订单表
CREATE TABLE orders AS
SELECT * FROM (VALUES
    ('Alice', 'Laptop Pro 15'),
    ('Bob',   'Wireless Mouse X200'),
    ('Carol', 'USB-C Hub Adapter'),
    ('Dave',  'Mechanical Keyboard RGB'),
    ('Eve',   'Monitor Stand Adjustable')
) AS t(name, product);

-- 查找包含 "USB" 的产品
SELECT name, product
FROM orders
WHERE product LIKE '%USB%';

-- 查找以 "Wireless" 开头，第7个字符是字母的产品
SELECT name, product
FROM orders
WHERE product LIKE 'Wireless %';

-- ILIKE：不区分大小写匹配
SELECT name, product
FROM orders
WHERE product ILIKE '%keyboard%';

-- SIMILAR TO：使用正则风格模式
SELECT name, product
FROM orders
WHERE product SIMILAR TO '(Laptop|Keyboard)%';

-- 提取产品名称中的数字
SELECT name, product,
       regexp_extract(product, '(\d+)') AS version_number
FROM orders;

-- 检查邮箱格式（示例数据）
CREATE TABLE contacts AS
SELECT * FROM (VALUES
    ('Alice',   '[email protected]'),
    ('Bob',     'bob@@company'),
    ('Carol',   '[email protected]'),
    ('Dave',    'dave_no_at_sign')
) AS t(name, email);

SELECT name, email,
       CASE WHEN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'
            THEN 'valid' ELSE 'invalid' END AS status
FROM contacts;

-- 替换文本中的特定模式
SELECT name,
       regexp_replace(product, '\s+', '_', 'g') AS slugified_name
FROM orders;

-- 加载 fuzzystrmatch 扩展
LOAD fuzzystrmatch;

-- 创建一份包含拼写错误的搜索记录
CREATE TABLE search_logs AS
SELECT * FROM (VALUES
    ('LappTop'),
    ('Wireles Mouse'),
    ('USB HUb'),
    ('Mchnical Keyboard'),
    ('Moniter Stand')
) AS t(query);

-- 计算每个搜索词与正确商品名的最小 Levenshtein 距离
SELECT sl.query, o.product,
       levenshtein(sl.query, o.product) AS distance
FROM search_logs sl
CROSS JOIN orders o
ORDER BY sl.query, distance;

-- 自动纠错：找到最接近的正确商品名
WITH ranked AS (
    SELECT sl.query, o.product,
           levenshtein(sl.query, o.product) AS dist,
           row_number() OVER (PARTITION BY sl.query ORDER BY levenshtein(sl.query, o.product)) AS rn
    FROM search_logs sl
    CROSS JOIN orders o
)
SELECT query, product AS corrected_product
FROM ranked
WHERE rn = 1;

-- 加载 FTS 扩展
LOAD fts;

-- 创建文档集合
CREATE TABLE articles AS
SELECT * FROM (VALUES
    (1, 'Getting Started with DuckDB: A Fast Analytical Database'),
    (2, 'DuckDB vs PostgreSQL: Performance Comparison for Analytics'),
    (3, 'Building Real-Time Dashboards with DuckDB and Python'),
    (4, 'Advanced Window Functions in DuckDB for Time Series Analysis'),
    (5, 'DuckDB Extensions: FTS, Spatial, and JSON Capabilities')
) AS t(id, title);

-- 创建 FTS 索引
CREATE INDEX idx_title_fts ON articles USING fts(title);

-- 基本全文搜索
SELECT id, title, rank
FROM articles
WHERE title MATCH 'DuckDB AND (Python OR Performance)'
ORDER BY rank DESC;

-- 使用 duckdb_fts_rank 获取相关性评分
SELECT id, title,
       duckdb_fts_rank(title, 'DuckDB AND Analytics') AS relevance_score
FROM articles
WHERE title MATCH 'DuckDB AND Analytics'
ORDER BY relevance_score DESC;

-- 综合示例：从用户评论中提取情感关键词并计算相似度
CREATE TABLE reviews AS
SELECT * FROM (VALUES
    (1, 'Amazing laptop! Super fast and great battery life.'),
    (2, 'The keyboard feels cheap and the screen is dim.'),
    (3, 'Best purchase ever! Highly recommend this mouse.'),
    (4, 'Terrible product. Broke after one week of use.')
) AS t(id, comment);

-- 提取正面和负面关键词
SELECT id,
       comment,
       CASE
           WHEN comment ILIKE '%amazing%' OR comment ILIKE '%best%' OR comment ILIKE '%great%'
                OR comment ILIKE '%recommend%' THEN 'positive'
           WHEN comment ILIKE '%terrible%' OR comment ILIKE '%cheap%' OR comment ILIKE '%dim%'
                OR comment ILIKE '%broke%' THEN 'negative'
           ELSE 'neutral'
       END AS sentiment
FROM reviews;

-- 结合正则和 Levenshtein 进行品牌名称标准化
CREATE TABLE brand_mentions AS
SELECT * FROM (VALUES
    ('duckdb'),
    ('DuckDB'),
    ('duck_db'),
    ('Duck DB'),
    ('PostgreSQL'),
    ('postgres'),
    ('Postgres')
) AS t(brand);

SELECT brand,
       lower(regexp_replace(brand, '[^a-zA-Z]', '', 'g')) AS normalized_brand
FROM brand_mentions;