<?php
declare(strict_types=1);
defined('ABSPATH') || exit;

class DSProofwp_Review_Request_Repository
{
	private wpdb $wpdb;

	private string $settings_table;
	private string $templates_table;
	private string $requests_table;

	public function __construct(wpdb $wpdb)
	{
		$this->wpdb = $wpdb;

		$prefix = $wpdb->prefix . 'dsproofwp_';

		$this->settings_table = $prefix . 'review_request_settings';
		$this->templates_table = $prefix . 'review_request_email_templates';
		$this->requests_table = $prefix . 'review_requests';
	}

	/**
	 * SETTINGS
	 */
	public function get_settings(): array
	{
		$row = $this->wpdb->get_row(
			"SELECT * FROM {$this->settings_table} LIMIT 1",
			ARRAY_A
		);
 
		if ($row) {
			if (!isset($row['followup_max_count'])) {
				$row['followup_max_count'] = 1;
			}
			return $row;
		}

		return [
			'first_request_enabled'    => 0,
			'first_request_delay_days' => 1,
			'followup_enabled'         => 0,
			'followup_delay_days'      => 3,
			'followup_max_count'       => 1,
		];
	}

	public function upsert_settings(array $data): bool
	{
		$existing = $this->wpdb->get_var("SELECT id FROM {$this->settings_table} LIMIT 1");
 
		$payload = [
			'first_request_enabled'    => (int) (! empty($data['first_request_enabled'])),
			'first_request_delay_days' => (int) ($data['first_request_delay_days'] ?? 0),
			'followup_enabled'         => (int) (! empty($data['followup_enabled'])),
			'followup_delay_days'      => (int) ($data['followup_delay_days'] ?? 0),
			'followup_max_count'       => (int) ($data['followup_max_count'] ?? 1),
		];
 
		if ($existing) {
			return (bool) $this->wpdb->update(
				$this->settings_table,
				$payload,
				['id' => $existing],
				['%d', '%d', '%d', '%d', '%d'],
				['%d']
			);
		}
 
		return (bool) $this->wpdb->insert(
			$this->settings_table,
			$payload,
			['%d', '%d', '%d', '%d', '%d']
		);
	}

	/**
	 * TEMPLATES
	 */
	public function get_templates(): array
	{
		$rows = $this->wpdb->get_results(
			"SELECT * FROM {$this->templates_table}",
			ARRAY_A
		);

		return array_map([$this, 'map_template'], $rows ?: []);
	}

	public function upsert_template(string $type, array $data): bool
	{
		$exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->templates_table} WHERE type = %s LIMIT 1",
				$type
			)
		);

		$payload = [
			'type' => $type,
			'subject' => sanitize_text_field((string) ($data['subject'] ?? '')),
			'body' => wp_kses_post((string) ($data['body'] ?? '')),
			'button_text' => sanitize_text_field((string) ($data['button_text'] ?? '')),
		];

		if ($exists) {
			return (bool) $this->wpdb->update(
				$this->templates_table,
				$payload,
				['type' => $type],
				['%s', '%s', '%s', '%s'],
				['%s']
			);
		}

		return (bool) $this->wpdb->insert(
			$this->templates_table,
			$payload,
			['%s', '%s', '%s', '%s']
		);
	}

	/**
	 * REVIEW REQUESTS (QUEUE)
	 */
	public function list_requests(array $filters = []): array
	{
		$where = ['1=1'];
		$params = [];

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

		if (!empty($filters['email_type'])) {
			$where[] = 'type = %s';
			$params[] = sanitize_text_field($filters['email_type']);
		}

		$per_page = max(1, min(100, (int) ($filters['per_page'] ?? 20)));
		$page = max(1, (int) ($filters['page'] ?? 1));
		$offset = ($page - 1) * $per_page;

		$sort = (isset($filters['sort']) && $filters['sort'] === 'oldest') ? 'ASC' : 'DESC';

		$query = "SELECT SQL_CALC_FOUND_ROWS *
			FROM {$this->requests_table}
			WHERE " . implode(' AND ', $where) . "
			ORDER BY scheduled_at {$sort}
			LIMIT %d OFFSET %d";

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

		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare($query, $params),
			ARRAY_A
		);

		return [
			'items' => array_map([$this, 'map_request'], $rows ?: []),
			'total' => (int) $this->wpdb->get_var('SELECT FOUND_ROWS()'),
			'page' => $page,
			'per_page' => $per_page,
		];
	}

	public function cancel_request(int $id): bool
	{
		return (bool) $this->wpdb->update(
			$this->requests_table,
			['status' => 'cancelled'],
			['id' => $id],
			['%s'],
			['%d']
		);
	}

	public function delete_request(int $id): bool
	{
		return (bool) $this->wpdb->delete(
			$this->requests_table,
			['id' => $id],
			['%d']
		);
	}

	/**
	 * MAPPERS
	 */
	private function map_template(array $row): array
	{
		return [
			'id' => (int) $row['id'],
			'type' => (string) $row['type'],
			'subject' => (string) $row['subject'],
			'body' => (string) $row['body'],
			'button_text' => (string) $row['button_text'],
		];
	}

	private function map_request(array $row): array
	{
		return [
			'id' => (int) $row['id'],
			'order_id' => (int) $row['order_id'],
			'token' => isset($row['token']) ? (string) $row['token'] : null,
			'customer_email' => (string) $row['customer_email'],
			'customer_name' => (string) $row['customer_name'],
			'type' => (string) $row['type'],
			'status' => (string) $row['status'],
			'scheduled_at' => (string) $row['scheduled_at'],
			'sent_at' => (string) $row['sent_at'],
			'reminder_count' => isset($row['reminder_count']) ? (int) $row['reminder_count'] : 0,
		];
	}
 
	public function create_request(array $data): bool
	{
		return (bool) $this->wpdb->insert(
			$this->requests_table,
			[
				'order_id' => (int) $data['order_id'],
				'token' => !empty($data['token']) ? sanitize_text_field($data['token']) : null,
				'customer_email' => sanitize_email($data['customer_email']),
				'customer_name' => sanitize_text_field($data['customer_name']),
				'type' => sanitize_text_field($data['type']),
				'scheduled_at' => $data['scheduled_at'],
				'status' => 'scheduled',
				'reminder_count' => (int) ($data['reminder_count'] ?? 0),
			],
			['%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d']
		);
	}

	public function get_due_requests(): array
	{
		return $this->wpdb->get_results(
			"SELECT * FROM {$this->requests_table}
		WHERE status = 'scheduled'
		AND scheduled_at <= NOW()",
			ARRAY_A
		) ?: [];
	}


	public function get_requests_by_order(int $order_id): array
	{
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->requests_table} WHERE order_id = %d",
				$order_id
			),
			ARRAY_A
		) ?: [];
	}

	public function update_request(int $id, array $data): bool
	{
		$result = $this->wpdb->update(
			$this->requests_table,
			$data,            // Data to update
			['id' => $id],    // Where clause
			null,             // Format of data (null lets wpdb infer from array)
			['%d']            // Format of the where clause (%d for integer ID)
		);

		return $result !== false;
	}

	public function mark_as_sent(int $id): void
	{
		$this->wpdb->update(
			$this->requests_table,
			[
				'status' => 'sent',
				'sent_at' => current_time('mysql'),
			],
			['id' => $id],
			['%s', '%s'],
			['%d']
		);
	}

	public function mark_as_failed(int $id): void
	{
		$this->wpdb->update(
			$this->requests_table,
			['status' => 'failed'],
			['id' => $id],
			['%s'],
			['%d']
		);
	}

	public function get_template_by_type(string $type): array
	{
		return $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->templates_table} WHERE type = %s LIMIT 1",
				$type
			),
			ARRAY_A
		) ?: [];
	}

	public function cancel_followup_requests(int $product_id, string $email): void
	{
		$this->wpdb->update(
			$this->requests_table,
			['status' => 'cancelled'],
			[
				'customer_email' => $email,
				'type' => 'followup',
				'status' => 'scheduled',
			],
			['%s'],
			['%s', '%s', '%s']
		);
	}

	public function cancel_followup_requests_by_order(int $order_id): void
	{
		$this->wpdb->update(
			$this->requests_table,
			['status' => 'cancelled'],
			[
				'order_id' => $order_id,
				'type' => 'followup',
				'status' => 'scheduled',
			],
			['%s'],
			['%d', '%s', '%s']
		);
	}

	public function cancel_all_requests_by_order(int $order_id): void
	{
		$this->wpdb->update(
			$this->requests_table,
			['status' => 'cancelled'],
			[
				'order_id' => $order_id,
				'status'   => 'scheduled',
			],
			['%s'],
			['%d', '%s']
		);
	}

	public function get_request_by_token(string $token): array
	{
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->requests_table} WHERE token = %s LIMIT 1",
				$token
			),
			ARRAY_A
		);

		return $row ? $this->map_request($row) : [];
	}

	public function get_requests_table_name(): string
	{
		return $this->requests_table;
	}
}
