Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users
(user_id BIGSERIAL PRIMARY KEY NOT NULL,
name VARCHAR(256) NOT NULL,
email VARCHAR(256) NOT NULL,
password VARCHAR(512),
active_role  VARCHAR(256) NOT NULL,
allowed_roles VARCHAR(256)[],
is_verified BOOLEAN DEFAULT FALSE,  --expert only
rating FLOAT DEFAULT 0,
candidate_rating FLOAT DEFAULT 0,
--balance FLOAT DEFAULT 0 CHECK(balance >= 0), 
balance FLOAT DEFAULT 0, --for testing purposes removed constraint TODO: get constaint back, rock111
is_promoted BOOLEAN, --expert only
interviews_conducted INT DEFAULT 0, --expert only
interviews_taken INT DEFAULT 0, -- caniddate only
--These are work values, which are used by us and not displayed to the user
total_score INT DEFAULT 0, --experts only(for easier rating calculation)
total_candidate_score INT DEFAULT 0, --candidates onlu(for easier rating calculation)
on_moderation BOOLEAN,
onboarded BOOLEAN DEFAULT FALSE,
email_validated BOOLEAN DEFAULT FALSE,
validation_str VARCHAR(128),
reset_password_token VARCHAR(128),
reset_expires BIGSERIAL,
registry_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, --expert only
UNIQUE (email));

CREATE TABLE user_settings
(
    settings_id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGSERIAL,
    CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id),
    payment_type VARCHAR(256),
    payment_info VARCHAR(256),
    timeslots VARCHAR(256)[],
    default_timezone BOOLEAN,
    timezone VARCHAR(256),
    notification_settings VARCHAR(256),
    short_bio VARCHAR(1000),
    picture VARCHAR(256),
    biography VARCHAR(2028),
    price FLOAT CHECK (price > 0), --expert only
    phone VARCHAR(256),
    calendar_account VARCHAR(256)
);

CREATE TABLE technologies
(tech_id BIGSERIAL PRIMARY KEY NOT NULL,
technology_name VARCHAR(256) NOT NULL,
popularity INT CHECK (popularity >= 0),
experts_num INT CHECK (experts_num >= 0));

CREATE TABLE users_tech
(    
    user_id BIGSERIAL REFERENCES users (user_id) ON UPDATE CASCADE,
    tech_id BIGSERIAL REFERENCES technologies (tech_id) ON UPDATE CASCADE,
    CONSTRAINT tech_user_pkey PRIMARY KEY (user_id, tech_id)
);

CREATE OR REPLACE PROCEDURE register_user_system(_name VARCHAR(256), _email VARCHAR(256), _password VARCHAR(256), 
    _active_role VARCHAR(256), _rating FLOAT, _allowed_roles VARCHAR(256)[],  _is_verified BOOLEAN = FALSE, _is_promoted BOOLEAN = FALSE, _balance FLOAT = 0) AS $$
    INSERT INTO users (
        name, email, password, active_role, rating, is_verified, is_promoted, balance, allowed_roles
        ) VALUES(
        _name, _email, _password, _active_role, _rating, _is_verified, _is_promoted, _balance, _allowed_roles 
        )
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION register_user_info(VARCHAR(256), VARCHAR(256), VARCHAR(256), VARCHAR(256), VARCHAR(256)) 
RETURNS TABLE (user_id BIGINT, password VARCHAR(512)) AS $$
    INSERT INTO users (name, email, password, active_role, validation_str) VALUES($1, $2, $3, $4, $5)
    RETURNING user_id, password
$$ LANGUAGE SQL;

CREATE OR REPLACE PROCEDURE register_user_settings_candidate(BIGINT, VARCHAR(256)) AS $$
    INSERT INTO user_settings (
        user_id, payment_info, timeslots, timezone, notification_settings, short_bio, picture, biography, phone, calendar_account, default_timezone
    ) VALUES(
        $1, NULL, NULL, $2, NULL, NULL, NULL, NULL, NULL, NULL, TRUE
    )
$$ LANGUAGE SQL;

CREATE OR REPLACE PROCEDURE register_user_settings_expert(BIGINT, VARCHAR(256)) AS $$
    INSERT INTO user_settings (
        user_id, payment_info, timeslots, timezone, notification_settings, short_bio, picture, biography, phone, calendar_account, price, default_timezone
    ) VALUES(
        $1, NULL, NULL, $2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TRUE
    )
$$ LANGUAGE SQL;

CREATE OR REPLACE PROCEDURE add_user_settings_system(_email VARCHAR(256), _timeslots VARCHAR(256)[], _timezone VARCHAR(256), 
    _payment_info VARCHAR(256), _payment_type VARCHAR(256), _picture VARCHAR(256), _price FLOAT, 
    _biography VARCHAR(2028) = NULL, _short_bio VARCHAR(1000) = NULL) AS $$
    INSERT INTO user_settings (
        user_id, timeslots, timezone, payment_info, payment_type, picture, price, biography, short_bio
    ) VALUES(
        (SELECT user_id FROM users WHERE email=_email), _timeslots, _timezone, _payment_info, _payment_type, _picture, _price, _biography, _short_bio
    )
$$ LANGUAGE SQL;

CREATE OR REPLACE PROCEDURE add_tech_info_system(_technology_name VARCHAR(256), _popularity INT, _experts_num INT) AS $$
    INSERT INTO technologies (technology_name, popularity, experts_num) VALUES(_technology_name, _popularity, _experts_num);
$$ LANGUAGE SQL;

CREATE OR REPLACE PROCEDURE add_users_tech_system(_technology_name VARCHAR(256), _email VARCHAR(256)) AS $$
    INSERT INTO users_tech (tech_id, user_id) VALUES(
        (SELECT tech_id FROM technologies WHERE technology_name = _technology_name),
        (SELECT user_id FROM users WHERE email = _email)
    )
$$ LANGUAGE SQL;

CREATE EXTENSION pgcrypto;

CALL register_user_system('Trump', 'trump@gmail.com', crypt('rocker', gen_salt('bf')), 'candidate', 4.5, '{"candidate"}', NULL, NULL, NULL);
CALL register_user_system('Pence', 'pence@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.8, '{"expert"}', TRUE, FALSE, 999);
CALL register_user_system('Pompeo', 'pompeo@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.7, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Jake', 'jake123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Carl', 'carl123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.6, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Juls', 'juls123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.7, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Fin', 'fin123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 3.8, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Helen', 'helen123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.7, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Neformal', 'tada.test.expert1@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.9, '{"expert"}', TRUE, TRUE, 888);

CALL register_user_system('Bryan', 'bryan123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Simone', 'simone123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Lily', 'lily123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Alex', 'alex123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Tim', 'tim123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Lucas', 'lucas123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Michelle', 'michelle123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Stuart', 'stuart123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Timothy', 'timothy123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Eduard', 'eduard123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Angela', 'angela123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Richard', 'richard123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Fernando', 'fernando123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('August', 'august123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Lisa', 'lisa123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('El', 'el123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Liza', 'liza123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Tor', 'tor123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Mon', 'mon123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Rita', 'rita123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Margo', 'margo123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Eliza', 'eliza123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Svyat', 'svyat123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Dima', 'dima123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);
CALL register_user_system('Nicolas', 'nicolas123@gmail.com', crypt('rocker', gen_salt('bf')), 'expert', 4.2, '{"expert"}', TRUE, TRUE, 888);

CALL add_tech_info_system('python', 3, 0);
CALL add_tech_info_system('javascript', 5, 0);

CALL add_users_tech_system('python', 'pence@gmail.com');
CALL add_users_tech_system('python', 'pompeo@gmail.com');
CALL add_users_tech_system('python', 'helen123@gmail.com');
CALL add_users_tech_system('python', 'jake123@gmail.com');
CALL add_users_tech_system('python', 'bryan123@gmail.com');
CALL add_users_tech_system('python', 'lucas123@gmail.com');
CALL add_users_tech_system('python', 'simone123@gmail.com');
CALL add_users_tech_system('python', 'lily123@gmail.com');
CALL add_users_tech_system('python', 'alex123@gmail.com');
CALL add_users_tech_system('python', 'tim123@gmail.com');
CALL add_users_tech_system('python', 'michelle123@gmail.com');
CALL add_users_tech_system('python', 'stuart123@gmail.com');
CALL add_users_tech_system('python', 'eduard123@gmail.com');
CALL add_users_tech_system('python', 'timothy123@gmail.com');
CALL add_users_tech_system('python', 'angela123@gmail.com');
CALL add_users_tech_system('python', 'richard123@gmail.com');
CALL add_users_tech_system('python', 'fernando123@gmail.com');

CALL add_users_tech_system('javascript', 'pence@gmail.com');
CALL add_users_tech_system('javascript', 'juls123@gmail.com');
CALL add_users_tech_system('javascript', 'fin123@gmail.com');
CALL add_users_tech_system('javascript', 'carl123@gmail.com');
CALL add_users_tech_system('javascript', 'helen123@gmail.com');
CALL add_users_tech_system('javascript', 'tada.test.expert1@gmail.com');
CALL add_users_tech_system('javascript', 'august123@gmail.com');
CALL add_users_tech_system('javascript', 'lisa123@gmail.com');
CALL add_users_tech_system('javascript', 'tor123@gmail.com');
CALL add_users_tech_system('javascript', 'mon123@gmail.com');
CALL add_users_tech_system('javascript', 'rita123@gmail.com');
CALL add_users_tech_system('javascript', 'margo123@gmail.com');
CALL add_users_tech_system('javascript', 'eliza123@gmail.com');
CALL add_users_tech_system('javascript', 'svyat123@gmail.com');
CALL add_users_tech_system('javascript', 'dima123@gmail.com');
CALL add_users_tech_system('javascript', 'nicolas123@gmail.com');

CALL add_user_settings_system('pence@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 50,
    'Was a vice-president during Trump admnistration', NULL);

CALL add_user_settings_system('pompeo@gmail.com', '{"1617248230408 Mon 10:00-11:30 n", 
    "1617248230408 Wed 20:00-23:00 n",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT-4', '1111222233334444', 'MasterCard', '', 45,
    'Was secretary of state during Trump admnistration', NULL);

CALL add_user_settings_system('jake123@gmail.com', '{"1617248230408 Mon 10:00-11:30 n", 
    "1617248230408 Wed 20:00-23:00 n",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT-4', '1111222233334444', 'MasterCard', '/assets/images/avatars/Tillman.jpg', 12,
    'Founder and CEO of Smith Digital', NULL);
CALL add_user_settings_system('carl123@gmail.com', '{"1617248230408 Mon 10:00-11:30 n", 
    "1617248230408 Wed 20:00-23:00 n",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT-4', '1111222233334444', 'MasterCard', '/assets/images/avatars/carl.jpg', 25,
    'Consultant at Google', NULL);
CALL add_user_settings_system('juls123@gmail.com', '{"1617248230408 Mon 10:00-11:30 n", 
    "1617248230408 Wed 20:00-23:00 n",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT-4', '1111222233334444', 'MasterCard', '/assets/images/avatars/joyce.jpg', 88,
    'Middle Software Engineer at Amazon', NULL);
CALL add_user_settings_system('fin123@gmail.com', '{"1617248230408 Mon 10:00-11:30 n", 
    "1617248230408 Wed 20:00-23:00 n",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT-4', '1111222233334444', 'MasterCard', '/assets/images/avatars/Shepard.jpg', 250,
    'Senior Software Engineer at Trello ', NULL);
CALL add_user_settings_system('helen123@gmail.com', '{"1617248230408 Mon 10:00-11:30 n", 
    "1617248230408 Wed 20:00-23:00 n",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT-4', '1111222233334444', 'MasterCard', '/assets/images/avatars/Nora.jpg', 56,
    'Middle Software Engineer at Tesla', NULL);
CALL add_user_settings_system('tada.test.expert1@gmail.com', 
'{"1617248230408 Mon 10:00-11:30 n",     "1617248230408 Wed 20:00-23:00 n",
     "1617248230408 Sun 16:50-18:20 M" }', 
     'GMT+2', '1111222233334444', 'MasterCard', '/assets/images/avatars/Nora.jpg', 1,    'Middle Software Engineer at Tesla', NULL);
CALL add_user_settings_system('bryan123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 11,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('simone123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 102,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('lily123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 43,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('alex123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 856,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('tim123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 21,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('lucas123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 34,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('michelle123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 63,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('stuart123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 85,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('timothy123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 12,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('eduard123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 46,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('angela123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 76,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('richard123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 86,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('fernando123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 18,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('august123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 50,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('lisa123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 16,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('el123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 18,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('liza123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 13,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('tor123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 14,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('mon123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 94,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('rita123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 57,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('margo123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 87,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('eliza123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 34,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('svyat123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 50,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('dima123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 73,
    'Was a vice-president during Trump admnistration', NULL);
CALL add_user_settings_system('nicolas123@gmail.com', '{"1617248230408 Mon 10:00-11:30 m", 
    "1617248230408 Wed 20:00-23:00 m",
    "1617248230408 Sun 14:00-15:30 M" }', 'GMT+3', '1111222233334444', 'MasterCard', '/assets/images/avatars/Velazquez.jpg', 21,
    'Was a vice-president during Trump admnistration', NULL);

CREATE OR REPLACE FUNCTION find_experts(BIGINT, _sort_column VARCHAR(256), _sort_direction VARCHAR(256)) 
    RETURNS TABLE (
    user_id BIGINT,
    name VARCHAR(256),
    email VARCHAR(256),
    email_validated BOOLEAN,
    is_verified BOOLEAN,
    rating FLOAT,
    is_promoted BOOLEAN,
    registry_date TIMESTAMP,
    picture VARCHAR(256),
    price FLOAT,
    biography VARCHAR(2028),
    short_bio VARCHAR(1000),
    timeslots VARCHAR(256)[],
    timezone VARCHAR(256)
) AS $$
    SELECT users.user_id, users.name, users.email, users.email_validated, users.is_verified, users.rating, users.is_promoted, users.registry_date,
    user_settings.picture, user_settings.price, user_settings.biography, user_settings.short_bio, user_settings.timeslots, user_settings.timezone
    FROM users 
    LEFT JOIN user_settings ON (users.user_id=user_settings.user_id)
    WHERE users.user_id IN 
    (SELECT user_id FROM users_tech) AND
    active_role='expert' AND price IS NOT NULL
    ORDER BY 
    CASE WHEN _sort_column = 'rating' AND _sort_direction = 'DESC' THEN rating END DESC,
    CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN price END ASC, rating ASC, registry_date ASC,
    CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN price END DESC, rating DESC, registry_date ASC
    OFFSET ($1 - 1) * 10
    LIMIT 10
$$ LANGUAGE SQL;

SELECT * FROM find_experts(1, 'price', 'DESC')

user_id	name	email	email_validated	is_verified	rating	is_promoted	registry_date	picture	price	biography	short_bio	timeslots	timezone
7	Fin	fin123@gmail.com	f	t	3.8	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Shepard.jpg	250	Senior Software Engineer at Trello	null	{"1617248230408 Mon 10:00-11:30 n","1617248230408 Wed 20:00-23:00 n","1617248230408 Sun 14:00-15:30 M"}	GMT-4
13	Alex	alex123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	856	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
11	Simone	simone123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	102	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
28	Mon	mon123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	94	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
30	Margo	margo123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	87	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
21	Richard	richard123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	86	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
17	Stuart	stuart123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	85	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
20	Angela	angela123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	76	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
33	Dima	dima123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	73	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3
16	Michelle	michelle123@gmail.com	f	t	4.2	t	2021-12-15 11:20:02.053967	/assets/images/avatars/Velazquez.jpg	63	Was a vice-president during Trump admnistration	null	{"1617248230408 Mon 10:00-11:30 m","1617248230408 Wed 20:00-23:00 m","1617248230408 Sun 14:00-15:30 M"}	GMT+3