<?php
/**
 * Form repository.
 *
 * @package FormPilot
 * @since   1.0.0
 */

declare( strict_types = 1 );

namespace FormPilot\Database\Repositories;

use FormPilot\Database\AbstractRepository;
use FormPilot\Database\QueryBuilder;
use FormPilot\Database\Schema;
use FormPilot\Models\Form;
use FormPilot\Uploads\UploadManager;

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

/**
 * Reads and writes forms.
 *
 * All SQL for the forms table lives here; controllers and views only ever see
 * {@see Form} entities.
 *
 * @since 1.0.0
 */
final class FormRepository extends AbstractRepository {

	/**
	 * Columns that may be sorted on.
	 */
	private const SORTABLE = array( 'id', 'title', 'slug', 'status', 'submission_count', 'created_at', 'updated_at' );

	/**
	 * Returns the forms table.
	 */
	protected function table(): string {
		return Schema::table( Schema::FORMS );
	}

	/**
	 * Returns the writable columns and their formats.
	 *
	 * @return array<string, string> Formats keyed by column name.
	 */
	protected function formats(): array {
		return array(
			'title'            => '%s',
			'slug'             => '%s',
			'status'           => '%s',
			'settings'         => '%s',
			'author_id'        => '%d',
			'submission_count' => '%d',
			'created_at'       => '%s',
			'updated_at'       => '%s',
		);
	}

	/**
	 * Finds a form by ID.
	 *
	 * @param int $id Form ID.
	 *
	 * @return Form|null The form, or null when it does not exist.
	 */
	public function find( int $id ): ?Form {
		$row = $this->find_row( $id );

		return null === $row ? null : Form::from_row( $row );
	}

	/**
	 * Finds a form by slug.
	 *
	 * @param string $slug Form slug.
	 *
	 * @return Form|null The form, or null when it does not exist.
	 */
	public function find_by_slug( string $slug ): ?Form {
		$row = $this->find_row_by( 'slug', $slug );

		return null === $row ? null : Form::from_row( $row );
	}

	/**
	 * Returns a page of forms.
	 *
	 * @param array<string, mixed> $args {
	 *     Optional. Query arguments.
	 *
	 *     @type string $search   Term matched against title and slug.
	 *     @type string $status   Restrict to a single status.
	 *     @type int    $author   Restrict to an author.
	 *     @type string $orderby  Column to sort on. Default 'created_at'.
	 *     @type string $order    Sort direction. Default 'DESC'.
	 *     @type int    $per_page Rows per page. Default 20.
	 *     @type int    $page     Page number, one based. Default 1.
	 * }
	 *
	 * @return array<int, Form> The matching forms.
	 */
	public function query( array $args = array() ): array {
		$args = $this->parse_args( $args );

		list( $per_page, $page ) = $this->pagination( $args );

		$query = $this->build_query( $args )
			->order_by( (string) $args['orderby'], (string) $args['order'], self::SORTABLE, 'created_at' )
			->paginate( $per_page, $page );

		return $this->hydrate(
			$this->select( $query ),
			static fn( array $row ): Form => Form::from_row( $row )
		);
	}

	/**
	 * Counts the forms matching a query.
	 *
	 * @param array<string, mixed> $args Same arguments as {@see FormRepository::query()}.
	 */
	public function count( array $args = array() ): int {
		return $this->count_rows( $this->build_query( $this->parse_args( $args ) ) );
	}

	/**
	 * Inserts a form.
	 *
	 * @param array<string, mixed> $data Column data. Unknown keys are ignored.
	 *
	 * @return int The new form ID, or 0 on failure.
	 */
	public function insert( array $data ): int {
		$now = $this->now();

		$data = array_merge(
			array(
				'title'      => '',
				'status'     => Form::STATUS_DRAFT,
				'author_id'  => get_current_user_id(),
				'created_at' => $now,
				'updated_at' => $now,
			),
			$data
		);

		$data['title']    = self::fit_title( (string) $data['title'] );
		$data['settings'] = $this->encode_settings( $data['settings'] ?? array() );
		$data['slug']     = $this->unique_slug( (string) ( $data['slug'] ?? '' ), 0, (string) $data['title'] );

		$form_id = $this->insert_row( $data );

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

		list( $columns ) = $this->prepare_columns( $data );

		/**
		 * Fires after a form has been created.
		 *
		 * @since 1.0.0
		 *
		 * @param int                  $form_id New form ID.
		 * @param array<string, mixed> $columns Stored column data.
		 */
		do_action( 'formpilot_form_created', $form_id, $columns );

		return $form_id;
	}

	/**
	 * Updates a form.
	 *
	 * @param int                  $id   Form ID.
	 * @param array<string, mixed> $data Column data. Unknown keys are ignored.
	 *
	 * @return bool True when the write succeeded.
	 */
	public function update( int $id, array $data ): bool {
		if ( $id <= 0 ) {
			return false;
		}

		$data['updated_at'] = $this->now();

		if ( array_key_exists( 'settings', $data ) ) {
			$data['settings'] = $this->encode_settings( $data['settings'] );
		}

		if ( array_key_exists( 'title', $data ) ) {
			$data['title'] = self::fit_title( (string) $data['title'] );
		}

		if ( array_key_exists( 'slug', $data ) ) {
			$data['slug'] = $this->unique_slug( (string) $data['slug'], $id, (string) ( $data['title'] ?? '' ) );
		}

		unset( $data['created_at'] );

		if ( ! $this->update_row( $id, $data ) ) {
			return false;
		}

		list( $columns ) = $this->prepare_columns( $data );

		/**
		 * Fires after a form has been updated.
		 *
		 * @since 1.0.0
		 *
		 * @param int                  $id      Form ID.
		 * @param array<string, mixed> $columns Stored column data.
		 */
		do_action( 'formpilot_form_updated', $id, $columns );

		return true;
	}

	/**
	 * Deletes a form and everything attached to it.
	 *
	 * The schema has no foreign keys, so the children are removed here to avoid
	 * orphaned fields and submissions.
	 *
	 * @param int $id Form ID.
	 *
	 * @return bool True when the form was removed.
	 */
	public function delete( int $id ): bool {
		if ( $id <= 0 ) {
			return false;
		}

		$fields          = Schema::table( Schema::FIELDS );
		$submissions     = Schema::table( Schema::SUBMISSIONS );
		$submission_meta = Schema::table( Schema::SUBMISSION_META );

		/**
		 * Fires before a form and its children are deleted.
		 *
		 * @since 1.0.0
		 *
		 * @param int $id Form ID.
		 */
		do_action( 'formpilot_before_form_deleted', $id );

		// Read the file list while the rows still exist; unlink only after commit.
		$uploads = $this->upload_payloads( $id );

		/*
		 * All five statements commit together. The schema carries no foreign
		 * keys, so this transaction is the only thing standing between a failed
		 * delete and submissions pointing at a form that no longer exists.
		 */
		$deleted = $this->transaction(
			function () use ( $id, $fields, $submissions, $submission_meta ): bool {
				/*
				 * A join, not `IN ( SELECT … )`. Before MySQL 8.0.21 and MariaDB 11.1 a
				 * single-table DELETE gets no semi-join, so the subquery form ran as a
				 * dependent subquery over every answer of every form - a full scan
				 * (EXPLAIN: type ALL) that, inside this transaction, locked the whole
				 * table against new submissions until the delete committed.
				 */
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names come from the schema, values are prepared.
				$this->db->query( $this->db->prepare( "DELETE m FROM {$submission_meta} m INNER JOIN {$submissions} s ON s.id = m.submission_id WHERE s.form_id = %d", $id ) );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names come from the schema, values are prepared.
				$this->db->query( $this->db->prepare( "DELETE FROM {$submissions} WHERE form_id = %d", $id ) );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names come from the schema, values are prepared.
				$this->db->query( $this->db->prepare( "DELETE FROM {$fields} WHERE form_id = %d", $id ) );

				/*
				 * The delivery log is the fifth table and the one the original
				 * cascade forgot. Its rows carry a full copy of every payload and
				 * double as the retry queue, so leaving them behind keeps the data
				 * a deletion was meant to remove and keeps retrying deliveries for
				 * a form that no longer exists.
				 */
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Child table, outside the CRUD helpers.
				$this->db->delete( Schema::table( Schema::WEBHOOK_LOG ), array( 'form_id' => $id ), array( '%d' ) );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository write.
				return (bool) $this->db->delete( $this->table(), array( 'id' => $id ), array( '%d' ) );
			}
		);

		if ( false === $deleted ) {
			return false;
		}

		/*
		 * Files are removed after the commit: the filesystem cannot roll back, so
		 * unlinking first would destroy attachments for a delete that failed.
		 */
		$manager = new UploadManager();

		foreach ( $uploads as $payload ) {
			$manager->delete( $payload );
		}

		/**
		 * Fires after a form and its children have been deleted.
		 *
		 * @since 1.0.0
		 *
		 * @param int $id Form ID.
		 */
		do_action( 'formpilot_form_deleted', $id );

		return true;
	}

	/**
	 * Returns the upload payloads stored against a form's submissions.
	 *
	 * Read before the cascade runs, because afterwards there is nothing left to
	 * ask. Deleting a form used to leave every uploaded file on disk with the row
	 * pointing at it gone — a silent failure for an erasure request, and one the
	 * orphan cleaner only sometimes swept up later.
	 *
	 * @param int $form_id Form ID.
	 *
	 * @return array<int, array<string, mixed>> Stored upload payloads.
	 */
	private function upload_payloads( int $form_id ): array {
		$submissions     = Schema::table( Schema::SUBMISSIONS );
		$submission_meta = Schema::table( Schema::SUBMISSION_META );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Maintenance scan; table names come from the schema.
		$rows = $this->db->get_col(
			$this->db->prepare(
				"SELECT m.meta_value
				FROM {$submission_meta} m
				INNER JOIN {$submissions} s ON s.id = m.submission_id
				WHERE s.form_id = %d AND m.meta_value LIKE %s",
				$form_id,
				'%' . $this->db->esc_like( '"file"' ) . '%'
			)
		);

		$uploads = array();

		foreach ( is_array( $rows ) ? $rows : array() as $raw ) {
			$decoded = json_decode( (string) $raw, true );

			if ( is_array( $decoded ) && ! empty( $decoded['file'] ) ) {
				$uploads[] = $decoded;
			}
		}

		return $uploads;
	}

	/**
	 * Duplicates a form.
	 *
	 * @param int $id Form ID to copy.
	 *
	 * @return int The new form ID, or 0 on failure.
	 */
	public function duplicate( int $id ): int {
		$source = $this->find( $id );

		if ( ! $source instanceof Form ) {
			return 0;
		}

		$title = sprintf(
			/* translators: %s: Title of the form being copied. */
			__( '%s (copy)', 'devplugins-custom-form-builder' ),
			$source->title()
		);

		$new_id = $this->insert(
			array(
				'title'     => $title,
				'slug'      => $source->slug(),
				'status'    => Form::STATUS_DRAFT,
				'settings'  => $source->settings(),
				'author_id' => get_current_user_id(),
			)
		);

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

		/**
		 * Fires after a form has been duplicated.
		 *
		 * Field copying attaches here once the fields repository exists.
		 *
		 * @since 1.0.0
		 *
		 * @param int $new_id    ID of the copy.
		 * @param int $source_id ID of the original.
		 */
		do_action( 'formpilot_form_duplicated', $new_id, $id );

		return $new_id;
	}

	/**
	 * Raises the cached submission counter of a form.
	 *
	 * Done in SQL rather than read-modify-write so two submissions arriving at
	 * the same moment cannot overwrite each other's increment.
	 *
	 * @param int $id Form ID.
	 *
	 * @return bool True when the counter was raised.
	 */
	public function increment_submission_count( int $id ): bool {
		return $this->adjust_submission_count( $id, 1 );
	}

	/**
	 * Lowers the cached submission counter of a form.
	 *
	 * Called when an entry is deleted. Without it the counter only ever grows
	 * and the Forms screen reports numbers the Entries screen contradicts.
	 *
	 * @param int $id Form ID.
	 *
	 * @return bool True when the counter was lowered.
	 */
	public function decrement_submission_count( int $id ): bool {
		return $this->adjust_submission_count( $id, -1 );
	}

	/**
	 * Recounts the submissions of a form from the submissions table.
	 *
	 * Repairs a counter that drifted, for example after rows were removed
	 * outside the plugin.
	 *
	 * @param int $id Form ID.
	 */
	public function recount_submissions( int $id ): int {
		if ( $id <= 0 ) {
			return 0;
		}

		$table       = $this->table();
		$submissions = Schema::table( Schema::SUBMISSIONS );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names come from the schema, values are prepared.
		$total = (int) $this->db->get_var( $this->db->prepare( "SELECT COUNT(*) FROM {$submissions} WHERE form_id = %d", $id ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- See above.
		$this->db->query( $this->db->prepare( "UPDATE {$table} SET submission_count = %d WHERE id = %d", $total, $id ) );

		return $total;
	}

	/**
	 * Moves the cached submission counter by a delta.
	 *
	 * Done in SQL rather than read-modify-write so two submissions arriving at
	 * the same moment cannot overwrite each other. `GREATEST` keeps the unsigned
	 * column from wrapping to a huge number if the counter ever drifts below the
	 * real total.
	 *
	 * @param int $id    Form ID.
	 * @param int $delta Amount to add, negative to subtract.
	 *
	 * @return bool True when the counter moved.
	 */
	private function adjust_submission_count( int $id, int $delta ): bool {
		if ( $id <= 0 || 0 === $delta ) {
			return false;
		}

		$table = $this->table();

		if ( $delta > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from the schema, values are prepared.
			$updated = $this->db->query(
				$this->db->prepare( "UPDATE {$table} SET submission_count = submission_count + %d WHERE id = %d", $delta, $id )
			);

			return false !== $updated;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- See above.
		$updated = $this->db->query(
			$this->db->prepare(
				"UPDATE {$table} SET submission_count = GREATEST( submission_count, %d ) - %d WHERE id = %d",
				abs( $delta ),
				abs( $delta ),
				$id
			)
		);

		return false !== $updated;
	}

	/**
	 * Determines whether a form exists.
	 *
	 * @param int $id Form ID.
	 *
	 * @return bool True when the row exists.
	 */
	public function exists( int $id ): bool {
		return $this->row_exists( $id );
	}

	/**
	 * Builds a slug that no other form uses.
	 *
	 * @param string $slug     Requested slug.
	 * @param int    $exclude  Optional. Form ID to ignore, for updates. Default 0.
	 * @param string $fallback Optional. Text used when the slug is empty. Default ''.
	 */
	public function unique_slug( string $slug, int $exclude = 0, string $fallback = '' ): string {
		$base = sanitize_title( '' !== $slug ? $slug : $fallback );

		/*
		 * The column is varchar(191), and wpdb refuses a longer value rather than
		 * truncating it, so a long title made the form impossible to create. Room is
		 * left for a `-N` suffix, and a percent-encoded character is never cut in half.
		 */
		$base = trim( (string) preg_replace( '/%[0-9a-f]?$/i', '', substr( $base, 0, 180 ) ), '-' );

		if ( '' === $base ) {
			$base = 'form';
		}

		$candidate = $base;
		$suffix    = 2;

		while ( $this->slug_taken( $candidate, $exclude ) ) {
			$candidate = $base . '-' . $suffix;
			++$suffix;
		}

		return $candidate;
	}

	/**
	 * Determines whether another form already uses a slug.
	 *
	 * @param string $slug    Slug to check.
	 * @param int    $exclude Form ID to ignore, for updates.
	 *
	 * @return bool True when the slug is taken.
	 */
	private function slug_taken( string $slug, int $exclude ): bool {
		$query = $this->query_builder()
			->where_equals( 'slug', $slug )
			->where( 'id != %d', array( max( 0, $exclude ) ) );

		return $this->count_rows( $query ) > 0;
	}

	/**
	 * Applies the query defaults.
	 *
	 * @param array<string, mixed> $args Raw arguments.
	 *
	 * @return array<string, mixed> The normalised arguments.
	 */
	private function parse_args( array $args ): array {
		return array_merge(
			array(
				'search'   => '',
				'status'   => '',
				'author'   => 0,
				'orderby'  => 'created_at',
				'order'    => 'DESC',
				'per_page' => 20,
				'page'     => 1,
			),
			$args
		);
	}

	/**
	 * Describes the filters of a query.
	 *
	 * Shared by {@see FormRepository::query()} and
	 * {@see FormRepository::count()} so a listing and its pagination total can
	 * never disagree about what they are counting.
	 *
	 * @param array<string, mixed> $args Normalised query arguments.
	 *
	 * @return QueryBuilder The builder, without sorting or pagination.
	 */
	private function build_query( array $args ): QueryBuilder {
		return $this->query_builder()
			->search( array( 'title', 'slug' ), $this->search_term( $args['search'] ?? '' ) )
			->where_equals( 'status', (string) ( $args['status'] ?? '' ) )
			->where_equals( 'author_id', (int) ( $args['author'] ?? 0 ), '%d' );
	}

	/**
	 * Encodes the settings payload for storage.
	 *
	 * @param mixed $settings Settings array or already encoded string.
	 */
	private function encode_settings( mixed $settings ): string {
		if ( is_string( $settings ) ) {
			return $settings;
		}

		return (string) wp_json_encode( is_array( $settings ) ? $settings : array() );
	}
	/**
	 * Shortens a title to what its column holds.
	 *
	 * `title` is varchar(255) and wpdb refuses, rather than truncates, a longer
	 * value - so a long title failed the whole save with a generic error.
	 *
	 * @param string $title Title as submitted.
	 */
	private static function fit_title( string $title ): string {
		return mb_substr( $title, 0, 255 );
	}
}