<?php
defined('ABSPATH') || die('Access Denied');
function BKFORB_GCAL_g_cal_install () {
	global $wpdb;
	
	$debug = false;
	$create_foreign_keys = true;

	$pref = $wpdb->prefix;
	$db_option_name = "gcal_db_version";


	$current_ver = intval(get_option($db_option_name, 0));
	$charset_collate = $wpdb->get_charset_collate();
			
	$tables = array(
		"{$pref}g_calendar_timing",
		"{$pref}g_calendar_inactivity_times",
		"{$pref}g_extra_calendar",
		"{$pref}g_extras_booking",
		"{$pref}g_extras",
		"{$pref}g_booking",
		"{$pref}g_calendars",
		"{$pref}g_customers"
	);
	$ver_1 = 2;
	
	if ($current_ver < $ver_1) {
		$sqls=array();

		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_booking` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
		  `date` date NOT NULL,
		  `time_from` int(11) NOT NULL,
		  `time_to` int(11) NOT NULL,
		  `customer_id` int(11) NOT NULL,
		  `calendar_id` int(11) NOT NULL,
		  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  PRIMARY KEY (`id`),
		  KEY `{$pref}fk_calendar` (`calendar_id`),
		  KEY `{$pref}fk_customer` (`customer_id`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_calendars` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
		  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  `time_from` int(11) NOT NULL DEFAULT '480',
		  `time_to` int(11) NOT NULL DEFAULT '1020',
		  `unavail_time_from` int(11) NOT NULL DEFAULT '0',
		  `unavail_time_to` int(11) NOT NULL DEFAULT '0',
		  `slot_duration` int(11) NOT NULL DEFAULT '60',
		  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
		  `currency` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '$',
		  PRIMARY KEY (`id`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_calendar_inactivity_times` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `calendar_id` int(11) NOT NULL,
		  `time_from` int(11) NOT NULL DEFAULT '0',
		  `time_to` int(11) NOT NULL DEFAULT '0',
		  PRIMARY KEY (`id`),
		  KEY `{$pref}fk_cal_time` (`calendar_id`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_calendar_timing` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `calendar_id` int(11) NOT NULL,
		  `status` enum('unavail','avail') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'unavail',
		  `date_from` date NOT NULL,
		  `date_to` date NOT NULL,
		  PRIMARY KEY (`id`),
		  KEY `{$pref}calendar_id` (`calendar_id`),
		  KEY `{$pref}date_ind` (`date_from`,`date_to`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_customers` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
		  `phone` varchar(18) COLLATE utf8mb4_unicode_ci NOT NULL,
		  `email` varchar(255) CHARACTER SET utf8 NOT NULL,
		  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  PRIMARY KEY (`id`),
		  UNIQUE KEY `email` (`email`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_extras` (
		  `id` int(11) NOT NULL AUTO_INCREMENT,
		  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
		  `price` decimal(10,2) DEFAULT '0.00',
		  `sum_op` enum('+','%') CHARACTER SET ascii NOT NULL DEFAULT '+',
		  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  PRIMARY KEY (`id`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_extras_booking` (
		  `extra_id` int(11) NOT NULL,
		  `booking_id` int(11) NOT NULL,
		  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  KEY `{$pref}fk_booking` (`booking_id`),
		  KEY `{$pref}fk_extra_id` (`extra_id`)
		) $charset_collate;";


		$sqls[]="CREATE TABLE IF NOT EXISTS `{$pref}g_extra_calendar` (
		  `calendar_id` int(11) NOT NULL,
		  `extra_id` int(11) NOT NULL,
		  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
		  UNIQUE KEY `uniq_cal_extra` (`calendar_id`,`extra_id`),
		  KEY `{$pref}fk_calendar_id` (`calendar_id`),
		  KEY `{$pref}fk_ex` (`extra_id`)
		) $charset_collate;";

		if ($create_foreign_keys) {
		$sqls[]="ALTER TABLE `{$pref}g_booking`
		  ADD CONSTRAINT `{$pref}fk_calendar_1sfg` FOREIGN KEY (`calendar_id`) REFERENCES `{$pref}g_calendars` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
		  ADD CONSTRAINT `{$pref}fk_customer_1sfg` FOREIGN KEY (`customer_id`) REFERENCES `{$pref}g_customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
		  
		  
		$sqls[]="ALTER TABLE `{$pref}g_calendar_inactivity_times`
		  ADD CONSTRAINT `{$pref}fk_cal_time_1sfg` FOREIGN KEY (`calendar_id`) REFERENCES `{$pref}g_calendars` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;";

		  
		$sqls[]="ALTER TABLE `{$pref}g_calendar_timing`
		  ADD CONSTRAINT `{$pref}fk_cal_1sfg` FOREIGN KEY (`calendar_id`) REFERENCES `{$pref}g_calendars` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
		  
		  
		$sqls[]="ALTER TABLE `{$pref}g_extras_booking`
		  ADD CONSTRAINT `{$pref}fk_booking_1sfg` FOREIGN KEY (`booking_id`) REFERENCES `{$pref}g_booking` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
		  ADD CONSTRAINT `{$pref}fk_extra_id_1sfg` FOREIGN KEY (`extra_id`) REFERENCES `{$pref}g_extras` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
		  
		  
		$sqls[]="ALTER TABLE `{$pref}g_extra_calendar`
		  ADD CONSTRAINT `{$pref}fk_calendar_id_1sfg` FOREIGN KEY (`calendar_id`) REFERENCES `{$pref}g_calendars` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
		  ADD CONSTRAINT `{$pref}fk_ex_1sfg` FOREIGN KEY (`extra_id`) REFERENCES `{$pref}g_extras` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;";
		}
		
		foreach ($tables as $table) {
			if (false === $wpdb->query("DROP TABLE IF EXISTS $table") and $debug) {
				echo "Table $table not deleted: ".$wpdb->last_error."\n";
			}
		}
		if ($debug) {
			echo "tables deleted \n";
		}
		
		$no_errors = true;
		foreach ($sqls as $sql) {
			if ($wpdb->query($sql) === false) {
				if ($debug) 
					echo "\n\n----\n\n{$sql}\n\n---\n\n " . $wpdb->last_error . "\n";
				$no_errors = false;
			}
		}
			
		if ($no_errors) {
			update_option( $db_option_name, $ver_1 );
			if ($debug) 
				echo "Tables created";
		} else {
			if ($debug) 
				echo "Version not updated";
		}	
	}
	
	if ($debug) {
		foreach($tables as $table) {
			$exists = $wpdb->get_var("SHOW TABLES LIKE '$table'") == $table?'exists':'NOT exists';
			echo $table . " - " . $exists . "\n ";
		}
	
		echo "Current ver: ".intval(get_option($db_option_name, 0));
	}
}