<?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' ) );
		add_action( 'wp_ajax_rpsync_check', array( $this, 'check' ) );
		add_action( 'wp_ajax_rpsync_check_associations', array( $this, 'check_associations' ) );
	}

	/**
	 * 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 = [ '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 = [
			'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':
				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 ${args['limit']} OFFSET ${args['offset']}";
		$order_by = 'ORDER BY ' . $args['sorting']['orderby'] . ' ' . $args['sorting']['order'];

		$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'];

		$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 );
	}

	/**
	 * Determine numref from product_id
	 *
	 * @param $product_id
	 *
	 * @return string
	 */
	public function get_numref( $product_id ) {
		global $wpdb;
		$numrefs = $wpdb->get_results( "SELECT numref FROM $this->numrefs_table WHERE product_id='$product_id';", ARRAY_A );
		$numrefs = array_column( $numrefs ?? array(), 'numref' );
		return implode( ' / ', $numrefs );
	}

	/**
	 * Forceful database sanity check
	 *
	 * @return void
	 */
	public function check() {
		global $wpdb;
		check_ajax_referer( 'rpsync_check', 'security' );
		$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 );

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

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

		// Check associated numrefs
		$results = $wpdb->get_results( "SELECT * FROM $this->numrefs_table WHERE product_id IS NOT NULL;", ARRAY_A );
		foreach ( $results as $product_data ) {
			// Check if WC product exists, set product_id to NULL if missing
			if ( empty( $wc_product = wc_get_product( $product_data['product_id'] ) ) ) {
				printf( "%s: le produit associé n'existe pas (ID: %d)<br/>", $product_data['numref'], $product_data['product_id'] );
				$wpdb->query( "UPDATE $this->numrefs_table SET product_id=NULL WHERE (product_id='${product_data['product_id']}');" );
				continue;
			}
			// Check WC product type
			if ( ! in_array( $wc_product->get_type(), array( 'simple', 'variable' ) ) ) {
				printf( "%s: le type de produit (%s) est invalide (ID: %d)<br/>", $product_data['numref'], $wc_product->get_type(), $product_data['product_id'] );
				$wpdb->query( "UPDATE $this->numrefs_table SET product_id=NULL WHERE (product_id='${product_data['product_id']}');" );
				$this->reset_md5( $product_data['numref'] );
				continue;
			}
			// Check if product numref exists
			if ( empty( $existing_numref = get_field( 'numref', $product_data['product_id'] ) ) ) {
				printf( "%s: numéro de référence manquant sur le produit (ID: %d)<br/>", $product_data['numref'], $product_data['product_id'] );
				update_field( 'numref', $product_data['numref'], $product_data['product_id'] );
				continue;
			}
			// Check if product numref matches
			if ( ! in_array( $product_data['numref'], explode( ' / ', $existing_numref ) ) ) {
				printf( "%s: numéro de référence invalide (%s) sur le produit (ID: %d)<br/>", $product_data['numref'], $existing_numref, $product_data['product_id'] );
				$this->reset_md5( $product_data['numref'] );
				continue;
			}
		}

		// Check missing numrefs
		$missing = get_posts( array(
			'fields'     => 'ids',
			'post_type'  => 'product',
			'nopaging'   => true,
			'meta_query' => array(
				'relation' => 'OR',
				array( 'key' => 'numref', 'value' => '' ),
				array( 'key' => 'numref', 'compare' => 'NOT EXISTS' ),
			),
		) );
		printf( "<h2>%d produits sans numref à traiter</h2>", sizeof( $missing ) );
		foreach ( $missing as $post_id ) {
			if ( empty( $sku = get_post_meta( $post_id, '_sku', true ) ) ) {
				printf( "SKU vide pour le produit: %d / %s<br/>", $post_id, get_the_title( $post_id ) );
				continue;
			}
			if ( $wpdb->query( "UPDATE $this->numrefs_table SET md5='' WHERE numref='$sku';" ) ) {
				printf( "SKU %s trouvé dans Retailpoint, pour le produit: %d / %s<br/>", $sku, $post_id, get_the_title( $post_id ) );
			} else {
				printf( "SKU %s introuvable pour le produit %d / %s<br/>", $sku, $post_id, get_the_title( $post_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();
	}

	/**
	 * Perform product association check
	 *
	 * @return void
	 */
	public function check_associations() {
		global $wpdb;
		check_ajax_referer( 'rpsync_check_associations', 'security' );
		$start    = microtime( true );
		$database = RPSync::get_instance()->database;

		// 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 all numrefs
		$numrefs = array_column( $wpdb->get_results( "SELECT numref FROM $this->numrefs_table;", ARRAY_A ), 'numref' );
		foreach ( $numrefs as $numref ) {
			$product_data = $database->load_product_data( $numref );
			$parent_ids   = array();
			foreach ( $product_data['skus'] as &$product ) {
				// Find WC product by SKU (fallback on numref as SKU)
				if ( empty( $product_id = $skus[ $product['sku'] ] ?: $skus[ $numref ] ?: null ) ) {
					$product['product_id'] = null;
					continue;
				}
				$product['product_id'] = $product_id;
				$parent_ids[]          = wp_get_post_parent_id( $product_id ) ?: $product_id;
			}
			$parent_ids                 = array_unique( $parent_ids );
			$product_data['product_id'] = sizeof( $parent_ids ) === 1 ? reset( $parent_ids ) : null;
			if ( sizeof( $parent_ids ) > 1 ) {
				printf( "Numref %s est associé à plus d'un produit", $numref );
			}
			$database->save_product_data( $product_data );
		}

		printf( "<h2>%d associations de produits ont été vérifiées en %.3f secs</h2>", sizeof( $numrefs ), microtime( true ) - $start );
		wp_die();
	}

}
