<?php

namespace Glovario\Repository;

use Glovario\Domain\BuiltinVariables;

defined( 'ABSPATH' ) || exit;

final class VariableRepository {
	const CACHE_GROUP = 'glovario_variables';
	private $local    = array();

	private function table() {
		global $wpdb;
		return $wpdb->prefix . 'glovario_variables';
	}

	public function find_by_key( $key ) {
		global $wpdb;
		$key = sanitize_key( $key );
		// Keyed by site: on multisite the same instance may serve several sites after switch_to_blog().
		$site = get_current_blog_id();
		if ( isset( $this->local[ $site ][ $key ] ) ) {
			return false === $this->local[ $site ][ $key ] ? null : $this->local[ $site ][ $key ];
		}

		$cached = wp_cache_get( $key, self::CACHE_GROUP, false, $found );
		if ( $found ) {
			$this->local[ $site ][ $key ] = $cached;
			return false === $cached ? null : $cached;
		}

		$row                          = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE variable_key = %s', $this->table(), $key ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Glovario's own table (no WordPress API for custom tables); cached above by key.
		$row                          = $row ? $row : false;
		$this->local[ $site ][ $key ] = $row;
		wp_cache_set( $key, $row, self::CACHE_GROUP );
		return false === $row ? null : $row;
	}

	public function find( $id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $this->table(), absint( $id ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Glovario's own table (no WordPress API for custom tables); admin lookup by ID before edits (resolution reads by key through the cached find_by_key()).
	}

	public function all( $args = array() ) {
		global $wpdb;
		$args   = wp_parse_args(
			$args,
			array(
				'number'   => 100,
				'offset'   => 0,
				'search'   => '',
				'category' => '',
				'type'     => '',
				'orderby'  => 'label',
				'order'    => 'ASC',
			)
		);
		$number = min( 500, max( 1, absint( $args['number'] ) ) );
		$offset = absint( $args['offset'] );
		$categories = $wpdb->prefix . 'glovario_categories';
		list( $where, $values ) = $this->query_conditions( $args );
		$order_columns = array(
			'label'      => 'variables.label',
			'key'        => 'variables.variable_key',
			'category'   => 'categories.name',
			'updated_at' => 'variables.updated_at',
		);
		$orderby = $order_columns[ $args['orderby'] ] ?? $order_columns['label'];
		$order   = 'DESC' === strtoupper( (string) $args['order'] ) ? 'DESC' : 'ASC';
		// ORDER BY uses allowlisted columns only; everything else is a placeholder.
		$sql    = 'SELECT variables.*, categories.name AS category_name, categories.slug AS category_slug FROM %i variables LEFT JOIN %i categories ON categories.id = variables.category_id' . $where . ' ORDER BY ' . $orderby . ' ' . $order . ', variables.id ASC LIMIT %d OFFSET %d';
		$values = array_merge( array( $this->table(), $categories ), $values, array( $number, $offset ) );
		return $wpdb->get_results( $wpdb->prepare( $sql, $values ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Glovario's own table (no WordPress API for custom tables); $sql is built from the screen's filters but holds placeholders and allowlisted columns only. Admin list and editor search, paged and filtered: not cached.
	}

	public function count( $args = array() ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'search'   => '',
				'category' => '',
				'type'     => '',
			)
		);
		list( $where, $values ) = $this->query_conditions( $args );
		$sql = 'SELECT COUNT(*) FROM %i variables' . $where;
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( $this->table() ), $values ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Glovario's own table (no WordPress API for custom tables); $sql is built from the screen's filters but holds placeholders only. Admin counts for the list and Overview: not cached.
	}

	private function query_conditions( $args ) {
		global $wpdb;
		$clauses = array();
		$values  = array();
		$search  = trim( (string) ( $args['search'] ?? '' ) );
		if ( '' !== $search ) {
			$like      = '%' . $wpdb->esc_like( $search ) . '%';
			$clauses[] = '(variables.label LIKE %s OR variables.variable_key LIKE %s OR variables.value LIKE %s OR variables.description LIKE %s)';
			$values    = array_merge( $values, array( $like, $like, $like, $like ) );
		}
		$category = $args['category'] ?? '';
		if ( 'uncategorized' === $category ) {
			$clauses[] = 'variables.category_id IS NULL';
		} elseif ( absint( $category ) ) {
			$clauses[] = 'variables.category_id = %d';
			$values[]  = absint( $category );
		}
		$type = sanitize_key( $args['type'] ?? '' );
		if ( '' !== $type ) {
			$clauses[] = 'variables.type = %s';
			$values[]  = $type;
		}
		return array( $clauses ? ' WHERE ' . implode( ' AND ', $clauses ) : '', $values );
	}

	public function insert( $data ) {
		global $wpdb;
		if ( BuiltinVariables::is_reserved_key( $data['variable_key'] ?? '' ) ) {
			return new \WP_Error( 'reserved_key', __( 'Keys beginning with glovario_builtin_ are reserved for read-only built-in variables.', 'glovario' ) );
		}
		if ( $this->find_by_key( $data['variable_key'] ) ) {
			return new \WP_Error( 'duplicate_key', __( 'A variable with this key already exists.', 'glovario' ) );
		}
		$now = current_time( 'mysql', true );
		$ok  = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Glovario's own table (no WordPress API for custom tables); invalidate() clears the key cache after the write.
			$this->table(),
			array(
				'variable_key' => $data['variable_key'],
				'label'        => $data['label'],
				'type'         => $data['type'],
				'value'        => $data['value'],
				'description'  => $data['description'],
				'category_id'  => ! empty( $data['category_id'] ) ? absint( $data['category_id'] ) : null,
				'created_at'   => $now,
				'updated_at'   => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s' )
		);
		if ( ! $ok ) {
			return new \WP_Error( 'database_error', __( 'The variable could not be saved.', 'glovario' ) );
		}
		$this->invalidate( $data['variable_key'] );
		$variable = $this->find( $wpdb->insert_id );
		do_action( 'glovario_after_variable_saved', $variable, null );
		return $variable;
	}

	public function update( $id, $data ) {
		global $wpdb;
		$old = $this->find( $id );
		if ( ! $old ) {
			return new \WP_Error( 'not_found', __( 'Variable not found.', 'glovario' ) );
		}
		if ( BuiltinVariables::is_reserved_key( $data['variable_key'] ?? '' ) ) {
			return new \WP_Error( 'reserved_key', __( 'Keys beginning with glovario_builtin_ are reserved for read-only built-in variables.', 'glovario' ) );
		}
		$duplicate = $this->find_by_key( $data['variable_key'] );
		if ( $duplicate && (int) $duplicate['id'] !== (int) $id ) {
			return new \WP_Error( 'duplicate_key', __( 'A variable with this key already exists.', 'glovario' ) );
		}
		$category_id = array_key_exists( 'category_id', $data ) ? ( $data['category_id'] ? absint( $data['category_id'] ) : null ) : $old['category_id'];
		$ok          = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Glovario's own table (no WordPress API for custom tables); invalidate() clears the key cache after the write.
			$this->table(),
			array(
				'variable_key' => $data['variable_key'],
				'label'        => $data['label'],
				'type'         => $data['type'],
				'value'        => $data['value'],
				'description'  => $data['description'],
				'category_id'  => $category_id,
				'updated_at'   => current_time( 'mysql', true ),
			),
			array( 'id' => absint( $id ) ),
			array( '%s', '%s', '%s', '%s', '%s', '%d', '%s' ),
			array( '%d' )
		);
		if ( false === $ok ) {
			return new \WP_Error( 'database_error', __( 'The variable could not be updated.', 'glovario' ) );
		}
		$this->invalidate( $old['variable_key'] );
		$this->invalidate( $data['variable_key'] );
		$variable = $this->find( $id );
		do_action( 'glovario_after_variable_saved', $variable, $old );
		return $variable;
	}

	public function assign_category( $id, $category_id ) {
		global $wpdb;
		$old = $this->find( $id );
		if ( ! $old ) {
			return new \WP_Error( 'not_found', __( 'Variable not found.', 'glovario' ) );
		}
		$ok = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Glovario's own table (no WordPress API for custom tables); invalidate() clears the key cache after the write.
			$this->table(),
			array(
				'category_id' => $category_id ? absint( $category_id ) : null,
				'updated_at'  => current_time( 'mysql', true ),
			),
			array( 'id' => absint( $id ) ),
			array( '%d', '%s' ),
			array( '%d' )
		);
		if ( false === $ok ) {
			return new \WP_Error( 'database_error', __( 'The variable category could not be updated.', 'glovario' ) );
		}
		$this->invalidate( $old['variable_key'] );
		$variable = $this->find( $id );
		do_action( 'glovario_after_variable_saved', $variable, $old );
		return $variable;
	}

	public function delete( $id ) {
		global $wpdb;
		$old = $this->find( $id );
		if ( ! $old ) {
			return false;
		}
		$deleted = (bool) $wpdb->delete( $this->table(), array( 'id' => absint( $id ) ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Glovario's own table (no WordPress API for custom tables); invalidate() clears the key cache after the write.
		if ( $deleted ) {
			$this->invalidate( $old['variable_key'] );
			do_action( 'glovario_variable_deleted', $old );
		}
		return $deleted;
	}

	public function invalidate( $key ) {
		$key = sanitize_key( $key );
		unset( $this->local[ get_current_blog_id() ][ $key ] );
		wp_cache_delete( $key, self::CACHE_GROUP );
	}
}