\c hawkentracker

-- Performance indexes
CREATE INDEX ix_matches_first_seen ON public.matches (first_seen);
CREATE INDEX ix_matches_last_seen ON public.matches (last_seen);
CREATE INDEX ix_matches_seen_range ON public.matches (first_seen, last_seen) INCLUDE (match_id);
CREATE INDEX ix_players_first_seen ON public.players (first_seen);
CREATE INDEX ix_players_last_seen ON public.players (last_seen);
CREATE INDEX ix_players_seen_range ON public.players (first_seen, last_seen) INCLUDE (player_id);
CREATE INDEX ix_match_players_first_seen ON public.match_players (first_seen);
CREATE INDEX ix_match_players_last_seen ON public.match_players (last_seen);
CREATE INDEX ix_match_players_players_seen_range ON public.match_players (first_seen, last_seen) INCLUDE (player_id);
CREATE INDEX ix_polls_seen ON public.polls ("start", "end");

-- Make sure everything's ready to be worked on
VACUUM ANALYZE alembic_version, match_players, matches, player_stats, players, polls, updates;

-- Backfill: polls - end
UPDATE polls
SET
  "end" = "start" + MAKE_INTERVAL(secs => time_elapsed)
WHERE
  "end" IS NULL;

-- Correct data consistency: Remove overlapping polls
-- Extend outer end to cover full range
DO $$
DECLARE 
  changes integer := 0;
	counter integer := 1;
BEGIN
  -- While changes to be made
  WHILE (
    SELECT
      COUNT(*)
    FROM
      (
        SELECT
          p2.start "outer_start",
          p2.end "outer_end",
          MIN(p3.start) "inner_start",
          MAX(p3.end) "inner_end"
        FROM
          polls p2
          JOIN polls p3 ON p2.start < p3.start
          AND p2.end >= p3.start
          AND p3.status = 3
        WHERE
          p2.status = 3
        GROUP BY
          p2.start,
          p2.end
        ORDER BY
          outer_start
      )
    WHERE
      outer_end < inner_end
  ) > 0 LOOP
  -- Make changes
  WITH rows_affected AS (
    UPDATE polls
    SET
      "end" = inner_end
    FROM
      (
        SELECT
          p2.start "outer_start",
          p2.end "outer_end",
          MIN(polls.start) "inner_start",
          MAX(polls.end) "inner_end"
        FROM
          polls,
          polls p2
        WHERE
          p2.start < polls.start
          AND p2.end >= polls.start
          AND polls.status = 3
          AND p2.status = 3
        GROUP BY
          p2.start,
          p2.end
      )
    WHERE
      polls.start = outer_start
      AND outer_end < inner_end
      RETURNING *
    )
    SELECT COUNT(*) INTO changes FROM rows_affected;
    RAISE NOTICE 'Update Iteration %: %', counter, changes;
    counter := counter + 1;
  END LOOP;
  RAISE NOTICE 'Update Iteration %: Complete', counter;
END;
$$;
-- Remove inner polling ranges
DELETE FROM polls p1 USING (
  SELECT
    *
  FROM
    polls p2
  WHERE
    EXISTS (
      SELECT
        1
      FROM
        polls p3
      WHERE
        p2.start < p3.start
        AND p2.end >= p3.start
        AND p3.status = 3
    )
    AND p2.status = 3
) p2
WHERE
  p1.start > p2.start
  AND p1.end <= p2.end;

-- Correct data consistency: pin players seen timestamps to polls
-- first_seen
UPDATE players
SET
  first_seen = "start"
FROM
  polls
WHERE
  "status" = 3
  AND first_seen >= "start"
  AND first_seen <= "end"
  AND first_seen != "start";
-- last_seen
UPDATE players
SET
  last_seen = "start"
FROM
  polls
WHERE
  "status" = 3
  AND last_seen >= "start"
  AND last_seen <= "end"
  AND last_seen != "start";
-- Correct data consistency: pin matches seen timestamps to polls
-- first_seen
UPDATE matches
SET
  first_seen = "start"
FROM
  polls
WHERE
  "status" = 3
  AND first_seen >= "start"
  AND first_seen <= "end"
  AND first_seen != "start";
-- last_seen
UPDATE matches
SET
  last_seen = "start"
FROM
  polls
WHERE
  "status" = 3
  AND last_seen >= "start"
  AND last_seen <= "end"
  AND last_seen != "start";
-- Correct data consistency: pin match players seen timestamps to polls
-- first_seen
UPDATE match_players
SET
  first_seen = "start"
FROM
  polls
WHERE
  "status" = 3
  AND first_seen >= "start"
  AND first_seen <= "end"
  AND first_seen != "start";
-- last_seen
UPDATE match_players
SET
  last_seen = "start"
FROM
  polls
WHERE
  "status" = 3
  AND last_seen >= "start"
  AND last_seen <= "end"
  AND last_seen != "start";

-- Refresh: players, matches, match_players
VACUUM ANALYZE players, matches, match_players;

-- Backfill: polls - players_updated, matches_updated
UPDATE polls
SET
  players_updated = (
    SELECT
      COUNT(DISTINCT player_id)
    FROM
      match_players
    WHERE
      first_seen <= "start"
      AND last_seen >= "start"
  ),
  matches_updated = (
    SELECT
      COUNT(match_id)
    FROM
      matches
    WHERE
      first_seen <= "start"
      AND last_seen >= "start"
  )
WHERE
  "status" = 3
  AND players_updated IS NULL
  AND matches_updated IS NULL;

-- Backfill: polls - players_added, matches_added
UPDATE polls
SET
  players_added = (
    SELECT
      COUNT(player_id)
    FROM
      players
    WHERE
      first_seen = "start"
  ),
  matches_added = (
    SELECT
      COUNT(match_id)
    FROM
      matches
    WHERE
      first_seen = "start"
  )
WHERE
  "status" = 3
  AND players_added IS NULL
  AND matches_added IS NULL;

-- Cleanup
VACUUM ANALYZE alembic_version, match_players, matches, player_stats, players, polls, updates;
