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

latepoint / lib / helpers / database_helper.php

latepoint / lib / helpers Last commit date

database_helper.php

932 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	LATEPOINT_TABLE_RECURRENCES,
220	LATEPOINT_TABLE_CUSTOMER_OTP_CODES,
221	];
222
223	/**
224	* Get list of all tables that hold latepoint related data
225	*
226	* @param {array} $tables list of tables
227	* @returns {array} The filtered list of tables
228	*
229	* @since 5.1.3
230	* @hook get_all_latepoint_tables
231	*
232	*/
233	return apply_filters( 'get_all_latepoint_tables', $tables );
234
235	}
236
237	public static function get_initial_table_queries() {
238
239	global $wpdb;
240
241	$charset_collate = $wpdb->get_charset_collate();
242
243	$sqls = [];
244
245
246	/* OTP Codes */
247	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOMER_OTP_CODES . " (
248	id mediumint(9) NOT NULL AUTO_INCREMENT,
249	contact_value varchar(255) NOT NULL,
250	contact_type varchar(30) NOT NULL,
251	delivery_method varchar(30) NOT NULL,
252	otp_hash VARCHAR(255) NOT NULL,
253	expires_at datetime not null,
254	status varchar(30) DEFAULT '" . LATEPOINT_CUSTOMER_OTP_CODE_STATUS_ACTIVE . "' NOT NULL,
255	attempts INT DEFAULT 0,
256	used_at DATETIME NULL,
257	created_at datetime,
258	updated_at datetime,
259	PRIMARY KEY (id),
260	KEY idx_contact_status (contact_value, status),
261	KEY idx_expires_status (expires_at, status)
262	) $charset_collate;";
263
264
265	/* Recurrences */
266	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_RECURRENCES . " (
267	id mediumint(9) NOT NULL AUTO_INCREMENT,
268	rules text,
269	overrides text,
270	created_at datetime,
271	updated_at datetime,
272	PRIMARY KEY (id)
273	) $charset_collate;";
274
275
276	// ---------------
277	// STEPS
278	// ---------------
279
280
281	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_STEPS . " (
282	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
283	title text,
284	before_content text,
285	after_content text,
286	side_title text,
287	side_description text,
288	use_custom_image boolean,
289	custom_image_id int(11),
290	code varchar(100),
291	parent_step_id smallint(6),
292	position smallint(6),
293	created_at datetime,
294	updated_at datetime,
295	PRIMARY KEY (id)
296	) $charset_collate;";
297
298	// ---------------
299	// CART
300	// ---------------
301
302	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CARTS . " (
303	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
304	uuid varchar(36),
305	order_intent_id int(11),
306	order_id int(11),
307	coupon_code varchar(100),
308	created_at datetime,
309	updated_at datetime,
310	PRIMARY KEY (id),
311	KEY uuid_index (uuid),
312	KEY order_id_index (order_id),
313	KEY order_intent_id_index (order_intent_id)
314	) $charset_collate;";
315
316	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CART_ITEMS . " (
317	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
318	cart_id int(11) NOT NULL,
319	variant varchar(55),
320	item_data text,
321	connected_cart_item_id int(11),
322	created_at datetime,
323	updated_at datetime,
324	PRIMARY KEY (id),
325	KEY connected_cart_item_id_index (connected_cart_item_id),
326	KEY cart_id_index (cart_id)
327	) $charset_collate;";
328
329
330	// ---------------
331	// ORDERS
332	// ---------------
333
334	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDERS . " (
335	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
336	subtotal decimal(20,4),
337	total decimal(20,4),
338	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_STATUS_OPEN . "' NOT NULL,
339	fulfillment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_FULFILLMENT_STATUS_NOT_FULFILLED . "' NOT NULL,
340	payment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_PAYMENT_STATUS_NOT_PAID . "' NOT NULL,
341	source_id varchar(100),
342	source_url text,
343	ip_address varchar(55),
344	customer_id int(11) NOT NULL,
345	customer_comment text,
346	confirmation_code varchar(10),
347	price_breakdown text,
348	coupon_code varchar(100),
349	coupon_discount decimal(20,4),
350	tax_total decimal(20,4),
351	initial_payment_data text,
352	created_at datetime,
353	updated_at datetime,
354	PRIMARY KEY (id),
355	KEY customer_id_index (customer_id)
356	) $charset_collate;";
357
358	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_ITEMS . " (
359	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
360	order_id int(11) NOT NULL,
361	variant varchar(55),
362	item_data text,
363	subtotal decimal(20,4),
364	total decimal(20,4),
365	coupon_code varchar(100),
366	coupon_discount decimal(20,4),
367	tax_total decimal(20,4),
368	created_at datetime,
369	updated_at datetime,
370	PRIMARY KEY (id),
371	KEY order_id_index (order_id)
372	) $charset_collate;";
373
374
375	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_INTENTS . " (
376	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
377	intent_key varchar(55) NOT NULL,
378	customer_id int(11) NOT NULL,
379	cart_items_data text,
380	restrictions_data text,
381	presets_data text,
382	payment_data text,
383	other_data text,
384	order_id int(11),
385	booking_form_page_url text,
386	total decimal(20,4),
387	subtotal decimal(20,4),
388	coupon_code varchar(100),
389	coupon_discount decimal(20,4),
390	tax_total decimal(20,4),
391	charge_amount decimal(20,4),
392	specs_charge_amount varchar(55),
393	price_breakdown text,
394	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_INTENT_STATUS_NEW . "' NOT NULL,
395	created_at datetime,
396	updated_at datetime,
397	PRIMARY KEY (id),
398	UNIQUE KEY intent_key_index (intent_key)
399	) $charset_collate;";
400
401
402	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_INVOICES . " (
403	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
404	order_id int(11) NOT NULL,
405	invoice_number varchar(10),
406	data text,
407	status varchar(30) DEFAULT '" . LATEPOINT_INVOICE_STATUS_OPEN . "' NOT NULL,
408	charge_amount decimal(20,4),
409	due_at datetime,
410	payment_portion varchar(55),
411	access_key varchar(36),
412	created_at datetime,
413	updated_at datetime,
414	PRIMARY KEY (id),
415	KEY order_id_index (order_id),
416	UNIQUE KEY invoice_number_index (invoice_number)
417	) $charset_collate;";
418
419
420	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PAYMENT_REQUESTS . " (
421	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
422	order_id int(11) NOT NULL,
423	invoice_id int(11) NOT NULL,
424	charge_amount decimal(20,4),
425	due_at datetime,
426	`portion` varchar(55),
427	created_at datetime,
428	updated_at datetime,
429	PRIMARY KEY (id),
430	KEY invoice_id_index (invoice_id),
431	KEY order_id_index (order_id)
432	) $charset_collate;";
433
434
435	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PROCESS_JOBS . " (
436	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
437	process_id int(11) NOT NULL,
438	object_id int(11) NOT NULL,
439	object_model_type varchar(55),
440	settings text,
441	to_run_after_utc datetime,
442	status varchar(30) DEFAULT '" . LATEPOINT_JOB_STATUS_SCHEDULED . "',
443	run_result text,
444	process_info text,
445	created_at datetime,
446	updated_at datetime,
447	PRIMARY KEY (id)
448	) $charset_collate;";
449
450
451	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SESSIONS . " (
452	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
453	session_key varchar(55) NOT NULL,
454	session_value longtext NOT NULL,
455	expiration BIGINT UNSIGNED NOT NULL,
456	hash varchar(50) NOT NULL,
457	PRIMARY KEY (id),
458	UNIQUE KEY session_key (session_key)
459	) $charset_collate;";
460
461	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BOOKINGS . " (
462	id int(11) NOT NULL AUTO_INCREMENT,
463	booking_code varchar(10),
464	start_date date,
465	end_date date,
466	start_time mediumint(9),
467	end_time mediumint(9),
468	start_datetime_utc datetime,
469	end_datetime_utc datetime,
470	buffer_before mediumint(9) NOT NULL,
471	buffer_after mediumint(9) NOT NULL,
472	duration mediumint(9),
473	status varchar(30) DEFAULT '" . LATEPOINT_BOOKING_STATUS_PENDING . "' NOT NULL,
474	customer_id mediumint(9) NOT NULL,
475	service_id mediumint(9) NOT NULL,
476	agent_id mediumint(9) NOT NULL,
477	location_id mediumint(9),
478	order_item_id mediumint(9),
479	recurrence_id mediumint(9),
480	total_attendees mediumint(4),
481	customer_timezone varchar(100),
482	server_timezone varchar(100),
483	created_at datetime,
484	updated_at datetime,
485	KEY start_date_index (start_date),
486	KEY end_date_index (end_date),
487	KEY status_index (status),
488	KEY customer_id_index (customer_id),
489	KEY service_id_index (service_id),
490	KEY agent_id_index (agent_id),
491	KEY location_id_index (location_id),
492	KEY recurrence_id_index (recurrence_id),
493	PRIMARY KEY (id)
494	) $charset_collate;";
495
496
497	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BLOCKED_PERIODS . " (
498	id int(11) NOT NULL AUTO_INCREMENT,
499	summary text,
500	start_date date,
501	end_date date,
502	start_time mediumint(9),
503	end_time mediumint(9),
504	start_datetime_utc datetime,
505	end_datetime_utc datetime,
506	service_id mediumint(9),
507	agent_id mediumint(9),
508	location_id mediumint(9),
509	server_timezone varchar(100),
510	created_at datetime,
511	updated_at datetime,
512	KEY start_date_index (start_date),
513	KEY end_date_index (end_date),
514	KEY service_id_index (service_id),
515	KEY agent_id_index (agent_id),
516	KEY location_id_index (location_id),
517	PRIMARY KEY (id)
518	) $charset_collate;";
519
520
521	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CART_META . " (
522	id mediumint(9) NOT NULL AUTO_INCREMENT,
523	object_id mediumint(9) NOT NULL,
524	meta_key varchar(110) NOT NULL,
525	meta_value text,
526	created_at datetime,
527	updated_at datetime,
528	KEY meta_key_index (meta_key),
529	KEY object_id_index (object_id),
530	PRIMARY KEY (id)
531	) $charset_collate;";
532
533	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_META . " (
534	id mediumint(9) NOT NULL AUTO_INCREMENT,
535	object_id mediumint(9) NOT NULL,
536	meta_key varchar(110) NOT NULL,
537	meta_value text,
538	created_at datetime,
539	updated_at datetime,
540	KEY meta_key_index (meta_key),
541	KEY object_id_index (object_id),
542	PRIMARY KEY (id)
543	) $charset_collate;";
544
545
546	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BOOKING_META . " (
547	id mediumint(9) NOT NULL AUTO_INCREMENT,
548	object_id mediumint(9) NOT NULL,
549	meta_key varchar(110) NOT NULL,
550	meta_value text,
551	created_at datetime,
552	updated_at datetime,
553	KEY meta_key_index (meta_key),
554	KEY object_id_index (object_id),
555	PRIMARY KEY (id)
556	) $charset_collate;";
557
558
559	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PROCESSES . " (
560	id mediumint(9) NOT NULL AUTO_INCREMENT,
561	name varchar(110) NOT NULL,
562	event_type varchar(110) NOT NULL,
563	actions_json text,
564	status varchar(30) DEFAULT '" . LATEPOINT_STATUS_ACTIVE . "',
565	created_at datetime,
566	updated_at datetime,
567	PRIMARY KEY (id)
568	) $charset_collate;";
569
570	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICE_META . " (
571	id mediumint(9) NOT NULL AUTO_INCREMENT,
572	object_id mediumint(9) NOT NULL,
573	meta_key varchar(110) NOT NULL,
574	meta_value text,
575	created_at datetime,
576	updated_at datetime,
577	KEY meta_key_index (meta_key),
578	KEY object_id_index (object_id),
579	PRIMARY KEY (id)
580	) $charset_collate;";
581
582	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOMER_META . " (
583	id mediumint(9) NOT NULL AUTO_INCREMENT,
584	object_id mediumint(9) NOT NULL,
585	meta_key varchar(110) NOT NULL,
586	meta_value text,
587	created_at datetime,
588	updated_at datetime,
589	KEY meta_key_index (meta_key),
590	KEY object_id_index (object_id),
591	PRIMARY KEY (id)
592	) $charset_collate;";
593
594	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENT_META . " (
595	id mediumint(9) NOT NULL AUTO_INCREMENT,
596	object_id mediumint(9) NOT NULL,
597	meta_key varchar(110) NOT NULL,
598	meta_value text,
599	created_at datetime,
600	updated_at datetime,
601	KEY meta_key_index (meta_key),
602	KEY object_id_index (object_id),
603	PRIMARY KEY (id)
604	) $charset_collate;";
605
606	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BUNDLE_META . " (
607	id mediumint(9) NOT NULL AUTO_INCREMENT,
608	object_id mediumint(9) NOT NULL,
609	meta_key varchar(110) NOT NULL,
610	meta_value text,
611	created_at datetime,
612	updated_at datetime,
613	KEY meta_key_index (meta_key),
614	KEY object_id_index (object_id),
615	PRIMARY KEY (id)
616	) $charset_collate;";
617
618
619	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SETTINGS . " (
620	id mediumint(9) NOT NULL AUTO_INCREMENT,
621	name varchar(110) NOT NULL,
622	value longtext,
623	created_at datetime,
624	updated_at datetime,
625	KEY name_index (name),
626	PRIMARY KEY (id)
627	) $charset_collate;";
628
629
630	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_LOCATIONS . " (
631	id mediumint(9) NOT NULL AUTO_INCREMENT,
632	name varchar(255) NOT NULL,
633	full_address text,
634	status varchar(20) NOT NULL,
635	category_id int(11),
636	order_number int(11),
637	selection_image_id int(11),
638	created_at datetime,
639	updated_at datetime,
640	KEY status_index (status),
641	PRIMARY KEY (id)
642	) $charset_collate;";
643
644
645	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_LOCATION_CATEGORIES . " (
646	id mediumint(9) NOT NULL AUTO_INCREMENT,
647	name varchar(100) NOT NULL,
648	short_description text,
649	parent_id mediumint(9),
650	selection_image_id int(11),
651	order_number int(11),
652	created_at datetime,
653	updated_at datetime,
654	KEY order_number_index (order_number),
655	KEY parent_id_index (parent_id),
656	PRIMARY KEY (id)
657	) $charset_collate;";
658
659
660	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BUNDLES . " (
661	id mediumint(9) NOT NULL AUTO_INCREMENT,
662	name varchar(255) NOT NULL,
663	short_description text,
664	charge_amount decimal(20,4),
665	deposit_amount 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
932	}