<?php
defined('ABSPATH') || die('Access Denied');
function BKFORB_GCAL_g_cal_install () {
	global $wpdb;
	
	$debug = false;
	
	$pref = $wpdb->prefix;
	
	$current_ver = intval(get_option("gcal_db_version", 0));
	$charset_collate = $wpdb->get_charset_collate();
	
	$tables=array(	"{$pref}g_calendars",
					"{$pref}g_calendar_inactivity_times",
					"{$pref}g_calendar_timing",
					"{$pref}g_customers",
					"{$pref}g_extras",
					"{$pref}g_booking",
					"{$pref}g_extras_booking",
					"{$pref}g_extra_calendar");
					
	$ver_1 = 1;
	
	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;";


		$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( "gcal_db_version", 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("gcal_db_version", 0));
	}
}



/*****

OLD SQLS CREATED FOR dbDelta: 

	//require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

$sqls = array();
		$sqls[] = "CREATE TABLE IF NOT EXISTS {$pref}g_calendars (
		id int(11) NOT NULL AUTO_INCREMENT,
		name varchar(255) 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) 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 fk_cal_time (calendar_id),
		CONSTRAINT fk_cal_time FOREIGN KEY (calendar_id) REFERENCES {$pref}g_calendars (id) ON DELETE CASCADE ON UPDATE CASCADE
		) $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') NOT NULL DEFAULT 'unavail',
		date_from date NOT NULL,
		date_to date NOT NULL,
		PRIMARY KEY  (id),
		KEY calendar_id (calendar_id),
		KEY date_ind (date_from,date_to),
		CONSTRAINT fk_cal FOREIGN KEY (calendar_id) REFERENCES {$pref}g_calendars (id) ON DELETE CASCADE ON UPDATE CASCADE
		) $charset_collate;";

		$sqls[] = "CREATE TABLE IF NOT EXISTS {$pref}g_customers (
		id int(11) NOT NULL AUTO_INCREMENT,
		name varchar(255) NOT NULL,
		phone varchar(12) 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) 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_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 fk_calendar (calendar_id),
		KEY fk_customer (customer_id),
		CONSTRAINT fk_calendar FOREIGN KEY (calendar_id) REFERENCES {$pref}g_calendars (id) ON DELETE CASCADE ON UPDATE CASCADE,
		CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES {$pref}g_customers (id) ON DELETE CASCADE ON UPDATE CASCADE
		) $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 fk_booking (booking_id),
		KEY fk_extra_id (extra_id),
		CONSTRAINT fk_booking FOREIGN KEY (booking_id) REFERENCES {$pref}g_booking (id) ON DELETE CASCADE ON UPDATE CASCADE,
		CONSTRAINT fk_extra_id FOREIGN KEY (extra_id) REFERENCES {$pref}g_extras (id) ON DELETE CASCADE ON UPDATE CASCADE
		) $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 fk_calendar_id (calendar_id),
		KEY fk_ex (extra_id),
		CONSTRAINT fk_calendar_id FOREIGN KEY (calendar_id) REFERENCES {$pref}g_calendars (id) ON DELETE CASCADE ON UPDATE CASCADE,
		CONSTRAINT fk_ex FOREIGN KEY (extra_id) REFERENCES {$pref}g_extras (id) ON DELETE CASCADE ON UPDATE CASCADE
		) $charset_collate;";
		
		*/