DROP SCHEMA IF EXISTS apasc CASCADE;
CREATE SCHEMA apasc;

CREATE TABLE apasc.eduinst
(
    eduinst_id UUID NOT NULL,
    CONSTRAINT eduinst_pk PRIMARY KEY (eduinst_id)
);

CREATE TABLE apasc.spec
(
    spec_id    UUID NOT NULL,
    eduinst_id UUID NOT NULL,
    CONSTRAINT spec_pk PRIMARY KEY (spec_id),
    CONSTRAINT spec_eduinst_fk FOREIGN KEY (eduinst_id) REFERENCES apasc.eduinst (eduinst_id)
);

CREATE SEQUENCE apasc.selection_session_seq START 1 INCREMENT BY 1;
CREATE TABLE apasc.selection_session
(
    selection_session_id INT       NOT NULL DEFAULT nextval('apasc.selection_session_seq')::regclass,
    created              TIMESTAMP NOT NULL DEFAULT current_timestamp,
    CONSTRAINT selection_session_pk PRIMARY KEY (selection_session_id)
);
CREATE INDEX selection_session_created_idx ON apasc.selection_session (created);

CREATE TABLE apasc.ses_spec
(
    spec_id              UUID NOT NULL,
    selection_session_id INT  NOT NULL,
    CONSTRAINT ses_spec_pk PRIMARY KEY (spec_id, selection_session_id),
    CONSTRAINT ses_spec_spec_id_fk FOREIGN KEY (spec_id) REFERENCES apasc.spec (spec_id),
    CONSTRAINT ses_spec_selection_session_id_fk FOREIGN KEY (selection_session_id) REFERENCES apasc.selection_session (selection_session_id)
);


CREATE TABLE apasc.enrollee
(
    user_id              UUID     NOT NULL,
    selection_session_id INT      NOT NULL,
    disabled             BOOLEAN  NOT NULL DEFAULT FALSE,
    selected_target_id   UUID              DEFAULT NULL,
    selection_limit      SMALLINT NOT NULL DEFAULT 3,
    CONSTRAINT enrollee_pk PRIMARY KEY (user_id, selection_session_id),
    CONSTRAINT enrollee_selection_session_id_fk FOREIGN KEY (selection_session_id) REFERENCES apasc.selection_session (selection_session_id),
    CONSTRAINT enrollee_selection_limit_more_than_zero CHECK ( selection_limit >= 0 ),
    CONSTRAINT enrollee_selected_target_id_fk FOREIGN KEY (selected_target_id, selection_session_id) REFERENCES apasc.ses_spec (spec_id, selection_session_id)
);
CREATE INDEX enrollee_selection_idx ON apasc.enrollee (selected_target_id, disabled);

CREATE TABLE apasc.enrollee_select
(
    user_id              UUID      NOT NULL,
    selection_session_id INT       NOT NULL,
    spec_id              UUID      NOT NULL,
    status               INT       NOT NULL DEFAULT 0,
    score                INT       NOT NULL DEFAULT 0,
    created              TIMESTAMP NOT NULL DEFAULT current_timestamp,
    CONSTRAINT enrollee_select_pk PRIMARY KEY (user_id, selection_session_id, spec_id),
    CONSTRAINT enrollee_select_user_fk FOREIGN KEY (user_id, selection_session_id) REFERENCES apasc.enrollee (user_id, selection_session_id),
    CONSTRAINT enrollee_select_spec_fk FOREIGN KEY (spec_id, selection_session_id) REFERENCES apasc.ses_spec (spec_id, selection_session_id)
);
CREATE INDEX enrollee_select_enrollee_idx ON apasc.enrollee_select (user_id, selection_session_id);
CREATE INDEX enrollee_select_spec_id_idx ON apasc.enrollee_select (spec_id, selection_session_id);
CREATE INDEX enrollee_select_status_idx ON apasc.enrollee_select (status);
CREATE INDEX enrollee_select_score_idx ON apasc.enrollee_select (score);
CREATE INDEX enrollee_select_created_idx ON apasc.enrollee_select (created);


DO
$$
    DECLARE
        i    bigint;
        j    BIGINT;
        eiid UUID;
        sid  UUID;
        ssid INT;
    BEGIN
        FOR i IN 1..100
            LOOP
                eiid := CAST(LPAD(TO_HEX(i), 32, '0') AS UUID);
                INSERT INTO apasc.eduinst(eduinst_id) VALUES (eiid);
                FOR j IN 1..100
                    LOOP
                        sid := CAST(LPAD(TO_HEX((i - 1) * 100 + j), 32, '0') AS UUID);
                        INSERT INTO apasc.spec(spec_id, eduinst_id) VALUES (sid, eiid);
                    END LOOP;
            END LOOP;
        FOR ssid IN 1..10
            LOOP
                INSERT INTO apasc.selection_session(selection_session_id, created) VALUES (ssid, current_timestamp);
                FOR i IN 1..100
                    LOOP
                        eiid := CAST(LPAD(TO_HEX(i), 32, '0') AS UUID);
                        FOR j IN 1..100
                            LOOP
                                sid := CAST(LPAD(TO_HEX((i - 1) * 100 + j), 32, '0') AS UUID);
                                INSERT INTO apasc.ses_spec(spec_id, selection_session_id)
                                VALUES (sid, ssid);
                            END LOOP;
                    END LOOP;
            END LOOP;
    END;
$$;

DO
$$
    DECLARE
        i   bigint;
        j   BIGINT;
        uid UUID;
        sid UUID;
    BEGIN
        FOR i IN 1..1000000
            LOOP
                uid := CAST(LPAD(TO_HEX(i), 32, '0') AS UUID);
                INSERT INTO apasc.enrollee(user_id, selection_session_id)
                VALUES (uid, 1);
                FOR j IN 1..5
                    LOOP
                        sid = CAST(LPAD(TO_HEX((i - 1 + j) % 10000 + 1), 32, '0') AS UUID);
                        INSERT INTO apasc.enrollee_select(user_id, selection_session_id, spec_id, score)
                        VALUES (uid, 1, sid, floor(random() * 1000)::int);
                    END LOOP;
            END LOOP;
    END;
$$;

EXPLAIN ANALYSE
SELECT es.user_id
     , es.score
     , es.selection_session_id
     , es.spec_id
     , e.disabled
     , e.selection_limit
     , e.selected_target_id
FROM apasc.enrollee_select es
         JOIN apasc.enrollee e on e.user_id = es.user_id and e.selection_session_id = es.selection_session_id
WHERE es.spec_id = CAST(LPAD(TO_HEX(1), 32, '0') AS UUID)
ORDER BY es.score DESC, es.created
LIMIT 200;

EXPLAIN ANALYSE
INSERT INTO apasc.enrollee_select(user_id, selection_session_id, spec_id)
VALUES (CAST(LPAD(TO_HEX(1), 32, '0') AS UUID), 1, CAST(LPAD(TO_HEX(201), 32, '0') AS UUID));

EXPLAIN ANALYSE
UPDATE apasc.enrollee e
SET selected_target_id = CAST(LPAD(TO_HEX(2), 32, '0') AS UUID),
    selection_limit    = selection_limit - 1
WHERE user_id = CAST(LPAD(TO_HEX(1), 32, '0') AS UUID);

EXPLAIN ANALYSE
SELECT es.spec_id
     , es.score
     , (SELECT count(*)
        FROM apasc.enrollee_select es2
        WHERE es.selection_session_id = es2.selection_session_id
          AND es.spec_id = es2.spec_id
          AND es.user_id <> es2.user_id
          AND (es.score < es2.score OR (es.score = es2.score AND es.created > es2.created))) + 1 AS place
FROM apasc.enrollee_select es
WHERE es.user_id = CAST(LPAD(TO_HEX(20), 32, '0') AS UUID)
  AND es.selection_session_id = 1;

SELECT count(*)
FROM apasc.enrollee_select es
WHERE es.spec_id = CAST(LPAD(TO_HEX(5), 32, '0') AS UUID)
AND es.selection_session_id = 1;