<?php
/**
 * Auto-reply template repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\AutoReplies;

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

if ( ! class_exists( AutoReplyPreheader::class ) ) {
	require_once __DIR__ . '/AutoReplyPreheader.php';
}

if ( ! class_exists( AutoReplyDeliverySnapshot::class ) ) {
	require_once __DIR__ . '/AutoReplyDeliverySnapshot.php';
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository methods read/write Submission Inbox custom auto-reply template tables directly; WordPress has no native API for these records.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names are plugin-owned $wpdb->prefix values, and dynamic clauses are built from sanitized allowlists/placeholders in this repository.

/**
 * Reads and writes reusable auto-reply templates.
 */
class AutoReplyTemplateRepository {
	const STATUS_ACTIVE = 'active';
	const STATUS_DRAFT  = 'draft';
	const READINESS_PREVIEW_CHARACTERS = 4096;

	/**
	 * Create a template.
	 *
	 * @param array<string,mixed> $data Template data.
	 * @return int
	 */
	public function create( array $data ) {
		global $wpdb;

		$table = $this->table_name();
		$now   = current_time( 'mysql' );
		$data  = $this->sanitize_data( $data );

		if ( '' === $data['name'] ) {
			return 0;
		}

		$data['created_at'] = $now;
		$data['updated_at'] = $now;

		$inserted = $wpdb->insert(
			$table,
			$data,
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

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

	/**
	 * Update a template.
	 *
	 * @param int                 $template_id Template id.
	 * @param array<string,mixed> $data Template data.
	 * @return bool
	 */
	public function update( $template_id, array $data ) {
		global $wpdb;

		$template_id = absint( $template_id );
		if ( $template_id <= 0 ) {
			return false;
		}

		$data = $this->sanitize_data( $data );
		if ( '' === $data['name'] ) {
			return false;
		}

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

		$updated = $wpdb->update(
			$this->table_name(),
			$data,
			array( 'id' => $template_id ),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Update only fields owned by the saved-design editor.
	 *
	 * Message metadata (subject and preheader), legacy builder metadata, template
	 * provenance, and publication status are intentionally excluded. Existing
	 * legacy shared rules can still inherit their historical message metadata,
	 * while new rules own those values independently.
	 *
	 * @param int                 $template_id Template id.
	 * @param array<string,mixed> $data Editable template content.
	 * @return bool
	 */
	public function update_content( $template_id, array $data ) {
		global $wpdb;

		$template_id = absint( $template_id );
		if ( $template_id <= 0 ) {
			return false;
		}

		$body_source     = $this->string_value( $data['body'] ?? '' );
		$rendered_source = $this->string_value( $data['rendered_html'] ?? '' );
		$body          = $this->sanitize_body( '' !== trim( $body_source ) ? $body_source : $rendered_source );
		$rendered_html = $this->sanitize_body( '' !== trim( $rendered_source ) ? $rendered_source : $body_source );
		$name          = sanitize_text_field( $this->string_value( $data['name'] ?? '' ) );
		if ( '' === $name ) {
			return false;
		}

		$content = array(
			'name'          => $name,
			'body'          => $body,
			'rendered_html' => $rendered_html,
			'updated_at'    => current_time( 'mysql' ),
		);

		$updated = $wpdb->update(
			$this->table_name(),
			$content,
			array( 'id' => $template_id ),
			array( '%s', '%s', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Get a template by id.
	 *
	 * @param int $template_id Template id.
	 * @return array<string,mixed>|null
	 */
	public function get( $template_id ) {
		global $wpdb;

		$template_id = absint( $template_id );
		if ( $template_id <= 0 ) {
			return null;
		}

		$table = $this->table_name();
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $template_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Get only the fields needed when copying a selected design into a rule.
	 *
	 * Body columns are truncated by bytes in SQL before PHP receives them. The
	 * extra byte lets the policy service distinguish an exact-limit value from
	 * an oversized/corrupt row without loading builder project data or CSS.
	 *
	 * @param int $template_id Template id.
	 * @param int $max_body_bytes Maximum supported body bytes.
	 * @return array<string,mixed>|null
	 */
	public function get_design_content( $template_id, $max_body_bytes ) {
		global $wpdb;

		$template_id   = absint( $template_id );
		$bounded_bytes = max( 1, absint( $max_body_bytes ) ) + 1;
		if ( $template_id <= 0 ) {
			return null;
		}

		$table = $this->table_name();
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, template_key, name, status, LEFT(subject, 256) AS subject, LEFT(preheader, 501) AS preheader, LEFT(CAST(body AS BINARY), %d) AS body, LEFT(CAST(rendered_html AS BINARY), %d) AS rendered_html FROM {$table} WHERE id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$bounded_bytes,
				$bounded_bytes,
				$template_id
			),
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Get a bounded set of templates in one query.
	 *
	 * @param array<int,int|string> $template_ids Template ids.
	 * @return array<int,array<string,mixed>> Templates keyed by id.
	 */
	public function get_by_ids( array $template_ids ) {
		global $wpdb;

		$template_ids = array_values( array_unique( array_filter( array_map( 'absint', $template_ids ) ) ) );
		$template_ids = array_slice( $template_ids, 0, 1000 );
		if ( empty( $template_ids ) ) {
			return array();
		}

		$table          = $this->table_name();
		$placeholders   = implode( ', ', array_fill( 0, count( $template_ids ), '%d' ) );
		$effective_body = "CASE WHEN TRIM(COALESCE(rendered_html, '')) <> '' THEN rendered_html ELSE COALESCE(body, '') END";
		$sql            = "SELECT id, template_key, name, status, LEFT(subject, 256) AS subject, LEFT(preheader, 501) AS preheader, '' AS body, CASE WHEN OCTET_LENGTH({$effective_body}) > %d THEN '' ELSE LEFT({$effective_body}, %d) END AS rendered_html, updated_at, OCTET_LENGTH(COALESCE(subject, '')) AS readiness_subject_bytes, OCTET_LENGTH({$effective_body}) AS readiness_body_bytes, 1 AS readiness_projection FROM {$table} WHERE id IN ({$placeholders})";
		$params         = array_merge(
			array( AutoReplyDeliverySnapshot::MAX_BODY_BYTES, self::READINESS_PREVIEW_CHARACTERS ),
			$template_ids
		);
		$rows           = $wpdb->get_results(
			$wpdb->prepare( $sql, $params ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);
		$templates = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$template_id = absint( $row['id'] ?? 0 );
			if ( $template_id > 0 ) {
				$templates[ $template_id ] = $row;
			}
		}

		return $templates;
	}

	/**
	 * Load one effective shared-design body for an exact readiness check.
	 *
	 * Only the selected effective body is returned, capped at one byte beyond the
	 * delivery limit so corrupt legacy LONGTEXT values never enter PHP in full.
	 *
	 * @param int $template_id Template id.
	 * @param int $max_body_bytes Maximum supported body bytes.
	 * @return array<string,mixed>|null
	 */
	public function get_readiness_content( $template_id, $max_body_bytes ) {
		global $wpdb;

		$template_id   = absint( $template_id );
		$bounded_bytes = max( 1, absint( $max_body_bytes ) ) + 1;
		if ( $template_id <= 0 ) {
			return null;
		}

		$table          = $this->table_name();
		$effective_body = "CASE WHEN TRIM(COALESCE(rendered_html, '')) <> '' THEN rendered_html ELSE COALESCE(body, '') END";
		$row            = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, '' AS body, LEFT(CAST({$effective_body} AS BINARY), %d) AS rendered_html FROM {$table} WHERE id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$bounded_bytes,
				$template_id
			),
			ARRAY_A
		);

		return is_array( $row ) ? $row : null;
	}

	/**
	 * Get paginated templates.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,array<string,mixed>>
	 */
	public function get_paginated( array $args = array() ) {
		global $wpdb;

		$table   = $this->table_name();
		$where   = $this->build_where( $args );
		$limit   = max( 1, absint( $args['per_page'] ?? 20 ) );
		$offset  = max( 0, absint( $args['offset'] ?? 0 ) );
		$sql     = "SELECT * FROM {$table} {$where['sql']} ORDER BY updated_at DESC, id DESC LIMIT %d OFFSET %d";
		$params  = array_merge( $where['params'], array( $limit, $offset ) );
		$results = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

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

	/**
	 * Get bounded template metadata for selection controls without loading bodies.
	 *
	 * Template HTML may be as large as the delivery limit, so picker bootstrap
	 * requests must not SELECT it for every saved design. Full content is loaded
	 * separately only after an administrator chooses one design.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,array<string,mixed>>
	 */
	public function get_choice_metadata( array $args = array() ) {
		global $wpdb;

		$table   = $this->table_name();
		$where   = $this->build_where( $args );
		$limit   = min( 500, max( 1, absint( $args['per_page'] ?? 100 ) ) );
		$offset  = max( 0, absint( $args['offset'] ?? 0 ) );
		$sql     = "SELECT id, template_key, name, status FROM {$table} {$where['sql']} ORDER BY updated_at DESC, id DESC LIMIT %d OFFSET %d";
		$params  = array_merge( $where['params'], array( $limit, $offset ) );
		$results = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

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

	/**
	 * Count templates.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @return int
	 */
	public function count( array $filters = array() ) {
		global $wpdb;

		$table    = $this->table_name();
		$where    = $this->build_where( $filters );
		$sql      = "SELECT COUNT(*) FROM {$table} {$where['sql']}";

		if ( ! empty( $where['params'] ) ) {
			return absint( $wpdb->get_var( $wpdb->prepare( $sql, $where['params'] ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		return absint( $wpdb->get_var( $sql ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Duplicate a template.
	 *
	 * @param int $template_id Template id.
	 * @return int
	 */
	public function duplicate( $template_id ) {
		$template = $this->get( $template_id );
		if ( ! $template ) {
			return 0;
		}

		$template['name']   = sprintf(
			/* translators: %s: Original template name. */
			__( '%s Copy', 'submission-inbox' ),
			$template['name']
		);
		$template['status'] = self::STATUS_ACTIVE;

		return $this->create( $template );
	}

	/**
	 * Delete a template.
	 *
	 * @param int $template_id Template id.
	 * @return bool
	 */
	public function delete( $template_id ) {
		global $wpdb;

		$template_id = absint( $template_id );
		if ( $template_id <= 0 ) {
			return false;
		}

		return false !== $wpdb->delete( $this->table_name(), array( 'id' => $template_id ), array( '%d' ) );
	}

	/**
	 * Get supported statuses.
	 *
	 * @return array<int,string>
	 */
	public function get_statuses() {
		return array( self::STATUS_ACTIVE, self::STATUS_DRAFT );
	}

	/**
	 * Sanitize status.
	 *
	 * @param string $status Status.
	 * @return string
	 */
	public function sanitize_status( $status ) {
		$status = sanitize_key( $status );

		return in_array( $status, $this->get_statuses(), true ) ? $status : self::STATUS_ACTIVE;
	}

	/**
	 * Sanitize template data.
	 *
	 * @param array<string,mixed> $data Raw data.
	 * @return array<string,mixed>
	 */
	private function sanitize_data( array $data ) {
		$body_source     = $this->string_value( $data['body'] ?? '' );
		$rendered_source = $this->string_value( $data['rendered_html'] ?? '' );

		return array(
			'template_key'  => sanitize_key( $this->string_value( $data['template_key'] ?? '' ) ),
			'name'          => sanitize_text_field( $this->string_value( $data['name'] ?? '' ) ),
			'status'        => $this->sanitize_status( $this->string_value( $data['status'] ?? self::STATUS_ACTIVE ) ),
			'subject'       => sanitize_text_field( $this->string_value( $data['subject'] ?? '' ) ),
			'body'          => $this->sanitize_body( '' !== trim( $body_source ) ? $body_source : $rendered_source ),
			'preheader'     => AutoReplyPreheader::normalize_text( sanitize_textarea_field( $this->string_value( $data['preheader'] ?? '' ) ) ),
			'project_json'  => $this->sanitize_project_json( $data['project_json'] ?? '' ),
			'rendered_html' => $this->sanitize_body( '' !== trim( $rendered_source ) ? $rendered_source : $body_source ),
			'rendered_css'  => $this->sanitize_css( $data['rendered_css'] ?? '' ),
		);
	}

	/**
	 * Sanitize email body HTML.
	 *
	 * @param mixed $body Raw body.
	 * @return string
	 */
	private function sanitize_body( $body ) {
		$body = is_string( $body ) ? $body : '';
		$body = preg_replace( '/<\?(?:php)?[\s\S]*?\?>/i', '', $body );
		$body = strip_shortcodes( (string) $body );

		$body = wp_kses( $body, self::allowed_email_html(), array( 'http', 'https', 'mailto', 'tel' ) );

		return self::sanitize_svg_paint_attributes( $body );
	}

	/**
	 * Keep SVG paint values constrained to non-referencing color values.
	 *
	 * @param string $html Sanitized HTML.
	 * @return string
	 */
	public static function sanitize_svg_paint_attributes( $html ) {
		return (string) preg_replace_callback(
			'/\s(fill|stroke)=(["\'])(.*?)\2/i',
			function ( $matches ) {
				$value = trim( (string) $matches[3] );
				if ( preg_match( '/^(none|currentColor|#[0-9a-f]{3,8})$/i', $value ) ) {
					return ' ' . strtolower( $matches[1] ) . '=' . $matches[2] . $value . $matches[2];
				}
				return ' ' . strtolower( $matches[1] ) . '=' . $matches[2] . 'none' . $matches[2];
			},
			$html
		);
	}

	/**
	 * Sanitize rendered CSS.
	 *
	 * @param mixed $css Raw CSS.
	 * @return string
	 */
	private function sanitize_css( $css ) {
		$css = is_string( $css ) ? $css : '';
		$css = preg_replace( '/<\?(?:php)?[\s\S]*?\?>/i', '', $css );
		$css = strip_shortcodes( (string) $css );
		$css = wp_strip_all_tags( $css );
		$css = preg_replace( '/\/\*[\s\S]*?\*\//', '', $css );
		$css = preg_replace( '/@import[^;]+;/i', '', $css );
		$css = preg_replace_callback(
			'/url\s*\(\s*([\'"]?)(.*?)\1\s*\)/i',
			function ( $matches ) {
				$url = esc_url_raw( $matches[2], array( 'http', 'https' ) );
				if ( '' === $url ) {
					return '';
				}
				return 'url("' . $url . '")';
			},
			$css
		);
		$css = preg_replace( '/expression\s*\([^)]*\)/i', '', $css );
		$css = preg_replace( '/(?:behavior|-moz-binding)\s*:[^;}]+[;}]?/i', '', $css );
		$css = preg_replace( '/(?:javascript|data)\s*:/i', '', $css );

		return trim( (string) $css );
	}

	/**
	 * Sanitize GrapesJS project JSON.
	 *
	 * @param mixed $project_json Raw JSON.
	 * @return string
	 */
	private function sanitize_project_json( $project_json ) {
		$project_json = is_string( $project_json ) ? $project_json : '';
		if ( '' === $project_json ) {
			return '';
		}

		$decoded = json_decode( $project_json, true );
		if ( ! is_array( $decoded ) ) {
			return '';
		}

		$encoded = wp_json_encode( $this->sanitize_project_value( $decoded ) );

		return is_string( $encoded ) ? $encoded : '';
	}

	/**
	 * Sanitize GrapesJS project values recursively.
	 *
	 * @param mixed $value Raw value.
	 * @return mixed
	 */
	private function sanitize_project_value( $value ) {
		if ( is_array( $value ) ) {
			$clean = array();
			foreach ( $value as $key => $item ) {
				$clean_key           = is_int( $key ) ? $key : sanitize_key( (string) $key );
				$clean[ $clean_key ] = $this->sanitize_project_value( $item );
			}
			return $clean;
		}

		if ( is_bool( $value ) || is_int( $value ) || is_float( $value ) || null === $value ) {
			return $value;
		}

		$value = $this->string_value( $value );
		$value = preg_replace( '/<\?(?:php)?[\s\S]*?\?>/i', '', $value );
		$value = strip_shortcodes( (string) $value );

		if ( false !== strpos( $value, '<' ) ) {
			return $this->sanitize_body( $value );
		}

		if ( preg_match( '/(?:javascript|data)\s*:|on[a-z]+\s*=/i', $value ) ) {
			return '';
		}

		return sanitize_text_field( $value );
	}

	/**
	 * Get a scalar string value.
	 *
	 * @param mixed $value Raw value.
	 * @return string
	 */
	private function string_value( $value ) {
		return is_scalar( $value ) ? (string) $value : '';
	}

	/**
	 * Allowed email body HTML.
	 *
	 * @return array<string,array<string,array<int,string>|bool|string>>
	 */
	public static function allowed_email_html() {
		$global_attrs = array(
			'align'                    => true,
			'aria-hidden'              => true,
			'class'                    => true,
			'data-subin-auto-reply-preheader' => true,
			'data-subin-component'       => true,
			'data-subin-email-component' => true,
			'data-subin-icon-align'      => true,
			'data-subin-icon-bg'         => true,
			'data-subin-icon-color'      => true,
			'data-subin-icon-key'        => true,
			'data-subin-icon-label'      => true,
			'data-subin-icon-radius'     => true,
			'data-subin-icon-size'       => true,
			'style'                    => true,
		);

		return array(
			'a'          => array_merge(
				$global_attrs,
				array(
					'href'   => true,
					'title'  => true,
					'target' => true,
					'rel'    => true,
				)
			),
			'blockquote' => $global_attrs,
			'b'          => array(),
			'body'       => $global_attrs,
			'br'         => array(),
			'div'        => $global_attrs,
			'em'         => array(),
			'h1'         => $global_attrs,
			'h2'         => $global_attrs,
			'h3'         => $global_attrs,
			'head'       => array(),
			'html'       => array(),
			'hr'         => array(),
			'i'          => array(),
			'img'        => array_merge(
				$global_attrs,
				array(
					'alt'    => true,
					'height' => true,
					'src'    => true,
					'title'  => true,
					'width'  => true,
				)
			),
			'li'         => $global_attrs,
			'meta'       => array(
				'charset' => true,
				'content' => true,
				'name'    => true,
			),
			'ol'         => $global_attrs,
			'p'          => $global_attrs,
			'small'      => $global_attrs,
			'span'       => $global_attrs,
			'svg'        => array(
				'aria-hidden'      => true,
				'class'            => true,
				'fill'             => true,
				'focusable'        => true,
				'height'           => true,
				'role'             => true,
				'stroke'           => true,
				'stroke-linecap'   => true,
				'stroke-linejoin'  => true,
				'stroke-width'     => true,
				'viewbox'          => true,
				'viewBox'          => true,
				'width'            => true,
				'xmlns'            => true,
			),
			'path'       => array(
				'd' => true,
			),
			'circle'     => array(
				'cx' => true,
				'cy' => true,
				'r'  => true,
			),
			'line'       => array(
				'x1' => true,
				'x2' => true,
				'y1' => true,
				'y2' => true,
			),
			'rect'       => array(
				'height' => true,
				'rx'     => true,
				'ry'     => true,
				'width'  => true,
				'x'      => true,
				'y'      => true,
			),
			'polyline'   => array(
				'points' => true,
			),
			'polygon'    => array(
				'points' => true,
			),
			'strong'     => array(),
			'table'      => array_merge(
				$global_attrs,
				array(
					'align'       => true,
					'cellpadding' => true,
					'cellspacing' => true,
					'role'        => true,
					'width'       => true,
				)
			),
			'tbody'      => $global_attrs,
			'td'         => array_merge(
				$global_attrs,
				array(
					'align'   => true,
					'colspan' => true,
					'rowspan' => true,
					'valign'  => true,
					'width'   => true,
				)
			),
			'tr'         => $global_attrs,
			'ul'         => $global_attrs,
		);
	}

	/**
	 * Resolve legacy/body HTML when a visual-design snapshot is blank.
	 *
	 * @param array<string,mixed>|null $template Template row.
	 * @return string
	 */
	public static function effective_body( $template ) {
		if ( ! is_array( $template ) ) {
			return '';
		}
		$rendered = is_scalar( $template['rendered_html'] ?? null ) ? (string) $template['rendered_html'] : '';

		return '' !== trim( $rendered ) ? $rendered : ( is_scalar( $template['body'] ?? null ) ? (string) $template['body'] : '' );
	}

	/**
	 * Build SQL where conditions.
	 *
	 * @param array<string,mixed> $filters Filters.
	 * @return array{sql:string,params:array<int,mixed>}
	 */
	private function build_where( array $filters ) {
		global $wpdb;

		$where  = array();
		$params = array();

		if ( ! empty( $filters['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = $this->sanitize_status( (string) $filters['status'] );
		}
		$search = sanitize_text_field( (string) ( $filters['search'] ?? '' ) );
		if ( '' !== $search ) {
			$like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where[]  = 'name LIKE %s';
			$params[] = $like;
		}

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

	/**
	 * Get template table name.
	 *
	 * @return string
	 */
	private function table_name() {
		$tables = \SUBIN_Database::get_table_names();

		return $tables['auto_reply_templates'];
	}
}
