<?php
defined( 'ABSPATH' ) || exit;

class Formulate_Pricing_Entry {

	public static function create( $form_id, $entry_data, $extra = array() ) {
		global $wpdb;
		$table = Formulate_Pricing_Database::get_entries_table();

		$data = array(
			'form_id'         => intval( $form_id ),
			'entry_data'      => wp_json_encode( $entry_data ),
			'status'          => isset( $extra['status'] ) ? sanitize_text_field( $extra['status'] ) : 'unread',
			'payment_status'  => isset( $extra['payment_status'] ) ? sanitize_text_field( $extra['payment_status'] ) : '',
			'user_agent'      => sanitize_text_field( wp_unslash( isset( $_SERVER['HTTP_USER_AGENT'] ) ? $_SERVER['HTTP_USER_AGENT'] : '' ) ),
			'created_at'      => current_time( 'mysql' ),
			'wc_order_id'     => ! empty( $extra['wc_order_id'] ) ? intval( $extra['wc_order_id'] ) : null,
			'booking_post_id' => null,
		);

		$inserted = $wpdb->insert( $table, $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		return $inserted ? $wpdb->insert_id : false;
	}

	public static function get( $id ) {
		global $wpdb;
		$table = Formulate_Pricing_Database::get_entries_table();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
		$entry = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `$table` WHERE id = %d", intval( $id ) ) );
		if ( $entry ) {
			$entry->entry_data = json_decode( $entry->entry_data, true );
		}
		return $entry;
	}

	public static function get_all( $args = array() ) {
		global $wpdb;
		$table = Formulate_Pricing_Database::get_entries_table();

		$defaults = array(
			'form_id'  => 0,
			'status'   => '',
			'search'   => '',
			'per_page' => 20,
			'page'     => 1,
			'orderby'  => 'created_at',
			'order'    => 'DESC',
		);
		$args = wp_parse_args( $args, $defaults );

		$where  = '1=1';
		$values = array();

		if ( $args['form_id'] ) {
			$where   .= ' AND form_id = %d';
			$values[] = intval( $args['form_id'] );
		}

		if ( $args['status'] ) {
			$where   .= ' AND status = %s';
			$values[] = $args['status'];
		}

		if ( $args['search'] ) {
			$where   .= ' AND entry_data LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		$offset  = ( max( 1, intval( $args['page'] ) ) - 1 ) * intval( $args['per_page'] );
		$allowed_order   = array( 'ASC', 'DESC' );
		$order   = in_array( strtoupper( $args['order'] ), $allowed_order, true ) ? strtoupper( $args['order'] ) : 'DESC';
		$allowed_orderby = array( 'id', 'form_id', 'status', 'created_at' );
		$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';

		$values[] = intval( $args['per_page'] );
		$values[] = $offset;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$entries = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM `$table` WHERE $where ORDER BY $orderby $order LIMIT %d OFFSET %d", ...$values ) );
		foreach ( $entries as &$entry ) {
			$entry->entry_data = json_decode( $entry->entry_data, true );
		}
		return $entries;
	}

	public static function count( $args = array() ) {
		global $wpdb;
		$table  = Formulate_Pricing_Database::get_entries_table();
		$where  = '1=1';
		$values = array();

		if ( ! empty( $args['form_id'] ) ) {
			$where   .= ' AND form_id = %d';
			$values[] = intval( $args['form_id'] );
		}

		if ( ! empty( $args['status'] ) ) {
			$where   .= ' AND status = %s';
			$values[] = $args['status'];
		}

		if ( ! empty( $args['search'] ) ) {
			$where   .= ' AND entry_data LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		if ( $values ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `$table` WHERE $where", ...$values ) );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$table` WHERE $where" );
	}

	public static function update_status( $id, $status ) {
		global $wpdb;
		$table   = Formulate_Pricing_Database::get_entries_table();
		$allowed = array( 'read', 'unread', 'starred', 'trash' );
		if ( ! in_array( $status, $allowed, true ) ) {
			return false;
		}
		return $wpdb->update( $table, array( 'status' => $status ), array( 'id' => intval( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Map WooCommerce order status to entry payment_status.
	 *
	 * @param string $wc_status WooCommerce order status slug (without wc- prefix).
	 * @return string
	 */
	public static function payment_status_from_wc( $wc_status ) {
		$wc_status = str_replace( 'wc-', '', (string) $wc_status );
		$map       = array(
			'pending'    => 'pending',
			'on-hold'    => 'on-hold',
			'processing' => 'processing',
			'completed'  => 'paid',
			'cancelled'  => 'cancelled',
			'failed'     => 'failed',
			'refunded'   => 'refunded',
		);

		return isset( $map[ $wc_status ] ) ? $map[ $wc_status ] : 'pending';
	}

	/**
	 * Human label + badge color for payment status.
	 *
	 * @param string $payment_status Entry payment status.
	 * @return array{label: string, color: string}
	 */
	public static function get_payment_status_display( $payment_status ) {
		$labels = array(
			''           => array( __( '—', 'formulate-pricing' ), '#9ca3af' ),
			'none'       => array( __( 'N/A', 'formulate-pricing' ), '#9ca3af' ),
			'pending'    => array( __( 'Pending payment', 'formulate-pricing' ), '#f59e0b' ),
			'on-hold'    => array( __( 'On hold', 'formulate-pricing' ), '#f59e0b' ),
			'processing' => array( __( 'Processing', 'formulate-pricing' ), '#3b82f6' ),
			'paid'       => array( __( 'Paid', 'formulate-pricing' ), '#10b981' ),
			'cancelled'  => array( __( 'Cancelled', 'formulate-pricing' ), '#ef4444' ),
			'failed'     => array( __( 'Failed', 'formulate-pricing' ), '#ef4444' ),
			'refunded'   => array( __( 'Refunded', 'formulate-pricing' ), '#6b7280' ),
		);

		$info = isset( $labels[ $payment_status ] ) ? $labels[ $payment_status ] : array( ucfirst( $payment_status ), '#9ca3af' );

		return array(
			'label' => $info[0],
			'color' => $info[1],
		);
	}

	/**
	 * Link a WooCommerce order to an entry.
	 *
	 * @param int $entry_id Entry ID.
	 * @param int $order_id Order ID.
	 * @return bool|int
	 */
	public static function link_wc_order( $entry_id, $order_id ) {
		global $wpdb;
		$table = Formulate_Pricing_Database::get_entries_table();

		return $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$table,
			array( 'wc_order_id' => intval( $order_id ) ),
			array( 'id' => intval( $entry_id ) )
		);
	}

	/**
	 * Update entry payment status (WooCommerce checkout flow).
	 *
	 * @param int    $entry_id       Entry ID.
	 * @param string $payment_status Payment status slug.
	 * @return bool|int
	 */
	public static function update_payment_status( $entry_id, $payment_status ) {
		global $wpdb;
		$table   = Formulate_Pricing_Database::get_entries_table();
		$allowed = array( '', 'none', 'pending', 'on-hold', 'processing', 'paid', 'cancelled', 'failed', 'refunded' );

		if ( ! in_array( $payment_status, $allowed, true ) ) {
			return false;
		}

		return $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$table,
			array( 'payment_status' => $payment_status ),
			array( 'id' => intval( $entry_id ) )
		);
	}

	/**
	 * Sync all entries on an order from its WooCommerce status.
	 *
	 * @param int $order_id Order ID.
	 */
	public static function sync_payment_from_order( $order_id ) {
		$order = wc_get_order( $order_id );
		if ( ! $order ) {
			return;
		}

		$payment_status = self::payment_status_from_wc( $order->get_status() );
		$entry_ids      = array();

		foreach ( $order->get_items() as $item ) {
			$entry_id = (int) $item->get_meta( '_formulate_pricing_entry_id' );
			if ( $entry_id > 0 ) {
				$entry_ids[] = $entry_id;
			}
		}

		$entry_ids = array_unique( $entry_ids );

		foreach ( $entry_ids as $entry_id ) {
			self::link_wc_order( $entry_id, $order_id );
			self::update_payment_status( $entry_id, $payment_status );
		}

		if ( ! empty( $entry_ids ) ) {
			$primary = (string) $entry_ids[0];
			$order->update_meta_data( '_formulate_pricing_entry_id', $primary );
			$order->update_meta_data( 'Entry ID', '#' . $primary );
			$order->save();
		}
	}

	/**
	 * Up to N columns for the entries list (form config labels, not raw field IDs).
	 *
	 * @param int $form_id     Form ID.
	 * @param int $max_columns Max columns.
	 * @return array<int, array{id: string, label: string, field: array}>
	 */
	public static function get_list_columns( $form_id, $max_columns = 2 ) {
		$form = Formulate_Pricing_Form::get( $form_id );
		if ( ! $form ) {
			return array();
		}

		$layout          = Formulate_Pricing_Form::layout_field_types();
		$preferred_types = array( 'email', 'text', 'name', 'tel', 'textarea', 'select', 'date', 'time', 'number' );
		$candidates      = array();

		foreach ( Formulate_Pricing_Form::get_all_fields( $form->form_config ) as $field ) {
			$type = isset( $field['type'] ) ? $field['type'] : 'text';
			if ( in_array( $type, $layout, true ) ) {
				continue;
			}
			$candidates[] = $field;
		}

		usort(
			$candidates,
			function ( $a, $b ) use ( $preferred_types ) {
				$ta = array_search( isset( $a['type'] ) ? $a['type'] : 'text', $preferred_types, true );
				$tb = array_search( isset( $b['type'] ) ? $b['type'] : 'text', $preferred_types, true );
				$ta = false === $ta ? 99 : $ta;
				$tb = false === $tb ? 99 : $tb;
				return $ta - $tb;
			}
		);

		$columns = array();
		foreach ( array_slice( $candidates, 0, $max_columns ) as $field ) {
			$fid = isset( $field['id'] ) ? $field['id'] : '';
			if ( ! $fid ) {
				continue;
			}
			$columns[] = array(
				'id'    => $fid,
				'label' => isset( $field['label'] ) && '' !== $field['label'] ? $field['label'] : __( 'Field', 'formulate-pricing' ),
				'field' => $field,
			);
		}

		return $columns;
	}

	/**
	 * Resolve a human field label (never expose internal field IDs in the UI).
	 *
	 * @param array       $entry_data Entry data.
	 * @param string      $field_id   Field ID.
	 * @param array|null  $field      Optional field config.
	 * @return string
	 */
	public static function get_field_label( $entry_data, $field_id, $field = null ) {
		if ( isset( $entry_data[ '_label_' . $field_id ] ) && '' !== $entry_data[ '_label_' . $field_id ] ) {
			return $entry_data[ '_label_' . $field_id ];
		}
		if ( $field && ! empty( $field['label'] ) ) {
			return $field['label'];
		}

		return __( 'Field', 'formulate-pricing' );
	}

	/**
	 * Format one field value for display.
	 *
	 * @param array      $entry_data Entry data.
	 * @param string     $field_id   Field ID.
	 * @param array|null $field      Optional field config.
	 * @return string
	 */
	public static function format_field_value( $entry_data, $field_id, $field = null ) {
		if ( ! isset( $entry_data[ $field_id ] ) ) {
			return '';
		}

		$value = $entry_data[ $field_id ];
		if ( $field && class_exists( 'Formulate_Pricing_Form' ) ) {
			return Formulate_Pricing_Form::format_field_display_value( $field, $value );
		}
		if ( is_array( $value ) ) {
			return implode( ', ', $value );
		}

		return (string) $value;
	}

	/**
	 * Short one-line summary for the entries table (all-forms view).
	 *
	 * @param int   $form_id    Form ID.
	 * @param array $entry_data Entry data.
	 * @param int   $max_parts  Max label:value pairs.
	 * @return string
	 */
	public static function build_entry_summary( $form_id, $entry_data, $max_parts = 2 ) {
		if ( ! is_array( $entry_data ) ) {
			return '';
		}

		$parts = array();

		if ( $form_id && class_exists( 'Formulate_Pricing_Form' ) ) {
			$lines = Formulate_Pricing_Form::get_cart_display_items( $form_id, $entry_data );
			foreach ( array_slice( $lines, 0, $max_parts ) as $line ) {
				$parts[] = $line['key'] . ': ' . $line['value'];
			}
			return implode( ' · ', $parts );
		}

		foreach ( $entry_data as $key => $val ) {
			if ( 0 === strpos( $key, '_label_' ) || 0 === strpos( $key, '_' ) ) {
				continue;
			}
			if ( '' === $val || null === $val || array() === $val ) {
				continue;
			}
			$label = self::get_field_label( $entry_data, $key );
			if ( is_array( $val ) ) {
				$val = implode( ', ', $val );
			}
			$parts[] = $label . ': ' . $val;
			if ( count( $parts ) >= $max_parts ) {
				break;
			}
		}

		return implode( ' · ', $parts );
	}

	/**
	 * Format entry for admin JSON (includes order link).
	 *
	 * @param object $entry Entry row object.
	 * @return object
	 */
	public static function format_for_admin( $entry ) {
		if ( ! $entry ) {
			return $entry;
		}

		$pay                              = self::get_payment_status_display( $entry->payment_status ?? '' );
		$entry->payment_status_label      = $pay['label'];
		$entry->payment_status_color      = $pay['color'];
		$entry->wc_order_url              = '';
		$entry->wc_order_number           = '';

		if ( ! empty( $entry->wc_order_id ) && function_exists( 'wc_get_order' ) ) {
			$order = wc_get_order( $entry->wc_order_id );
			if ( $order ) {
				$entry->wc_order_url    = $order->get_edit_order_url();
				$entry->wc_order_number = $order->get_order_number();
				if ( empty( $entry->payment_status ) || 'pending' === $entry->payment_status ) {
					$synced = self::payment_status_from_wc( $order->get_status() );
					$pay    = self::get_payment_status_display( $synced );
					$entry->payment_status       = $synced;
					$entry->payment_status_label = $pay['label'];
					$entry->payment_status_color = $pay['color'];
				}
			}
		}

		$entry->display_rows = array();
		if ( ! empty( $entry->form_id ) && is_array( $entry->entry_data ) && class_exists( 'Formulate_Pricing_Form' ) ) {
			$entry->display_rows = Formulate_Pricing_Form::get_cart_display_items( (int) $entry->form_id, $entry->entry_data );
		}

		return $entry;
	}

	public static function delete( $id ) {
		global $wpdb;
		$table = Formulate_Pricing_Database::get_entries_table();
		return $wpdb->delete( $table, array( 'id' => intval( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	public static function bulk_delete( $ids ) {
		global $wpdb;
		$table = Formulate_Pricing_Database::get_entries_table();
		$ids   = array_map( 'intval', (array) $ids );
		if ( empty( $ids ) ) {
			return false;
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->query( $wpdb->prepare( "DELETE FROM `$table` WHERE id IN ($placeholders)", ...$ids ) );
	}

	public static function export_csv( $args = array() ) {
		$args['per_page'] = 9999;
		$args['page']     = 1;
		$entries          = self::get_all( $args );

		if ( empty( $entries ) ) {
			return '';
		}

		$export_columns = array();
		$form           = ! empty( $args['form_id'] ) ? Formulate_Pricing_Form::get( (int) $args['form_id'] ) : null;

		if ( $form ) {
			$layout = Formulate_Pricing_Form::layout_field_types();
			foreach ( Formulate_Pricing_Form::get_all_fields( $form->form_config ) as $field ) {
				$type = isset( $field['type'] ) ? $field['type'] : 'text';
				$fid  = isset( $field['id'] ) ? $field['id'] : '';
				if ( ! $fid || in_array( $type, $layout, true ) ) {
					continue;
				}
				$export_columns[] = array(
					'id'    => $fid,
					'label' => isset( $field['label'] ) && '' !== $field['label'] ? $field['label'] : __( 'Field', 'formulate-pricing' ),
					'field' => $field,
				);
			}
		} else {
			$key_labels = array();
			foreach ( $entries as $entry ) {
				if ( ! is_array( $entry->entry_data ) ) {
					continue;
				}
				foreach ( $entry->entry_data as $key => $val ) {
					if ( 0 === strpos( $key, '_label_' ) || 0 === strpos( $key, '_' ) ) {
						continue;
					}
					if ( ! isset( $key_labels[ $key ] ) ) {
						$key_labels[ $key ] = self::get_field_label( $entry->entry_data, $key );
					}
				}
			}
			foreach ( $key_labels as $fid => $label ) {
				$export_columns[] = array(
					'id'    => $fid,
					'label' => $label,
					'field' => null,
				);
			}
		}

		$rows   = array();
		$header = array_merge(
			array( 'ID', 'Date', 'Status', 'Payment Status', 'Order ID' ),
			wp_list_pluck( $export_columns, 'label' )
		);
		$rows[] = $header;

		foreach ( $entries as $entry ) {
			$pay_display = self::get_payment_status_display( $entry->payment_status ?? '' );
			$row         = array(
				$entry->id,
				$entry->created_at,
				$entry->status,
				$pay_display['label'],
				$entry->wc_order_id ? $entry->wc_order_id : '',
			);
			foreach ( $export_columns as $col ) {
				$field = $col['field'];
				if ( ! $field && ! empty( $entry->form_id ) ) {
					$form_row = Formulate_Pricing_Form::get( (int) $entry->form_id );
					if ( $form_row ) {
						foreach ( Formulate_Pricing_Form::get_all_fields( $form_row->form_config ) as $f ) {
							if ( isset( $f['id'] ) && $f['id'] === $col['id'] ) {
								$field = $f;
								break;
							}
						}
					}
				}
				$row[] = self::format_field_value( $entry->entry_data, $col['id'], $field );
			}
			$rows[] = $row;
		}

		$csv = '';
		foreach ( $rows as $row ) {
			$escaped = array_map(
				function ( $cell ) {
					$cell = (string) $cell;
					if ( false !== strpos( $cell, '"' ) || false !== strpos( $cell, ',' ) || false !== strpos( $cell, "\n" ) ) {
						$cell = '"' . str_replace( '"', '""', $cell ) . '"';
					}
					return $cell;
				},
				$row
			);
			$csv .= implode( ',', $escaped ) . "\r\n";
		}

		return $csv;
	}
}
