<?php
/**
 * Form folders and tags persistence.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Forms;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This repository owns the form organization tables.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names are built from the trusted WordPress prefix.

/**
 * Stores folders and tags assigned to built-in forms.
 */
class FormOrganizationRepository {
	/**
	 * Return all folders with assigned-form counts.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function get_folders() {
		global $wpdb;

		$folders = $wpdb->prefix . 'subin_form_folders';
		$map     = $wpdb->prefix . 'subin_form_folder_map';
		$rows    = $wpdb->get_results(
			"SELECT f.id, f.name, f.slug, COUNT(m.form_id) AS form_count
			FROM {$folders} f
			LEFT JOIN {$map} m ON m.folder_id = f.id
			GROUP BY f.id, f.name, f.slug
			ORDER BY f.name ASC, f.id ASC",
			ARRAY_A
		);

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

	/**
	 * Return all tags with assigned-form counts.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function get_tags() {
		global $wpdb;

		$tags = $wpdb->prefix . 'subin_form_tags';
		$map  = $wpdb->prefix . 'subin_form_tag_map';
		$rows = $wpdb->get_results(
			"SELECT t.id, t.name, t.slug, COUNT(m.form_id) AS form_count
			FROM {$tags} t
			LEFT JOIN {$map} m ON m.tag_id = t.id
			GROUP BY t.id, t.name, t.slug
			ORDER BY t.name ASC, t.id ASC",
			ARRAY_A
		);

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

	/**
	 * Load folder and tag assignments for many forms without per-form queries.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @return array<int,array{folder:array<string,mixed>|null,tags:array<int,array<string,mixed>>}>
	 */
	public function get_assignments( array $form_ids ) {
		global $wpdb;

		$form_ids = $this->sanitize_ids( $form_ids );
		$result   = array();
		foreach ( $form_ids as $form_id ) {
			$result[ $form_id ] = array(
				'folder' => null,
				'tags'   => array(),
			);
		}

		if ( empty( $form_ids ) ) {
			return $result;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		$folders      = $wpdb->prefix . 'subin_form_folders';
		$folder_map   = $wpdb->prefix . 'subin_form_folder_map';
		$folder_rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT m.form_id, f.id, f.name, f.slug
				FROM {$folder_map} m
				INNER JOIN {$folders} f ON f.id = m.folder_id
				WHERE m.form_id IN ({$placeholders})",
				$form_ids
			),
			ARRAY_A
		);

		foreach ( (array) $folder_rows as $row ) {
			$form_id = absint( $row['form_id'] ?? 0 );
			if ( isset( $result[ $form_id ] ) ) {
				$result[ $form_id ]['folder'] = array(
					'id'   => absint( $row['id'] ?? 0 ),
					'name' => sanitize_text_field( (string) ( $row['name'] ?? '' ) ),
					'slug' => sanitize_title( (string) ( $row['slug'] ?? '' ) ),
				);
			}
		}

		$tags     = $wpdb->prefix . 'subin_form_tags';
		$tag_map  = $wpdb->prefix . 'subin_form_tag_map';
		$tag_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT m.form_id, t.id, t.name, t.slug
				FROM {$tag_map} m
				INNER JOIN {$tags} t ON t.id = m.tag_id
				WHERE m.form_id IN ({$placeholders})
				ORDER BY t.name ASC, t.id ASC",
				$form_ids
			),
			ARRAY_A
		);

		foreach ( (array) $tag_rows as $row ) {
			$form_id = absint( $row['form_id'] ?? 0 );
			if ( isset( $result[ $form_id ] ) ) {
				$result[ $form_id ]['tags'][] = array(
					'id'   => absint( $row['id'] ?? 0 ),
					'name' => sanitize_text_field( (string) ( $row['name'] ?? '' ) ),
					'slug' => sanitize_title( (string) ( $row['slug'] ?? '' ) ),
				);
			}
		}

		return $result;
	}

	/**
	 * Create a folder, returning an existing matching folder when possible.
	 *
	 * @param string $name Folder name.
	 * @return int
	 */
	public function create_folder( $name ) {
		return $this->create_term( 'folder', $name );
	}

	/**
	 * Create a tag, returning an existing matching tag when possible.
	 *
	 * @param string $name Tag name.
	 * @return int
	 */
	public function create_tag( $name ) {
		return $this->create_term( 'tag', $name );
	}

	/**
	 * Move forms into one folder. A zero folder id means Unfiled.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @param int                   $folder_id Folder id.
	 * @return bool
	 */
	public function move_forms( array $form_ids, $folder_id ) {
		global $wpdb;

		$form_ids  = $this->sanitize_ids( $form_ids );
		$folder_id = absint( $folder_id );
		if ( empty( $form_ids ) || ! $this->forms_exist( $form_ids ) ) {
			return false;
		}
		if ( $folder_id > 0 && ! $this->term_exists( 'folder', $folder_id ) ) {
			return false;
		}

		$map          = $wpdb->prefix . 'subin_form_folder_map';
		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		if ( 0 === $folder_id ) {
			return false !== $wpdb->query( $wpdb->prepare( "DELETE FROM {$map} WHERE form_id IN ({$placeholders})", $form_ids ) );
		}

		$forms  = $wpdb->prefix . 'subin_forms';
		$params = array_merge( array( $folder_id, current_time( 'mysql' ) ), $form_ids );
		$query  = $wpdb->prepare(
			"INSERT INTO {$map} (form_id, folder_id, created_at)
			SELECT f.id, %d, %s FROM {$forms} f WHERE f.id IN ({$placeholders})
			ON DUPLICATE KEY UPDATE folder_id = VALUES(folder_id), created_at = VALUES(created_at)",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The query is prepared immediately above with a generated placeholder list.
		return false !== $wpdb->query( $query );
	}

	/**
	 * Add one tag to forms.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @param int                   $tag_id Tag id.
	 * @return bool
	 */
	public function add_tag( array $form_ids, $tag_id ) {
		global $wpdb;

		$form_ids = $this->sanitize_ids( $form_ids );
		$tag_id   = absint( $tag_id );
		if ( empty( $form_ids ) || ! $this->forms_exist( $form_ids ) || ! $this->term_exists( 'tag', $tag_id ) ) {
			return false;
		}

		$map          = $wpdb->prefix . 'subin_form_tag_map';
		$forms        = $wpdb->prefix . 'subin_forms';
		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		$params       = array_merge( array( $tag_id, current_time( 'mysql' ) ), $form_ids );
		$query        = $wpdb->prepare(
			"INSERT INTO {$map} (form_id, tag_id, created_at)
			SELECT f.id, %d, %s FROM {$forms} f WHERE f.id IN ({$placeholders})
			ON DUPLICATE KEY UPDATE tag_id = VALUES(tag_id)",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The query is prepared immediately above with a generated placeholder list.
		return false !== $wpdb->query( $query );
	}

	/**
	 * Remove one tag from forms.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @param int                   $tag_id Tag id.
	 * @return bool
	 */
	public function remove_tag( array $form_ids, $tag_id ) {
		global $wpdb;

		$form_ids = $this->sanitize_ids( $form_ids );
		$tag_id   = absint( $tag_id );
		if ( empty( $form_ids ) || $tag_id <= 0 ) {
			return false;
		}

		$map          = $wpdb->prefix . 'subin_form_tag_map';
		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		$params       = array_merge( array( $tag_id ), $form_ids );
		$query        = $wpdb->prepare( "DELETE FROM {$map} WHERE tag_id = %d AND form_id IN ({$placeholders})", $params );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The query is prepared immediately above with a generated placeholder list.
		return false !== $wpdb->query( $query );
	}

	/**
	 * Copy organization metadata when a form is duplicated.
	 *
	 * @param int $source_form_id Source form id.
	 * @param int $target_form_id Target form id.
	 * @return bool
	 */
	public function copy_assignments( $source_form_id, $target_form_id ) {
		$assignments = $this->get_assignments( array( $source_form_id ) );
		$source      = $assignments[ absint( $source_form_id ) ] ?? array();
		$ok          = true;

		if ( ! empty( $source['folder']['id'] ) ) {
			$ok = $this->move_forms( array( $target_form_id ), absint( $source['folder']['id'] ) );
		}
		foreach ( (array) ( $source['tags'] ?? array() ) as $tag ) {
			$ok = $this->add_tag( array( $target_form_id ), absint( $tag['id'] ?? 0 ) ) && $ok;
		}

		return $ok;
	}

	/**
	 * Delete all organization mappings for forms.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @return bool
	 */
	public function delete_assignments( array $form_ids ) {
		global $wpdb;

		$form_ids = $this->sanitize_ids( $form_ids );
		if ( empty( $form_ids ) ) {
			return true;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		$folder_map   = $wpdb->prefix . 'subin_form_folder_map';
		$tag_map      = $wpdb->prefix . 'subin_form_tag_map';
		$folders_ok   = false !== $wpdb->query( $wpdb->prepare( "DELETE FROM {$folder_map} WHERE form_id IN ({$placeholders})", $form_ids ) );
		$tags_ok      = false !== $wpdb->query( $wpdb->prepare( "DELETE FROM {$tag_map} WHERE form_id IN ({$placeholders})", $form_ids ) );

		return $folders_ok && $tags_ok;
	}

	/**
	 * Create or fetch a folder/tag row.
	 *
	 * @param string $type Term type.
	 * @param string $name Display name.
	 * @return int
	 */
	private function create_term( $type, $name ) {
		global $wpdb;

		$name = sanitize_text_field( (string) $name );
		$slug = sanitize_title( $name );
		if ( '' === $name || '' === $slug ) {
			return 0;
		}

		$table    = 'folder' === $type ? $wpdb->prefix . 'subin_form_folders' : $wpdb->prefix . 'subin_form_tags';
		$existing = absint( $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE slug = %s LIMIT 1", $slug ) ) );
		if ( $existing > 0 ) {
			return $existing;
		}

		$now      = current_time( 'mysql' );
		$inserted = $wpdb->insert(
			$table,
			array(
				'name'       => $name,
				'slug'       => $slug,
				'created_at' => $now,
				'updated_at' => $now,
			),
			array( '%s', '%s', '%s', '%s' )
		);

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

	/**
	 * Check whether an organization term exists.
	 *
	 * @param string $type Term type.
	 * @param int    $term_id Term id.
	 * @return bool
	 */
	private function term_exists( $type, $term_id ) {
		global $wpdb;

		$table = 'folder' === $type ? $wpdb->prefix . 'subin_form_folders' : $wpdb->prefix . 'subin_form_tags';
		return absint( $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE id = %d LIMIT 1", absint( $term_id ) ) ) ) > 0;
	}

	/**
	 * Check that every requested form exists before changing mappings.
	 *
	 * @param array<int,int> $form_ids Sanitized form ids.
	 * @return bool
	 */
	private function forms_exist( array $form_ids ) {
		global $wpdb;

		if ( empty( $form_ids ) ) {
			return false;
		}

		$forms        = $wpdb->prefix . 'subin_forms';
		$placeholders = implode( ', ', array_fill( 0, count( $form_ids ), '%d' ) );
		$found        = absint( $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$forms} WHERE id IN ({$placeholders})", $form_ids ) ) );

		return count( $form_ids ) === $found;
	}

	/**
	 * Normalize a list of ids.
	 *
	 * @param array<int,int|string> $ids Raw ids.
	 * @return array<int,int>
	 */
	private function sanitize_ids( array $ids ) {
		return array_values( array_unique( array_filter( array_map( 'absint', $ids ) ) ) );
	}
}
