<?php
/**
 * Wpdb persistence for automation runs.
 *
 * @package Bookline
 */

declare( strict_types=1 );

namespace Bookline\Infrastructure\Persistence;

use Bookline\Domain\Automation\AutomationContext;
use Bookline\Domain\Automation\AutomationRun;
use Bookline\Domain\Automation\AutomationRunRepository;
use Bookline\Domain\Automation\AutomationRunStatus;
use Bookline\Domain\Automation\SkipReason;
use Bookline\Domain\Automation\SubjectType;
use DateTimeImmutable;
use RuntimeException;

/**
 * Automation runs table adapter.
 *
 * @since 0.1.0
 */
final class WpdbAutomationRunRepository extends AbstractWpdbRepository implements AutomationRunRepository {

	/**
	 * {@inheritDoc}
	 *
	 * @param int $id Id.
	 */
	public function find_by_id( int $id ): ?AutomationRun {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `id` = %d LIMIT 1',
			$this->table( 'automation_runs' ),
			$id
		);
		$row = $this->db->get_row( $sql, ARRAY_A );
		return is_array( $row ) ? $this->hydrate( $row ) : null;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int    $automation_id    Automation id.
	 * @param string $trigger_event_id Event id.
	 * @param int    $subject_id       Subject id.
	 */
	public function find_by_dedupe( int $automation_id, string $trigger_event_id, int $subject_id ): ?AutomationRun {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `automation_id` = %d AND `trigger_event_id` = %s AND `subject_id` = %d LIMIT 1',
			$this->table( 'automation_runs' ),
			$automation_id,
			$trigger_event_id,
			$subject_id
		);
		$row = $this->db->get_row( $sql, ARRAY_A );
		return is_array( $row ) ? $this->hydrate( $row ) : null;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param AutomationRun $run Run.
	 *
	 * @throws RuntimeException When the insert fails.
	 */
	public function save( AutomationRun $run ): void {
		$data = [
			'automation_id'       => $run->automation_id(),
			'trigger_event_id'    => $run->trigger_event_id(),
			'subject_type'        => $run->subject_type()->value,
			'subject_id'          => $run->subject_id(),
			'customer_id'         => $run->customer_id(),
			'status'              => $run->status()->value,
			'skip_reason'         => $run->skip_reason()?->value,
			'current_step'        => $run->current_step(),
			'scheduled_for'       => null === $run->scheduled_for() ? null : $this->format_datetime( $run->scheduled_for() ),
			'started_at'          => null === $run->started_at() ? null : $this->format_datetime( $run->started_at() ),
			'completed_at'        => null === $run->completed_at() ? null : $this->format_datetime( $run->completed_at() ),
			'context'             => $this->encode_json( $run->context()->to_array() ),
			'error'               => $run->error(),
			'action_scheduler_id' => $run->action_scheduler_id(),
			'steps'               => $this->encode_json( $run->steps() ),
			'created_at'          => $this->format_datetime( $run->created_at() ),
			'updated_at'          => $this->format_datetime( $run->updated_at() ),
		];

		if ( null === $run->id() ) {
			$existing = $this->find_by_dedupe( $run->automation_id(), $run->trigger_event_id(), $run->subject_id() );
			if ( null !== $existing && null !== $existing->id() ) {
				$run->assign_id( (int) $existing->id() );
				$this->db->update( $this->table( 'automation_runs' ), $data, [ 'id' => $existing->id() ] );
				return;
			}
			$inserted = $this->db->insert( $this->table( 'automation_runs' ), $data );
			if ( false === $inserted ) {
				$again = $this->find_by_dedupe( $run->automation_id(), $run->trigger_event_id(), $run->subject_id() );
				if ( null !== $again && null !== $again->id() ) {
					$run->assign_id( (int) $again->id() );
					return;
				}
				$err = is_string( $this->db->last_error ) && '' !== $this->db->last_error
					? $this->db->last_error
					: 'Could not insert automation run.';
				throw new RuntimeException( esc_html( $err ) );
			}
			$run->assign_id( $this->last_insert_id() );
			return;
		}
		$this->db->update( $this->table( 'automation_runs' ), $data, [ 'id' => $run->id() ] );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param SubjectType $subject_type Subject type.
	 * @param int         $subject_id   Subject id.
	 *
	 * @return list<AutomationRun>
	 */
	public function find_open_for_subject( SubjectType $subject_type, int $subject_id ): array {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `subject_type` = %s AND `subject_id` = %d AND `status` IN (%s, %s)',
			$this->table( 'automation_runs' ),
			$subject_type->value,
			$subject_id,
			AutomationRunStatus::Pending->value,
			AutomationRunStatus::Running->value
		);
		return $this->hydrate_rows( $this->db->get_results( $sql, ARRAY_A ) );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int $customer_id Customer id.
	 *
	 * @return list<AutomationRun>
	 */
	public function find_open_for_customer( int $customer_id ): array {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `customer_id` = %d AND `status` IN (%s, %s)',
			$this->table( 'automation_runs' ),
			$customer_id,
			AutomationRunStatus::Pending->value,
			AutomationRunStatus::Running->value
		);
		return $this->hydrate_rows( $this->db->get_results( $sql, ARRAY_A ) );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int         $automation_id Automation id.
	 * @param SubjectType $subject_type  Subject type.
	 * @param int         $subject_id    Subject id.
	 */
	public function find_latest_counted( int $automation_id, SubjectType $subject_type, int $subject_id ): ?AutomationRun {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `automation_id` = %d AND `subject_type` = %s AND `subject_id` = %d AND `status` <> %s ORDER BY `created_at` DESC LIMIT 50',
			$this->table( 'automation_runs' ),
			$automation_id,
			$subject_type->value,
			$subject_id,
			AutomationRunStatus::Skipped->value
		);
		$rows = $this->hydrate_rows( $this->db->get_results( $sql, ARRAY_A ) );
		foreach ( $rows as $run ) {
			if ( ! $run->context()->is_test() ) {
				return $run;
			}
		}
		return null;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param int               $automation_id Automation id.
	 * @param DateTimeImmutable $since         Since.
	 */
	public function count_since( int $automation_id, DateTimeImmutable $since ): int {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `automation_id` = %d AND `created_at` >= %s AND `status` <> %s',
			$this->table( 'automation_runs' ),
			$automation_id,
			$this->format_datetime( $since ),
			AutomationRunStatus::Skipped->value
		);
		$count = 0;
		foreach ( $this->hydrate_rows( $this->db->get_results( $sql, ARRAY_A ) ) as $run ) {
			if ( ! $run->context()->is_test() ) {
				++$count;
			}
		}
		return $count;
	}

	/**
	 * {@inheritDoc}
	 *
	 * @param array<string, mixed> $filters  Filters.
	 * @param int                  $page     Page.
	 * @param int                  $per_page Per page.
	 *
	 * @return array{items: list<AutomationRun>, total: int}
	 */
	public function search( array $filters, int $page, int $per_page ): array {
		$search = isset( $filters['search'] ) && is_string( $filters['search'] ) ? trim( $filters['search'] ) : '';
		if ( '' !== $search ) {
			return $this->search_with_joins( $filters, $search, $page, $per_page );
		}

		$where = [ '1=1' ];
		$args  = [ $this->table( 'automation_runs' ) ];
		$this->append_run_filters( $where, $args, $filters, '' );

		$where_sql = implode( ' AND ', $where );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- allow-listed fragments.
		$count_sql = $this->db->prepare(
			'SELECT COUNT(*) FROM %i WHERE ' . $where_sql, // @phpstan-ignore argument.type
			...$args
		);
		$total = (int) $this->db->get_var( $count_sql );

		$offset    = max( 0, ( $page - 1 ) * $per_page );
		$list_args = array_merge( $args, [ $per_page, $offset ] );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- allow-listed fragments.
		$list_sql = $this->db->prepare(
			'SELECT * FROM %i WHERE ' . $where_sql . ' ORDER BY `id` DESC LIMIT %d OFFSET %d', // @phpstan-ignore argument.type
			...$list_args
		);

		return [
			'items' => $this->hydrate_rows( $this->db->get_results( $list_sql, ARRAY_A ) ),
			'total' => $total,
		];
	}

	/**
	 * Search joining customers and automations (docs/20 §9.5).
	 *
	 * @param array<string, mixed> $filters  Filters.
	 * @param string               $search   Term.
	 * @param int                  $page     Page.
	 * @param int                  $per_page Per page.
	 * @return array{items: list<AutomationRun>, total: int}
	 */
	private function search_with_joins( array $filters, string $search, int $page, int $per_page ): array {
		$like  = '%' . $this->db->esc_like( $search ) . '%';
		$where = [ '1=1' ];
		$args  = [
			$this->table( 'automation_runs' ),
			$this->table( 'customers' ),
			$this->table( 'automations' ),
		];
		$this->append_run_filters( $where, $args, $filters, 'r.' );

		$where[] = '(c.`email` LIKE %s OR c.`first_name` LIKE %s OR c.`last_name` LIKE %s OR r.`trigger_event_id` LIKE %s OR a.`name` LIKE %s OR CAST(r.`subject_id` AS CHAR) LIKE %s)';
		$args[]  = $like;
		$args[]  = $like;
		$args[]  = $like;
		$args[]  = $like;
		$args[]  = $like;
		$args[]  = $like;

		$where_sql = implode( ' AND ', $where );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- allow-listed fragments.
		$count_sql = $this->db->prepare(
			'SELECT COUNT(*) FROM %i r LEFT JOIN %i c ON c.`id` = r.`customer_id` LEFT JOIN %i a ON a.`id` = r.`automation_id` WHERE ' . $where_sql, // @phpstan-ignore argument.type
			...$args
		);
		$total     = (int) $this->db->get_var( $count_sql );
		$offset    = max( 0, ( $page - 1 ) * $per_page );
		$list_args = array_merge( $args, [ $per_page, $offset ] );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- allow-listed fragments.
		$list_sql = $this->db->prepare(
			'SELECT r.* FROM %i r LEFT JOIN %i c ON c.`id` = r.`customer_id` LEFT JOIN %i a ON a.`id` = r.`automation_id` WHERE ' . $where_sql . ' ORDER BY r.`id` DESC LIMIT %d OFFSET %d', // @phpstan-ignore argument.type
			...$list_args
		);

		return [
			'items' => $this->hydrate_rows( $this->db->get_results( $list_sql, ARRAY_A ) ),
			'total' => $total,
		];
	}

	/**
	 * Automation, customer, and status fragments.
	 *
	 * @param list<string>         $where   WHERE parts (mutated).
	 * @param list<mixed>          $args    Prepare args (mutated).
	 * @param array<string, mixed> $filters Filters.
	 * @param string               $prefix  Column prefix (`r.` or empty).
	 */
	private function append_run_filters( array &$where, array &$args, array $filters, string $prefix ): void {
		$aid = isset( $filters['automation_id'] ) ? (int) $filters['automation_id'] : 0;
		if ( $aid > 0 ) {
			$where[] = $prefix . '`automation_id` = %d';
			$args[]  = $aid;
		}
		$cid = isset( $filters['customer_id'] ) ? (int) $filters['customer_id'] : 0;
		if ( $cid > 0 ) {
			$where[] = $prefix . '`customer_id` = %d';
			$args[]  = $cid;
		}
		$statuses = $this->status_values( $filters );
		if ( [] !== $statuses ) {
			$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
			$where[]      = $prefix . '`status` IN (' . $placeholders . ')';
			foreach ( $statuses as $status ) {
				$args[] = $status;
			}
		}
	}

	/**
	 * Allowed status filters.
	 *
	 * @param array<string, mixed> $filters Filters.
	 * @return list<string>
	 */
	private function status_values( array $filters ): array {
		$raw = [];
		if ( isset( $filters['statuses'] ) && is_array( $filters['statuses'] ) ) {
			$raw = $filters['statuses'];
		} elseif ( isset( $filters['status'] ) && is_string( $filters['status'] ) && '' !== $filters['status'] ) {
			if ( 'queue' === $filters['status'] ) {
				return [
					AutomationRunStatus::Pending->value,
					AutomationRunStatus::Running->value,
				];
			}
			$raw = explode( ',', $filters['status'] );
		}
		$out = [];
		foreach ( $raw as $piece ) {
			if ( ! is_string( $piece ) ) {
				continue;
			}
			$key = trim( $piece );
			if ( '' === $key ) {
				continue;
			}
			$enum = AutomationRunStatus::tryFrom( $key );
			if ( null !== $enum ) {
				$out[] = $enum->value;
			}
		}
		return array_values( array_unique( $out ) );
	}

	/**
	 * Hydrate many rows.
	 *
	 * @param mixed $rows Rows.
	 * @return list<AutomationRun>
	 */
	private function hydrate_rows( mixed $rows ): array {
		$out = [];
		if ( ! is_array( $rows ) ) {
			return $out;
		}
		foreach ( $rows as $row ) {
			if ( is_array( $row ) ) {
				$out[] = $this->hydrate( $row );
			}
		}
		return $out;
	}

	/**
	 * Hydrate one row.
	 *
	 * @param array<string, mixed> $row Row.
	 */
	private function hydrate( array $row ): AutomationRun {
		$context_raw = $this->decode_meta( isset( $row['context'] ) ? (string) $row['context'] : null );
		$steps_raw   = json_decode( isset( $row['steps'] ) ? (string) $row['steps'] : '[]', true );
		$steps       = is_array( $steps_raw ) ? $steps_raw : [];
		$skip        = isset( $row['skip_reason'] ) && is_string( $row['skip_reason'] ) && '' !== $row['skip_reason']
			? SkipReason::from( $row['skip_reason'] )
			: null;

		return new AutomationRun(
			(int) $row['id'],
			(int) $row['automation_id'],
			(string) $row['trigger_event_id'],
			SubjectType::from( (string) $row['subject_type'] ),
			(int) $row['subject_id'],
			isset( $row['customer_id'] ) && null !== $row['customer_id'] && '' !== (string) $row['customer_id'] ? (int) $row['customer_id'] : null,
			AutomationRunStatus::from( (string) $row['status'] ),
			$skip,
			(int) ( $row['current_step'] ?? 0 ),
			isset( $row['scheduled_for'] ) && is_string( $row['scheduled_for'] ) && '' !== $row['scheduled_for'] ? $this->parse_datetime( $row['scheduled_for'] ) : null,
			isset( $row['started_at'] ) && is_string( $row['started_at'] ) && '' !== $row['started_at'] ? $this->parse_datetime( $row['started_at'] ) : null,
			isset( $row['completed_at'] ) && is_string( $row['completed_at'] ) && '' !== $row['completed_at'] ? $this->parse_datetime( $row['completed_at'] ) : null,
			AutomationContext::from_array( $context_raw ?? [] ),
			isset( $row['error'] ) && null !== $row['error'] ? (string) $row['error'] : null,
			isset( $row['action_scheduler_id'] ) && null !== $row['action_scheduler_id'] && '' !== (string) $row['action_scheduler_id'] ? (int) $row['action_scheduler_id'] : null,
			$steps,
			$this->parse_datetime( (string) $row['created_at'] ),
			$this->parse_datetime( (string) $row['updated_at'] )
		);
	}

	/**
	 * Encode JSON.
	 *
	 * @param array<string, mixed>|list<mixed> $data Data.
	 */
	private function encode_json( array $data ): string {
		$json = wp_json_encode( $data );
		return is_string( $json ) ? $json : '{}';
	}
}