<?php
// DESCRIPTION: Provides CRUD operations for the swise_sync_logs table, including
// log creation, querying with filters/pagination, stats aggregation, and cleanup.

namespace SheetWise\Sync;

use SheetWise\Install\Installer;

class SyncLogger {

	/**
	 * Insert a sync log entry
	 *
	 * @since 2.0.0
	 *
	 * @param array $data {
	 *     @type int    $integration_id Integration post ID
	 *     @type string $hook           WordPress hook name
	 *     @type string $status         Status: success, failed, dead_lettered, retrying
	 *     @type int    $attempts       Number of attempts made
	 *     @type string $error_message  Error message on failure
	 *     @type string $error_code     Error code on failure
	 *     @type mixed  $data_payload   Original data sent (will be JSON-encoded)
	 *     @type int    $row_count      Number of rows affected
	 * }
	 *
	 * @return int|false Insert ID or false on failure
	 */
	public function log( array $data ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		$defaults = [
			'integration_id' => 0,
			'hook'           => '',
			'status'         => 'pending',
			'attempts'       => 0,
			'error_message'  => null,
			'error_code'     => null,
			'data_payload'   => null,
			'row_count'      => 0,
		];

		$data = wp_parse_args( $data, $defaults );

		// JSON-encode the data payload if it's not already a string
		if ( $data['data_payload'] !== null && ! is_string( $data['data_payload'] ) ) {
			$data['data_payload'] = wp_json_encode( $data['data_payload'] );
		}

		$result = $wpdb->insert(
			$table,
			[
				'integration_id' => absint( $data['integration_id'] ),
				'hook'           => sanitize_text_field( $data['hook'] ),
				'status'         => sanitize_text_field( $data['status'] ),
				'attempts'       => absint( $data['attempts'] ),
				'error_message'  => $data['error_message'],
				'error_code'     => $data['error_code'] ? sanitize_text_field( $data['error_code'] ) : null,
				'data_payload'   => $data['data_payload'],
				'row_count'      => absint( $data['row_count'] ),
			],
			[ '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%d' ]
		);

		if ( $result === false ) {
			return false;
		}

		return $wpdb->insert_id;
	}

	/**
	 * Get paginated sync log entries with optional filters
	 *
	 * @since 2.0.0
	 *
	 * @param array $args {
	 *     @type int    $integration_id Filter by integration ID
	 *     @type string $status         Filter by status
	 *     @type string $date_from      Filter by start date (Y-m-d)
	 *     @type string $date_to        Filter by end date (Y-m-d)
	 *     @type int    $page           Page number (1-based)
	 *     @type int    $per_page       Items per page
	 *     @type string $orderby        Column to order by
	 *     @type string $order          ASC or DESC
	 * }
	 *
	 * @return array {
	 *     @type array $items Array of log entry objects
	 *     @type int   $total Total matching entries
	 *     @type int   $pages Total pages
	 * }
	 */
	public function get_logs( array $args = [] ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		$defaults = [
			'integration_id' => 0,
			'status'         => '',
			'date_from'      => '',
			'date_to'        => '',
			'page'           => 1,
			'per_page'       => 20,
			'orderby'        => 'created_at',
			'order'          => 'DESC',
		];

		$args = wp_parse_args( $args, $defaults );

		$where  = [];
		$values = [];

		if ( ! empty( $args['integration_id'] ) ) {
			$where[]  = 'integration_id = %d';
			$values[] = absint( $args['integration_id'] );
		}

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_text_field( $args['status'] );
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'created_at >= %s';
			$values[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00';
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'created_at <= %s';
			$values[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59';
		}

		$where_clause = '';
		if ( ! empty( $where ) ) {
			$where_clause = 'WHERE ' . implode( ' AND ', $where );
		}

		// Whitelist orderby columns
		$allowed_orderby = [ 'id', 'integration_id', 'hook', 'status', 'attempts', 'created_at', 'updated_at' ];
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = strtoupper( $args['order'] ) === 'ASC' ? 'ASC' : 'DESC';

		$per_page = absint( $args['per_page'] );
		$page     = max( 1, absint( $args['page'] ) );
		$offset   = ( $page - 1 ) * $per_page;

		// Get total count
		$count_sql = "SELECT COUNT(*) FROM $table $where_clause";
		if ( ! empty( $values ) ) {
			$count_sql = $wpdb->prepare( $count_sql, $values );
		}
		$total = (int) $wpdb->get_var( $count_sql );

		// Get items
		$query = "SELECT * FROM $table $where_clause ORDER BY $orderby $order LIMIT %d OFFSET %d";
		$query_values = array_merge( $values, [ $per_page, $offset ] );
		$items = $wpdb->get_results( $wpdb->prepare( $query, $query_values ) );

		return [
			'items' => $items ?: [],
			'total' => $total,
			'pages' => $per_page > 0 ? (int) ceil( $total / $per_page ) : 0,
		];
	}

	/**
	 * Get a single sync log entry by ID
	 *
	 * @since 2.0.0
	 *
	 * @param int $id Log entry ID
	 *
	 * @return object|null
	 */
	public function get_log( $id ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM $table WHERE id = %d", absint( $id ) )
		);
	}

	/**
	 * Update a sync log entry
	 *
	 * @since 2.0.0
	 *
	 * @param int   $id   Log entry ID
	 * @param array $data Fields to update
	 *
	 * @return bool
	 */
	public function update_log( $id, array $data ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		$result = $wpdb->update(
			$table,
			$data,
			[ 'id' => absint( $id ) ]
		);

		return $result !== false;
	}

	/**
	 * Delete sync log entries matching criteria
	 *
	 * @since 2.0.0
	 *
	 * @param array $args {
	 *     @type string $older_than     Delete entries older than this date (Y-m-d)
	 *     @type string $status         Delete entries with this status
	 *     @type int    $integration_id Delete entries for this integration
	 * }
	 *
	 * @return int Number of rows deleted
	 */
	public function delete_logs( array $args ) {
		global $wpdb;

		$table  = Installer::get_sync_logs_table();
		$where  = [];
		$values = [];

		if ( ! empty( $args['older_than'] ) ) {
			$where[]  = 'created_at < %s';
			$values[] = sanitize_text_field( $args['older_than'] ) . ' 00:00:00';
		}

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = sanitize_text_field( $args['status'] );
		}

		if ( ! empty( $args['integration_id'] ) ) {
			$where[]  = 'integration_id = %d';
			$values[] = absint( $args['integration_id'] );
		}

		if ( empty( $where ) ) {
			return 0;
		}

		$where_clause = 'WHERE ' . implode( ' AND ', $where );
		$sql          = "DELETE FROM $table $where_clause";

		return (int) $wpdb->query( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Delete a single sync log entry by ID
	 *
	 * @since 2.0.0
	 *
	 * @param int $id Log entry ID
	 *
	 * @return bool
	 */
	public function delete_log( $id ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		$result = $wpdb->delete( $table, [ 'id' => absint( $id ) ], [ '%d' ] );

		return $result !== false;
	}

	/**
	 * Get aggregate stats for sync logs
	 *
	 * @since 2.0.0
	 *
	 * @param array $args {
	 *     @type int    $integration_id Filter by integration ID
	 *     @type string $date_from      Filter by start date (Y-m-d)
	 *     @type string $date_to        Filter by end date (Y-m-d)
	 * }
	 *
	 * @return array {
	 *     @type int $total          Total sync entries
	 *     @type int $success        Successful syncs
	 *     @type int $failed         Failed syncs
	 *     @type int $dead_lettered  Dead-lettered syncs
	 * }
	 */
	public function get_stats( array $args = [] ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		$where  = [];
		$values = [];

		if ( ! empty( $args['integration_id'] ) ) {
			$where[]  = 'integration_id = %d';
			$values[] = absint( $args['integration_id'] );
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'created_at >= %s';
			$values[] = sanitize_text_field( $args['date_from'] ) . ' 00:00:00';
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'created_at <= %s';
			$values[] = sanitize_text_field( $args['date_to'] ) . ' 23:59:59';
		}

		$where_clause = '';
		if ( ! empty( $where ) ) {
			$where_clause = 'WHERE ' . implode( ' AND ', $where );
		}

		$sql = "SELECT
			COUNT(*) as total,
			SUM( CASE WHEN status = 'success' THEN 1 ELSE 0 END ) as success,
			SUM( CASE WHEN status = 'failed' THEN 1 ELSE 0 END ) as failed,
			SUM( CASE WHEN status = 'dead_lettered' THEN 1 ELSE 0 END ) as dead_lettered,
			SUM( CASE WHEN status = 'skipped' THEN 1 ELSE 0 END ) as skipped
		FROM $table $where_clause";

		if ( ! empty( $values ) ) {
			$sql = $wpdb->prepare( $sql, $values );
		}

		$row = $wpdb->get_row( $sql );

		return [
			'total'         => (int) ( $row->total ?? 0 ),
			'success'       => (int) ( $row->success ?? 0 ),
			'failed'        => (int) ( $row->failed ?? 0 ),
			'dead_lettered' => (int) ( $row->dead_lettered ?? 0 ),
			'skipped'       => (int) ( $row->skipped ?? 0 ),
		];
	}

	/**
	 * Count consecutive failures for a given integration (most recent first)
	 *
	 * @since 2.0.0
	 *
	 * @param int $integration_id Integration post ID
	 *
	 * @return int
	 */
	public function get_consecutive_failures( $integration_id ) {
		global $wpdb;

		$table = Installer::get_sync_logs_table();

		// Get the most recent logs for this integration, ordered newest first
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT status FROM $table WHERE integration_id = %d ORDER BY created_at DESC LIMIT 50",
				absint( $integration_id )
			)
		);

		if ( empty( $results ) ) {
			return 0;
		}

		$count = 0;
		foreach ( $results as $row ) {
			if ( $row->status === 'success' ) {
				break;
			}
			$count++;
		}

		return $count;
	}

	/**
	 * Hook into swise_sync_completed to automatically log sync results
	 *
	 * @since 2.0.0
	 *
	 * @param array $sync_result Result from RetryHandler
	 * @param array $context     Sync context with integration_id, hook, data_payload
	 *
	 * @return void
	 */
	public function on_sync_completed( $sync_result, $context ) {
		$log_data = [
			'integration_id' => $context['integration_id'] ?? 0,
			'hook'           => $context['hook'] ?? '',
			'status'         => $sync_result['status'] ?? 'failed',
			'attempts'       => $sync_result['attempts'] ?? 1,
			'data_payload'   => $context['data_payload'] ?? null,
			'row_count'      => 1,
		];

		if ( ! empty( $sync_result['error'] ) ) {
			$log_data['error_message'] = $sync_result['error']['message'] ?? '';
			$log_data['error_code']    = (string) ( $sync_result['error']['code'] ?? '' );
		}

		$this->log( $log_data );
	}
}
