-- Pure SQL can't do fuzzy matching
SELECT a.name, b.name
FROM list_a a, list_b b
WHERE a.name LIKE b.name;  -- ❌ Returns nothing useful

-- Basic syntax
CREATE FUNCTION function_name(param1 TYPE, param2 TYPE) RETURNS return_type
AS $$
    -- Python code
    return result
$$ LANGUAGE python;

pip install duckdb

-- Install and load the Python extension
INSTALL python;
LOAD python;

-- Create a fuzzy matching function
CREATE FUNCTION fuzzy_match(a TEXT, b TEXT) RETURNS FLOAT
AS $$
    from difflib import SequenceMatcher
    return SequenceMatcher(None, a, b).ratio()
$$ LANGUAGE python;

-- Match all pairs with similarity > 75%
SELECT 
    a.name AS source_name,
    b.name AS target_name,
    fuzzy_match(a.name, b.name) AS similarity_score
FROM customer_list_a a
CROSS JOIN customer_list_b b
WHERE fuzzy_match(a.name, b.name) > 0.75
ORDER BY similarity_score DESC;

-- Find best matches with deduplication
WITH matched AS (
    SELECT 
        a.id AS a_id,
        a.name AS a_name,
        b.id AS b_id,
        b.name AS b_name,
        fuzzy_match(a.name, b.name) AS score
    FROM dedup_a a
    CROSS JOIN dedup_b b
),
top_matches AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY a_id ORDER BY score DESC
    ) AS rn
    FROM matched
    WHERE score > 0.8
)
SELECT a_id, a_name, b_id, b_name, ROUND(score, 4) AS score
FROM top_matches
WHERE rn = 1
ORDER BY score DESC;

-- Smarter matching with company suffix normalization
CREATE FUNCTION smart_match(a TEXT, b TEXT) RETURNS FLOAT
AS $$
    import re
    
    def normalize(name):
        # Remove parenthetical content
        name = re.sub(r'[（(].*?[）)]', '', name)
        # Remove common suffixes
        name = re.sub(r'(Limited|Inc|Corp|Group|Co\.)$', '', name)
        name = name.strip()
        return name
    
    from difflib import SequenceMatcher
    na, nb = normalize(a), normalize(b)
    return SequenceMatcher(None, na, nb).ratio()
$$ LANGUAGE python;

CREATE FUNCTION sentiment_score(text_input TEXT) RETURNS INTEGER
AS $$
    from textblob import TextBlob
    blob = TextBlob(text_input)
    return int(blob.sentiment.polarity * 100)
$$ LANGUAGE python;

-- Batch analyze review sentiment
SELECT 
    review_id,
    review_text,
    sentiment_score(review_text) AS score,
    CASE 
        WHEN sentiment_score(review_text) > 20 THEN 'Positive'
        WHEN sentiment_score(review_text) < -20 THEN 'Negative'
        ELSE 'Neutral'
    END AS sentiment
FROM product_reviews
ORDER BY score ASC;

CREATE FUNCTION validate_id_card(id_num TEXT) RETURNS BOOLEAN
AS $$
    if len(id_num) != 18:
        return False
    weights = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2]
    check_codes = '10X98765432'
    total = sum(int(id_num[i]) * weights[i] for i in range(17))
    return id_num[17].upper() == check_codes[total % 11]
$$ LANGUAGE python;

-- Validate all IDs in user table
SELECT user_id, id_card, validate_id_card(id_card) AS is_valid
FROM users
WHERE validate_id_card(id_card) = false;

CREATE FUNCTION standardize_address(addr TEXT) RETURNS TEXT
AS $$
    import re
    addr = re.sub(r'\s+', ' ', addr.strip())
    # Standardize abbreviations
    replacements = {
        'St.': 'Street', 'Ave.': 'Avenue',
        'NY': 'New York', 'SF': 'San Francisco'
    }
    for k, v in replacements.items():
        addr = addr.replace(k, v)
    return addr
$$ LANGUAGE python;

-- Batch standardize customer addresses
SELECT id, standardize_address(raw_address) AS clean_address
FROM customer_addresses;

-- ✅ GOOD: Filter first, then apply UDF
SELECT *, fuzzy_match(a.name, b.name) AS score
FROM list_a a, list_b b
WHERE a.region = b.region  -- Narrow the data first
  AND fuzzy_match(a.name, b.name) > 0.8;

-- ❌ BAD: Apply UDF to all combinations
SELECT *, fuzzy_match(a.name, b.name) AS score
FROM list_a a, list_b b;

-- One step to Python-powered SQL
LOAD python;
CREATE FUNCTION my_udf(x TEXT) RETURNS TEXT
AS $$ return x.upper() $$ LANGUAGE python;
SELECT my_udf('hello duckdb');
-- Result: HELLO DUCKDB