<?php

namespace WC_Affiliate\Models;

use WC_Affiliate\Traits\Validation;
use wpdb;

defined( 'ABSPATH' ) || exit;

/**
 * Database Model Class
 *
 * Provides generic database operations for handling CRUD operations across different tables.
 * Includes methods for table creation, data insertion, updates, deletion, and querying.
 *
 * @since   1.0.0
 * @package WC_Affiliate\Models
 */
class Database {

	use Validation;

	/**
	 * The WordPress database access abstraction object.
	 *
	 * @var wpdb
	 */
	protected wpdb $db;

	/**
	 * The full database table name including the prefix.
	 *
	 * @var string
	 */
	protected $table_name;

	/**
	 * The primary key for the specific table.
	 *
	 * @var string
	 */
	protected $primary_key;

	/**
	 * Constructor to set up database operations.
	 *
	 * @param string|null $table_name  Table name for the specific entity (without prefix).
	 * @param string      $primary_key The primary key for the specific table.
	 */
	public function __construct( ?string $table_name = null, string $primary_key = 'id' ) {
		global $wpdb;

		$this->db          = $wpdb;
		$this->table_name  = $table_name;
		$this->primary_key = $primary_key;
	}

	/**
	 * Sets the table name and updates the table name property.
	 *
	 * @param string $table_name Table name for the specific entity (without prefix).
	 *
	 * @return void
	 */
	public function set_table( string $table_name ) {
		$this->table_name = $table_name;
	}

	/**
	 * Gets the table name
	 *
	 * @return string $table_name Table name for the specific entity (with prefix).
	 */
	public function get_table(): ?string {
		return $this->table_name;
	}

	/**
	 * Gets table data with safe column selection
	 *
	 * @param string|array $columns Column names to select. Accepts '*' for all columns or array of column names.
	 *
	 * @return array $data Table data
	 * @todo rename method and maybe merge it with get_rows
	 */
	public function get_data( $columns = '*' ): array {
		// Sanitize column selection - only allow specific columns or *.
		if ( is_array( $columns ) ) {
			// Sanitize each column name to prevent SQL injection.
			$safe_columns = array();
			foreach ( $columns as $column ) {
				// Allow only alphanumeric characters, underscores, and dots for table.column syntax.
				if ( preg_match( '/^[a-zA-Z0-9_\.]+$/', $column ) ) {
					$safe_columns[] = sanitize_key( $column );
				}
			}
			$columns = empty( $safe_columns ) ? '*' : implode( ', ', $safe_columns );
		} elseif ( '*' !== $columns ) {
			// For single column, sanitize it.
			if ( ! preg_match( '/^[a-zA-Z0-9_\.]+$/', $columns ) ) {
				$columns = '*'; // Fallback to * if invalid column name.
			} else {
				$columns = sanitize_key( $columns );
			}
		}

		$query = "SELECT $columns FROM {$this->table_name}";

		return $this->db->get_results( $query );
	}

	/**
	 * Creates a new table in the database based on the table name provided during the object's instantiation.
	 *
	 * @param array $columns Associative array of column definitions.
	 * @param array $options Additional options for table creation like primary key.
	 *
	 * @return bool
	 */
	public function create_table( array $columns, array $options = array() ): bool {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $this->db->get_charset_collate();
		$sql             = "CREATE TABLE {$this->table_name} (\n";

		// Add columns.
		foreach ( $columns as $column => $definition ) {
			$sql .= "$column $definition,\n";
		}

		// Add primary key.
		if ( ! empty( $options['primary_key'] ) ) {
			$sql .= "PRIMARY KEY ({$options['primary_key']}),\n";
		} else {
			$sql .= "PRIMARY KEY ({$this->primary_key}),\n";
		}

		// Add unique keys.
		if ( ! empty( $options['unique_keys'] ) ) {
			foreach ( $options['unique_keys'] as $key => $columns ) {
				$sql .= "UNIQUE KEY $key ($columns),\n";
			}
		}

		// Add indexes.
		if ( ! empty( $options['indexes'] ) ) {
			foreach ( $options['indexes'] as $index => $columns ) {
				$sql .= "INDEX $index ($columns),\n";
			}
		}

		// Remove trailing comma.
		$sql = rtrim( $sql, ",\n" ) . "\n) $charset_collate;";

		// Execute dbDelta.
		dbDelta( $sql );

		// Check if the table exists after creation.
		if ( $this->db->get_var( "SHOW TABLES LIKE '{$this->table_name}'" ) === null ) {
			return false;
		}

		// Add foreign keys.
		if ( ! empty( $options['foreign_keys'] ) ) {
			foreach ( $options['foreign_keys'] as $key => $fk_options ) {
				$ref_table  = $fk_options['ref_table'];
				$ref_column = $fk_options['ref_column'];

				// Ensure the referenced table exists.
				if ( $this->db->get_var( "SHOW TABLES LIKE '$ref_table'" ) === null ) {
					continue;
				}

				// Generate unique foreign key name.
				$unique_key_name = "{$key}_{$this->table_name}";

				// Check if the foreign key already exists.
				$foreign_key_exists = $this->db->get_var(
					$this->db->prepare(
						'SELECT CONSTRAINT_NAME 
	                     FROM information_schema.KEY_COLUMN_USAGE 
	                     WHERE TABLE_NAME = %s AND CONSTRAINT_NAME = %s',
						$this->table_name,
						$unique_key_name
					)
				);

				if ( $foreign_key_exists ) {
					continue;
				}

				// Add the foreign key.
				$fk_sql = "ALTER TABLE {$this->table_name} 
	                       ADD CONSTRAINT $unique_key_name 
	                       FOREIGN KEY ({$fk_options['column']}) 
	                       REFERENCES {$ref_table} ({$ref_column}) 
	                       ON DELETE {$fk_options['on_delete']} ON UPDATE {$fk_options['on_update']};";

				$this->db->query( $fk_sql );
			}
		}

		return true;
	}

	/**
	 * Drops the table from the database.
	 *
	 * @return void
	 */
	public function drop_table() {
		$sql = "DROP TABLE IF EXISTS {$this->table_name}";
		$this->db->query( $sql );
	}

	/**
	 * Prepares a SQL query for safe execution.
	 *
	 * @param string $query   The SQL query with placeholders.
	 * @param mixed  ...$args The values to substitute for placeholders.
	 *
	 * @return string The prepared SQL query.
	 */
	public function prepare( string $query, ...$args ): string {
		return $this->db->prepare( $query, ...$args );
	}

	/**
	 * Retrieves an entry by its primary key ID.
	 *
	 * @param int $id The ID of the entry to retrieve.
	 *
	 * @return object|null The object if found, otherwise null.
	 */
	public function get_by_id( int $id ): ?object {
		$query = $this->db->prepare( "SELECT * FROM {$this->table_name} WHERE {$this->primary_key} = %d", $id );

		return $this->db->get_row( $query );
	}

	/**
	 * Inserts a new entry into the database.
	 *
	 * @param array $data Associative array of data to insert.
	 *
	 * @return int|null The last inserted ID or null on failure.
	 */
	public function insert_row( array $data ): ?int {
		if ( $this->db->insert( $this->table_name, $data ) ) {
			return $this->db->insert_id;
		}

		return null;
	}

	/**
	 * Updates an existing entry.
	 *
	 * @param int   $id   The ID of the entry to update.
	 * @param array $data Associative array of data to update.
	 *
	 * @return bool True if successful, false otherwise.
	 */
	public function update_row( int $id, array $data ): bool {
		return $this->db->update( $this->table_name, $data, array( $this->primary_key => $id ) );
	}

	/**
	 * Deletes an entry by its primary key ID.
	 *
	 * @param int $id The ID of the entry to delete.
	 *
	 * @return bool True if successful, false otherwise.
	 */
	public function delete_row( int $id ): bool {
		return $this->db->delete( $this->table_name, array( $this->primary_key => $id ) );
	}

	/**
	 * Inserts multiple entries into the database.
	 *
	 * @param array $rows Array of associative arrays of data to insert.
	 *
	 * @return array Array of inserted IDs or null on failure.
	 */
	public function insert_rows( array $rows ): ?array {
		$inserted_ids = array();
		foreach ( $rows as $data ) {
			$id = $this->insert_row( $data );

			if ( null === $id ) {
				return null;
			}

			$inserted_ids[] = $id;
		}

		return $inserted_ids;
	}

	/**
	 * Updates multiple entries in the database.
	 *
	 * @param array $rows Array of associative arrays containing 'id' and 'data' keys.
	 *
	 * @return bool True if all updates are successful, false otherwise.
	 */
	public function update_rows( array $rows ): bool {
		foreach ( $rows as $row ) {
			if ( ! $this->update_row( $row['id'], $row['data'] ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Deletes multiple entries from the database.
	 *
	 * @param array $ids Array of IDs of the entries to delete.
	 *
	 * @return bool True if all deletions are successful, false otherwise.
	 */
	public function delete_rows( array $ids ): bool {
		foreach ( $ids as $id ) {
			if ( ! $this->delete_row( $id ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Retrieves an entry based on associative array of conditions.
	 *
	 * @param array $conditions Associative array of conditions.
	 *
	 * @return object|null The object if found, otherwise null.
	 */
	public function get_row( array $conditions ): ?object {
		$where_clause = array();
		$values       = array();

		foreach ( $conditions as $key => $value ) {
			$where_clause[] = "$key = %s";
			$values[]       = $value;
		}

		$where_clause = implode( ' AND ', $where_clause );

		$query = $this->db->prepare( "SELECT * FROM {$this->table_name} WHERE $where_clause", ...$values );

		return $this->db->get_row( $query );
	}

	/**
	 * Retrieves multiple entries based on associative array of conditions.
	 *
	 * This method dynamically builds a query to retrieve rows from the database
	 * table, allowing for flexible conditions, sorting, pagination, and nested conditions.
	 *
	 * CONDITIONS EXAMPLES:
	 * - Basic conditions: `[ [ 'status' => 'active' ], [ 'price' => 100 ] ]`
	 *   Produces: `WHERE status = 'active' AND price = 100`
	 * - Nested conditions: `[ [ 'price' => [ '>', 50 ] ] ]`
	 *   Produces: `WHERE price > 50`
	 * - Multiple nested conditions: `[ [ 'price' => [ '>', 50 ] ], [ 'quantity' => [ '<', 20 ] ] ]`
	 *   Produces: `WHERE price > 50 AND quantity < 20`
	 *
	 * SORTING EXAMPLES:
	 * - Default sorting by ID ascending: `'ASC'`
	 * - For descending order: `'DESC'`
	 *
	 * PAGINATION EXAMPLES:
	 * - Limit to 10 rows: `$limit = 10`
	 * - Skip the first 5 rows: `$offset = 5`
	 *
	 * @param array  $conditions     Associative array of conditions.
	 *                               - For simple conditions: `[ [ 'column' => 'value' ] ]` (e.g., `[ [ 'status' => 'active' ] ]`).
	 *                               - For nested conditions: `[ [ 'column' => [ 'operator', value ] ] ]` (e.g., `[ [ 'price' => [ '>', 50 ] ] ]`).
	 *
	 * @param int    $limit          The number of rows to return. Defaults to 0 (no limit).
	 * @param int    $offset         The number of rows to skip before starting to return results. Defaults to 0.
	 * @param string $order          The sorting order ('ASC' or 'DESC'). Defaults to 'ASC'.
	 *
	 * @return array Array of objects if found, otherwise an empty array.
	 *
	 * USAGE EXAMPLES:
	 *
	 * Example 1: Retrieve all active rows with price > 100, sorted descending
	 * $rows = $db->get_rows(
	 *     [
	 *          [ 'status'  => 'active' ],
	 *          [ 'price'   => [ '>', 100 ]
	 *     ],
	 *     10, // Limit
	 *     0,  // Offset
	 *     'DESC' // Order
	 * );
	 *
	 * Example 2: Retrieve rows with multiple conditions and pagination
	 * $rows = $db->get_rows(
	 *     [
	 *         [ 'price'    => [ '>', 50 ] ],
	 *         [ 'quantity' => [ '<', 20 ] ],
	 *     ],
	 *     5,  // Limit
	 *     10, // Offset
	 *     'ASC' // Order
	 * );
	 *
	 * Example 3: Retrieve rows without any conditions (get all)
	 * $rows = $db->get_rows( [], 20, 0, 'ASC');
	 */
	public function get_rows( array $conditions = array(), int $limit = 0, int $offset = 0, string $order = 'DESC' ): array {
		$where_clause = array();
		$values       = array();

		if ( ! empty( $conditions ) && is_array( $conditions ) ) {
			/**
			 * Ensure 2-level conditions
			 */
			if ( ! is_array( reset( $conditions ) ) ) {
				$conditions = array( $conditions );
			}

			/**
			 * Loop through the conditions to contruct the query
			 */
			foreach ( $conditions as $condition ) {
				if ( is_array( $condition ) ) {
					foreach ( $condition as $column => $value ) {
						// Handle nested conditions like `[ 'key' => [ '>=', 'value' ] ]`.
						if ( is_array( $value ) && count( $value ) === 2 && ! is_array( $value[1] ) ) {
							$operator       = $value[0];
							$_value         = $value[1];
							$placeholder    = is_numeric( $_value ) ? '%d' : '%s';
							$where_clause[] = "{$column} {$operator} {$placeholder}";
							$values[]       = $_value;
						} elseif ( is_array( $value ) && count( $value ) === 2 && strtoupper( $value[0] ) === 'IN' && is_array( $value[1] ) ) {
							$_values        = $value[1];
							$placeholders   = implode( ', ', array_fill( 0, count( $_values ), is_numeric( reset( $_values ) ) ? '%d' : '%s' ) );
							$where_clause[] = "{$column} IN ({$placeholders})";
							$values         = array_merge( $values, $_values );
						} else {
							$placeholder    = is_numeric( $value ) ? '%d' : '%s';
							$where_clause[] = "{$column} = {$placeholder}";
							$values[]       = $value;
						}
					}
				}
			}
		}

		$where_clause_str = ! empty( $where_clause ) ? 'WHERE ' . implode( ' AND ', $where_clause ) : '';

		$order_clause = strtoupper( $order ) === 'DESC' ? ' ORDER BY id DESC' : ' ORDER BY id ASC';

		$query = "SELECT * FROM {$this->table_name} $where_clause_str $order_clause";

		if ( $limit > 0 ) {
			$query   .= ' LIMIT %d';
			$values[] = $limit;

			if ( $offset > 0 ) {
				$query   .= ' OFFSET %d';
				$values[] = $offset;
			}
		}

		$prepared_query = ! empty( $values ) ? $this->db->prepare( $query, ...$values ) : $query;

		return (array) $this->db->get_results( $prepared_query );
	}

	/**
	 * Retrieves the total count of entries based on associative array of conditions.
	 *
	 * @param array $conditions Associative array of conditions.
	 *
	 * @return int The count of entries.
	 */
	public function get_count( array $conditions = array() ): int {
		$where_clause = array();
		$values       = array();

		if ( ! empty( $conditions ) && is_array( $conditions ) ) {
			foreach ( $conditions as $key => $condition ) {
				$where_clause[] = "$key = %s";
				$values[]       = $condition;
			}
		}

		$where_clause_str = ! empty( $where_clause ) ? 'WHERE ' . implode( ' AND ', $where_clause ) : '';
		$query            = "SELECT COUNT(*) FROM {$this->table_name} $where_clause_str";

		$prepared_query = ! empty( $values ) ? $this->db->prepare( $query, ...$values ) : $query;

		return (int) $this->db->get_var( $prepared_query );
	}

	/**
	 * Get the default WordPress table prefix.
	 *
	 * @return string
	 */
	public function get_wp_prefix(): string {
		return $this->db->prefix;
	}

	/**
	 * Get the table prefix.
	 *
	 * @return string
	 */
	public function get_prefix(): string {
		return $this->get_wp_prefix();
	}

	/**
	 * Gets the $db instance
	 */
	public function get_instance() {
		return $this->db;
	}
}
