<?php
/**
 * Sync log: write and query the wcattio_sync_log table
 *
 * @package WooAttioSync
 */

declare( strict_types=1 );

defined( 'ABSPATH' ) || exit;

/**
 * Handles reading and writing sync-log rows.
 *
 * Table columns:
 *   id, object_type, object_id, attio_record_id,
 *   status, error_message, synced_at, created_at
 */
class WooAttioSync_Sync_Log {

	const STATUSES = array( 'success', 'error', 'pending' );

	private string $table;

	public function __construct() {
		global $wpdb;
		$this->table = esc_sql( $wpdb->prefix . 'wcattio_sync_log' );
	}

	/**
	 * Log (or update) a sync attempt for a given object.
	 *
	 * Uses INSERT ... ON DUPLICATE KEY UPDATE so repeated syncs of the same
	 * object_type/object_id pair update the existing row instead of duplicating.
	 * Requires a UNIQUE KEY on (object_type, object_id) in the table schema.
	 *
	 * @param string $object_type     'person', 'company', or 'order'
	 * @param string $object_id       WooCommerce entity ID
	 * @param string $status          'success', 'error', or 'pending'
	 * @param string $error_message   Human-readable error detail (empty on success)
	 * @param string $attio_record_id The Attio record UUID returned on success
	 */
	public static function log(
		string $object_type,
		string $object_id,
		string $status,
		string $error_message = '',
		string $attio_record_id = ''
	): void {
		global $wpdb;

		if ( ! in_array( $status, self::STATUSES, true ) ) {
			$status = 'pending';
		}

		$table     = esc_sql( $wpdb->prefix . 'wcattio_sync_log' );
		$synced_at = gmdate( 'Y-m-d H:i:s' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO `{$table}`
					(object_type, object_id, attio_record_id, status, error_message, synced_at)
				VALUES
					(%s, %s, %s, %s, %s, %s)
				ON DUPLICATE KEY UPDATE
					attio_record_id = VALUES(attio_record_id),
					status          = VALUES(status),
					error_message   = VALUES(error_message),
					synced_at       = VALUES(synced_at)",
				$object_type,
				$object_id,
				$attio_record_id,
				$status,
				$error_message,
				$synced_at
			)
		);
		// phpcs:enable
	}

	/**
	 * Find the logged object behind an Attio record ID.
	 *
	 * A record.deleted webhook carries only the Attio record ID and the record is
	 * already gone, so it cannot be fetched back — the sync log is the only
	 * mapping from an Attio record to the local object that produced it.
	 *
	 * @param string $attio_record_id Attio record UUID.
	 * @param string $object_type     Log object type to match, e.g. 'person'.
	 * @return object|null Row with object_type/object_id, or null when unknown.
	 */
	public static function find_by_attio_record_id( string $attio_record_id, string $object_type = 'person' ): ?object {
		global $wpdb;

		if ( '' === $attio_record_id ) {
			return null;
		}

		$table = esc_sql( $wpdb->prefix . 'wcattio_sync_log' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT object_type, object_id FROM `{$table}`
				WHERE attio_record_id = %s AND object_type = %s
				LIMIT 1",
				$attio_record_id,
				$object_type
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery

		return is_object( $row ) ? $row : null;
	}

	/**
	 * Retrieve log rows with optional filters.
	 *
	 * @param array{
	 *   status?:      string,
	 *   object_type?: string,
	 *   since?:       string,
	 *   per_page?:    int,
	 *   page?:        int,
	 * } $args
	 * @return list<object> Array of log row objects
	 */
	public function get_logs( array $args = array() ): array {
		global $wpdb;

		$t        = $this->table;
		$per_page = max( 1, (int) ( $args['per_page'] ?? 20 ) );
		$page     = max( 1, (int) ( $args['page'] ?? 1 ) );
		$offset   = ( $page - 1 ) * $per_page;

		[ $where_sql, $params ] = $this->build_where( $args );

		$params[] = $per_page;
		$params[] = $offset;

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.DirectDatabaseQuery
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM `{$t}`{$where_sql} ORDER BY synced_at DESC LIMIT %d OFFSET %d",
				...$params
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.DirectDatabaseQuery
		return is_array( $results ) ? $results : array();
	}

	/**
	 * Count log rows matching optional filters.
	 *
	 * @param array{status?: string, object_type?: string, since?: string} $filters
	 *   `since` accepts a datetime string (Y-m-d H:i:s) or a strtotime-parseable string
	 *   such as '-24 hours'; rows with synced_at before that point are excluded.
	 * @return int Total matching row count
	 */
	public function get_log_count( array $filters = array() ): int {
		global $wpdb;

		$t = $this->table;

		[ $where_sql, $params ] = $this->build_where( $filters );

		if ( $params ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery
			return (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM `{$t}`{$where_sql}", ...$params )
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$t}`" );
	}

	/**
	 * Build a WHERE clause string and corresponding prepared-value array from filter args.
	 *
	 * Supported keys: status, object_type, since (datetime string or strtotime expression).
	 *
	 * @param array<string,mixed> $filters
	 * @return array{0: string, 1: list<mixed>} [' WHERE …' or '', param array]
	 */
	private function build_where( array $filters ): array {
		$clauses = array();
		$params  = array();

		$status = ( ! empty( $filters['status'] ) && in_array( $filters['status'], self::STATUSES, true ) )
			? $filters['status'] : '';
		if ( '' !== $status ) {
			$clauses[] = 'status = %s';
			$params[]  = $status;
		}

		$object_type = ! empty( $filters['object_type'] ) ? sanitize_text_field( $filters['object_type'] ) : '';
		if ( '' !== $object_type ) {
			$clauses[] = 'object_type = %s';
			$params[]  = $object_type;
		}

		$since = ! empty( $filters['since'] ) ? (string) $filters['since'] : '';
		if ( '' !== $since ) {
			// Accept a pre-formatted datetime or any strtotime-parseable string
			$ts = strtotime( $since );
			if ( false !== $ts ) {
				$clauses[] = 'synced_at >= %s';
				$params[]  = gmdate( 'Y-m-d H:i:s', $ts );
			}
		}

		$where_sql = $clauses ? ' WHERE ' . implode( ' AND ', $clauses ) : '';
		return array( $where_sql, $params );
	}

	/**
	 * Delete log rows older than the given number of days.
	 *
	 * @param int $days Rows older than this many days are removed
	 * @return int Number of rows deleted
	 */
	public static function clear_old_logs( int $days = 30 ): int {
		global $wpdb;

		$days      = max( 1, min( 3650, $days ) );
		$t         = esc_sql( $wpdb->prefix . 'wcattio_sync_log' );
		$threshold = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
		$deleted = $wpdb->query(
			$wpdb->prepare( "DELETE FROM `{$t}` WHERE synced_at < %s", $threshold )
		);
		// phpcs:enable

		return (int) $deleted;
	}

	/**
	 * Render the sync log admin page (registered as a submenu callback)
	 */
	public static function render_log_page(): void {
		if ( ! current_user_can( 'manage_woocommerce' ) ) {
			wp_die( esc_html__( 'You do not have sufficient permissions to access this page.', 'harbor-sync-with-attio-for-woocommerce' ) );
		}

		// Handle "clear old logs" form action
		if (
			isset( $_POST['wcattio_clear_logs'], $_POST['_wpnonce'] ) &&
			wp_verify_nonce( sanitize_key( $_POST['_wpnonce'] ), 'wcattio_clear_logs' )
		) {
			$deleted = self::clear_old_logs( 30 );
			add_settings_error(
				'wcattio_log',
				'logs_cleared',
				/* translators: %d: number of rows deleted */
				sprintf( __( 'Cleared %d log entries older than 30 days.', 'harbor-sync-with-attio-for-woocommerce' ), $deleted ),
				'success'
			);
		}

		// Collect filter args from GET - read-only filters, no state mutation, no nonce required
		// phpcs:disable WordPress.Security.NonceVerification.Recommended
		$status_filter = sanitize_text_field( wp_unslash( $_GET['status_filter'] ?? '' ) );
		$type_filter   = sanitize_text_field( wp_unslash( $_GET['type_filter'] ?? '' ) );
		$current_page  = max( 1, absint( wp_unslash( $_GET['paged'] ?? '1' ) ) );
		// phpcs:enable WordPress.Security.NonceVerification.Recommended
		$per_page = 20;

		$instance = new self();
		$filters  = array_filter(
			array(
				'status'      => $status_filter,
				'object_type' => $type_filter,
			)
		);
		$logs     = $instance->get_logs(
			array_merge(
				$filters,
				array(
					'per_page' => $per_page,
					'page'     => $current_page,
				)
			)
		);
		$total    = $instance->get_log_count( $filters );
		$pages    = (int) ceil( $total / $per_page );

		// Pre-fetch edit URLs for all order rows to avoid an N+1 inside the loop
		$order_edit_urls = array();
		if ( $logs ) {
			$order_ids = array_map(
				static fn( $row ) => (int) $row->object_id,
				array_filter( $logs, static fn( $row ) => 'order' === $row->object_type )
			);
			if ( $order_ids ) {
				foreach ( wc_get_orders(
					array(
						'include' => $order_ids,
						'limit'   => -1,
						'return'  => 'objects',
					)
				) as $o ) {
					$order_edit_urls[ $o->get_id() ] = $o->get_edit_order_url();
				}
			}
		}

		$base_url = add_query_arg(
			array(
				'page'          => 'wcattio-sync-log',
				'status_filter' => $status_filter,
				'type_filter'   => $type_filter,
			),
			admin_url( 'admin.php' )
		);
		?>
		<div class="wrap">
			<h1><?php esc_html_e( 'Attio Sync Log', 'harbor-sync-with-attio-for-woocommerce' ); ?></h1>

			<?php WooAttioSync_Admin_Settings::render_nav_tabs( 'sync-log' ); ?>

			<?php settings_errors( 'wcattio_log' ); ?>

			<div class="wcattio-log-toolbar tablenav top">
				<form method="get" action="">
					<input type="hidden" name="page" value="wcattio-sync-log" />

					<label for="wcattio-status-filter" class="screen-reader-text">
						<?php esc_html_e( 'Filter by status', 'harbor-sync-with-attio-for-woocommerce' ); ?>
					</label>
					<select name="status_filter" id="wcattio-status-filter">
						<option value=""><?php esc_html_e( 'All statuses', 'harbor-sync-with-attio-for-woocommerce' ); ?></option>
						<?php foreach ( self::STATUSES as $s ) : ?>
							<option value="<?php echo esc_attr( $s ); ?>" <?php selected( $status_filter, $s ); ?>>
								<?php echo esc_html( ucfirst( $s ) ); ?>
							</option>
						<?php endforeach; ?>
					</select>

					<label for="wcattio-type-filter" class="screen-reader-text">
						<?php esc_html_e( 'Filter by type', 'harbor-sync-with-attio-for-woocommerce' ); ?>
					</label>
					<select name="type_filter" id="wcattio-type-filter">
						<option value=""><?php esc_html_e( 'All types', 'harbor-sync-with-attio-for-woocommerce' ); ?></option>
						<option value="person" <?php selected( $type_filter, 'person' ); ?>><?php esc_html_e( 'Person', 'harbor-sync-with-attio-for-woocommerce' ); ?></option>
						<option value="company" <?php selected( $type_filter, 'company' ); ?>><?php esc_html_e( 'Company', 'harbor-sync-with-attio-for-woocommerce' ); ?></option>
						<option value="order" <?php selected( $type_filter, 'order' ); ?>><?php esc_html_e( 'Order', 'harbor-sync-with-attio-for-woocommerce' ); ?></option>
						<option value="list" <?php selected( $type_filter, 'list' ); ?>><?php esc_html_e( 'List', 'harbor-sync-with-attio-for-woocommerce' ); ?></option>
					</select>

					<input type="submit" class="button" value="<?php esc_attr_e( 'Filter', 'harbor-sync-with-attio-for-woocommerce' ); ?>" />
				</form>

				<form method="post" action="" class="wcattio-clear-logs-form">
					<?php wp_nonce_field( 'wcattio_clear_logs' ); ?>
					<input
						type="submit"
						name="wcattio_clear_logs"
						class="button button-secondary"
						value="<?php esc_attr_e( 'Clear logs older than 30 days', 'harbor-sync-with-attio-for-woocommerce' ); ?>"
						onclick="return confirm('<?php echo esc_js( __( 'Delete all sync log entries older than 30 days?', 'harbor-sync-with-attio-for-woocommerce' ) ); ?>')"
					/>
				</form>
			</div>

			<div class="wcattio-log-table-wrap">
			<table class="wp-list-table widefat fixed striped wcattio-log-table">
				<thead>
					<tr>
						<th scope="col" class="column-id"><?php esc_html_e( 'ID', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Type', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Object ID', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Status', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Attio Record ID', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Error', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Synced At', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
					</tr>
				</thead>
				<tbody>
					<?php if ( $logs ) : ?>
						<?php foreach ( $logs as $row ) : ?>
							<tr>
								<td><?php echo esc_html( (string) $row->id ); ?></td>
								<td><?php echo esc_html( (string) $row->object_type ); ?></td>
								<td>
									<?php
									if ( 'order' === $row->object_type ) {
										$edit_url = $order_edit_urls[ (int) $row->object_id ] ?? '';
										if ( $edit_url ) {
											echo '<a href="' . esc_url( $edit_url ) . '">' . esc_html( (string) $row->object_id ) . '</a>';
										} else {
											echo esc_html( (string) $row->object_id );
										}
									} elseif ( 'person' === $row->object_type && ctype_digit( (string) $row->object_id ) ) {
										// Only real (numeric) user IDs link to a profile; order-sourced
										// person rows are namespaced "order-N" and have no user to link to.
										$edit_url = get_edit_user_link( (int) $row->object_id );
										if ( $edit_url ) {
											echo '<a href="' . esc_url( $edit_url ) . '">' . esc_html( (string) $row->object_id ) . '</a>';
										} else {
											echo esc_html( (string) $row->object_id );
										}
									} else {
										echo esc_html( (string) $row->object_id );
									}
									?>
								</td>
								<td>
									<?php
									$badge_mod = match ( $row->status ) {
										'success' => 'wcattio-badge--success',
										'error'   => 'wcattio-badge--error',
										default   => 'wcattio-badge--pending',
									};
	?>
									<span class="wcattio-badge <?php echo esc_attr( $badge_mod ); ?>">
										<?php echo esc_html( ucfirst( (string) $row->status ) ); ?>
									</span>
								</td>
								<td>
									<?php if ( $row->attio_record_id ) : ?>
										<code><?php echo esc_html( (string) $row->attio_record_id ); ?></code>
									<?php else : ?>
										<span class="wcattio-empty">—</span>
									<?php endif; ?>
								</td>
								<td>
									<?php if ( $row->error_message ) : ?>
										<?php
										$msg     = (string) $row->error_message;
										$preview = wp_trim_words( $msg, 10, '' );
										if ( $preview === $msg ) :
											?>
											<span class="wcattio-error-text"><?php echo esc_html( $msg ); ?></span>
										<?php else : ?>
											<details class="wcattio-error-details">
												<summary class="wcattio-error-text"><?php echo esc_html( $preview . '…' ); ?></summary>
												<p class="wcattio-error-full"><?php echo esc_html( $msg ); ?></p>
											</details>
										<?php endif; ?>
									<?php else : ?>
										<span class="wcattio-empty">—</span>
									<?php endif; ?>
								</td>
								<td>
									<?php
									$ts = $row->synced_at ? strtotime( $row->synced_at ) : false;
									if ( false !== $ts ) {
										echo esc_html(
											wp_date(
												get_option( 'date_format' ) . ' ' . get_option( 'time_format' ),
												$ts
											)
										);
									} else {
										echo '<span class="wcattio-empty">—</span>';
									}
									?>
								</td>
							</tr>
						<?php endforeach; ?>
					<?php else : ?>
						<tr>
							<td colspan="7" class="wcattio-empty-state"><?php esc_html_e( 'No sync log entries found.', 'harbor-sync-with-attio-for-woocommerce' ); ?></td>
						</tr>
					<?php endif; ?>
				</tbody>
				<?php if ( $logs ) : ?>
				<tfoot>
					<tr>
						<th scope="col"><?php esc_html_e( 'ID', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Type', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Object ID', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Status', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Attio Record ID', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Error', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
						<th scope="col"><?php esc_html_e( 'Synced At', 'harbor-sync-with-attio-for-woocommerce' ); ?></th>
					</tr>
				</tfoot>
				<?php endif; ?>
			</table>
			</div>

			<?php if ( $pages > 1 ) : ?>
				<div class="tablenav bottom">
					<div class="tablenav-pages">
						<span class="displaying-num">
							<?php
							echo esc_html(
								sprintf(
									/* translators: %d: total row count */
									_n( '%d item', '%d items', $total, 'harbor-sync-with-attio-for-woocommerce' ),
									$total
								)
							);
							?>
						</span>
						<span class="pagination-links">
							<?php if ( $current_page > 1 ) : ?>
								<a class="first-page button" href="<?php echo esc_url( add_query_arg( 'paged', 1, $base_url ) ); ?>">
									<span aria-hidden="true">&laquo;</span>
									<span class="screen-reader-text"><?php esc_html_e( 'First page', 'harbor-sync-with-attio-for-woocommerce' ); ?></span>
								</a>
								<a class="prev-page button" href="<?php echo esc_url( add_query_arg( 'paged', $current_page - 1, $base_url ) ); ?>">
									<span aria-hidden="true">&lsaquo;</span>
									<span class="screen-reader-text"><?php esc_html_e( 'Previous page', 'harbor-sync-with-attio-for-woocommerce' ); ?></span>
								</a>
							<?php endif; ?>

							<span class="paging-input">
								<?php
								echo wp_kses(
									sprintf(
										/* translators: 1: current page number, 2: total pages */
										__( '%1$s of %2$s', 'harbor-sync-with-attio-for-woocommerce' ),
										'<strong>' . esc_html( (string) $current_page ) . '</strong>',
										'<strong>' . esc_html( (string) $pages ) . '</strong>'
									),
									array( 'strong' => array() )
								);
								?>
							</span>

							<?php if ( $current_page < $pages ) : ?>
								<a class="next-page button" href="<?php echo esc_url( add_query_arg( 'paged', $current_page + 1, $base_url ) ); ?>">
									<span aria-hidden="true">&rsaquo;</span>
									<span class="screen-reader-text"><?php esc_html_e( 'Next page', 'harbor-sync-with-attio-for-woocommerce' ); ?></span>
								</a>
								<a class="last-page button" href="<?php echo esc_url( add_query_arg( 'paged', $pages, $base_url ) ); ?>">
									<span aria-hidden="true">&raquo;</span>
									<span class="screen-reader-text"><?php esc_html_e( 'Last page', 'harbor-sync-with-attio-for-woocommerce' ); ?></span>
								</a>
							<?php endif; ?>
						</span>
					</div>
				</div>
			<?php endif; ?>
		</div>
		<?php
	}
}
