<?php

namespace BwWinnersGlobalSite\Entities;

if ( ! class_exists( __NAMESPACE__ . '\Brand_Store' ) ) {

	class Brand_Store {

		private $brand_table_name = 'bw_winners_v2_brands';
		private $brand_award_table_name = 'bw_winners_v2_brand_awards';
		private $brand_posts_table_name = 'bw_winners_v2_brand_posts';

		public function clean ( $update_at ) {

			global $wpdb;

			$table_names = array(
				$wpdb->prefix . $this->brand_posts_table_name,
				$wpdb->prefix . $this->brand_award_table_name,
				$wpdb->prefix . $this->brand_table_name
			);

			foreach ( $table_names as $table_name ) {
				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$table_name} WHERE updated_at < %s",
					$update_at
				) );
			}
		}

		public function get_brands () {

			global $wpdb;

			$brand_table_name = $wpdb->prefix . $this->brand_table_name;

			return $wpdb->get_results( "SELECT id, import_id, name  FROM {$brand_table_name}", ARRAY_A  );

		}

		public function get_brand ( $brand_id ) {

			global $wpdb;

			$brand_table_name = $wpdb->prefix . $this->brand_table_name;

			$brand = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id, import_id, name FROM {$brand_table_name} WHERE id = %d",
					[ $brand_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $brand ) {
				return false;
			}

			return $brand;
		}

		public function get_brand_id ( $import_id ) {

			global $wpdb;

			$brand_table_name = $wpdb->prefix . $this->brand_table_name;

			$brand_id = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$brand_table_name} WHERE import_id = %d",
					[ $import_id ]
				)
			);

			if ( $wpdb->last_error || ! $brand_id ) {
				return false;
			}

			return $brand_id;
		}

		public function get_brand_id_from_name( $name ) {

			global $wpdb;

			$brand_table_name = $wpdb->prefix . $this->brand_table_name;

			$brand_id = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$brand_table_name} WHERE name = %s",
					[ $name ]
				)
			);

			if ( $wpdb->last_error || ! $brand_id ) {
				return false;
			}

			return $brand_id;
		}

		public function update_brand ( $brand ) {

			if ( ! is_array( $brand ) ) {
				return false;
			}

			global $wpdb;

			$brand_table_name = $wpdb->prefix . $this->brand_table_name;

			$brand['updated_at'] = gmdate('Y-m-d H:i:s');

			
			// insert
			
			$insert_column_map = array(
				'id'         => '%d',
				'import_id'  => '%d',
				'name'       => '%s',
				'updated_at' => '%s'
			);

			$column_names = [];
			$value_formats  = [];
			$values  = [];

			foreach ( $insert_column_map as $column => $format ) {
				if ( array_key_exists( $column, $brand ) ) {
					$column_names[] = $column;
					$value_formats[] = $format;
					$values[] = $brand[ $column ];
				}
			}

			$insert_column_names = implode( ', ', $column_names );
			$insert_values = $wpdb->prepare( implode( ', ', $value_formats ), $values );


			// update
			
			$update_column_map = array(
				'name'       => '%s',
				'updated_at' => '%s'
			);
			
			$column_value_formats = [];
			$values  = [];

			foreach ( $update_column_map as $column => $format ) {
				if ( array_key_exists( $column, $brand ) ) {
					$column_value_formats[] = "{$column}={$format}";
					$values[]  = $brand[ $column ];
				}
			}

			$column_value_formats[] = "id=LAST_INSERT_ID(id)";

			$update_column_values = $wpdb->prepare(
				implode( ', ', $column_value_formats ),
				$values
			);

			$sql = "INSERT INTO {$brand_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_brand ( $brand_id ) {

			global $wpdb;

			$brand_table_name = $wpdb->prefix . $this->brand_table_name;

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$brand_table_name} WHERE id = %d",
				[ $brand_id ]
			) );

			if ( $wpdb->last_error ) {
				return false;
			}

			return true;
		}

		public function get_brand_posts ( $brand_id ) {

			global $wpdb;

			$brand_posts_table_name = $wpdb->prefix . $this->brand_posts_table_name;

			$query = $wpdb->prepare(
				"SELECT post_id
				 FROM {$brand_posts_table_name}
				 WHERE brand_id = %d",
				$brand_id
			);

			return array_column( $wpdb->get_results( $query, ARRAY_A ) ?: array(), 'post_id' );
		}

		public function set_brand_posts ( $brand_id, $post_ids ) {

			global $wpdb;

			$brand_posts_table_name = $wpdb->prefix . $this->brand_posts_table_name;

			if ( is_array( $post_ids ) && count( $post_ids ) ) {

				$values = [];
				$in = [];

				foreach ( $post_ids as $post_id ) {
					$values[] = $wpdb->prepare( "(%d, %d)", array( $brand_id, $post_id ) );
					$in[] = $wpdb->prepare( "%d", $post_id );
				}

				$values = implode( ',', $values );
				$in = implode( ',', $in );

				$wpdb->query( "INSERT IGNORE INTO {$brand_posts_table_name} (brand_id, post_id) VALUES {$values}" );

				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$brand_posts_table_name} WHERE brand_id=%d AND post_id NOT IN ({$in})",
					$brand_id
				) );

			} else {
				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$brand_posts_table_name} WHERE brand_id=%d",
					$brand_id
				) );
			}

		}

		public function get_post_brands ( $post_id ) {

			global $wpdb;

			$brand_posts_table_name = $wpdb->prefix . $this->brand_posts_table_name;

			$query = $wpdb->prepare(
				"SELECT brand_id
				 FROM {$brand_posts_table_name}
				 WHERE post_id = %d",
				$post_id
			);

			return array_column( $wpdb->get_results( $query, ARRAY_A ) ?: array(), 'brand_id' );
		}

		public function set_post_brands ( $post_id, $brand_ids ) {

			global $wpdb;

			$brand_posts_table_name = $wpdb->prefix . $this->brand_posts_table_name;

			if ( is_array( $brand_ids ) && count( $brand_ids ) ) {

				$values = [];
				$in = [];

				foreach ( $brand_ids as $brand_id ) {
					$values[] = $wpdb->prepare( "(%d, %d)", array( $brand_id, $post_id ) );
					$in[] = $wpdb->prepare( "%d", $brand_id );
				}

				$values = implode( ',', $values );
				$in = implode( ',', $in );

				$wpdb->query( "INSERT IGNORE INTO {$brand_posts_table_name} (brand_id, post_id) VALUES {$values}" );

				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$brand_posts_table_name} WHERE post_id=%d AND brand_id NOT IN ({$in})",
					$post_id
				) );

			} else {
				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$brand_posts_table_name} WHERE post_id=%d",
					$post_id
				) );
			}
		}


		// ----------------------------------------------------------------------------
		// Brand Awards
		// ----------------------------------------------------------------------------

		public function get_brand_awards ( $brand_id ) {

			global $wpdb;

			$brand_award_table_name = $wpdb->prefix . $this->brand_award_table_name;

			$brand_awards = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						id,
						import_id,
						brand_id,
						competition_id,
						country,
						region,
						name,
						year
					FROM {$brand_award_table_name} WHERE brand_id = %d",
					[ $brand_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $brand_awards ) {
				return array();
			}

			return $brand_awards;
		}

		public function set_brand_awards ( $brand_id, $awards ) {

			if ( ! is_array( $awards ) ) {
				return false;
			}

			global $wpdb;

			$brand_award_table_name = $wpdb->prefix . $this->brand_award_table_name;

			$start = gmdate('Y-m-d H:i:s');

			foreach ( $awards as $award ) {

				if ( ! is_array( $award ) ) {
					continue;
				}

				$award['brand_id'] = $brand_id;

				$this->update_brand_award( $award );
			}

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$brand_award_table_name} WHERE brand_id = {$brand_id} AND updated_at < %s",
				$start
			) );
		}

		public function delete_brand_awards ( $brand_id ) {

			global $wpdb;

			$brand_award_table_name = $wpdb->prefix . $this->brand_award_table_name;

			$wpdb->query( "DELETE FROM {$brand_award_table_name} WHERE brand_id = {$brand_id}" );
		}

		public function get_brand_award ( $award_id ) {

			global $wpdb;

			$brand_award_table_name = $wpdb->prefix . $this->brand_award_table_name;

			$brand_award = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
						id,
						import_id,
						brand_id,
						competition_id,
						country,
						region,
						name,
						year
					FROM {$brand_award_table_name} WHERE id = %d",
					[ $award_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $brand_award ) {
				return false;
			}

			return $brand_award;
		}

		public function update_brand_award ( $award ) {

			if ( ! is_array( $award ) ) {
				return false;
			}

			global $wpdb;

			$brand_award_table_name = $wpdb->prefix . $this->brand_award_table_name;

			$award['updated_at'] = gmdate('Y-m-d H:i:s');

			// update
			
			$update_column_map = array(
				'brand_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 {$brand_award_table_name} SET {$update_column_values} WHERE id = %d",
					[ $award['id'] ]
				);

			} else {

				$insert_column_map = array(
					'import_id'      => '%d',
					'brand_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 {$brand_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_brand_award ( $award_id ) {

			global $wpdb;

			$brand_award_table_name = $wpdb->prefix . $this->brand_award_table_name;

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$brand_award_table_name} WHERE id = %d",
				[ $award_id ]
			) );

			if ( $wpdb->last_error ) {
				return false;
			}

			return true;
		}
	}
}