· 8 years ago · Mar 12, 2018, 04:12 PM
1CREATE OR REPLACE FUNCTION words_play_game(
2 in_uid integer,
3 in_gid integer,
4 in_tiles jsonb
5 ) RETURNS table (
6 out_uid integer, -- the player to be notified
7 out_fcm text,
8 out_apns text,
9 out_adm text,
10 out_body text
11 ) AS
12$func$
13DECLARE
14 _tile jsonb;
15 _letter char;
16 _value integer;
17 _col integer;
18 _row integer;
19 _pos integer;
20 _mid bigint;
21 _total integer;
22 _hand_len integer;
23 _pile_len integer;
24 _move_len integer;
25 _pile char[];
26 _hand char[];
27 _letters char[][];
28 _values integer[][];
29 _opponent integer;
30 _finished timestamptz;
31 _reason text;
32 _score1 integer;
33 _score2 integer;
34BEGIN
35 IF EXISTS (SELECT 1 FROM words_users
36 WHERE uid = in_uid AND
37 banned_until > CURRENT_TIMESTAMP) THEN
38 RAISE EXCEPTION 'User % is banned', in_uid;
39 END IF;
40
41 -- fetch the 4 arrays (_hand, _pile, _letters, _values) for the current game
42 SELECT
43 hand1,
44 pile,
45 letters,
46 values
47 INTO
48 _hand,
49 _pile,
50 _letters,
51 _values
52 FROM words_games WHERE
53 gid = in_gid AND
54 player1 = in_uid AND
55 -- game is not over yet
56 finished IS NULL AND
57 -- and it is first player's turn
58 (played1 IS NULL OR played1 < played2);
59
60 IF NOT FOUND THEN
61 SELECT
62 hand2,
63 pile,
64 letters,
65 values
66 INTO
67 _hand,
68 _pile,
69 _letters,
70 _values
71 FROM words_games WHERE
72 gid = in_gid AND
73 player2 = in_uid AND
74 -- game is not over yet
75 finished IS NULL AND
76 -- and it is second player's turn
77 (played2 IS NULL OR played2 < played1);
78 END IF;
79
80 IF NOT FOUND THEN
81 RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
82 END IF;
83
84 PERFORM words_check_positions(in_uid, in_gid, in_tiles);
85
86 FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_tiles)
87 LOOP
88 _letter := _tile->>'letter';
89 _value := (_tile->>'value')::int;
90 _col := (_tile->>'col')::int + 1;
91 _row := (_tile->>'row')::int + 1;
92
93 IF NOT words_valid_tile(_letter, _value) THEN
94 RAISE EXCEPTION 'Invalid tile = %', _tile;
95 END IF;
96
97 -- search for the played tile in the player hand
98 IF _value = 0 THEN
99 _pos := ARRAY_POSITION(_hand, '*');
100 ELSE
101 _pos := ARRAY_POSITION(_hand, _letter);
102 END IF;
103
104 IF _pos >= 1 THEN
105 _hand[_pos] := NULL;
106 ELSE
107 RAISE EXCEPTION 'Tile % not found in hand %', _tile, _hand;
108 END IF;
109
110 _letters[_col][_row] := _letter;
111 _values[_col][_row] := _value;
112 END LOOP;
113
114 -- remove played tiles from player hand
115 _hand := ARRAY_REMOVE(_hand, NULL);
116 -- move up to 7 missing tiles from pile to hand
117 _hand_len := CARDINALITY(_hand);
118 _pile_len := CARDINALITY(_pile);
119 _move_len := LEAST(7 - _hand_len, _pile_len);
120 _hand := _hand || _pile[1:_move_len];
121 _pile := _pile[(1 + _move_len):_pile_len];
122
123 INSERT INTO words_moves (
124 action,
125 gid,
126 uid,
127 played,
128 tiles
129 ) VALUES (
130 'play',
131 in_gid,
132 in_uid,
133 CURRENT_TIMESTAMP,
134 in_tiles
135 ) RETURNING mid INTO STRICT _mid;
136
137 INSERT INTO words_scores (
138 mid,
139 gid,
140 uid,
141 word,
142 score
143 ) ( SELECT
144 _mid,
145 in_gid,
146 in_uid,
147 out_word,
148 max(out_score)
149 FROM words_check_words(in_uid, in_gid, in_tiles)
150 GROUP BY out_word);
151
152 SELECT
153 SUM(score),
154 words_get_given(in_uid) || ': ' || STRING_AGG(FORMAT('%s (%s)', word, score), ', ')
155 INTO STRICT
156 _total,
157 out_body
158 FROM words_scores
159 WHERE mid = _mid;
160
161 if _move_len = 7 THEN
162 _total := _total + 15;
163 out_body := out_body || ' +15 бонуÑ';
164 END IF;
165
166 -- player has no tiles, game over
167 IF CARDINALITY(_hand) = 0 THEN
168 _finished := CURRENT_TIMESTAMP;
169 _reason := 'regular';
170 -- TODO append win, loss, draw to out_body
171 END IF;
172
173 UPDATE words_moves SET
174 score = _total
175 WHERE mid = _mid;
176
177 -- RAISE NOTICE '_hand = %', _hand;
178 -- RAISE NOTICE '_pile = %', _pile;
179 -- RAISE NOTICE '_letters = %', _letters;
180 -- RAISE NOTICE '_values = %', _values;
181 -- RAISE NOTICE '_hand_len = %', _hand_len;
182 -- RAISE NOTICE '_pile_len = %', _pile_len;
183 -- RAISE NOTICE '_move_len = %', _move_len;
184 -- RAISE NOTICE '_total = %', _total;
185
186 -- TODO update score and store played words and stats
187
188 UPDATE words_games SET
189 finished = _finished,
190 reason = _reason,
191 played1 = CURRENT_TIMESTAMP,
192 score1 = score1 + _total,
193 hand1 = _hand,
194 pile = _pile,
195 letters = _letters,
196 values = _values,
197 state1 = words_get_state(_finished, score1 + _total, score2),
198 state2 = words_get_state(_finished, score2, score1 + _total),
199 hint1 = words_get_hint(_finished, FALSE, score1 + _total, score2),
200 hint2 = words_get_hint(_finished, TRUE, score2, score1 + _total)
201 WHERE
202 gid = in_gid AND
203 player1 = in_uid AND
204 -- game is not over yet
205 finished IS NULL AND
206 -- and it is first player's turn
207 (played1 IS NULL OR played1 < played2)
208 RETURNING
209 player2,
210 score1,
211 score2
212 INTO
213 _opponent,
214 _score1,
215 _score2;
216
217 IF NOT FOUND THEN
218 UPDATE words_games SET
219 finished = _finished,
220 reason = _reason,
221 played2 = CURRENT_TIMESTAMP,
222 score2 = score2 + _total,
223 hand2 = _hand,
224 pile = _pile,
225 letters = _letters,
226 values = _values,
227 state1 = words_get_state(_finished, score1, score2 + _total),
228 state2 = words_get_state(_finished, score2 + _total, score1),
229 hint1 = words_get_hint(_finished, TRUE, score1, score2 + _total),
230 hint2 = words_get_hint(_finished, FALSE, score2 + _total, score1)
231 WHERE
232 gid = in_gid AND
233 player2 = in_uid AND
234 -- game is not over yet
235 finished IS NULL AND
236 -- and it is second player's turn
237 (played2 IS NULL OR played2 < played1)
238 RETURNING
239 player1,
240 score2,
241 score1
242 INTO
243 _opponent,
244 _score1,
245 _score2;
246 END IF;
247
248 IF NOT FOUND THEN
249 RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
250 END IF;
251
252 -- this is the very first move in 1-player game, notification not needed
253 IF _opponent IS NULL THEN
254 RETURN;
255 END IF;
256
257 SELECT
258 _opponent,
259 fcm,
260 apns,
261 adm
262 FROM words_users
263 WHERE uid = _opponent
264 INTO STRICT
265 out_uid,
266 out_fcm,
267 out_apns,
268 out_adm;
269 -- add 1 row (containing notification) to the output table
270 RETURN NEXT;
271END
272$func$ LANGUAGE plpgsql;