<?php
/**
 * Data access for the `embp_jobs` table.
 *
 * @package EmberPrice
 */

namespace EmberPrice\Core;

defined( 'ABSPATH' ) || exit;

/**
 * Lives in Core (not Ingestion) for the same reason as {@see Suppliers}:
 * jobs are read/written across the whole pipeline.
 */
final class Jobs {

	/**
	 * Creates a job, returning its id.
	 *
	 * @param array $data Must include supplier_id, file_name, file_hash,
	 *                    status and created_by.
	 */
	public function create( array $data ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_jobs';

		$wpdb->insert(
			$table,
			array(
				'supplier_id' => (int) $data['supplier_id'],
				'file_name'   => (string) $data['file_name'],
				'file_hash'   => (string) $data['file_hash'],
				'status'      => (string) $data['status'],
				'totals'      => wp_json_encode( array() ),
				'created_by'  => (int) $data['created_by'],
				'created_at'  => current_time( 'mysql' ),
			),
			array( '%d', '%s', '%s', '%s', '%s', '%d', '%s' )
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Finds a job by id, or null if it doesn't exist.
	 *
	 * @param int $id Job id.
	 */
	public function find( int $id ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_jobs';

		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ),
			ARRAY_A
		);

		if ( null === $row ) {
			return null;
		}

		$row['totals'] = json_decode( (string) $row['totals'], true ) ?? array();

		return $row;
	}

	/**
	 * Every job, most recent first -- the "Historial" screen's listing
	 * (GET /jobs).
	 *
	 * @return array[]
	 */
	public function list_all(): array {
		return $this->list_paginated( 1, 1000 )['jobs'];
	}

	/**
	 * Lists jobs most-recent-first without loading the entire audit history.
	 *
	 * @param int $page     1-based page.
	 * @param int $per_page Page size.
	 *
	 * @return array{jobs: array[], total: int}
	 */
	public function list_paginated( int $page, int $per_page ): array {
		global $wpdb;
		$table  = $wpdb->prefix . 'embp_jobs';
		$page   = max( 1, $page );
		$limit  = min( 1000, max( 1, $per_page ) );
		$offset = ( $page - 1 ) * $limit;

		$rows  = $wpdb->get_results(
			$wpdb->prepare( 'SELECT * FROM %i ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d', $table, $limit, $offset ),
			ARRAY_A
		);
		$total = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );

		return array(
			'jobs'  => array_map(
				static function ( array $row ): array {
					$row['totals'] = json_decode( (string) $row['totals'], true ) ?? array();

					return $row;
				},
				null !== $rows ? $rows : array()
			),
			'total' => $total,
		);
	}

	/**
	 * Updates a job's status.
	 *
	 * @param int    $id     Job id.
	 * @param string $status New status.
	 */
	public function update_status( int $id, string $status ): void {
		global $wpdb;
		$table   = $wpdb->prefix . 'embp_jobs';
		$data    = array( 'status' => $status );
		$formats = array( '%s' );

		if ( 'applied' === $status ) {
			$data['applied_at'] = current_time( 'mysql' );
			$formats[]          = '%s';
		}

		$wpdb->update( $table, $data, array( 'id' => $id ), $formats, array( '%d' ) );
	}

	/**
	 * Updates a job's `totals` (e.g. row counts by status, once matching finishes).
	 *
	 * @param int   $id     Job id.
	 * @param array $totals Value to store, JSON-encoded.
	 */
	public function update_totals( int $id, array $totals ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_jobs';

		$wpdb->update( $table, array( 'totals' => wp_json_encode( $totals ) ), array( 'id' => $id ), array( '%s' ), array( '%d' ) );
	}

	/**
	 * Merges top-level values into a job's totals JSON.
	 *
	 * @param int   $id     Job id.
	 * @param array $values Values to merge.
	 */
	public function merge_totals( int $id, array $values ): void {
		$job = $this->find( $id );

		if ( null === $job ) {
			return;
		}

		$this->update_totals( $id, array_merge( (array) $job['totals'], $values ) );
	}

	/**
	 * Moves a job to the terminal failed state with a safe diagnostic.
	 *
	 * @param int    $id      Job id.
	 * @param string $stage   Pipeline stage that failed.
	 * @param string $message Human-readable message, never a stack trace.
	 */
	public function mark_failed( int $id, string $stage, string $message ): void {
		$this->merge_totals(
			$id,
			array(
				'error' => array(
					'stage'      => $stage,
					'message'    => $message,
					'updated_at' => current_time( 'mysql', true ),
				),
			)
		);
		$this->update_status( $id, 'failed' );
	}

	/**
	 * Terminal jobs older than a retention window.
	 *
	 * @param int $days  Age in days.
	 * @param int $limit Maximum jobs returned.
	 *
	 * @return array[]
	 */
	public function terminal_older_than( int $days, int $limit = 100 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_jobs';

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE status IN ('applied','rolled_back','failed') AND created_at < ( NOW() - INTERVAL %d DAY ) ORDER BY created_at ASC LIMIT %d",
				$table,
				max( 1, $days ),
				min( 1000, max( 1, $limit ) )
			),
			ARRAY_A
		);

		return array_map(
			static function ( array $row ): array {
				$row['totals'] = json_decode( (string) $row['totals'], true ) ?? array();

				return $row;
			},
			null !== $rows ? $rows : array()
		);
	}

	/**
	 * Deletes one job record. Related plugin tables are deleted explicitly
	 * by the retention service because the schema intentionally has no
	 * foreign keys.
	 *
	 * @param int $id Job id.
	 */
	public function delete( int $id ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_jobs';

		$wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
	}
}
