<?php
namespace AcademyProGroupPlus\Db\Models;

use AcademyProGroupPlus\Db\Common;
use AcademyProGroupPlus\Interfaces\Db\ModelInterface;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * @class Model
 */
abstract class Model extends Common implements ModelInterface {

	protected array $columns = [];

	public static function ins() {
		static $instance = [];
		$lsb_class = get_called_class();
		if ( ! isset( $instance[ $lsb_class ] ) ) {
			$instance[ $lsb_class ] = new static(); // lsb
		}
		return $instance[ $lsb_class ];
	}

	/**
	 * @method get
	 * Get a group data from table
	 * @param int $id
	 * @return ?stdClass
	 */
	public function get_by_id( int $id ) : ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared 
				"SELECT * FROM {$this->table} 
				WHERE id=%d ",
				$id
			),
			ARRAY_A
		) ?? [];
	}

	/**
	 * @method get
	 * Get a group data from table
	 * @param  array  $conditions
	 * @param  array  $args
	 * @param  int    $per_page
	 * @param  int    $current_page
	 * @param  string $order_by
	 * @param  string $order_direction
	 * @return ?stdClass
	 */
	public function get(
		array $conditions = [],
		array $args = [],
		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC'
	) : array {
		global $wpdb;

		if ( $per_page <= 0 ) {
			return [];
		}

		$query = "SELECT * FROM {$this->table} ";
		$count_query = "SELECT COUNT(*) FROM {$this->table} ";

		if ( ! empty( $conditions ) ) {
			$query .= ' WHERE ' . implode( ' AND ', $conditions );
			$count_query .= ' WHERE ' . implode( ' AND ', $conditions );
		}

		if ( empty( $args ) && empty( $conditions ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			$total = $wpdb->get_var( $count_query );
		} else {

			// get a single row
			if ( 1 === $per_page ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
				return $wpdb->get_row( $wpdb->prepare( $count_query, ...$args ), ARRAY_A ) ?? [];
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total = intval( $wpdb->get_var( $wpdb->prepare( $count_query, ...$args ) ) );
		}

		// order by
		if ( ! in_array( $order_by, $this->columns ) ) {
			$order_by = 'updated_at';
		}
		// order direction
		if (
			! in_array( strtoupper( $order_direction ), [ 'ASC', 'DESC' ] )
		) {
			$order_direction = 'ASC';
		}
		$query .= " ORDER BY {$order_by} {$order_direction}";

		// pagination
		$current_page = absint( $current_page );
		$per_page = absint( $per_page );
		if ( $current_page < 1 || $current_page > $total ) {
			$current_page = 1;
		}
		if ( $per_page < 1 ) {
			$per_page = 20;
		}
		$offset = ( $current_page - 1 ) * $per_page;

		$query .= ' LIMIT %d OFFSET %d';
		$args[] = $per_page;
		$args[] = $offset;

		return [
			'current_page' => $current_page,
			'total_pages'  => ( $per_page > 0 && $total > 0 ) ? ceil( $total / $per_page ) : 1,
			'per_page'     => $per_page,
			'total'        => $total,
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			'data'         => $wpdb->get_results( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? []
		];

	}

	/**
	 * @method delete
	 * Delete rows from table
	 * @param  array $where
	 * @return bool
	 */
	public function delete( array $where ) : bool {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->delete(
			$this->table,
			$where,
			self::get_format_by_data_type( $where )
		);

		return is_int( $result ) ? true : false;
	}

	/**
	 * @method create
	 * Create new row at table
	 * @param  array $data
	 * @return int|null
	 */
	public function create( array $data ) : ?int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert(
			$this->table,
			$data,
			self::get_format_by_data_type( $data )
		);

		return $wpdb->insert_id;
	}

	/**
	 * @method update
	 * Update rows at table
	 * @param  array $data
	 * @param  array $where
	 * @return bool
	 */
	public function update( array $data, array $where ) : bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			$this->table,
			$data,
			$where,
			self::get_format_by_data_type( $data ),
			self::get_format_by_data_type( $where )
		);
		return is_int( $result ) ? true : false;
	}


	public function get_advance(
		string $select,
		string $count_col,
		string $table_alias,
		string $join,
		array $where,
		array $args,
		string $after_query = '',
		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC'
	) : array {
		global $wpdb;
		$query = "SELECT {$select} FROM {$this->table} as {$table_alias} {$join} ";
		$count_query = "SELECT COUNT({$count_col}) FROM {$this->table} as {$table_alias} {$join} ";

		if ( ! empty( $where ) ) {
			$query .= ' WHERE ' . implode( ' AND ', $where );
			$count_query .= ' WHERE ' . implode( ' AND ', $where );
		}

		if ( empty( $args ) && empty( $where ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total = $wpdb->get_var( $count_query );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total = intval( $wpdb->get_var( $wpdb->prepare( $count_query, ...$args ) ) );
		}

		if ( ! empty( $after_query ) ) {
			$query .= $after_query;
		}

		// get a single row
		if ( 1 === $per_page ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			return $wpdb->get_row( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? [];
		}

		// order by
		if ( ! in_array( $order_by, $this->columns ) ) {
			$order_by = 'updated_at';
		}
		// order direction
		if (
			! in_array( strtoupper( $order_direction ), [ 'ASC', 'DESC' ] )
		) {
			$order_direction = 'ASC';
		}
		$query .= " ORDER BY {$order_by} {$order_direction}";

		// pagination
		$current_page = absint( $current_page );
		$per_page = absint( $per_page );
		if ( $current_page < 1 || $current_page > $total ) {
			$current_page = 1;
		}
		if ( $per_page < 1 ) {
			$per_page = 20;
		}
		$offset = ( $current_page - 1 ) * $per_page;

		$query .= ' LIMIT %d OFFSET %d';
		$args[] = $per_page;
		$args[] = $offset;

		// wp_send_json_error([$wpdb->prepare($query, ...$args)]);
		return [
			'current_page' => $current_page,
			'total_pages'  => ( $per_page > 0 && $total > 0 ) ? ceil( $total / $per_page ) : 1,
			'per_page'     => $per_page,
			'total'        => $total,
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			'data'         => $wpdb->get_results( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? [],
		];

	}


	public function get_advance_ct(
		string $select,
		string $count_col,
		string $table,
		string $join,
		array $where,
		array $args,
		string $after_query = '',
		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC'
	) : array {
		global $wpdb;
		$query = "SELECT {$select} FROM {$table} {$join} ";
		$count_query = "SELECT COUNT({$count_col}) FROM {$table} {$join} ";

		if ( ! empty( $where ) ) {
			$query .= ' WHERE ' . implode( ' AND ', $where );
			$count_query .= ' WHERE ' . implode( ' AND ', $where );
		}

		if ( empty( $args ) && empty( $where ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total = $wpdb->get_var( $count_query );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total = intval( $wpdb->get_var( $wpdb->prepare( $count_query, ...$args ) ) );
		}

		if ( ! empty( $after_query ) ) {
			$query .= $after_query;
		}

		// get a single row
		if ( 1 === $per_page ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			return $wpdb->get_row( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? [];
		}

		// order by
		if ( empty( $order_by ) ) {
			$order_by = 'updated_at';
		}
		// order direction
		if (
			! in_array( strtoupper( $order_direction ), [ 'ASC', 'DESC' ] )
		) {
			$order_direction = 'ASC';
		}
		$query .= " ORDER BY {$order_by} {$order_direction}";

		// pagination
		$current_page = absint( $current_page );
		$per_page = absint( $per_page );
		if ( $current_page < 1 || $current_page > $total ) {
			$current_page = 1;
		}
		if ( $per_page < 1 ) {
			$per_page = 20;
		}
		$offset = ( $current_page - 1 ) * $per_page;

		$query .= ' LIMIT %d OFFSET %d';
		$args[] = $per_page;
		$args[] = $offset;

		return [
			'current_page' => $current_page,
			'total_pages'  => ( $per_page > 0 && $total > 0 ) ? ceil( $total / $per_page ) : 1,
			'per_page'     => $per_page,
			'total'        => $total,
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			'data'         => $wpdb->get_results( $wpdb->prepare( $query, ...$args ), ARRAY_A ) ?? []
		];

	}
	/**
	 * @method get_format_by_data_type
	 * Get data fromat from array element
	 * @param  array $data
	 * @return array
	 */
	public static function get_format_by_data_type( array $data ) : array {
		$formats = [];
		foreach ( $data as $value ) {
			if ( is_int( $value ) ) {
				$formats[] = '%d';
			} elseif ( is_float( $value ) ) {
				$formats[] = '%f';
			} elseif ( is_bool( $value ) ) {
				$formats[] = '%d';
			} elseif ( is_string( $value ) || is_null( $value ) ) {
				$formats[] = '%s';
			} else {
				$formats[] = '%s';
			}
		}
		return $formats;
	}
}
