<?php

class RPSync_Database {

	// Database table names
	public $numrefs_table  = '';
	public $products_table = '';

	public function __construct() {
		global $wpdb;
		$this->numrefs_table  = $wpdb->prefix . 'rpsync_numrefs';
		$this->products_table = $wpdb->prefix . 'rpsync_products';

		// Plugin activation / deactivation
		register_activation_hook( 'rpsync/rpsync.php', array( $this, 'create_tables' ) );
		register_deactivation_hook( 'rpsync/rpsync.php', array( $this, 'destroy_tables' ) );

		// RPSync actions
		add_action( 'wp_ajax_rpsync_check', array( $this, 'check' ) );
	}

	/**
	 * Create RPSync database tables
	 *
	 * @return void
	 */
	public function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset_collate = $wpdb->get_charset_collate();
		$sql             = "
			CREATE TABLE $this->numrefs_table (
		    `numref` VARCHAR(255) NOT NULL,
		    `title` VARCHAR(255) NOT NULL,
		    `product_id` INTEGER NULL,
		    `md5` VARCHAR(32) NOT NULL,
		    UNIQUE INDEX `numref`(`numref`),
		    INDEX (`product_id`),
		    PRIMARY KEY (`numref`)
			) $charset_collate;

			CREATE TABLE $this->products_table (
				`numref` VARCHAR(255) NOT NULL,
				`sku` VARCHAR(255) NOT NULL,
				`product_id` INTEGER NULL,
				`title` VARCHAR(255) NOT NULL,
				`brand` VARCHAR(255) NOT NULL,
				`department` VARCHAR(255) NOT NULL,
				`season` VARCHAR(255) NOT NULL,
				`attributes` JSON NOT NULL,
				`stock` INTEGER NOT NULL,
				`regular_price` VARCHAR(255) NOT NULL,
				`sale_price` VARCHAR(255) NOT NULL,
				`suggested_price` VARCHAR(255) NOT NULL,
				UNIQUE INDEX `sku`(`sku`),
				UNIQUE INDEX `product_id`(`product_id`),
				INDEX (`numref`),
				PRIMARY KEY (`sku`)
			) $charset_collate;
		";
		dbDelta( $sql );
	}

	/**
	 * Destroy RPSync database tables
	 *
	 * @return void
	 */
	public function destroy_tables() {
		global $wpdb;
		$wpdb->query( "DROP TABLE IF EXISTS $this->products_table,$this->numrefs_table;" );
	}

	/**
	 * Get md5 from numref
	 *
	 * @param string $numref
	 *
	 * @return false|string
	 */
	public function get_md5( string $numref ) {
		global $wpdb;
		$results = $wpdb->get_results( "SELECT md5 from $this->numrefs_table WHERE numref='$numref';", ARRAY_A );
		return empty( $results ) ? false : (string) $results[0]['md5'];
	}

	/**
	 * Save product data in database
	 *
	 * @param array $product_data
	 *
	 * @return bool
	 */
	public function save_product_data( array $product_data ) {
		global $wpdb;
		if ( empty( $product_data['numref'] ) || empty( $product_data['skus'] ) ) {
			return false;
		}

		// Upsert rpsync_numrefs
		$numref_columns = array( 'numref', 'title', 'product_id', 'md5' );
		$numref_values  = array_intersect_key( $product_data, array_flip( $numref_columns ) );
		$sql_existing   = "SELECT * FROM $this->numrefs_table WHERE numref='${numref_values['numref']}';";
		if ( ! empty( $existing = $wpdb->get_results( $sql_existing, ARRAY_A ) ) ) {
			$result = $wpdb->update( $this->numrefs_table, wp_parse_args( $numref_values, $existing[0] ), array( 'numref' => $product_data['numref'] ) );
		} else {
			$result = $wpdb->insert( $this->numrefs_table, $numref_values );
		}
		if ( $result === false ) {
			RPSync::get_instance()->error( "Erreur de sauvegarde pour le produit Retailpoint ${product_data['numref']}" );
			return false;
		}

		// Upsert rpsync_products
		$product_columns = array(
			'numref',
			'sku',
			'product_id',
			'title',
			'brand',
			'department',
			'season',
			'attributes',
			'stock',
			'regular_price',
			'sale_price',
			'suggested_price',
		);
		foreach ( $product_data['skus'] as $sku => $product ) {
			$product_values               = array_intersect_key( $product, array_flip( $product_columns ) );
			$product_values['attributes'] = json_encode( $product_values['attributes'] );
			$sql_existing                 = "SELECT * FROM $this->products_table WHERE sku='$sku';";
			if ( ! empty( $existing = $wpdb->get_results( $sql_existing, ARRAY_A ) ) ) {
				$result = $wpdb->update( $this->products_table, wp_parse_args( $product_values, $existing[0] ), array( 'sku' => $sku ) );
			} else {
				$result = $wpdb->insert( $this->products_table, $product_values );
			}
			if ( $result === false ) {
				RPSync::get_instance()->error( "Erreur de sauvegarde pour la variation de produit Retailpoint ${product_data['numref']} (SKU: $sku)" );
				return false;
			}
		}

		return true;
	}

	/**
	 * Load product data from database
	 *
	 * @param string $numref
	 *
	 * @return array|false
	 */
	public function load_product_data( string $numref ) {
		global $wpdb;
		if ( empty( $numref ) ) {
			return false;
		}

		// Load numref
		if ( empty( $results = $wpdb->get_results( "SELECT * FROM $this->numrefs_table WHERE numref='$numref';", ARRAY_A ) ) ) {
			return false;
		}
		$product_data = $results[0];

		// Load skus
		if ( empty( $results = $wpdb->get_results( "SELECT * FROM $this->products_table WHERE numref='$numref';", ARRAY_A ) ) ) {
			return false;
		};
		// Process DB values
		array_walk(
			$results,
			function ( &$result ) {
				$result['attributes'] = json_decode( $result['attributes'], ARRAY_A );
				$result['stock']      = (int) $result['stock'];
			}
		);
		// Sort variations by attribute
		usort(
			$results,
			function ( $a, $b ) {
				return floatval( $a['attributes']['grandeur'] ?? 0 ) > floatval( $b['attributes']['grandeur'] ?? 0 ) ? 1 : - 1;
			}
		);

		// SKU => processed product
		$product_data['skus'] = array_combine( array_column( $results, 'sku' ), $results );

		return $product_data;
	}

	/**
	 * Fetch multiple products with filters, pagination & sorting
	 *
	 * @param array $args
	 *
	 * @return array
	 */
	public function fetch_products( array $args ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'offset'  => 0,
				'limit'   => 20,
				'filters' => array(),
				'sorting' => array(
					'orderby' => 'numref',
					'order'   => 'ASC',
				),
			)
		);

		// WHERE
		$where = array_map(
			function ( $column, $value ) {
				switch ( $column ) {
					case 'numref':
						return "$column LIKE '$value%'";
					case 'title':
						return "$column LIKE '%$value%'";
					case 'product_id':
						return $value === 'existing' ? "product_id IS NOT NULL" : "product_id IS NULL";
					default:
						return "$column = '$value'";
				}
			},
			array_keys( $args['filters'] ),
			$args['filters']
		);
		$where = empty( $where ) ? '' : 'WHERE ' . implode( ' AND ', $where );

		// LIMIT
		$limit    = "LIMIT ${args['limit']} OFFSET ${args['offset']}";
		$order_by = 'ORDER BY ' . $args['sorting']['orderby'] . ' ' . $args['sorting']['order'];

		// Query results
		$results = $wpdb->get_results( "SELECT DISTINCT numref FROM $this->products_table $where $order_by $limit;", ARRAY_A );
		$total   = $wpdb->get_results( "SELECT COUNT(DISTINCT numref) as total FROM $this->products_table $where;", ARRAY_A )[0]['total'];

		// Process loaded results
		$products = array_map(
			function ( $numref ) {
				return $this->load_product_data( $numref );
			},
			array_column( $results, 'numref' )
		);

		return array(
			'products' => $products,
			'total'    => $total,
		);
	}

	/**
	 * Reset the md5 value
	 *
	 * @return bool
	 */
	public function reset_md5( $numref = false ) {
		global $wpdb;
		$where = $numref ? "WHERE numref='$numref'" : "";
		$query = "UPDATE $this->numrefs_table SET md5='' $where;";
		return ! ! $wpdb->query( $query );
	}

	/**
	 * Delete product_id from RPSync tables (dissociate product and its variations)
	 *
	 * @param $product_id
	 *
	 * @return void
	 */
	public function delete_product_id( $product_id ) {
		global $wpdb;
		$children = $wpdb->get_results( "SELECT ID FROM $wpdb->posts WHERE post_parent=$product_id;" );
		$post_ids = implode( ',', array_merge( array( $product_id ), array_column( $children, 'ID' ) ) );
		$wpdb->query( "UPDATE $this->numrefs_table SET product_id=NULL WHERE product_id IN ($post_ids);" );
		$wpdb->query( "UPDATE $this->products_table SET product_id=NULL WHERE product_id IN ($post_ids);" );
	}

	/**
	 * Determine numrefs from product_id
	 *
	 * @param $product_id
	 *
	 * @return array
	 */
	public function get_numrefs( $product_id ) {
		global $wpdb;
		$children = array_column( $wpdb->get_results( "SELECT ID FROM $wpdb->posts WHERE post_parent=$product_id;", ARRAY_A ), 'ID' );
		$post_ids = implode( ',', array_merge( array( $product_id ), $children ) );
		return array_column( $wpdb->get_results( "SELECT DISTINCT numref FROM $this->products_table WHERE product_id IN ($post_ids);", ARRAY_A ) ?: array(), 'numref' );
	}

	/**
	 * Forceful database sanity check
	 *
	 * @return void
	 */
	public function check() {
		global $wpdb;
		$database = RPSync::get_instance()->database;
		$start    = microtime( true );

		// Remove orphaned product variations
		$deleted = $wpdb->query( "DELETE FROM $wpdb->posts WHERE post_type='product_variation' AND post_parent=0;" );
		printf( "<h2>Suppression de %d variations de produits WooCommerce orphelines.</h2>", $deleted );

		// Remove orphaned postmeta
		$deleted = $wpdb->query( "DELETE FROM $wpdb->postmeta WHERE NOT EXISTS (SELECT * FROM $wpdb->posts p WHERE $wpdb->postmeta.post_id = p.ID);" );
		printf( "<h2>Suppression de %d métadonnées orphelines</h2>", $deleted );

		// Update orphaned rpsync_numrefs
		$deleted = $wpdb->query( "UPDATE $this->numrefs_table SET product_id=NULL WHERE product_id IS NOT NULL AND NOT EXISTS (SELECT * FROM $wpdb->posts p WHERE $this->numrefs_table.product_id = p.ID);" );
		printf( "<h2>Dissociation de %d produits Retailpoint orphelins</h2>", $deleted );

		// Update orphaned rpsync_products
		$deleted = $wpdb->query( "UPDATE $this->products_table SET product_id=NULL WHERE product_id IS NOT NULL AND NOT EXISTS (SELECT * FROM $wpdb->posts p WHERE $this->products_table.product_id = p.ID);" );
		printf( "<h2>Dissociation de %d variations Retailpoint orphelines</h2>", $deleted );

		// Cache for SKU => post_id
		$skus = $wpdb->get_results( "SELECT post_id, meta_value FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value != '';", ARRAY_A );
		$skus = array_combine( array_column( $skus, 'meta_value' ), array_column( $skus, 'post_id' ) );

		// Check associations / numrefs
		$numrefs = array_column( $wpdb->get_results( "SELECT numref FROM $this->numrefs_table;", ARRAY_A ), 'numref' );
		foreach ( $numrefs as $numref ) {
			$has_changed  = false;
			$product_data = $database->load_product_data( $numref );
			$parent_ids   = array();
			if ( ! is_array( $product_data['skus'] ) ) {
				continue;
			}
			foreach ( $product_data['skus'] as &$product ) {
				// Find product_id (simple / variation only) by SKU (fallback on numref as SKU)
				if ( empty( $product_id = $skus[ $product['sku'] ] ?: $skus[ $numref ] ?: null ) ||
					 has_term( 'variable', 'product_type', $product_id ) ) {
					if ( $product['product_id'] !== null ) {
						printf( "%s (SKU %s): dissociation du produit (ID: %d => null)<br/>", $numref, $product['sku'], $product['product_id'] );
						$product['product_id'] = null;
						$has_changed           = true;
					}
					continue;
				}
				if ( $product['product_id'] != $product_id ) {
					printf( "%s (SKU %s): association du produit (ID %d => %d)<br/>", $numref, $product['sku'], $product['product_id'], $product_id );
					$product['product_id'] = $product_id;
					$has_changed           = true;
				}
				$parent_ids[] = wp_get_post_parent_id( $product_id ) ?: $product_id;
			}
			$parent_ids = array_unique( $parent_ids );
			if ( sizeof( $parent_ids ) > 1 ) {
				printf( "%s est associé à plus d'un produit (%s)<br/>", $numref, implode( ', ', $parent_ids ) );
			}
			$parent_id = sizeof( $parent_ids ) === 1 ? reset( $parent_ids ) : null;
			if ( $product_data['product_id'] != $parent_id ) {
				printf( "%s: mise à jour du produit parent (ID %d)<br/>", $numref, $parent_id );
				$product_data['product_id'] = $parent_id;
				$has_changed                = true;
			}
			if ( $has_changed ) {
				$product_ids = array_filter( array_column( $product_data['skus'], 'product_id' ) );
				if ( count( $product_ids ) !== count( array_unique( $product_ids ) ) ) {
					printf( "%s: variations associées au même produit ... ignorer les avertissements précédents (ID %d)<br/>", $numref, $parent_id );
					continue;
				}
				$database->save_product_data( $product_data );
			}
		}

		// Update numrefs
		$product_ids = array_column( $wpdb->get_results( "SELECT product_id FROM $this->numrefs_table WHERE product_id IS NOT NULL;", ARRAY_A ), 'product_id' );
		foreach ( $product_ids as $product_id ) {
			$numref = implode( ' / ', RPSync::get_instance()->database->get_numrefs( $product_id ) );
			if ( get_field( 'numref', $product_id ) !== $numref ) {
				printf( "%s: mise à jour du numref sur le produit (ID %d)<br/>", $numref, $product_id );
				update_field( 'numref', $numref, $product_id );
			}
		}

		RPSync::get_instance()->process_xml();
		printf( "<h2>La base de données a été vérifiée en %.3f secs</h2><p>Le traitement des produits à réparer commencera sous peu...</p>", microtime( true ) - $start );
		wp_die();
	}

}
