CREATE TABLE draft_statuses(
    draft_status_id smallint NOT NULL,
    name text NOT NULL,
    UNIQUE (name),
    CONSTRAINT draft_statuses_pk PRIMARY KEY (draft_status_id)
);

INSERT INTO draft_statuses(draft_status_id, name)
    VALUES (1, 'Scheduled');

INSERT INTO draft_statuses(draft_status_id, name)
    VALUES (2, 'Running');

INSERT INTO draft_statuses(draft_status_id, name)
    VALUES (3, 'Complete');

INSERT INTO draft_statuses(draft_status_id, name)
    VALUES (4, 'Manual');

CREATE TABLE drafts (
    draft_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    season_id bigint NOT NULL,
    draft_status_id smallint NOT NULL DEFAULT 1,
    name text,
    account_id bigint NOT NULL,
    started_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT drafts_pk PRIMARY KEY (draft_id),
    CONSTRAINT drafts_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT drafts_name_ck CHECK (char_length(name) <= 255),
    CONSTRAINT drafts_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE draft_users (
    draft_user_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    draft_id bigint NOT NULL,
    name text NOT NULL,
    account_id bigint,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT draft_users_pk PRIMARY KEY (draft_user_id),
    CONSTRAINT draft_users_drafts_fk FOREIGN KEY (draft_id) REFERENCES drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT draft_users_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE OR REPLACE FUNCTION gen_invite_code()
    RETURNS  varchar
    LANGUAGE plpgsql
AS $$
DECLARE
    base10 bigint;
    base36 varchar := '';
    intval bigint;
    char0z char[]  := regexp_split_to_array('0123456789abcdefghijklmnopqrstuvwxyz', '');
BEGIN
    SELECT floor(random() * 78364164095 + 2176782336) INTO base10;
    intval := abs(base10);
    WHILE intval != 0 LOOP
        base36 := char0z[(intval % 36)+1] || base36;
        intval := intval / 36;
    END LOOP;

    IF base10 = 0 THEN base36 := '0'; END IF;
    RETURN base36;
END;
$$;

CREATE TABLE draft_user_invites (
    draft_user_invite_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    draft_user_id bigint NOT NULL,
    name text NOT NULL,
    code text NOT NULL DEFAULT gen_invite_code(),
    created_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT draft_user_invites_pk PRIMARY KEY (draft_user_id),
    CONSTRAINT draft_user_invites_draft_users_fk FOREIGN KEY (draft_user_id) REFERENCES draft_users(draft_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT draft_user_invites_name_ck CHECK (char_length(name) <= 255)
);

CREATE TABLE picks (
    pick_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    draft_id integer NOT NULL,
    draft_user_id integer NOT NULL,
    team_id int NOT NULL,
    auto boolean NOT NULL DEFAULT FALSE,
    picked_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    UNIQUE (draft_id, team_id),
    CONSTRAINT picks_pk PRIMARY KEY (pick_id),
    CONSTRAINT picks_drafts_fk FOREIGN KEY (draft_id) REFERENCES drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT picks_draft_users_fk FOREIGN KEY (draft_user_id) REFERENCES draft_users(draft_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

-- TODO: support more than 2 players by persisting draft order and current_picker
CREATE OR REPLACE FUNCTION current_picker_name(_draft_id bigint)
    RETURNS text
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
BEGIN
    RETURN (SELECT name FROM draft_users WHERE draft_user_id = current_picker_id(_draft_id));
END;
$$;

CREATE OR REPLACE FUNCTION current_picker_account_id(_draft_id bigint)
    RETURNS bigint
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    pick_count smallint;
    player_1 integer;
    player_2 integer;
BEGIN
    SELECT count(1) FROM picks WHERE draft_id = _draft_id INTO pick_count;

    SELECT account_id
    FROM draft_users
    WHERE draft_id = _draft_id
    ORDER BY draft_user_id 
    LIMIT 1 INTO player_1;

    SELECT account_id
    FROM draft_users
    WHERE draft_id = _draft_id
    ORDER BY draft_user_id DESC
    LIMIT 1 INTO player_2;

    IF pick_count % 2 = 0 THEN
        RETURN player_1;
    ELSE
        RETURN player_2;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION current_picker_id(_draft_id bigint)
    RETURNS integer
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    pick_count smallint;
    player_1 integer;
    player_2 integer;
BEGIN
    SELECT count(1) FROM picks WHERE draft_id = _draft_id INTO pick_count;

    SELECT draft_user_id
    FROM draft_users
    WHERE draft_id = _draft_id
    ORDER BY draft_user_id 
    LIMIT 1 INTO player_1;

    SELECT draft_user_id
    FROM draft_users
    WHERE draft_id = _draft_id
    ORDER BY draft_user_id DESC
    LIMIT 1 INTO player_2;

    IF pick_count % 2 = 0 THEN
        RETURN player_1;
    ELSE
        RETURN player_2;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION pick(_draft_id integer, _team_id integer)
    RETURNS boolean
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
BEGIN
    INSERT INTO picks (draft_id, league_user_id, team_id) VALUES (_draft_id, current_picker_id(_draft_id), _team_id);
END;
$$;

-- create missing draft picks if any
CREATE OR REPLACE PROCEDURE auto_draft(_draft_id integer)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
    latest_pick_time timestamptz;
    latest_pick picks%ROWTYPE;
    pick_count integer;
    next_auto_draft_team integer;
    draft drafts%ROWTYPE;
    draft_league_id integer;
BEGIN
    SELECT * FROM drafts WHERE draft_id = _draft_id INTO draft;
    SELECT league_id FROM seasons WHERE season_id = draft.season_id INTO draft_league_id;

    latest_pick_time = draft.started_at;
    
    IF now() >= latest_pick_time AND draft.draft_status_id = 1 THEN
        UPDATE drafts SET draft_status_id = 2, modified_at = now() WHERE draft_id = _draft_id;
    END IF;

    LOOP
        SELECT count(1) FROM picks WHERE draft_id = _draft_id INTO pick_count;

        IF pick_count = (SELECT count(1) FROM teams JOIN league_divisions USING(league_division_id) WHERE league_id = draft_league_id) THEN
            UPDATE drafts SET draft_status_id = 3, modified_at = now() WHERE draft_id = _draft_id;
            RETURN;
        END IF;

        SELECT * FROM picks WHERE draft_id = _draft_id ORDER BY picked_at DESC LIMIT 1 INTO latest_pick;

        IF latest_pick IS NOT NULL THEN
            latest_pick_time = latest_pick.picked_at;
        END IF;

        IF (date_part('epoch', now())::integer - date_part('epoch', latest_pick_time)::integer) / 30 >= 1 THEN
            SELECT
                current_rankings.team_id
            FROM
                current_rankings
            LEFT JOIN picks ON picks.team_id = current_rankings.team_id
            WHERE
                pick_id IS NULL
                AND league_id = draft_league_id
            ORDER BY
                rank ASC
            LIMIT 1 INTO next_auto_draft_team;

            INSERT INTO picks(draft_id, draft_user_id, team_id, auto, picked_at)
                VALUES (_draft_id, current_picker_id(_draft_id),
                    (SELECT
                        current_rankings.team_id
                    FROM
                        current_rankings
                    LEFT JOIN picks ON picks.team_id = current_rankings.team_id
                        AND picks.draft_id = _draft_id
                    WHERE
                        pick_id IS NULL
                    ORDER BY
                        rank ASC
                    LIMIT 1),
                TRUE,
                latest_pick_time + interval '30 seconds');
        ELSE
            EXIT;
        END IF;
    END LOOP;
END;
$$;

CREATE OR REPLACE VIEW draft_picks AS
SELECT
    picks.draft_id,
    teams.team_id,
    teams.name AS team,
    accounts.name AS player
FROM
    picks
    JOIN teams USING (team_id)
    JOIN draft_users USING (draft_user_id)
    JOIN accounts USING (account_id)
ORDER BY
    picks.created_at;

CREATE OR REPLACE VIEW draft_info AS
WITH latest_draft_pick AS (
    SELECT
        draft_id,
        MAX(picked_at) AS picked_at
    FROM
        picks
    GROUP BY
        draft_id
)
SELECT
    draft_id,
    current_picker_account_id(draft_id) as current_player_id,
    current_picker_name(draft_id) as current_player_name,
    draft_status_id,
    CASE WHEN draft_status_id IN (1, 3) THEN
        0
    ELSE
        date_part('epoch',(coalesce(latest_draft_pick.picked_at, started_at) + interval '30 seconds') - now())::integer
    END AS round_time_remaining,
    CASE WHEN draft_status_id = 1 THEN
        'The draft will start in ' || to_char(started_at - now(), 'MI:SS')
    WHEN draft_status_id = 2 THEN
        'It''s ' || current_picker_name(draft_id) || '''s turn!'
    WHEN draft_status_id = 3 THEN
        'The draft is over, good luck!'
    END AS message,
    draft_status_id = 2 AS can_pick
FROM
    drafts
    LEFT JOIN latest_draft_pick USING (draft_id);

CREATE OR REPLACE FUNCTION accept_invite(_code text)
    RETURNS table (session_id text, invite_draft_id bigint)
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    new_account_id bigint;
    invite_draft_user_id bigint;
    invite_draft_id bigint;
    new_session_id text;
BEGIN
    SELECT draft_user_id FROM draft_user_invites WHERE code = _code INTO invite_draft_user_id;
    SELECT draft_id FROM draft_users WHERE draft_user_id = invite_draft_user_id INTO invite_draft_id;
    
    INSERT INTO accounts DEFAULT VALUES RETURNING account_id INTO new_account_id;

    UPDATE draft_users SET account_id = new_account_id WHERE draft_user_id = invite_draft_user_id;
    SELECT create_session(new_account_id) INTO new_session_id;

    RETURN QUERY SELECT new_session_id, invite_draft_id;
END;
$$;

CREATE OR REPLACE FUNCTION create_draft(league_id int, player_1 text, player_2 text, started_at timestamptz)
    RETURNS table (session_id text, new_draft_id bigint)
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    new_account_id bigint;
    new_draft_id bigint;
    current_season_id int := current_season_id(league_id);
    new_session_id text;
    player_2_id bigint;
BEGIN
    INSERT INTO accounts DEFAULT VALUES RETURNING account_id INTO new_account_id;
    SELECT create_session(new_account_id) INTO new_session_id;

    IF started_at IS NULL THEN
        INSERT INTO drafts (season_id, account_id, draft_status_id) VALUES (current_season_id, new_account_id, 4) RETURNING draft_id INTO new_draft_id;
    ELSE
        INSERT INTO drafts (season_id, account_id, started_at) VALUES (current_season_id, new_account_id, started_at) RETURNING draft_id INTO new_draft_id;
    END IF;

    INSERT INTO draft_users (draft_id, name, account_id) VALUES (new_draft_id, player_1, new_account_id);
    INSERT INTO draft_users (draft_id, name) VALUES (new_draft_id, CASE WHEN player_2 = '' THEN 'Player 2' ELSE player_2 END) RETURNING draft_user_id INTO player_2_id;
    INSERT INTO draft_user_invites (draft_user_id, name) VALUES (player_2_id, player_2);

    RETURN QUERY SELECT new_session_id, new_draft_id;
END;
$$;

CREATE OR REPLACE FUNCTION current_draft_picks(_draft_id int)
    RETURNS TABLE (team_id      int,
                   rank         smallint,
                   name         text,
                   picked       boolean,
                   pick_user    text)
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    draft_season_id integer;
BEGIN
    SELECT season_id FROM drafts WHERE draft_id = _draft_id INTO draft_season_id;
RETURN QUERY
    WITH season_rankings AS (
        SELECT
            teams.team_id,
            rankings.rank,
            teams.name
        FROM
            teams
            LEFT JOIN rankings ON rankings.team_id = teams.team_id
                AND rankings.season_id = draft_season_id
            ORDER BY
                rank
    )
    SELECT r.*, draft_users.name IS NOT NULL AS picked, draft_users.name AS pick_user 
    FROM season_rankings r
    LEFT JOIN picks ON r.team_id = picks.team_id AND picks.draft_id = _draft_id
    LEFT JOIN draft_users ON draft_users.draft_user_id = picks.draft_user_id;
END;
$$;