<?php
/**
 * Lead field repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Leads;

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

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

/**
 * Reads and writes lead fields.
 */
class LeadFieldRepository {
	/**
	 * Insert fields for a lead.
	 *
	 * @param int              $lead_id Lead id.
	 * @param array<int,array> $fields Fields.
	 * @return true|\WP_Error
	 */
	public function insert_fields( $lead_id, array $fields ) {
		global $wpdb;

		$lead_id = absint( $lead_id );
		if ( $lead_id <= 0 ) {
			return new \WP_Error(
				'subin_invalid_lead_id',
				__( 'A valid lead is required before fields can be stored.', 'submission-inbox' )
			);
		}

		$table = $wpdb->prefix . 'subin_lead_fields';
		$now   = current_time( 'mysql' );

		foreach ( $fields as $field ) {
			if ( empty( $field['field_key'] ) ) {
				continue;
			}

			$inserted = $wpdb->insert(
				$table,
				array(
					'lead_id'      => $lead_id,
					'field_key'    => sanitize_text_field( $field['field_key'] ),
					'field_label'  => sanitize_text_field( $field['field_label'] ?? $this->label_from_key( $field['field_key'] ) ),
					'field_value'  => sanitize_textarea_field( $field['field_value'] ?? '' ),
					'is_sensitive' => ! empty( $field['is_sensitive'] ) ? 1 : 0,
					'created_at'   => $now,
				),
				array( '%d', '%s', '%s', '%s', '%d', '%s' )
			);

			if ( false === $inserted ) {
				return new \WP_Error(
					'subin_lead_field_insert_failed',
					__( 'One or more submission fields could not be stored.', 'submission-inbox' ),
					array(
						'field_key' => sanitize_key( (string) $field['field_key'] ),
						'db_error'  => sanitize_text_field( (string) $wpdb->last_error ),
					)
				);
			}
		}

		return true;
	}

	/**
	 * Get fields by lead id.
	 *
	 * @param int $lead_id Lead id.
	 * @return array<int,object>
	 */
	public function get_by_lead_id( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_lead_fields';

		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE lead_id = %d ORDER BY id ASC", absint( $lead_id ) ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get fields grouped by lead id.
	 *
	 * @param array<int,int> $lead_ids Lead ids.
	 * @return array<int,array<int,object>>
	 */
	public function get_grouped_by_lead_ids( array $lead_ids ) {
		global $wpdb;

		$lead_ids = array_values( array_filter( array_map( 'absint', $lead_ids ) ) );
		if ( empty( $lead_ids ) ) {
			return array();
		}

		$table        = $wpdb->prefix . 'subin_lead_fields';
		$placeholders = implode( ',', array_fill( 0, count( $lead_ids ), '%d' ) );
		$rows         = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE lead_id IN ({$placeholders}) ORDER BY lead_id ASC, id ASC",
				$lead_ids
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		);

		$grouped = array();
		foreach ( $rows as $row ) {
			$lead_id = absint( $row->lead_id ?? 0 );
			if ( ! isset( $grouped[ $lead_id ] ) ) {
				$grouped[ $lead_id ] = array();
			}

			$grouped[ $lead_id ][] = $row;
		}

		return $grouped;
	}

	/**
	 * Delete fields for a lead.
	 *
	 * @param int $lead_id Lead id.
	 * @return bool
	 */
	public function delete_by_lead_id( $lead_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'subin_lead_fields';

		return false !== $wpdb->delete( $table, array( 'lead_id' => absint( $lead_id ) ), array( '%d' ) );
	}

	/**
	 * Get non-sensitive field summaries for lead ids.
	 *
	 * @param array<int,int> $lead_ids Lead ids.
	 * @return array<int,string>
	 */
	public function get_non_sensitive_summaries_by_lead_ids( array $lead_ids ) {
		global $wpdb;

		$lead_ids = array_values( array_filter( array_map( 'absint', $lead_ids ) ) );
		if ( empty( $lead_ids ) ) {
			return array();
		}

		$table        = $wpdb->prefix . 'subin_lead_fields';
		$placeholders = implode( ',', array_fill( 0, count( $lead_ids ), '%d' ) );
		$rows         = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT lead_id, field_label, field_value FROM {$table} WHERE lead_id IN ({$placeholders}) AND is_sensitive = 0 ORDER BY lead_id ASC, id ASC",
				$lead_ids
			) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		);

		$summaries = array();
		foreach ( $rows as $row ) {
			$lead_id = absint( $row->lead_id );
			if ( ! isset( $summaries[ $lead_id ] ) ) {
				$summaries[ $lead_id ] = array();
			}

			$summaries[ $lead_id ][] = sanitize_text_field( $row->field_label ) . ': ' . sanitize_text_field( $row->field_value );
		}

		foreach ( $summaries as $lead_id => $parts ) {
			$summaries[ $lead_id ] = implode( '; ', $parts );
		}

		return $summaries;
	}

	/**
	 * Generate a display label from a field key.
	 *
	 * @param string $key Field key.
	 * @return string
	 */
	public function label_from_key( $key ) {
		$key = str_replace( array( '-', '_' ), ' ', sanitize_text_field( $key ) );

		return ucwords( trim( $key ) );
	}
}
