<?php
/**
 * Message log repository.
 *
 * @package IntelTele
 */

namespace IntelTele\Repositories;

use IntelTele\Database\Installer;

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

final class MessageLogRepository {
	/**
	 * Insert a message log row.
	 *
	 * @param array<string,mixed> $data Log data.
	 */
	public function insert( array $data ): int {
		global $wpdb;

		$tables = Installer::get_table_names();
		$now    = current_time( 'mysql' );

		$defaults = array(
			'internal_id'          => wp_generate_uuid4(),
			'campaign_id'          => null,
			'order_id'             => null,
			'send_type'            => 'manual',
			'recipient_phone'      => '',
			'recipient_name'       => '',
			'recipient_role'       => '',
			'channel'              => '',
			'scheme'               => '',
			'message'              => '',
			'fallback_message'     => '',
			'api_message_id'       => '',
			'api_status'           => '',
			'api_status_code'      => null,
			'error_code'           => '',
			'api_response'         => null,
			'status'               => 'created',
			'error_reason'         => null,
			'viber_ttl'            => null,
			'viber_sent_at'        => null,
			'sms_fallback_sent'    => 0,
			'sms_fallback_sent_at' => null,
			'duplicate_key'        => '',
			'created_at'           => $now,
			'updated_at'           => $now,
			'status_updated_at'    => null,
		);

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

		$inserted = $wpdb->insert(
			$tables['message_logs'],
			$row,
			array(
				'%s',
				'%d',
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%d',
				'%s',
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			)
		);

		return false === $inserted ? 0 : (int) $wpdb->insert_id;
	}

	/**
	 * Check if a successful message with duplicate key already exists.
	 *
	 * @param string $duplicate_key Duplicate key.
	 */
	public function successful_duplicate_exists( string $duplicate_key ): bool {
		global $wpdb;

		if ( '' === $duplicate_key ) {
			return false;
		}

		$tables = Installer::get_table_names();
		$count  = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$tables['message_logs']} WHERE duplicate_key = %s AND status = 'sent'",
				$duplicate_key
			)
		);

		return $count > 0;
	}

	/**
	 * Get manual logs for reports.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_manual_logs( array $args = array() ): array {
		return $this->get_logs_by_type( 'manual', $args );
	}

	/**
	 * Get order status logs for reports.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_order_status_logs( array $args = array() ): array {
		return $this->get_logs_by_type( 'order_status', $args );
	}

	/**
	 * Get bulk logs for reports.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_bulk_logs( array $args = array() ): array {
		return $this->get_logs_by_type( 'bulk', $args );
	}

	/**
	 * Get logs that need status sync.
	 *
	 * @param string $channel Channel.
	 * @param int    $limit Limit.
	 * @return array<int,object>
	 */
	public function get_sync_candidates( string $channel, int $limit = 50 ): array {
		global $wpdb;

		$tables = Installer::get_table_names();

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$tables['message_logs']}
				WHERE channel = %s
					AND api_message_id <> ''
					AND status IN ('sent', 'pending')
					AND (api_status = '' OR api_status = 'OK' OR api_status = 'pending')
				ORDER BY updated_at ASC
				LIMIT %d",
				$channel,
				$limit
			)
		);
	}

	/**
	 * Update delivery status.
	 *
	 * @param int    $log_id Log ID.
	 * @param string $api_status API status.
	 * @param string $status Internal status.
	 * @param string $date Status date.
	 */
	public function update_delivery_status( int $log_id, string $api_status, string $status, string $date = '' ): bool {
		global $wpdb;

		$tables = Installer::get_table_names();

		return false !== $wpdb->update(
			$tables['message_logs'],
			array(
				'api_status'        => $api_status,
				'status'            => $status,
				'status_updated_at' => '' === $date ? current_time( 'mysql' ) : get_date_from_gmt( gmdate( 'Y-m-d H:i:s', strtotime( $date ) ) ),
				'updated_at'        => current_time( 'mysql' ),
			),
			array( 'id' => $log_id ),
			array( '%s', '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Update delivery status by API message ID.
	 *
	 * @param string $api_message_id API message ID.
	 * @param string $api_status API status.
	 * @param string $status Internal status.
	 * @param string $date Status date.
	 */
	public function update_delivery_status_by_api_message_id( string $api_message_id, string $api_status, string $status, string $date = '' ): int {
		global $wpdb;

		$api_message_id = trim( $api_message_id );
		if ( '' === $api_message_id ) {
			return 0;
		}

		$tables = Installer::get_table_names();
		$date   = '' === $date ? current_time( 'mysql' ) : get_date_from_gmt( gmdate( 'Y-m-d H:i:s', strtotime( $date ) ) );
		$matched = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$tables['message_logs']} WHERE api_message_id = %s",
				$api_message_id
			)
		);

		if ( 0 === $matched ) {
			return 0;
		}

		$wpdb->update(
			$tables['message_logs'],
			array(
				'api_status'        => $api_status,
				'status'            => $status,
				'status_updated_at' => $date,
				'updated_at'        => current_time( 'mysql' ),
			),
			array( 'api_message_id' => $api_message_id ),
			array( '%s', '%s', '%s', '%s' ),
			array( '%s' )
		);

		return $matched;
	}

	/**
	 * Get Viber + SMS fallback candidates.
	 *
	 * @param int $limit Limit.
	 * @return array<int,object>
	 */
	public function get_viber_fallback_candidates( int $limit = 50 ): array {
		global $wpdb;

		$tables = Installer::get_table_names();
		$now    = current_time( 'mysql' );

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$tables['message_logs']}
				WHERE scheme = 'viber_sms'
					AND channel = 'viber'
					AND sms_fallback_sent = 0
					AND api_message_id <> ''
					AND (
						api_status IN ('undeliv', 'expired', 'rejectd')
						OR (
							api_status IN ('', 'OK', 'pending')
							AND viber_sent_at IS NOT NULL
							AND viber_ttl IS NOT NULL
							AND DATE_ADD(viber_sent_at, INTERVAL viber_ttl SECOND) <= %s
						)
					)
				ORDER BY created_at ASC
				LIMIT %d",
				$now,
				$limit
			)
		);
	}

	/**
	 * Mark fallback sent.
	 *
	 * @param int $log_id Log ID.
	 */
	public function mark_sms_fallback_sent( int $log_id ): bool {
		global $wpdb;

		$tables = Installer::get_table_names();

		return false !== $wpdb->update(
			$tables['message_logs'],
			array(
				'sms_fallback_sent'    => 1,
				'sms_fallback_sent_at' => current_time( 'mysql' ),
				'updated_at'           => current_time( 'mysql' ),
			),
			array( 'id' => $log_id ),
			array( '%d', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Get logs by send type.
	 *
	 * @param string              $send_type Send type.
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	private function get_logs_by_type( string $send_type, array $args = array() ): array {
		return $this->get_logs( array_merge( $args, array( 'send_type' => $send_type ) ) );
	}

	/**
	 * Get logs with filters.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public function get_logs( array $args = array() ): array {
		global $wpdb;

		$tables   = Installer::get_table_names();
		$limit    = isset( $args['limit'] ) ? max( 1, min( 5000, absint( $args['limit'] ) ) ) : 20;
		$offset   = isset( $args['offset'] ) ? max( 0, absint( $args['offset'] ) ) : 0;
		$where    = array( '1=1' );
		$params   = array();
		$send_type = isset( $args['send_type'] ) ? sanitize_key( (string) $args['send_type'] ) : '';
		$search   = isset( $args['search'] ) ? trim( (string) $args['search'] ) : '';
		$date_from = isset( $args['date_from'] ) ? trim( (string) $args['date_from'] ) : '';
		$date_to   = isset( $args['date_to'] ) ? trim( (string) $args['date_to'] ) : '';
		$role      = isset( $args['role'] ) ? trim( (string) $args['role'] ) : '';
		$campaign_id = isset( $args['campaign_id'] ) ? absint( $args['campaign_id'] ) : 0;
		$require_api_message_id = ! empty( $args['require_api_message_id'] );

		if ( '' !== $send_type ) {
			$where[]  = 'send_type = %s';
			$params[] = $send_type;
		}

		if ( '' !== $search ) {
			$where[]  = '(recipient_phone LIKE %s OR recipient_name LIKE %s OR api_message_id LIKE %s OR error_code LIKE %s)';
			$like     = '%' . $wpdb->esc_like( $search ) . '%';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

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

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

		if ( '' !== $role ) {
			$where[]  = 'recipient_role = %s';
			$params[] = $role;
		}

		if ( $campaign_id > 0 ) {
			$where[]  = 'campaign_id = %d';
			$params[] = $campaign_id;
		}

		if ( $require_api_message_id ) {
			$where[] = "api_message_id <> ''";
		}

		$sql = "SELECT * FROM {$tables['message_logs']} WHERE " . implode( ' AND ', $where ) . ' ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d';
		$params[] = $limit;
		$params[] = $offset;

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Internal table names and fixed WHERE clauses; all values are bound by prepare().
	}

	/**
	 * Count logs with filters.
	 *
	 * @param array<string,mixed> $args Query args.
	 */
	public function count_logs( array $args = array() ): int {
		global $wpdb;

		$tables = Installer::get_table_names();
		$where  = array( '1=1' );
		$params = array();
		$send_type = isset( $args['send_type'] ) ? sanitize_key( (string) $args['send_type'] ) : '';
		$search = isset( $args['search'] ) ? trim( (string) $args['search'] ) : '';
		$date_from = isset( $args['date_from'] ) ? trim( (string) $args['date_from'] ) : '';
		$date_to = isset( $args['date_to'] ) ? trim( (string) $args['date_to'] ) : '';
		$role = isset( $args['role'] ) ? trim( (string) $args['role'] ) : '';
		$campaign_id = isset( $args['campaign_id'] ) ? absint( $args['campaign_id'] ) : 0;
		$require_api_message_id = ! empty( $args['require_api_message_id'] );

		if ( '' !== $send_type ) {
			$where[] = 'send_type = %s';
			$params[] = $send_type;
		}

		if ( '' !== $search ) {
			$where[] = '(recipient_phone LIKE %s OR recipient_name LIKE %s OR api_message_id LIKE %s OR error_code LIKE %s)';
			$like = '%' . $wpdb->esc_like( $search ) . '%';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

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

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

		if ( '' !== $role ) {
			$where[] = 'recipient_role = %s';
			$params[] = $role;
		}

		if ( $campaign_id > 0 ) {
			$where[] = 'campaign_id = %d';
			$params[] = $campaign_id;
		}

		if ( $require_api_message_id ) {
			$where[] = "api_message_id <> ''";
		}

		$sql = "SELECT COUNT(*) FROM {$tables['message_logs']} WHERE " . implode( ' AND ', $where );

		if ( empty( $params ) ) {
			return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user values: the query contains only internal table names and fixed clauses.
		}

		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Internal table names and fixed WHERE clauses; all values are bound by prepare().
	}
}