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

latepoint / lib / helpers / database_helper.php

latepoint / lib / helpers Last commit date

database_helper.php

929 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	status varchar(20) NOT NULL,
665	visibility varchar(20) NOT NULL,
666	order_number int(11),
667	created_at datetime,
668	updated_at datetime,
669	KEY order_number_index (order_number),
670	KEY status_index (status),
671	PRIMARY KEY (id)
672	) $charset_collate;";
673
674
675	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_JOIN_BUNDLES_SERVICES . " (
676	id mediumint(9) NOT NULL AUTO_INCREMENT,
677	bundle_id mediumint(9),
678	service_id mediumint(9),
679	total_attendees mediumint(4),
680	duration int(11),
681	quantity mediumint(4),
682	created_at datetime,
683	updated_at datetime,
684	KEY bundle_id_index (bundle_id),
685	KEY service_id_index (service_id),
686	PRIMARY KEY (id)
687	) $charset_collate;";
688
689	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SERVICES . " (
690	id mediumint(9) NOT NULL AUTO_INCREMENT,
691	name varchar(255) NOT NULL,
692	short_description text,
693	is_price_variable boolean,
694	price_min decimal(20,4),
695	price_max decimal(20,4),
696	charge_amount decimal(20,4),
697	deposit_amount decimal(20,4),
698	is_deposit_required boolean,
699	duration_name varchar(255),
700	override_default_booking_status varchar(255),
701	duration int(11) NOT NULL,
702	buffer_before int(11),
703	buffer_after int(11),
704	category_id int(11),
705	order_number int(11),
706	selection_image_id int(11),
707	description_image_id int(11),
708	bg_color varchar(20),
709	earliest_possible_booking varchar(50),
710	latest_possible_booking varchar(50),
711	timeblock_interval int(11),
712	capacity_min int(4),
713	capacity_max int(4),
714	status varchar(20) NOT NULL,
715	visibility varchar(20) NOT NULL,
716	created_at datetime,
717	updated_at datetime,
718	KEY category_id_index (category_id),
719	KEY order_number_index (order_number),
720	KEY status_index (status),
721	PRIMARY KEY (id)
722	) $charset_collate;";
723
724	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_AGENTS . " (
725	id mediumint(9) NOT NULL AUTO_INCREMENT,
726	avatar_image_id int(11),
727	bio_image_id int(11),
728	first_name varchar(255) NOT NULL,
729	last_name varchar(255),
730	display_name varchar(255),
731	title varchar(255),
732	bio text,
733	features text,
734	email varchar(110) NOT NULL,
735	phone varchar(255),
736	password varchar(255),
737	custom_hours boolean,
738	wp_user_id int(11),
739	status varchar(20) NOT NULL,
740	extra_emails text,
741	extra_phones text,
742	created_at datetime,
743	updated_at datetime,
744	KEY email_index (email),
745	PRIMARY KEY (id)
746	) $charset_collate;";
747
748	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_STEP_SETTINGS . " (
749	id mediumint(9) NOT NULL AUTO_INCREMENT,
750	label varchar(50) NOT NULL,
751	value text,
752	step varchar(50),
753	created_at datetime,
754	updated_at datetime,
755	KEY step_index (step),
756	KEY label_index (label),
757	PRIMARY KEY (id)
758	) $charset_collate;";
759
760
761	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CUSTOMERS . " (
762	id mediumint(9) NOT NULL AUTO_INCREMENT,
763	uuid varchar(36),
764	first_name varchar(255),
765	last_name varchar(255),
766	email varchar(320),
767	phone varchar(255),
768	avatar_image_id int(11),
769	status varchar(50) NOT NULL,
770	password varchar(255),
771	activation_key varchar(255),
772	account_nonse varchar(255),
773	google_user_id varchar(255),
774	facebook_user_id varchar(255),
775	wordpress_user_id int(11),
776	is_guest boolean,
777	notes text,
778	admin_notes text,
779	created_at datetime,
780	updated_at datetime,
781	UNIQUE KEY uuid_index (uuid),
782	KEY email_index (email),
783	KEY status_index (status),
784	KEY wordpress_user_id_index (wordpress_user_id),
785	PRIMARY KEY (id)
786	) $charset_collate;";
787
788	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_SERVICE_CATEGORIES . " (
789	id mediumint(9) NOT NULL AUTO_INCREMENT,
790	name varchar(100) NOT NULL,
791	short_description text,
792	parent_id mediumint(9),
793	selection_image_id int(11),
794	order_number int(11),
795	created_at datetime,
796	updated_at datetime,
797	KEY order_number_index (order_number),
798	KEY parent_id_index (parent_id),
799	PRIMARY KEY (id)
800	) $charset_collate;";
801
802	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_CUSTOM_PRICES . " (
803	id mediumint(9) NOT NULL AUTO_INCREMENT,
804	agent_id int(11) NOT NULL,
805	service_id int(11) NOT NULL,
806	location_id int(11) NOT NULL,
807	is_price_variable boolean,
808	price_min decimal(20,4),
809	price_max decimal(20,4),
810	charge_amount decimal(20,4),
811	is_deposit_required boolean,
812	deposit_amount decimal(20,4),
813	created_at datetime,
814	updated_at datetime,
815	KEY agent_id_index (agent_id),
816	KEY service_id_index (service_id),
817	KEY location_id_index (location_id),
818	PRIMARY KEY (id)
819	) $charset_collate;";
820
821	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_WORK_PERIODS . " (
822	id mediumint(9) NOT NULL AUTO_INCREMENT,
823	agent_id int(11) NOT NULL,
824	service_id int(11) NOT NULL,
825	location_id int(11) NOT NULL,
826	start_time smallint(6) NOT NULL,
827	end_time smallint(6) NOT NULL,
828	week_day tinyint(3) NOT NULL,
829	custom_date date,
830	chain_id varchar(20),
831	created_at datetime,
832	updated_at datetime,
833	KEY agent_id_index (agent_id),
834	KEY service_id_index (service_id),
835	KEY location_id_index (location_id),
836	KEY week_day_index (week_day),
837	KEY custom_date_index (custom_date),
838	PRIMARY KEY (id)
839	) $charset_collate;";
840
841	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_AGENTS_SERVICES . " (
842	id mediumint(9) NOT NULL AUTO_INCREMENT,
843	agent_id int(11) NOT NULL,
844	service_id int(11) NOT NULL,
845	location_id int(11),
846	is_custom_hours BOOLEAN,
847	is_custom_price BOOLEAN,
848	is_custom_duration BOOLEAN,
849	created_at datetime,
850	updated_at datetime,
851	KEY agent_id_index (agent_id),
852	KEY service_id_index (service_id),
853	KEY location_id_index (location_id),
854	PRIMARY KEY (id)
855	) $charset_collate;";
856
857	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_ACTIVITIES . " (
858	id mediumint(9) NOT NULL AUTO_INCREMENT,
859	agent_id int(11),
860	booking_id int(11),
861	service_id int(11),
862	customer_id int(11),
863	location_id int(11),
864	order_id int(11),
865	order_item_id int(11),
866	coupon_id int(11),
867	code varchar(255) NOT NULL,
868	description text,
869	initiated_by varchar(100),
870	initiated_by_id int(11),
871	created_at datetime,
872	updated_at datetime,
873	PRIMARY KEY (id)
874	) $charset_collate;";
875
876
877	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_TRANSACTION_INTENTS . " (
878	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
879	intent_key varchar(55) NOT NULL,
880	order_id int(11) NOT NULL,
881	customer_id int(11),
882	invoice_id int(11),
883	transaction_id int(11),
884	payment_data text,
885	charge_amount decimal(20,4),
886	specs_charge_amount varchar(55),
887	order_form_page_url text,
888	status varchar(30) DEFAULT '" . LATEPOINT_TRANSACTION_INTENT_STATUS_NEW . "' NOT NULL,
889	created_at datetime,
890	updated_at datetime,
891	PRIMARY KEY (id),
892	UNIQUE KEY intent_key_index (intent_key)
893	) $charset_collate;";
894
895	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_TRANSACTIONS . " (
896	id mediumint(9) NOT NULL AUTO_INCREMENT,
897	token text,
898	invoice_id int(11),
899	order_id int(11),
900	customer_id int(11),
901	processor varchar(100),
902	payment_method varchar(55),
903	payment_portion varchar(55),
904	kind varchar(40),
905	status varchar(100) NOT NULL,
906	amount decimal(20,4),
907	receipt_number varchar(10),
908	access_key varchar(36),
909	notes text,
910	created_at datetime,
911	updated_at datetime,
912	PRIMARY KEY (id)
913	) $charset_collate;";
914
915
916	$sqls[] = 'CREATE TABLE ' . LATEPOINT_TABLE_TRANSACTION_REFUNDS . " (
917	id mediumint(9) NOT NULL AUTO_INCREMENT,
918	token text,
919	transaction_id int(11),
920	amount decimal(20,4),
921	created_at datetime,
922	updated_at datetime,
923	PRIMARY KEY (id)
924	) $charset_collate;";
925
926	return $sqls;
927	}
928	}
929