-- Performance Benchmarking Scripts for PostGIS Proximity Queries
-- Used to generate results reported in the paper

-- =============================================================================
-- PERFORMANCE BENCHMARKING SCRIPT 1: WITH SPATIAL INDEXING
-- =============================================================================

-- Create spatial index (if not already exists)
CREATE INDEX IF NOT EXISTS idx_merchants_geom ON merchants USING GIST(geom);

-- Benchmark ST_DWithin with spatial indexing
-- 10-mile radius from Georgetown, TX
\timing on

-- Test query 1: ST_DWithin (10 mile radius)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH source AS (
    SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom
)
SELECT
    m.merchant_name,
    m.address1,
    m.city,
    m.state,
    ST_Distance(m.geom, source.geom) * 0.000621371 AS distance_miles,
    ST_X(ST_Transform(m.geom::geometry, 4326)) as longitude,
    ST_Y(ST_Transform(m.geom::geometry, 4326)) as latitude
FROM merchants m
CROSS JOIN source
WHERE ST_DWithin(m.geom, source.geom, 16093.4) -- 10 miles in meters
ORDER BY distance_miles;

-- Run the same query 10 times for average timing
DO $$
DECLARE
    i INTEGER;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    total_time INTERVAL := '0 seconds';
BEGIN
    FOR i IN 1..10 LOOP
        start_time := clock_timestamp();
        
        PERFORM m.merchant_name
        FROM merchants m
        CROSS JOIN (SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom) source
        WHERE ST_DWithin(m.geom, source.geom, 16093.4);
        
        end_time := clock_timestamp();
        total_time := total_time + (end_time - start_time);
    END LOOP;
    
    RAISE NOTICE 'ST_DWithin Average Time (10 runs): %', total_time / 10;
END $$;

-- Test query 2: ST_Buffer (10 mile radius)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH source AS (
    SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom
),
circle AS (
    SELECT ST_Buffer(source.geom, 16093.4) AS geom
    FROM source
)
SELECT
    m.merchant_name,
    m.address1,
    ST_Distance(m.geom, (SELECT geom FROM source), 16093.4) * 0.000621371 AS distance_miles
FROM merchants m
WHERE ST_Within(m.geom::geometry, (SELECT geom::geometry FROM circle))
ORDER BY distance_miles;

-- Run ST_Buffer query 10 times for average
DO $$
DECLARE
    i INTEGER;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    total_time INTERVAL := '0 seconds';
BEGIN
    FOR i IN 1..10 LOOP
        start_time := clock_timestamp();
        
        PERFORM m.merchant_name
        FROM merchants m
        WHERE ST_Within(m.geom::geometry, 
            ST_Buffer(ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography, 16093.4)::geometry);
        
        end_time := clock_timestamp();
        total_time := total_time + (end_time - start_time);
    END LOOP;
    
    RAISE NOTICE 'ST_Buffer Average Time (10 runs): %', total_time / 10;
END $$;

-- =============================================================================
-- PERFORMANCE BENCHMARKING SCRIPT 2: WITHOUT SPATIAL INDEXING
-- =============================================================================

-- Drop spatial index to test without indexing
DROP INDEX IF EXISTS idx_merchants_geom;

-- Test ST_DWithin without spatial index
DO $$
DECLARE
    i INTEGER;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    total_time INTERVAL := '0 seconds';
BEGIN
    FOR i IN 1..10 LOOP
        start_time := clock_timestamp();
        
        PERFORM m.merchant_name
        FROM merchants m
        CROSS JOIN (SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom) source
        WHERE ST_DWithin(m.geom, source.geom, 16093.4);
        
        end_time := clock_timestamp();
        total_time := total_time + (end_time - start_time);
    END LOOP;
    
    RAISE NOTICE 'ST_DWithin WITHOUT INDEX Average Time (10 runs): %', total_time / 10;
END $$;

-- Test ST_Buffer without spatial index
DO $$
DECLARE
    i INTEGER;
    start_time TIMESTAMP;
    end_time TIMESTAMP;
    total_time INTERVAL := '0 seconds';
BEGIN
    FOR i IN 1..10 LOOP
        start_time := clock_timestamp();
        
        PERFORM m.merchant_name
        FROM merchants m
        WHERE ST_Within(m.geom::geometry, 
            ST_Buffer(ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography, 16093.4)::geometry);
        
        end_time := clock_timestamp();
        total_time := total_time + (end_time - start_time);
    END LOOP;
    
    RAISE NOTICE 'ST_Buffer WITHOUT INDEX Average Time (10 runs): %', total_time / 10;
END $$;

-- Recreate spatial index after testing
CREATE INDEX idx_merchants_geom ON merchants USING GIST(geom);

-- =============================================================================
-- INDEX SIZE AND CREATION TIME ANALYSIS
-- =============================================================================

-- Check index size
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes 
WHERE indexrelname = 'idx_merchants_geom';

-- Test index creation time
DROP INDEX IF EXISTS idx_merchants_geom;

\timing on
CREATE INDEX idx_merchants_geom ON merchants USING GIST(geom);
\timing off

-- =============================================================================
-- SCALABILITY TESTING QUERIES
-- =============================================================================

-- Query for different radius sizes to test scalability
-- 5-mile radius
WITH source AS (
    SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom
)
SELECT COUNT(*) as stores_within_5_miles
FROM merchants m
CROSS JOIN source
WHERE ST_DWithin(m.geom, source.geom, 8046.7); -- 5 miles

-- 15-mile radius  
WITH source AS (
    SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom
)
SELECT COUNT(*) as stores_within_15_miles
FROM merchants m
CROSS JOIN source
WHERE ST_DWithin(m.geom, source.geom, 24140.1); -- 15 miles

-- 30-mile radius
WITH source AS (
    SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom
)
SELECT COUNT(*) as stores_within_30_miles
FROM merchants m
CROSS JOIN source
WHERE ST_DWithin(m.geom, source.geom, 48280.3); -- 30 miles

-- =============================================================================
-- MEMORY USAGE ANALYSIS
-- =============================================================================

-- Check table and index memory usage
SELECT 
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as index_size
FROM pg_stat_user_tables 
WHERE relname = 'merchants';

-- Show query plan and buffer usage
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH source AS (
    SELECT ST_SetSRID(ST_MakePoint(-97.80909, 30.61703), 4326)::geography AS geom
)
SELECT m.merchant_name, ST_Distance(m.geom, source.geom) * 0.000621371 AS distance_miles
FROM merchants m
CROSS JOIN source
WHERE ST_DWithin(m.geom, source.geom, 16093.4)
ORDER BY distance_miles;