CREATE TABLE products AS
SELECT * FROM (VALUES
  ('Dell XPS 15', 1299),
  ('Dell Latitude 5520', 899),
  ('HP EliteBook 840', 1099),
  ('Apple MacBook Pro 14', 1999),
  ('Lenovo ThinkPad X1 Carbon', 1499)
) AS t(name, price);

-- 找出所有 Dell 笔记本电脑
SELECT name, price
FROM products
WHERE name LIKE 'Dell %';

-- 不区分大小写的搜索
SELECT name FROM products WHERE name ILIKE '%pro%';

-- 匹配符合 SKU 格式的产品编码（大写字母-数字组合）
CREATE TABLE sku_map AS
SELECT * FROM (VALUES
  ('SKU-A1B2', 'Dell XPS 15'),
  ('SKU-C3D4', 'HP EliteBook 840'),
  ('REF-XYZ',  'Apple MacBook Pro 14'),
  ('INV-99A1', 'Lenovo ThinkPad X1 Carbon')
) AS t(sku, product_name);

SELECT sku, product_name
FROM sku_map
WHERE sku ~ '^[A-Z]{3}-[A-Z0-9]{4}$';

-- 清理产品名称中的多余空格和特殊字符
SELECT
  name,
  regexp_replace(name, '\s+', ' ', 'g') AS cleaned_name,
  regexp_replace(name, '[^a-zA-Z0-9 ]', '', 'g') AS alphanum_only
FROM products;

CREATE TABLE brand_names AS
SELECT * FROM (VALUES
  ('Dell'), ('HP'), ('Apple'), ('Lenovo'), ('Asus'), ('Acer')
) AS t(brand);

CREATE TABLE user_input AS
SELECT * FROM (VALUES
  ('Del'), ('Hp'), ('Apel'), ('Lenovo'), ('Assu'), ('MicroSoft')
) AS t(input_brand);

-- 为每个用户输入找到最相似的品牌
SELECT
  u.input_brand,
  b.brand,
  levenshtein(u.input_brand, b.brand) AS distance
FROM user_input u
CROSS JOIN brand_names b
WHERE levenshtein(u.input_brand, b.brand) <= 2
ORDER BY u.input_brand, distance;

CREATE TABLE addresses AS
SELECT * FROM (VALUES
  ('1600 Amphitheatre Pkwy, Mountain View, CA'),
  ('1600 amphetamine pkwy mountain view ca'),
  ('350 5th Ave, New York, NY'),
  ('350 Fifth Avenue, New York, NY'),
  ('1 Apple Park Way, Cupertino, CA')
) AS t(raw_address);

-- 找出相似度 >= 0.7 的地址对
WITH normalized AS (
  SELECT
    raw_address,
    row_number() OVER () AS rid,
    LOWER(regexp_replace(raw_address, '[^a-zA-Z0-9 ]', '')) AS clean_addr
  FROM addresses
)
SELECT
  a.raw_address,
  b.raw_address,
  round(1.0 - levenshtein(a.clean_addr, b.clean_addr)::FLOAT /
        GREATEST(length(a.clean_addr), length(b.clean_addr)), 2) AS similarity
FROM normalized a
JOIN normalized b ON a.rid < b.rid
WHERE 1.0 - levenshtein(a.clean_addr, b.clean_addr)::FLOAT /
        GREATEST(length(a.clean_addr), length(b.clean_addr)) >= 0.7;

INSTALL fts;
LOAD fts;

-- 创建模拟产品评论表
CREATE TABLE reviews AS
SELECT * FROM (VALUES
  (1, 'Great laptop, fast and lightweight. Battery lasts all day.'),
  (2, 'Terrible product. Screen cracked after one week of use.'),
  (3, 'The keyboard feels premium, but the trackpad is too small.'),
  (4, 'Excellent value for money. Highly recommend for students.'),
  (5, 'Overpriced and overhyped. Nothing special about this model.')
) AS t(id, comment);

-- 创建 FTS 索引
SELECT create_fts_index('reviews', 'comment', false, false, 'reviews_idx', '', false);

-- 全文搜索（关键词：laptop, battery, keyboard）
SELECT id, comment FROM reviews
WHERE search_fts_index('reviews_idx', 'laptop battery keyboard') > 0
ORDER BY id;

-- 搜索并查看相关性评分
SELECT
  id,
  comment,
  ROUND(search_fts_index('reviews_idx', 'great value'), 3) AS relevance
FROM reviews
WHERE search_fts_index('reviews_idx', 'great value') > 0;

-- 用户历史购买记录
CREATE TABLE purchases AS
SELECT * FROM (VALUES
  (1001, 'Dell XPS 15'),
  (1002, 'HP EliteBook 840'),
  (1003, 'MacBook Pro 14')
) AS t(user_id, product_name);

-- 候选推荐商品
CREATE TABLE candidates AS
SELECT * FROM (VALUES
  ('Dell XPS 17'),
  ('Dell Latitude 5530'),
  ('HP Spectre x360'),
  ('Lenovo ThinkPad T14'),
  ('MacBook Air 15'),
  ('Asus ZenBook 14')
) AS t(product_name);

-- 基于 Levenshtein 距离推荐相似产品
SELECT
  p.user_id,
  p.product_name AS purchased,
  c.product_name AS recommended,
  levenshtein(LOWER(p.product_name), LOWER(c.product_name)) AS similarity_score
FROM purchases p
CROSS JOIN candidates c
WHERE levenshtein(LOWER(p.product_name), LOWER(c.product_name)) <= 8
ORDER BY p.user_id, similarity_score;