-- Database optimization script for WAHA application
-- This script adds indexes to improve query performance

-- Indexes for vehicles table
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_make ON vehicles USING btree (LOWER(make));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_fuel_type ON vehicles USING btree (fuel_type);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_year ON vehicles USING btree (year);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_transmission ON vehicles USING btree ("Transmission");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_size_class ON vehicles USING btree (vehicle_size_class);

-- Indexes for vehicles_4 table
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_4_make ON vehicles_4 USING btree (LOWER(make));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_4_base_model ON vehicles_4 USING btree (LOWER(base_model));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vehicles_4_model ON vehicles_4 USING btree (LOWER(model));

-- Indexes for inserted_cars table
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_inserted_cars_name ON inserted_cars USING btree (LOWER(name));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_inserted_cars_year ON inserted_cars USING btree (year);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_inserted_cars_composite ON inserted_cars USING btree (LOWER(name), year, LOWER(base_model));

-- Indexes for user-related tables (assuming they exist)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_phone_number ON users USING btree (phone_number);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_language ON users USING btree (language);

-- Indexes for buy/sell tables (generic pattern - adjust table names as needed)
-- These will need to be created for each country-specific table
-- Example for bahrain tables:
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_buy_user_id ON bahrain_buy USING btree (user_id);
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_buy_vehicle_id ON bahrain_buy USING btree (vehicle_id);
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_sell_user_id ON bahrain_sell USING btree (user_id);
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_sell_vehicle_id ON bahrain_sell USING btree (vehicle_id);

-- Indexes for match tables
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_matches_buy_id ON bahrain_matches USING btree (buy_id);
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_matches_sell_id ON bahrain_matches USING btree (sell_id);
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bahrain_matches_buyer_price ON bahrain_matches USING btree (buyer_price);

-- Analyze tables to update statistics
ANALYZE vehicles;
ANALYZE vehicles_4;
ANALYZE inserted_cars;

-- Show index usage statistics
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- Show table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC; 