<?php
/**
 * Base repository providing common CRUD against a custom table.
 *
 * @package Lucrativeit_Subscription_Recurring_Billing
 */

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

/**
 * Abstract base data-access object.
 */
abstract class LUCRSURE_Repository {

	/**
	 * Table name without the WordPress / plugin prefix (e.g. "customers").
	 *
	 * @var string
	 */
	protected $table = '';

	/**
	 * Column format map (column => %s|%d|%f) used by $wpdb.
	 *
	 * @var array<string,string>
	 */
	protected $formats = array();

	/**
	 * Fully-qualified table name.
	 *
	 * @return string
	 */
	public function table() {
		return lucrsure_table( $this->table );
	}

	/**
	 * Resolve the $wpdb format strings for a data array, preserving key order.
	 *
	 * @param array $data Data keyed by column.
	 * @return string[]
	 */
	protected function formats_for( array $data ) {
		$formats = array();
		foreach ( array_keys( $data ) as $column ) {
			$formats[] = isset( $this->formats[ $column ] ) ? $this->formats[ $column ] : '%s';
		}
		return $formats;
	}

	/**
	 * Insert a row and return the new ID.
	 *
	 * @param array $data Column data.
	 * @return int Inserted ID (0 on failure).
	 */
	public function insert( array $data ) {
		global $wpdb;

		$now = current_time( 'mysql' );
		if ( ! isset( $data['created_at'] ) ) {
			$data['created_at'] = $now;
		}
		$data['updated_at'] = $now;

		$result = $wpdb->insert( $this->table(), $data, $this->formats_for( $data ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $result ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Update a row by ID.
	 *
	 * @param int   $id   Row ID.
	 * @param array $data Column data.
	 * @return bool
	 */
	public function update( $id, array $data ) {
		global $wpdb;

		$data['updated_at'] = current_time( 'mysql' );

		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->table(),
			$data,
			array( 'id' => (int) $id ),
			$this->formats_for( $data ),
			array( '%d' )
		);
		return false !== $result;
	}

	/**
	 * Delete a row by ID.
	 *
	 * @param int $id Row ID.
	 * @return bool
	 */
	public function delete( $id ) {
		global $wpdb;
		return (bool) $wpdb->delete( $this->table(), array( 'id' => (int) $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Find a single row by ID.
	 *
	 * @param int $id Row ID.
	 * @return array|null
	 */
	public function find( $id ) {
		global $wpdb;
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $this->table(), (int) $id ),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Count all rows, optionally filtered by a column = value pair.
	 *
	 * @param string $column Optional column to filter on.
	 * @param mixed  $value  Filter value.
	 * @return int
	 */
	public function count( $column = '', $value = null ) {
		global $wpdb;
		if ( $column ) {
			$column = preg_replace( '/[^a-z0-9_]/i', '', $column );
			return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE %i = %s', $this->table(), $column, $value )
			);
		}
		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->table() )
		);
	}

	/**
	 * Get all rows ordered by a column.
	 *
	 * @param string $orderby Column to order by.
	 * @param string $order   ASC|DESC.
	 * @return array[]
	 */
	public function all( $orderby = 'id', $order = 'DESC' ) {
		global $wpdb;
		$orderby = preg_replace( '/[^a-z0-9_]/i', '', $orderby );
		$order   = 'ASC' === strtoupper( $order ) ? 'ASC' : 'DESC';
		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->prepare(
				'SELECT * FROM %i ORDER BY %i ' . $order, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->table(),
				$orderby
			),
			ARRAY_A
		);
	}
}
