Rdfe09SB

1-- RUN ALL
2-- *************************************************
3--                    Extensions
4-- *************************************************
5
6CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
7
8-- *************************************************
9--                   Char Wolly Tables
10-- *************************************************
11CREATE TABLE IF NOT EXISTS Player(
12	id                      BIGSERIAL PRIMARY KEY,
13	guid                    TEXT                   NOT NULL,
14	facebookId              TEXT			            NULL,
15	userName                TEXT						NULL,
16	profilePic              TEXT						NULL,
17	tokenBalance            INTEGER                     NULL,
18	daily_token_balance		INTEGER						NULL,
19	last_login_timestamp	TIMESTAMP WITH TIME ZONE 	NULL,
20	created_timestamp       TIMESTAMP WITH TIME ZONE NOT NULL,
21	modified_timestamp      TIMESTAMP WITH TIME ZONE NOT NULL
22);
23
24CREATE UNIQUE INDEX IF NOT EXISTS player_guid_idx
25ON player(guid);
26
27CREATE TABLE IF NOT EXISTS GameStatus(
28	id                       BIGSERIAL PRIMARY KEY,
29	guid					 TEXT      NOT NULL,
30	status             		 TEXT	   NOT NULL,
31	created_timestamp        TIMESTAMP WITH TIME ZONE      NOT NULL,
32	modified_timestamp       TIMESTAMP WITH TIME ZONE      NOT NULL
33);
34
35CREATE UNIQUE INDEX IF NOT EXISTS game_status_guid_idx
36ON gameStatus(guid);
37
38CREATE TABLE IF NOT EXISTS Card(
39  id                       BIGSERIAL PRIMARY KEY,
40  guid                     TEXT                 NOT NULL,
41  card               	   TEXT		        NULL,
42
43  created_timestamp        TIMESTAMP WITH TIME ZONE NOT NULL,
44  modified_timestamp       TIMESTAMP WITH TIME ZONE NOT NULL
45);
46
47CREATE UNIQUE INDEX IF NOT EXISTS card_guid_idx
48ON card(guid);
49
50CREATE TABLE IF NOT EXISTS Hand(
51  id                       BIGSERIAL PRIMARY KEY,
52  guid                     TEXT                 NOT NULL,
53
54  playerId                 BIGINT REFERENCES player(id)  NULL,
55  card1               	   BIGINT REFERENCES card(id)  NULL,
56  card2                    BIGINT REFERENCES card(id)  NULL,
57  card3                    BIGINT REFERENCES card(id)  NULL,
58  winner				   BOOLEAN					   NULL,
59  fourOfAKind			   BOOLEAN   				   NULL,	
60
61  created_timestamp        TIMESTAMP WITH TIME ZONE NOT NULL,
62  modified_timestamp       TIMESTAMP WITH TIME ZONE NOT NULL
63);
64
65CREATE UNIQUE INDEX IF NOT EXISTS hand_guid_idx
66ON hand(guid);
67
68CREATE TABLE IF NOT EXISTS Game(
69  id                       BIGSERIAL PRIMARY KEY,
70  guid                     TEXT                 NOT NULL,
71
72  gameStatusId             BIGINT REFERENCES gameStatus(id) NULL, 
73  startTime                TIMESTAMP                NULL,
74
75  created_timestamp        TIMESTAMP WITH TIME ZONE NOT NULL,
76  modified_timestamp       TIMESTAMP WITH TIME ZONE NOT NULL
77);
78
79CREATE UNIQUE INDEX IF NOT EXISTS game_guid_idx
80  ON game(guid);
81  
82CREATE TABLE IF NOT EXISTS GameSet(
83  id                       BIGSERIAL PRIMARY KEY,
84  guid                     TEXT                 NOT NULL,
85
86  gameId               	   BIGINT REFERENCES game(id) NULL,
87  handId                   BIGINT REFERENCES hand(id)       NULL,
88  gameStatusId             BIGINT REFERENCES gameStatus(id) NULL, 
89  startTime                TIMESTAMP                NULL,
90
91  created_timestamp        TIMESTAMP WITH TIME ZONE NOT NULL,
92  modified_timestamp       TIMESTAMP WITH TIME ZONE NOT NULL
93);
94
95CREATE UNIQUE INDEX IF NOT EXISTS gameSet_guid_idx
96  ON gameSet(guid);
97
98CREATE TABLE IF NOT EXISTS Wins(
99  id                       BIGSERIAL PRIMARY KEY,
100  guid					   TEXT      NOT NULL,
101  
102  playerId                 BIGINT REFERENCES player (id) NOT NULL,
103
104  set1               	   INTEGER		                NULL,
105  set2                     INTEGER                     NULL,
106  set3                     INTEGER			            NULL,
107  set4                     INTEGER                     NULL,
108
109  created_timestamp        TIMESTAMP WITH TIME ZONE      NOT NULL,
110  modified_timestamp       TIMESTAMP WITH TIME ZONE      NOT NULL
111);
112
113CREATE UNIQUE INDEX IF NOT EXISTS wins_playerId_idx
114  ON wins(playerId);
115
116CREATE TABLE IF NOT EXISTS Item(
117	id                       BIGSERIAL PRIMARY KEY,
118	guid					 TEXT      NOT NULL,
119	
120	cost             		 NUMERIC	   NOT NULL,
121	tokenAmount              BIGINT    NOT NULL,
122
123	created_timestamp        TIMESTAMP WITH TIME ZONE      NOT NULL,
124	modified_timestamp       TIMESTAMP WITH TIME ZONE      NOT NULL
125);
126
127CREATE UNIQUE INDEX IF NOT EXISTS item_guid_idx
128ON item(guid);
129
130CREATE TABLE IF NOT EXISTS Purchase(
131	id                       BIGSERIAL PRIMARY KEY,
132	guid					 TEXT                          NOT NULL,
133	
134	playerId                 BIGINT REFERENCES player (id) NOT NULL,
135	itemId                   BIGINT REFERENCES Item (id) NOT NULL,
136	purchaseDate             TIMESTAMP			          NULL,
137
138	created_timestamp        TIMESTAMP WITH TIME ZONE      NOT NULL,
139	modified_timestamp       TIMESTAMP WITH TIME ZONE      NOT NULL
140);
141
142CREATE UNIQUE INDEX IF NOT EXISTS purchase_guid_idx
143ON purchase(guid);