<?php

declare( strict_types=1 );

namespace Brightfern\Inbound;

use Brightfern\Transmission\InboundInvoice;

/**
 * Persistence for received e-invoices in a dedicated custom table. We use our
 * own table (not post-meta) precisely because inbound documents are not orders
 * and must not bloat the orders store — this is HPOS-friendly by construction.
 *
 * Phase-2 hook: {@see \Brightfern\Archive\ArchiveInterface} can mirror each stored
 * document into immutable legal retention with an audit trail.
 */
final class InboundStore {

	public const STATUS_NEW       = 'new';
	public const STATUS_MATCHED   = 'matched';
	public const STATUS_UNMATCHED = 'unmatched';
	public const STATUS_ARCHIVED  = 'archived';

	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'brightfern_inbound';
	}

	/**
	 * Create/upgrade the table. Called on activation, and again on every plugin
	 * update via {@see \Brightfern\Plugin::ensureSchema()} (dbDelta() is a cheap
	 * no-op when the schema already matches), so widening a column here reaches
	 * already-installed sites automatically the next time the version changes —
	 * no separate migration step needed.
	 *
	 * issue_date is VARCHAR(64), not the exactly-fitted VARCHAR(20) it used to
	 * be: a plausible xs:date value like "2026-07-28" fits either width, but
	 * UblInboundParser is deliberately lenient about a sender supplying a full
	 * dateTime instead ("2026-07-28T00:00:00+02:00", 25 chars) — a real, common
	 * third-party deviation, not a contrived edge case. Past a column's
	 * declared width, $wpdb's own strip_invalid_text() check makes insert()
	 * return false silently — no error surfaced anywhere, the whole received
	 * document simply vanishes. 64 is deliberately generous, matching every
	 * other text column here, rather than exactly-fitted to today's known case.
	 */
	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table();
		$collate = $wpdb->get_charset_collate();

		// dbDelta is whitespace- and format-sensitive; keep two spaces after PRIMARY KEY.
		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			provider_message_id VARCHAR(191) NOT NULL DEFAULT '',
			sender_id VARCHAR(191) NOT NULL DEFAULT '',
			recipient_id VARCHAR(191) NOT NULL DEFAULT '',
			document_type VARCHAR(40) NOT NULL DEFAULT 'invoice',
			format VARCHAR(60) NOT NULL DEFAULT '',
			status VARCHAR(20) NOT NULL DEFAULT 'new',
			matched_order_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			invoice_number VARCHAR(191) NOT NULL DEFAULT '',
			supplier_name VARCHAR(191) NOT NULL DEFAULT '',
			supplier_vat VARCHAR(60) NOT NULL DEFAULT '',
			currency VARCHAR(8) NOT NULL DEFAULT '',
			payable_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
			issue_date VARCHAR(64) NOT NULL DEFAULT '',
			received_at DATETIME NULL DEFAULT NULL,
			summary LONGTEXT NULL,
			xml LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY provider_message_id (provider_message_id),
			KEY status (status),
			KEY matched_order_id (matched_order_id)
		) {$collate};";

		dbDelta( $sql );
	}

	public static function exists( string $providerMessageId ): bool {
		global $wpdb;
		if ( '' === $providerMessageId ) {
			return false;
		}
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input, and table names cannot be bound via prepare().
		$id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE provider_message_id = %s", $providerMessageId ) );
		return null !== $id;
	}

	/**
	 * Store a received document together with its parsed summary and a match.
	 * Idempotent on provider_message_id so duplicate webhooks don't double-store.
	 *
	 * @return int|null Inserted row id on success; 0 for a benign duplicate
	 *   (already stored, nothing to log or retry); null when the INSERT itself
	 *   failed (e.g. a value exceeded a column's width) — a genuinely lost
	 *   document, distinct from a duplicate specifically so callers can log
	 *   and surface it instead of the two cases being silently indistinguishable.
	 */
	public static function save( InboundInvoice $inbound, InboundSummary $summary, int $matchedOrderId = 0 ): ?int {
		global $wpdb;

		if ( self::exists( $inbound->providerMessageId ) ) {
			return 0;
		}

		$status = $matchedOrderId > 0 ? self::STATUS_MATCHED : self::STATUS_UNMATCHED;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input, and table names cannot be bound via prepare().
		$ok = $wpdb->insert(
			self::table(),
			array(
				'provider_message_id' => $inbound->providerMessageId,
				'sender_id'           => $inbound->senderId,
				'recipient_id'        => $inbound->recipientId,
				'document_type'       => $summary->documentType ?: $inbound->documentType,
				'format'              => $inbound->format,
				'status'              => $status,
				'matched_order_id'    => $matchedOrderId,
				'invoice_number'      => $summary->invoiceNumber,
				'supplier_name'       => $summary->supplierName,
				'supplier_vat'        => $summary->supplierVat,
				'currency'            => $summary->currency,
				'payable_amount'      => $summary->payableAmount,
				'issue_date'          => $summary->issueDate,
				'received_at'         => '' !== $inbound->receivedAt ? gmdate( 'Y-m-d H:i:s', strtotime( $inbound->receivedAt ) ?: time() ) : gmdate( 'Y-m-d H:i:s' ),
				'summary'             => wp_json_encode( $summary->toArray() ),
				'xml'                 => $inbound->xml,
				'created_at'          => gmdate( 'Y-m-d H:i:s' ),
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%f', '%s', '%s', '%s', '%s', '%s' )
		);

		return $ok ? (int) $wpdb->insert_id : null;
	}

	/** @return array<string,mixed>|null */
	public static function get( int $id ): ?array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input, and table names cannot be bound via prepare().
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A );
		return is_array( $row ) ? $row : null;
	}

	/**
	 * @param array{status?:string,limit?:int,offset?:int} $args
	 * @return array<int,array<string,mixed>>
	 */
	public static function query( array $args = array() ): array {
		global $wpdb;
		$table  = self::table();
		$limit  = (int) ( $args['limit'] ?? 50 );
		$offset = (int) ( $args['offset'] ?? 0 );

		if ( ! empty( $args['status'] ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input.
			$rows = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names cannot be bound via prepare(); every value is.
				$wpdb->prepare( "SELECT * FROM {$table} WHERE status = %s ORDER BY received_at DESC LIMIT %d OFFSET %d", $args['status'], $limit, $offset ),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input.
			$rows = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names cannot be bound via prepare(); every value is.
				$wpdb->prepare( "SELECT * FROM {$table} ORDER BY received_at DESC LIMIT %d OFFSET %d", $limit, $offset ),
				ARRAY_A
			);
		}

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

	/** @return array<string,int> status => count */
	public static function countsByStatus(): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input, and table names cannot be bound via prepare().
		$rows = $wpdb->get_results( "SELECT status, COUNT(*) AS c FROM {$table} GROUP BY status", ARRAY_A );
		$out  = array();
		foreach ( (array) $rows as $row ) {
			$out[ (string) $row['status'] ] = (int) $row['c'];
		}
		return $out;
	}

	public static function setMatch( int $id, int $orderId ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix, never user input, and table names cannot be bound via prepare().
		$wpdb->update(
			self::table(),
			array(
				'matched_order_id' => $orderId,
				'status'           => $orderId > 0 ? self::STATUS_MATCHED : self::STATUS_UNMATCHED,
			),
			array( 'id' => $id ),
			array( '%d', '%s' ),
			array( '%d' )
		);
	}
}
