database_helper.php — LatePoint – Calendar Booking Plugin for Appointments and Events 5.1.3

latepoint / lib / helpers / database_helper.php

latepoint / lib / helpers Last commit date

database_helper.php

830 lines

1	<?php
2
3	class OsDatabaseHelper {
4
5	public static function run_setup() {
6	self::install_database();
7	}
8
9	public static function check_db_version() {
10	$current_db_version = OsSettingsHelper::get_db_version();
11	if (!$current_db_version \|\| version_compare(LATEPOINT_DB_VERSION, $current_db_version)) {
12	self::install_database();
13	}
14	}
15
16	// [name => 'addon_name', 'db_version' => '1.0.0', 'version' => '1.0.0']
17	public static function get_installed_addons_list() {
18	$installed_addons = [];
19	$installed_addons = apply_filters('latepoint_installed_addons', $installed_addons);
20	return $installed_addons;
21	}
22
23
24	// Check if addons databases are up to date
25	public static function check_db_version_for_addons() {
26	$is_new_addon_db_version_available = false;
27	$installed_addons = self::get_installed_addons_list();
28	if (empty($installed_addons)) return;
29	foreach ($installed_addons as $installed_addon) {
30	$current_addon_db_version = get_option($installed_addon['name'] . '_addon_db_version');
31	if (!$current_addon_db_version \|\| version_compare($current_addon_db_version, $installed_addon['db_version'])) {
32	self::save_addon_info($installed_addon['name'], $installed_addon['db_version']);
33	$is_new_addon_db_version_available = true;
34	}
35	}
36	if ($is_new_addon_db_version_available) self::install_database_for_addons();
37	}
38
39
40	public static function save_addon_info($name, $version){
41	update_option( $name . '_addon_db_version', $version );
42	$active_addons = OsSettingsHelper::get_active_addons();
43	$active_addons[] = $name;
44	$active_addons = array_unique( $active_addons );
45	$verified_active_addons = [];
46	foreach($active_addons as $active_addon){
47	if(($active_addon == $name) \|\| is_plugin_active($active_addon.'/'.$active_addon.'.php')){
48	$verified_active_addons[] = $active_addon;
49	}
50	}
51	OsSettingsHelper::save_setting_by_name('active_addons', wp_json_encode($verified_active_addons));
52	}
53
54	public static function delete_addon_info($name, $version){
55	delete_option( $name . '_addon_db_version' );
56	$active_addons = OsSettingsHelper::get_active_addons();
57	$verified_active_addons = [];
58	foreach($active_addons as $active_addon){
59	if(($active_addon != $name) && is_plugin_active($active_addon.'/'.$active_addon.'.php')){
60	$verified_active_addons[] = $active_addon;
61	}
62	}
63	OsSettingsHelper::save_setting_by_name('active_addons', wp_json_encode($verified_active_addons));
64	}
65
66
67	// Install queries for addons
68	public static function install_database_for_addons() {
69	$sqls = self::get_table_queries_for_addons();
70	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
71	foreach ($sqls as $sql) {
72	error_log(print_r(dbDelta($sql), true));
73	}
74	}
75
76
77	public static function install_database() {
78	$sqls = self::get_initial_table_queries();
79	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
80	foreach ($sqls as $sql) {
81	error_log(print_r(dbDelta($sql), true));
82	}
83	OsVersionSpecificUpdatesHelper::run_version_specific_updates();
84	self::seed_initial_data();
85	update_option('latepoint_db_version', LATEPOINT_DB_VERSION);
86	}
87
88	public static function seed_initial_data() {
89	// if DB version is set (means that it's probably an update) skip seeding
90	if (OsSettingsHelper::get_db_version()) return false;
91	// if database was already seeded before - skip it
92	if (OsSettingsHelper::get_settings_value('is_database_seeded', false)) return false;
93
94	// set default booking status rules
95	OsSettingsHelper::save_setting_by_name('default_booking_status', LATEPOINT_BOOKING_STATUS_APPROVED);
96	OsSettingsHelper::save_setting_by_name('timeslot_blocking_statuses', LATEPOINT_BOOKING_STATUS_APPROVED);
97	OsSettingsHelper::save_setting_by_name('calendar_hidden_statuses', LATEPOINT_BOOKING_STATUS_CANCELLED);
98	OsSettingsHelper::save_setting_by_name('need_action_statuses', implode(',', [LATEPOINT_BOOKING_STATUS_PENDING, LATEPOINT_BOOKING_STATUS_PAYMENT_PENDING]));
99	// create default processes
100	$process = new OsProcessModel();
101	$process->event_type = 'booking_created';
102	$process->name = 'New Booking Notification';
103	$actions = [];
104
105	require_once ABSPATH . 'wp-admin/includes/file.php';
106	if ( ! WP_Filesystem() ) {
107	OsDebugHelper::log( __( 'Failed to initialise WC_Filesystem API while trying to setup notifications for initial data seed.', 'latepoint' ) );
108	}else{
109	global $wp_filesystem;
110	foreach (['agent', 'customer'] as $user_type) {
111	$action = [];
112	$action['type'] = 'send_email';
113	$action['settings']['to_email'] = '{{' . $user_type . '_full_name}} <{{' . $user_type . '_email}}>';
114	$action['settings']['subject'] = ($user_type == 'agent') ? "New Appointment Received" : "Appointment Confirmation";
115	$action['settings']['content'] = OsEmailHelper::get_email_layout($wp_filesystem->get_contents(LATEPOINT_VIEWS_ABSPATH . 'mailers/' . $user_type . '/booking_created.html'));
116	$actions[\LatePoint\Misc\ProcessAction::generate_id()] = $action;
117	}
118	}
119
120	$process_actions = OsProcessesHelper::iterate_trigger_conditions([], $actions);
121	$process_actions[0]['time_offset'] = [];
122	$process->actions_json = wp_json_encode($process_actions);
123	if (!OsProcessesHelper::check_if_process_exists($process)) $process->save();
124
125	/**
126	* Hook your initial data seed actions here
127	*
128	* @since 4.7.0
129	* @hook latepoint_seed_initial_data
130	*
131	*/
132	do_action('latepoint_seed_initial_data');
133	OsSettingsHelper::save_setting_by_name('is_database_seeded', true);
134
135	}
136
137	public static function run_query(string $sql) {
138	global $wpdb;
139	OsDebugHelper::log_query($sql);
140	return $wpdb->query($sql);
141	}
142
143	public static function run_queries($sqls) {
144	global $wpdb;
145	if ($sqls && is_array($sqls)) {
146	foreach ($sqls as $sql) {
147	$wpdb->query($sql);
148	OsDebugHelper::log_query($sql);
149	}
150	}
151	}
152
153
154	// Get queries registered by addons
155	public static function get_table_queries_for_addons() {
156	$sqls = [];
157	$sqls = apply_filters('latepoint_addons_sqls', $sqls);
158	return $sqls;
159	}
160
161	public static function get_all_latepoint_tables(){
162	$tables = [
163	LATEPOINT_TABLE_BUNDLES,
164	LATEPOINT_TABLE_JOIN_BUNDLES_SERVICES,
165	LATEPOINT_TABLE_BOOKINGS,
166	LATEPOINT_TABLE_SESSIONS,
167	LATEPOINT_TABLE_SERVICES,
168	LATEPOINT_TABLE_SETTINGS,
169	LATEPOINT_TABLE_SERVICE_CATEGORIES,
170	LATEPOINT_TABLE_WORK_PERIODS,
171	LATEPOINT_TABLE_CUSTOM_PRICES,
172	LATEPOINT_TABLE_AGENTS_SERVICES,
173	LATEPOINT_TABLE_ACTIVITIES,
174	LATEPOINT_TABLE_TRANSACTIONS,
175	LATEPOINT_TABLE_TRANSACTION_REFUNDS,
176	LATEPOINT_TABLE_TRANSACTION_INTENTS,
177	LATEPOINT_TABLE_AGENTS,
178	LATEPOINT_TABLE_CUSTOMERS,
179	LATEPOINT_TABLE_CUSTOMER_META,
180	LATEPOINT_TABLE_SERVICE_META,
181	LATEPOINT_TABLE_BOOKING_META,
182	LATEPOINT_TABLE_AGENT_META,
183	LATEPOINT_TABLE_STEPS,
184	LATEPOINT_TABLE_STEP_SETTINGS,
185	LATEPOINT_TABLE_LOCATIONS,
186	LATEPOINT_TABLE_LOCATION_CATEGORIES,
187	LATEPOINT_TABLE_PROCESSES,
188	LATEPOINT_TABLE_PROCESS_JOBS,
189	LATEPOINT_TABLE_CARTS,
190	LATEPOINT_TABLE_CART_META,
191	LATEPOINT_TABLE_CART_ITEMS,
192	LATEPOINT_TABLE_ORDERS,
193	LATEPOINT_TABLE_ORDER_META,
194	LATEPOINT_TABLE_ORDER_ITEMS,
195	LATEPOINT_TABLE_ORDER_INTENTS,
196	LATEPOINT_TABLE_ORDER_INTENT_META,
197	LATEPOINT_TABLE_ORDER_INVOICES,
198	LATEPOINT_TABLE_PAYMENT_REQUESTS
199	];
200	/**
201	* Get list of all tables that hold latepoint related data
202	*
203	* @since 5.1.3
204	* @hook get_all_latepoint_tables
205	*
206	* @param {array} $tables list of tables
207	* @returns {array} The filtered list of tables
208	*/
209	return apply_filters('get_all_latepoint_tables', $tables);
210
211	}
212
213	public static function get_initial_table_queries() {
214
215	global $wpdb;
216
217	$charset_collate = $wpdb->get_charset_collate();
218
219	$sqls = [];
220
221
222	// ---------------
223	// STEPS
224	// ---------------
225
226
227	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_STEPS . " (
228	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
229	title text,
230	before_content text,
231	after_content text,
232	side_title text,
233	side_description text,
234	use_custom_image boolean,
235	custom_image_id int(11),
236	code varchar(100),
237	parent_step_id smallint(6),
238	position smallint(6),
239	created_at datetime,
240	updated_at datetime,
241	PRIMARY KEY (id)
242	) $charset_collate;";
243
244	// ---------------
245	// CART
246	// ---------------
247
248	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CARTS . " (
249	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
250	uuid varchar(36),
251	order_intent_id int(11),
252	order_id int(11),
253	coupon_code varchar(100),
254	created_at datetime,
255	updated_at datetime,
256	PRIMARY KEY (id),
257	KEY uuid_index (uuid),
258	KEY order_id_index (order_id),
259	KEY order_intent_id_index (order_intent_id)
260	) $charset_collate;";
261
262	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CART_ITEMS . " (
263	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
264	cart_id int(11) NOT NULL,
265	variant varchar(55),
266	item_data text,
267	created_at datetime,
268	updated_at datetime,
269	PRIMARY KEY (id),
270	KEY cart_id_index (cart_id)
271	) $charset_collate;";
272
273
274	// ---------------
275	// ORDERS
276	// ---------------
277
278	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDERS . " (
279	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
280	subtotal decimal(20,4),
281	total decimal(20,4),
282	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_STATUS_OPEN . "' NOT NULL,
283	fulfillment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_FULFILLMENT_STATUS_NOT_FULFILLED . "' NOT NULL,
284	payment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_PAYMENT_STATUS_NOT_PAID . "' NOT NULL,
285	source_id varchar(100),
286	source_url text,
287	ip_address varchar(55),
288	customer_id int(11) NOT NULL,
289	customer_comment text,
290	confirmation_code varchar(10),
291	price_breakdown text,
292	coupon_code varchar(100),
293	coupon_discount decimal(20,4),
294	tax_total decimal(20,4),
295	initial_payment_data text,
296	created_at datetime,
297	updated_at datetime,
298	PRIMARY KEY (id),
299	KEY customer_id_index (customer_id)
300	) $charset_collate;";
301
302	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_ITEMS . " (
303	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
304	order_id int(11) NOT NULL,
305	variant varchar(55),
306	item_data text,
307	subtotal decimal(20,4),
308	total decimal(20,4),
309	coupon_code varchar(100),
310	coupon_discount decimal(20,4),
311	tax_total decimal(20,4),
312	created_at datetime,
313	updated_at datetime,
314	PRIMARY KEY (id),
315	KEY order_id_index (order_id)
316	) $charset_collate;";
317
318
319	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_INTENTS . " (
320	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
321	intent_key varchar(55) NOT NULL,
322	customer_id int(11) NOT NULL,
323	cart_items_data text,
324	restrictions_data text,
325	presets_data text,
326	payment_data text,
327	other_data text,
328	order_id int(11),
329	booking_form_page_url text,
330	total decimal(20,4),
331	subtotal decimal(20,4),
332	coupon_code varchar(100),
333	coupon_discount decimal(20,4),
334	tax_total decimal(20,4),
335	charge_amount decimal(20,4),
336	specs_charge_amount varchar(55),
337	price_breakdown text,
338	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_INTENT_STATUS_NEW . "' NOT NULL,
339	created_at datetime,
340	updated_at datetime,
341	PRIMARY KEY (id),
342	UNIQUE KEY intent_key_index (intent_key)
343	) $charset_collate;";
344
345
346	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_INVOICES . " (
347	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
348	order_id int(11) NOT NULL,
349	invoice_number varchar(10),
350	data text,
351	status varchar(30) DEFAULT '" . LATEPOINT_INVOICE_STATUS_OPEN . "' NOT NULL,
352	charge_amount decimal(20,4),
353	due_at datetime,
354	payment_portion varchar(55),
355	access_key varchar(36),
356	created_at datetime,
357	updated_at datetime,
358	PRIMARY KEY (id),
359	KEY order_id_index (order_id),
360	UNIQUE KEY invoice_number_index (invoice_number)
361	) $charset_collate;";
362
363
364	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PAYMENT_REQUESTS . " (
365	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
366	order_id int(11) NOT NULL,
367	invoice_id int(11) NOT NULL,
368	charge_amount decimal(20,4),
369	due_at datetime,
370	portion varchar(55),
371	created_at datetime,
372	updated_at datetime,
373	PRIMARY KEY (id),
374	KEY invoice_id_index (invoice_id),
375	KEY order_id_index (order_id)
376	) $charset_collate;";
377
378
379	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PROCESS_JOBS . " (
380	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
381	process_id int(11) NOT NULL,
382	object_id int(11) NOT NULL,
383	object_model_type varchar(55),
384	settings text,
385	to_run_after_utc datetime,
386	status varchar(30) DEFAULT '" . LATEPOINT_JOB_STATUS_SCHEDULED . "',
387	run_result text,
388	process_info text,
389	created_at datetime,
390	updated_at datetime,
391	PRIMARY KEY (id)
392	) $charset_collate;";
393
394
395	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SESSIONS . " (
396	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
397	session_key varchar(55) NOT NULL,
398	session_value longtext NOT NULL,
399	expiration BIGINT UNSIGNED NOT NULL,
400	hash varchar(50) NOT NULL,
401	PRIMARY KEY (id),
402	UNIQUE KEY session_key (session_key)
403	) $charset_collate;";
404
405	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BOOKINGS . " (
406	id int(11) NOT NULL AUTO_INCREMENT,
407	booking_code varchar(10),
408	start_date date,
409	end_date date,
410	start_time mediumint(9),
411	end_time mediumint(9),
412	start_datetime_utc datetime,
413	end_datetime_utc datetime,
414	buffer_before mediumint(9) NOT NULL,
415	buffer_after mediumint(9) NOT NULL,
416	duration mediumint(9),
417	status varchar(30) DEFAULT '" . LATEPOINT_BOOKING_STATUS_PENDING . "' NOT NULL,
418	customer_id mediumint(9) NOT NULL,
419	service_id mediumint(9) NOT NULL,
420	agent_id mediumint(9) NOT NULL,
421	location_id mediumint(9),
422	order_item_id mediumint(9),
423	total_attendees mediumint(4),
424	created_at datetime,
425	updated_at datetime,
426	KEY start_date_index (start_date),
427	KEY end_date_index (end_date),
428	KEY status_index (status),
429	KEY customer_id_index (customer_id),
430	KEY service_id_index (service_id),
431	KEY agent_id_index (agent_id),
432	KEY location_id_index (location_id),
433	PRIMARY KEY (id)
434	) $charset_collate;";
435
436
437	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CART_META . " (
438	id mediumint(9) NOT NULL AUTO_INCREMENT,
439	object_id mediumint(9) NOT NULL,
440	meta_key varchar(110) NOT NULL,
441	meta_value text,
442	created_at datetime,
443	updated_at datetime,
444	KEY meta_key_index (meta_key),
445	KEY object_id_index (object_id),
446	PRIMARY KEY (id)
447	) $charset_collate;";
448
449	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_META . " (
450	id mediumint(9) NOT NULL AUTO_INCREMENT,
451	object_id mediumint(9) NOT NULL,
452	meta_key varchar(110) NOT NULL,
453	meta_value text,
454	created_at datetime,
455	updated_at datetime,
456	KEY meta_key_index (meta_key),
457	KEY object_id_index (object_id),
458	PRIMARY KEY (id)
459	) $charset_collate;";
460
461
462	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BOOKING_META . " (
463	id mediumint(9) NOT NULL AUTO_INCREMENT,
464	object_id mediumint(9) NOT NULL,
465	meta_key varchar(110) NOT NULL,
466	meta_value text,
467	created_at datetime,
468	updated_at datetime,
469	KEY meta_key_index (meta_key),
470	KEY object_id_index (object_id),
471	PRIMARY KEY (id)
472	) $charset_collate;";
473
474
475	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PROCESSES . " (
476	id mediumint(9) NOT NULL AUTO_INCREMENT,
477	name varchar(110) NOT NULL,
478	event_type varchar(110) NOT NULL,
479	actions_json text,
480	status varchar(30) DEFAULT '" . LATEPOINT_STATUS_ACTIVE . "',
481	created_at datetime,
482	updated_at datetime,
483	PRIMARY KEY (id)
484	) $charset_collate;";
485
486	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICE_META . " (
487	id mediumint(9) NOT NULL AUTO_INCREMENT,
488	object_id mediumint(9) NOT NULL,
489	meta_key varchar(110) NOT NULL,
490	meta_value text,
491	created_at datetime,
492	updated_at datetime,
493	KEY meta_key_index (meta_key),
494	KEY object_id_index (object_id),
495	PRIMARY KEY (id)
496	) $charset_collate;";
497
498	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOMER_META . " (
499	id mediumint(9) NOT NULL AUTO_INCREMENT,
500	object_id mediumint(9) NOT NULL,
501	meta_key varchar(110) NOT NULL,
502	meta_value text,
503	created_at datetime,
504	updated_at datetime,
505	KEY meta_key_index (meta_key),
506	KEY object_id_index (object_id),
507	PRIMARY KEY (id)
508	) $charset_collate;";
509
510	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENT_META . " (
511	id mediumint(9) NOT NULL AUTO_INCREMENT,
512	object_id mediumint(9) NOT NULL,
513	meta_key varchar(110) NOT NULL,
514	meta_value text,
515	created_at datetime,
516	updated_at datetime,
517	KEY meta_key_index (meta_key),
518	KEY object_id_index (object_id),
519	PRIMARY KEY (id)
520	) $charset_collate;";
521
522
523	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SETTINGS . " (
524	id mediumint(9) NOT NULL AUTO_INCREMENT,
525	name varchar(110) NOT NULL,
526	value longtext,
527	created_at datetime,
528	updated_at datetime,
529	KEY name_index (name),
530	PRIMARY KEY (id)
531	) $charset_collate;";
532
533
534	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_LOCATIONS . " (
535	id mediumint(9) NOT NULL AUTO_INCREMENT,
536	name varchar(255) NOT NULL,
537	full_address text,
538	status varchar(20) NOT NULL,
539	category_id int(11),
540	order_number int(11),
541	selection_image_id int(11),
542	created_at datetime,
543	updated_at datetime,
544	KEY status_index (status),
545	PRIMARY KEY (id)
546	) $charset_collate;";
547
548
549	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_LOCATION_CATEGORIES . " (
550	id mediumint(9) NOT NULL AUTO_INCREMENT,
551	name varchar(100) NOT NULL,
552	short_description text,
553	parent_id mediumint(9),
554	selection_image_id int(11),
555	order_number int(11),
556	created_at datetime,
557	updated_at datetime,
558	KEY order_number_index (order_number),
559	KEY parent_id_index (parent_id),
560	PRIMARY KEY (id)
561	) $charset_collate;";
562
563
564	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BUNDLES . " (
565	id mediumint(9) NOT NULL AUTO_INCREMENT,
566	name varchar(255) NOT NULL,
567	short_description text,
568	charge_amount decimal(20,4),
569	deposit_amount decimal(20,4),
570	status varchar(20) NOT NULL,
571	visibility varchar(20) NOT NULL,
572	order_number int(11),
573	created_at datetime,
574	updated_at datetime,
575	KEY order_number_index (order_number),
576	KEY status_index (status),
577	PRIMARY KEY (id)
578	) $charset_collate;";
579
580
581	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_JOIN_BUNDLES_SERVICES . " (
582	id mediumint(9) NOT NULL AUTO_INCREMENT,
583	bundle_id mediumint(9),
584	service_id mediumint(9),
585	total_attendees mediumint(4),
586	duration int(11),
587	quantity mediumint(4),
588	created_at datetime,
589	updated_at datetime,
590	KEY bundle_id_index (bundle_id),
591	KEY service_id_index (service_id),
592	PRIMARY KEY (id)
593	) $charset_collate;";
594
595	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICES . " (
596	id mediumint(9) NOT NULL AUTO_INCREMENT,
597	name varchar(255) NOT NULL,
598	short_description text,
599	is_price_variable boolean,
600	price_min decimal(20,4),
601	price_max decimal(20,4),
602	charge_amount decimal(20,4),
603	deposit_amount decimal(20,4),
604	is_deposit_required boolean,
605	duration_name varchar(255),
606	override_default_booking_status varchar(255),
607	duration int(11) NOT NULL,
608	buffer_before int(11),
609	buffer_after int(11),
610	category_id int(11),
611	order_number int(11),
612	selection_image_id int(11),
613	description_image_id int(11),
614	bg_color varchar(20),
615	timeblock_interval int(11),
616	capacity_min int(4),
617	capacity_max int(4),
618	status varchar(20) NOT NULL,
619	visibility varchar(20) NOT NULL,
620	created_at datetime,
621	updated_at datetime,
622	KEY category_id_index (category_id),
623	KEY order_number_index (order_number),
624	KEY status_index (status),
625	PRIMARY KEY (id)
626	) $charset_collate;";
627
628	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENTS . " (
629	id mediumint(9) NOT NULL AUTO_INCREMENT,
630	avatar_image_id int(11),
631	bio_image_id int(11),
632	first_name varchar(255) NOT NULL,
633	last_name varchar(255),
634	display_name varchar(255),
635	title varchar(255),
636	bio text,
637	features text,
638	email varchar(110) NOT NULL,
639	phone varchar(255),
640	password varchar(255),
641	custom_hours boolean,
642	wp_user_id int(11),
643	status varchar(20) NOT NULL,
644	extra_emails text,
645	extra_phones text,
646	created_at datetime,
647	updated_at datetime,
648	KEY email_index (email),
649	PRIMARY KEY (id)
650	) $charset_collate;";
651
652	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_STEP_SETTINGS . " (
653	id mediumint(9) NOT NULL AUTO_INCREMENT,
654	label varchar(50) NOT NULL,
655	value text,
656	step varchar(50),
657	created_at datetime,
658	updated_at datetime,
659	KEY step_index (step),
660	KEY label_index (label),
661	PRIMARY KEY (id)
662	) $charset_collate;";
663
664
665	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOMERS . " (
666	id mediumint(9) NOT NULL AUTO_INCREMENT,
667	first_name varchar(255),
668	last_name varchar(255),
669	email varchar(110) NOT NULL,
670	phone varchar(255),
671	avatar_image_id int(11),
672	status varchar(50) NOT NULL,
673	password varchar(255),
674	activation_key varchar(255),
675	account_nonse varchar(255),
676	google_user_id varchar(255),
677	facebook_user_id varchar(255),
678	wordpress_user_id int(11),
679	is_guest boolean,
680	notes text,
681	admin_notes text,
682	created_at datetime,
683	updated_at datetime,
684	KEY email_index (email),
685	KEY status_index (status),
686	KEY wordpress_user_id_index (wordpress_user_id),
687	PRIMARY KEY (id)
688	) $charset_collate;";
689
690	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICE_CATEGORIES . " (
691	id mediumint(9) NOT NULL AUTO_INCREMENT,
692	name varchar(100) NOT NULL,
693	short_description text,
694	parent_id mediumint(9),
695	selection_image_id int(11),
696	order_number int(11),
697	created_at datetime,
698	updated_at datetime,
699	KEY order_number_index (order_number),
700	KEY parent_id_index (parent_id),
701	PRIMARY KEY (id)
702	) $charset_collate;";
703
704	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOM_PRICES . " (
705	id mediumint(9) NOT NULL AUTO_INCREMENT,
706	agent_id int(11) NOT NULL,
707	service_id int(11) NOT NULL,
708	location_id int(11) NOT NULL,
709	is_price_variable boolean,
710	price_min decimal(20,4),
711	price_max decimal(20,4),
712	charge_amount decimal(20,4),
713	is_deposit_required boolean,
714	deposit_amount decimal(20,4),
715	created_at datetime,
716	updated_at datetime,
717	KEY agent_id_index (agent_id),
718	KEY service_id_index (service_id),
719	KEY location_id_index (location_id),
720	PRIMARY KEY (id)
721	) $charset_collate;";
722
723	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_WORK_PERIODS . " (
724	id mediumint(9) NOT NULL AUTO_INCREMENT,
725	agent_id int(11) NOT NULL,
726	service_id int(11) NOT NULL,
727	location_id int(11) NOT NULL,
728	start_time smallint(6) NOT NULL,
729	end_time smallint(6) NOT NULL,
730	week_day tinyint(3) NOT NULL,
731	custom_date date,
732	chain_id varchar(20),
733	created_at datetime,
734	updated_at datetime,
735	KEY agent_id_index (agent_id),
736	KEY service_id_index (service_id),
737	KEY location_id_index (location_id),
738	KEY week_day_index (week_day),
739	KEY custom_date_index (custom_date),
740	PRIMARY KEY (id)
741	) $charset_collate;";
742
743	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENTS_SERVICES . " (
744	id mediumint(9) NOT NULL AUTO_INCREMENT,
745	agent_id int(11) NOT NULL,
746	service_id int(11) NOT NULL,
747	location_id int(11),
748	is_custom_hours BOOLEAN,
749	is_custom_price BOOLEAN,
750	is_custom_duration BOOLEAN,
751	created_at datetime,
752	updated_at datetime,
753	KEY agent_id_index (agent_id),
754	KEY service_id_index (service_id),
755	KEY location_id_index (location_id),
756	PRIMARY KEY (id)
757	) $charset_collate;";
758
759	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ACTIVITIES . " (
760	id mediumint(9) NOT NULL AUTO_INCREMENT,
761	agent_id int(11),
762	booking_id int(11),
763	service_id int(11),
764	customer_id int(11),
765	location_id int(11),
766	order_id int(11),
767	order_item_id int(11),
768	code varchar(255) NOT NULL,
769	description text,
770	initiated_by varchar(100),
771	initiated_by_id int(11),
772	created_at datetime,
773	updated_at datetime,
774	PRIMARY KEY (id)
775	) $charset_collate;";
776
777
778	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_TRANSACTION_INTENTS . " (
779	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
780	intent_key varchar(55) NOT NULL,
781	order_id int(11) NOT NULL,
782	customer_id int(11),
783	invoice_id int(11),
784	transaction_id int(11),
785	payment_data text,
786	charge_amount decimal(20,4),
787	specs_charge_amount varchar(55),
788	status varchar(30) DEFAULT '" . LATEPOINT_TRANSACTION_INTENT_STATUS_NEW . "' NOT NULL,
789	created_at datetime,
790	updated_at datetime,
791	PRIMARY KEY (id),
792	UNIQUE KEY intent_key_index (intent_key)
793	) $charset_collate;";
794
795	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_TRANSACTIONS . " (
796	id mediumint(9) NOT NULL AUTO_INCREMENT,
797	token text,
798	invoice_id int(11),
799	order_id int(11),
800	customer_id int(11),
801	processor varchar(100),
802	payment_method varchar(55),
803	payment_portion varchar(55),
804	kind varchar(40),
805	status varchar(100) NOT NULL,
806	amount decimal(20,4),
807	receipt_number varchar(10),
808	access_key varchar(36),
809	notes text,
810	created_at datetime,
811	updated_at datetime,
812	PRIMARY KEY (id)
813	) $charset_collate;";
814
815
816	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_TRANSACTION_REFUNDS . " (
817	id mediumint(9) NOT NULL AUTO_INCREMENT,
818	token text,
819	transaction_id int(11),
820	amount decimal(20,4),
821	created_at datetime,
822	updated_at datetime,
823	PRIMARY KEY (id)
824	) $charset_collate;";
825
826	return $sqls;
827	}
828
829
830	}