<?php
/**
 * Estimate CRUD operations with line items and auto-numbering.
 *
 * @since 1.0.0
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB

class ZINV_Estimate {

	private ZINV_Database $db;
	private ZINV_Database $line_items_db;

	private const ALLOWED_STATUSES    = [ 'draft', 'sent', 'approved', 'declined', 'expired', 'converted', 'void' ];
	private const TERMINAL_STATUSES   = [ 'approved', 'declined', 'expired', 'converted', 'void' ];
	private const ALLOWED_SORT_FIELDS = [ 'id', 'estimate_number', 'client_id', 'status', 'issue_date', 'expiry_date', 'total', 'created_at' ];
	private const ALLOWED_DISCOUNT_TYPES = [ 'percentage', 'fixed' ];

	private const TRANSITIONS = [
		'draft'    => [ 'sent', 'void' ],
		'sent'     => [ 'approved', 'declined', 'expired', 'void', 'converted' ],
		'approved' => [ 'converted' ],
	];

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

	public function create( array $data, array $line_items = [] ): int {
		global $wpdb;

		$now  = current_time( 'mysql' );
		$date = current_time( 'Y-m-d' );

		try {
			$estimate_number = $this->generate_number( $date );

			$wpdb->query( 'START TRANSACTION' );

			// Template + terms fall back to the saved Invoicing defaults when
			// the request omits them, not to a hardcoded value.
			$template = sanitize_text_field(
				$data['template'] ?? (string) ZINV_Settings::get( 'default_template', 'clean-sheet' )
			);
			if ( '' === $template ) {
				$template = 'clean-sheet';
			}

			$terms_raw = $data['terms'] ?? ZINV_Settings::get( 'default_terms', '' );
			$terms     = '' !== trim( (string) $terms_raw )
				? sanitize_textarea_field( (string) $terms_raw )
				: null;

			$subtotal = $this->calculate_subtotal( $line_items );

			// Honour the client-level tax-exempt flag: bypass all tax for this
			// client regardless of what the line items say.
			$client_id     = absint( $data['client_id'] ?? 0 );
			$client_obj    = ( new ZINV_Client() )->get( $client_id );
			$is_tax_exempt = $client_obj && ( (int) ( $client_obj['tax_exempt'] ?? 0 ) === 1 );
			$tax_total     = $is_tax_exempt ? '0' : $this->calculate_tax_total( $line_items );
			$discount_total = $this->calculate_discount_total(
				$subtotal,
				$data['discount_type'] ?? null,
				isset( $data['discount_value'] ) ? (string) $data['discount_value'] : '0'
			);
			$total = bcsub( bcadd( $subtotal, $tax_total, 4 ), $discount_total, 4 );

			$insert = [
				'estimate_number' => $estimate_number,
				'client_id'       => absint( $data['client_id'] ),
				'status'          => 'draft',
				'issue_date'      => sanitize_text_field( $data['issue_date'] ?? $date ),
				'expiry_date'     => isset( $data['expiry_date'] ) ? sanitize_text_field( $data['expiry_date'] ) : null,
				'subtotal'        => $subtotal,
				'tax_total'       => $tax_total,
				'discount_total'  => $discount_total,
				'total'           => $total,
				'currency'        => sanitize_text_field( $data['currency'] ?? 'USD' ),
				'discount_type'   => isset( $data['discount_type'] ) ? $this->validate_discount_type( $data['discount_type'] ) : null,
				'discount_value'  => isset( $data['discount_value'] ) ? (string) $data['discount_value'] : '0',
				'template'        => $template,
				'document_title'  => isset( $data['document_title'] ) ? sanitize_text_field( $data['document_title'] ) : null,
				'notes'           => isset( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : null,
				'terms'           => $terms,
				'auto_convert'    => isset( $data['auto_convert'] ) ? (int) (bool) $data['auto_convert'] : 0,
				'version'         => 1,
				'created_at'      => $now,
				'updated_at'      => $now,
			];

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

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

			$this->sync_line_items( $estimate_id, $line_items, $is_tax_exempt );

			$wpdb->query( 'COMMIT' );

			// Activity log: free-tier audit trail.
			ZINV_Activity::log(
				'estimate.created',
				'estimate',
				$estimate_id,
				/* translators: %s: estimate number */
				sprintf( __( 'Estimate %s was created.', 'zoventic-invoices' ), $estimate_number )
			);

			return $estimate_id;

		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' );
			return 0;
		}
	}

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

		if ( ! $estimate ) {
			return null;
		}

		$estimate['discount_value'] = isset( $estimate['discount_value'] ) ? (float) $estimate['discount_value'] : 0.0;
		$estimate['subtotal']       = isset( $estimate['subtotal'] ) ? (float) $estimate['subtotal'] : 0.0;
		$estimate['tax_total']      = isset( $estimate['tax_total'] ) ? (float) $estimate['tax_total'] : 0.0;
		$estimate['total']          = isset( $estimate['total'] ) ? (float) $estimate['total'] : 0.0;
		$estimate['line_items']     = $this->get_line_items( $id );
		return $estimate;
	}

	/**
	 * Update an estimate.
	 *
	 * @param array|null $line_items NULL = leave existing line items untouched.
	 *                               array (including empty []) = replace with this set.
	 */
	public function update( int $id, array $data, ?array $line_items, int $version ): bool {
		$estimate = $this->get( $id );
		if ( ! $estimate ) {
			return false;
		}

		if ( in_array( $estimate['status'], self::TERMINAL_STATUSES, true ) ) {
			return false;
		}

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

		$wpdb->query( 'START TRANSACTION' );

		try {
			// Resolve the client to check for the tax-exempt flag. Prefer the
			// updated client_id from $data if provided; otherwise fall back to
			// the stored client_id on the existing estimate row.
			$client_id_for_update = isset( $data['client_id'] )
				? absint( $data['client_id'] )
				: absint( $estimate['client_id'] );
			$client_for_update    = ( new ZINV_Client() )->get( $client_id_for_update );
			$is_tax_exempt        = $client_for_update && ( (int) ( $client_for_update['tax_exempt'] ?? 0 ) === 1 );

			if ( null !== $line_items ) {
				$this->sync_line_items( $id, $line_items, $is_tax_exempt );
			}

			$refreshed = $this->get( $id );
			$subtotal  = $refreshed ? $this->calculate_subtotal_from_db( $id ) : (string) $estimate['subtotal'];
			$tax_total = $is_tax_exempt ? '0' : ( $refreshed ? $this->calculate_tax_total_from_db( $id ) : (string) $estimate['tax_total'] );
			$discount_type  = $data['discount_type'] ?? $estimate['discount_type'];
			$discount_value = isset( $data['discount_value'] ) ? (string) $data['discount_value'] : (string) $estimate['discount_value'];
			$discount_total = $this->calculate_discount_total( $subtotal, $discount_type, $discount_value );
			$total          = bcsub( bcadd( $subtotal, $tax_total, 4 ), $discount_total, 4 );

			$fields = [
				'subtotal'       => $subtotal,
				'tax_total'      => $tax_total,
				'discount_total' => $discount_total,
				'total'          => $total,
				'version'        => $version + 1,
				'updated_at'     => $now,
			];

			$updatable = [
				'issue_date'    => 'sanitize_text_field',
				'expiry_date'   => 'sanitize_text_field',
				'notes'         => 'sanitize_textarea_field',
				'terms'         => 'sanitize_textarea_field',
				'discount_type' => 'sanitize_text_field',
				'currency'      => 'sanitize_text_field',
				'template'      => 'sanitize_text_field',
				'document_title' => 'sanitize_text_field',
			];

			foreach ( $updatable as $field => $sanitizer ) {
				if ( array_key_exists( $field, $data ) ) {
					$fields[ $field ] = $sanitizer( $data[ $field ] );
				}
			}

			if ( isset( $data['discount_value'] ) ) {
				$fields['discount_value'] = (string) $data['discount_value'];
			}

			if ( isset( $data['client_id'] ) ) {
				$fields['client_id'] = absint( $data['client_id'] );
			}

			if ( isset( $data['auto_convert'] ) ) {
				$fields['auto_convert'] = (int) (bool) $data['auto_convert'];
			}

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

			if ( false === $result || $wpdb->rows_affected === 0 ) {
				throw new \Exception( 'Version conflict.' );
			}

			$wpdb->query( 'COMMIT' );
			return true;

		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}
	}

	public function send( int $id ): bool {
		$estimate = $this->get( $id );
		if ( ! $estimate ) {
			return false;
		}

		$current_status = (string) $estimate['status'];
		if ( in_array( $current_status, self::TERMINAL_STATUSES, true ) ) {
			return false;
		}

		$ok = true;
		if ( 'draft' === $current_status ) {
			$ok = $this->transition_status( $id, 'sent', [
				'sent_at' => current_time( 'mysql' ),
			] );
		}

		if ( $ok ) {
			$client = null;
			if ( ! empty( $estimate['client_id'] ) ) {
				$client_crud = new ZINV_Client();
				$client      = $client_crud->get( (int) $estimate['client_id'] );
			}

			do_action( 'zinv_estimate_sent', $id, $estimate, $client );

			// Activity log: free-tier audit trail.
			ZINV_Activity::log(
				'estimate.sent',
				'estimate',
				$id,
				/* translators: %s: estimate number */
				sprintf( __( 'Estimate %s was sent to the client.', 'zoventic-invoices' ), $estimate['estimate_number'] ?? '' )
			);

			if ( $client && ! empty( $client['email'] ) ) {
				self::send_basic_estimate_email( $estimate, $client );
			}
		}

		return $ok;
	}

	/**
	 * Plain-text estimate notification used to dispatch quotation emails with PDF attachments.
	 */
	private static function send_basic_estimate_email( array $estimate, array $client ): void {
		$to = (string) ( $client['email'] ?? '' );
		if ( '' === $to || ! is_email( $to ) ) {
			return;
		}

		if ( ! class_exists( 'ZINV_Settings' ) ) {
			require_once __DIR__ . '/class-settings.php';
		}

		$business        = wp_specialchars_decode( (string) get_option( 'blogname' ), ENT_QUOTES );
		$estimate_no     = (string) ( $estimate['estimate_number'] ?? '' );
		$total           = (string) ( $estimate['total'] ?? '0' );
		$currency        = (string) ( $estimate['currency'] ?? 'USD' );
		$formatted_total = is_numeric( $total ) ? number_format( (float) $total, 2 ) : $total;
		$due_date        = ! empty( $estimate['expiry_date'] ) && '0000-00-00' !== $estimate['expiry_date'] ? gmdate( 'M j, Y', strtotime( (string) $estimate['expiry_date'] ) ) : '';

		$tags = [
			'{client_name}'     => (string) ( $client['name'] ?? '' ),
			'{estimate_number}' => $estimate_no,
			'{amount}'          => trim( $formatted_total . ' ' . strtoupper( $currency ) ),
			'{expiry_date}'     => $due_date,
			'{business_name}'   => $business,
		];

		$default_subject = __( 'Estimate {estimate_number} from {business_name}', 'zoventic-invoices' );
		$default_body    = __( "Dear {client_name},\n\nThank you for reaching out. We have prepared estimate {estimate_number} for {amount}.\n\nEstimate Details:\n----------------------------------------\nEstimate Number: {estimate_number}\nTotal Amount:    {amount}\nValid Until:     {expiry_date}\n----------------------------------------\n\nA PDF copy of your estimate is attached to this email.\n\nPlease review the estimate and feel free to reply directly to this email if you have any questions.\n\nBest regards,\n{business_name}", 'zoventic-invoices' );

		$custom_subject = ZINV_Settings::get( 'email_estimate_subject', '' );
		$custom_body    = ZINV_Settings::get( 'email_estimate_body', '' );

		$subject_template = ! empty( $custom_subject ) ? $custom_subject : $default_subject;
		$body_template    = ! empty( $custom_body ) ? $custom_body : $default_body;

		$subject = str_replace( array_keys( $tags ), array_values( $tags ), $subject_template );
		$body    = str_replace( array_keys( $tags ), array_values( $tags ), $body_template );

		$custom_from_name  = ZINV_Settings::get( 'email_from_name', '' );
		$custom_from_email = ZINV_Settings::get( 'email_from_email', '' );

		$from_name  = ! empty( $custom_from_name ) ? $custom_from_name : $business;
		$from_email = ! empty( $custom_from_email ) && is_email( $custom_from_email ) && 'invoices@yourdomain.com' !== $custom_from_email
			? $custom_from_email
			: (string) get_option( 'admin_email' );

		$from_name  = preg_replace( "/[\r\n]+/", ' ', (string) $from_name );
		$from_email = preg_replace( "/[\r\n]+/", '', (string) $from_email );
		$name_q     = '"' . str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $from_name ) . '"';

		$subject = str_replace( [ "\r", "\n" ], ' ', $subject );
		$subject = trim( $subject );

		$attachments = [];
		if ( ! empty( $estimate['id'] ) && class_exists( 'ZINV_PDF_Generator' ) && ZINV_PDF_Generator::fonts_ready() ) {
			try {
				$template = ! empty( $estimate['template'] ) ? $estimate['template'] : 'clean-sheet';
				$pdf_file = ZINV_PDF_Generator::generate_estimate( (int) $estimate['id'], $template );
				if ( file_exists( $pdf_file ) ) {
					$attachments[] = $pdf_file;
				}
			} catch ( \Exception $e ) {
				// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
				error_log( 'Zoventic Invoices estimate email PDF attachment failed for estimate ' . $estimate['id'] . ': ' . $e->getMessage() );
			}
		}

		wp_mail(
			$to,
			$subject,
			$body,
			[
				'Content-Type: text/plain; charset=UTF-8',
				sprintf( 'From: %s <%s>', $name_q, $from_email ),
			],
			$attachments
		);
	}

	public function approve( int $id ): bool {
		return $this->transition_status( $id, 'approved', [
			'approved_at' => current_time( 'mysql' ),
		] );
	}

	public function decline( int $id ): bool {
		return $this->transition_status( $id, 'declined', [
			'declined_at' => current_time( 'mysql' ),
		] );
	}

	public function expire( int $id ): bool {
		return $this->transition_status( $id, 'expired' );
	}

	public function void( int $id ): bool {
		return $this->transition_status( $id, 'void' );
	}

	public function convert_to_invoice( int $id ): int {
		$estimate = $this->get( $id );
		// Match REST controller: allow conversion from 'sent' OR 'approved'.
		if ( ! $estimate || ! in_array( $estimate['status'], [ 'sent', 'approved' ], true ) ) {
			return 0;
		}

		$line_items = array_map( function ( $item ) {
			$row = (array) $item;
			return [
				'description' => $row['description'] ?? '',
				'quantity'    => $row['quantity']    ?? 1,
				'unit_price'  => $row['unit_price']  ?? 0,
				'tax_rate'    => $row['tax_rate']    ?? 0,
			];
		}, $estimate['line_items'] ?? [] );

		$issue_date = current_time( 'Y-m-d' );
		$due_days   = (int) ( ( new ZINV_Settings() )->get( 'default_due_days' ) ?: 30 );
		$due_date   = gmdate( 'Y-m-d', strtotime( "{$issue_date} +{$due_days} days" ) );

		$invoice_data = [
			'client_id'       => (int) $estimate['client_id'],
			'estimate_id'     => $id,
			'issue_date'      => $issue_date,
			'due_date'        => $due_date,
			'currency'        => $estimate['currency'],
			'discount_type'   => $estimate['discount_type'],
			'discount_value'  => (string) $estimate['discount_value'],
			// Carry the estimate's template so the converted invoice keeps the
			// same look instead of silently reverting to the saved default.
			'template'        => $estimate['template'] ?? 'clean-sheet',
			'notes'           => $estimate['notes'],
			'terms'           => $estimate['terms'],
		];

		$invoice = new ZINV_Invoice();
		$invoice_id = $invoice->create( $invoice_data, $line_items );

		if ( ! $invoice_id ) {
			return 0;
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$updated = $wpdb->update(
			$this->db->get_table_name(),
			[
				'status'              => 'converted',
				'converted_invoice_id' => $invoice_id,
				'updated_at'          => current_time( 'mysql' ),
			],
			[ 'id' => $id ],
			[ '%s', '%d', '%s' ],
			[ '%d' ]
		);

		// Estimate didn't link to its invoice — log but don't fail: invoice
		// itself was created successfully, so caller still gets an invoice id.
		if ( false === $updated ) {
			error_log( '[ZINV_Estimate] convert_to_invoice: failed to mark estimate #' . $id . ' as converted to invoice #' . $invoice_id ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
		}

		return $invoice_id;
	}

	public function list( array $args = [] ): array {
		$defaults = [
			'page'      => 1,
			'per_page'  => 20,
			'search'    => '',
			'status'    => '',
			'client_id' => 0,
			'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[]      = "(e.estimate_number LIKE %s OR e.document_title LIKE %s OR e.issue_date LIKE %s OR c.name LIKE %s OR c.company LIKE %s)";
			$where_args[] = $like;
			$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[]      = "e.status = %s";
			$where_args[] = sanitize_text_field( $args['status'] );
		}

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

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

		if ( ! empty( $args['date_to'] ) ) {
			$where[]      = "e.issue_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(
			'e.*, c.name AS client_name, c.email AS client_email, c.company AS client_company',
			"{$table} e LEFT JOIN {$clients_table} c ON c.id = e.client_id",
			(int) $args['page'],
			(int) $args['per_page'],
			$where_clause,
			"e.{$orderby}",
			$args['order'],
			$where_args
		);
	}

	public function get_line_items( int $estimate_id ): array {
		global $wpdb;
		$table = $this->line_items_db->get_table_name();
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$table} WHERE estimate_id = %d ORDER BY sort_order ASC, id ASC",
			$estimate_id
		), ARRAY_A ) ?: [];

		foreach ( $rows as &$row ) {
			$row['quantity']   = isset( $row['quantity'] ) ? (float) $row['quantity'] : 0.0;
			$row['unit_price'] = isset( $row['unit_price'] ) ? (float) $row['unit_price'] : 0.0;
			$row['tax_rate']   = isset( $row['tax_rate'] ) ? (float) $row['tax_rate'] : 0.0;
			$row['tax_amount'] = isset( $row['tax_amount'] ) ? (float) $row['tax_amount'] : 0.0;
			$row['total']      = isset( $row['total'] ) ? (float) $row['total'] : 0.0;
			$row['sort_order'] = isset( $row['sort_order'] ) ? (int) $row['sort_order'] : 0;
		}
		unset( $row );

		return $rows;
	}

	private function transition_status( int $id, string $new_status, array $extra = [] ): bool {
		$estimate = $this->get( $id );
		if ( ! $estimate ) {
			return false;
		}

		if ( ! in_array( $new_status, self::ALLOWED_STATUSES, true ) ) {
			return false;
		}

		$allowed = self::TRANSITIONS[ $estimate['status'] ] ?? [];
		if ( ! in_array( $new_status, $allowed, true ) ) {
			return false;
		}

		$now    = current_time( 'mysql' );
		$fields = array_merge( [
			'status'     => $new_status,
			'updated_at' => $now,
		], $extra );

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

		return false !== $result;
	}

	/**
	 * Persist line items for an estimate.
	 *
	 * When $is_tax_exempt is true (client is marked tax-exempt) every line item
	 * is stored with tax_rate = '0' and tax_amount = '0.0000', ensuring the
	 * database and any PDF render always reflect zero tax regardless of what
	 * the UI submitted.
	 *
	 * @param int   $estimate_id   The parent estimate ID.
	 * @param array $items         Array of line-item data from the request.
	 * @param bool  $is_tax_exempt When true, force all tax values to zero.
	 */
	private function sync_line_items( int $estimate_id, array $items, bool $is_tax_exempt = false ): void {
		global $wpdb;
		$table = $this->line_items_db->get_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->delete( $table, [ 'estimate_id' => $estimate_id ], [ '%d' ] );

		$sort = 0;
		foreach ( $items as $item ) {
			$qty_val   = $item['quantity'] ?? '1';
			$price_val = $item['unit_price'] ?? '0';
			$tax_val   = $item['tax_rate'] ?? '0';

			$qty   = is_numeric( $qty_val ) ? (string) $qty_val : '0';
			$price = is_numeric( $price_val ) ? (string) $price_val : '0';
			$total = bcmul( $qty, $price, 4 );

			// Tax-exempt clients always store zero tax — override any rate that
			// was submitted by the frontend so the DB stays consistent.
			if ( $is_tax_exempt ) {
				$tax_rate   = '0';
				$tax_amount = '0.0000';
			} else {
				$tax_rate   = is_numeric( $tax_val ) ? (string) $tax_val : '0';
				$tax_amount = bcmul( $total, bcdiv( $tax_rate, '100', 4 ), 4 );
			}

			$ok = $this->line_items_db->insert( [
				'estimate_id' => $estimate_id,
				'description' => sanitize_text_field( $item['description'] ?? '' ),
				'quantity'    => $qty,
				'unit_price'  => $price,
				'total'       => $total,
				'sort_order'  => $sort++,
				'tax_rate'    => $tax_rate,
				'tax_amount'  => $tax_amount,
			] );
			if ( ! $ok ) {
				throw new \Exception( 'Failed to insert estimate line item.' );
			}
		}
	}

	private function generate_number( string $date ): string {
		global $wpdb;

		$year  = gmdate( 'Y', strtotime( $date ) ?: time() );
		$key   = "estimate_{$year}";
		$now   = gmdate( 'Y-m-d H:i:s' );
		$table = $wpdb->prefix . 'zinv_sequences';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$ok = $wpdb->query( $wpdb->prepare(
			"INSERT INTO {$table} (sequence_key, current_value, created_at, updated_at)
			 VALUES (%s, 1, %s, %s)
			 ON DUPLICATE KEY UPDATE
			   current_value = current_value + 1,
			   updated_at    = VALUES(updated_at)",
			$key, $now, $now
		) );

		if ( false === $ok ) {
			throw new \Exception( 'Failed to allocate estimate number.' );
		}

		$next = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT current_value FROM {$table} WHERE sequence_key = %s",
			$key
		) );

		// Prefix from the Invoicing settings; trailing dash normalised so the
		// separator is always exactly one dash.
		$prefix = rtrim( (string) ZINV_Settings::get( 'estimate_prefix', 'EST-' ), '-' );
		if ( '' === $prefix ) {
			$prefix = 'EST';
		}

		return sprintf( '%s-%s-%03d', $prefix, $year, $next );
	}

	private function calculate_subtotal( array $items ): string {
		$sum = '0';
		foreach ( $items as $item ) {
			$qty   = (string) ( $item['quantity'] ?? '1' );
			$price = (string) ( $item['unit_price'] ?? '0' );
			$sum   = bcadd( $sum, bcmul( $qty, $price, 4 ), 4 );
		}
		return $sum;
	}

	private function calculate_subtotal_from_db( int $estimate_id ): string {
		global $wpdb;
		$table = $this->line_items_db->get_table_name();
		return (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(total), 0) FROM {$table} WHERE estimate_id = %d",
			$estimate_id
		) );
	}

	private function calculate_tax_total( array $items ): string {
		$sum = '0';
		foreach ( $items as $item ) {
			$qty      = (string) ( $item['quantity'] ?? '1' );
			$price    = (string) ( $item['unit_price'] ?? '0' );
			$total    = bcmul( $qty, $price, 4 );
			$tax_rate = (string) ( $item['tax_rate'] ?? '0' );
			$tax      = bcmul( $total, bcdiv( $tax_rate, '100', 4 ), 4 );
			$sum      = bcadd( $sum, $tax, 4 );
		}
		return $sum;
	}

	private function calculate_tax_total_from_db( int $estimate_id ): string {
		global $wpdb;
		$table = $this->line_items_db->get_table_name();
		return (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(tax_amount), 0) FROM {$table} WHERE estimate_id = %d",
			$estimate_id
		) );
	}

	private function calculate_discount_total( string $subtotal, ?string $type, string $value ): string {
		if ( empty( $type ) || bccomp( $value, '0', 4 ) <= 0 ) {
			return '0';
		}

		if ( 'percentage' === $type ) {
			$capped = bccomp( $value, '100', 4 ) > 0 ? '100' : $value;
			return bcmul( $subtotal, bcdiv( $capped, '100', 4 ), 4 );
		}

		// fixed: can't exceed subtotal
		return bccomp( $value, $subtotal, 4 ) > 0 ? $subtotal : $value;
	}

	private function validate_discount_type( string $type ): string {
		if ( '' === $type ) {
			return $type;
		}
		if ( ! in_array( $type, self::ALLOWED_DISCOUNT_TYPES, true ) ) {
			throw new \InvalidArgumentException( 'Invalid discount_type: ' . esc_html( $type ) );
		}
		return $type;
	}

	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;
	}
}


