-- Database Index Optimization (fixed)
-- Run with: psql -U lahuasca -d lahuasca -f add-indexes.sql

-- Images table indexes (most queried)
CREATE INDEX IF NOT EXISTS idx_images_room_id ON images(room_id);
CREATE INDEX IF NOT EXISTS idx_images_category ON images(category);
CREATE INDEX IF NOT EXISTS idx_images_location_target ON images(location_target);
CREATE INDEX IF NOT EXISTS idx_images_uploaded_at ON images(uploaded_at DESC);

-- Room amenity links (join table)
CREATE INDEX IF NOT EXISTS idx_room_amenity_links_amenity_id ON room_amenity_links(amenity_id);

-- Orders table
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at DESC);

-- Room reservations
CREATE INDEX IF NOT EXISTS idx_room_reservations_room_id ON room_reservations(room_id);
CREATE INDEX IF NOT EXISTS idx_room_reservations_dates ON room_reservations(check_in, check_out);
CREATE INDEX IF NOT EXISTS idx_room_reservations_user_id ON room_reservations(user_id);

-- Restaurant reservations
CREATE INDEX IF NOT EXISTS idx_reservations_date ON reservations(date);
CREATE INDEX IF NOT EXISTS idx_reservations_user_id ON reservations(user_id);

-- Social event reservations
CREATE INDEX IF NOT EXISTS idx_social_event_reservations_event_id ON social_event_reservations(social_event_id);

-- Contact messages
CREATE INDEX IF NOT EXISTS idx_contact_messages_created_at ON contact_messages(created_at DESC);

-- Room comments
CREATE INDEX IF NOT EXISTS idx_room_comments_room_id ON room_comments(room_id);
CREATE INDEX IF NOT EXISTS idx_room_comments_created_at ON room_comments(created_at DESC);

-- Messages (chat)
CREATE INDEX IF NOT EXISTS idx_messages_room ON messages(room_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);

-- Enable TOAST compression for large TEXT columns in images
ALTER TABLE images ALTER COLUMN data SET STORAGE EXTERNAL;
ALTER TABLE images ALTER COLUMN thumbnail SET STORAGE EXTERNAL;
ALTER TABLE images ALTER COLUMN medium SET STORAGE EXTERNAL;

-- Vacuum analyze to update statistics
ANALYZE images;
ANALYZE rooms;
ANALYZE orders;
ANALYZE room_reservations;
ANALYZE reservations;