<?php
/**
 * Invitation repository.
 *
 * Handles CRUD and lookup operations for creator invitations.
 *
 * @package Ugcify\Database
 */

namespace Ugcify\Database;

use WP_Error;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class InvitationRepository
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- All dynamic queries use $wpdb->prepare(); table name is a trusted internal constant.
 */
class InvitationRepository {
	/**
	 * Allowed invitation statuses.
	 *
	 * @var array<int,string>
	 */
	private const ALLOWED_STATUSES = array( 'pending', 'accepted', 'expired', 'revoked' );

	/**
	 * WordPress database instance.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table;

	/**
	 * Constructor.
	 *
	 * @param \wpdb|null $wpdb WordPress database instance. Falls back to global.
	 */
	public function __construct( ?\wpdb $wpdb = null ) {
		if ( null === $wpdb ) {
			global $wpdb;
		}

		$this->wpdb  = $wpdb;
		$this->table = $this->wpdb->prefix . 'ugcify_influencer_invitations';
	}

	/**
	 * Create a new invitation.
	 *
	 * @param array<string,mixed> $data Invitation data.
	 * @return array<string,mixed>|WP_Error
	 */
	public function create( array $data ) {
		if ( empty( $data['email'] ) || empty( $data['token'] ) || empty( $data['expires_at'] ) || empty( $data['invited_by'] ) ) {
			return new WP_Error(
				'invalid_invitation_data',
				__( 'Invitation email, token, expiry, and inviter are required.', 'ugcify' )
			);
		}

		$record = $this->prepare_record( $data, true );

		if ( empty( $record['email'] ) || empty( $record['token'] ) || empty( $record['expires_at'] ) || empty( $record['invited_by'] ) ) {
			return new WP_Error(
				'invalid_invitation_data',
				__( 'Invitation email, token, expiry, and inviter are required.', 'ugcify' )
			);
		}

		$result = $this->wpdb->insert( $this->table, $record, $this->get_formats( $record ) );

		if ( false === $result ) {
			return new WP_Error(
				'db_insert_error',
				__( 'Failed to create invitation.', 'ugcify' )
			);
		}

		$invitation = $this->find( (int) $this->wpdb->insert_id );

		if ( ! $invitation ) {
			return new WP_Error(
				'db_insert_error',
				__( 'Failed to read created invitation.', 'ugcify' )
			);
		}

		return $invitation;
	}

	/**
	 * Find an invitation by ID.
	 *
	 * @param int $invitation_id Invitation ID.
	 * @return array<string,mixed>|null
	 */
	public function find( int $invitation_id ): ?array {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE id = %d",
				$invitation_id
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		return $this->normalize_row( $row );
	}

	/**
	 * Find the latest invitation by email.
	 *
	 * @param string            $email    Invitation email.
	 * @param array<int,string> $statuses Optional statuses.
	 * @return array<string,mixed>|null
	 */
	public function find_by_email( string $email, array $statuses = array() ): ?array {
		$results = $this->query(
			array(
				'email'  => $email,
				'status' => $statuses,
				'limit'  => 1,
				'offset' => 0,
			)
		);

		return ! empty( $results ) ? $results[0] : null;
	}

	/**
	 * Find an invitation by token.
	 *
	 * @param string $token Invitation token.
	 * @return array<string,mixed>|null
	 */
	public function find_by_token( string $token ): ?array {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE token = %s",
				sanitize_text_field( $token )
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		return $this->normalize_row( $row );
	}

	/**
	 * Query invitations.
	 *
	 * @param array<string,mixed> $filters Query filters.
	 * @return array<int,array<string,mixed>>
	 */
	public function query( array $filters = array() ): array {
		$where = $this->build_where_clauses( $filters );
		$limit = max( 1, absint( $filters['limit'] ?? 20 ) );
		$offset = max( 0, absint( $filters['offset'] ?? 0 ) );

		$sql = "SELECT * FROM {$this->table}{$where['sql']} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d";
		$values = array_merge( $where['values'], array( $limit, $offset ) );
		$prepared_sql = $this->wpdb->prepare( $sql, $values );
		$rows = $this->wpdb->get_results( $prepared_sql, ARRAY_A );

		if ( ! is_array( $rows ) ) {
			return array();
		}

		$invitations = array();
		foreach ( $rows as $row ) {
			$invitations[] = $this->normalize_row( $row );
		}

		return $invitations;
	}

	/**
	 * Count invitations matching filters.
	 *
	 * @param array<string,mixed> $filters Query filters.
	 * @return int
	 */
	public function count( array $filters = array() ): int {
		$where = $this->build_where_clauses( $filters );
		$sql = "SELECT COUNT(*) FROM {$this->table}{$where['sql']}";
		$prepared_sql = empty( $where['values'] ) ? $sql : $this->wpdb->prepare( $sql, $where['values'] );
		$count = $this->wpdb->get_var( $prepared_sql );

		return absint( $count );
	}

	/**
	 * Update an invitation.
	 *
	 * @param int                 $invitation_id Invitation ID.
	 * @param array<string,mixed> $data          Fields to update.
	 * @return bool|WP_Error
	 */
	public function update( int $invitation_id, array $data ) {
		$record = $this->prepare_record( $data, false );

		if ( array_key_exists( 'email', $record ) && '' === $record['email'] ) {
			return new WP_Error(
				'invalid_invitation_data',
				__( 'Invitation email is invalid.', 'ugcify' )
			);
		}

		if ( array_key_exists( 'token', $record ) && '' === $record['token'] ) {
			return new WP_Error(
				'invalid_invitation_data',
				__( 'Invitation token is invalid.', 'ugcify' )
			);
		}

		if ( empty( $record ) ) {
			return true;
		}

		$record['updated_at'] = current_time( 'mysql' );

		$result = $this->wpdb->update(
			$this->table,
			$record,
			array( 'id' => $invitation_id ),
			$this->get_formats( $record ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_update_error',
				__( 'Failed to update invitation.', 'ugcify' )
			);
		}

		return true;
	}

	/**
	 * Delete an invitation.
	 *
	 * @param int $invitation_id Invitation ID.
	 * @return bool|WP_Error
	 */
	public function delete( int $invitation_id ) {
		$result = $this->wpdb->delete(
			$this->table,
			array( 'id' => $invitation_id ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_delete_error',
				__( 'Failed to delete invitation.', 'ugcify' )
			);
		}

		return true;
	}

	/**
	 * Prepare invitation data for writes.
	 *
	 * @param array<string,mixed> $data      Raw data.
	 * @param bool                $is_create Whether this is a create operation.
	 * @return array<string,mixed>
	 */
	private function prepare_record( array $data, bool $is_create ): array {
		$record = array();

		if ( array_key_exists( 'email', $data ) ) {
			$record['email'] = sanitize_email( $data['email'] );
		}

		if ( array_key_exists( 'name', $data ) ) {
			$record['name'] = '' !== trim( (string) $data['name'] ) ? sanitize_text_field( $data['name'] ) : null;
		}

		if ( array_key_exists( 'token', $data ) ) {
			$record['token'] = sanitize_text_field( $data['token'] );
		}

		if ( array_key_exists( 'status', $data ) ) {
			$record['status'] = $this->sanitize_status( (string) $data['status'] );
		}

		if ( array_key_exists( 'expires_at', $data ) ) {
			$record['expires_at'] = sanitize_text_field( $data['expires_at'] );
		}

		if ( array_key_exists( 'user_id', $data ) ) {
			$record['user_id'] = absint( $data['user_id'] ) ?: null;
		}

		if ( array_key_exists( 'custom_message', $data ) ) {
			$record['custom_message'] = '' !== trim( (string) $data['custom_message'] ) ? sanitize_textarea_field( $data['custom_message'] ) : null;
		}

		if ( array_key_exists( 'metadata', $data ) ) {
			$record['metadata'] = $this->prepare_metadata_for_storage( $data['metadata'] );
		}

		if ( array_key_exists( 'invited_by', $data ) ) {
			$record['invited_by'] = absint( $data['invited_by'] );
		}

		if ( array_key_exists( 'accepted_at', $data ) ) {
			$record['accepted_at'] = '' !== trim( (string) $data['accepted_at'] ) ? sanitize_text_field( $data['accepted_at'] ) : null;
		}

		if ( $is_create ) {
			$record['status'] = $record['status'] ?? 'pending';
			$record['created_at'] = current_time( 'mysql' );
			$record['updated_at'] = current_time( 'mysql' );
		}

		return $record;
	}

	/**
	 * Build WHERE clauses for invitation filters.
	 *
	 * @param array<string,mixed> $filters Query filters.
	 * @return array{sql:string,values:array<int,mixed>}
	 */
	private function build_where_clauses( array $filters ): array {
		$where = array();
		$values = array();

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

		if ( ! empty( $filters['email'] ) ) {
			$where[] = 'email = %s';
			$values[] = sanitize_email( $filters['email'] );
		}

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

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

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

		$statuses = $this->normalize_status_filters( $filters['status'] ?? array() );
		if ( ! empty( $statuses ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
			$where[] = "status IN ({$placeholders})";
			foreach ( $statuses as $status ) {
				$values[] = $status;
			}
		}

		if ( ! empty( $filters['search'] ) ) {
			$search = '%' . $this->wpdb->esc_like( sanitize_text_field( $filters['search'] ) ) . '%';
			$where[] = '(email LIKE %s OR name LIKE %s)';
			$values[] = $search;
			$values[] = $search;
		}

		return array(
			'sql'    => ! empty( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '',
			'values' => $values,
		);
	}

	/**
	 * Get wpdb format strings for a record.
	 *
	 * @param array<string,mixed> $record DB record.
	 * @return array<int,string>
	 */
	private function get_formats( array $record ): array {
		$formats = array();

		foreach ( $record as $key => $value ) {
			unset( $value );

			if ( in_array( $key, array( 'id', 'user_id', 'invited_by' ), true ) ) {
				$formats[] = '%d';
				continue;
			}

			$formats[] = '%s';
		}

		return $formats;
	}

	/**
	 * Normalize an invitation row.
	 *
	 * @param array<string,mixed> $row Raw row.
	 * @return array<string,mixed>
	 */
	private function normalize_row( array $row ): array {
		return array(
			'id'             => isset( $row['id'] ) ? (int) $row['id'] : 0,
			'email'          => isset( $row['email'] ) ? (string) $row['email'] : '',
			'name'           => isset( $row['name'] ) ? (string) $row['name'] : '',
			'token'          => isset( $row['token'] ) ? (string) $row['token'] : '',
			'status'         => isset( $row['status'] ) ? (string) $row['status'] : 'pending',
			'expires_at'     => isset( $row['expires_at'] ) ? (string) $row['expires_at'] : '',
			'user_id'        => isset( $row['user_id'] ) ? (int) $row['user_id'] : 0,
			'custom_message' => isset( $row['custom_message'] ) ? (string) $row['custom_message'] : '',
			'metadata'       => $this->normalize_metadata( $row['metadata'] ?? '' ),
			'invited_by'     => isset( $row['invited_by'] ) ? (int) $row['invited_by'] : 0,
			'accepted_at'    => isset( $row['accepted_at'] ) ? (string) $row['accepted_at'] : '',
			'created_at'     => isset( $row['created_at'] ) ? (string) $row['created_at'] : '',
			'updated_at'     => isset( $row['updated_at'] ) ? (string) $row['updated_at'] : '',
		);
	}

	/**
	 * Prepare metadata for storage.
	 *
	 * @param mixed $metadata Metadata value.
	 * @return string|null
	 */
	private function prepare_metadata_for_storage( $metadata ): ?string {
		if ( null === $metadata || '' === $metadata ) {
			return null;
		}

		if ( is_array( $metadata ) ) {
			return wp_json_encode( $metadata );
		}

		return sanitize_textarea_field( (string) $metadata );
	}

	/**
	 * Normalize metadata from storage.
	 *
	 * @param mixed $metadata Stored metadata.
	 * @return array<string,mixed>
	 */
	private function normalize_metadata( $metadata ): array {
		if ( ! is_string( $metadata ) || '' === $metadata ) {
			return array();
		}

		$decoded = json_decode( $metadata, true );

		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Sanitize a single invitation status.
	 *
	 * @param string $status Raw status.
	 * @return string
	 */
	private function sanitize_status( string $status ): string {
		$status = sanitize_key( $status );

		if ( ! in_array( $status, self::ALLOWED_STATUSES, true ) ) {
			return 'pending';
		}

		return $status;
	}

	/**
	 * Normalize one or more invitation statuses.
	 *
	 * @param mixed $statuses Raw statuses.
	 * @return array<int,string>
	 */
	private function normalize_status_filters( $statuses ): array {
		if ( is_string( $statuses ) && '' !== $statuses ) {
			$statuses = array( $statuses );
		}

		if ( ! is_array( $statuses ) ) {
			return array();
		}

		$statuses = array_map( 'sanitize_key', $statuses );
		$statuses = array_values( array_intersect( self::ALLOWED_STATUSES, $statuses ) );
		$statuses = array_values( array_unique( array_filter( $statuses ) ) );

		return $statuses;
	}
}