-- Table: public.vouchers

-- DROP TABLE public.vouchers;

CREATE TABLE public.vouchers
(
  id serial NOT NULL ,
  campaign_id integer,
  code character varying(25) NOT NULL,
  status integer NOT NULL,
  reference_code character varying(1000) DEFAULT NULL::character varying,
  usedon timestamp without time zone,
  reference_code_created timestamp without time zone,
  reference_code_expired timestamp without time zone,
  CONSTRAINT vouchers_pkey PRIMARY KEY (id),
  CONSTRAINT reference_code UNIQUE (reference_code)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.vouchers
  OWNER TO postgres;


-- Table: public.campaigns

-- DROP TABLE public.campaigns;

CREATE TABLE public.campaigns
(
  id serial NOT NULL ,
  title character varying(100) NOT NULL,
  regtext text,
  contactno character varying(15) NOT NULL,
  txtcontact text,
  txtterms text,
  sucessmsg character varying(1000) DEFAULT NULL::character varying,
  startdate date,
  enddate date,
  sitebanner character varying(50),
  status integer,
  created_date timestamp without time zone,
  updated_date timestamp without time zone,
  total_attempt integer,
  privacy_policy text,
  CONSTRAINT campaigns_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.campaigns
  OWNER TO postgres;


-- Table: public.messages

-- DROP TABLE public.messages;

CREATE TABLE public.messages
(
  id serial NOT NULL,
  createdon timestamp(0) without time zone NOT NULL,
  fromid character varying(50) DEFAULT NULL::character varying,
  toid character varying(50) NOT NULL,
  sub character varying(250) DEFAULT NULL::character varying,
  body character varying(1500) 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,
  CONSTRAINT messages_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.messages
  OWNER TO postgres;



-- Table: public.customer

-- DROP TABLE public.customer;

CREATE TABLE public.customer
(
  id serial NOT NULL ,
  created_date timestamp(0) without time zone DEFAULT NULL::timestamp without time zone,
  campaign_id integer,
  access_token character varying(1000),
  access_token_expiry timestamp without time zone,
  access_token_created timestamp without time zone,
  access_token_updated timestamp without time zone,
  updated_date timestamp without time zone,
  fp character varying(255),
  uuid character varying(255),
  total_attempts integer,
  CONSTRAINT customer_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.customer
  OWNER TO postgres;


-- Table: public.customer_data

-- DROP TABLE public.customer_data;

CREATE TABLE public.customer_data
(
  id serial NOT NULL ,
  customer_id integer NOT NULL,
  customername character varying(50),
  email character varying(50),
  mobile character varying(50),
  created_date timestamp without time zone,
  ipaddress character varying(50),
  otp character varying(25),
  city_id integer,
  lat character varying(50),
  lng character varying(50),
  status integer,
  internal_status integer DEFAULT 0,
  agent_comments text,
  agent_comments_date timestamp without time zone,
  campaign_id integer,
  updated_date timestamp without time zone,
  CONSTRAINT customer_data_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.customer_data
  OWNER TO postgres;

  -- Table: public.rewards

  -- DROP TABLE public.rewards;

  CREATE TABLE public.rewards
  (
    id serial NOT NULL ,
    prod_name character varying(100) NOT NULL,
    value numeric(42,0) NOT NULL,
    status smallint NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    imgurl character varying(255),
    CONSTRAINT rewards_pkey PRIMARY KEY (id)
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE public.rewards
    OWNER TO postgres;

  ALTER TABLE public.customer_data
      ADD COLUMN reward_id integer;

  ALTER TABLE public.customer_data
  ADD COLUMN voucher_code character varying(50);

  ALTER TABLE public.customer_data
   ALTER COLUMN customer_id DROP NOT NULL;

  ALTER TABLE public.vouchers
   ALTER COLUMN code TYPE character varying(50);

   CREATE TABLE public.sentiment_reward_config
(
   id serial,
   reward_name character varying(150),
   rewad_desc text,
   emotions character varying(150),
   emotions_rule text,
   status smallint DEFAULT 1,
   PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
;

CREATE TABLE public.sentiment_analysis_data
(
   id serial,
   campaign_id integer,
   no_of_posts_anlaysed bigint,
   origin_post_from character varying(150),
   created_timestamp timestamp without time zone,
   cumulative_positive_score numeric(15,2),
   cumulative_negative_score numeric(15,2),
   PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
;

ALTER TABLE public.customer_data
  ADD COLUMN sentiment_analysis_result numeric(15,2);
ALTER TABLE public.customer_data
  ADD COLUMN sentiment_reward_config_id integer;
ALTER TABLE public.sentiment_analysis_data
    ADD COLUMN cumulative_compound_score numeric(15,2);

    CREATE TABLE public.socialwall_config
    (
       id serial,
       wall_id bigint,
       wall_name character varying(150),
       hastags json[],
       created_date timestamp without time zone,
       updated_date timestamp without time zone,
       status smallint DEFAULT 1,
       PRIMARY KEY (id)
    )
    WITH (
      OIDS = FALSE
    )
    ;
    ALTER TABLE public.sentiment_analysis_data
      ADD COLUMN wall_id bigint;

  ALTER TABLE public.sentiment_analysis_data
    ADD COLUMN customer_id integer;

    ALTER TABLE public.sentiment_analysis_data
  ADD COLUMN status smallint DEFAULT 0;
