<?php
/**
 * Form storage.
 *
 * @package Fillaro
 */

namespace Fillaro\Forms;

use Fillaro\Install;
use Fillaro\Support\Arr;

defined( 'ABSPATH' ) || exit;

/**
 * Reads and writes forms in the custom table.
 */
class FormRepository {

	/**
	 * Per-request cache.
	 *
	 * @var array<int,array>
	 */
	private static $cache = array();

	/**
	 * Find one form.
	 *
	 * @param int $id Form ID.
	 * @return array|null
	 */
	public static function find( $id ) {
		global $wpdb;
		$id = absint( $id );
		if ( ! $id ) {
			return null;
		}
		if ( isset( self::$cache[ $id ] ) ) {
			return self::$cache[ $id ];
		}
		$table = Install::tables()['forms'];
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table, cached per request.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ), ARRAY_A );
		if ( ! $row ) {
			return null;
		}
		self::$cache[ $id ] = self::hydrate( $row );
		return self::$cache[ $id ];
	}

	/**
	 * List forms with entry counts.
	 *
	 * @param array $args search, status, page, per_page.
	 * @return array{items:array,total:int}
	 */
	public static function query( array $args = array() ) {
		global $wpdb;
		$t        = Install::tables();
		$per_page = max( 1, min( 100, isset( $args['per_page'] ) ? (int) $args['per_page'] : 20 ) );
		$page     = max( 1, isset( $args['page'] ) ? (int) $args['page'] : 1 );
		$where    = array( '1=1' );
		$params   = array();

		if ( ! empty( $args['search'] ) ) {
			$where[]  = 'f.title LIKE %s';
			$params[] = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
		}
		if ( ! empty( $args['status'] ) && in_array( $args['status'], array( 'published', 'draft' ), true ) ) {
			$where[]  = 'f.status = %s';
			$params[] = $args['status'];
		}
		// Quick filters. Table names are added to $params in order below.
		$has       = isset( $args['has'] ) ? (string) $args['has'] : '';
		$has_sql   = array(
			'unread'  => "EXISTS (SELECT 1 FROM %i x WHERE x.form_id = f.id AND x.status = 'unread')",
			'entries' => 'EXISTS (SELECT 1 FROM %i x WHERE x.form_id = f.id)',
			'empty'   => 'NOT EXISTS (SELECT 1 FROM %i x WHERE x.form_id = f.id)',
		);
		$has_table = array();
		if ( isset( $has_sql[ $has ] ) ) {
			$where[]     = $has_sql[ $has ];
			$has_table[] = $t['entries'];
		}
		$where_sql = implode( ' AND ', $where );

		$sorts   = array(
			'updated' => 'f.updated_at',
			'created' => 'f.created_at',
			'title'   => 'f.title',
			'entries' => 'entry_count',
			'unread'  => 'unread_count',
			'views'   => 'views',
		);
		$orderby = isset( $args['orderby'], $sorts[ $args['orderby'] ] ) ? $sorts[ $args['orderby'] ] : 'f.updated_at';
		$order   = isset( $args['order'] ) && 'asc' === $args['order'] ? 'ASC' : 'DESC';

		// Only placeholders and fixed SQL are interpolated; every value goes through prepare().
		$count_sql = "SELECT COUNT(*) FROM %i f WHERE {$where_sql}";
		$list_sql  = "SELECT f.*,
				(SELECT COUNT(*) FROM %i e WHERE e.form_id = f.id) AS entry_count,
				(SELECT COUNT(*) FROM %i e WHERE e.form_id = f.id AND e.status = 'unread') AS unread_count,
				(SELECT COALESCE(SUM(s.views),0) FROM %i s WHERE s.form_id = f.id) AS views,
				(SELECT COALESCE(SUM(s.submissions),0) FROM %i s WHERE s.form_id = f.id) AS submissions
			FROM %i f WHERE {$where_sql} ORDER BY {$orderby} {$order}, f.id DESC LIMIT %d OFFSET %d";

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom tables; SQL is fixed text plus placeholders, values go through prepare().
		$params = array_merge( $params, $has_table );
		$total  = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, array_merge( array( $t['forms'] ), $params ) ) );
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				$list_sql,
				array_merge(
					array( $t['entries'], $t['entries'], $t['stats'], $t['stats'], $t['forms'] ),
					$params,
					array( $per_page, ( $page - 1 ) * $per_page )
				)
			),
			ARRAY_A
		);
		// phpcs:enable

		$items = array();
		foreach ( (array) $rows as $row ) {
			$form          = self::hydrate( $row );
			$form['stats'] = array(
				'entries'     => (int) $row['entry_count'],
				'unread'      => (int) $row['unread_count'],
				'views'       => (int) $row['views'],
				'submissions' => (int) $row['submissions'],
			);
			$items[]       = $form;
		}
		return array(
			'items' => $items,
			'total' => $total,
		);
	}

	/**
	 * Forms per status, for filter tabs.
	 *
	 * @return array{all:int,published:int,draft:int}
	 */
	public static function counts() {
		global $wpdb;
		$t = Install::tables();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i GROUP BY status', $t['forms'] ), ARRAY_A );
		$out  = array(
			'all'       => 0,
			'published' => 0,
			'draft'     => 0,
		);
		foreach ( (array) $rows as $row ) {
			if ( isset( $out[ $row['status'] ] ) ) {
				$out[ $row['status'] ] = (int) $row['n'];
			}
			$out['all'] += (int) $row['n'];
		}
		return $out;
	}

	/**
	 * Site-wide totals for the overview cards.
	 *
	 * @return array{entries:int,unread:int,views:int,submissions:int}
	 */
	public static function totals() {
		global $wpdb;
		$t = Install::tables();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery -- Custom tables.
		$entries = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS entries, COALESCE(SUM(status = 'unread'),0) AS unread FROM %i", $t['entries'] ), ARRAY_A );
		$stats   = $wpdb->get_row( $wpdb->prepare( 'SELECT COALESCE(SUM(views),0) AS views, COALESCE(SUM(submissions),0) AS submissions FROM %i', $t['stats'] ), ARRAY_A );
		// phpcs:enable
		return array(
			'entries'     => (int) $entries['entries'],
			'unread'      => (int) $entries['unread'],
			'views'       => (int) $stats['views'],
			'submissions' => (int) $stats['submissions'],
		);
	}

	/**
	 * Create a form.
	 *
	 * @param array $data title, status, fields, settings, theme.
	 * @return int New ID, 0 on failure.
	 */
	public static function create( array $data ) {
		global $wpdb;
		$now   = current_time( 'mysql', true );
		$clean = self::prepare( $data );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table.
		$ok = $wpdb->insert(
			Install::tables()['forms'],
			array_merge(
				$clean,
				array(
					'created_by' => get_current_user_id(),
					'created_at' => $now,
					'updated_at' => $now,
				)
			)
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Update a form. Only keys present in $data change.
	 *
	 * @param int   $id   Form ID.
	 * @param array $data Changes.
	 * @return bool
	 */
	public static function update( $id, array $data ) {
		global $wpdb;
		$current = self::find( $id );
		if ( ! $current ) {
			return false;
		}
		$merged = array_merge(
			$current,
			array_intersect_key( $data, array_flip( array( 'title', 'status', 'fields', 'settings', 'theme' ) ) )
		);
		$clean  = self::prepare( $merged );

		$clean['updated_at'] = current_time( 'mysql', true );
		unset( self::$cache[ (int) $id ] );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return false !== $wpdb->update( Install::tables()['forms'], $clean, array( 'id' => (int) $id ) );
	}

	/**
	 * Delete a form with its entries and stats.
	 *
	 * @param int $id Form ID.
	 * @return bool
	 */
	public static function delete( $id ) {
		global $wpdb;
		$t  = Install::tables();
		$id = (int) $id;
		unset( self::$cache[ $id ] );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery -- Custom tables.
		$wpdb->delete( $t['values'], array( 'form_id' => $id ) );
		$wpdb->delete( $t['entries'], array( 'form_id' => $id ) );
		$wpdb->delete( $t['stats'], array( 'form_id' => $id ) );
		$deleted = $wpdb->delete( $t['forms'], array( 'id' => $id ) );
		// phpcs:enable

		/**
		 * Fires after a form and its data are deleted.
		 *
		 * @param int $id Form ID.
		 */
		do_action( 'fillaro/form_deleted', $id );
		return (bool) $deleted;
	}

	/**
	 * Duplicate a form (definition only, no entries).
	 *
	 * @param int $id Form ID.
	 * @return int New ID, 0 on failure.
	 */
	public static function duplicate( $id ) {
		$form = self::find( $id );
		if ( ! $form ) {
			return 0;
		}
		/* translators: %s: original form title. */
		$form['title']  = sprintf( __( '%s (copy)', 'fillaro' ), $form['title'] );
		$form['status'] = 'draft';
		return self::create( $form );
	}

	/**
	 * Sanitize and encode a form for storage.
	 *
	 * @param array $data Form data.
	 * @return array Column values.
	 */
	private static function prepare( array $data ) {
		$title = isset( $data['title'] ) ? trim( sanitize_text_field( (string) $data['title'] ) ) : '';
		return array(
			'title'    => '' === $title ? __( 'Untitled form', 'fillaro' ) : mb_substr( $title, 0, 255 ),
			'status'   => ( isset( $data['status'] ) && 'draft' === $data['status'] ) ? 'draft' : 'published',
			'fields'   => wp_json_encode( FormSchema::sanitize_fields( isset( $data['fields'] ) ? $data['fields'] : array() ) ),
			'settings' => wp_json_encode( FormSchema::sanitize_settings( isset( $data['settings'] ) ? $data['settings'] : array() ) ),
			'theme'    => wp_json_encode( Theme::sanitize( isset( $data['theme'] ) ? $data['theme'] : array() ) ),
		);
	}

	/**
	 * Decode a row. Stored values were sanitized on write; defaults fill
	 * keys added by newer versions.
	 *
	 * @param array $row DB row.
	 * @return array
	 */
	private static function hydrate( array $row ) {
		$fields   = json_decode( (string) $row['fields'], true );
		$settings = json_decode( (string) $row['settings'], true );
		$theme    = json_decode( (string) $row['theme'], true );

		return array(
			'id'         => (int) $row['id'],
			'title'      => (string) $row['title'],
			'status'     => (string) $row['status'],
			'fields'     => is_array( $fields ) ? $fields : array(),
			'settings'   => Arr::merge_deep( $settings, FormSchema::default_settings() ),
			'theme'      => array_merge( Theme::defaults(), is_array( $theme ) ? $theme : array() ),
			'created_at' => mysql_to_rfc3339( $row['created_at'] ),
			'updated_at' => mysql_to_rfc3339( $row['updated_at'] ),
		);
	}
}