-- #! ../data/house_prices_sample.csv

-- Example: Using CASE generation for data analysis
--
-- This example demonstrates how the --generate-case feature can be used
-- to create intelligent CASE statements based on actual data values.
--
-- Run: ./target/release/sql-cli --generate-case data/house_prices_sample.csv --column ocean_proximity --style values
-- to generate a CASE statement for ocean proximity categories.

-- Group houses by ocean proximity and age category
WITH categorized AS (
    SELECT
        *,
        -- Generated using: --generate-case data/house_prices_sample.csv --column ocean_proximity --style values
        CASE
            WHEN ocean_proximity = '<1H OCEAN' THEN '<1H OCEAN'
            WHEN ocean_proximity = 'INLAND' THEN 'INLAND'
            WHEN ocean_proximity = 'ISLAND' THEN 'ISLAND'
            WHEN ocean_proximity = 'NEAR BAY' THEN 'NEAR BAY'
            WHEN ocean_proximity = 'NEAR OCEAN' THEN 'NEAR OCEAN'
            ELSE 'Other'
        END AS ocean_category,

        -- Generated using: --generate-case data/house_prices_sample.csv --column housing_median_age --style ranges
        CASE
            WHEN housing_median_age <= 10 THEN '0-10 years'
            WHEN housing_median_age > 10 AND housing_median_age <= 20 THEN '11-20 years'
            WHEN housing_median_age > 20 AND housing_median_age <= 30 THEN '21-30 years'
            WHEN housing_median_age > 30 AND housing_median_age <= 40 THEN '31-40 years'
            WHEN housing_median_age > 40 THEN '40+ years'
        END AS age_category
    FROM house_prices_sample
)
SELECT
    ocean_category,
    age_category,
    COUNT(*) as count,
    FORMAT_CURRENCY(AVG(median_house_value), 'USD') as avg_price,
    FORMAT_CURRENCY(MIN(median_house_value), 'USD') as min_price,
    FORMAT_CURRENCY(MAX(median_house_value), 'USD') as max_price
FROM categorized
GROUP BY ocean_category, age_category
ORDER BY ocean_category, age_category;
GO

-- Demonstrate value-based CASE generation with custom density categories
WITH density_analysis AS (
    SELECT
        *,
        population / households as persons_per_household,
        -- This CASE could be generated by analyzing the distribution of the ratio
        CASE
            WHEN population / households < 2 THEN 'Low Density'
            WHEN population / households >= 2 AND population / households <= 3 THEN 'Medium Density'
            WHEN population / households > 3 THEN 'High Density'
        END AS density_category
    FROM house_prices_sample
    WHERE households > 0
)
SELECT
    density_category,
    COUNT(*) as count,
    FORMAT_CURRENCY(AVG(median_house_value), 'USD') as avg_price,
    ROUND(AVG(persons_per_household), 2) as avg_persons_per_household
FROM density_analysis
GROUP BY density_category
ORDER BY avg_persons_per_household;
GO