-- EHS shared database (hse): performance indexes for high-traffic list/filter queries.
-- Safe to re-run: each statement is skipped if the index already exists (MySQL 8+).
-- Apply once per environment: mysql -u ... -p hse < docs/sql/add_performance_indexes.sql

-- ------------------------------------------------------------------
-- PTW
-- ------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS `idx_ptw_permits_status` ON `ptw_permits` (`permit_status_id`);
CREATE INDEX IF NOT EXISTS `idx_ptw_permits_location` ON `ptw_permits` (`location_id`);
CREATE INDEX IF NOT EXISTS `idx_ptw_permits_uploader` ON `ptw_permits` (`uploader_user_id`);
CREATE INDEX IF NOT EXISTS `idx_ptw_permits_created` ON `ptw_permits` (`created_at`);

CREATE INDEX IF NOT EXISTS `idx_ptw_submissions_permit` ON `ptw_submissions` (`permit_id`);
CREATE INDEX IF NOT EXISTS `idx_ptw_submissions_permit_role` ON `ptw_submissions` (`permit_id`, `submission_role`);

CREATE INDEX IF NOT EXISTS `idx_ptw_ocm_permit_closing` ON `ptw_opening_closing_map` (`permit_id`, `closing_sub_id`);

CREATE INDEX IF NOT EXISTS `idx_ptw_form_section_map_form_order` ON `ptw_form_section_map` (`form_id`, `section_in_form_order`);

-- ------------------------------------------------------------------
-- Ticketing (PTW + SWI)
-- ------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS `idx_tickets_created` ON `tickets` (`created_at`);
CREATE INDEX IF NOT EXISTS `idx_tickets_status` ON `tickets` (`status_id`);
CREATE INDEX IF NOT EXISTS `idx_tickets_logs_ticket_created` ON `tickets_logs` (`ticket_id`, `created_at`);

-- ------------------------------------------------------------------
-- SWI
-- ------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS `idx_swi_document_assigned` ON `swi_document` (`assigned_to_user_id`, `assigned_at`);
CREATE INDEX IF NOT EXISTS `idx_swi_document_location` ON `swi_document` (`location_id`, `issued_at`);

-- ------------------------------------------------------------------
-- ORAC
-- ------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS `idx_orac_assessments_created` ON `orac_assessments` (`created_at`);
CREATE INDEX IF NOT EXISTS `idx_orac_assessments_uploader` ON `orac_assessments` (`uploader_user_id`);

-- ------------------------------------------------------------------
-- Shared auth / users
-- ------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS `idx_users_email` ON `users` (`email_id`);
