<?php

declare(strict_types=1);

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

final class RC_Area_Repository {

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

	public static function get( int $id ): ?object {
		global $wpdb;
		$table = RC_Database::table( 'areas' );
		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( 'areas' );
		$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( 'areas' );

		$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( 'areas' );
		$result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		return (bool) $result;
	}

	public static function move_up( int $id ): void {
		global $wpdb;
		$table = RC_Database::table( 'areas' );
		$area  = self::get( $id );
		if ( ! $area ) {
			return;
		}
		$prev = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, sort_order FROM {$table} WHERE sort_order < %d ORDER BY sort_order DESC LIMIT 1",
				(int) $area->sort_order
			)
		);
		if ( ! $prev ) {
			return;
		}
		$wpdb->update( $table, array( 'sort_order' => (int) $prev->sort_order ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'update', $table );
		$wpdb->update( $table, array( 'sort_order' => (int) $area->sort_order ), array( 'id' => (int) $prev->id ), array( '%d' ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'update', $table );
	}

	public static function repair_sort_orders(): void {
		global $wpdb;
		$table = RC_Database::table( 'areas' );
		$areas = $wpdb->get_results(
			"SELECT id, sort_order FROM {$table} ORDER BY IF(sort_order=0, 999999, sort_order) ASC, name ASC"
		);
		$order = 1;
		foreach ( $areas as $area ) {
			if ( (int) $area->sort_order !== $order ) {
				$wpdb->update( $table, array( 'sort_order' => $order ), array( 'id' => (int) $area->id ), array( '%d' ), array( '%d' ) );
				RC_Exception::throw_on_db_error( 'update', $table );
			}
			$order++;
		}
	}

	public static function move_down( int $id ): void {
		global $wpdb;
		$table = RC_Database::table( 'areas' );
		$area  = self::get( $id );
		if ( ! $area ) {
			return;
		}
		$next = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, sort_order FROM {$table} WHERE sort_order > %d ORDER BY sort_order ASC LIMIT 1",
				(int) $area->sort_order
			)
		);
		if ( ! $next ) {
			return;
		}
		$wpdb->update( $table, array( 'sort_order' => (int) $next->sort_order ), array( 'id' => $id ), array( '%d' ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'update', $table );
		$wpdb->update( $table, array( 'sort_order' => (int) $area->sort_order ), array( 'id' => (int) $next->id ), array( '%d' ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'update', $table );
	}
}
