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

latepoint / lib / helpers / database_helper.php

latepoint / lib / helpers Last commit date

database_helper.php

852 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	// ---------------
245	// STEPS
246	// ---------------
247
248
249	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_STEPS . " (
250	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
251	title text,
252	before_content text,
253	after_content text,
254	side_title text,
255	side_description text,
256	use_custom_image boolean,
257	custom_image_id int(11),
258	code varchar(100),
259	parent_step_id smallint(6),
260	position smallint(6),
261	created_at datetime,
262	updated_at datetime,
263	PRIMARY KEY (id)
264	) $charset_collate;";
265
266	// ---------------
267	// CART
268	// ---------------
269
270	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CARTS . " (
271	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
272	uuid varchar(36),
273	order_intent_id int(11),
274	order_id int(11),
275	coupon_code varchar(100),
276	created_at datetime,
277	updated_at datetime,
278	PRIMARY KEY (id),
279	KEY uuid_index (uuid),
280	KEY order_id_index (order_id),
281	KEY order_intent_id_index (order_intent_id)
282	) $charset_collate;";
283
284	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CART_ITEMS . " (
285	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
286	cart_id int(11) NOT NULL,
287	variant varchar(55),
288	item_data text,
289	created_at datetime,
290	updated_at datetime,
291	PRIMARY KEY (id),
292	KEY cart_id_index (cart_id)
293	) $charset_collate;";
294
295
296	// ---------------
297	// ORDERS
298	// ---------------
299
300	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDERS . " (
301	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
302	subtotal decimal(20,4),
303	total decimal(20,4),
304	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_STATUS_OPEN . "' NOT NULL,
305	fulfillment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_FULFILLMENT_STATUS_NOT_FULFILLED . "' NOT NULL,
306	payment_status varchar(30) DEFAULT '" . LATEPOINT_ORDER_PAYMENT_STATUS_NOT_PAID . "' NOT NULL,
307	source_id varchar(100),
308	source_url text,
309	ip_address varchar(55),
310	customer_id int(11) NOT NULL,
311	customer_comment text,
312	confirmation_code varchar(10),
313	price_breakdown text,
314	coupon_code varchar(100),
315	coupon_discount decimal(20,4),
316	tax_total decimal(20,4),
317	initial_payment_data text,
318	created_at datetime,
319	updated_at datetime,
320	PRIMARY KEY (id),
321	KEY customer_id_index (customer_id)
322	) $charset_collate;";
323
324	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_ITEMS . " (
325	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
326	order_id int(11) NOT NULL,
327	variant varchar(55),
328	item_data text,
329	subtotal decimal(20,4),
330	total decimal(20,4),
331	coupon_code varchar(100),
332	coupon_discount decimal(20,4),
333	tax_total decimal(20,4),
334	created_at datetime,
335	updated_at datetime,
336	PRIMARY KEY (id),
337	KEY order_id_index (order_id)
338	) $charset_collate;";
339
340
341	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_INTENTS . " (
342	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
343	intent_key varchar(55) NOT NULL,
344	customer_id int(11) NOT NULL,
345	cart_items_data text,
346	restrictions_data text,
347	presets_data text,
348	payment_data text,
349	other_data text,
350	order_id int(11),
351	booking_form_page_url text,
352	total decimal(20,4),
353	subtotal decimal(20,4),
354	coupon_code varchar(100),
355	coupon_discount decimal(20,4),
356	tax_total decimal(20,4),
357	charge_amount decimal(20,4),
358	specs_charge_amount varchar(55),
359	price_breakdown text,
360	status varchar(30) DEFAULT '" . LATEPOINT_ORDER_INTENT_STATUS_NEW . "' NOT NULL,
361	created_at datetime,
362	updated_at datetime,
363	PRIMARY KEY (id),
364	UNIQUE KEY intent_key_index (intent_key)
365	) $charset_collate;";
366
367
368	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_INVOICES . " (
369	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
370	order_id int(11) NOT NULL,
371	invoice_number varchar(10),
372	data text,
373	status varchar(30) DEFAULT '" . LATEPOINT_INVOICE_STATUS_OPEN . "' NOT NULL,
374	charge_amount decimal(20,4),
375	due_at datetime,
376	payment_portion varchar(55),
377	access_key varchar(36),
378	created_at datetime,
379	updated_at datetime,
380	PRIMARY KEY (id),
381	KEY order_id_index (order_id),
382	UNIQUE KEY invoice_number_index (invoice_number)
383	) $charset_collate;";
384
385
386	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PAYMENT_REQUESTS . " (
387	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
388	order_id int(11) NOT NULL,
389	invoice_id int(11) NOT NULL,
390	charge_amount decimal(20,4),
391	due_at datetime,
392	portion varchar(55),
393	created_at datetime,
394	updated_at datetime,
395	PRIMARY KEY (id),
396	KEY invoice_id_index (invoice_id),
397	KEY order_id_index (order_id)
398	) $charset_collate;";
399
400
401	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PROCESS_JOBS . " (
402	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
403	process_id int(11) NOT NULL,
404	object_id int(11) NOT NULL,
405	object_model_type varchar(55),
406	settings text,
407	to_run_after_utc datetime,
408	status varchar(30) DEFAULT '" . LATEPOINT_JOB_STATUS_SCHEDULED . "',
409	run_result text,
410	process_info text,
411	created_at datetime,
412	updated_at datetime,
413	PRIMARY KEY (id)
414	) $charset_collate;";
415
416
417	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SESSIONS . " (
418	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
419	session_key varchar(55) NOT NULL,
420	session_value longtext NOT NULL,
421	expiration BIGINT UNSIGNED NOT NULL,
422	hash varchar(50) NOT NULL,
423	PRIMARY KEY (id),
424	UNIQUE KEY session_key (session_key)
425	) $charset_collate;";
426
427	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BOOKINGS . " (
428	id int(11) NOT NULL AUTO_INCREMENT,
429	booking_code varchar(10),
430	start_date date,
431	end_date date,
432	start_time mediumint(9),
433	end_time mediumint(9),
434	start_datetime_utc datetime,
435	end_datetime_utc datetime,
436	buffer_before mediumint(9) NOT NULL,
437	buffer_after mediumint(9) NOT NULL,
438	duration mediumint(9),
439	status varchar(30) DEFAULT '" . LATEPOINT_BOOKING_STATUS_PENDING . "' NOT NULL,
440	customer_id mediumint(9) NOT NULL,
441	service_id mediumint(9) NOT NULL,
442	agent_id mediumint(9) NOT NULL,
443	location_id mediumint(9),
444	order_item_id mediumint(9),
445	total_attendees mediumint(4),
446	created_at datetime,
447	updated_at datetime,
448	KEY start_date_index (start_date),
449	KEY end_date_index (end_date),
450	KEY status_index (status),
451	KEY customer_id_index (customer_id),
452	KEY service_id_index (service_id),
453	KEY agent_id_index (agent_id),
454	KEY location_id_index (location_id),
455	PRIMARY KEY (id)
456	) $charset_collate;";
457
458
459	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CART_META . " (
460	id mediumint(9) NOT NULL AUTO_INCREMENT,
461	object_id mediumint(9) NOT NULL,
462	meta_key varchar(110) NOT NULL,
463	meta_value text,
464	created_at datetime,
465	updated_at datetime,
466	KEY meta_key_index (meta_key),
467	KEY object_id_index (object_id),
468	PRIMARY KEY (id)
469	) $charset_collate;";
470
471	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ORDER_META . " (
472	id mediumint(9) NOT NULL AUTO_INCREMENT,
473	object_id mediumint(9) NOT NULL,
474	meta_key varchar(110) NOT NULL,
475	meta_value text,
476	created_at datetime,
477	updated_at datetime,
478	KEY meta_key_index (meta_key),
479	KEY object_id_index (object_id),
480	PRIMARY KEY (id)
481	) $charset_collate;";
482
483
484	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BOOKING_META . " (
485	id mediumint(9) NOT NULL AUTO_INCREMENT,
486	object_id mediumint(9) NOT NULL,
487	meta_key varchar(110) NOT NULL,
488	meta_value text,
489	created_at datetime,
490	updated_at datetime,
491	KEY meta_key_index (meta_key),
492	KEY object_id_index (object_id),
493	PRIMARY KEY (id)
494	) $charset_collate;";
495
496
497	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_PROCESSES . " (
498	id mediumint(9) NOT NULL AUTO_INCREMENT,
499	name varchar(110) NOT NULL,
500	event_type varchar(110) NOT NULL,
501	actions_json text,
502	status varchar(30) DEFAULT '" . LATEPOINT_STATUS_ACTIVE . "',
503	created_at datetime,
504	updated_at datetime,
505	PRIMARY KEY (id)
506	) $charset_collate;";
507
508	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICE_META . " (
509	id mediumint(9) NOT NULL AUTO_INCREMENT,
510	object_id mediumint(9) NOT NULL,
511	meta_key varchar(110) NOT NULL,
512	meta_value text,
513	created_at datetime,
514	updated_at datetime,
515	KEY meta_key_index (meta_key),
516	KEY object_id_index (object_id),
517	PRIMARY KEY (id)
518	) $charset_collate;";
519
520	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOMER_META . " (
521	id mediumint(9) NOT NULL AUTO_INCREMENT,
522	object_id mediumint(9) NOT NULL,
523	meta_key varchar(110) NOT NULL,
524	meta_value text,
525	created_at datetime,
526	updated_at datetime,
527	KEY meta_key_index (meta_key),
528	KEY object_id_index (object_id),
529	PRIMARY KEY (id)
530	) $charset_collate;";
531
532	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENT_META . " (
533	id mediumint(9) NOT NULL AUTO_INCREMENT,
534	object_id mediumint(9) NOT NULL,
535	meta_key varchar(110) NOT NULL,
536	meta_value text,
537	created_at datetime,
538	updated_at datetime,
539	KEY meta_key_index (meta_key),
540	KEY object_id_index (object_id),
541	PRIMARY KEY (id)
542	) $charset_collate;";
543
544
545	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SETTINGS . " (
546	id mediumint(9) NOT NULL AUTO_INCREMENT,
547	name varchar(110) NOT NULL,
548	value longtext,
549	created_at datetime,
550	updated_at datetime,
551	KEY name_index (name),
552	PRIMARY KEY (id)
553	) $charset_collate;";
554
555
556	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_LOCATIONS . " (
557	id mediumint(9) NOT NULL AUTO_INCREMENT,
558	name varchar(255) NOT NULL,
559	full_address text,
560	status varchar(20) NOT NULL,
561	category_id int(11),
562	order_number int(11),
563	selection_image_id int(11),
564	created_at datetime,
565	updated_at datetime,
566	KEY status_index (status),
567	PRIMARY KEY (id)
568	) $charset_collate;";
569
570
571	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_LOCATION_CATEGORIES . " (
572	id mediumint(9) NOT NULL AUTO_INCREMENT,
573	name varchar(100) NOT NULL,
574	short_description text,
575	parent_id mediumint(9),
576	selection_image_id int(11),
577	order_number int(11),
578	created_at datetime,
579	updated_at datetime,
580	KEY order_number_index (order_number),
581	KEY parent_id_index (parent_id),
582	PRIMARY KEY (id)
583	) $charset_collate;";
584
585
586	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_BUNDLES . " (
587	id mediumint(9) NOT NULL AUTO_INCREMENT,
588	name varchar(255) NOT NULL,
589	short_description text,
590	charge_amount decimal(20,4),
591	deposit_amount decimal(20,4),
592	status varchar(20) NOT NULL,
593	visibility varchar(20) NOT NULL,
594	order_number int(11),
595	created_at datetime,
596	updated_at datetime,
597	KEY order_number_index (order_number),
598	KEY status_index (status),
599	PRIMARY KEY (id)
600	) $charset_collate;";
601
602
603	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_JOIN_BUNDLES_SERVICES . " (
604	id mediumint(9) NOT NULL AUTO_INCREMENT,
605	bundle_id mediumint(9),
606	service_id mediumint(9),
607	total_attendees mediumint(4),
608	duration int(11),
609	quantity mediumint(4),
610	created_at datetime,
611	updated_at datetime,
612	KEY bundle_id_index (bundle_id),
613	KEY service_id_index (service_id),
614	PRIMARY KEY (id)
615	) $charset_collate;";
616
617	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICES . " (
618	id mediumint(9) NOT NULL AUTO_INCREMENT,
619	name varchar(255) NOT NULL,
620	short_description text,
621	is_price_variable boolean,
622	price_min decimal(20,4),
623	price_max decimal(20,4),
624	charge_amount decimal(20,4),
625	deposit_amount decimal(20,4),
626	is_deposit_required boolean,
627	duration_name varchar(255),
628	override_default_booking_status varchar(255),
629	duration int(11) NOT NULL,
630	buffer_before int(11),
631	buffer_after int(11),
632	category_id int(11),
633	order_number int(11),
634	selection_image_id int(11),
635	description_image_id int(11),
636	bg_color varchar(20),
637	timeblock_interval int(11),
638	capacity_min int(4),
639	capacity_max int(4),
640	status varchar(20) NOT NULL,
641	visibility varchar(20) NOT NULL,
642	created_at datetime,
643	updated_at datetime,
644	KEY category_id_index (category_id),
645	KEY order_number_index (order_number),
646	KEY status_index (status),
647	PRIMARY KEY (id)
648	) $charset_collate;";
649
650	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENTS . " (
651	id mediumint(9) NOT NULL AUTO_INCREMENT,
652	avatar_image_id int(11),
653	bio_image_id int(11),
654	first_name varchar(255) NOT NULL,
655	last_name varchar(255),
656	display_name varchar(255),
657	title varchar(255),
658	bio text,
659	features text,
660	email varchar(110) NOT NULL,
661	phone varchar(255),
662	password varchar(255),
663	custom_hours boolean,
664	wp_user_id int(11),
665	status varchar(20) NOT NULL,
666	extra_emails text,
667	extra_phones text,
668	created_at datetime,
669	updated_at datetime,
670	KEY email_index (email),
671	PRIMARY KEY (id)
672	) $charset_collate;";
673
674	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_STEP_SETTINGS . " (
675	id mediumint(9) NOT NULL AUTO_INCREMENT,
676	label varchar(50) NOT NULL,
677	value text,
678	step varchar(50),
679	created_at datetime,
680	updated_at datetime,
681	KEY step_index (step),
682	KEY label_index (label),
683	PRIMARY KEY (id)
684	) $charset_collate;";
685
686
687	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOMERS . " (
688	id mediumint(9) NOT NULL AUTO_INCREMENT,
689	first_name varchar(255),
690	last_name varchar(255),
691	email varchar(110) NOT NULL,
692	phone varchar(255),
693	avatar_image_id int(11),
694	status varchar(50) NOT NULL,
695	password varchar(255),
696	activation_key varchar(255),
697	account_nonse varchar(255),
698	google_user_id varchar(255),
699	facebook_user_id varchar(255),
700	wordpress_user_id int(11),
701	is_guest boolean,
702	notes text,
703	admin_notes text,
704	created_at datetime,
705	updated_at datetime,
706	KEY email_index (email),
707	KEY status_index (status),
708	KEY wordpress_user_id_index (wordpress_user_id),
709	PRIMARY KEY (id)
710	) $charset_collate;";
711
712	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_SERVICE_CATEGORIES . " (
713	id mediumint(9) NOT NULL AUTO_INCREMENT,
714	name varchar(100) NOT NULL,
715	short_description text,
716	parent_id mediumint(9),
717	selection_image_id int(11),
718	order_number int(11),
719	created_at datetime,
720	updated_at datetime,
721	KEY order_number_index (order_number),
722	KEY parent_id_index (parent_id),
723	PRIMARY KEY (id)
724	) $charset_collate;";
725
726	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_CUSTOM_PRICES . " (
727	id mediumint(9) NOT NULL AUTO_INCREMENT,
728	agent_id int(11) NOT NULL,
729	service_id int(11) NOT NULL,
730	location_id int(11) NOT NULL,
731	is_price_variable boolean,
732	price_min decimal(20,4),
733	price_max decimal(20,4),
734	charge_amount decimal(20,4),
735	is_deposit_required boolean,
736	deposit_amount decimal(20,4),
737	created_at datetime,
738	updated_at datetime,
739	KEY agent_id_index (agent_id),
740	KEY service_id_index (service_id),
741	KEY location_id_index (location_id),
742	PRIMARY KEY (id)
743	) $charset_collate;";
744
745	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_WORK_PERIODS . " (
746	id mediumint(9) NOT NULL AUTO_INCREMENT,
747	agent_id int(11) NOT NULL,
748	service_id int(11) NOT NULL,
749	location_id int(11) NOT NULL,
750	start_time smallint(6) NOT NULL,
751	end_time smallint(6) NOT NULL,
752	week_day tinyint(3) NOT NULL,
753	custom_date date,
754	chain_id varchar(20),
755	created_at datetime,
756	updated_at datetime,
757	KEY agent_id_index (agent_id),
758	KEY service_id_index (service_id),
759	KEY location_id_index (location_id),
760	KEY week_day_index (week_day),
761	KEY custom_date_index (custom_date),
762	PRIMARY KEY (id)
763	) $charset_collate;";
764
765	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_AGENTS_SERVICES . " (
766	id mediumint(9) NOT NULL AUTO_INCREMENT,
767	agent_id int(11) NOT NULL,
768	service_id int(11) NOT NULL,
769	location_id int(11),
770	is_custom_hours BOOLEAN,
771	is_custom_price BOOLEAN,
772	is_custom_duration BOOLEAN,
773	created_at datetime,
774	updated_at datetime,
775	KEY agent_id_index (agent_id),
776	KEY service_id_index (service_id),
777	KEY location_id_index (location_id),
778	PRIMARY KEY (id)
779	) $charset_collate;";
780
781	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_ACTIVITIES . " (
782	id mediumint(9) NOT NULL AUTO_INCREMENT,
783	agent_id int(11),
784	booking_id int(11),
785	service_id int(11),
786	customer_id int(11),
787	location_id int(11),
788	order_id int(11),
789	order_item_id int(11),
790	code varchar(255) NOT NULL,
791	description text,
792	initiated_by varchar(100),
793	initiated_by_id int(11),
794	created_at datetime,
795	updated_at datetime,
796	PRIMARY KEY (id)
797	) $charset_collate;";
798
799
800	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_TRANSACTION_INTENTS . " (
801	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
802	intent_key varchar(55) NOT NULL,
803	order_id int(11) NOT NULL,
804	customer_id int(11),
805	invoice_id int(11),
806	transaction_id int(11),
807	payment_data text,
808	charge_amount decimal(20,4),
809	specs_charge_amount varchar(55),
810	status varchar(30) DEFAULT '" . LATEPOINT_TRANSACTION_INTENT_STATUS_NEW . "' NOT NULL,
811	created_at datetime,
812	updated_at datetime,
813	PRIMARY KEY (id),
814	UNIQUE KEY intent_key_index (intent_key)
815	) $charset_collate;";
816
817	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_TRANSACTIONS . " (
818	id mediumint(9) NOT NULL AUTO_INCREMENT,
819	token text,
820	invoice_id int(11),
821	order_id int(11),
822	customer_id int(11),
823	processor varchar(100),
824	payment_method varchar(55),
825	payment_portion varchar(55),
826	kind varchar(40),
827	status varchar(100) NOT NULL,
828	amount decimal(20,4),
829	receipt_number varchar(10),
830	access_key varchar(36),
831	notes text,
832	created_at datetime,
833	updated_at datetime,
834	PRIMARY KEY (id)
835	) $charset_collate;";
836
837
838	$sqls[] = "CREATE TABLE " . LATEPOINT_TABLE_TRANSACTION_REFUNDS . " (
839	id mediumint(9) NOT NULL AUTO_INCREMENT,
840	token text,
841	transaction_id int(11),
842	amount decimal(20,4),
843	created_at datetime,
844	updated_at datetime,
845	PRIMARY KEY (id)
846	) $charset_collate;";
847
848	return $sqls;
849	}
850
851
852	}