<?php

namespace Glovario\Repository;

defined( 'ABSPATH' ) || exit;

final class CategoryRepository {
	const CACHE_GROUP = 'glovario_categories';

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

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

	public function all() {
		global $wpdb;
		$cached = wp_cache_get( 'all', self::CACHE_GROUP, false, $found );
		if ( $found ) {
			return $cached;
		}
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY name ASC', $this->table() ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Glovario's own table (no WordPress API for custom tables); cached above.
		wp_cache_set( 'all', $rows, self::CACHE_GROUP );
		return $rows;
	}

	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 when a form is saved or edited.
	}

	/**
	 * Whether another category already uses this name or its identifier.
	 *
	 * @param string $name       Category name.
	 * @param int    $exclude_id Category being renamed, if any.
	 * @return bool
	 */
	public function name_taken( $name, $exclude_id = 0 ) {
		$slug = sanitize_title( $name );
		foreach ( $this->all() as $category ) {
			if ( (int) $category['id'] === (int) $exclude_id ) {
				continue;
			}
			if ( $slug === $category['slug'] || 0 === strcasecmp( $name, $category['name'] ) ) {
				return true;
			}
		}
		return false;
	}

	public function count() {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Glovario's own table (no WordPress API for custom tables); shown on the Overview screen only.
	}

	public function insert( $data ) {
		global $wpdb;
		$name = sanitize_text_field( $data['name'] ?? '' );
		$slug = sanitize_title( $data['slug'] ?? $name );
		if ( '' === $name || '' === $slug ) {
			return new \WP_Error( 'invalid_category', __( 'Enter a valid category name.', 'glovario' ) );
		}
		if ( $this->name_taken( $name ) ) {
			return new \WP_Error( 'duplicate_category', __( 'A category with this name 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 category list cache after the write.
			$this->table(),
			array(
				'name'        => $name,
				'slug'        => $slug,
				'description' => sanitize_textarea_field( $data['description'] ?? '' ),
				'created_at'  => $now,
				'updated_at'  => $now,
			),
			array( '%s', '%s', '%s', '%s', '%s' )
		);
		if ( ! $ok ) {
			return new \WP_Error( 'category_database_error', __( 'The category could not be created. Its name may already be in use.', 'glovario' ) );
		}
		$this->invalidate();
		$category = $this->find( $wpdb->insert_id );
		do_action( 'glovario_category_saved', $category, null );
		return $category;
	}

	public function update( $id, $data ) {
		global $wpdb;
		$old = $this->find( $id );
		if ( ! $old ) {
			return new \WP_Error( 'category_not_found', __( 'Category not found.', 'glovario' ) );
		}
		$name = sanitize_text_field( $data['name'] ?? '' );
		if ( '' === $name ) {
			return new \WP_Error( 'invalid_category', __( 'Enter a valid category name.', 'glovario' ) );
		}
		if ( $this->name_taken( $name, $id ) ) {
			return new \WP_Error( 'duplicate_category', __( 'A category with this name already exists.', '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 category list cache after the write.
			$this->table(),
			array(
				'name'        => $name,
				'description' => sanitize_textarea_field( $data['description'] ?? '' ),
				'updated_at'  => current_time( 'mysql', true ),
			),
			array( 'id' => absint( $id ) ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
		if ( false === $ok ) {
			return new \WP_Error( 'category_database_error', __( 'The category could not be updated.', 'glovario' ) );
		}
		$this->invalidate();
		$category = $this->find( $id );
		do_action( 'glovario_category_saved', $category, $old );
		return $category;
	}

	public function delete( $id ) {
		global $wpdb;
		$old = $this->find( $id );
		if ( ! $old ) {
			return false;
		}
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET category_id = NULL WHERE category_id = %d', $this->variables_table(), absint( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Glovario's own table (no WordPress API for custom tables); the variables cache group is flushed below.
		$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 category list cache after the write.
		if ( $deleted ) {
			$this->invalidate();
			if ( function_exists( 'wp_cache_flush_group' ) ) {
				wp_cache_flush_group( VariableRepository::CACHE_GROUP );
			}
			do_action( 'glovario_category_deleted', $old );
		}
		return $deleted;
	}

	private function invalidate() {
		wp_cache_delete( 'all', self::CACHE_GROUP );
	}
}