What is the right approach to create indexes to a complex query

SQL++
,
2

Each subquery optimized itself. Index will be used when the FROM clause has bucket. You can run the EXPLAIN on each SELECT (if there is correlated replace with dummy $xxx) and check how the plan looks and what index needs to be created.

See if you can reduce complexity by using MAX and HAVING clauses based on your query logic.
Also try with Index Join [Index on joined field] because there are lot of predicates on right side of JOIN

    SELECT RAW DISTINCT event.visitId FROM bucket event
    JOIN bucket visit ON KEYS event.visitId
    WHERE event.type = "EVENT"
        AND (visit.status = "OPEN" OR (visit.status IS MISSING AND visit.stopDateTime IS NOT VALUED))
        AND visit.visitType = "VT_CONSULTATION"
        AND event.visitId NOT IN(
            SELECT raw ev.visitId FROM bucket ev
                WHERE
                    ev.type = "EVENT"
                AND (
                        ev.eventTypeId IN ["ET_CPN", "ET_CPS", "ET_CPON", "ET_PF"]
                    OR  ev.configurableMetadataId IN ["ET_CPN", "ET_CPS", "ET_CPON", "ET_PF"]
                )
                AND SPLIT(META(ev).`id`,':')[0] != "_sync"
                AND (ev.isVoided = false OR ev.isVoided IS MISSING)
        )
        AND SPLIT(META(visit).`id`,':')[0] != "_sync"
        AND (visit.isVoided = false OR visit.isVoided IS MISSING)
    GROUP BY event.visitId
    LETTING mdateTime = MAX(event.dateTime)
    HAVING DATE_DIFF_MILLIS(NOW_MILLIS(), mdateTime, "hour") > 6 ;