<?php
/**
 * Handles custom database table and functionality for co-authors in Author Kit.
 *
 * This file contains logic for creating, managing, and interacting with a custom DB table
 * used to store co-author relationships for posts.
 *
 * @package    Author_Kit
 * @since      1.0.0
 */

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

/**
 * Class to add Custom DB Table for Coauthors and their respective functions.
 *
 * Handles the creation and management of a custom database table for co-authors.
 *
 * @since      1.0.0
 * @package    Author_Kit
 * @subpackage Author_Kit/includes
 */
class Author_Kit_Coauthors {


	/**
	 * Database version for tracking updates.
	 *
	 * @since 1.0.0
	 * @var string
	 */
	private $db_version;

	/**
	 * Database table name.
	 *
	 * @var string
	 */
	private $table_name;

	/**
	 * Constructor: Initializes hooks and actions for database setup.
	 *
	 * @since 1.0.0
	 */
	public function __construct() {
		$this->db_version = AUTHOR_KIT_DB_VERSION;

		global $wpdb;
		$this->table_name = $wpdb->prefix . 'author_kit_coauthors'; // Define the table name once.

		// Hook into WordPress post deletion.
		add_action( 'before_delete_post', array( $this, 'handle_post_deletion' ) );

		// Hook into WordPress user deletion.
		add_action( 'delete_user', array( $this, 'handle_user_deletion' ) );
	}

	/**
	 * Creates the custom database table for co-authors.
	 *
	 * This function ensures that the table is created only if it does not already exist.
	 *
	 * @since 1.0.0
	 */
	public function create_coauthor_table() {
		global $wpdb;
		$table_name      = $this->table_name; // Table name with prefix for compatibility.
		$charset_collate = $wpdb->get_charset_collate(); // Get the default character set and collation.

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
            post_id BIGINT(20) UNSIGNED NOT NULL,              
            user_id BIGINT(20) UNSIGNED NOT NULL,         
            added_by BIGINT(20) UNSIGNED NOT NULL,             
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,     
            updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,   
    
            UNIQUE KEY unique_post_user (post_id, user_id),    
            INDEX idx_post (post_id),                         
            INDEX idx_user (user_id)                           
        ) $charset_collate;";

		// Ensures the table is created/upgraded properly.
		dbDelta( $sql );

		// Store database version in wp_options for tracking.
		add_option( 'author_kit_coauthor_db_version', $this->db_version );
	}

	/**
	 * Drops the custom database table for co-authors.
	 *
	 * This function ensures that the table is Dropped only during plugin deletion and if it already exist.
	 *
	 * @since 1.0.0
	 */
	public function drop_coauthor_table() {
		global $wpdb;
		$table_name = $wpdb->prefix . 'author_kit_coauthors';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Safe schema change in uninstall.php
		$wpdb->query( 'DROP TABLE IF EXISTS `' . esc_sql( $table_name ) . '`' );
	}


	/**
	 * Add a single co-author to a post.
	 *
	 * This function assigns a user as a co-author to a specific post, ensuring no duplicates.
	 * It also notifies the co-author upon successful addition.
	 *
	 * @since 1.0.0
	 * @param int $post_id  The ID of the post.
	 * @param int $user_id  The ID of the user to be added as a co-author.
	 * @param int $added_by The ID of the user who is assigning the co-author.
	 * @return int|false The inserted co-author entry ID on success, or false on failure.
	 */
	public function add_single_coauthor( $post_id, $user_id, $added_by ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name from class property.

		// Validate input to ensure values are positive integers.
		$post_id  = absint( $post_id );
		$user_id  = absint( $user_id );
		$added_by = absint( $added_by );

		if ( ! $post_id || ! $user_id || ! $added_by ) {
			return false; // Invalid input.
		}

		// Check if the co-author relationship already exists.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM `' . esc_sql( $table_name ) . '` WHERE post_id = %d AND user_id = %d',
				$post_id,
				$user_id
			)
		);

		if ( $exists ) {
			return false; // Co-author already assigned.
		}

		// Insert new co-author relationship.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$inserted = $wpdb->insert(
			$table_name,
			array(
				'post_id'  => $post_id,
				'user_id'  => $user_id,
				'added_by' => $added_by,
			),
			array( '%d', '%d', '%d' )
		);

		if ( $inserted ) {
			$this->notify_coauthor( $user_id, $post_id, $added_by ); // Notify the co-author.
			return $wpdb->insert_id;
		}

		return false;
	}


	/**
	 * Add multiple co-authors to a specific post.
	 *
	 * This function takes an array of user IDs and assigns them as co-authors to the given post.
	 * It prevents duplicates and notifies each user upon successful addition.
	 *
	 * @since 1.0.0
	 * @param int   $post_id  The ID of the post.
	 * @param array $user_ids Array of user IDs to be added as co-authors.
	 * @param int   $added_by The ID of the user who is assigning the co-authors.
	 * @return array An array containing added co-authors and failed user IDs.
	 */
	public function add_bulk_coauthors( $post_id, $user_ids, $added_by ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name from class property.

		// Validate input.
		$post_id  = absint( $post_id );
		$added_by = absint( $added_by );
		$user_ids = array_map( 'absint', (array) $user_ids ); // Ensure all user IDs are integers.

		if ( ! $post_id || ! $added_by || empty( $user_ids ) ) {
			return array(
				'added'  => array(),
				'failed' => $user_ids,
			); // Invalid input.
		}

		$added  = array();
		$failed = array();

		foreach ( $user_ids as $user_id ) {
			if ( ! $user_id ) {
				$failed[] = $user_id;
				continue;
			}

			// Check if the co-author relationship already exists.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT id FROM `' . esc_sql( $table_name ) . '` WHERE post_id = %d AND user_id = %d',
					$post_id,
					$user_id
				)
			);

			if ( $exists ) {
				$failed[] = $user_id; // Skip duplicate co-author entries.
				continue;
			}

			// Insert new co-author record.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$inserted = $wpdb->insert(
				$table_name,
				array(
					'post_id'  => $post_id,
					'user_id'  => $user_id,
					'added_by' => $added_by,
				),
				array( '%d', '%d', '%d' )
			);

			if ( $inserted ) {
				$added[] = $user_id;
				$this->notify_coauthor( $user_id, $post_id, $added_by ); // Send notification.
			} else {
				$failed[] = $user_id;
			}
		}

		return array(
			'added'  => $added,
			'failed' => $failed,
		);
	}


	/**
	 * Remove a single co-author from a post.
	 *
	 * @since 1.0.0
	 * @param int $post_id The ID of the post.
	 * @param int $user_id The ID of the co-author to be removed.
	 * @return bool True on success, false on failure.
	 */
	public function remove_single_coauthor( $post_id, $user_id ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate input to ensure values are positive integers.
		$post_id = absint( $post_id );
		$user_id = absint( $user_id );

		if ( ! $post_id || ! $user_id ) {
			return false; // Invalid input.
		}

		// Delete the co-author entry from the table.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->delete(
			$table_name,
			array(
				'post_id' => $post_id,
				'user_id' => $user_id,
			),
			array( '%d', '%d' )
		);

		return false !== $deleted; // Return true if deleted, false otherwise.
	}


	/**
	 * Remove multiple co-authors from a specific post and return deleted/failed user IDs.
	 *
	 * @since      1.0.0
	 * @package    Author_Kit
	 * @subpackage Author_Kit/includes
	 *
	 * @param int   $post_id   The ID of the post.
	 * @param array $user_ids  An array of user IDs to be removed as co-authors.
	 * @return array|false     Associative array with 'deleted' and 'failed' users, or false on failure.
	 */
	public function remove_bulk_coauthors( $post_id, $user_ids ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate input: Ensure $post_id is a valid integer, and $user_ids is a non-empty array.
		$post_id = absint( $post_id );
		if ( ! $post_id || ! is_array( $user_ids ) || empty( $user_ids ) ) {
			return false; // Invalid input.
		}

		$user_ids = array_map( 'absint', $user_ids ); // Ensure all user IDs are integers.

		$deleted_users = array();
		$failed_users  = array();

		// Loop through each user and attempt to delete them individually.
		foreach ( $user_ids as $user_id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->delete(
				$table_name,
				array(
					'post_id' => $post_id,
					'user_id' => $user_id,
				),
				array( '%d', '%d' )
			);

			if ( $result ) {
				$deleted_users[] = $user_id; // Successfully deleted.
			} else {
				$failed_users[] = $user_id; // Failed to delete.
			}
		}

		return array(
			'deleted' => $deleted_users,
			'failed'  => $failed_users,
		);
	}


	/**
	 * Retrieve all co-authors for a given post.
	 *
	 * @since 1.0.0
	 * @param int $post_id The ID of the post.
	 * @return array|false An array of co-author user IDs on success, false on failure.
	 */
	public function get_coauthors_by_post( $post_id ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate input to ensure it's a positive integer.
		$post_id = absint( $post_id );

		if ( ! $post_id ) {
			return false; // Invalid input.
		}

		// Query to retrieve all co-author user IDs for the given post.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$coauthors = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT user_id FROM `' . esc_sql( $table_name ) . '` WHERE post_id = %d',
				$post_id
			)
		);

		return $coauthors ? $coauthors : false; // Return array of user IDs or false if none found.
	}

	/**
	 * Retrieve all post IDs where a given user is a co-author.
	 *
	 * @since 1.0.0
	 * @param int $user_id The ID of the co-author.
	 * @return array|false An array of post IDs on success, false on failure.
	 */
	public function get_posts_by_coauthor( $user_id ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate input to ensure it's a positive integer.
		$user_id = absint( $user_id );

		if ( ! $user_id ) {
			return false; // Invalid input.
		}

		// Query to retrieve all post IDs where the user is a co-author.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$posts = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT post_id FROM `' . esc_sql( $table_name ) . '` WHERE user_id = %d',
				$user_id
			)
		);

		return $posts ? $posts : false; // Return array of post IDs or false if none found.
	}

	/**
	 * Count the number of posts where a given user is a co-author.
	 *
	 * @since 1.0.0
	 * @param int $user_id The ID of the co-author.
	 * @return int The number of posts the user has co-authored, or 0 if none found.
	 */
	public function count_posts_by_coauthor( $user_id ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate input to ensure it's a positive integer.
		$user_id = absint( $user_id );

		if ( ! $user_id ) {
			return 0; // Invalid input, return 0.
		}

		// Query to count the number of posts where the user is a co-author.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM `' . esc_sql( $table_name ) . '` WHERE user_id = %d',
				$user_id
			)
		);

		return (int) $count; // Ensure integer return value.
	}

	/**
	 * Handle post deletion by removing associated co-author entries.
	 *
	 * This function is triggered when a post is deleted. It ensures that
	 * all co-author relationships related to the deleted post are also removed.
	 *
	 * @since 1.0.0
	 * @param int $post_id The ID of the post being deleted.
	 */
	public function handle_post_deletion( $post_id ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate post ID.
		$post_id = absint( $post_id );

		if ( ! $post_id ) {
			return; // Invalid post ID, exit function.
		}

		// Delete all co-author records associated with this post.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( $table_name, array( 'post_id' => $post_id ), array( '%d' ) );
	}

	/**
	 * Handle user deletion by removing co-author entries and reassigning "added_by" records.
	 *
	 * When a user is deleted:
	 * - Co-author relationships involving the user are removed.
	 * - If the user was responsible for adding other co-authors, reassign the "added_by" field to the admin who performed the deletion.
	 *
	 * @since 1.0.0
	 * @param int $user_id The ID of the user being deleted.
	 */
	public function handle_user_deletion( $user_id ) {
		global $wpdb;
		$table_name = $this->table_name; // Custom table name with prefix.

		// Validate user ID.
		$user_id = absint( $user_id );

		if ( ! $user_id ) {
			return; // Invalid user ID, exit function.
		}

		// Get current user ID (who is deleting the user).
		$current_user_id = get_current_user_id();

		// Check if the current user is an administrator.
		if ( user_can( $current_user_id, 'delete_users' ) ) {
			// Update "added_by" field for records added by the deleted user.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$table_name,
				array( 'added_by' => $current_user_id ), // Assign to the admin who deletes the user.
				array( 'added_by' => $user_id ), // Condition: Where added_by is the deleted user.
				array( '%d' ),
				array( '%d' )
			);
		}

		// Delete all co-author records where the user is a co-author.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( $table_name, array( 'user_id' => $user_id ), array( '%d' ) );
	}


	/**
	 * Notify a user when they are added as a co-author to a post.
	 *
	 * Sends an email notification to the co-author with post details.
	 *
	 * @since 1.0.0
	 * @param int $user_id The ID of the co-author being added.
	 * @param int $post_id The ID of the post they are assigned to.
	 * @param int $added_by The ID of the user who added them.
	 */
	public function notify_coauthor( $user_id, $post_id, $added_by ) {
		// Get co-author's email.
		$user = get_userdata( $user_id );
		if ( ! $user ) {
			return false; // User does not exist.
		}
		$email = $user->user_email;

		// Get post details.
		$post = get_post( $post_id );
		if ( ! $post ) {
			return false; // Post does not exist.
		}
		$post_title = $post->post_title;
		$post_url   = get_permalink( $post_id );

		// Get admin/user who added the co-author.
		$added_by_user = get_userdata( $added_by );
		$added_by_name = $added_by_user ? $added_by_user->display_name : __( 'An admin', 'author-kit' );

		// Email subject and message.
		$subject = sprintf(
			// Translators: %s is a post title.
			__( 'You have been added as a co-author to "%s"', 'author-kit' ),
			$post_title
		);

		// Translators: %1$s is the user's display name.
		$greeting = sprintf(
			// Translators: %1$s is the user's display name.
			__( 'Hello %1$s,', 'author-kit' ),
			$user->display_name
		);

		$body = sprintf(
			// Translators: %1$s is the post title, %2$s is the name of the person who added the co-author.
			__( 'You have been added as a co-author to the post "%1$s" by %2$s.', 'author-kit' ),
			$post_title,
			$added_by_name
		);

		$link = sprintf(
			// Translators: %s is the post URL.
			__( 'You can view and contribute to the post here: %s', 'author-kit' ),
			$post_url
		);

		$closing = sprintf(
			// Translators: %s is the blog/site name.
			__( 'Best regards, %s', 'author-kit' ),
			get_bloginfo( 'name' )
		);

		$message = "$greeting\n\n$body\n\n$link\n\n$closing";

		// Email headers.
		$headers = array( 'Content-Type: text/plain; charset=UTF-8' );

		// Send email.
		return wp_mail( $email, $subject, $message, $headers );
	}

	/**
	 * Updates the co-authors for a given post based on the selected users from the meta box.
	 *
	 * This function compares the existing co-authors in the database with the newly selected ones.
	 * It adds new co-authors that are not already assigned and removes those who were deselected.
	 *
	 * @param int   $post_id       The ID of the post.
	 * @param array $selected_users An array of user IDs selected as co-authors.
	 * @param int   $added_by      The ID of the user making the update.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function update_post_coauthors( $post_id, $selected_users, $added_by ) {

		// Validate inputs.
		$post_id  = absint( $post_id );
		$added_by = absint( $added_by );
		if ( ! $post_id || empty( $selected_users ) || ! is_array( $selected_users ) ) {
			return false;
		}

		// Get existing co-authors using the helper function.
		$existing_coauthors = $this->get_coauthors_by_post( $post_id );
		$existing_coauthors = $existing_coauthors ? $existing_coauthors : array();

		// Ensure selected users are unique and sanitized.
		$selected_users = array_map( 'absint', $selected_users );

		// Determine which users to add and remove.
		$users_to_add    = array_diff( $selected_users, $existing_coauthors );
		$users_to_remove = array_diff( $existing_coauthors, $selected_users );

		// Add new co-authors if necessary.
		if ( ! empty( $users_to_add ) ) {
			$this->add_bulk_coauthors( $post_id, $users_to_add, $added_by );
		}

		// Remove co-authors who are no longer assigned.
		if ( ! empty( $users_to_remove ) ) {
			$this->remove_bulk_coauthors( $post_id, $users_to_remove );
		}

		return true;
	}
}

if ( class_exists( 'Author_Kit_Coauthors' ) ) {
	$GLOBALS['author_kit_coauthors'] = new Author_Kit_Coauthors();
}
