<?php

namespace Limb_Chatbot\Includes\Services\Notifications;

use Limb_Chatbot\Includes\Data_Objects\Chatbot_User;
use Limb_Chatbot\Includes\Data_Objects\Notification_Log;
use Limb_Chatbot\Includes\Services\Collection;
use wpdb;

/**
 * Persists notification delivery log (audit) to lbaic_notification_log.
 *
 * Used optionally by Notification_Service to record sent/failed notifications.
 *
 * @package Limb_Chatbot\Includes\Services\Notifications
 * @since 1.0.15
 */
class Notification_Log_Repository {

	/**
	 * Table name (without prefix).
	 */
	const TABLE_NAME = 'lbaic_notification_log';

	/**
	 * @var wpdb
	 * @since 1.0.15
	 */
	private wpdb $wpdb;

	/**
	 * Constructor.
	 *
	 * @param  wpdb|null  $wpdb  Optional wpdb instance.
	 *
	 * @since 1.0.15
	 */
	public function __construct( ?wpdb $wpdb = null ) {
		$this->wpdb = $wpdb ?? $GLOBALS['wpdb'];
	}

	/**
	 * Insert a pending notification row (during queueing). Call update_status when processed.
	 *
	 * @param  int  $chatbot_user_id  Chatbot user ID.
	 * @param  string  $type_key  Notification type key.
	 * @param  string  $channel  Channel key.
	 *
	 * @return int|null Inserted row ID, or null on failure.
	 * @since 1.0.15
	 */
	public function insert_pending( int $chatbot_user_id, string $type_key, string $channel ): ?int {
		$table = $this->wpdb->prefix . self::TABLE_NAME;

		$result = $this->wpdb->insert(
			$table,
			[
				'chatbot_user_id' => $chatbot_user_id,
				'type_key'        => $type_key,
				'channel'         => $channel,
				'status'          => 'pending',
				'sent_at'         => null,
			],
			[ '%d', '%s', '%s', '%s', '%s' ]
		);

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

		return (int) $this->wpdb->insert_id;
	}

	/**
	 * Update a notification log row to completed/failed (during processing).
	 *
	 * @param  int  $id  Log row ID.
	 * @param  string  $status  'sent' or 'failed'.
	 * @param  string|null  $sent_at  Timestamp when sent; null uses current_time.
	 * @param  string|null  $fail_reason  Reason for failure when status is 'failed'.
	 * @param  string|null  $content_type  Content type (e.g. 'text/plain', 'text/html').
	 * @param  string|null  $content  Body content that was sent or attempted.
	 *
	 * @return bool True if updated.
	 * @since 1.0.15
	 */
	public function update_status( int $id, string $status, ?string $sent_at = null, ?string $fail_reason = null, ?string $content_type = null, ?string $content = null ): bool {
		$table = $this->wpdb->prefix . self::TABLE_NAME;
		$sent  = $sent_at ?? current_time( 'mysql' );

		$data   = [ 'status' => $status, 'sent_at' => $sent ];
		$format = [ '%s', '%s' ];

		if ( $status === 'failed' && $fail_reason !== null ) {
			$data['fail_reason'] = $fail_reason;
			$format[]            = '%s';
		}
		if ( $content_type !== null ) {
			$data['content_type'] = $content_type;
			$format[]             = '%s';
		}
		if ( $content !== null ) {
			$data['content'] = $content;
			$format[]        = '%s';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->update(
			$table,
			$data,
			[ 'id' => $id ],
			$format,
			[ '%d' ]
		);

		return $result !== false;
	}

	/**
	 * Log a notification delivery attempt (legacy: when no pending row exists, e.g. old queue items).
	 *
	 * @param  int  $chatbot_user_id  Chatbot user ID.
	 * @param  string  $type_key  Notification type key.
	 * @param  string  $channel  Channel key.
	 * @param  string  $status  'sent' or 'failed'.
	 * @param  string|null  $fail_reason  Reason for failure when status is 'failed'.
	 * @param  string|null  $content_type  Content type (e.g. 'text/plain', 'text/html').
	 * @param  string|null  $content  Body content that was sent or attempted.
	 *
	 * @return bool True if inserted.
	 * @since 1.0.15
	 */
	public function log( int $chatbot_user_id, string $type_key, string $channel, string $status = 'sent', ?string $fail_reason = null, ?string $content_type = null, ?string $content = null ): bool {
		$table = $this->wpdb->prefix . self::TABLE_NAME;

		$data = [
			'chatbot_user_id' => $chatbot_user_id,
			'type_key'        => $type_key,
			'channel'         => $channel,
			'status'          => $status,
			'sent_at'         => current_time( 'mysql' ),
		];
		$format = [ '%d', '%s', '%s', '%s', '%s' ];

		if ( $status === 'failed' && $fail_reason !== null ) {
			$data['fail_reason'] = $fail_reason;
			$format[]            = '%s';
		}
		if ( $content_type !== null ) {
			$data['content_type'] = $content_type;
			$format[]             = '%s';
		}
		if ( $content !== null ) {
			$data['content'] = $content;
			$format[]        = '%s';
		}

		$result = $this->wpdb->insert( $table, $data, $format );

		return $result !== false;
	}

	/**
	 * Get notification log rows with pagination and ordering.
	 *
	 * Supported params:
	 * - page (int, default 1)
	 * - per_page (int, default 20, max 100)
	 * - orderby (id|sent_at|created_at|status|type_key|channel)
	 * - order (asc|desc)
	 *
	 * @param  array  $params
	 * @return Collection
	 * @since 2.0.1
	 */
	public function get_items( array $params = [] ): Collection {
		$page     = isset( $params['page'] ) ? max( 1, (int) $params['page'] ) : 1;
		$per_page = isset( $params['per_page'] ) ? max( 1, (int) $params['per_page'] ) : 20;
		$per_page = min( 100, $per_page );

		$allowed_orderby = [ 'id', 'sent_at', 'created_at', 'status', 'type_key', 'channel' ];
		$orderby = isset( $params['orderby'] ) ? sanitize_key( (string) $params['orderby'] ) : 'id';
		$orderby = in_array( $orderby, $allowed_orderby, true ) ? $orderby : 'id';

		$order = isset( $params['order'] ) ? strtoupper( sanitize_key( (string) $params['order'] ) ) : 'DESC';
		$order = in_array( $order, [ 'ASC', 'DESC' ], true ) ? $order : 'DESC';

		$table = $this->wpdb->prefix . self::TABLE_NAME;
		if ( ! $this->table_exists( $table ) ) {
			$collection = new Collection( [] );
			$collection->set_total( 0 );

			return $collection;
		}

		$include = $params['include'] ?? [];
		if ( ! is_array( $include ) ) {
			$include = is_string( $include ) ? [ $include ] : [];
		}
		$include = array_values( array_filter( array_map( 'sanitize_key', $include ) ) );
		$include_chatbot_user = in_array( 'chatbot_user', $include, true );

		$total = (int) ( Notification_Log::count() ?? 0 );

		if ( $total <= 0 ) {
			$collection = new Collection( [] );
			$collection->set_total( 0 );

			return $collection;
		}

		$collection = Notification_Log::where( [], $per_page, $page, $orderby, $order );
		$collection->set_total( $total );

		if ( $include_chatbot_user ) {
			$collection->each(
				function ( $item ) {
					$chatbot_user_id = $item->get_chatbot_user_id();
					$chatbot_user    = $chatbot_user_id ? Chatbot_User::find( $chatbot_user_id ) : null;
					$item->included['chatbot_user'] = $chatbot_user instanceof Chatbot_User ? $chatbot_user->with( 'email' ) : null;
				}
			);
		}

		return $collection;
	}

	/**
	 * Check whether a database table exists.
	 *
	 * @param  string  $table
	 * @return bool
	 */
	private function table_exists( string $table ): bool {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var( $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
	}
}
