-- ============================================================
-- Research Survey Management System - Indexes & Partitioning
-- ============================================================

USE research_survey_system;

-- ============================================================
-- Full-text Search Indexes
-- ============================================================

-- Already created in schema:
-- research_projects: ft_projects (project_name, description)
-- surveys: ft_surveys (title, description)
-- question_responses: ft_qr_value (response_value)

-- ============================================================
-- Additional Performance Indexes
-- ============================================================

-- Survey responses by date range
CREATE INDEX IF NOT EXISTS idx_responses_started ON survey_responses(started_at);
CREATE INDEX IF NOT EXISTS idx_responses_completed ON survey_responses(completed_at);

-- Question responses by response date
CREATE INDEX IF NOT EXISTS idx_qr_response_date ON question_responses(response_date);

-- Participant tracking by invitation date
CREATE INDEX IF NOT EXISTS idx_participants_invited ON survey_participants(invited_at);
CREATE INDEX IF NOT EXISTS idx_participants_completed ON survey_participants(completed_at);

-- Analytics by calculation time
CREATE INDEX IF NOT EXISTS idx_ra_calculated_at ON response_analytics(calculated_at);

-- Audit log by date range
CREATE INDEX IF NOT EXISTS idx_audit_created_at ON audit_log(created_at);

-- System events by completion
CREATE INDEX IF NOT EXISTS idx_events_completed ON system_events(completed_at);

-- Composite indexes for common queries
CREATE INDEX IF NOT EXISTS idx_responses_survey_status ON survey_responses(survey_id, status);
CREATE INDEX IF NOT EXISTS idx_responses_survey_completed ON survey_responses(survey_id, completed_at);
CREATE INDEX IF NOT EXISTS idx_qr_response_question ON question_responses(response_id, question_id);

-- ============================================================
-- Table Partitioning Setup (for high-volume tables)
-- ============================================================

-- Note: Partitioning is set up for future scalability.
-- Uncomment and modify based on expected data volume.

-- ALTER TABLE question_responses
-- PARTITION BY RANGE (YEAR(created_at)) (
--     PARTITION p2024 VALUES LESS THAN (2025),
--     PARTITION p2025 VALUES LESS THAN (2026),
--     PARTITION p2026 VALUES LESS THAN (2027),
--     PARTITION p_future VALUES LESS THAN MAXVALUE
-- );

-- ALTER TABLE audit_log
-- PARTITION BY RANGE (YEAR(created_at)) (
--     PARTITION p2024 VALUES LESS THAN (2025),
--     PARTITION p2025 VALUES LESS THAN (2026),
--     PARTITION p2026 VALUES LESS THAN (2027),
--     PARTITION p_future VALUES LESS THAN MAXVALUE
-- );

-- ============================================================
-- Scheduled Events (Automated Tasks)
-- ============================================================

-- Enable event scheduler
SET GLOBAL event_scheduler = ON;

-- Event: Cleanup expired sessions (Hourly)
CREATE EVENT IF NOT EXISTS cleanup_expired_sessions_event
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
    CALL CleanupExpiredSessions();

-- Event: Update survey analytics (Every 30 minutes)
CREATE EVENT IF NOT EXISTS update_survey_analytics_event
ON SCHEDULE EVERY 30 MINUTE
STARTS CURRENT_TIMESTAMP
DO
    CALL CalculateSurveyAnalytics();

-- Event: Archive old completed surveys (Daily)
CREATE EVENT IF NOT EXISTS archive_completed_surveys_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
    CALL ArchiveCompletedSurveys(90);