-- Performance indexes for Rafif Hotel
-- Run via: node src/migrate.js  (the migrate script handles duplicate-key errors gracefully)

ALTER TABLE bookings  ADD INDEX idx_bookings_guest    (guest_id);
ALTER TABLE bookings  ADD INDEX idx_bookings_room     (room_id);
ALTER TABLE bookings  ADD INDEX idx_bookings_checkin  (check_in);
ALTER TABLE bookings  ADD INDEX idx_bookings_checkout (check_out);
ALTER TABLE bookings  ADD INDEX idx_bookings_status   (status);
ALTER TABLE bookings  ADD INDEX idx_bookings_updated  (updated_at);
ALTER TABLE bookings  ADD INDEX idx_bookings_room_dates (room_id, check_in, check_out);

ALTER TABLE payments  ADD INDEX idx_payments_booking  (booking_id);
ALTER TABLE payments  ADD INDEX idx_payments_created  (created_at);

ALTER TABLE guests    ADD INDEX idx_guests_name       (full_name);
ALTER TABLE guests    ADD INDEX idx_guests_phone      (phone);
ALTER TABLE guests    ADD INDEX idx_guests_idno       (id_or_passport_no);

-- guest_documents index lives in 004_schema_sync.sql (table is created there)

ALTER TABLE audit_logs ADD INDEX idx_audit_entity     (entity_type, entity_id);
ALTER TABLE audit_logs ADD INDEX idx_audit_created    (created_at);
ALTER TABLE audit_logs ADD INDEX idx_audit_staff      (staff_name);