<?php
// @codingStandardsIgnoreFile
global $wpdb;
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

$charset_collate = $wpdb->get_charset_collate();

$competition_table_name = $wpdb->prefix . 'bw_winners_v2_competitions';
$query = "CREATE TABLE IF NOT EXISTS $competition_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(200) UNIQUE KEY,
	type VARCHAR(200),
	updated_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	CONSTRAINT `FK-{$competition_table_name}-posts` FOREIGN KEY (id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}


$brand_table_name = $wpdb->prefix . 'bw_winners_v2_brands';
$query = "CREATE TABLE IF NOT EXISTS $brand_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	import_id BIGINT(20) UNSIGNED UNIQUE KEY,
	name VARCHAR(200),
	updated_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	CONSTRAINT `FK-{$brand_table_name}-posts` FOREIGN KEY (id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}


$product_table_name = $wpdb->prefix . 'bw_winners_v2_products';
$query = "CREATE TABLE IF NOT EXISTS $product_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	import_id BIGINT(20) UNSIGNED UNIQUE KEY,
	brand_id BIGINT(20) UNSIGNED NULL,
	name VARCHAR(200),
	categories VARCHAR(200),
	type VARCHAR(200),
	price VARCHAR(200),
	country VARCHAR(200),
	updated_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	CONSTRAINT `FK-{$product_table_name}-posts` FOREIGN KEY (id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE,
	CONSTRAINT `FK-{$product_table_name}-brands` FOREIGN KEY (brand_id) REFERENCES $brand_table_name (id) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}


$entry_table_name = $wpdb->prefix . 'bw_winners_v2_entries';
$query = "CREATE TABLE IF NOT EXISTS $entry_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	import_id BIGINT(20) UNSIGNED NULL UNIQUE KEY,
	product_id BIGINT(20) UNSIGNED NOT NULL,
	competition_id BIGINT(20) UNSIGNED NOT NULL,
	competition_product_category VARCHAR(200),
	year SMALLINT(4) UNSIGNED,
	score TINYINT(3) UNSIGNED,
	updated_at DATETIME NOT NULL,
	UNIQUE KEY `UK-{$entry_table_name}-composite` (year, competition_id, product_id),
	CONSTRAINT `FK-{$entry_table_name}-products` FOREIGN KEY (product_id) REFERENCES $product_table_name (id) ON DELETE CASCADE,
	CONSTRAINT `FK-{$entry_table_name}-competitions` FOREIGN KEY (competition_id) REFERENCES $competition_table_name (id) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

$brand_award_table_name = $wpdb->prefix . 'bw_winners_v2_brand_awards';
$query = "CREATE TABLE IF NOT EXISTS $brand_award_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	import_id BIGINT(20) UNSIGNED NULL UNIQUE KEY,
	brand_id BIGINT(20) UNSIGNED NOT NULL,
	competition_id BIGINT(20) UNSIGNED NOT NULL,
	country VARCHAR(200),
	region VARCHAR(200),
	name VARCHAR(200),
	year SMALLINT(4) UNSIGNED,
	updated_at DATETIME NOT NULL,
	UNIQUE KEY `UK-{$brand_award_table_name}-composite` (year, competition_id, brand_id, name, region, country),
	CONSTRAINT `FK-{$brand_award_table_name}-brands` FOREIGN KEY (brand_id) REFERENCES $brand_table_name (id) ON DELETE CASCADE,
	CONSTRAINT `FK-{$brand_award_table_name}-competitions` FOREIGN KEY (competition_id) REFERENCES $competition_table_name (id) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

$product_award_table_name = $wpdb->prefix . 'bw_winners_v2_product_awards';
$query = "CREATE TABLE IF NOT EXISTS $product_award_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	import_id BIGINT(20) UNSIGNED NULL UNIQUE KEY,
	product_id BIGINT(20) UNSIGNED NOT NULL,
	competition_id BIGINT(20) UNSIGNED NOT NULL,
	country VARCHAR(200),
	region VARCHAR(200),
	name VARCHAR(200),
	year SMALLINT(4) UNSIGNED,
	updated_at DATETIME NOT NULL,
	UNIQUE KEY `UK-{$product_award_table_name}-composite` (year, competition_id, product_id, name, region, country),
	CONSTRAINT `FK-{$product_award_table_name}-products` FOREIGN KEY (product_id) REFERENCES $product_table_name (id) ON DELETE CASCADE,
	CONSTRAINT `FK-{$product_award_table_name}-competitions` FOREIGN KEY (competition_id) REFERENCES $competition_table_name (id) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}


$brand_posts_name = $wpdb->prefix . 'bw_winners_v2_brand_posts';
$query = "CREATE TABLE IF NOT EXISTS $brand_posts_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	brand_id BIGINT(20) UNSIGNED,
	post_id BIGINT(20) UNSIGNED,
	PRIMARY KEY (id),
	UNIQUE KEY `UK-{$brand_posts_name}-brand_id-post_id` (brand_id, post_id),
	CONSTRAINT `FK-{$brand_posts_name}-brands` FOREIGN KEY (brand_id) REFERENCES {$brand_table_name} (id) ON DELETE CASCADE,
	CONSTRAINT `FK-{$brand_posts_name}-posts`  FOREIGN KEY (post_id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE
) $charset_collate;";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

// return sql error string or false

return false;