gkv0013

-- Table: public.users

-- DROP TABLE IF EXISTS public.users;

CREATE TABLE IF NOT EXISTS public.users ( id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass), telegramid bigint NOT NULL, username character varying(255) COLLATE pg_catalog."default", firstname character varying(255) COLLATE pg_catalog."default" NOT NULL, lastname character varying(255) COLLATE pg_catalog."default" NOT NULL, created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, last_login timestamp without time zone, profit_per_tap numeric(10,2) NOT NULL, profit_per_hour numeric(10,2) NOT NULL, button_press_count integer NOT NULL DEFAULT 0, total_coins bigint NOT NULL DEFAULT 0, friends_invited integer NOT NULL DEFAULT 0, referral_bonus numeric(10,2) NOT NULL DEFAULT 0.00, daily_login_streak integer NOT NULL DEFAULT 0, CONSTRAINT users_pkey PRIMARY KEY (id), CONSTRAINT users_telegramid_key UNIQUE (telegramid) )

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.users OWNER to postgres;

-- FUNCTION: public.insert_user(bigint, character varying, character varying, numeric, numeric, character varying, timestamp without time zone, integer, bigint, integer, numeric, integer)

-- DROP FUNCTION IF EXISTS public.insert_user(bigint, character varying, character varying, numeric, numeric, character varying, timestamp without time zone, integer, bigint, integer, numeric, integer);

CREATE OR REPLACE FUNCTION public.insert_user( p_telegramid bigint, p_firstname character varying, p_lastname character varying, p_profit_per_tap numeric, p_profit_per_hour numeric, p_username character varying DEFAULT NULL::character varying, p_last_login timestamp without time zone DEFAULT NULL::timestamp without time zone, p_button_press_count integer DEFAULT 0, p_total_coins bigint DEFAULT 0, p_friends_invited integer DEFAULT 0, p_referral_bonus numeric DEFAULT 0.00, p_daily_login_streak integer DEFAULT 0) RETURNS integer LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE v_id INTEGER; BEGIN INSERT INTO users ( telegramid, username, firstname, lastname, created_at, last_login, profit_per_tap, profit_per_hour, button_press_count, total_coins, friends_invited, referral_bonus, daily_login_streak ) VALUES ( p_telegramid, p_username, p_firstname, p_lastname, CURRENT_TIMESTAMP, p_last_login, p_profit_per_tap, p_profit_per_hour, p_button_press_count, p_total_coins, p_friends_invited, p_referral_bonus, p_daily_login_streak ) RETURNING id INTO v_id;

RETURN v_id;  -- Return the auto-generated ID

END; $BODY$;

ALTER FUNCTION public.insert_user(bigint, character varying, character varying, numeric, numeric, character varying, timestamp without time zone, integer, bigint, integer, numeric, integer) OWNER TO postgres; -- FUNCTION: public.update_user(integer, bigint, timestamp without time zone, bigint)

-- DROP FUNCTION IF EXISTS public.update_user(integer, bigint, timestamp without time zone, bigint);

CREATE OR REPLACE FUNCTION public.update_user( p_mode integer, p_telegramid bigint, p_last_login timestamp without time zone DEFAULT NULL::timestamp without time zone, p_total_coins bigint DEFAULT NULL::bigint) RETURNS boolean LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE v_exists BOOLEAN; BEGIN CASE p_mode WHEN 0 THEN -- Mode 0: Update last_login based on telegramid UPDATE users SET last_login = COALESCE(p_last_login, CURRENT_TIMESTAMP) WHERE telegramid = p_telegramid; RETURN TRUE;

    WHEN 1 THEN
        -- Mode 1: Update total_coins based on telegramid
        UPDATE users
        SET total_coins = COALESCE(p_total_coins, total_coins)
        WHERE telegramid = p_telegramid;
        RETURN TRUE;

    WHEN 2 THEN
        -- Mode 2: Update both last_login and total_coins based on telegramid
        UPDATE users
        SET last_login = COALESCE(p_last_login, CURRENT_TIMESTAMP),
            total_coins = COALESCE(p_total_coins, total_coins)
        WHERE telegramid = p_telegramid;
        RETURN TRUE;
		
    WHEN 3 THEN
        -- Mode 3: Check if telegramid exists in the users table
        SELECT EXISTS (
            SELECT 1 FROM users WHERE telegramid = p_telegramid
        ) INTO v_exists;
        RETURN v_exists;
    ELSE
        -- Invalid mode
        RAISE EXCEPTION 'Invalid mode: %', p_mode;
        RETURN FALSE;
END CASE;

END; $BODY$;

ALTER FUNCTION public.update_user(integer, bigint, timestamp without time zone, bigint) OWNER TO postgres; -- FUNCTION: public.user_login(integer, bigint, character varying, character varying, character varying, numeric, numeric, bigint, integer, numeric, integer, timestamp without time zone, refcursor)

-- DROP FUNCTION IF EXISTS public.user_login(integer, bigint, character varying, character varying, character varying, numeric, numeric, bigint, integer, numeric, integer, timestamp without time zone, refcursor);

CREATE OR REPLACE FUNCTION public.user_login( p_mode integer, p_telegramid bigint, p_username character varying, p_firstname character varying, p_lastname character varying, p_profit_per_tap numeric, p_profit_per_hour numeric, p_total_coins bigint, p_friends_invited integer, p_referral_bonus numeric, p_daily_login_streak integer, p_lastlogin timestamp without time zone, ref refcursor) RETURNS refcursor LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE v_user RECORD; BEGIN -- Check if user exists by telegramid SELECT * INTO v_user FROM users WHERE telegramid = p_telegramid;

-- Mode 0: Insert new user if not found
IF p_mode = 0 THEN
    IF NOT FOUND THEN
        INSERT INTO users (telegramid, username, firstname, lastname, created_at, last_login, profit_per_tap, profit_per_hour, total_coins, friends_invited, referral_bonus, daily_login_streak)
        VALUES (p_telegramid, p_username, p_firstname, p_lastname, CURRENT_TIMESTAMP, p_lastLogin, p_profit_per_tap, p_profit_per_hour, p_total_coins, p_friends_invited, p_referral_bonus, p_daily_login_streak)
        RETURNING * INTO v_user;
       ELSE
        -- If user is found, update the last_login
        UPDATE users
        SET last_login = p_lastLogin
        WHERE telegramid = p_telegramid;
    END IF;

    -- Open the refcursor to return the user information
    OPEN ref FOR SELECT * FROM users WHERE telegramid = p_telegramid;

    -- Return the refcursor
    RETURN ref;

-- Mode 1: Update profit_per_tap, profit_per_hour, total_coins, and last_login if user exists
ELSIF p_mode = 1 THEN
    IF FOUND THEN
        UPDATE users
        SET profit_per_tap = p_profit_per_tap,
            profit_per_hour = p_profit_per_hour,
            total_coins = p_total_coins,
            last_login = p_lastLogin
        WHERE telegramid = p_telegramid;
    ELSE
        RAISE EXCEPTION 'User with telegramid % not found.', p_telegramid;
    END IF;
ELSE
    RAISE EXCEPTION 'Invalid mode value. Mode must be 0 or 1.';
END IF;

-- In mode 1, do not return the refcursor
RETURN null;

END; $BODY$;

ALTER FUNCTION public.user_login(integer, bigint, character varying, character varying, character varying, numeric, numeric, bigint, integer, numeric, integer, timestamp without time zone, refcursor) OWNER TO postgres;