nFKgRpNB

1create table if not exists migrations
2(
3	id serial not null
4		constraint migrations_pkey
5			primary key,
6	migration varchar(255) not null,
7	batch integer not null
8)
9;
10
11alter table migrations owner to postgres
12;
13
14create table if not exists users
15(
16	id serial not null
17		constraint users_pkey
18			primary key,
19	name varchar(255) not null,
20	email varchar(255) not null
21		constraint users_email_unique
22			unique,
23	email_verified_at timestamp(0),
24	password varchar(255) not null,
25	remember_token varchar(100),
26	created_at timestamp(0),
27	updated_at timestamp(0),
28	ip varchar
29)
30;
31
32alter table users owner to postgres
33;
34
35create table if not exists password_resets
36(
37	email varchar(255) not null,
38	token varchar(255) not null,
39	created_at timestamp(0)
40)
41;
42
43alter table password_resets owner to postgres
44;
45
46create index if not exists password_resets_email_index
47	on password_resets (email)
48;
49
50create table if not exists permissions
51(
52	id serial not null
53		constraint permissions_pkey
54			primary key,
55	name varchar(255) not null,
56	guard_name varchar(255) not null,
57	created_at timestamp(0),
58	updated_at timestamp(0)
59)
60;
61
62alter table permissions owner to postgres
63;
64
65create table if not exists roles
66(
67	id serial not null
68		constraint roles_pkey
69			primary key,
70	name varchar(255) not null,
71	guard_name varchar(255) not null,
72	created_at timestamp(0),
73	updated_at timestamp(0)
74)
75;
76
77alter table roles owner to postgres
78;
79
80create table if not exists model_has_permissions
81(
82	permission_id integer not null
83		constraint model_has_permissions_permission_id_foreign
84			references permissions
85				on delete cascade,
86	model_type varchar(255) not null,
87	model_id bigint not null,
88	constraint model_has_permissions_pkey
89		primary key (permission_id, model_id, model_type)
90)
91;
92
93alter table model_has_permissions owner to postgres
94;
95
96create index if not exists model_has_permissions_model_id_model_type_index
97	on model_has_permissions (model_id, model_type)
98;
99
100create table if not exists model_has_roles
101(
102	role_id integer not null
103		constraint model_has_roles_role_id_foreign
104			references roles
105				on delete cascade,
106	model_type varchar(255) not null,
107	model_id bigint not null,
108	constraint model_has_roles_pkey
109		primary key (role_id, model_id, model_type)
110)
111;
112
113alter table model_has_roles owner to postgres
114;
115
116create index if not exists model_has_roles_model_id_model_type_index
117	on model_has_roles (model_id, model_type)
118;
119
120create table if not exists role_has_permissions
121(
122	permission_id integer not null
123		constraint role_has_permissions_permission_id_foreign
124			references permissions
125				on delete cascade,
126	role_id integer not null
127		constraint role_has_permissions_role_id_foreign
128			references roles
129				on delete cascade,
130	constraint role_has_permissions_pkey
131		primary key (permission_id, role_id)
132)
133;
134
135alter table role_has_permissions owner to postgres
136;
137
138create table if not exists ranktracker_projects
139(
140	id serial not null
141		constraint ranktracker_projects_pkey
142			primary key,
143	name varchar(255) not null,
144	emails varchar(255) not null,
145	domains varchar(255) not null,
146	url varchar(255) not null,
147	keywords varchar(255) not null,
148	user_id integer not null,
149	created_at timestamp(0),
150	updated_at timestamp(0)
151)
152;
153
154alter table ranktracker_projects owner to postgres
155;
156
157create table if not exists ranktracker_project_searchengines
158(
159	id serial not null
160		constraint ranktracker_project_searchengines_pkey
161			primary key,
162	project_id integer not null,
163	se_id integer not null,
164	se_name varchar(255) not null,
165	se_country varchar(255) not null,
166	se_language varchar(255) not null,
167	loc_id integer,
168	created_at timestamp(0),
169	updated_at timestamp(0)
170)
171;
172
173alter table ranktracker_project_searchengines owner to postgres
174;
175
176create table if not exists keywords
177(
178	id serial not null
179		constraint keywords_pkey
180			primary key,
181	key varchar(255) not null
182		constraint keywords_key_unique
183			unique,
184	created_at timestamp(0),
185	updated_at timestamp(0)
186)
187;
188
189alter table keywords owner to postgres
190;
191
192create table if not exists ranktracker_project_keywords
193(
194	keyword_id integer not null,
195	project_id integer not null,
196	status boolean default true not null
197)
198;
199
200alter table ranktracker_project_keywords owner to postgres
201;
202
203create table if not exists jobs
204(
205	id bigserial not null
206		constraint jobs_pkey
207			primary key,
208	queue varchar(255) not null,
209	payload text not null,
210	attempts smallint not null,
211	full_payload text,
212	user_id integer,
213	project_id integer,
214	reserved_at integer,
215	available_at integer not null,
216	created_at integer not null
217)
218;
219
220alter table jobs owner to postgres
221;
222
223create index if not exists jobs_queue_index
224	on jobs (queue)
225;
226
227create table if not exists users_jobs
228(
229	id bigserial not null
230		constraint users_jobs_pkey
231			primary key,
232	user_id integer,
233	project_id integer,
234	jobs_count integer not null,
235	results_count integer,
236	failed_count integer,
237	processed_count integer,
238	payload text not null,
239	type varchar(255) not null,
240	status varchar(255) not null,
241	created_at timestamp(0),
242	updated_at timestamp(0)
243)
244;
245
246alter table users_jobs owner to postgres
247;
248
249create index if not exists users_jobs_user_id_index
250	on users_jobs (user_id)
251;
252
253create index if not exists users_jobs_payload_index
254	on users_jobs (payload)
255;
256
257create index if not exists users_jobs_type_index
258	on users_jobs (type)
259;
260
261create index if not exists users_jobs_status_index
262	on users_jobs (status)
263;
264
265create table if not exists failed_jobs
266(
267	id bigserial not null
268		constraint failed_jobs_pkey
269			primary key,
270	connection text not null,
271	queue text not null,
272	payload text not null,
273	exception text not null,
274	failed_at timestamp(0) default CURRENT_TIMESTAMP not null
275)
276;
277
278alter table failed_jobs owner to postgres
279;
280
281create table if not exists ranktracker_jobs
282(
283	id serial not null
284		constraint ranktracker_jobs_pkey
285			primary key,
286	user_id integer not null,
287	project_id integer not null,
288	se_id integer not null,
289	dfs_task_id bigint not null,
290	post_id integer not null,
291	system_key_id integer not null,
292	message text,
293	main_job integer not null,
294	status integer not null,
295	created_at timestamp(0),
296	updated_at timestamp(0)
297)
298;
299
300comment on column ranktracker_jobs.status is '0 - processing, 1 - processed, 2 - error'
301;
302
303alter table ranktracker_jobs owner to postgres
304;
305
306create table if not exists ranktracker_rankings
307(
308	id serial not null
309		constraint ranktracker_rankings_pkey
310			primary key,
311	project_id integer not null,
312	se_id integer not null,
313	main_job_id integer not null,
314	user_id integer not null,
315	updating_date date not null,
316	created_at timestamp(0),
317	updated_at timestamp(0)
318)
319;
320
321alter table ranktracker_rankings owner to postgres
322;
323
324create table if not exists keywords_info
325(
326	id serial not null
327		constraint keywords_info_pkey
328			primary key,
329	key_id integer not null,
330	se_id integer not null,
331	cmp double precision,
332	cpc double precision,
333	sv integer,
334	categories json,
335	ms json,
336	created_at timestamp(0),
337	updated_at timestamp(0),
338	constraint keywords_info_key_id_se_id_unique
339		unique (key_id, se_id)
340)
341;
342
343alter table keywords_info owner to postgres
344;
345
346create index if not exists keywords_info_key_id_index
347	on keywords_info (key_id)
348;
349
350create table if not exists locations
351(
352	id serial not null
353		constraint locations_pkey
354			primary key,
355	loc_id integer not null,
356	loc_id_parent integer,
357	loc_name varchar(255),
358	loc_name_canonical varchar(255),
359	loc_type varchar(255),
360	loc_country_iso_code varchar(255),
361	dma_region boolean not null,
362	kwrd_finder boolean not null,
363	kwrd_finder_lang varchar(255),
364	created_at timestamp(0),
365	updated_at timestamp(0)
366)
367;
368
369alter table locations owner to postgres
370;
371
372create table if not exists ranktracker_project_segments
373(
374	id serial not null
375		constraint ranktracker_project_segments_pkey
376			primary key,
377	project_id integer not null,
378	se_id integer not null,
379	name varchar(255) not null,
380	filter json not null,
381	created_at timestamp(0),
382	updated_at timestamp(0)
383)
384;
385
386alter table ranktracker_project_segments owner to postgres
387;
388
389create index if not exists ranktracker_project_segments_project_id_index
390	on ranktracker_project_segments (project_id)
391;
392
393create index if not exists ranktracker_project_segments_se_id_index
394	on ranktracker_project_segments (se_id)
395;
396
397create table if not exists ranktracker_segment_stats
398(
399	id serial not null
400		constraint ranktracker_segment_stats_pkey
401			primary key,
402	segment_id integer not null,
403	total_kws integer not null,
404	avg_sv integer not null,
405	avg_position integer not null,
406	created_at timestamp(0),
407	updated_at timestamp(0)
408)
409;
410
411alter table ranktracker_segment_stats owner to postgres
412;
413
414create index if not exists ranktracker_segment_stats_segment_id_index
415	on ranktracker_segment_stats (segment_id)
416;
417
418create table if not exists ranktracker_keywords_segments
419(
420	key_id bigint not null,
421	segment_id integer not null
422)
423;
424
425alter table ranktracker_keywords_segments owner to postgres
426;
427
428create index if not exists ranktracker_keywords_segments_key_id_index
429	on ranktracker_keywords_segments (key_id)
430;
431
432create index if not exists ranktracker_keywords_segments_segment_id_index
433	on ranktracker_keywords_segments (segment_id)
434;
435
436create table if not exists ranktracker_project_rankings
437(
438	project_id integer not null,
439	main_job_id integer not null,
440	key_id integer not null,
441	position integer not null,
442	relevant_url varchar(255) not null,
443	created_at timestamp(0),
444	updated_at timestamp(0),
445	constraint ranktracker_project_rankings_project_id_main_job_id_key_id_rele
446		unique (project_id, main_job_id, key_id, relevant_url, position)
447)
448;
449
450alter table ranktracker_project_rankings owner to postgres
451;
452
453create index if not exists ranktracker_project_rankings_main_job_id_index
454	on ranktracker_project_rankings (main_job_id)
455;
456
457create index if not exists ranktracker_project_rankings_key_id_index
458	on ranktracker_project_rankings (key_id)
459;
460
461create table if not exists ranktracker_plans
462(
463	id serial not null
464		constraint ranktracker_plans_pkey
465			primary key,
466	name varchar(255) not null,
467	price double precision not null,
468	request_price double precision not null,
469	created_at timestamp(0),
470	updated_at timestamp(0)
471)
472;
473
474alter table ranktracker_plans owner to postgres
475;
476
477create table if not exists user_billing
478(
479	id serial not null
480		constraint user_billing_pkey
481			primary key,
482	user_id integer not null,
483	sum integer not null,
484	user_plan_id integer not null,
485	be_paid_on date not null,
486	type char(40) not null,
487	is_payed integer not null,
488	created_at timestamp(0),
489	updated_at timestamp(0),
490	constraint user_billing_user_id_user_plan_id_be_paid_on_unique
491		unique (user_id, user_plan_id, be_paid_on)
492)
493;
494
495comment on column user_billing.is_payed is '0 - not paid,  1 paid'
496;
497
498alter table user_billing owner to postgres
499;
500
501create table if not exists user_billing_processed_keywords
502(
503	id serial not null
504		constraint user_billing_processed_keywords_pkey
505			primary key,
506	billing_id integer not null,
507	processed_keywords integer not null,
508	billing_date date not null,
509	created_at timestamp(0),
510	updated_at timestamp(0)
511)
512;
513
514alter table user_billing_processed_keywords owner to postgres
515;
516
517create index if not exists user_billing_processed_keywords_billing_id_index
518	on user_billing_processed_keywords (billing_id)
519;
520
521create table if not exists notificattions
522(
523	id serial not null
524		constraint notificattions_pkey
525			primary key,
526	updated_date date not null,
527	ads_found integer default 0 not null,
528	project_id integer not null,
529	was_sent_at timestamp(0),
530	created_at timestamp(0),
531	updated_at timestamp(0),
532	constraint notificattions_updated_date_project_id_unique
533		unique (updated_date, project_id)
534)
535;
536
537alter table notificattions owner to postgres
538;
539
540create table if not exists subscriptions
541(
542	id serial not null
543		constraint subscriptions_pkey
544			primary key,
545	name varchar(255) not null
546		constraint subscriptions_name_unique
547			unique,
548	price_per_month double precision not null,
549	projects_count smallint not null,
550	keywords_per_project smallint not null,
551	regions_per_project smallint not null,
552	created_at timestamp(0),
553	updated_at timestamp(0)
554)
555;
556
557alter table subscriptions owner to postgres
558;
559
560create table if not exists user_subscription
561(
562	id serial not null
563		constraint user_subscription_pkey
564			primary key,
565	user_id integer not null
566		constraint user_subscription_user_id_foreign
567			references users
568				on delete cascade,
569	subscription_id smallint not null,
570	activated_at timestamp(0) not null,
571	expired_at timestamp(0) not null,
572	created_at timestamp(0),
573	updated_at timestamp(0),
574	constraint user_subscription_user_id_subscription_id_unique
575		unique (user_id, subscription_id)
576)
577;
578
579alter table user_subscription owner to postgres
580;
581
582create index if not exists user_subscription_subscription_id_index
583	on user_subscription (subscription_id)
584;
585
586create table if not exists payments
587(
588	id serial not null
589		constraint payments_pkey
590			primary key,
591	event_id text not null,
592	order_id text not null,
593	payer_name text not null,
594	payer_last_name text not null,
595	payer_phone text,
596	payer_email text not null,
597	"invoiceUrl" text,
598	account_id text,
599	user_id integer not null,
600	user_email text not null,
601	user_last_ip text not null,
602	product_name text not null,
603	subscribe_id text not null,
604	status varchar(255) not null
605		constraint payments_status_check
606			check ((status)::text = ANY ((ARRAY['completed'::character varying, 'pending'::character varying, 'canceled'::character varying, 'decline'::character varying, 'updated'::character varying, 'processing'::character varying])::text[])),
607	price double precision not null,
608	created_at timestamp(0),
609	updated_at timestamp(0)
610)
611;
612
613alter table payments owner to postgres
614;