

CREATE TABLE IF NOT EXISTS public.users
(
id serial,
name character varying(150) DEFAULT NULL::character varying,
mobile_number character varying(25),
email character varying(255) DEFAULT NULL::character varying,
is_agreed_tandc smallint DEFAULT 0,
is_above_legal_drinking_age smallint DEFAULT 0,
is_agree_contacted smallint DEFAULT 0,
referred_by bigint DEFAULT NULL,
referral_code character varying(20) DEFAULT NULL::character varying,
referrer_code character varying(20) DEFAULT NULL::character varying,
claimed_unique_code character varying(20) DEFAULT NULL::character varying,
claimed_brand_id integer,
claimed_brand_unique_code character varying(255) DEFAULT NULL::character varying,
status smallint DEFAULT 1,
createdat timestamp without time zone,
updatedat timestamp without time zone,
PRIMARY KEY (id),
CONSTRAINT mobile_key UNIQUE (mobile_number),
CONSTRAINT referrer_code_key UNIQUE (referrer_code)
)
WITH (
OIDS = FALSE
);

COMMENT ON COLUMN users.status
IS '1->Active, 2->Blocked';


CREATE TABLE IF NOT EXISTS public.game
(
id serial,
game_name character varying(100),
game_desc text,
user_game_capping integer,
game_url text,
PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
);


INSERT INTO "game" ("game_name", "game_desc", "user_game_capping", "game_url") VALUES ('Heineken Silververse', 'Heineken Silververse', 3, 'https://heinekensilververse.arweb.app/?zid=z/IpRo1c&rs=&menu=&toolbar=&_vids=2');





CREATE TABLE public.messages
(
    id serial,
    createdon timestamp(0) without time zone NOT NULL,
    fromid character varying(50) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    toid character varying(50) COLLATE pg_catalog."default" NOT NULL,
    sub character varying(250) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    body text COLLATE pg_catalog."default" NOT NULL,
    msgtype smallint NOT NULL,
    senton timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
    status smallint NOT NULL,
    campaign_id integer,
    program_id integer,
    template_id text COLLATE pg_catalog."default",
    CONSTRAINT messages_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)


TABLESPACE pg_default;



-- DROP INDEX public.msg_campaign_id;

CREATE INDEX msg_campaign_id
    ON public.messages USING btree
    (campaign_id ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: msgtypeidx

-- DROP INDEX public.msgtypeidx;

CREATE INDEX msgtypeidx
    ON public.messages USING btree
    (msgtype ASC NULLS LAST, status ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: senton

-- DROP INDEX public.senton;

CREATE INDEX senton
    ON public.messages USING btree
    (senton ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: toid

-- DROP INDEX public.toid;

CREATE INDEX toid
    ON public.messages USING btree
    (toid COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;





CREATE TABLE IF NOT EXISTS public.silver_promo_unique_codes
(
id serial,
promo_code_type character varying(20) DEFAULT NULL::character varying,
promo_unique_codes character varying(20) DEFAULT NULL::character varying,
created_at timestamp without time zone,
status smallint DEFAULT 0,
verified_at timestamp without time zone,
mobile_number character varying(20) DEFAULT NULL::character varying,
PRIMARY KEY (id),
CONSTRAINT promo_unique_codes_key UNIQUE (promo_unique_codes)
)
WITH (
OIDS = FALSE
);

COMMENT ON COLUMN silver_promo_unique_codes.status
IS '0->Pending, 1->Verified';



CREATE TABLE IF NOT EXISTS public.promo_brands
(
id serial,
brand_name character varying(255) DEFAULT NULL::character varying,
image_path text,
status smallint DEFAULT 1,
template_id text COLLATE pg_catalog."default",
template text,
template_action character varying(20) DEFAULT NULL::character varying,
created_at timestamp without time zone,
PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
);

COMMENT ON COLUMN promo_brands.status
IS '0->Inactive, 1->Active';


CREATE TABLE IF NOT EXISTS public.silver_promo_brand_codes
(
id serial,
brand_id integer,
promo_brand_unique_codes character varying(255) DEFAULT NULL::character varying,
created_at timestamp without time zone,
status smallint DEFAULT 0,
redeemed_at timestamp without time zone,
mobile_number character varying(20) DEFAULT NULL::character varying,
PRIMARY KEY (id),
CONSTRAINT promo_brand_unique_codes_key UNIQUE (promo_brand_unique_codes)
)
WITH (
OIDS = FALSE
);

COMMENT ON COLUMN silver_promo_brand_codes.status
IS '0->Pending, 1->redeemed';



CREATE TABLE IF NOT EXISTS public.user_brand_code_mapping
(
id serial,
brand_id integer,
user_brand_unique_codes character varying(255) DEFAULT NULL::character varying,
created_at timestamp without time zone,
status  smallint DEFAULT 1,
mobile_number character varying(20) DEFAULT NULL::character varying,
PRIMARY KEY (id),
CONSTRAINT user_brand_unique_codes_key UNIQUE (user_brand_unique_codes),
CONSTRAINT user_mob_key UNIQUE (mobile_number)
)
WITH (
OIDS = FALSE
);

COMMENT ON COLUMN user_brand_code_mapping.status
IS '0->Pending, 1->redeemed';

INSERT INTO "promo_brands" ("id", "brand_name", "image_path", "status", "template_id", "template", "template_action", "created_at") VALUES (1, 'Delicious Treats', 'https://gameapi.heineken.silververse.bigcityvoucher.co.in/brands/swiggy.png', 1, '1707166219024470933', 'Your Rs. 75 Swiggy Code is {{RewardCode}}. To redeem: Login to Swiggy App > Add items to Cart > On the Cart page, enter code under Apply Coupon section. The discount will be applied to your order. For T&C visit bigcityexperience.com. For queries, email us on feedback@bigcity.in. Team BigCity', 'swiggy', NULL);
INSERT INTO "promo_brands" ("id", "brand_name", "image_path", "status", "template_id", "template", "template_action", "created_at") VALUES (2, 'Cab Trips', 'https://gameapi.heineken.silververse.bigcityvoucher.co.in/brands/uber.png', 1, '1707160404035437900', 'Success! Click  {{RewardCode}}   to claim your Rs. 75 Uber Voucher. You need to sign in to your Uber App to redeem the voucher. For queries, email us at feedback@bigcity.in. Team BigCity', 'uber', NULL);
INSERT INTO "promo_brands" ("id", "brand_name", "image_path", "status", "template_id", "template", "template_action", "created_at") VALUES (3, 'Movie/event Tix', 'https://gameapi.heineken.silververse.bigcityvoucher.co.in/brands/book_my_show.png', 1, '1707164932602509246', 'Your Rs. 75 Book my show Code is {{RewardCode}}. To redeem: login to BookMyShow app > Make your booking > On the Booking Confirmation page > Click Unlock Offers or Apply Promo Codes. The discount will be applied on your booking. For T&C visit bigcityexperience.com. For queries, email us on feedback@bigcity.in Team BigCity', 'book_my_show', NULL);

ALTER TABLE user_brand_code_mapping DROP CONSTRAINT user_mob_key;
CREATE INDEX user_mob_key_id ON public.user_brand_code_mapping USING btree (mobile_number);
CREATE INDEX user_mob_promo_id ON public.silver_promo_unique_codes USING btree (mobile_number);



ALTER TABLE users DROP COLUMN claimed_unique_code;
ALTER TABLE users DROP COLUMN claimed_brand_id;
ALTER TABLE users DROP COLUMN claimed_brand_unique_code;


ALTER TABLE silver_promo_brand_codes DROP COLUMN redeemed_at;
ALTER TABLE silver_promo_brand_codes DROP COLUMN mobile_number;


ALTER TABLE user_brand_code_mapping 
ADD column claimed_unique_code character varying(100) DEFAULT NULL::character varying;




 CREATE TABLE IF NOT EXISTS public.share_social_media
        (
            id serial,
            player_id integer,
            sharedat timestamp without time zone,
            status smallint DEFAULT 1,          
            PRIMARY KEY (id)
        )
        WITH (
            OIDS = FALSE
        );

        COMMENT ON COLUMN share_social_media.status
        IS '0->Inactive, 1->Active';


drop table winner_list;
        CREATE TABLE IF NOT EXISTS public.winner_list
(
id serial,
sl_no integer,
name character varying(255) DEFAULT NULL::character varying,
reward character varying(255) DEFAULT NULL::character varying,
created_at timestamp without time zone,
status smallint DEFAULT 1,          
PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
);