-- Simulate an e-commerce orders table
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);

-- Find products containing "USB"
SELECT name, product
FROM orders
WHERE product LIKE '%USB%';

-- Find products starting with "Wireless"
SELECT name, product
FROM orders
WHERE product LIKE 'Wireless %';

-- ILIKE: case-insensitive match
SELECT name, product
FROM orders
WHERE product ILIKE '%keyboard%';

-- SIMILAR TO: regex-style patterns
SELECT name, product
FROM orders
WHERE product SIMILAR TO '(Laptop|Keyboard)%';

-- Extract version numbers from product names
SELECT name, product,
       regexp_extract(product, '(\d+)') AS version_number
FROM orders;

-- Validate email format (sample data)
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;

-- Replace specific patterns in text
SELECT name,
       regexp_replace(product, '\s+', '_', 'g') AS slugified_name
FROM orders;

-- Load fuzzystrmatch extension
LOAD fuzzystrmatch;

-- Create a table of search records with typos
CREATE TABLE search_logs AS
SELECT * FROM (VALUES
    ('LappTop'),
    ('Wireles Mouse'),
    ('USB HUb'),
    ('Mchnical Keyboard'),
    ('Moniter Stand')
) AS t(query);

-- Calculate minimum Levenshtein distance for each search term
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;

-- Auto-correction: find the closest correct product name
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;

-- Load FTS extension
LOAD fts;

-- Create a document collection
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);

-- Create FTS index
CREATE INDEX idx_title_fts ON articles USING fts(title);

-- Basic full-text search
SELECT id, title, rank
FROM articles
WHERE title MATCH 'DuckDB AND (Python OR Performance)'
ORDER BY rank DESC;

-- Use duckdb_fts_rank for relevance scoring
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;

-- Comprehensive example: extract sentiment keywords from reviews
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);

-- Extract positive and negative sentiment keywords
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;

-- Combine regex and Levenshtein for brand name normalization
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;