· 7 years ago · Oct 09, 2018, 10:32 AM
1DROP SCHEMA public CASCADE;
2CREATE SCHEMA public;
3SET search_path TO public;
4
5BEGIN TRANSACTION;
6 -- Extensions
7 CREATE EXTENSION pgcrypto; -- used for password encryption/decryption
8 CREATE EXTENSION CITEXT;
9 CREATE EXTENSION UNACCENT;
10 CREATE EXTENSION "uuid-ossp"; -- used for generating UUIDs for sessions
11
12 -- Global functions
13 CREATE OR REPLACE FUNCTION set_updated_at_column() RETURNS TRIGGER AS $$
14 BEGIN
15 NEW.updated_at = NOW();
16 RETURN NEW;
17 END;
18 $$ LANGUAGE 'plpgsql';
19 CREATE OR REPLACE FUNCTION clean(TEXT) RETURNS TEXT AS $$
20 SELECT TRANSLATE(LOWER(UNACCENT($1)),' ','-');
21 $$ LANGUAGE SQL
22 IMMUTABLE;
23
24 -- Custom operator for handling "more or like equal" situations
25 -- TODO: this is currently duplicated from the tenant schema into the public schema
26 -- to fix errors in production ; the root cause should still be investigated
27 CREATE FUNCTION more_or_like_equal(NUMERIC, NUMERIC) RETURNS BOOLEAN AS $$
28 SELECT $1 BETWEEN $2 * .9 AND 1 + $2 * 1.2;
29 $$ LANGUAGE SQL IMMUTABLE STRICT;
30 CREATE OPERATOR ~= (
31 LEFTARG = NUMERIC,
32 RIGHTARG = NUMERIC,
33 PROCEDURE = more_or_like_equal,
34 COMMUTATOR = ~=,
35 NEGATOR = !~=
36 );
37
38 -- JSON Web Token
39 -- inspired by: https://github.com/PierreRochard/pgjwt [MIT license]
40 BEGIN;
41 CREATE OR REPLACE FUNCTION url_encode(data bytea) RETURNS text LANGUAGE SQL AS $$
42 SELECT translate(encode(data, 'base64'), E'+/=\n', '-_');
43 $$;
44
45 CREATE OR REPLACE FUNCTION url_decode(data text) RETURNS bytea LANGUAGE SQL AS $$
46 WITH t AS (SELECT translate(data, '-_', '+/')),
47 rem AS (SELECT length((SELECT * FROM t)) % 4) -- compute padding size
48 SELECT decode(
49 (SELECT * FROM t) ||
50 CASE WHEN (SELECT * FROM rem) > 0
51 THEN repeat('=', (4 - (SELECT * FROM rem)))
52 ELSE '' END,
53 'base64');
54 $$;
55
56 CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text) RETURNS text LANGUAGE SQL AS $$
57 WITH alg AS (
58 SELECT CASE
59 WHEN algorithm = 'HS256' THEN 'sha256'
60 WHEN algorithm = 'HS384' THEN 'sha384'
61 WHEN algorithm = 'HS512' THEN 'sha512'
62 ELSE '' END -- hmac throws error
63 )
64 SELECT url_encode(hmac(signables, secret, (SELECT * FROM alg)));
65 $$;
66
67 CREATE OR REPLACE FUNCTION jwt_sign(payload json, secret text, algorithm text DEFAULT 'HS256') RETURNS text LANGUAGE SQL AS $$
68 WITH header AS (
69 SELECT url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8'))
70 ),
71 payload AS (
72 SELECT url_encode(convert_to(payload::text, 'utf8'))
73 ),
74 signables AS (
75 SELECT (SELECT * FROM header) || '.' || (SELECT * FROM payload)
76 )
77 SELECT (SELECT * FROM signables) || '.' || algorithm_sign((SELECT * FROM signables), secret, algorithm);
78 $$;
79
80 CREATE OR REPLACE FUNCTION jwt_verify(token text, secret text, algorithm text DEFAULT 'HS256') RETURNS table(header json, payload json, valid boolean) LANGUAGE SQL AS $$
81 SELECT
82 convert_from(url_decode(r[1]), 'utf8')::json AS header,
83 convert_from(url_decode(r[2]), 'utf8')::json AS payload,
84 r[3] = algorithm_sign(r[1] || '.' || r[2], secret, algorithm) AS valid
85 FROM regexp_split_to_array(token, '\.') r;
86 $$;
87 COMMIT;
88
89
90 BEGIN;
91 CREATE TABLE tenants (
92 id BIGSERIAL PRIMARY KEY,
93 name TEXT NOT NULL,
94 seed INTEGER NOT NULL DEFAULT(floor(random() * (99999 + 1))),
95 settings JSONB NOT NULL DEFAULT '{ "custom_domain": "", "css": "", "js":"", "input_placeholder": "", "onboarding_slider": true, "onboarding_slider_logo": "", "onboarding_slide_1": "", "onboarding_slide_2": "", "onboarding_slide_3": "", "suggestive_buttons": false, "custom_results_layout": false, "dedicated_filters_page": true, "dedicated_results_page": true, "speech_interval": "200 + 55 * SPEECH_LENGTH / 3", "debug_mode": false }',
96 assistant JSONB NOT NULL DEFAULT '{ "avatar": "", "avatarUrl": "", "name": "", "color": "" }',
97 UNIQUE (name, seed)
98 );
99
100 DROP TYPE IF EXISTS USER_REGISTRATION_STATUS;
101 CREATE TYPE USER_REGISTRATION_STATUS AS ENUM ('unconfirmed', 'confirmed');
102 DROP TYPE IF EXISTS ASSISTANT_MODULES;
103 CREATE TYPE ASSISTANT_MODULES AS ENUM ('welcome', 'help', 'insults');
104 DROP TYPE IF EXISTS ASSISTANT_SELECT_AD;
105 CREATE TYPE ASSISTANT_SELECT_AD AS ENUM ('autonomous', 'redirect');
106 CREATE TABLE IF NOT EXISTS users (
107 id BIGSERIAL PRIMARY KEY,
108 tenant_id INT REFERENCES tenants(id) NOT NULL,
109 viewer BOOLEAN NOT NULL DEFAULT false,
110 email TEXT UNIQUE NOT NULL,
111 encrypted_password TEXT NOT NULL,
112 registration_status USER_REGISTRATION_STATUS NOT NULL DEFAULT 'unconfirmed',
113 confirmation_token TEXT DEFAULT MD5(RANDOM()::text || CLOCK_TIMESTAMP()::text) NOT NULL UNIQUE,
114 confirmed_at TIMESTAMP,
115 full_name TEXT,
116 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
117 updated_at TIMESTAMP NOT NULL DEFAULT NOW()
118 );
119 CREATE INDEX users_email ON users (email);
120 CREATE INDEX users_created_at ON users (created_at);
121 CREATE INDEX users_updated_at ON users (updated_at);
122 CREATE INDEX users_registration_status ON users (registration_status);
123
124 CREATE TRIGGER users_set_updated_at
125 BEFORE UPDATE ON users FOR EACH ROW
126 EXECUTE PROCEDURE set_updated_at_column();
127
128 DROP TYPE IF EXISTS USER_SIGN_UP_RETURN CASCADE;
129 CREATE TYPE USER_SIGN_UP_RETURN AS (id int, confirmation_token TEXT);
130 CREATE OR REPLACE FUNCTION user_sign_up(email TEXT, password TEXT, viewer BOOLEAN, tenant_id INT) RETURNS USER_SIGN_UP_RETURN AS $$
131 INSERT INTO users (email, encrypted_password, viewer, tenant_id)
132 VALUES ($1, crypt($2, gen_salt('bf', 8)), $3, $4)
133 RETURNING (id, confirmation_token)::USER_SIGN_UP_RETURN;
134 $$ LANGUAGE SQL;
135
136 CREATE OR REPLACE FUNCTION user_change_password(email TEXT, old_password TEXT, new_password TEXT, tenant_id INT) RETURNS USER_SIGN_UP_RETURN AS $$
137 UPDATE users SET encrypted_password = crypt($3, gen_salt('bf', 8))
138 WHERE email = $1 AND encrypted_password = crypt($2, encrypted_password) AND tenant_id = $4
139 RETURNING (id, confirmation_token)::USER_SIGN_UP_RETURN;
140 $$ LANGUAGE SQL;
141
142 CREATE OR REPLACE FUNCTION user_auth(email TEXT, password TEXT) RETURNS TABLE (id BIGINT, email TEXT, full_name TEXT, tenant_id INT) AS $$
143 SELECT id, email, full_name, tenant_id
144 FROM users
145 WHERE email = $1
146 AND encrypted_password = crypt($2, encrypted_password);
147 $$ LANGUAGE SQL;
148
149 CREATE OR REPLACE FUNCTION populate_tenant_schema(schema_name TEXT) RETURNS INT AS $TENANT_SCHEMA$
150 BEGIN
151 EXECUTE format('SET search_path TO ''%I'';', $1);
152 EXECUTE format('SELECT public.populate_tenant_schema_2(''%s'');', $1);
153 EXECUTE format('SELECT public.populate_tenant_data(''%s'');', $1);
154 EXECUTE format('ALTER FUNCTION update_quality_indices() SET search_path = %s,public;', $1);
155 EXECUTE format('ALTER FUNCTION update_quality_indice() SET search_path = %s,public;', $1);
156 EXECUTE format('ALTER FUNCTION price_stats() SET search_path = %s,public;', $1);
157 EXECUTE format('ALTER FUNCTION categories_stats() SET search_path = %s,public;', $1);
158 EXECUTE format('ALTER FUNCTION attributes_stats() SET search_path = %s,public;', $1);
159 EXECUTE format('ALTER FUNCTION thesaurus() SET search_path = %s,public;', $1);
160 EXECUTE format('ALTER FUNCTION more_or_like_equal(NUMERIC, NUMERIC) SET search_path = %s,public;', $1);
161 EXECUTE format('ALTER FUNCTION set_session_uuid() SET search_path = %s,public;', $1);
162 EXECUTE format('ALTER FUNCTION set_session_type() SET search_path = %s,public;', $1);
163 EXECUTE format('ALTER FUNCTION add_end_user() SET search_path = %s,public;', $1);
164 EXECUTE format('ALTER FUNCTION session_start(TEXT, INET, VARCHAR(2), JSONB, BIGINT) SET search_path = %s,public;', $1);
165 EXECUTE format('ALTER FUNCTION session_end(BIGINT) SET search_path = %s,public;', $1);
166 EXECUTE format('ALTER FUNCTION stats() SET search_path = %s,public;', $1);
167 EXECUTE format('ALTER FUNCTION conversations_not_understood() SET search_path = %s,public;', $1);
168 EXECUTE format('ALTER FUNCTION conversations_stats_repartition(TIMESTAMP, INTERVAL, TIMESTAMP) SET search_path = %s;', $1);
169 EXECUTE format('ALTER FUNCTION conversations_stats_criteria(TIMESTAMP, INTERVAL, TIMESTAMP) SET search_path = %s;', $1);
170 EXECUTE format('ALTER FUNCTION conversations_stats_funnel(TIMESTAMP, INTERVAL, TIMESTAMP) SET search_path = %s;', $1);
171 EXECUTE format('ALTER FUNCTION conversations_stats(TIMESTAMP, INTERVAL, TIMESTAMP) SET search_path = %s;', $1);
172 EXECUTE format('ALTER FUNCTION get_last_connection(BIGINT) SET search_path = %s;', $1);
173 EXECUTE format('ALTER FUNCTION get_last_criteria(BIGINT) SET search_path = %s;', $1);
174 EXECUTE format('ALTER FUNCTION get_favorite_item(BIGINT) SET search_path = %s;', $1);
175
176 EXECUTE 'SET search_path TO "$user",public;';
177 RETURN 1;
178 END
179 $TENANT_SCHEMA$ LANGUAGE plpgsql;
180
181 CREATE OR REPLACE FUNCTION confirm(token TEXT, secret text, algorithm text) RETURNS text LANGUAGE SQL AS $$
182 UPDATE users SET registration_status = 'confirmed' WHERE confirmation_token = $1 RETURNING id;
183 SELECT jwt_sign(
184 (SELECT ('{"id":' || id || ', "email":"' || email || '", "tenant_id":' || tenant_id || '}')::JSON from users WHERE confirmation_token = $1),
185 $2, $3
186 );
187 $$;
188
189 CREATE OR REPLACE FUNCTION populate_tenant_schema_2(schema_name TEXT) RETURNS INT AS $TENANT_SCHEMA$
190 DROP TYPE IF EXISTS PROPERTY_TRANSACTION_TYPE;
191 CREATE TYPE PROPERTY_TRANSACTION_TYPE AS ENUM ('sale', 'rent', 'life annuity');
192 CREATE TABLE properties (
193 id BIGSERIAL PRIMARY KEY,
194 property_id TEXT NOT NULL,
195 permalink TEXT,
196 transaction_type PROPERTY_TRANSACTION_TYPE NOT NULL DEFAULT 'sale',
197 title TEXT,
198 description TEXT NOT NULL,
199 price DECIMAL(12,2),
200 picture_urls TEXT[] NOT NULL,
201 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
202 crawled_attr JSONB NOT NULL DEFAULT '{}',
203 extracted_attr JSONB NOT NULL DEFAULT '{}',
204 extracted_at TIMESTAMP,
205 text_mined_attr JSONB NOT NULL DEFAULT '{}',
206 text_mined_at TIMESTAMP,
207 active BOOLEAN DEFAULT TRUE,
208 quality_indice INT NOT NULL DEFAULT 0,
209 new BOOLEAN DEFAULT FALSE
210 );
211 CREATE INDEX properties_transaction_type ON properties (transaction_type);
212 CREATE INDEX properties_crawled_attr ON properties USING GIN(crawled_attr);
213 CREATE INDEX properties_created_at ON properties (created_at);
214 CREATE INDEX properties_active ON properties (active);
215 CREATE INDEX properties_quality_indice ON properties (quality_indice);
216 CREATE UNIQUE INDEX properties_property_id ON properties (property_id);
217
218 CREATE FUNCTION update_quality_indices() RETURNS TABLE(quality_indice INT) AS $$
219 UPDATE properties
220 SET quality_indice = ROUND(
221 100 * (
222 -- the more attributes, the merrier
223 (SELECT COUNT(*) FROM (SELECT jsonb_object_keys(crawled_attr || extracted_attr || text_mined_attr)) tmp)
224 -- the more pictures, the merrier
225 +(SELECT LOG(10 * ((SELECT GREATEST(array_length(picture_urls::TEXT[], 1), 0)) + 1)) ^ 2)::NUMERIC
226 -- new properties are top quality
227 +(SELECT CASE new WHEN TRUE THEN 50 ELSE 0 END)
228 -- downgrade atypical properties
229 -(CASE WHEN ((extracted_attr->>'atypical' IS NOT NULL) OR (text_mined_attr->>'atypical' IS NOT NULL)) THEN 5 ELSE 0 END)
230 )
231 )
232 RETURNING quality_indice;
233 $$ LANGUAGE SQL;
234
235 CREATE FUNCTION update_quality_indice() RETURNS TRIGGER AS $$
236 DECLARE
237 attributes_quality numeric;
238 pictures_count numeric;
239 pictures_quality numeric;
240 new_quality numeric;
241 atypical_property numeric;
242 BEGIN
243 -- the more attributes, the merrier
244 attributes_quality := (SELECT COUNT(*) FROM (SELECT jsonb_object_keys(NEW.crawled_attr || NEW.extracted_attr || NEW.text_mined_attr)) tmp);
245 -- the more pictures, the merrier
246 pictures_count := (SELECT GREATEST(array_length(NEW.picture_urls::TEXT[], 1), 0));
247 pictures_quality := (SELECT LOG(10 * (pictures_count + 1)) ^ 2)::NUMERIC;
248 -- new properties are top quality
249 new_quality := (CASE NEW.new WHEN TRUE THEN 50 ELSE 0 END);
250 -- downgrade atypical properties
251 atypical_property := (CASE WHEN ((NEW.extracted_attr->>'atypical' IS NOT NULL) OR (NEW.text_mined_attr->>'atypical' IS NOT NULL)) THEN 5 ELSE 0 END);
252 NEW.quality_indice := ROUND(100 * (attributes_quality + pictures_quality + new_quality - atypical_property));
253 RETURN NEW;
254 END;
255 $$ LANGUAGE plpgsql;
256
257 CREATE TRIGGER set_quality_indice_on_insert
258 BEFORE INSERT ON properties FOR EACH ROW
259 EXECUTE PROCEDURE update_quality_indice();
260
261 CREATE TRIGGER set_quality_indice_on_update
262 BEFORE UPDATE ON properties FOR EACH ROW
263 WHEN (OLD.crawled_attr IS DISTINCT FROM NEW.crawled_attr
264 OR OLD.extracted_attr IS DISTINCT FROM NEW.extracted_attr
265 OR OLD.text_mined_attr IS DISTINCT FROM NEW.text_mined_attr
266 OR OLD.picture_urls IS DISTINCT FROM NEW.picture_urls)
267 EXECUTE PROCEDURE update_quality_indice();
268
269 -- property programs
270 CREATE TABLE property_programs (
271 id BIGSERIAL PRIMARY KEY,
272 program_id TEXT NOT NULL,
273 permalink TEXT,
274 title TEXT,
275 description TEXT NOT NULL,
276 price_min DECIMAL(12,2),
277 price_max DECIMAL(12,2),
278 units INT NOT NULL DEFAULT 0,
279 picture_urls TEXT[] NOT NULL,
280 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
281 crawled_attr JSONB NOT NULL DEFAULT '{}',
282 extracted_attr JSONB NOT NULL DEFAULT '{}',
283 extracted_at TIMESTAMP,
284 text_mined_attr JSONB NOT NULL DEFAULT '{}',
285 text_mined_at TIMESTAMP,
286 active BOOLEAN DEFAULT TRUE
287 );
288 CREATE INDEX property_programs_crawled_attr ON property_programs USING GIN(crawled_attr);
289 CREATE INDEX property_programs_created_at ON property_programs (created_at);
290 CREATE INDEX property_programs_active ON property_programs (active);
291 CREATE UNIQUE INDEX property_programs_program_id ON property_programs (program_id);
292
293 -- reference programs
294 ALTER TABLE properties ADD COLUMN program_id BIGINT REFERENCES property_programs;
295
296 -- TODO: CREATE MATERIALIZED VIEW
297 CREATE VIEW properties_data AS
298 SELECT id, property_id, permalink, transaction_type, program_id, new, title, description, price, picture_urls, created_at, (text_mined_attr || extracted_attr - 'price' - 'transaction_type') AS attr, crawled_attr AS more, quality_indice
299 FROM properties
300 WHERE active
301 ORDER BY quality_indice DESC;
302
303 -- TODO: CREATE UNIQUE INDEX properties_data_id ON properties_data (id);
304 -- TODO: CREATE INDEX properties_data_transaction_type ON properties_data (transaction_type);
305 -- TODO: CREATE INDEX properties_data_attr ON properties_data USING GIN(attr);
306
307 CREATE FUNCTION price_stats() RETURNS TABLE (transaction_type PROPERTY_TRANSACTION_TYPE, count BIGINT, min DECIMAL, max DECIMAL, sum DECIMAL, avg DECIMAL) AS $$
308 SELECT
309 transaction_type,
310 COUNT(*),
311 MIN(price)::DECIMAL(12,2) min,
312 MAX(price)::DECIMAL(12,2) max,
313 SUM(price)::DECIMAL(12,2) sum,
314 AVG(price)::DECIMAL(12,2) avg
315 FROM properties_data
316 GROUP BY transaction_type;
317 $$ LANGUAGE SQL;
318
319 CREATE FUNCTION categories_stats() RETURNS TABLE (category TEXT, count BIGINT) AS $$
320 SELECT
321 category,
322 COUNT(*)
323 FROM (
324 SELECT jsonb_object_keys(attr) category FROM properties_data
325 ) tmp
326 GROUP BY category
327 ORDER BY count DESC;
328 $$ LANGUAGE SQL;
329
330 CREATE OR REPLACE FUNCTION conversations_stats(argfrom timestamp without time zone, argstep interval, argto timestamp without time zone DEFAULT now())
331 RETURNS integer[]
332 LANGUAGE plpgsql
333 AS $$
334 declare
335 varFrom timestamp;
336 varTo timestamp;
337 varStep interval;
338 varResult integer[];
339 BEGIN
340 varStep = argStep;
341 varFrom = argFrom;
342 varTo = varFrom + varStep;
343 LOOP
344 varResult = array_append(varResult, CAST((SELECT COUNT(*) FROM sessions
345 WHERE connected_at BETWEEN varFrom AND varTo) as integer));
346 varFrom = varTo;
347 varTo = varTo + varStep;
348 IF varTo > argTo THEN
349 EXIT;
350 END IF;
351 END LOOP;
352 RETURN varResult;
353 END
354 $$;
355
356 CREATE OR REPLACE FUNCTION conversations_stats_funnel(argfrom timestamp without time zone, argstep interval, argto timestamp without time zone DEFAULT now())
357 RETURNS integer[]
358 LANGUAGE plpgsql
359 AS $$
360 declare
361 varFrom timestamp;
362 varTo timestamp;
363 varStep interval;
364 varResult integer[];
365 BEGIN
366 varStep = argStep;
367 varFrom = argFrom;
368 varTo = varFrom + varStep;
369 LOOP
370 varResult = array_append(varResult, CAST((
371 SELECT COUNT(DISTINCT s.id)
372 FROM
373 end_users u
374 LEFT JOIN sessions s ON s.end_user_id = u.id
375 LEFT JOIN conversation_details c ON s.id = c.session_id
376 WHERE s.connected_at BETWEEN varFrom AND varTo
377 AND c.conversation_id IS NULL
378 ) as integer));
379
380 varResult = array_append(varResult, CAST((
381 SELECT COUNT(DISTINCT s.id)
382 FROM
383 end_users u
384 LEFT JOIN sessions s ON s.end_user_id = u.id
385 LEFT JOIN conversation_details c ON s.id = c.session_id
386 WHERE s.connected_at BETWEEN varFrom AND varTo
387 AND c.conversation_id IS NOT NULL
388 ) as integer));
389
390 varResult = array_append(varResult, CAST((
391 SELECT COUNT(DISTINCT s.id)
392 FROM
393 end_users u
394 LEFT JOIN sessions s ON s.end_user_id = u.id
395 LEFT JOIN conversation_details c ON s.id = c.session_id
396 WHERE s.connected_at BETWEEN varFrom AND varTo
397 AND c.type = 'results'
398 ) as integer));
399
400 varResult = array_append(varResult, CAST((
401 SELECT COUNT(DISTINCT s.id)
402 FROM
403 end_users u
404 LEFT JOIN sessions s ON s.end_user_id = u.id
405 LEFT JOIN conversation_details c ON s.id = c.session_id
406 WHERE s.connected_at BETWEEN varFrom AND varTo
407 AND (c.type = 'converted lead' OR c.value = 'reva:convertedlead')
408 ) as integer));
409 varFrom = varTo;
410 varTo = varTo + varStep;
411 IF varTo > argTo THEN
412 EXIT;
413 END IF;
414 END LOOP;
415 RETURN varResult;
416 END
417 $$;
418
419 CREATE OR REPLACE FUNCTION conversations_stats_repartition(argfrom timestamp without time zone, argstep interval, argto timestamp without time zone DEFAULT now())
420 RETURNS json
421 LANGUAGE plpgsql
422 AS $$
423 declare
424 varResult json;
425 BEGIN
426 varResult = CAST((
427 with MyCTE as (
428 select d.value, COUNT(d.value) from conversation_details d
429 WHERE d.type = 'botml module'
430 AND d.created_at BETWEEN argfrom AND argto
431 GROUP BY d.value
432 )
433 select json_agg(row_to_json(MyCTE))
434 from MyCTE
435 ) as json);
436 RETURN varResult;
437 END
438 $$;
439
440 CREATE OR REPLACE FUNCTION conversations_stats_criteria(argfrom timestamp without time zone, argstep interval, argto timestamp without time zone DEFAULT now())
441 RETURNS json
442 LANGUAGE plpgsql
443 AS $$
444 declare
445 varResult json;
446 BEGIN
447 varResult = CAST((
448 with dupe as (
449 select COUNT(criteria->'category'), criteria->'category' as criterion
450 from (
451 select *,
452 jsonb_array_elements(d.data->'criteria') as criteria
453 from conversation_details d
454 WHERE d.created_at BETWEEN argfrom AND argto
455 ) d
456 group by criterion
457 order by d.count DESC
458 )
459 select json_agg(row_to_json(dupe))
460 from dupe
461 ) as json);
462 RETURN varResult;
463 END
464 $$;
465
466 CREATE FUNCTION attributes_stats() RETURNS TABLE (crawled NUMERIC, extracted NUMERIC, mined NUMERIC, merged NUMERIC, added_value NUMERIC) AS $$
467 WITH
468 data_crawled AS (
469 SELECT id, jsonb_object_keys(crawled_attr) AS keys
470 FROM properties
471 ),
472 data_extracted AS (
473 SELECT id, jsonb_object_keys(extracted_attr) AS keys
474 FROM properties
475 ),
476 data_mined AS (
477 SELECT id, jsonb_object_keys(text_mined_attr) AS keys
478 FROM properties
479 ),
480 data_merged AS (
481 SELECT id, jsonb_object_keys(crawled_attr || extracted_attr || text_mined_attr) AS keys
482 FROM properties
483 )
484 SELECT *, merged - crawled AS added_value
485 FROM (SELECT
486 (SELECT AVG(count) FROM (SELECT COUNT(keys) FROM data_crawled GROUP BY id) sub) AS crawled,
487 (SELECT AVG(count) FROM (SELECT COUNT(keys) FROM data_extracted GROUP BY id) sub) AS extracted,
488 (SELECT AVG(count) FROM (SELECT COUNT(keys) FROM data_mined GROUP BY id) sub) AS mined,
489 (SELECT AVG(count) FROM (SELECT COUNT(keys) FROM data_merged GROUP BY id) sub) AS merged
490 ) tmp;
491 $$ LANGUAGE SQL;
492
493 CREATE FUNCTION thesaurus() RETURNS TABLE (word TEXT, count BIGINT) AS $$
494 WITH words AS (
495 SELECT trim(BOTH '''' FROM unnest(string_to_array(string_agg(words, ' '), ' '))) word
496 FROM (SELECT strip(to_tsvector(description)) ::text words FROM properties) tmp
497 )
498 SELECT word, COUNT(*)
499 FROM words
500 WHERE char_length(word) > 1
501 GROUP BY word
502 ORDER BY COUNT DESC;
503 $$ LANGUAGE SQL;
504
505 -- Custom operator for handling "more or like equal" situations
506 CREATE FUNCTION more_or_like_equal(NUMERIC, NUMERIC) RETURNS BOOLEAN AS $$
507 SELECT $1 BETWEEN $2 * .9 AND 1 + $2 * 1.2;
508 $$ LANGUAGE SQL IMMUTABLE STRICT;
509 CREATE OPERATOR ~= (
510 LEFTARG = NUMERIC,
511 RIGHTARG = NUMERIC,
512 PROCEDURE = more_or_like_equal,
513 COMMUTATOR = ~=,
514 NEGATOR = !~=
515 );
516
517 -- Refresh properties_data
518 -- TODO: REFRESH MATERIALIZED VIEW properties_data;
519
520
521 CREATE TABLE ads_imports (
522 id BIGSERIAL PRIMARY KEY,
523 user_id INT REFERENCES public.users(id),
524 import BYTEA,
525 mimetype TEXT,
526 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
527 updated_at TIMESTAMP NOT NULL DEFAULT NOW()
528 );
529 CREATE INDEX user_imports_user_id ON ads_imports (user_id);
530 CREATE INDEX user_imports_created_at ON ads_imports (created_at);
531
532 -- End users
533 CREATE TABLE end_users (
534 id BIGSERIAL PRIMARY KEY,
535 email TEXT UNIQUE NULL,
536 encrypted_password TEXT NULL,
537 firstname TEXT NULL,
538 lastname TEXT NULL,
539 mobile_phone VARCHAR NULL,
540 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
541 updated_at TIMESTAMP NOT NULL DEFAULT NOW()
542 );
543 CREATE INDEX end_users_email ON end_users (email);
544 CREATE INDEX end_users_created_at ON end_users (created_at);
545 CREATE INDEX end_users_updated_at ON end_users (updated_at);
546
547 -- Sessions
548 DROP TYPE IF EXISTS SESSION_TYPE;
549 CREATE TYPE SESSION_TYPE AS ENUM ('real', 'demo');
550 CREATE TABLE sessions (
551 id BIGSERIAL PRIMARY KEY,
552 user_id BIGINT REFERENCES public.users,
553 end_user_id BIGINT REFERENCES end_users,
554 connection_id TEXT NOT NULL,
555 connected_at TIMESTAMP NOT NULL DEFAULT NOW(),
556 disconnected_at TIMESTAMP,
557 ip INET,
558 country VARCHAR(2) DEFAULT NULL,
559 headers JSONB DEFAULT '{}',
560 type SESSION_TYPE NOT NULL DEFAULT 'real',
561 uuid TEXT
562 );
563 CREATE INDEX sessions_user_id ON sessions (user_id);
564 CREATE INDEX sessions_end_user_id ON sessions (end_user_id);
565 CREATE INDEX sessions_connection_id ON sessions (connection_id);
566 CREATE INDEX sessions_connected_at ON sessions (connected_at);
567 CREATE INDEX sessions_type ON sessions (type);
568 CREATE INDEX sessions_uuid ON sessions (uuid);
569
570 CREATE FUNCTION set_session_uuid() RETURNS TRIGGER AS $$
571 BEGIN
572 NEW.uuid = uuid_generate_v5(uuid_ns_url(), CONCAT('reva', NEW.ip, jsonb_set(jsonb_set(jsonb_set(jsonb_set(jsonb_set(NEW.headers, '{cookie}', '""'), '{sec-websocket-key}', '""'), '{origin}', '""'), '{referer}', '""'), '{cf-ray}', '""')::text));
573 RETURN NEW;
574 END;
575 $$ LANGUAGE 'plpgsql';
576 CREATE TRIGGER sessions_set_uuid
577 BEFORE INSERT ON sessions FOR EACH ROW
578 EXECUTE PROCEDURE set_session_uuid();
579
580 CREATE FUNCTION set_session_type() RETURNS TRIGGER AS $$
581 BEGIN
582 NEW.type = CASE (NEW.headers->>'referer' ~ '^https?:\/\/127\.0\.0\.1' OR NEW.headers->>'referer' ~ '^https?:\/\/localhost') WHEN TRUE THEN 'demo' ELSE 'real' END;
583 RETURN NEW;
584 END;
585 $$ LANGUAGE 'plpgsql';
586 CREATE TRIGGER sessions_set_type
587 BEFORE INSERT ON sessions FOR EACH ROW
588 EXECUTE PROCEDURE set_session_type();
589
590 CREATE FUNCTION add_end_user() RETURNS BIGINT AS $$
591 INSERT INTO end_users DEFAULT VALUES
592 RETURNING id;
593 $$ LANGUAGE SQL;
594
595 CREATE FUNCTION session_start(connection_id TEXT, ip INET, country VARCHAR(2), headers JSONB, end_user_id BIGINT) RETURNS BIGINT AS $$
596 INSERT INTO sessions (connection_id, ip, country, headers, end_user_id)
597 VALUES ($1, $2, $3, $4, $5)
598 RETURNING id;
599 $$ LANGUAGE SQL;
600
601 CREATE FUNCTION session_end(session_id BIGINT) RETURNS BIGINT AS $$
602 UPDATE sessions
603 SET disconnected_at = NOW()
604 WHERE id = $1
605 RETURNING id;
606 $$ LANGUAGE SQL;
607
608 CREATE OR REPLACE FUNCTION get_last_connection(session_id BIGINT)
609 RETURNS timestamp without time zone
610 LANGUAGE SQL
611 AS $$
612 SELECT max(disconnected_at) FROM sessions WHERE uuid = (SELECT uuid FROM sessions WHERE id = $1)
613 $$;
614
615 -- Conversations
616 CREATE TABLE conversations (
617 id BIGSERIAL PRIMARY KEY,
618 session_id BIGINT REFERENCES sessions NOT NULL,
619 response_to BIGINT REFERENCES conversations,
620 bot BOOLEAN NOT NULL DEFAULT FALSE,
621 text TEXT NOT NULL,
622 type TEXT,
623 created_at TIMESTAMP DEFAULT NOW()
624 );
625 CREATE INDEX conversations_session_id ON conversations (session_id);
626 CREATE INDEX conversations_response_to ON conversations (response_to);
627 CREATE INDEX conversations_bot ON conversations (bot);
628 CREATE INDEX conversations_session_bot ON conversations (session_id, bot);
629 CREATE INDEX conversations_type ON conversations (type);
630
631 -- Conversation details
632 CREATE TABLE conversation_details (
633 id BIGSERIAL PRIMARY KEY,
634 session_id BIGINT REFERENCES sessions NOT NULL,
635 conversation_id BIGINT REFERENCES conversations,
636 type TEXT NOT NULL,
637 value TEXT,
638 data JSONB,
639 created_at TIMESTAMP DEFAULT NOW()
640 );
641 CREATE INDEX conversation_details_conversation_id ON conversation_details (conversation_id);
642 CREATE INDEX conversation_details_session_id ON conversation_details (session_id);
643 CREATE INDEX conversation_details_type ON conversation_details (type);
644 CREATE INDEX conversation_details_data ON conversation_details USING GIN(data);
645 CREATE INDEX conversation_details_created_at ON conversation_details (created_at);
646
647 CREATE OR REPLACE FUNCTION get_last_criteria(session_id BIGINT)
648 RETURNS jsonb
649 LANGUAGE SQL
650 AS $$
651 SELECT data FROM conversation_details WHERE created_at = (
652 SELECT max(created_at) FROM conversation_details WHERE session_id = $1 AND TYPE = 'criteria')
653 $$;
654
655 CREATE OR REPLACE FUNCTION array_subtract(array1 VARCHAR[], array2 VARCHAR[])
656 RETURNS VARCHAR[] AS $$
657 DECLARE
658 main_array VARCHAR[] := array1;
659 support_array VARCHAR[] := array2;
660 i VARCHAR;
661 BEGIN
662 FOREACH i IN ARRAY support_array LOOP
663 main_array[array_position(main_array, i)] := NULL;
664 END LOOP;
665 FOREACH i IN ARRAY main_array LOOP
666 main_array := array_remove(main_array, NULL);
667 END LOOP;
668 RETURN main_array;
669 END;
670 $$ LANGUAGE plpgsql;
671
672 CREATE OR REPLACE FUNCTION get_favorite_item(session_id BIGINT)
673 RETURNS VARCHAR[]
674 LANGUAGE SQL
675 AS $$
676 SELECT array_subtract(
677 array(SELECT data ->> 'item' FROM conversation_details WHERE session_id = $1 AND TYPE = 'add favorite'),
678 array(SELECT data ->> 'item' FROM conversation_details WHERE session_id = $1 AND TYPE = 'remove favorite')
679 ) AS items
680 $$;
681
682 CREATE FUNCTION stats() RETURNS TABLE (sessions BIGINT, users BIGINT, conversations BIGINT, average_session_duration INTEGER, average_discussion_length NUMERIC) AS $$
683 WITH sessions AS (
684 SELECT *
685 FROM sessions
686 WHERE connected_at >= (DATE(connected_at) - INTERVAL '1 WEEK')
687 AND type = 'real'
688 )
689 , conversations AS (
690 SELECT *
691 FROM conversations
692 WHERE created_at >= (DATE(created_at) - INTERVAL '1 WEEK')
693 )
694 SELECT
695 ( SELECT COUNT(*) FROM sessions ) AS sessions,
696 ( SELECT COUNT(DISTINCT uuid) FROM sessions ) AS users,
697 ( SELECT COUNT(DISTINCT session_id) FROM conversations WHERE session_id IN (SELECT id FROM sessions ) AND bot IS FALSE ) AS conversations,
698 ( SELECT COALESCE( (SELECT EXTRACT(epoch FROM AVG(disconnected_at - connected_at))::INTEGER FROM sessions), 0 )) AS average_session_duration,
699 ( SELECT COALESCE( (SELECT ROUND(AVG(count), 2) FROM (SELECT COUNT(*) FROM conversations WHERE session_id IN (SELECT id FROM sessions ) GROUP BY session_id) tmp), 0 )) AS average_discussion_length;
700 $$ LANGUAGE SQL;
701
702 CREATE FUNCTION conversations_not_understood() RETURNS TABLE (word TEXT, count BIGINT) AS $$
703 WITH not_understood AS (
704 SELECT *
705 FROM conversations
706 WHERE id = ANY (
707 SELECT response_to
708 FROM conversations
709 WHERE bot IS TRUE
710 AND text IN ('Navrée, je n''ai pas compris', 'Je n''ai pas compris, désolée', 'Veuillez m''excuser, je n''ai pas saisi')
711 )
712 )
713 , words AS (
714 SELECT trim(BOTH '''' FROM unnest(string_to_array(string_agg(words, ' '), ' '))) word
715 FROM (SELECT strip(to_tsvector(text)) ::text words FROM not_understood) tmp
716 )
717 SELECT word, COUNT(*)
718 FROM words
719 WHERE char_length(word) > 1
720 GROUP BY word
721 ORDER BY COUNT DESC;
722 $$ LANGUAGE SQL;
723
724 CREATE TABLE IF NOT EXISTS custom_assistant_modules (
725 id BIGSERIAL PRIMARY KEY,
726 module_id INTEGER REFERENCES public.default_assistant_modules(id),
727 enabled BOOLEAN NOT NULL,
728 position INTEGER NOT NULL,
729 name TEXT,
730 botml TEXT,
731 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
732 last_editor TEXT DEFAULT NULL,
733 edited_at TIMESTAMP DEFAULT NULL
734 );
735 SELECT 1;
736 $TENANT_SCHEMA$ LANGUAGE SQL;
737
738 CREATE TABLE IF NOT EXISTS default_assistant_modules (
739 id BIGSERIAL PRIMARY KEY,
740 enabled BOOLEAN NOT NULL DEFAULT TRUE,
741 editable BOOLEAN NOT NULL DEFAULT TRUE,
742 sortable BOOLEAN NOT NULL DEFAULT TRUE,
743 first BOOLEAN NOT NULL DEFAULT FALSE,
744 last BOOLEAN NOT NULL DEFAULT FALSE,
745 default_name TEXT NOT NULL,
746 default_position INTEGER NOT NULL,
747 default_botml TEXT,
748 created_at TIMESTAMP NOT NULL DEFAULT NOW()
749 );
750
751 INSERT INTO default_assistant_modules (enabled, default_name, default_position, first, last, sortable, editable, default_botml) VALUES
752 (true, 'Message de bienvenue', 0, TRUE, FALSE, FALSE, TRUE, E'~ default\n< Bonjour, je suis $assistant_name, votre assistant immo. Décrivez-moi votre logement idéal'),
753 (true, 'Module recherche immobilière', 1, FALSE, FALSE, TRUE, FALSE, null),
754 (false, 'Rattrapage', 2, FALSE, TRUE, FALSE, TRUE, E'> *\n< Navré, je n''ai pas compris.');
755
756 CREATE OR REPLACE FUNCTION populate_tenant_data(schema_name TEXT) RETURNS INT AS $TENANT_DATA$
757 BEGIN
758 EXECUTE('INSERT INTO custom_assistant_modules (module_id, position, enabled)
759 SELECT id, default_position, enabled FROM public.default_assistant_modules;');
760
761 RETURN 1;
762 END
763 $TENANT_DATA$ LANGUAGE plpgsql;
764
765 CREATE OR REPLACE FUNCTION create_tenant_record(name TEXT) RETURNS TABLE (id BIGINT) AS $$
766 INSERT INTO tenants (name) VALUES ($1) RETURNING id
767 $$ LANGUAGE SQL;
768
769 CREATE OR REPLACE FUNCTION create_tenant(name TEXT) RETURNS TABLE (id BIGINT, tenant_name TEXT) AS $$
770 DECLARE
771 _id int;
772 _schema_name TEXT;
773 _create_schema_flag INT;
774 BEGIN
775 _id = create_tenant_record($1);
776 _schema_name = 'tenant_' || _id;
777 EXECUTE format('CREATE SCHEMA %s;', _schema_name);
778 RETURN QUERY SELECT tenants.id, tenants.name from tenants where tenants.id = _id;
779 END
780 $$ LANGUAGE plpgsql;
781 COMMIT;
782
783END TRANSACTION;