<?php

namespace BwWinnersGlobalSite\Entities;

if ( ! class_exists( __NAMESPACE__ . '\Product_Store' ) ) {

	class Product_Store {

		private $product_table_name = 'bw_winners_v2_products';
		private $entry_table_name = 'bw_winners_v2_entries';
		private $product_award_table_name = 'bw_winners_v2_product_awards';

		public function clean ( $update_at ) {

			global $wpdb;

			$table_names = array(
				$wpdb->prefix . $this->product_award_table_name,
				$wpdb->prefix . $this->entry_table_name,
				$wpdb->prefix . $this->product_table_name
			);

			foreach ( $table_names as $table_name ) {
				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$table_name} WHERE updated_at < %s",
					$update_at
				) );
			}
		}

		// ----------------------------------------------------------------------------
		// Product
		// ----------------------------------------------------------------------------

		public function get_products () {

			global $wpdb;

			$product_table_name = $wpdb->prefix . $this->product_table_name;

			return $wpdb->get_results( "SELECT id, import_id, brand_id, name, categories, type, price, country, updated_at FROM {$product_table_name}", ARRAY_A );

		}

		public function get_product ( $product_id ) {

			global $wpdb;

			$product_table_name = $wpdb->prefix . $this->product_table_name;

			$product = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id, import_id, brand_id, name, categories, type, price, country, updated_at FROM {$product_table_name} WHERE id = %d",
					[ $product_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $product ) {
				return false;
			}

			return $product;
		}

		public function get_product_id ( $import_id ) {

			global $wpdb;

			$product_table_name = $wpdb->prefix . $this->product_table_name;

			$product_id = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$product_table_name} WHERE import_id = %d",
					[ $import_id ]
				)
			);

			if ( $wpdb->last_error || ! $product_id ) {
				return false;
			}

			return $product_id;
		}

		public function get_product_id_from_name( $name, $brand_id ) {

			global $wpdb;

			$product_table_name = $wpdb->prefix . $this->product_table_name;

			$product_id = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$product_table_name} WHERE name = %s AND brand_id = %d",
					[ $name, $brand_id ]
				)
			);

			if ( $wpdb->last_error || ! $product_id ) {
				return false;
			}

			return $product_id;
		}

		public function update_product ( $product ) {

			if ( ! is_array( $product ) ) {
				return false;
			}

			global $wpdb;

			$product_table_name = $wpdb->prefix . $this->product_table_name;

			$product['updated_at'] = gmdate('Y-m-d H:i:s');

			// insert

			$insert_column_map = array(
				'id'         => '%d',
				'import_id'  => '%d',
				'brand_id'   => '%d',
				'name'       => '%s',
				'categories' => '%s',
				'type'       => '%s',
				'price'      => '%s',
				'country'    => '%s',
				'updated_at' => '%s'
			);

			$column_names = [];
			$value_formats  = [];
			$values  = [];

			foreach ( $insert_column_map as $column => $format ) {
				if ( array_key_exists( $column, $product ) ) {
					$column_names[] = $column;
					$value_formats[] = $format;
					$values[] = $product[ $column ];
				}
			}

			$insert_column_names = implode( ', ', $column_names );
			$insert_values = $wpdb->prepare( implode( ', ', $value_formats ), $values );

			// update
			
			$update_column_map = array(
				'brand_id'   => '%d',
				'name'       => '%s',
				'categories' => '%s',
				'type'       => '%s',
				'price'      => '%s',
				'country'    => '%s',
				'updated_at' => '%s'
			);
			
			$column_value_formats = [];
			$values  = [];

			foreach ( $update_column_map as $column => $format ) {
				if ( array_key_exists( $column, $product ) ) {
					$column_value_formats[] = "{$column}={$format}";
					$values[]  = $product[ $column ];
				}
			}

			$column_value_formats[] = "id=LAST_INSERT_ID(id)";

			$update_column_values = $wpdb->prepare(
				implode( ', ', $column_value_formats ),
				$values
			);

			$sql = "INSERT INTO {$product_table_name} ({$insert_column_names})
				VALUES ({$insert_values}) ON DUPLICATE KEY UPDATE {$update_column_values}";


			$wpdb->query( $sql );

			if ( $wpdb->last_error ) {
				return false;
			}
			
			return $wpdb->insert_id;
		}

		public function delete_product ( $product_id ) {

			global $wpdb;

			$product_table_name = $wpdb->prefix . $this->product_table_name;

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$product_table_name} WHERE id = %d",
				[ $product_id ]
			) );

			if ( $wpdb->last_error ) {
				return false;
			}

			return true;
		}


		// ----------------------------------------------------------------------------
		// Product Awards
		// ----------------------------------------------------------------------------

		public function get_product_awards ( $product_id ) {

			global $wpdb;

			$product_award_table_name = $wpdb->prefix . $this->product_award_table_name;

			$product_awards = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						id,
						import_id,
						product_id,
						competition_id,
						country,
						region,
						name,
						year
					FROM {$product_award_table_name} WHERE product_id = %d",
					[ $product_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $product_awards ) {
				return [];
			}

			return $product_awards;
		}

		public function set_product_awards ( $product_id, $awards ) {

			if ( ! is_array( $awards ) ) {
				return false;
			}

			global $wpdb;

			$product_award_table_name = $wpdb->prefix . $this->product_award_table_name;

			$start = gmdate('Y-m-d H:i:s');

			foreach ( $awards as $award ) {

				if ( ! is_array( $award ) ) {
					continue;
				}

				$award['product_id'] = $product_id;

				$this->update_product_award( $award );
			}

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$product_award_table_name} WHERE product_id = {$product_id} AND updated_at < %s",
				$start
			) );
		}

		public function delete_product_awards ( $product_id ) {

			global $wpdb;

			$product_award_table_name = $wpdb->prefix . $this->product_award_table_name;

			$wpdb->query( "DELETE FROM {$product_award_table_name} WHERE product_id = {$product_id}" );
		}

		public function get_product_award ( $award_id ) {

			global $wpdb;

			$product_award_table_name = $wpdb->prefix . $this->product_award_table_name;

			$product_award = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
						id,
						import_id,
						product_id,
						competition_id,
						country,
						region,
						name,
						year
					FROM {$product_award_table_name} WHERE id = %d",
					[ $award_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $product_award ) {
				return false;
			}

			return $product_award;
		}

		public function update_product_award ( $award ) {

			if ( ! is_array( $award ) ) {
				return false;
			}

			global $wpdb;

			$product_award_table_name = $wpdb->prefix . $this->product_award_table_name;

			$award['updated_at'] = gmdate('Y-m-d H:i:s');

			// update
			
			$update_column_map = array(
				'product_id'     => '%d',
				'competition_id' => '%d',
				'country'        => '%s',
				'region'         => '%s',
				'name'           => '%s',
				'year'           => '%d',
				'updated_at'     => '%s'
			);
			
			$column_value_formats = [];
			$values  = [];

			foreach ( $update_column_map as $column => $format ) {
				if ( array_key_exists( $column, $award ) ) {
					$column_value_formats[] = "{$column}={$format}";
					$values[]  = $award[ $column ];
				}
			}

			$column_value_formats[] = "id=LAST_INSERT_ID(id)";

			$update_column_values = $wpdb->prepare(
				implode( ', ', $column_value_formats ),
				$values
			);

			// insert

			if ( ! empty( $award['id'] ) ) {

				$sql = $wpdb->prepare(
					"UPDATE {$product_award_table_name} SET {$update_column_values} WHERE id = %d",
					[ $award['id'] ]
				);

			} else {

				$insert_column_map = array(
					'import_id'      => '%d',
					'product_id'     => '%d',
					'competition_id' => '%d',
					'country'        => '%s',
					'region'         => '%s',
					'name'           => '%s',
					'year'           => '%d',
					'updated_at'     => '%s'
				);

				$column_names = [];
				$value_formats  = [];
				$values  = [];

				foreach ( $insert_column_map as $column => $format ) {
					if ( array_key_exists( $column, $award ) ) {
						$column_names[] = $column;
						$value_formats[] = $format;
						$values[] = $award[ $column ];
					}
				}

				$insert_column_names = implode( ', ', $column_names );
				$insert_values = $wpdb->prepare( implode( ', ', $value_formats ), $values );

				$sql = "INSERT INTO {$product_award_table_name} ({$insert_column_names})
					VALUES ({$insert_values}) ON DUPLICATE KEY UPDATE {$update_column_values}";
			}


			$wpdb->query( $sql );

			if ( $wpdb->last_error ) {
				return false;
			}
			
			return $wpdb->insert_id;
		}

		public function delete_product_award ( $award_id ) {

			global $wpdb;

			$product_award_table_name = $wpdb->prefix . $this->product_award_table_name;

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$product_award_table_name} WHERE id = %d",
				[ $award_id ]
			) );

			if ( $wpdb->last_error ) {
				return false;
			}

			return true;
		}


		// ----------------------------------------------------------------------------
		// Product Entries
		// ----------------------------------------------------------------------------

		public function get_product_entries ( $product_id ) {
			// echo "<pre>-- get entries --</pre>";

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$product_entries = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						id,
						import_id,
						product_id,
						competition_id,
						competition_product_category,
						year,
						score,
						updated_at
					FROM {$entry_table_name} WHERE product_id = %d",
					[ $product_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $product_entries ) {
				return [];
			}

			return $product_entries;
		}

		public function set_product_entries ( $product_id, $entries ) {

			if ( ! is_array( $entries ) ) {
				return false;
			}

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$start = gmdate('Y-m-d H:i:s');

			foreach ( $entries as $entry ) {

				if ( ! is_array( $entry ) ) {
					continue;
				}

				$entry['product_id'] = $product_id;

				$this->update_entry( $entry );
			}

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$entry_table_name} WHERE product_id = {$product_id} AND updated_at < %s",
				$start
			) );
		}

		public function delete_product_entries ( $product_id ) {

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$wpdb->query( "DELETE FROM {$entry_table_name} WHERE product_id = {$product_id}" );
		}

		public function get_entry ( $entry_id ) {

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$entry = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
						id,
						import_id,
						product_id,
						competition_id,
						competition_product_category,
						year,
						score,
						updated_at
					FROM {$entry_table_name} WHERE id = %d",
					[ $entry_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $entry ) {
				return false;
			}

			return $entry;
		}

		public function update_entry ( $entry ) {

			if ( ! is_array( $entry ) ) {
				return false;
			}

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$entry['updated_at'] = gmdate('Y-m-d H:i:s');

			// update
			
			$update_column_map = array(
				'import_id'                    => '%d',
				'product_id'                   => '%d',
				'competition_id'               => '%d',
				'competition_product_category' => '%s',
				'year'                         => '%d',
				'score'                        => '%d',
				'updated_at'                   => '%s'
			);
			
			$column_value_formats = [];
			$values  = [];

			foreach ( $update_column_map as $column => $format ) {
				if ( array_key_exists( $column, $entry ) ) {
					$column_value_formats[] = "{$column}={$format}";
					$values[]  = $entry[ $column ];
				}
			}

			$column_value_formats[] = "id=LAST_INSERT_ID(id)";

			$update_column_values = $wpdb->prepare(
				implode( ', ', $column_value_formats ),
				$values
			);

			// insert

			if ( ! empty( $entry['id'] ) ) {

				$sql = $wpdb->prepare(
					"UPDATE {$entry_table_name} SET {$update_column_values} WHERE id = %d",
					[ $entry['id'] ]
				);

			} else {

				$insert_column_map = array(
					'import_id'                    => '%d',
					'product_id'                   => '%d',
					'competition_id'               => '%d',
					'competition_product_category' => '%s',
					'year'                         => '%d',
					'score'                        => '%d',
					'updated_at'                   => '%s'
				);

				$column_names = [];
				$value_formats  = [];
				$values  = [];

				foreach ( $insert_column_map as $column => $format ) {
					if ( array_key_exists( $column, $entry ) ) {
						$column_names[] = $column;
						$value_formats[] = $format;
						$values[] = $entry[ $column ];
					}					
				}

				$insert_column_names = implode( ', ', $column_names );
				$insert_values = $wpdb->prepare( implode( ', ', $value_formats ), $values );

				$sql = "INSERT INTO {$entry_table_name} ({$insert_column_names})
					VALUES ({$insert_values}) ON DUPLICATE KEY UPDATE {$update_column_values}";
			}


			$wpdb->query( $sql );

			if ( $wpdb->last_error ) {
				return false;
			}
			
			return $wpdb->insert_id;
		}

		public function delete_entry ( $entry_id ) {

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$entry_table_name} WHERE id = %d",
				[ $entry_id ]
			) );

			if ( $wpdb->last_error ) {
				return false;
			}

			return true;
		}
	}
}