CREATE OR REPLACE PROCEDURE enable_platform_user (tid VARCHAR(32))
AS $$
BEGIN
    UPDATE platform_user SET enabled = TRUE
    WHERE platform_user.id = tid;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_new_platform_user(tid VARCHAR(32), trole VARCHAR(50))
AS $$
BEGIN
    INSERT INTO platform_user(id, role, enabled, created_at)
    VALUES (tid, trole, FALSE, localtimestamp);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_user_lang(tuser_id VARCHAR(32), tlang VARCHAR(2))
AS $$
BEGIN
    INSERT INTO user_lang(user_id, lang)
    VALUES (tuser_id, tlang);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE create_user_profile_with_activation(
    tuser_id VARCHAR(32),
    temail          VARCHAR(200),
    tfirst_name     VARCHAR(256),
    tlast_name      VARCHAR(256),
    tgender         VARCHAR(10),
    tbirth_day      DATE,
    tcountry        VARCHAR(2)
) AS $$
BEGIN
    INSERT INTO user_profile(user_id, email, first_name, last_name, gender, birth_day, country)
    VALUES (tuser_id, temail, tfirst_name, tlast_name, tgender, tbirth_day, tcountry);
    CALL enable_platform_user(tuser_id);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_requester(tid VARCHAR(32))
AS $$
BEGIN
    INSERT INTO requester(user_id, created_at)
    VALUES (tid, localtimestamp);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_worker(tid VARCHAR(32), teducation VARCHAR(10))
AS $$
BEGIN
    INSERT INTO worker(user_id, education, created_at)
    VALUES (tid, teducation, localtimestamp);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE create_project(
    tid                            BIGINT,
    trequester_id                  VARCHAR(32),
    tname                          VARCHAR(256),
    tdescription                   TEXT,
    tinstructions                  TEXT,
    tfail_limit                    INT,
    ttime_limit                    INT
) AS $$
BEGIN
    INSERT INTO project(id, requester_id, name, description, instructions, status, fail_limit, time_limit, created_at)
    VALUES (tid, trequester_id, tname, tdescription, tinstructions, 'ACTIVE', tfail_limit, ttime_limit, localtimestamp);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION f() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO
        worker_balance(user_id)
        VALUES (new.user_id);

    RETURN new;
END;
$BODY$
language plpgsql;


CREATE TRIGGER init_worker_balance
     AFTER INSERT ON worker
     FOR EACH ROW
     EXECUTE PROCEDURE f();

CREATE OR REPLACE PROCEDURE create_skill(
    tid                 BIGINT       ,
    trequester_id       VARCHAR(32)  ,
    tname               VARCHAR(256)
) AS $$
BEGIN
    INSERT INTO skill(id, requester_id, name, created_at)
    VALUES (tid, trequester_id, tname, localtimestamp);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_worker_skill(
    tid                 BIGINT      ,
    tworker_id          VARCHAR(32) ,
    tskill_id           BIGINT   ,
    tvalue              INT
) AS $$
BEGIN
    INSERT INTO worker_skill(id, worker_id, skill_id, value)
    VALUES (tid, tworker_id, tskill_id, tvalue);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE create_pool(
    tid                          BIGINT         ,
    tproject_id                  BIGINT         ,
    tname                        VARCHAR(255)   ,
    tprice                       NUMERIC(15, 2) ,
    ttasks_count                 BIGINT
) AS $$
BEGIN
    INSERT INTO pool(id, project_id, name, price, tasks_count)
    VALUES (tid, tproject_id, tname, tprice, ttasks_count);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_pool_lang(tpool_id BIGINT, tlang VARCHAR(2))
AS $$
BEGIN
    INSERT INTO pool_lang(pool_id, lang)
    VALUES (tpool_id, tlang);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE add_pool_skill(
    tid        BIGINT   ,
    tpool_id   BIGINT   ,
    tskill_id  BIGINT   ,
    tthreshold INT
) AS $$
BEGIN
    INSERT INTO pool_skills(id, pool_id, skill_id, threshold)
    VALUES (tid, tpool_id, tskill_id, tthreshold);
END;
$$ LANGUAGE plpgsql;


-- GET POOLS FOR USER

SELECT *
FROM pool
WHERE
    (SELECT count(*) FROM pool_lang WHERE pool_lang.pool_id = pool.id) =
    (SELECT count(*)
        FROM pool_lang
        WHERE pool_lang.pool_id = pool.id
        AND pool_lang.lang IN (SELECT lang FROM user_lang WHERE user_lang.user_id = '2'))
    AND
    (SELECT count(*) FROM pool_skills WHERE pool_skills.pool_id = pool.id) =
    (SELECT count(*)
        FROM pool_skills
        WHERE pool_skills.pool_id = pool.id
        AND (SELECT count(*) from worker_skill WHERE worker_skill.worker_id = '2'
            AND worker_skill.skill_id = pool_skills.skill_id
            AND worker_skill.value < pool_skills.threshold) = 0)
;