<?php

namespace BwWinnersGlobalSite\Entities;

if ( ! class_exists( __NAMESPACE__ . '\Competition_Store' ) ) {

	class Competition_Store {

		private $competition_table_name = 'bw_winners_v2_competitions';
		private $entry_table_name = 'bw_winners_v2_entries';

		public function clean ( $update_at ) {

			global $wpdb;

			$table_names = array(
				$wpdb->prefix . $this->competition_table_name
			);

			foreach ( $table_names as $table_name ) {
				$wpdb->query( $wpdb->prepare(
					"DELETE FROM {$table_name} WHERE updated_at < %s",
					$update_at
				) );
			}
		}

		// ----------------------------------------------------------------------------
		// Competition
		// ----------------------------------------------------------------------------

		public function get_competitions () {

			global $wpdb;

			$competition_table_name = $wpdb->prefix . $this->competition_table_name;

			return $wpdb->get_results( "SELECT id, type, name FROM {$competition_table_name}", ARRAY_A );

		}

		public function get_competition ( $competition_id ) {

			global $wpdb;

			$competition_table_name = $wpdb->prefix . $this->competition_table_name;

			$competition = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id, type, name FROM {$competition_table_name} WHERE id = %d",
					[ $competition_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $competition ) {
				return false;
			}

			return $competition;
		}

		public function get_competition_id ( $name ) {

			global $wpdb;

			$competition_table_name = $wpdb->prefix . $this->competition_table_name;

			$competition_id = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$competition_table_name} WHERE name = %s",
					[ $name ]
				)
			);

			if ( $wpdb->last_error || ! $competition_id ) {
				return false;
			}

			return $competition_id;
		}

		public function update_competition ( $competition ) {

			if ( ! is_array( $competition ) ) {
				return false;
			}

			global $wpdb;

			$competition_table_name = $wpdb->prefix . $this->competition_table_name;

			$competition['updated_at'] = gmdate('Y-m-d H:i:s');

			// insert

			$insert_column_map = array(
				'id'         => '%d',
				'name'       => '%s',
				'type'       => '%s',
				'updated_at' => '%s'
			);

			$column_names = [];
			$value_formats  = [];
			$values  = [];

			foreach ( $insert_column_map as $column => $format ) {
				if ( array_key_exists( $column, $competition ) ) {
					$column_names[] = $column;
					$value_formats[] = $format;
					$values[] = $competition[ $column ];
				}
			}

			$insert_column_names = implode( ', ', $column_names );
			$insert_values = $wpdb->prepare( implode( ', ', $value_formats ), $values );


			// update
			
			$update_column_map = array(
				'name'       => '%s',
				'type'       => '%s',
				'updated_at' => '%s'
			);
			
			$column_value_formats = [];
			$values  = [];

			foreach ( $update_column_map as $column => $format ) {
				if ( array_key_exists( $column, $competition ) ) {
					$column_value_formats[] = "{$column}={$format}";
					$values[]  = $competition[ $column ];
				}
			}

			$column_value_formats[] = "id=LAST_INSERT_ID(id)";

			$update_column_values = $wpdb->prepare(
				implode( ', ', $column_value_formats ),
				$values
			);

			$sql = "INSERT INTO {$competition_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_competition ( $competition_id ) {

			global $wpdb;

			$competition_table_name = $wpdb->prefix . $this->competition_table_name;

			$wpdb->query( $wpdb->prepare(
				"DELETE FROM {$competition_table_name} WHERE id = %d",
				[ $competition_id ]
			) );

			if ( $wpdb->last_error ) {
				return false;
			}

			return true;
		}


		// ----------------------------------------------------------------------------
		// Competition Entries
		// ----------------------------------------------------------------------------

		public function get_competition_entries ( $competition_id ) {

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$competition_entries = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						id,
						product_id,
						competition_id,
						competition_product_category,
						year,
						score,
						updated_at
					FROM {$entry_table_name} WHERE competition_id = %d",
					[ $competition_id ]
				),
				ARRAY_A
			);

			if ( $wpdb->last_error || ! $competition_entries ) {
				return [];
			}

			return $competition_entries;
		}

		public function get_competition_product_categories ( $competition_id ) {

			global $wpdb;

			$entry_table_name = $wpdb->prefix . $this->entry_table_name;

			$get_competition_product_categories = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT competition_product_category
					FROM {$entry_table_name}
					WHERE competition_id = %d
					GROUP BY competition_product_category",
					[ $competition_id ]
				)
			);

			if ( $wpdb->last_error || ! $get_competition_product_categories ) {
				return [];
			}

			return $get_competition_product_categories;
		}
	}
}