<?php
/**
 * Storage for report runs.
 *
 * @package TurtleReport
 */

namespace TurtleReport\Delivery;

defined( 'ABSPATH' ) || exit;

/**
 * A run is one report for one site and period: scheduled, test or manual.
 *
 * Statuses:
 * - preparing:       being built (or a job stopped while building it);
 * - awaiting_review: built, waiting for an administrator to approve it;
 * - held:            not sent because it was incomplete (see Readiness);
 * - sending:         emails going out;
 * - sent:            every recipient's email was accepted by the mail system;
 * - partial:         some accepted, some failed for good;
 * - retrying:        some emails failed and will be tried again;
 * - failed:          no email was accepted;
 * - cancelled:       an administrator skipped it, or the site was deleted.
 *
 * Scheduled runs use the key "{site}|{slot}", which is unique in the table,
 * so two cron processes can never create the same report twice.
 */
final class RunRepository {

	const PREPARING = 'preparing';
	const REVIEW    = 'awaiting_review';
	const HELD      = 'held';
	const SENDING   = 'sending';
	const SENT      = 'sent';
	const PARTIAL   = 'partial';
	const RETRYING  = 'retrying';
	const FAILED    = 'failed';
	const CANCELLED = 'cancelled';

	const KIND_SCHEDULED = 'scheduled';
	const KIND_TEST      = 'test';
	const KIND_MANUAL    = 'manual';

	/** Columns holding JSON. */
	const JSON = array( 'checks', 'insights', 'snapshot', 'meta' );

	/**
	 * Creates a run, or returns the one that already has this key.
	 *
	 * @param array $fields Columns (run_key, site_id, kind, slot, period_*, compare_*).
	 * @return array|null The run.
	 */
	public function open( array $fields ) {
		global $wpdb;
		$now      = time();
		$defaults = array(
			'run_key'       => '',
			'site_id'       => '',
			'kind'          => self::KIND_SCHEDULED,
			'slot'          => 0,
			'period_start'  => '',
			'period_end'    => '',
			'compare_start' => '',
			'compare_end'   => '',
			'status'        => self::PREPARING,
		);
		if ( array_diff_key( $fields, $defaults ) ) {
			return null;
		}
		$row = array_merge( $defaults, $fields );
		foreach ( $row as $column => $value ) {
			if ( 'slot' === $column ) {
				if ( ! is_int( $value ) || $value < 0 ) {
					return null;
				}
			} elseif ( ! is_string( $value ) ) {
				return null;
			}
		}
		if ( '' === $row['run_key'] || '' === $row['site_id'] || ! in_array( $row['kind'], array( self::KIND_SCHEDULED, self::KIND_TEST, self::KIND_MANUAL ), true ) ) {
			return null;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Atomic unique run creation; columns and placeholders are fixed.
		$created = $wpdb->query( $wpdb->prepare( 'INSERT IGNORE INTO %i (run_key, site_id, kind, slot, period_start, period_end, compare_start, compare_end, status, created, updated) VALUES (%s, %s, %s, %d, %s, %s, %s, %s, %s, %d, %d)', Schema::runs(), $row['run_key'], $row['site_id'], $row['kind'], $row['slot'], $row['period_start'], $row['period_end'], $row['compare_start'], $row['compare_end'], $row['status'], $now, $now ) );
		if ( false === $created ) {
			return null;
		}
		return $this->find_by_key( (string) $row['run_key'] );
	}

	/**
	 * A run by ID.
	 *
	 * @param int $id Run ID.
	 * @return array|null
	 */
	public function find( $id ) {
		global $wpdb;
		$table = Schema::runs();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table.
		return self::decode( $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ), ARRAY_A ) );
	}

	/**
	 * A run by key.
	 *
	 * @param string $key Run key.
	 * @return array|null
	 */
	public function find_by_key( $key ) {
		global $wpdb;
		$table = Schema::runs();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table.
		return self::decode( $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE run_key = %s", (string) $key ), ARRAY_A ) );
	}

	/**
	 * Updates columns of a run.
	 *
	 * @param int   $id     Run ID.
	 * @param array $fields Columns; JSON columns may be arrays.
	 * @return void
	 * @throws \InvalidArgumentException When fields are unknown or have invalid types.
	 * @throws \RuntimeException When storage fails.
	 */
	public function update( $id, array $fields ) {
		global $wpdb;
		$allowed = array( 'status', 'readiness', 'checks', 'insights', 'notes', 'snapshot', 'meta', 'attempts', 'next_check', 'reviewed_by', 'reviewed_at', 'sent_at' );
		if ( array_diff_key( $fields, array_flip( $allowed ) ) ) {
			throw new \InvalidArgumentException( __( 'Unknown report fields cannot be saved.', 'turtlereport' ) ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Plain domain exception, not HTML output; preserve message bytes and escape at the response boundary.
		}
		foreach ( $fields as $column => $value ) {
			if ( ! in_array( $column, self::JSON, true ) && ! is_scalar( $value ) && null !== $value ) {
				throw new \InvalidArgumentException( __( 'Invalid report fields cannot be saved.', 'turtlereport' ) ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Plain domain exception, not HTML output; preserve message bytes and escape at the response boundary.
			}
		}
		$fields['updated'] = time();
		foreach ( self::JSON as $col ) {
			if ( array_key_exists( $col, $fields ) && ! is_string( $fields[ $col ] ) && null !== $fields[ $col ] ) {
				$fields[ $col ] = (string) wp_json_encode( $fields[ $col ] );
			}
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		if ( false === $wpdb->update( Schema::runs(), $fields, array( 'id' => (int) $id ) ) ) {
			throw new \RuntimeException( __( 'The report status could not be saved. Try again.', 'turtlereport' ) ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Plain domain exception, not HTML output; preserve message bytes and escape at the response boundary.
		}
	}

	/**
	 * Moves a run to a new status only if it is still in one of the expected
	 * ones. Atomic: when two processes race, one of them wins.
	 *
	 * @param int      $id   Run ID.
	 * @param string[] $from Allowed current statuses.
	 * @param string   $to   New status.
	 * @return bool Whether this call made the change.
	 */
	public function transition( $id, array $from, $to ) {
		global $wpdb;
		$statuses = array( self::PREPARING, self::REVIEW, self::HELD, self::SENDING, self::SENT, self::PARTIAL, self::RETRYING, self::FAILED, self::CANCELLED );
		if ( ! $from || array_diff( $from, $statuses ) || ! in_array( $to, $statuses, true ) ) {
			return false;
		}
		$table = Schema::runs();
		$in    = implode( ',', array_fill( 0, count( $from ), '%s' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Each allowlisted status has its own generated placeholder; real MySQL transition/injection regressions cover the values.
		$n = $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET status = %s, updated = %d WHERE id = %d AND status IN ({$in})", array_merge( array( $to, time(), (int) $id ), array_values( $from ) ) ) );
		return 1 === (int) $n;
	}

	/**
	 * Runs, newest first.
	 *
	 * @param array $filter site_id, status (string or list), kind; limit; offset.
	 * @return array[]
	 */
	public function query( array $filter = array() ) {
		global $wpdb;
		list( $where, $args ) = self::where( $filter );
		$limit                = isset( $filter['limit'] ) ? max( 1, min( 500, (int) $filter['limit'] ) ) : 50;
		$offset               = isset( $filter['offset'] ) ? max( 0, (int) $filter['offset'] ) : 0;
		$table                = Schema::runs();
		$cols                 = 'id, run_key, site_id, kind, slot, period_start, period_end, compare_start, compare_end, status, readiness, checks, meta, attempts, next_check, reviewed_by, reviewed_at, sent_at, created, updated';
		$sql                  = "SELECT {$cols} FROM {$table} {$where} ORDER BY created DESC, id DESC LIMIT %d OFFSET %d";
		$args[]               = $limit;
		$args[]               = $offset;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom table; prepared here.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
		return array_map( array( __CLASS__, 'decode' ), is_array( $rows ) ? $rows : array() );
	}

	/**
	 * Number of runs matching a filter.
	 *
	 * @param array $filter As query().
	 * @return int
	 */
	public function count( array $filter = array() ) {
		global $wpdb;
		list( $where, $args ) = self::where( $filter );
		$table                = Schema::runs();
		$sql                  = "SELECT COUNT(*) FROM {$table} {$where}";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom table.
		return (int) $wpdb->get_var( $args ? $wpdb->prepare( $sql, $args ) : $sql );
	}

	/**
	 * Runs that need work from the background job: held runs due for another
	 * check, and runs a job stopped in the middle of.
	 *
	 * @param int $now Unix time.
	 * @return int[]
	 */
	public function needing_work( $now ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE ( status = %s AND next_check > 0 AND next_check <= %d ) OR ( status IN (%s, %s) AND updated < %d ) ORDER BY id ASC LIMIT 20',
				Schema::runs(),
				self::HELD,
				(int) $now,
				self::PREPARING,
				self::SENDING,
				(int) $now - 15 * MINUTE_IN_SECONDS
			),
			ARRAY_A
		);
		return is_array( $rows ) ? array_map( 'intval', array_column( $rows, 'id' ) ) : array();
	}

	/**
	 * Clears the stored numbers of old runs and deletes very old runs.
	 *
	 * @param int $snapshot_days Keep snapshots this many days.
	 * @param int $history_days  Keep runs (and their deliveries) this many days.
	 * @return void
	 */
	public function purge( $snapshot_days, $history_days ) {
		global $wpdb;
		$runs  = Schema::runs();
		$sends = Schema::deliveries();
		$now   = time();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom tables.
		$wpdb->query( $wpdb->prepare( "UPDATE {$runs} SET snapshot = NULL WHERE snapshot IS NOT NULL AND created < %d AND status NOT IN (%s, %s)", $now - (int) $snapshot_days * DAY_IN_SECONDS, self::REVIEW, self::HELD ) );
		$old = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$runs} WHERE created < %d LIMIT 500", $now - (int) $history_days * DAY_IN_SECONDS ) );
		if ( $old ) {
			$ids = implode( ',', array_map( 'intval', $old ) );
			$wpdb->query( "DELETE FROM {$sends} WHERE run_id IN ({$ids})" );
			$wpdb->query( "DELETE FROM {$runs} WHERE id IN ({$ids})" );
		}
		// phpcs:enable
	}

	/**
	 * Deletes reports from the history, with their per-recipient rows.
	 *
	 * A report that is being prepared or sent right now is left alone, so a
	 * send in progress is never cut off halfway. Deleting a report doesn't
	 * touch the schedule: the next send is already booked, and a deleted
	 * scheduled report is not sent again.
	 *
	 * @param int[] $ids Run IDs.
	 * @return array{deleted:int,busy:int}
	 */
	public function delete( array $ids ) {
		global $wpdb;
		$ids = array_values( array_unique( array_filter( array_map( 'absint', $ids ) ) ) );
		$out = array(
			'deleted' => 0,
			'busy'    => 0,
		);
		if ( ! $ids ) {
			return $out;
		}
		$runs  = Schema::runs();
		$sends = Schema::deliveries();
		$list  = implode( ',', $ids );
		// Busy: sending now, or preparing and touched in the last 15 minutes (an older "preparing" run is stuck).
		// The condition is part of the DELETE, so a run that starts sending meanwhile is never removed.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom tables; IDs are integers.
		$found          = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$runs} WHERE id IN ({$list})" );
		$out['deleted'] = (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$runs} WHERE id IN ({$list}) AND status <> %s AND NOT ( status = %s AND updated > %d )",
				self::SENDING,
				self::PREPARING,
				time() - 15 * MINUTE_IN_SECONDS
			)
		);
		if ( $out['deleted'] ) {
			$left = array_map( 'intval', (array) $wpdb->get_col( "SELECT id FROM {$runs} WHERE id IN ({$list})" ) );
			$gone = array_diff( $ids, $left );
			if ( $gone ) {
				$gone_list = implode( ',', array_map( 'intval', $gone ) );
				$wpdb->query( "DELETE FROM {$sends} WHERE run_id IN ({$gone_list})" );
			}
		}
		// phpcs:enable
		$out['busy'] = max( 0, $found - $out['deleted'] );
		return $out;
	}

	/**
	 * Deletes every run of a site (the site was deleted).
	 *
	 * @param string $site_id Site ID.
	 * @return void
	 */
	public function forget_site( $site_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables.
		$wpdb->delete( Schema::deliveries(), array( 'site_id' => (string) $site_id ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom tables.
		$wpdb->delete( Schema::runs(), array( 'site_id' => (string) $site_id ) );
	}

	/**
	 * WHERE clause for a filter.
	 *
	 * @param array $filter Filter.
	 * @return array{0:string,1:array}
	 */
	private static function where( array $filter ) {
		$parts = array();
		$args  = array();
		if ( ! empty( $filter['site_id'] ) ) {
			$parts[] = 'site_id = %s';
			$args[]  = (string) $filter['site_id'];
		}
		if ( ! empty( $filter['kind'] ) ) {
			$parts[] = 'kind = %s';
			$args[]  = (string) $filter['kind'];
		}
		if ( ! empty( $filter['status'] ) ) {
			$list    = array_values( (array) $filter['status'] );
			$parts[] = 'status IN (' . implode( ',', array_fill( 0, count( $list ), '%s' ) ) . ')';
			$args    = array_merge( $args, array_map( 'strval', $list ) );
		}
		return array( $parts ? 'WHERE ' . implode( ' AND ', $parts ) : '', $args );
	}

	/**
	 * Decodes a row.
	 *
	 * @param array|null $row Row.
	 * @return array|null
	 */
	public static function decode( $row ) {
		if ( ! is_array( $row ) ) {
			return null;
		}
		foreach ( array( 'id', 'slot', 'attempts', 'next_check', 'reviewed_by', 'reviewed_at', 'sent_at', 'created', 'updated' ) as $col ) {
			if ( isset( $row[ $col ] ) ) {
				$row[ $col ] = (int) $row[ $col ];
			}
		}
		foreach ( self::JSON as $col ) {
			if ( array_key_exists( $col, $row ) ) {
				$value       = is_string( $row[ $col ] ) && '' !== $row[ $col ] ? json_decode( $row[ $col ], true ) : null;
				$row[ $col ] = is_array( $value ) ? $value : ( 'snapshot' === $col ? null : array() );
			}
		}
		return $row;
	}

	/**
	 * The period of a run.
	 *
	 * @param array $run Run.
	 * @return array{s:string,e:string,ps:string,pe:string}
	 */
	public static function period( array $run ) {
		return array(
			's'  => (string) $run['period_start'],
			'e'  => (string) $run['period_end'],
			'ps' => (string) $run['compare_start'],
			'pe' => (string) $run['compare_end'],
		);
	}

	/**
	 * Human label for a status.
	 *
	 * @param string $status Status.
	 * @return string
	 */
	public static function label( $status ) {
		$labels = array(
			self::PREPARING => __( 'Preparing', 'turtlereport' ),
			self::REVIEW    => __( 'Waiting for review', 'turtlereport' ),
			self::HELD      => __( 'Held: data incomplete', 'turtlereport' ),
			self::SENDING   => __( 'Sending', 'turtlereport' ),
			self::SENT      => __( 'Accepted by mail system', 'turtlereport' ),
			self::PARTIAL   => __( 'Partly accepted', 'turtlereport' ),
			self::RETRYING  => __( 'Retrying', 'turtlereport' ),
			self::FAILED    => __( 'Failed', 'turtlereport' ),
			self::CANCELLED => __( 'Skipped', 'turtlereport' ),
		);
		return isset( $labels[ $status ] ) ? $labels[ $status ] : (string) $status;
	}

	/**
	 * Badge style for a status: good, bad, warn or flat.
	 *
	 * @param string $status Status.
	 * @return string
	 */
	public static function tone( $status ) {
		switch ( $status ) {
			case self::SENT:
				return 'good';
			case self::FAILED:
				return 'bad';
			case self::PARTIAL:
			case self::RETRYING:
			case self::HELD:
			case self::REVIEW:
				return 'warn';
			default:
				return 'flat';
		}
	}
}