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

latepoint / lib / helpers / database_helper.php

latepoint / lib / helpers Last commit date

database_helper.php

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