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

latepoint / lib / helpers / database_helper.php

latepoint / lib / helpers Last commit date

database_helper.php

931 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	public static function run_query( string $sql ) {
155	global $wpdb;
156	OsDebugHelper::log_query( $sql );
157
158	return $wpdb->query( $sql );
159	}
160
161	public static function run_queries( $sqls ) {
162	global $wpdb;
163	if ( $sqls && is_array( $sqls ) ) {
164	foreach ( $sqls as $sql ) {
165	$wpdb->query( $sql );
166	OsDebugHelper::log_query( $sql );
167	}
168	}
169	}
170
171
172	// Get queries registered by addons
173	public static function get_table_queries_for_addons() {
174	$sqls = [];
175	$sqls = apply_filters( 'latepoint_addons_sqls', $sqls );
176
177	return $sqls;
178	}
179
180	public static function get_all_latepoint_tables() {
181	$tables = [
182	LATEPOINT_TABLE_BUNDLES,
183	LATEPOINT_TABLE_JOIN_BUNDLES_SERVICES,
184	LATEPOINT_TABLE_BOOKINGS,
185	LATEPOINT_TABLE_SESSIONS,
186	LATEPOINT_TABLE_SERVICES,
187	LATEPOINT_TABLE_SETTINGS,
188	LATEPOINT_TABLE_SERVICE_CATEGORIES,
189	LATEPOINT_TABLE_WORK_PERIODS,
190	LATEPOINT_TABLE_CUSTOM_PRICES,
191	LATEPOINT_TABLE_AGENTS_SERVICES,
192	LATEPOINT_TABLE_ACTIVITIES,
193	LATEPOINT_TABLE_TRANSACTIONS,
194	LATEPOINT_TABLE_TRANSACTION_REFUNDS,
195	LATEPOINT_TABLE_TRANSACTION_INTENTS,
196	LATEPOINT_TABLE_AGENTS,
197	LATEPOINT_TABLE_CUSTOMERS,
198	LATEPOINT_TABLE_CUSTOMER_META,
199	LATEPOINT_TABLE_SERVICE_META,
200	LATEPOINT_TABLE_BOOKING_META,
201	LATEPOINT_TABLE_AGENT_META,
202	LATEPOINT_TABLE_STEPS,
203	LATEPOINT_TABLE_STEP_SETTINGS,
204	LATEPOINT_TABLE_LOCATIONS,
205	LATEPOINT_TABLE_LOCATION_CATEGORIES,
206	LATEPOINT_TABLE_PROCESSES,
207	LATEPOINT_TABLE_PROCESS_JOBS,
208	LATEPOINT_TABLE_CARTS,
209	LATEPOINT_TABLE_CART_META,
210	LATEPOINT_TABLE_CART_ITEMS,
211	LATEPOINT_TABLE_ORDERS,
212	LATEPOINT_TABLE_ORDER_META,
213	LATEPOINT_TABLE_ORDER_ITEMS,
214	LATEPOINT_TABLE_ORDER_INTENTS,
215	LATEPOINT_TABLE_ORDER_INTENT_META,
216	LATEPOINT_TABLE_ORDER_INVOICES,
217	LATEPOINT_TABLE_PAYMENT_REQUESTS,
218	LATEPOINT_TABLE_RECURRENCES,
219	LATEPOINT_TABLE_CUSTOMER_OTP_CODES,
220	];
221
222	/**
223	* Get list of all tables that hold latepoint related data
224	*
225	* @param {array} $tables list of tables
226	* @returns {array} The filtered list of tables
227	*
228	* @since 5.1.3
229	* @hook get_all_latepoint_tables
230	*
231	*/
232	return apply_filters( 'get_all_latepoint_tables', $tables );
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	/* OTP Codes */
245	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CUSTOMER_OTP_CODES . " (
246	id mediumint(9) NOT NULL AUTO_INCREMENT,
247	contact_value varchar(255) NOT NULL,
248	contact_type varchar(30) NOT NULL,
249	delivery_method varchar(30) NOT NULL,
250	otp_hash VARCHAR(255) NOT NULL,
251	expires_at datetime not null,
252	status varchar(30) DEFAULT '" . LATEPOINT_CUSTOMER_OTP_CODE_STATUS_ACTIVE . "' NOT NULL,
253	attempts INT DEFAULT 0,
254	used_at DATETIME NULL,
255	created_at datetime,
256	updated_at datetime,
257	PRIMARY KEY (id),
258	KEY idx_contact_status (contact_value, status),
259	KEY idx_expires_status (expires_at, status)
260	) $charset_collate;";
261
262
263	/* Recurrences */
264	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_RECURRENCES . " (
265	id mediumint(9) NOT NULL AUTO_INCREMENT,
266	rules text,
267	overrides text,
268	created_at datetime,
269	updated_at datetime,
270	PRIMARY KEY (id)
271	) $charset_collate;";
272
273
274	// ---------------
275	// STEPS
276	// ---------------
277
278
279	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_STEPS . " (
280	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
281	title text,
282	before_content text,
283	after_content text,
284	side_title text,
285	side_description text,
286	use_custom_image boolean,
287	custom_image_id int(11),
288	code varchar(100),
289	parent_step_id smallint(6),
290	position smallint(6),
291	created_at datetime,
292	updated_at datetime,
293	PRIMARY KEY (id)
294	) $charset_collate;";
295
296	// ---------------
297	// CART
298	// ---------------
299
300	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CARTS . " (
301	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
302	uuid varchar(36),
303	order_intent_id int(11),
304	order_id int(11),
305	coupon_code varchar(100),
306	created_at datetime,
307	updated_at datetime,
308	PRIMARY KEY (id),
309	KEY uuid_index (uuid),
310	KEY order_id_index (order_id),
311	KEY order_intent_id_index (order_intent_id)
312	) $charset_collate;";
313
314	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CART_ITEMS . " (
315	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
316	cart_id int(11) NOT NULL,
317	variant varchar(55),
318	item_data text,
319	connected_cart_item_id int(11),
320	created_at datetime,
321	updated_at datetime,
322	PRIMARY KEY (id),
323	KEY connected_cart_item_id_index (connected_cart_item_id),
324	KEY cart_id_index (cart_id)
325	) $charset_collate;";
326
327
328	// ---------------
329	// ORDERS
330	// ---------------
331
332	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ORDERS . " (
333	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
334	subtotal decimal(20,4),
335	total decimal(20,4),
336	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_STATUS_OPEN . "' NOT NULL,
337	fulfillment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_FULFILLMENT_STATUS_NOT_FULFILLED . "' NOT NULL,
338	payment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_PAYMENT_STATUS_NOT_PAID . "' NOT NULL,
339	source_id varchar(100),
340	source_url text,
341	ip_address varchar(55),
342	customer_id int(11) NOT NULL,
343	customer_comment text,
344	confirmation_code varchar(10),
345	price_breakdown text,
346	coupon_code varchar(100),
347	coupon_discount decimal(20,4),
348	tax_total decimal(20,4),
349	initial_payment_data text,
350	created_at datetime,
351	updated_at datetime,
352	PRIMARY KEY (id),
353	KEY customer_id_index (customer_id)
354	) $charset_collate;";
355
356	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ORDER_ITEMS . " (
357	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
358	order_id int(11) NOT NULL,
359	variant varchar(55),
360	item_data text,
361	subtotal decimal(20,4),
362	total decimal(20,4),
363	coupon_code varchar(100),
364	coupon_discount decimal(20,4),
365	tax_total decimal(20,4),
366	created_at datetime,
367	updated_at datetime,
368	PRIMARY KEY (id),
369	KEY order_id_index (order_id)
370	) $charset_collate;";
371
372
373	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ORDER_INTENTS . " (
374	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
375	intent_key varchar(55) NOT NULL,
376	customer_id int(11) NOT NULL,
377	cart_items_data text,
378	restrictions_data text,
379	presets_data text,
380	payment_data text,
381	other_data text,
382	order_id int(11),
383	booking_form_page_url text,
384	total decimal(20,4),
385	subtotal decimal(20,4),
386	coupon_code varchar(100),
387	coupon_discount decimal(20,4),
388	tax_total decimal(20,4),
389	charge_amount decimal(20,4),
390	specs_charge_amount varchar(55),
391	price_breakdown text,
392	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_INTENT_STATUS_NEW . "' NOT NULL,
393	created_at datetime,
394	updated_at datetime,
395	PRIMARY KEY (id),
396	UNIQUE KEY intent_key_index (intent_key)
397	) $charset_collate;";
398
399
400	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ORDER_INVOICES . " (
401	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
402	order_id int(11) NOT NULL,
403	invoice_number varchar(10),
404	data text,
405	status varchar(30) DEFAULT '" . LATEPOINT_INVOICE_STATUS_OPEN . "' NOT NULL,
406	charge_amount decimal(20,4),
407	due_at datetime,
408	payment_portion varchar(55),
409	access_key varchar(36),
410	created_at datetime,
411	updated_at datetime,
412	PRIMARY KEY (id),
413	KEY order_id_index (order_id),
414	UNIQUE KEY invoice_number_index (invoice_number)
415	) $charset_collate;";
416
417
418	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_PAYMENT_REQUESTS . " (
419	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
420	order_id int(11) NOT NULL,
421	invoice_id int(11) NOT NULL,
422	charge_amount decimal(20,4),
423	due_at datetime,
424	`portion` varchar(55),
425	created_at datetime,
426	updated_at datetime,
427	PRIMARY KEY (id),
428	KEY invoice_id_index (invoice_id),
429	KEY order_id_index (order_id)
430	) $charset_collate;";
431
432
433	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_PROCESS_JOBS . " (
434	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
435	process_id int(11) NOT NULL,
436	object_id int(11) NOT NULL,
437	object_model_type varchar(55),
438	settings text,
439	to_run_after_utc datetime,
440	status varchar(30) DEFAULT '" . LATEPOINT_JOB_STATUS_SCHEDULED . "',
441	run_result text,
442	process_info text,
443	created_at datetime,
444	updated_at datetime,
445	PRIMARY KEY (id)
446	) $charset_collate;";
447
448
449	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SESSIONS . " (
450	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
451	session_key varchar(55) NOT NULL,
452	session_value longtext NOT NULL,
453	expiration BIGINT UNSIGNED NOT NULL,
454	hash varchar(50) NOT NULL,
455	PRIMARY KEY (id),
456	UNIQUE KEY session_key (session_key)
457	) $charset_collate;";
458
459	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_BOOKINGS . " (
460	id int(11) NOT NULL AUTO_INCREMENT,
461	booking_code varchar(10),
462	start_date date,
463	end_date date,
464	start_time mediumint(9),
465	end_time mediumint(9),
466	start_datetime_utc datetime,
467	end_datetime_utc datetime,
468	buffer_before mediumint(9) NOT NULL,
469	buffer_after mediumint(9) NOT NULL,
470	duration mediumint(9),
471	status varchar(30) DEFAULT '" . LATEPOINT_BOOKING_STATUS_PENDING . "' NOT NULL,
472	customer_id mediumint(9) NOT NULL,
473	service_id mediumint(9) NOT NULL,
474	agent_id mediumint(9) NOT NULL,
475	location_id mediumint(9),
476	order_item_id mediumint(9),
477	recurrence_id mediumint(9),
478	total_attendees mediumint(4),
479	customer_timezone varchar(100),
480	server_timezone varchar(100),
481	created_at datetime,
482	updated_at datetime,
483	KEY start_date_index (start_date),
484	KEY end_date_index (end_date),
485	KEY status_index (status),
486	KEY customer_id_index (customer_id),
487	KEY service_id_index (service_id),
488	KEY agent_id_index (agent_id),
489	KEY location_id_index (location_id),
490	KEY recurrence_id_index (recurrence_id),
491	PRIMARY KEY (id)
492	) $charset_collate;";
493
494
495	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_BLOCKED_PERIODS . " (
496	id int(11) NOT NULL AUTO_INCREMENT,
497	summary text,
498	start_date date,
499	end_date date,
500	start_time mediumint(9),
501	end_time mediumint(9),
502	start_datetime_utc datetime,
503	end_datetime_utc datetime,
504	service_id mediumint(9),
505	agent_id mediumint(9),
506	location_id mediumint(9),
507	server_timezone varchar(100),
508	created_at datetime,
509	updated_at datetime,
510	KEY start_date_index (start_date),
511	KEY end_date_index (end_date),
512	KEY service_id_index (service_id),
513	KEY agent_id_index (agent_id),
514	KEY location_id_index (location_id),
515	PRIMARY KEY (id)
516	) $charset_collate;";
517
518
519	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CART_META . " (
520	id mediumint(9) NOT NULL AUTO_INCREMENT,
521	object_id mediumint(9) NOT NULL,
522	meta_key varchar(110) NOT NULL,
523	meta_value text,
524	created_at datetime,
525	updated_at datetime,
526	KEY meta_key_index (meta_key),
527	KEY object_id_index (object_id),
528	PRIMARY KEY (id)
529	) $charset_collate;";
530
531	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ORDER_META . " (
532	id mediumint(9) NOT NULL AUTO_INCREMENT,
533	object_id mediumint(9) NOT NULL,
534	meta_key varchar(110) NOT NULL,
535	meta_value text,
536	created_at datetime,
537	updated_at datetime,
538	KEY meta_key_index (meta_key),
539	KEY object_id_index (object_id),
540	PRIMARY KEY (id)
541	) $charset_collate;";
542
543
544	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_BOOKING_META . " (
545	id mediumint(9) NOT NULL AUTO_INCREMENT,
546	object_id mediumint(9) NOT NULL,
547	meta_key varchar(110) NOT NULL,
548	meta_value text,
549	created_at datetime,
550	updated_at datetime,
551	KEY meta_key_index (meta_key),
552	KEY object_id_index (object_id),
553	PRIMARY KEY (id)
554	) $charset_collate;";
555
556
557	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_PROCESSES . " (
558	id mediumint(9) NOT NULL AUTO_INCREMENT,
559	name varchar(110) NOT NULL,
560	event_type varchar(110) NOT NULL,
561	actions_json text,
562	status varchar(30) DEFAULT '" . LATEPOINT_STATUS_ACTIVE . "',
563	created_at datetime,
564	updated_at datetime,
565	PRIMARY KEY (id)
566	) $charset_collate;";
567
568	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SERVICE_META . " (
569	id mediumint(9) NOT NULL AUTO_INCREMENT,
570	object_id mediumint(9) NOT NULL,
571	meta_key varchar(110) NOT NULL,
572	meta_value text,
573	created_at datetime,
574	updated_at datetime,
575	KEY meta_key_index (meta_key),
576	KEY object_id_index (object_id),
577	PRIMARY KEY (id)
578	) $charset_collate;";
579
580	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CUSTOMER_META . " (
581	id mediumint(9) NOT NULL AUTO_INCREMENT,
582	object_id mediumint(9) NOT NULL,
583	meta_key varchar(110) NOT NULL,
584	meta_value text,
585	created_at datetime,
586	updated_at datetime,
587	KEY meta_key_index (meta_key),
588	KEY object_id_index (object_id),
589	PRIMARY KEY (id)
590	) $charset_collate;";
591
592	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_AGENT_META . " (
593	id mediumint(9) NOT NULL AUTO_INCREMENT,
594	object_id mediumint(9) NOT NULL,
595	meta_key varchar(110) NOT NULL,
596	meta_value text,
597	created_at datetime,
598	updated_at datetime,
599	KEY meta_key_index (meta_key),
600	KEY object_id_index (object_id),
601	PRIMARY KEY (id)
602	) $charset_collate;";
603
604	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_BUNDLE_META . " (
605	id mediumint(9) NOT NULL AUTO_INCREMENT,
606	object_id mediumint(9) NOT NULL,
607	meta_key varchar(110) NOT NULL,
608	meta_value text,
609	created_at datetime,
610	updated_at datetime,
611	KEY meta_key_index (meta_key),
612	KEY object_id_index (object_id),
613	PRIMARY KEY (id)
614	) $charset_collate;";
615
616
617	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SETTINGS . " (
618	id mediumint(9) NOT NULL AUTO_INCREMENT,
619	name varchar(110) NOT NULL,
620	value longtext,
621	created_at datetime,
622	updated_at datetime,
623	KEY name_index (name),
624	PRIMARY KEY (id)
625	) $charset_collate;";
626
627
628	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_LOCATIONS . " (
629	id mediumint(9) NOT NULL AUTO_INCREMENT,
630	name varchar(255) NOT NULL,
631	full_address text,
632	status varchar(20) NOT NULL,
633	category_id int(11),
634	order_number int(11),
635	selection_image_id int(11),
636	created_at datetime,
637	updated_at datetime,
638	KEY status_index (status),
639	PRIMARY KEY (id)
640	) $charset_collate;";
641
642
643	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_LOCATION_CATEGORIES . " (
644	id mediumint(9) NOT NULL AUTO_INCREMENT,
645	name varchar(100) NOT NULL,
646	short_description text,
647	parent_id mediumint(9),
648	selection_image_id int(11),
649	order_number int(11),
650	created_at datetime,
651	updated_at datetime,
652	KEY order_number_index (order_number),
653	KEY parent_id_index (parent_id),
654	PRIMARY KEY (id)
655	) $charset_collate;";
656
657
658	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_BUNDLES . " (
659	id mediumint(9) NOT NULL AUTO_INCREMENT,
660	name varchar(255) NOT NULL,
661	short_description text,
662	charge_amount decimal(20,4),
663	deposit_amount decimal(20,4),
664	price_min decimal(20,4),
665	price_max decimal(20,4),
666	status varchar(20) NOT NULL,
667	visibility varchar(20) NOT NULL,
668	order_number int(11),
669	created_at datetime,
670	updated_at datetime,
671	KEY order_number_index (order_number),
672	KEY status_index (status),
673	PRIMARY KEY (id)
674	) $charset_collate;";
675
676
677	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_JOIN_BUNDLES_SERVICES . " (
678	id mediumint(9) NOT NULL AUTO_INCREMENT,
679	bundle_id mediumint(9),
680	service_id mediumint(9),
681	total_attendees mediumint(4),
682	duration int(11),
683	quantity mediumint(4),
684	created_at datetime,
685	updated_at datetime,
686	KEY bundle_id_index (bundle_id),
687	KEY service_id_index (service_id),
688	PRIMARY KEY (id)
689	) $charset_collate;";
690
691	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SERVICES . " (
692	id mediumint(9) NOT NULL AUTO_INCREMENT,
693	name varchar(255) NOT NULL,
694	short_description text,
695	is_price_variable boolean,
696	price_min decimal(20,4),
697	price_max decimal(20,4),
698	charge_amount decimal(20,4),
699	deposit_amount decimal(20,4),
700	is_deposit_required boolean,
701	duration_name varchar(255),
702	override_default_booking_status varchar(255),
703	duration int(11) NOT NULL,
704	buffer_before int(11),
705	buffer_after int(11),
706	category_id int(11),
707	order_number int(11),
708	selection_image_id int(11),
709	description_image_id int(11),
710	bg_color varchar(20),
711	earliest_possible_booking varchar(50),
712	latest_possible_booking varchar(50),
713	timeblock_interval int(11),
714	capacity_min int(4),
715	capacity_max int(4),
716	status varchar(20) NOT NULL,
717	visibility varchar(20) NOT NULL,
718	created_at datetime,
719	updated_at datetime,
720	KEY category_id_index (category_id),
721	KEY order_number_index (order_number),
722	KEY status_index (status),
723	PRIMARY KEY (id)
724	) $charset_collate;";
725
726	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_AGENTS . " (
727	id mediumint(9) NOT NULL AUTO_INCREMENT,
728	avatar_image_id int(11),
729	bio_image_id int(11),
730	first_name varchar(255) NOT NULL,
731	last_name varchar(255),
732	display_name varchar(255),
733	title varchar(255),
734	bio text,
735	features text,
736	email varchar(110) NOT NULL,
737	phone varchar(255),
738	password varchar(255),
739	custom_hours boolean,
740	wp_user_id int(11),
741	status varchar(20) NOT NULL,
742	extra_emails text,
743	extra_phones text,
744	created_at datetime,
745	updated_at datetime,
746	KEY email_index (email),
747	PRIMARY KEY (id)
748	) $charset_collate;";
749
750	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_STEP_SETTINGS . " (
751	id mediumint(9) NOT NULL AUTO_INCREMENT,
752	label varchar(50) NOT NULL,
753	value text,
754	step varchar(50),
755	created_at datetime,
756	updated_at datetime,
757	KEY step_index (step),
758	KEY label_index (label),
759	PRIMARY KEY (id)
760	) $charset_collate;";
761
762
763	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CUSTOMERS . " (
764	id mediumint(9) NOT NULL AUTO_INCREMENT,
765	uuid varchar(36),
766	first_name varchar(255),
767	last_name varchar(255),
768	email varchar(320),
769	phone varchar(255),
770	avatar_image_id int(11),
771	status varchar(50) NOT NULL,
772	password varchar(255),
773	activation_key varchar(255),
774	account_nonse varchar(255),
775	google_user_id varchar(255),
776	facebook_user_id varchar(255),
777	wordpress_user_id int(11),
778	is_guest boolean,
779	notes text,
780	admin_notes text,
781	created_at datetime,
782	updated_at datetime,
783	UNIQUE KEY uuid_index (uuid),
784	KEY email_index (email),
785	KEY status_index (status),
786	KEY wordpress_user_id_index (wordpress_user_id),
787	PRIMARY KEY (id)
788	) $charset_collate;";
789
790	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SERVICE_CATEGORIES . " (
791	id mediumint(9) NOT NULL AUTO_INCREMENT,
792	name varchar(100) NOT NULL,
793	short_description text,
794	parent_id mediumint(9),
795	selection_image_id int(11),
796	order_number int(11),
797	created_at datetime,
798	updated_at datetime,
799	KEY order_number_index (order_number),
800	KEY parent_id_index (parent_id),
801	PRIMARY KEY (id)
802	) $charset_collate;";
803
804	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CUSTOM_PRICES . " (
805	id mediumint(9) NOT NULL AUTO_INCREMENT,
806	agent_id int(11) NOT NULL,
807	service_id int(11) NOT NULL,
808	location_id int(11) NOT NULL,
809	is_price_variable boolean,
810	price_min decimal(20,4),
811	price_max decimal(20,4),
812	charge_amount decimal(20,4),
813	is_deposit_required boolean,
814	deposit_amount decimal(20,4),
815	created_at datetime,
816	updated_at datetime,
817	KEY agent_id_index (agent_id),
818	KEY service_id_index (service_id),
819	KEY location_id_index (location_id),
820	PRIMARY KEY (id)
821	) $charset_collate;";
822
823	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_WORK_PERIODS . " (
824	id mediumint(9) NOT NULL AUTO_INCREMENT,
825	agent_id int(11) NOT NULL,
826	service_id int(11) NOT NULL,
827	location_id int(11) NOT NULL,
828	start_time smallint(6) NOT NULL,
829	end_time smallint(6) NOT NULL,
830	week_day tinyint(3) NOT NULL,
831	custom_date date,
832	chain_id varchar(20),
833	created_at datetime,
834	updated_at datetime,
835	KEY agent_id_index (agent_id),
836	KEY service_id_index (service_id),
837	KEY location_id_index (location_id),
838	KEY week_day_index (week_day),
839	KEY custom_date_index (custom_date),
840	PRIMARY KEY (id)
841	) $charset_collate;";
842
843	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_AGENTS_SERVICES . " (
844	id mediumint(9) NOT NULL AUTO_INCREMENT,
845	agent_id int(11) NOT NULL,
846	service_id int(11) NOT NULL,
847	location_id int(11),
848	is_custom_hours BOOLEAN,
849	is_custom_price BOOLEAN,
850	is_custom_duration BOOLEAN,
851	created_at datetime,
852	updated_at datetime,
853	KEY agent_id_index (agent_id),
854	KEY service_id_index (service_id),
855	KEY location_id_index (location_id),
856	PRIMARY KEY (id)
857	) $charset_collate;";
858
859	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ACTIVITIES . " (
860	id mediumint(9) NOT NULL AUTO_INCREMENT,
861	agent_id int(11),
862	booking_id int(11),
863	service_id int(11),
864	customer_id int(11),
865	location_id int(11),
866	order_id int(11),
867	order_item_id int(11),
868	coupon_id int(11),
869	code varchar(255) NOT NULL,
870	description text,
871	initiated_by varchar(100),
872	initiated_by_id int(11),
873	created_at datetime,
874	updated_at datetime,
875	PRIMARY KEY (id)
876	) $charset_collate;";
877
878
879	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_TRANSACTION_INTENTS . " (
880	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
881	intent_key varchar(55) NOT NULL,
882	order_id int(11) NOT NULL,
883	customer_id int(11),
884	invoice_id int(11),
885	transaction_id int(11),
886	payment_data text,
887	charge_amount decimal(20,4),
888	specs_charge_amount varchar(55),
889	order_form_page_url text,
890	status varchar(30) DEFAULT '" . LATEPOINT_TRANSACTION_INTENT_STATUS_NEW . "' NOT NULL,
891	created_at datetime,
892	updated_at datetime,
893	PRIMARY KEY (id),
894	UNIQUE KEY intent_key_index (intent_key)
895	) $charset_collate;";
896
897	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_TRANSACTIONS . " (
898	id mediumint(9) NOT NULL AUTO_INCREMENT,
899	token text,
900	invoice_id int(11),
901	order_id int(11),
902	customer_id int(11),
903	processor varchar(100),
904	payment_method varchar(55),
905	payment_portion varchar(55),
906	kind varchar(40),
907	status varchar(100) NOT NULL,
908	amount decimal(20,4),
909	receipt_number varchar(10),
910	access_key varchar(36),
911	notes text,
912	created_at datetime,
913	updated_at datetime,
914	PRIMARY KEY (id)
915	) $charset_collate;";
916
917
918	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_TRANSACTION_REFUNDS . " (
919	id mediumint(9) NOT NULL AUTO_INCREMENT,
920	token text,
921	transaction_id int(11),
922	amount decimal(20,4),
923	created_at datetime,
924	updated_at datetime,
925	PRIMARY KEY (id)
926	) $charset_collate;";
927
928	return $sqls;
929	}
930	}
931