<?php
global $wpdb;
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

$tables = array();

$company_table_name = $wpdb->prefix . 'bw_winners_companies';
$query = "CREATE TABLE IF NOT EXISTS $company_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	import_id BIGINT(20) UNSIGNED UNIQUE KEY,
	name VARCHAR(200),
	url VARCHAR(2083),
	PRIMARY KEY (id),
	CONSTRAINT fk_{$company_table_name}_company_id FOREIGN KEY (id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE
);";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

$brand_table_name = $wpdb->prefix . 'bw_winners_brands';
$query = "CREATE TABLE IF NOT EXISTS $brand_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	company_id BIGINT(20) UNSIGNED NULL,
	name VARCHAR(200),
	url VARCHAR(2083),
	PRIMARY KEY (id),
	CONSTRAINT fk_{$company_table_name}_brand_id FOREIGN KEY (id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE,
	FOREIGN KEY (company_id) REFERENCES $company_table_name (id) ON DELETE SET NULL
);";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

$product_table_name = $wpdb->prefix . 'bw_winners_products';
$query = "CREATE TABLE IF NOT EXISTS $product_table_name (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	brand_id BIGINT(20) UNSIGNED NULL,
	name VARCHAR(200),
	url VARCHAR(2083),
	type VARCHAR(200),
	price VARCHAR(200),
	PRIMARY KEY (id),
	CONSTRAINT fk_{$company_table_name}_product_id FOREIGN KEY (id) REFERENCES {$wpdb->posts} (ID) ON DELETE CASCADE,
	FOREIGN KEY (brand_id) REFERENCES $brand_table_name (id) ON DELETE SET NULL
);";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

$entry_table_name = $wpdb->prefix . 'bw_winners_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,
	year SMALLINT(4) UNSIGNED,
	score TINYINT(3) UNSIGNED,
	FOREIGN KEY (product_id) REFERENCES $product_table_name (id) ON DELETE CASCADE
);";

dbDelta( $query );
if ( $wpdb->last_error ) {
	return $wpdb->last_error;
}

return false;