<?php
/**
 * Payment CRUD with invoice status auto-update.
 *
 * @since 1.0.0
 */

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

class ZINV_Payment {

	private ZINV_Database $db;

	private const ALLOWED_STATUSES    = [ 'successful', 'pending', 'failed', 'refunded', 'partially_refunded' ];
	private const ALLOWED_METHODS     = [ 'stripe', 'bank_transfer', 'paypal', 'manual', 'other' ];
	private const ALLOWED_SORT_FIELDS = [ 'id', 'invoice_id', 'client_id', 'amount', 'status', 'method', 'transaction_date', 'created_at' ];

	public function __construct() {
		$this->db = new ZINV_Database( 'zinv_payments' );
	}

	public function create( array $data ): int {
		global $wpdb;

		$invoice_id = absint( $data['invoice_id'] ?? 0 );
		$invoice    = ( new ZINV_Invoice() )->get( $invoice_id );

		if ( ! $invoice ) {
			return 0;
		}

		$amount = (string) ( $data['amount'] ?? '0' );
		// Guard: no zero or negative payments.
		if ( bccomp( $amount, '0', 4 ) <= 0 ) {
			return 0;
		}
		$currency = sanitize_text_field( $data['currency'] ?? $invoice['currency'] );
		// Reject currency mismatch — recording a EUR payment on a USD invoice
		// produces meaningless paid_total / amount_due math downstream.
		if ( ! empty( $invoice['currency'] ) && strtoupper( $currency ) !== strtoupper( $invoice['currency'] ) ) {
			return 0;
		}
		$method = $this->validate_method( $data['method'] ?? 'manual' );
		$status = in_array( $data['status'] ?? 'successful', self::ALLOWED_STATUSES, true )
			? $data['status']
			: 'successful';

		$now = current_time( 'mysql' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( 'START TRANSACTION' );

		try {
			$insert = [
				'invoice_id'       => $invoice_id,
				'client_id'        => absint( $data['client_id'] ?? $invoice['client_id'] ),
				'amount'           => $amount,
				'currency'         => $currency,
				'status'           => $status,
				'method'           => $method,
				'stripe_charge_id' => isset( $data['stripe_charge_id'] ) ? sanitize_text_field( $data['stripe_charge_id'] ) : null,
				'transaction_date' => ! empty( $data['transaction_date'] ) ? sanitize_text_field( $data['transaction_date'] ) : $now,
				'gateway_response' => isset( $data['gateway_response'] ) ? wp_json_encode( $data['gateway_response'] ) : null,
				'notes'            => isset( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : null,
				'created_at'       => $now,
				'updated_at'       => $now,
			];

			$payment_id = $this->db->insert( $insert );

			if ( ! $payment_id ) {
				throw new \Exception( 'Failed to insert payment.' );
			}

			if ( 'successful' === $status ) {
				$this->update_invoice_paid_status( $invoice_id );
			}

			delete_transient( 'zinv_dashboard_kpis' );
			delete_transient( 'zinv_dash_monthly_revenue' );
			( new ZINV_Summary() )->refresh_all( 300, true );

			$wpdb->query( 'COMMIT' );

			// Activity log: free-tier audit trail.
			if ( 'successful' === $status ) {
				ZINV_Activity::log(
					'payment.received',
					'payment',
					$payment_id,
					sprintf(
						/* translators: 1: amount, 2: currency code, 3: invoice number */
						__( 'Payment of %1$s %2$s was recorded for invoice %3$s.', 'zoventic-invoices' ),
						$amount,
						strtoupper( $currency ),
						$invoice['invoice_number'] ?? ''
					)
				);
			}

			do_action( 'zinv_payment_created', $payment_id, $insert );

			return $payment_id;

		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' );
			return 0;
		} finally {
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
	}

	public function get( int $id ): ?array {
		return $this->db->get_row(
			"SELECT * FROM {$this->db->get_table_name()} WHERE id = %d",
			$id
		);
	}

	public function update( int $id, array $data ): bool {
		$payment = $this->get( $id );
		if ( ! $payment ) {
			return false;
		}

		global $wpdb;
		$now = current_time( 'mysql' );

		$fields = [ 'updated_at' => $now ];

		// notes is the only text field without domain-specific validation.
		if ( array_key_exists( 'notes', $data ) ) {
			$fields['notes'] = sanitize_textarea_field( $data['notes'] );
		}

		// status: whitelist only.
		if ( array_key_exists( 'status', $data ) ) {
			if ( ! in_array( $data['status'], self::ALLOWED_STATUSES, true ) ) {
				return false;
			}
			$fields['status'] = $data['status'];
		}

		// method: whitelist via validate_method().
		if ( array_key_exists( 'method', $data ) ) {
			$fields['method'] = $this->validate_method( $data['method'] );
		}

		if ( isset( $data['refund_amount'] ) ) {
			$refund = (string) $data['refund_amount'];
			if ( bccomp( $refund, (string) $payment['amount'], 4 ) > 0 ) {
				return false;
			}
			$fields['refund_amount']    = $refund;
			$fields['stripe_refund_id'] = isset( $data['stripe_refund_id'] )
				? sanitize_text_field( $data['stripe_refund_id'] )
				: null;
		}

		if ( isset( $data['gateway_response'] ) ) {
			$fields['gateway_response'] = wp_json_encode( $data['gateway_response'] );
		}

		$old_status = $payment['status'];

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->update(
			$this->db->get_table_name(),
			$fields,
			[ 'id' => $id ],
			$this->get_formats( $fields ),
			[ '%d' ]
		);

		if ( false !== $result ) {
			$new_status = $fields['status'] ?? $old_status;
			// Recompute invoice when: payment becomes successful, OR refund amount
			// changes, OR status flips out of successful (refund/void). All three
			// affect amount_paid/amount_due on the linked invoice.
			$refund_changed = array_key_exists( 'refund_amount', $fields );
			$status_changed = $old_status !== $new_status;
			if ( $status_changed || $refund_changed ) {
				$this->update_invoice_paid_status( (int) $payment['invoice_id'] );
			}
		}

		return false !== $result;
	}

	public function list( array $args = [] ): array {
		$defaults = [
			'page'       => 1,
			'per_page'   => 20,
			'search'     => '',
			'status'     => '',
			'client_id'  => 0,
			'invoice_id' => 0,
			'method'     => '',
			'date_from'  => '',
			'date_to'    => '',
			'orderby'    => 'created_at',
			'order'      => 'DESC',
		];

		$args          = wp_parse_args( $args, $defaults );
		$table         = $this->db->get_table_name();
		$clients_table = ( new ZINV_Database( 'zinv_clients' ) )->get_table_name();
		$where         = [];
		$where_args    = [];

		if ( ! empty( $args['search'] ) ) {
			global $wpdb;
			$like         = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]      = '(p.stripe_charge_id LIKE %s OR p.notes LIKE %s OR c.name LIKE %s OR c.company LIKE %s)';
			$where_args[] = $like;
			$where_args[] = $like;
			$where_args[] = $like;
			$where_args[] = $like;
		}

		if ( ! empty( $args['status'] ) && in_array( $args['status'], self::ALLOWED_STATUSES, true ) ) {
			$where[]      = 'p.status = %s';
			$where_args[] = sanitize_text_field( $args['status'] );
		}

		if ( ! empty( $args['client_id'] ) ) {
			$where[]      = 'p.client_id = %d';
			$where_args[] = absint( $args['client_id'] );
		}

		if ( ! empty( $args['invoice_id'] ) ) {
			$where[]      = 'p.invoice_id = %d';
			$where_args[] = absint( $args['invoice_id'] );
		}

		if ( ! empty( $args['method'] ) && in_array( $args['method'], self::ALLOWED_METHODS, true ) ) {
			$where[]      = 'p.method = %s';
			$where_args[] = sanitize_text_field( $args['method'] );
		}

		if ( ! empty( $args['date_from'] ) ) {
			$where[]      = 'p.transaction_date >= %s';
			$where_args[] = sanitize_text_field( $args['date_from'] );
		}

		if ( ! empty( $args['date_to'] ) ) {
			$where[]      = 'p.transaction_date <= %s';
			$where_args[] = sanitize_text_field( $args['date_to'] );
		}

		$where_clause = ! empty( $where ) ? implode( ' AND ', $where ) : '';
		$orderby      = in_array( $args['orderby'], self::ALLOWED_SORT_FIELDS, true ) ? $args['orderby'] : 'created_at';

		return $this->db->get_paginated(
			'p.*, c.name AS client_name, c.email AS client_email, c.company AS client_company',
			"{$table} p LEFT JOIN {$clients_table} c ON c.id = p.client_id",
			(int) $args['page'],
			(int) $args['per_page'],
			$where_clause,
			"p.{$orderby}",
			$args['order'],
			$where_args
		);
	}

	private function update_invoice_paid_status( int $invoice_id ): void {
		global $wpdb;
		$table = $this->db->get_table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$total_paid = (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(amount), 0) FROM {$table} WHERE invoice_id = %d AND status = 'successful'",
			$invoice_id
		) );

		$total_refunded = (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(refund_amount), 0) FROM {$table} WHERE invoice_id = %d AND status IN ('refunded', 'partially_refunded')",
			$invoice_id
		) );

		$net_paid = bcsub( $total_paid, $total_refunded, 4 );
		if ( bccomp( $net_paid, '0', 4 ) < 0 ) {
			$net_paid = '0';
		}

		$invoice_table = ( new ZINV_Database( 'zinv_invoices' ) )->get_table_name();

		$invoice_row = $wpdb->get_row( $wpdb->prepare(
			"SELECT total, status, due_date, invoice_number FROM {$invoice_table} WHERE id = %d",
			$invoice_id
		), ARRAY_A );

		if ( ! $invoice_row ) {
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return;
		}

		$invoice_total  = (string) $invoice_row['total'];
		$current_status = (string) $invoice_row['status'];
		$amount_due     = bcsub( $invoice_total, $net_paid, 4 );
		if ( bccomp( $amount_due, '0', 4 ) < 0 ) {
			$amount_due = '0';
		}

		// Pick new status WITHOUT overwriting unrelated states.
		// Rule: fully paid → paid; partial payment → partial; otherwise leave current status.
		if ( bccomp( $amount_due, '0', 4 ) <= 0 && bccomp( $net_paid, '0', 4 ) > 0 ) {
			$new_status = 'paid';
		} elseif ( bccomp( $net_paid, '0', 4 ) > 0 ) {
			// Partial: only for non-terminal post-send statuses that aren't
			// already overdue. Overdue partial-payment stays overdue (debtor
			// hasn't caught up); promotion to 'partial' would hide the issue.
			if ( in_array( $current_status, [ 'sent', 'viewed', 'partial' ], true ) ) {
				$new_status = 'partial';
			} else {
				$new_status = $current_status;
			}
		} else {
			// All payments refunded back to zero — keep whatever state the invoice was in.
			$new_status = $current_status;
		}

		// Bump version so concurrent invoice updates using the old version
		// hit the optimistic-lock guard instead of clobbering paid totals.
		//
		// The paid_at column is conditional: set it when the invoice becomes
		// paid, clear it when it leaves paid, otherwise leave it untouched.
		// Build the SET fragment and the matching args together so the SQL
		// passed to prepare() is a single complete string with a fixed
		// placeholder/arg count — no concatenation inside prepare().
		$set_clauses = [
			'amount_paid = %s',
			'amount_due  = %s',
			'status      = %s',
			'updated_at  = %s',
			'version     = version + 1',
		];
		$args        = [
			$net_paid,
			$amount_due,
			$new_status,
			gmdate( 'Y-m-d H:i:s' ),
		];

		if ( 'paid' === $new_status ) {
			$set_clauses[] = 'paid_at = %s';
			$args[]        = gmdate( 'Y-m-d H:i:s' );
		} elseif ( 'paid' === $current_status ) {
			// Leaving the paid state — clear the timestamp. Static literal,
			// no placeholder needed.
			$set_clauses[] = 'paid_at = NULL';
		}

		$args[] = $invoice_id;

		$sql = "UPDATE {$invoice_table} SET " . implode( ', ', $set_clauses ) . ' WHERE id = %d';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( $wpdb->prepare( $sql, $args ) );

		// Activity log: free-tier audit trail — only when invoice becomes fully paid.
		if ( 'paid' === $new_status && 'paid' !== $current_status ) {
			ZINV_Activity::log(
				'invoice.paid',
				'invoice',
				$invoice_id,
				/* translators: %s: invoice number */
				sprintf( __( 'Invoice %s has been fully paid.', 'zoventic-invoices' ), $invoice_row['invoice_number'] ?? '' )
			);
		}
	}

	private function validate_method( string $method ): string {
		return in_array( $method, self::ALLOWED_METHODS, true ) ? $method : 'manual';
	}

	private function get_formats( array $data ): array {
		$formats = [];
		foreach ( $data as $value ) {
			if ( is_int( $value ) ) {
				$formats[] = '%d';
			} elseif ( null === $value ) {
				$formats[] = '%s';
			} else {
				$formats[] = '%s';
			}
		}
		return $formats;
	}
}
