<?php
/**
 * Audit service — an own log table (portable) mirrored to Simple History when present.
 *
 * @package OST\EditorialWorkflow
 */

namespace OST\EditorialWorkflow;

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

/**
 * Writes and reads workflow transition audit entries.
 */
class Audit {

	/**
	 * Fully-qualified table name.
	 *
	 * @return string
	 */
	public function table() {
		global $wpdb;
		return $wpdb->prefix . 'ostew_log';
	}

	/**
	 * Create the audit table (idempotent, via dbDelta).
	 *
	 * @return void
	 */
	public function create_table() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table           = $this->table();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NOT NULL,
			from_status VARCHAR(32) NOT NULL DEFAULT '',
			to_status VARCHAR(32) NOT NULL DEFAULT '',
			action VARCHAR(20) NOT NULL DEFAULT '',
			user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			note TEXT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY post_id (post_id),
			KEY created_at (created_at)
		) {$charset_collate};";

		dbDelta( $sql );
	}

	/**
	 * Record a transition.
	 *
	 * @param int    $post_id     Post ID.
	 * @param string $from_status Previous status.
	 * @param string $to_status   New status.
	 * @param int    $user_id     Acting user (0 = system).
	 * @param string $note        Optional editorial note.
	 * @param string $action      Workflow action behind the change (submit|approve|reject),
	 *                            or '' when the status was changed by other means.
	 * @return int|false Inserted row id, or false on failure.
	 */
	public function write( $post_id, $from_status, $to_status, $user_id = 0, $note = '', $action = '' ) {
		global $wpdb;

		$ok = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->table(),
			array(
				'post_id'     => (int) $post_id,
				'from_status' => (string) $from_status,
				'to_status'   => (string) $to_status,
				'action'      => (string) $action,
				'user_id'     => (int) $user_id,
				'note'        => ( '' === $note ) ? null : (string) $note,
				'created_at'  => current_time( 'mysql' ),
			),
			array( '%d', '%s', '%s', '%s', '%d', '%s', '%s' )
		);

		$this->mirror_to_simple_history( $post_id, $from_status, $to_status, $user_id, $note );

		return $ok ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Fetch audit entries for a post (newest first).
	 *
	 * @param int $post_id Post ID.
	 * @param int $limit   Max rows.
	 * @return array
	 */
	public function for_post( $post_id, $limit = 50 ) {
		global $wpdb;
		$table = $this->table();
		// Table name comes from $wpdb->prefix (not user input); values are placeholders.
		$sql = "SELECT * FROM {$table} WHERE post_id = %d ORDER BY id DESC LIMIT %d"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_results( $wpdb->prepare( $sql, (int) $post_id, (int) $limit ) );
	}

	/**
	 * The most recent audit entries across all posts, newest first.
	 *
	 * @param int $limit Max rows.
	 * @return array
	 */
	public function recent( $limit = 200 ) {
		global $wpdb;
		$table = $this->table();
		// Table name comes from $wpdb->prefix, not from user input.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} ORDER BY id DESC LIMIT %d", (int) $limit ) );
	}

	/**
	 * Every audit entry, oldest first. Used by the CSV export.
	 *
	 * @return array
	 */
	public function all_rows() {
		global $wpdb;
		$table = $this->table();
		// Table name comes from $wpdb->prefix, not from user input.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->get_results( "SELECT * FROM {$table} ORDER BY id ASC", ARRAY_A );
	}

	/**
	 * The audit trail as CSV text.
	 *
	 * @return string
	 */
	public function to_csv() {
		$columns = array( 'id', 'post_id', 'from_status', 'to_status', 'action', 'user_id', 'note', 'created_at' );
		$out     = self::csv_line( $columns );

		foreach ( (array) $this->all_rows() as $row ) {
			$line = array();
			foreach ( $columns as $column ) {
				$line[] = isset( $row[ $column ] ) ? $row[ $column ] : '';
			}
			$out .= self::csv_line( $line );
		}

		return $out;
	}

	/**
	 * One CSV record, quoted per RFC 4180 and terminated with CRLF.
	 *
	 * Built by hand rather than with fputcsv, which would mean opening a stream
	 * for something this small.
	 *
	 * @param array $values Field values.
	 * @return string
	 */
	private static function csv_line( array $values ) {
		$fields = array();
		foreach ( $values as $value ) {
			$fields[] = '"' . str_replace( '"', '""', (string) $value ) . '"';
		}
		return implode( ',', $fields ) . "\r\n";
	}

	/**
	 * Mirror an entry into Simple History if that plugin is active.
	 *
	 * @param int    $post_id     Post ID.
	 * @param string $from_status Previous status.
	 * @param string $to_status   New status.
	 * @param int    $user_id     Acting user.
	 * @param string $note        Optional note.
	 * @return void
	 */
	private function mirror_to_simple_history( $post_id, $from_status, $to_status, $user_id, $note ) {
		if ( ! function_exists( 'SimpleLogger' ) ) {
			return;
		}
		SimpleLogger()->info(
			'Editorial workflow: "{title}" moved {from} → {to}',
			array(
				'title'    => get_the_title( $post_id ),
				'from'     => $from_status,
				'to'       => $to_status,
				'post_id'  => $post_id,
				'user_id'  => $user_id,
				'note'     => $note,
				'_context' => 'ostew_workflow',
			)
		);
	}
}