<?php
/**
 * Email log storage (custom table).
 *
 * @package NoorSMTP
 */

namespace NoorWork\NoorSMTP\Log;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class LogRepository
 */
class LogRepository {

	/**
	 * Get the fully-qualified log table name.
	 *
	 * @return string
	 */
	public function table_name() {
		global $wpdb;

		return $wpdb->prefix . 'noorsmtp_log';
	}

	/**
	 * Create (or upgrade) the log table if needed.
	 *
	 * @return void
	 */
	public function maybe_create_table() {
		global $wpdb;

		$installed_version = get_option( 'noorsmtp_db_version' );

		if ( NOORSMTP_DB_VERSION === $installed_version ) {
			return;
		}

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table_name      = $this->table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			sent_at DATETIME NOT NULL,
			to_address TEXT NOT NULL,
			subject TEXT NOT NULL,
			mailer VARCHAR(50) NOT NULL DEFAULT '',
			status VARCHAR(20) NOT NULL DEFAULT 'sent',
			error_message TEXT NULL,
			PRIMARY KEY  (id),
			KEY sent_at (sent_at),
			KEY status (status)
		) {$charset_collate};";

		dbDelta( $sql );

		update_option( 'noorsmtp_db_version', NOORSMTP_DB_VERSION );
	}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

	/**
	 * Insert a log entry.
	 *
	 * @param array $data Log fields: to_address, subject, mailer, status, error_message.
	 * @return void
	 */
	public function insert( array $data ) {
		global $wpdb;

		$settings = get_option( 'noorsmtp_settings', array() );
		if ( empty( $settings['logging_enabled'] ) ) {
			return;
		}

		$wpdb->insert(
			$this->table_name(),
			array(
				'sent_at'       => current_time( 'mysql' ),
				'to_address'    => isset( $data['to_address'] ) ? $data['to_address'] : '',
				'subject'       => isset( $data['subject'] ) ? $data['subject'] : '',
				'mailer'        => isset( $data['mailer'] ) ? $data['mailer'] : '',
				'status'        => isset( $data['status'] ) ? $data['status'] : 'sent',
				'error_message' => isset( $data['error_message'] ) ? $data['error_message'] : null,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s' )
		);
	}

	/**
	 * Fetch recent log entries.
	 *
	 * @param int $per_page Number of rows.
	 * @param int $page     Page number (1-indexed).
	 * @return array
	 */
	public function get_recent( $per_page = 20, $page = 1 ) {
		$result = $this->query(
			array(
				'per_page' => $per_page,
				'page'     => $page,
			)
		);

		return $result['rows'];
	}

	/**
	 * Query log entries with search, filter, and pagination.
	 *
	 * @param array $args Query parameters.
	 * @return array {rows: array, total: int, pages: int}
	 */
	public function query( array $args = array() ) {
		global $wpdb;

		$table = $this->table_name();

		$page     = isset( $args['page'] ) ? max( 1, absint( $args['page'] ) ) : 1;
		$per_page = isset( $args['per_page'] ) ? max( 1, min( 100, absint( $args['per_page'] ) ) ) : 20;
		$offset   = ( $page - 1 ) * $per_page;
		$search   = isset( $args['search'] ) ? trim( sanitize_text_field( $args['search'] ) ) : '';
		$status   = isset( $args['status'] ) ? sanitize_key( $args['status'] ) : '';

		$where_clauses = array( '1=1' );
		$params        = array();

		if ( '' !== $status && 'all' !== $status ) {
			$where_clauses[] = 'status = %s';
			$params[]        = $status;
		}

		if ( '' !== $search ) {
			$like            = '%' . $wpdb->esc_like( $search ) . '%';
			$where_clauses[] = '(to_address LIKE %s OR subject LIKE %s OR mailer LIKE %s OR error_message LIKE %s)';
			$params[]        = $like;
			$params[]        = $like;
			$params[]        = $like;
			$params[]        = $like;
		}

		$where_sql = implode( ' AND ', $where_clauses );

		if ( ! empty( $params ) ) {
			$count_sql = $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", $params );
		} else {
			$count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}";
		}

		$total = (int) $wpdb->get_var( $count_sql );

		$query_params   = $params;
		$query_params[] = $per_page;
		$query_params[] = $offset;

		$data_sql = $wpdb->prepare(
			"SELECT * FROM {$table} WHERE {$where_sql} ORDER BY sent_at DESC LIMIT %d OFFSET %d",
			$query_params
		);

		$rows = $wpdb->get_results( $data_sql, ARRAY_A );

		return array(
			'rows'     => $rows ? $rows : array(),
			'total'    => $total,
			'pages'    => ceil( $total / $per_page ),
			'page'     => $page,
			'per_page' => $per_page,
		);
	}

	/**
	 * Get a single log entry by ID.
	 *
	 * @param int $id Log entry ID.
	 * @return array|null
	 */
	public function get( $id ) {
		global $wpdb;

		$table = $this->table_name();
		$id    = absint( $id );

		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ),
			ARRAY_A
		);

		return $row ? $row : null;
	}

	/**
	 * Get counts breakdown for logs.
	 *
	 * @return array
	 */
	public function get_counts() {
		global $wpdb;

		$table = $this->table_name();

		$total  = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
		$sent   = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'sent'" );
		$failed = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'failed'" );

		return array(
			'total'  => $total,
			'sent'   => $sent,
			'failed' => $failed,
		);
	}

	/**
	 * Clear all log entries.
	 *
	 * @return bool
	 */
	public function clear_all() {
		global $wpdb;

		$table = $this->table_name();

		return (bool) $wpdb->query( "TRUNCATE TABLE {$table}" );
	}

	/**
	 * Clear alias.
	 *
	 * @return bool
	 */
	public function clear() {
		return $this->clear_all();
	}

	/**
	 * Delete log entries older than the given number of days.
	 *
	 * @param int $days Retention period in days.
	 * @return void
	 */
	public function prune( $days = 30 ) {
		global $wpdb;

		$table = $this->table_name();
		$days  = max( 1, absint( $days ) );

		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE sent_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
				$days
			)
		);
	}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
}