CREATE OR REPLACE PACKAGE BODY user_connector AS
  PROCEDURE get_user(p_username IN VARCHAR2, p_out_cursor OUT sys_refcursor)
  AS
  BEGIN
    OPEN p_out_cursor FOR SELECT * FROM USER_PROFILE
                          WHERE username = p_username;
  END get_user;

  PROCEDURE get_user_by_id(p_id IN NUMBER, p_out_cursor OUT sys_refcursor)
  AS
  BEGIN
    OPEN p_out_cursor FOR SELECT * FROM USER_PROFILE WHERE USER_ID = p_id;
  END get_user_by_id;

  PROCEDURE get_users_money(p_user_id IN NUMBER, p_out_cursor OUT sys_refcursor)
  AS
  BEGIN
    OPEN p_out_cursor FOR SELECT Money FROM User_Profile WHERE User_ID = p_user_id;
  END get_users_money;

  PROCEDURE add_money_for_user(p_user_id IN NUMBER, p_money IN NUMBER, p_out_cursor OUT sys_refcursor)
  AS
    v_money NUMBER;
  BEGIN
    SELECT MONEY INTO v_money FROM USER_PROFILE
      WHERE USER_ID = p_user_id;

    v_money := v_money + p_money;

    UPDATE User_Profile SET money=v_money WHERE User_ID=p_user_id;
    COMMIT;
  END add_money_for_user;

  PROCEDURE authenticate(p_username IN VARCHAR2,
                         p_password IN VARCHAR2,
                         p_out_cursor OUT sys_refcursor)
  AS
  BEGIN
    OPEN p_out_cursor FOR SELECT * FROM USER_PROFILE
                          WHERE USERNAME = p_username
                          AND PASSWORD = p_password;
  END authenticate;

  PROCEDURE registration(p_username IN VARCHAR2,
                         p_password IN VARCHAR2,
                         p_conf IN VARCHAR2,
                         p_out_cursor OUT sys_refcursor)
  AS
    v_count NUMBER;
  BEGIN
    IF p_password = p_conf THEN
      SELECT COUNT(*) INTO v_count FROM USER_PROFILE WHERE USERNAME = p_username;
      IF v_count != 0 THEN
        INSERT INTO USER_PROFILE(USERNAME, PASSWORD, GROUP_ID, IS_ACTIVE)
                    VALUES(p_username, p_password, 2, 1);
        COMMIT;
      END IF;
    END IF;

    OPEN p_out_cursor FOR SELECT * FROM User_Profile
                          WHERE username = p_username
                          AND password = p_password;
  END registration;

  PROCEDURE fetch_all_users( p_out_cursor OUT sys_refcursor)
  AS
  BEGIN
    OPEN p_out_cursor FOR SELECT * FROM USER_PROFILE;
  END fetch_all_users;

END user_connector;
/