-- fix issue with current_scores; only combine user data after the median score has been selected
-- fix issue with both sms_index/ams_index; put DISTINCT on the initial insert into the ams_users_tmp/sms_users_tmp, which is necessary after current_scores change
DROP VIEW current_scores;
DROP VIEW current_network_scores;
CREATE OR REPLACE VIEW current_network_scores AS

WITH recent_scores AS (

  SELECT alocidispid, blocidispid, score, created_at, scorer FROM (SELECT *, row_number() OVER (PARTITION BY alocidispid, blocidispid ORDER BY scores.created_at DESC) AS rownum FROM scores) tmp WHERE rownum < 6

),   ranked_scores AS (

  SELECT alocidispid, blocidispid, score, created_at, scorer FROM (SELECT percent_rank() over (PARTITION BY alocidispid, blocidispid ORDER BY score ASC) AS pc, * FROM recent_scores) tmp WHERE pc <= .5 ORDER BY pc DESC

),   median_scores AS (

  SELECT alocidispid, blocidispid, score, created_at, scorer FROM (SELECT * , row_number() OVER (PARTITION BY alocidispid, blocidispid, scorer ORDER BY score DESC) AS pc2 FROM ranked_scores) tmp where pc2 < 2

)
SELECT alocidispid, blocidispid, score, created_at, scorer FROM median_scores;

CREATE OR REPLACE 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 OR REPLACE VIEW nondirected_network_scores AS
WITH recent_scores AS (

  SELECT alocidispid, blocidispid, score, created_at FROM (SELECT *, row_number() OVER (PARTITION BY alocidispid, blocidispid ORDER BY scores.created_at DESC) AS rownum FROM scores) tmp WHERE rownum < 6 AND scorer = 0

),   ranked_scores AS (

  SELECT alocidispid, blocidispid, score, created_at FROM (SELECT percent_rank() over (PARTITION BY alocidispid, blocidispid ORDER BY score ASC) AS pc, * FROM recent_scores) tmp WHERE pc <= .5 ORDER BY pc DESC

),   median_scores AS (

  SELECT alocidispid, blocidispid, score, created_at FROM (SELECT * , row_number() OVER (PARTITION BY alocidispid, blocidispid ORDER BY score DESC) AS pc2 FROM ranked_scores) tmp WHERE pc2 < 2

),   forward_scores AS (

  SELECT alocidispid AS from_location, blocidispid AS to_location, score, created_at FROM median_scores

),   backward_scores AS (

  SELECT blocidispid AS from_location, alocidispid AS to_location, score, created_at FROM median_scores

),   merged_directions AS (

  SELECT from_location, to_location, score, created_at FROM forward_scores UNION SELECT from_location, to_location, score, created_at FROM backward_scores

)
SELECT from_location AS alocidispid, to_location AS blocidispid, score, created_at FROM (SELECT *, row_number() OVER (PARTITION BY from_location, to_location ORDER BY created_at DESC) AS row FROM merged_directions) tmp WHERE row < 2;


CREATE OR REPLACE VIEW nondirected_scores AS

    SELECT nondirected_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 nondirected_network_scores
    INNER JOIN users as a_users ON a_users.last_jam_locidispid = nondirected_network_scores.alocidispid
    INNER JOIN users as b_users ON b_users.last_jam_locidispid = nondirected_network_scores.blocidispid
    ORDER BY full_score ASC;


CREATE VIEW most_recent_scores AS SELECT * FROM scores s WHERE score_dt = (SELECT max(score_dt) FROM scores s0 WHERE s0.alocidispid = s.alocidispid AND s0.blocidispid = s.blocidispid);

DROP FUNCTION get_work (mylocidispid BIGINT, myaddr BIGINT);
CREATE FUNCTION get_work (mylocidispid BIGINT, myaddr BIGINT) RETURNS TABLE (client_id VARCHAR(64)) ROWS 5 VOLATILE AS $$
BEGIN
	CREATE TEMPORARY TABLE foo (locidispid BIGINT, locid INT);
	INSERT INTO foo SELECT DISTINCT locidispid, locidispid/1000000 FROM connections WHERE client_type = 'client';
	DELETE FROM foo WHERE locidispid IN (SELECT DISTINCT blocidispid FROM most_recent_scores WHERE alocidispid = mylocidispid AND (current_timestamp - score_dt) < INTERVAL '24 hours');
	DELETE FROM foo WHERE locid NOT IN (SELECT locid FROM geoiplocations WHERE geog && st_buffer((SELECT geog FROM geoiplocations WHERE locid = mylocidispid/1000000), 4023360));
	CREATE TEMPORARY TABLE bar (client_id VARCHAR(64), locidispid BIGINT, r DOUBLE PRECISION);
	INSERT INTO bar SELECT l.client_id, l.locidispid, random() FROM connections l, foo f WHERE l.locidispid = f.locidispid AND l.client_type = 'client' AND addr != myaddr;
	DROP TABLE foo;
	DELETE FROM bar b WHERE r != (SELECT MAX(r) FROM bar b0 WHERE b0.locidispid = b.locidispid);
	RETURN QUERY SELECT b.client_id FROM bar b ORDER BY r LIMIT 5;
	DROP TABLE bar;
	RETURN;
END;
$$ LANGUAGE plpgsql;