<?php
/**
 * Invoice 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_Invoice {

	private ZINV_Database $db;
	private ZINV_Database $line_items_db;

	private const ALLOWED_STATUSES    = [ 'draft', 'sent', 'viewed', 'paid', 'partial', 'overdue', 'void', 'in_recovery' ];
	private const TERMINAL_STATUSES   = [ 'paid', 'void' ];
	private const ALLOWED_SORT_FIELDS = [ 'id', 'invoice_number', 'client_id', 'status', 'issue_date', 'due_date', 'total', 'amount_due', 'created_at' ];
	private const ALLOWED_DISCOUNT_TYPES = [ 'percentage', 'fixed' ];

	private const TRANSITIONS = [
		'draft'        => [ 'sent', 'void' ],
		'sent'         => [ 'viewed', 'paid', 'partial', 'overdue', 'void' ],
		'viewed'       => [ 'paid', 'partial', 'overdue', 'void' ],
		'partial'      => [ 'paid', 'overdue' ],
		'overdue'      => [ 'paid', 'partial', 'void', 'in_recovery' ],
		'in_recovery'  => [ 'paid', 'partial', 'void' ],
	];

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

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

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

		$max_attempts = 5;
		$attempts     = 0;

		while ( $attempts < $max_attempts ) {
			$attempts++;
			$duplicate_error = false;

			try {
				$invoice_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 );
			$amount_due = bccomp( $total, '0', 4 ) < 0 ? '0' : $total;

			// Recurring fields: when is_recurring=1, this invoice becomes a
			// template — the cron clones it on `recurring_next_run`. allowed
			// intervals match the dropdown in the UI; anything else falls back
			// to 'monthly' to avoid leaving a recurring row no cron can act on.
			$allowed_intervals = [ 'weekly', 'monthly', 'quarterly', 'yearly' ];
			$is_recurring      = ! empty( $data['is_recurring'] ) ? 1 : 0;
			$recurring_interval = null;
			$recurring_next_run = null;
			$recurring_count    = 0;
			if ( $is_recurring ) {
				$interval_raw       = isset( $data['recurring_interval'] ) ? sanitize_text_field( (string) $data['recurring_interval'] ) : 'monthly';
				$recurring_interval = in_array( $interval_raw, $allowed_intervals, true ) ? $interval_raw : 'monthly';
				$recurring_count    = isset( $data['recurring_count'] ) ? max( 0, (int) $data['recurring_count'] ) : 0;
				// Schedule first child run from the template's issue_date so the
				// child cycle aligns with what the admin entered, not the moment
				// the row was inserted.
				$base_dt            = ! empty( $data['issue_date'] ) ? strtotime( (string) $data['issue_date'] ) : time();
				$recurring_next_run = gmdate( 'Y-m-d H:i:s', self::advance_by_interval( $base_dt, $recurring_interval ) );
			}

			$issue_date_val = sanitize_text_field( $data['issue_date'] ?? $date );
			$due_date_val   = sanitize_text_field( $data['due_date'] ?? '' );
			if ( empty( $due_date_val ) && ! empty( $issue_date_val ) ) {
				$due_days     = (int) ( ( new ZINV_Settings() )->get( 'default_due_days' ) ?: 30 );
				$due_date_val = gmdate( 'Y-m-d', strtotime( "{$issue_date_val} +{$due_days} days" ) );
			}

			$insert = [
				'invoice_number'      => $invoice_number,
				'client_id'           => absint( $data['client_id'] ),
				'estimate_id'         => isset( $data['estimate_id'] ) ? absint( $data['estimate_id'] ) : null,
				'status'              => 'draft',
				'issue_date'          => $issue_date_val,
				'due_date'            => $due_date_val,
				'subtotal'            => $subtotal,
				'tax_total'           => $tax_total,
				'discount_total'      => $discount_total,
				'total'               => $total,
				'amount_paid'         => '0',
				'amount_due'          => $amount_due,
				'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,
				'footer_text'         => isset( $data['footer_text'] ) ? sanitize_text_field( $data['footer_text'] ) : null,
				'is_recurring'        => $is_recurring,
				'recurring_interval'  => $recurring_interval,
				'recurring_next_run'  => $recurring_next_run,
				'recurring_count'     => $recurring_count,
				'recurring_generated' => 0,
				'recurring_parent_id' => isset( $data['recurring_parent_id'] ) ? absint( $data['recurring_parent_id'] ) : null,
				'version'             => 1,
				'pdf_path'            => null,
				'created_at'          => $now,
				'updated_at'          => $now,
			];

			$invoice_id = $this->db->insert( $insert );
			if ( ! $invoice_id ) {
				$error = $this->db->get_last_error();
				if ( false !== stripos( $error, 'Duplicate entry' ) && false !== stripos( $error, 'invoice_number' ) ) {
					$wpdb->query( 'ROLLBACK' );
					continue;
				}
				throw new \Exception( 'Failed to insert invoice.' );
			}

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

			$wpdb->query( 'COMMIT' );

			// Activity log: free-tier audit trail.
			ZINV_Activity::log(
				'invoice.created',
				'invoice',
				$invoice_id,
				/* translators: %s: invoice number */
				sprintf( __( 'Invoice %s was created.', 'zoventic-invoices' ), $invoice_number )
			);

			return $invoice_id;

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

		return 0;
	}

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

		if ( ! $invoice ) {
			return null;
		}

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

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

		if ( in_array( $invoice['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 invoice row.
			$client_id_for_update = isset( $data['client_id'] )
				? absint( $data['client_id'] )
				: absint( $invoice['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) $invoice['subtotal'];
			$tax_total = $is_tax_exempt ? '0' : ( $refreshed ? $this->calculate_tax_total_from_db( $id ) : (string) $invoice['tax_total'] );

			$discount_type  = $data['discount_type'] ?? $invoice['discount_type'];
			$discount_value = isset( $data['discount_value'] ) ? (string) $data['discount_value'] : (string) $invoice['discount_value'];
			$discount_total = $this->calculate_discount_total( $subtotal, $discount_type, $discount_value );
			$total          = bcsub( bcadd( $subtotal, $tax_total, 4 ), $discount_total, 4 );
			$amount_due     = bcsub( $total, (string) $invoice['amount_paid'], 4 );
			// Clamp to zero using bccomp instead of max()
			$amount_due     = bccomp( $amount_due, '0', 4 ) < 0 ? '0' : $amount_due;

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

			$updatable = [
				'issue_date'     => 'sanitize_text_field',
				'due_date'       => 'sanitize_text_field',
				'notes'          => 'sanitize_textarea_field',
				'terms'          => 'sanitize_textarea_field',
				'footer_text'    => 'sanitize_text_field',
				'template'       => 'sanitize_text_field',
				'document_title' => 'sanitize_text_field',
				'discount_type'  => 'sanitize_text_field',
				'currency'       => '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'] );
			}

			// Recurring fields: handle toggle + interval/count edits. When the
			// admin disables recurring on an existing template, clear the
			// scheduling fields so the cron stops looking at it. When enabling,
			// require an interval and seed `recurring_next_run` from issue_date
			// (or now, whichever is later) so the next child generates on time.
			if ( array_key_exists( 'is_recurring', $data ) ) {
				$is_rec_now            = ! empty( $data['is_recurring'] ) ? 1 : 0;
				$fields['is_recurring'] = $is_rec_now;
				if ( ! $is_rec_now ) {
					$fields['recurring_interval'] = null;
					$fields['recurring_next_run'] = null;
				}
			}
			if ( array_key_exists( 'recurring_interval', $data ) ) {
				$allowed_intervals = [ 'weekly', 'monthly', 'quarterly', 'yearly' ];
				$raw               = sanitize_text_field( (string) $data['recurring_interval'] );
				$fields['recurring_interval'] = in_array( $raw, $allowed_intervals, true ) ? $raw : 'monthly';
				// Recompute next_run when interval changes so old cadence
				// doesn't linger.
				$issue_dt = isset( $fields['issue_date'] ) ? $fields['issue_date'] : $invoice['issue_date'];
				$base_dt  = $issue_dt ? strtotime( (string) $issue_dt ) : time();
				$fields['recurring_next_run'] = gmdate( 'Y-m-d H:i:s', self::advance_by_interval( $base_dt, $fields['recurring_interval'] ) );
			}
			if ( array_key_exists( 'recurring_count', $data ) ) {
				$fields['recurring_count'] = max( 0, (int) $data['recurring_count'] );
			}

			// 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' );
			do_action( 'zinv_invoice_updated', $id, $data );
			return true;

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

	public function void( int $id ): bool {
		$invoice = $this->get( $id );
		if ( ! $invoice || in_array( $invoice['status'], self::TERMINAL_STATUSES, true ) ) {
			return false;
		}

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

		return $this->transition_status( $id, 'void' );
	}

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

		$current_status = (string) $invoice['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 ) {
			// Look up the client + invoice for the email; either side may now be
			// stale (transition_status updated the row) so re-fetch.
			$client = null;
			if ( ! empty( $invoice['client_id'] ) ) {
				$client_crud = new ZINV_Client();
				$client      = $client_crud->get( (int) $invoice['client_id'] );
			}

			do_action( 'zinv_invoice_sent', $id, $invoice, $client );

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

			// Free-plugin fallback: if Pro is not active and the client has an
			// email, send a basic plain-text notification. Pro short-circuits
			// this via the `zinv_invoice_sent_handled` filter.
			$handled = apply_filters( 'zinv_invoice_sent_handled', false, $id, $invoice, $client );
			if ( ! $handled && $client && ! empty( $client['email'] ) ) {
				self::send_basic_invoice_email( $invoice, $client );
			}
		}

		return $ok;
	}

	/**
	 * Plain-text invoice notification used when the Pro emailer is not active.
	 *
	 * Keeps the body short: invoice number, due date, total, and a generic
	 * "log into the portal" line. The plugin's only PDF/portal links require
	 * REST auth so we don't try to embed them.
	 */
	private static function send_basic_invoice_email( array $invoice, array $client ): void {
		$to = (string) ( $client['email'] ?? '' );
		if ( '' === $to || ! is_email( $to ) ) {
			return;
		}

		// Ensure the Settings reader is loaded.
		if ( ! class_exists( 'ZINV_Settings' ) ) {
			require_once __DIR__ . '/class-settings.php';
		}

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

		// Get custom email subject & body from free settings.
		$custom_subject = ZINV_Settings::get( 'email_invoice_subject', '' );
		$custom_body    = ZINV_Settings::get( 'email_invoice_body', '' );

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

		$default_subject = __( 'Invoice {invoice_number} from {business_name}', 'zoventic-invoices' );
		$default_body    = __( "Dear {client_name},\n\nThank you for your business. We have issued invoice {invoice_number} for {amount}.\n\nInvoice Details:\n----------------------------------------\nInvoice Number: {invoice_number}\nTotal Amount:   {amount}\nDue Date:       {due_date}\n----------------------------------------\n\nA PDF copy of your invoice is attached to this email for your records.\n\nIf you have any questions, feel free to reply directly to this email.\n\nBest regards,\n{business_name}", 'zoventic-invoices' );

		$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 );

		// Get custom From Name & Email.
		$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' );

		// Strip CRLF from From components so a malicious blogname/setting can't inject headers.
		$from_name  = preg_replace( "/[\r\n]+/", ' ', (string) $from_name );
		$from_email = preg_replace( "/[\r\n]+/", '', (string) $from_email );
		$name_q     = '"' . str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $from_name ) . '"';

		// Strip CRLF from subject — template tag substitutions (e.g. {client_name},
		// {invoice_number}) could contain newlines from manually-inserted data.
		$subject = str_replace( [ "\r", "\n" ], ' ', $subject );
		$subject = trim( $subject );

		// Generate and attach PDF invoice when mPDF fonts are ready.
		$attachments = [];
		if ( ! empty( $invoice['id'] ) && class_exists( 'ZINV_PDF_Generator' ) && ZINV_PDF_Generator::fonts_ready() ) {
			try {
				$template = ! empty( $invoice['template'] ) ? $invoice['template'] : 'clean-sheet';
				$pdf_file = ZINV_PDF_Generator::generate( (int) $invoice['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 email PDF attachment failed for invoice ' . $invoice['id'] . ': ' . $e->getMessage() );
			}
		}

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

	/**
	 * Transition an invoice to a new status, enforcing the TRANSITIONS graph.
	 *
	 * Returns false when:
	 *  - the invoice does not exist
	 *  - the requested status is not in ALLOWED_STATUSES
	 *  - the current → requested edge is not in TRANSITIONS
	 */
	public function transition_status( int $id, string $new_status, array $extra = [] ): bool {
		if ( ! in_array( $new_status, self::ALLOWED_STATUSES, true ) ) {
			return false;
		}

		$invoice = $this->get( $id );
		if ( ! $invoice ) {
			return false;
		}

		$current = (string) $invoice['status'];
		// Same-state is a no-op success — caller may not have checked first.
		if ( $current === $new_status ) {
			return true;
		}

		$allowed_next = self::TRANSITIONS[ $current ] ?? [];
		if ( ! in_array( $new_status, $allowed_next, true ) ) {
			return false;
		}

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

		if ( 'paid' === $new_status ) {
			$fields['paid_at'] = $now;
		}

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

		// Activity log: free-tier audit trail — only on meaningful transitions.
		if ( false !== $result && $current !== $new_status && 'paid' === $new_status ) {
			ZINV_Activity::log(
				'invoice.paid',
				'invoice',
				$id,
				/* translators: %s: invoice number */
				sprintf( __( 'Invoice %s has been fully paid.', 'zoventic-invoices' ), $invoice['invoice_number'] ?? '' )
			);
		}

		if ( false !== $result ) {
			do_action( 'zinv_invoice_status_changed', $id, $new_status, $current );
		}

		return false !== $result;
	}

	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[]      = "(i.invoice_number LIKE %s OR i.document_title LIKE %s OR i.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[]      = "i.status = %s";
			$where_args[] = sanitize_text_field( $args['status'] );
		}

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

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

		if ( ! empty( $args['date_to'] ) ) {
			$where[]      = "i.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';

		$max = isset( $args['_max_per_page'] ) ? (int) $args['_max_per_page'] : 100;

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

	public function get_line_items( int $invoice_id ): array {
		global $wpdb;
		$table = $this->line_items_db->get_table_name();
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$table} WHERE invoice_id = %d ORDER BY sort_order ASC, id ASC",
			$invoice_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;
	}

	/**
	 * Persist line items for an invoice.
	 *
	 * 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   $invoice_id    The parent invoice 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 $invoice_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, [ 'invoice_id' => $invoice_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( [
				'invoice_id'  => $invoice_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 line item.' );
			}
		}
	}

	/**
	 * Atomic sequence allocation. Single statement so concurrent invoice creates
	 * cannot race — relies on UNIQUE KEY (sequence_key) + LAST_INSERT_ID() trick.
	 *
	 * After the query, LAST_INSERT_ID() returns the new current_value (set via the
	 * expression in ON DUPLICATE KEY UPDATE). MySQL/MariaDB documents this pattern
	 * explicitly for atomic counters.
	 */
	private function generate_number( string $date ): string {
		global $wpdb;

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
		$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 invoice number.' );
		}

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

	/**
	 * Build the display invoice number from the configured prefix + starting
	 * number. The prefix and starting number come from the Invoicing settings;
	 * a trailing dash on the prefix is normalised so the separator is always
	 * exactly one dash.
	 *
	 * @param string $year     Four-digit year.
	 * @param int    $sequence Raw sequence value (1-based) for this year.
	 */
	private static function format_number( string $year, int $sequence ): string {
		$prefix = (string) ZINV_Settings::get( 'invoice_prefix', 'INV-' );
		$prefix = rtrim( $prefix, '-' );
		if ( '' === $prefix ) {
			$prefix = 'INV';
		}

		// The starting number offsets the first invoice: sequence 1 should
		// render as the configured start (default 1).
		$start  = (int) ZINV_Settings::get( 'invoice_starting_number', 1 );
		$number = max( 1, $start ) + $sequence - 1;

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

	/**
	 * Best-effort preview. Caller MUST treat this as advisory — actual number is
	 * only assigned at create() time and may differ if another invoice is created
	 * concurrently.
	 */
	public function peek_number( string $date = '' ): string {
		global $wpdb;
		$year = $date ? gmdate( 'Y', strtotime( $date ) ?: time() ) : gmdate( 'Y' );
		$key  = "invoice_{$year}";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$current = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT current_value FROM {$wpdb->prefix}zinv_sequences WHERE sequence_key = %s",
			$key
		) );
		return self::format_number( $year, $current + 1 );
	}

	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 $invoice_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 invoice_id = %d",
			$invoice_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 $invoice_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 invoice_id = %d",
			$invoice_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;
	}

	public function set_pdf_path( int $id, string $relative_path ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->update(
			$this->db->get_table_name(),
			[ 'pdf_path' => $relative_path, 'updated_at' => gmdate( 'Y-m-d H:i:s' ) ],
			[ 'id' => $id ],
			[ '%s', '%s' ],
			[ '%d' ]
		);
	}

	public function clear_pdf_path( int $id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->update(
			$this->db->get_table_name(),
			[ 'pdf_path' => null, 'updated_at' => gmdate( 'Y-m-d H:i:s' ) ],
			[ 'id' => $id ],
			[ '%s', '%s' ],
			[ '%d' ]
		);
	}

	private function validate_discount_type( string $type ): string {
		// Reject unknown types rather than silently coercing to 'fixed' (which
		// keeps the discount_value but applies it under a different math path).
		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;
	}

	// -------------------------------------------------------------------------
	// Recurring invoice helpers
	// -------------------------------------------------------------------------

	/**
	 * Advance a unix timestamp by one recurring interval. Used both when first
	 * scheduling a template (issue_date + interval) and when bumping a
	 * template's next_run after a child is generated.
	 *
	 * @param int    $from_ts  Base unix timestamp.
	 * @param string $interval weekly|monthly|quarterly|yearly
	 * @return int             Unix timestamp of the next run.
	 */
	public static function advance_by_interval( int $from_ts, string $interval ): int {
		$map = [
			'weekly'    => '+1 week',
			'monthly'   => '+1 month',
			'quarterly' => '+3 months',
			'yearly'    => '+1 year',
		];
		$expr = $map[ $interval ] ?? '+1 month';
		$next = strtotime( $expr, $from_ts );
		return $next !== false ? $next : ( $from_ts + DAY_IN_SECONDS * 30 );
	}

	/**
	 * Cron-driven: find recurring templates whose `recurring_next_run` is due,
	 * clone each into a fresh child invoice, then advance the template's next
	 * run. Idempotent: a SELECT…NOT EXISTS join blocks double-issuance if two
	 * cron ticks race on the same template.
	 *
	 * Templates with `recurring_count > 0` stop generating once
	 * `recurring_generated >= recurring_count`. recurring_count = 0 means
	 * "generate forever" (admin cancels by toggling is_recurring off).
	 *
	 * @return int Number of child invoices generated this run.
	 */
	public static function process_recurring_due(): int {
		global $wpdb;

		$now_dt = gmdate( 'Y-m-d H:i:s' );
		// Batch cap so a huge backlog doesn't blow the cron window. Subsequent
		// ticks pick up the remainder.
		$batch_limit = (int) apply_filters( 'zinv_recurring_batch_limit', 50 );

		$prefix = $wpdb->prefix;
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT id, recurring_interval, recurring_count, recurring_generated
			   FROM {$prefix}zinv_invoices
			  WHERE is_recurring = 1
			    AND recurring_next_run IS NOT NULL
			    AND recurring_next_run <= %s
			    AND ( recurring_count = 0 OR recurring_generated < recurring_count )
			  LIMIT %d",
			$now_dt,
			$batch_limit
		), ARRAY_A ) ?: [];

		if ( empty( $rows ) ) {
			return 0;
		}

		$created = 0;
		$factory = new self();

		foreach ( $rows as $tpl_row ) {
			$tpl_id = (int) $tpl_row['id'];
			$tpl    = $factory->get( $tpl_id );
			if ( ! $tpl ) {
				continue;
			}

			// Clone payload. Reset status/dates so child looks like a fresh
			// invoice issued today; carry over discount, currency, template,
			// notes/terms verbatim. recurring_parent_id links back to template.
			$today    = gmdate( 'Y-m-d' );
			$due_off  = (int) apply_filters( 'zinv_recurring_due_offset_days', 14 );
			$due_date = gmdate( 'Y-m-d', strtotime( "+{$due_off} days" ) );

			$line_items = [];
			foreach ( (array) ( $tpl['line_items'] ?? [] ) as $li ) {
				$line_items[] = [
					'description' => (string) ( $li['description'] ?? '' ),
					'quantity'    => (string) ( $li['quantity']    ?? '1' ),
					'unit_price'  => (string) ( $li['unit_price']  ?? '0' ),
					'tax_rate'    => (string) ( $li['tax_rate']    ?? '0' ),
				];
			}

			$child_id = $factory->create( [
				'client_id'           => (int) $tpl['client_id'],
				'issue_date'          => $today,
				'due_date'            => $due_date,
				'currency'            => (string) $tpl['currency'],
				'discount_type'       => $tpl['discount_type'],
				'discount_value'      => (string) $tpl['discount_value'],
				'template'            => (string) $tpl['template'],
				'notes'               => $tpl['notes'],
				'terms'               => $tpl['terms'],
				'footer_text'         => $tpl['footer_text'],
				'is_recurring'        => 0, // child is a normal invoice, not another template
				'recurring_parent_id' => $tpl_id,
			], $line_items );

			if ( ! $child_id ) {
				// Log and skip — don't advance next_run on failure so the next
				// cron tick will retry.
				error_log( '[ZINV_Invoice] recurring: failed to clone template #' . $tpl_id ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
				continue;
			}

			$interval = (string) ( $tpl_row['recurring_interval'] ?? 'monthly' );
			$next_run = gmdate( 'Y-m-d H:i:s', self::advance_by_interval( time(), $interval ) );

			$wpdb->update(
				$prefix . 'zinv_invoices',
				[
					'recurring_next_run'  => $next_run,
					'recurring_generated' => (int) $tpl_row['recurring_generated'] + 1,
					'updated_at'          => gmdate( 'Y-m-d H:i:s' ),
				],
				[ 'id' => $tpl_id ],
				[ '%s', '%d', '%s' ],
				[ '%d' ]
			);

			do_action( 'zinv_recurring_invoice_generated', $tpl_id, $child_id );
			$created++;
		}

		return $created;
	}
}


