-- GRC Platform: Additional Performance Indexes

-- Full-text search on risks
ALTER TABLE risks ADD FULLTEXT INDEX ft_risk_search (title, description);

-- Full-text search on controls
ALTER TABLE controls ADD FULLTEXT INDEX ft_control_search (title, description);

-- Full-text search on audit findings
ALTER TABLE audit_findings ADD FULLTEXT INDEX ft_finding_search (title, description, recommendation);

-- Full-text search on policies
ALTER TABLE policies ADD FULLTEXT INDEX ft_policy_search (title, content);

-- Full-text search on evidence
ALTER TABLE evidence ADD FULLTEXT INDEX ft_evidence_search (title, description);

-- Compliance score queries
ALTER TABLE control_framework_mappings ADD INDEX idx_requirement (requirement_id);
ALTER TABLE control_framework_mappings ADD INDEX idx_control (control_id);

-- Risk treatment queries
ALTER TABLE risk_treatments ADD INDEX idx_risk (risk_id);
ALTER TABLE risk_treatments ADD INDEX idx_status (status);

-- Vendor assessment queries
ALTER TABLE vendors ADD INDEX idx_tenant_next_assessment (tenant_id, next_assessment);
ALTER TABLE vendors ADD INDEX idx_criticality (tenant_id, criticality);

-- Incident queries
ALTER TABLE incidents ADD INDEX idx_reported_at (tenant_id, reported_at);

-- Audit queries
ALTER TABLE audits ADD INDEX idx_tenant_dates (tenant_id, planned_start, planned_end);

-- Framework requirements hierarchy
ALTER TABLE framework_requirements ADD INDEX idx_ref_code (reference_code);

-- Audit logs retention
ALTER TABLE audit_logs ADD INDEX idx_created_at (created_at);
