DROP VIEW IF EXISTS current_scores;
DROP VIEW IF EXISTS current_network_scores;
DROP TABLE IF EXISTS current_network_scores;

-- this table is used to store a periodically computed summary of the scoring.
-- the raw scores are posted to the table scores and then processed somehow
-- and stored here.

CREATE TABLE current_network_scores (
  alocidispid BIGINT NOT NULL,
  blocidispid BIGINT NOT NULL,
  score INTEGER NOT NULL,
  limited BOOL NOT NULL,
  score_dt TIMESTAMP NOT NULL
);

CREATE UNIQUE INDEX current_network_scores_a_b_ndx ON current_network_scores(alocidispid, blocidispid);
CREATE UNIQUE INDEX current_network_scores_b_a_ndx ON current_network_scores(blocidispid, alocidispid);

-- the update_current_network_scores function is used to compute a new score to insert into current_network_scores
-- given that a new row has been inserted into scores. a new value for the summary score is computed and stored.
-- aloc is the alocidispid for the new score, bloc is the blocidispid.
CREATE OR REPLACE FUNCTION update_current_network_scores(aloc BIGINT, bloc BIGINT) RETURNS VOID
STRICT VOLATILE AS $$
  DECLARE
    newscore INTEGER;
    newscore_dt TIMESTAMP;
    newscore_limited BOOL;
    sum INTEGER;
    kount INTEGER;
    r RECORD;
    avgscore INTEGER;
    maxscore INTEGER;
    minscore INTEGER;
  BEGIN
    -- find the 6 most recent scores
    -- (supposedly newscore is the first...)
    -- hybrid scheme: compute the average of some recent scores, then limit newscore to be between 4/5 and 6/5 of the average
    newscore := NULL;
    newscore_dt := NULL;
    newscore_limited := FALSE;
    sum := 0;
    kount := 0;
    FOR r IN SELECT score, score_dt FROM scores WHERE alocidispid = aloc AND blocidispid = bloc ORDER BY score_dt DESC LIMIT 6 LOOP
      IF newscore IS NULL THEN
        newscore := r.score;
        newscore_dt := r.score_dt;
      ELSE
        sum := sum + r.score;
        kount := kount + 1;
      END IF;
    END LOOP;

    -- if no scores in query at all, then delete any current entry
    IF newscore IS NULL THEN
      DELETE FROM current_network_scores WHERE alocidispid = aloc AND blocidispid = bloc;
      IF aloc != bloc THEN
        DELETE FROM current_network_scores WHERE alocidispid = bloc AND blocidispid = aloc;
      END IF;
    END IF;

    -- if there are scores older than newscore, then use their average to limit the range of newscore
    IF kount > 0 THEN
      avgscore := sum / kount;
      maxscore := avgscore*6/5;
      minscore := avgscore*4/5;
      -- the score newscore will be inserted as the current value in current_network_scores, but we will limit it
      -- to be no greater than 120% of the average and no less than 80% of the average. this will dampen wild
      -- swings in the scores.
      IF newscore > maxscore THEN
        newscore := maxscore;
        newscore_limited := TRUE;
      ELSEIF newscore < minscore THEN
        newscore := minscore;
        newscore_limited := TRUE;
      END IF;
    END IF;

    UPDATE current_network_scores SET score = newscore, limited = newscore_limited, score_dt = newscore_dt WHERE alocidispid = aloc AND blocidispid = bloc;
    IF NOT FOUND THEN
      INSERT INTO current_network_scores (alocidispid, blocidispid, score, limited, score_dt) VALUES (aloc, bloc, newscore, newscore_limited, newscore_dt);
    END IF;

    IF aloc != bloc THEN
      UPDATE current_network_scores SET score = newscore, limited = newscore_limited, score_dt = newscore_dt WHERE alocidispid = bloc AND blocidispid = aloc;
      IF NOT FOUND THEN
        INSERT INTO current_network_scores (alocidispid, blocidispid, score, limited, score_dt) VALUES (bloc, aloc, newscore, newscore_limited, newscore_dt);
      END IF;
    END IF;
  END;
$$ LANGUAGE plpgsql;

CREATE VIEW current_scores AS

  SELECT current_network_scores.*, a_users.id as a_userid, b_users.id as b_userid, (COALESCE(a_users.last_jam_audio_latency, 13) + COALESCE(b_users.last_jam_audio_latency, 13) + score) AS full_score, a_users.last_jam_audio_latency AS a_audio_latency, b_users.last_jam_audio_latency AS b_audio_latency
  FROM current_network_scores
    INNER JOIN users as a_users ON a_users.last_jam_locidispid = current_network_scores.alocidispid
    INNER JOIN users as b_users ON b_users.last_jam_locidispid = current_network_scores.blocidispid
  ORDER BY full_score ASC;

create index users_last_jam_locidispid_ndx on users (last_jam_locidispid);

CREATE OR REPLACE FUNCTION populate_current_network_scores() RETURNS VOID
STRICT VOLATILE AS $$
  DECLARE
    r RECORD;
  BEGIN
    FOR r IN SELECT DISTINCT alocidispid, blocidispid FROM scores WHERE alocidispid <= blocidispid ORDER BY alocidispid, blocidispid LOOP
      PERFORM update_current_network_scores(r.alocidispid, r.blocidispid);
    END LOOP;
  END;
$$ LANGUAGE plpgsql;
