<?php

declare(strict_types=1);

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

final class RC_Menu_Type_Repository {

	public static function get_all(): array {
		global $wpdb;
		$table = RC_Database::table( 'menu_types' );
		return $wpdb->get_results( "SELECT id, name FROM {$table} ORDER BY sort_order ASC, name ASC" ) ?: array();
	}

	public static function get( int $id ): ?object {
		global $wpdb;
		$table = RC_Database::table( 'menu_types' );
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) ) ?: null;
	}

	public static function find_or_create( string $name ): int {
		global $wpdb;
		$table = RC_Database::table( 'menu_types' );
		$name  = trim( $name );
		if ( '' === $name ) {
			return 0;
		}
		$id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE name = %s", $name ) );
		if ( $id ) {
			return (int) $id;
		}
		$max = (int) $wpdb->get_var( "SELECT COALESCE(MAX(sort_order), 0) FROM {$table}" );
		$wpdb->insert( $table, array( 'name' => $name, 'sort_order' => $max + 1 ), array( '%s', '%d' ) );
		RC_Exception::throw_on_db_error( 'insert', $table );
		return (int) $wpdb->insert_id;
	}

	public static function upsert( array $data, ?int $id = null ): int {
		global $wpdb;
		$table = RC_Database::table( 'menu_types' );
		$row = array( 'name' => sanitize_text_field( (string) ( $data['name'] ?? '' ) ) );
		$formats = array( '%s' );
		if ( array_key_exists( 'sort_order', $data ) && null !== $data['sort_order'] ) {
			$row['sort_order'] = (int) $data['sort_order'];
			$formats[] = '%d';
		}
		if ( $id && $id > 0 ) {
			$wpdb->update( $table, $row, array( 'id' => $id ), $formats, array( '%d' ) );
			RC_Exception::throw_on_db_error( 'update', $table );
			return $id;
		}
		if ( ! isset( $row['sort_order'] ) ) {
			$row['sort_order'] = (int) $wpdb->get_var( "SELECT COALESCE(MAX(sort_order), 0) + 1 FROM {$table}" );
			$formats[] = '%d';
		}
		$wpdb->insert( $table, $row, $formats );
		RC_Exception::throw_on_db_error( 'insert', $table );
		return (int) $wpdb->insert_id;
	}

	public static function delete( int $id ): bool {
		global $wpdb;
		$table = RC_Database::table( 'menu_types' );
		$result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		return (bool) $result;
	}

	public static function get_name( int $id ): string {
		global $wpdb;
		$table = RC_Database::table( 'menu_types' );
		$name  = $wpdb->get_var( $wpdb->prepare( "SELECT name FROM {$table} WHERE id = %d", $id ) );
		return $name ?: '';
	}

	public static function options_html( ?int $selected_id = null ): string {
		$items = self::get_all();
		$html = '<option value="">' . esc_html__( 'Select menu type...', 'restaurant-costing' ) . '</option>';
		foreach ( $items as $item ) {
			$sel = ( $selected_id && (int) $item->id === $selected_id ) ? ' selected' : '';
			$html .= '<option value="' . (int) $item->id . '"' . $sel . '>' . esc_html( $item->name ) . '</option>';
		}
		return $html;
	}
}
