WIP DB setup

sudo apt-get install postgresql postgresql-contrib
sudo -i -u postgres
postgres@ip-172-31-8-245:~$ createuser --interactive
Enter name of role to add: eventyzeruser
Shall the new role be a superuser? (y/n) y
psql
alter user eventyzeruser password 'eventyzerpassword';
GRANT ALL PRIVILEGES ON DATABASE "eventyzerdb" TO eventyzeruser;
\q

# Don't forget to chown of your working directory to the main machine user instead of root
sudo nano /etc/postgresql/9.5/main/pg_hba.conf                          (peer to md5)
sudo service postgresql restart
sudo cp db_setup.sql /var/lib/postgresql/
sudo -iu postgres psql -U eventyzeruser -d eventyzerdb -a -f /var/lib/postgresql/db_setup.sql

#CREATE USER eventyzeruser WITH PASSWORD 'eventyzerpassword';
#/Applications/Postgres.app/Contents/Versions/9.4/bin/createdb eventyzerdb
#GRANT ALL PRIVILEGES ON DATABASE "eventyzerdb" TO eventyzeruser;
sudo -i -u postgres
psql
\c eventyzerdb eventyzeruser # use password eventyzerpassword

CREATE TABLE "public"."user" (
    "id" serial,
    "twitterId" text NOT NULL,
    "twitterHandle" text NOT NULL,
    "email" text,
    "deafultConfig" text,
    PRIMARY KEY ("id"),
    UNIQUE ("twitterId"),
    UNIQUE ("twitterHandle"),
    UNIQUE ("email")
);

ALTER TABLE "public"."user" ADD COLUMN "active" boolean NOT NULL DEFAULT 'FALSE';

ALTER TABLE "public"."user"
  ADD COLUMN "firstName" text NOT NULL,
  ADD COLUMN "lastName" text;


GRANT ALL PRIVILEGES ON TABLE public.user TO eventyzeruser;
GRANT ALL PRIVILEGES ON SEQUENCE public.user_id_seq TO eventyzeruser;


CREATE TABLE "public"."email_activation" (
    "id" serial,
    "email" text NOT NULL,
    "code" text NOT NULL,
    PRIMARY KEY ("id"),
    UNIQUE ("email")
);

GRANT ALL PRIVILEGES ON TABLE public.email_activation TO eventyzeruser;
GRANT ALL PRIVILEGES ON SEQUENCE public.email_activation_id_seq TO eventyzeruser;

CREATE TABLE "public"."user_events" (
    "id" serial,
    "twitter_user_id" text NOT NULL,
    "uuid" text NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("twitter_user_id") REFERENCES "public"."user"("twitterId") ON DELETE CASCADE
);

GRANT ALL PRIVILEGES ON TABLE public.user_events TO eventyzeruser;
GRANT ALL PRIVILEGES ON SEQUENCE public.user_events_id_seq TO eventyzeruser;

CREATE TABLE "public"."user_event_defaults" (
    "id" serial,
    "bg_colour" text NOT NULL,
    "screens_j_array" text NOT NULL,
    "screen_time_j_array" text NOT NULL,
    PRIMARY KEY ("id")
);

GRANT ALL PRIVILEGES ON TABLE public.user_event_defaults TO eventyzeruser;
GRANT ALL PRIVILEGES ON SEQUENCE public.user_event_defaults_id_seq TO eventyzeruser;

ALTER TABLE "public"."user_event_defaults"
  ADD COLUMN "screen_size" text NOT NULL,
  ADD COLUMN "hashtags_j_array" text NOT NULL,
  ADD COLUMN "moderated" boolean NOT NULL;


ALTER TABLE "public"."user_events" ADD UNIQUE ("uuid");

ALTER TABLE "public"."user_event_defaults"
  ADD COLUMN "uuid" text NOT NULL,
  ADD FOREIGN KEY ("uuid") REFERENCES "public"."user_events"("uuid") ON DELETE CASCADE;

ALTER TABLE "public"."user_event_defaults"
  DROP COLUMN "hashtags_j_array",
  DROP COLUMN "uuid";

ALTER TABLE "public"."user_event_defaults"
  ADD COLUMN "twitter_id" text NOT NULL,
  ADD FOREIGN KEY ("twitter_id") REFERENCES "public"."user"("twitterId") ON DELETE CASCADE;

\q # quit psql