<?php
/**
 * Database handling
 *
 * Handles all database operations.
 *
 * @package System
 * @author  Pierre Lannoy <https://pierre.lannoy.fr/>.
 * @since   1.0.0
 */

namespace PODeviceDetector\System;

/**
 * Define the database functionality.
 *
 * Handles all database operations.
 *
 * @package System
 * @author  Pierre Lannoy <https://pierre.lannoy.fr/>.
 * @since   1.0.0
 */
class Database {

	/**
	 * Initializes the class and set its properties.
	 *
	 * @since 1.0.0
	 */
	public function __construct() {
	}

	/**
	 * Update table with current value line.
	 *
	 * @param   string $table_name The table to update.
	 * @param   array  $value  The values to update or insert in the table.
	 * @return integer The insert id if anny.
	 * @since 1.0.0
	 */
	public function insert_line( $table_name, $value ) {
		global $wpdb;
		// phpcs:ignore
		if ( $wpdb->insert( $wpdb->prefix . $table_name, $value ) ) {
			return $wpdb->insert_id;
		}
		return 0;
	}

	/**
	 * Update table with current value lines.
	 *
	 * @param   string $table_name The table to update.
	 * @param   array  $lines  The array of value lines to update or insert in the table.
	 * @return array The list of insert ids if anny.
	 * @since 1.0.0
	 */
	public function insert_lines( $table_name, $lines ) {
		global $wpdb;
		$result = [];
		foreach ( $lines as $line ) {
			// phpcs:ignore
			if ( $wpdb->insert( $wpdb->prefix . $table_name, $line ) ) {
				$id = $wpdb->insert_id;
				if ( 0 !== $id ) {
					$result[] = $id;
				}
			}
		}
		return $result;
	}

	/**
	 * Delete some lines in a table.
	 *
	 * @param string $table_name The table to update.
	 * @param string $field_name The name of the field containing ids.
	 * @param array  $value  The list of id to delete.
	 * @param string $sep Optional. Separator.
	 * @return int|false The number of rows deleted, or false on error.
	 * @since 1.0.0
	 */
	public function delete_lines( $table_name, $field_name, $value, $sep = '' ) {
		global $wpdb;
		$table_name = $wpdb->prefix . $table_name;
		$sql        = 'DELETE FROM ' . $table_name . ' WHERE ' . $field_name . ' IN (' . $sep . implode( $sep . ',' . $sep, $value ) . $sep . ')';
		// phpcs:ignore
		return $wpdb->query( $sql );
	}

	/**
	 * Load some lines from a table.
	 *
	 * @param string $table_name The table to load.
	 * @param string $field_name The name of the field containing ids.
	 * @param array  $value  The list of id to load.
	 * @param string $sep Optional. Separator.
	 * @return array The loaded lines.
	 * @since 1.0.0
	 */
	public function load_lines( $table_name, $field_name, $value, $sep = '' ) {
		global $wpdb;
		$table_name = $wpdb->prefix . $table_name;
		$sql        = 'SELECT * FROM ' . $table_name . ' WHERE ' . $field_name . ' IN (' . $sep . implode( $sep . ',' . $sep, $value ) . $sep . ')';
		// phpcs:ignore
		return $wpdb->get_results( $sql, ARRAY_A );
	}

	/**
	 * Update table with current value line.
	 *
	 * @param string $table_name The table name.
	 * @param array  $value The values to update or insert in the table.
	 * @since 1.0.0
	 */
	public function insert_update( $table_name, $value ) {
		$field_insert = [];
		$value_insert = [];
		$value_update = [];
		foreach ( $value as $k => $v ) {
			$field_insert[] = '`' . $k . '`';
			$value_insert[] = "'" . $v . "'";
			$value_update[] = '`' . $k . '`=' . "'" . $v . "'";
		}
		if ( count( $field_insert ) > 0 ) {
			global $wpdb;
			$sql  = 'INSERT INTO `' . $wpdb->prefix . $table_name . '` ';
			$sql .= '(' . implode( ',', $field_insert ) . ') ';
			$sql .= 'VALUES (' . implode( ',', $value_insert ) . ') ';
			$sql .= 'ON DUPLICATE KEY UPDATE ' . implode( ',', $value_update ) . ';';
			// phpcs:ignore
			$wpdb->query( $sql );
		}
	}

	/**
	 * Insert in a table with current value line.
	 *
	 * @param string $table_name The table name.
	 * @param array  $value The values to update or insert in the table.
	 * @since 1.0.0
	 */
	public function insert_ignore( $table_name, $value ) {
		$field_insert = [];
		$value_insert = [];
		foreach ( $value as $k => $v ) {
			$field_insert[] = '`' . $k . '`';
			$value_insert[] = "'" . $v . "'";
		}
		if ( count( $field_insert ) > 0 ) {
			global $wpdb;
			$sql  = 'INSERT IGNORE INTO `' . $wpdb->prefix . $table_name . '` ';
			$sql .= '(' . implode( ',', $field_insert ) . ') ';
			$sql .= 'VALUES (' . implode( ',', $value_insert ) . ');';
			// phpcs:ignore
			$wpdb->query( $sql );
		}
	}

	/**
	 * Delete some old lines in a table.
	 *
	 * @param   string  $table_name The table to update.
	 * @param   string  $field_name   The name of the field containing ids.
	 * @param   integer $limit  The number of lines to delete.
	 * @return int|false The number of rows deleted, or false on error.
	 * @since 1.0.0
	 */
	public function rotate( $table_name, $field_name, $limit ) {
		global $wpdb;
		$table_name = $wpdb->prefix . $table_name;
		$sql        = 'DELETE FROM ' . $table_name . ' ORDER BY ' . $field_name . ' ASC LIMIT ' . $limit;
		// phpcs:ignore
		return $wpdb->query( $sql );
	}

	/**
	 * Delete some old lines in a table.
	 *
	 * @param string  $table_name The table to update.
	 * @param string  $field_name The name of the field containing timestamp.
	 * @param integer $interval The number of hours of age to delete.
	 * @return int|false The number of rows deleted, or false on error.
	 * @since 1.0.0
	 */
	public function purge( $table_name, $field_name, $interval ) {
		global $wpdb;
		$table_name = $wpdb->base_prefix . $table_name;
		$sql        = 'DELETE FROM ' . $table_name . ' WHERE (' . $field_name . ' < NOW() - INTERVAL ' . $interval . ' HOUR);';
		// phpcs:ignore
		return $wpdb->query( $sql );
	}

	/**
	 * Drop a table.
	 *
	 * @param string $table_name The table to drop.
	 * @since 1.0.0
	 * @access protected
	 */
	protected static function drop( $table_name ) {
		global $wpdb;
		$sql = 'DROP TABLE IF EXISTS ' . $wpdb->prefix . $table_name;
		// phpcs:ignore
		$wpdb->query( $sql );
	}

	/**
	 * Count the number of records in a table.
	 *
	 * @param string $table_name The table to count.
	 * @return integer Count of records.
	 * @since 1.0.0
	 */
	public function count_lines( $table_name ) {
		$result = -1;
		global $wpdb;
		$sql = 'SELECT COUNT(*) as CNT FROM `' . $wpdb->prefix . $table_name . '`;';
		// phpcs:ignore
		$cnt = $wpdb->get_results( $sql, ARRAY_A );
		if ( count( $cnt ) > 0 ) {
			if ( array_key_exists( 'CNT', $cnt[0] ) ) {
				$result = $cnt[0]['CNT'];
			}
		}
		return $result;
	}

	/**
	 * Performs a safe add column.
	 *
	 * @param string $table  Table name.
	 * @param string $column Column name.
	 * @param string $alter  Query to perform.
	 * @return boolean True if the operation was successful, false otherwise.
	 * @since 1.0.0
	 */
	public function safe_add_column( $table, $column, $alter ) {
		global $wpdb;
		$result = false;
		if ( ! $wpdb->get_var( "SHOW COLUMNS FROM `" . $table . "` LIKE '" . $column . "'" ) ) {
			try {
				// phpcs:ignore
				$wpdb->query( $alter );
				$result = true;
			}
			catch ( \Exception $ex ) {
				$result = false;
			}
		}
		return $result;
	}

}
