core-db.sql

CREATE OR REPLACE FUNCTION update_user_status_with_old_status_checked(user_id_ integer, status_old_ status, status_new_ status, check_old_status_ boolean)
      RETURNS SETOF lon_lat_user AS
$$
DECLARE
BEGIN
      RETURN QUERY
            SELECT (user_to_lon_lat_user(users_full.*, 0)).*
                  FROM users u
                  JOIN users_full ON users_full.id = u.id
                  WHERE u.id = user_id_
                  AND CASE WHEN check_old_status_ THEN u.status = status_old_ ELSE true END
                  FOR UPDATE OF u;

      IF FOUND THEN
            UPDATE users
                  SET status = status_new_,
                  updated_time = current_timestamp AT time zone 'UTC'
                  WHERE id = user_id_ AND status NOT IN ('inactivated', 'deleted');

            IF FOUND THEN
                  RETURN QUERY
                        SELECT (user_to_lon_lat_user(users_full.*, 0)).*
                        FROM users_full
                        WHERE id = user_id_;
            END IF;
      END IF;
END
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION find_whether_user_is_reported(user_id_ integer)
RETURNS SETOF BOOLEAN AS
$$
DECLARE
BEGIN
      RETURN QUERY
        SELECT EXISTS (SELECT * FROM reported_users WHERE user_id = user_id_ AND status = 'default');

END
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION select_user_ids_sharing_same_main_picture(user_id_ integer) RETURNS integer[] AS
$$
DECLARE
    uids_ integer[];
BEGIN
      WITH shared_picture_uids AS (
          SELECT p2.user_id
                  FROM user_pictures p1
                  JOIN user_pictures p2
                  ON p1.user_id != p2.user_id
                  AND p1.picture_dhash != 0
                  AND p1.status != 'deleted'
                  AND p1.index = 0
                  AND p1.picture_dhash = p2.picture_dhash
                  WHERE p1.user_id = user_id_
                  LIMIT 20 -- protection against pictures shared among too many users
      ), to_update as (
          SELECT * FROM shared_picture_uids
          UNION
          SELECT user_id_ WHERE EXISTS (SELECT 1 FROM shared_picture_uids)
      )
      SELECT ARRAY_AGG(user_id) INTO uids_ FROM to_update;
      RETURN uids_;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION select_whether_user_updated_gender(user_id_ integer) RETURNS BOOLEAN AS
$$
DECLARE
    res_ boolean;
BEGIN
    SELECT EXISTS INTO res_(
        SELECT 1
        FROM suspicious_user_events
        WHERE   user_id = user_id_
            AND status = 'default'
            AND event = 'user.updatedGenderCount'
            AND score > 0
    );
    RETURN res_;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION select_whether_user_suspicious(user_id_ integer) RETURNS BOOLEAN AS
$$
DECLARE
    res_ boolean;
BEGIN
    SELECT EXISTS INTO res_(
        SELECT 1
            FROM suspicious_user_events e
            JOIN users u ON u.id = e.user_id
            WHERE   e.event IN ('user.jailbroken', 'user.suspiciousAndroidDevice')
                AND e.user_id = user_id_
                AND e.status = 'default'
                AND u.gender = 'female'
    );
    RETURN res_;
END;
$$ LANGUAGE PLPGSQL;