<?php
/**
 * Invoice Series Store
 *
 * Manages invoice series for both normal invoices and rectificativas.
 * Each series has its own counter, prefix, suffix, and padding settings.
 *
 * @package Freefactu
 */

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

/**
 * Class Freefactu_Series_Store
 *
 * Handles CRUD operations for invoice series and number generation.
 */
class Freefactu_Series_Store {

	/** @var string Table name with WordPress prefix */
	private static $table_name = null;

	/**
	 * Get the series table name with WordPress prefix.
	 *
	 * @return string Table name with prefix.
	 */
	public static function get_table_name() {
		global $wpdb;
		if ( self::$table_name === null ) {
			self::$table_name = $wpdb->prefix . 'freefactu_series';
		}
		return self::$table_name;
	}

	/**
	 * Check if the series table exists.
	 *
	 * @return bool True if table exists, false otherwise.
	 */
	public static function table_exists() {
		global $wpdb;
		$table_name = self::get_table_name();
		return $wpdb->get_var( $wpdb->prepare( "SHOW TABLES LIKE %s", $table_name ) ) === $table_name; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Create the series database table.
	 *
	 * @return void
	 */
	public static function create_table() {
		global $wpdb;
		$table_name = self::get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE $table_name (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			name varchar(100) NOT NULL,
			prefix varchar(50) DEFAULT '' NOT NULL,
			suffix varchar(50) DEFAULT '' NOT NULL,
			padding int(2) DEFAULT 0 NOT NULL,
			type varchar(20) NOT NULL,
			next_number bigint(20) DEFAULT 1 NOT NULL,
			is_active tinyint(1) DEFAULT 0 NOT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			PRIMARY KEY  (id),
			KEY type (type),
			KEY is_active (is_active)
		) $charset_collate;";

		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $sql );
	}

	/**
	 * Create a new series.
	 *
	 * @param array $data {
	 *     @type string $name    Display name for the series.
	 *     @type string $prefix  Invoice number prefix.
	 *     @type string $suffix  Invoice number suffix.
	 *     @type int    $padding Zero padding length.
	 *     @type string $type    Series type: 'normal' or 'rectificativa'.
	 *     @type int    $next_number Starting number (default: 1).
	 * }
	 * @return int|WP_Error Insert ID on success, WP_Error on failure.
	 */
	public static function create_series( $data ) {
		global $wpdb;
		$table_name = self::get_table_name();

		// Validate required fields
		if ( empty( $data['name'] ) || empty( $data['type'] ) ) {
			return new WP_Error( 'missing_required_fields', __( 'Name and type are required.', 'freefactu' ) );
		}

		// Validate type
		if ( ! in_array( $data['type'], array( 'normal', 'rectificativa' ), true ) ) {
			return new WP_Error( 'invalid_type', __( 'Type must be "normal" or "rectificativa".', 'freefactu' ) );
		}

		// Check for duplicate series (same prefix + suffix + type)
		$prefix = isset( $data['prefix'] ) ? sanitize_text_field( $data['prefix'] ) : '';
		$suffix = isset( $data['suffix'] ) ? sanitize_text_field( $data['suffix'] ) : '';
		if ( self::series_exists( $prefix, $suffix, $data['type'] ) ) {
			return new WP_Error( 
				'duplicate_series', 
				__( 'Ya existe una serie con el mismo prefijo y sufijo.', 'freefactu' ) 
			);
		}

		// Check if this is the first series of this type - if so, make it active
		$existing_count = self::count_series_by_type( $data['type'] );
		$is_active = $existing_count === 0 ? 1 : 0;

		$result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$table_name,
			array(
				'name'        => sanitize_text_field( $data['name'] ),
				'prefix'      => isset( $data['prefix'] ) ? sanitize_text_field( $data['prefix'] ) : '',
				'suffix'      => isset( $data['suffix'] ) ? sanitize_text_field( $data['suffix'] ) : '',
				'padding'     => isset( $data['padding'] ) ? absint( $data['padding'] ) : 0,
				'type'        => $data['type'],
				'next_number' => isset( $data['next_number'] ) ? absint( $data['next_number'] ) : 1,
				'is_active'   => $is_active,
				'created_at'  => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%d', '%s', '%d', '%d', '%s' )
		);

		if ( false === $result ) {
			$error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
			Freefactu_Logger::log(
				sprintf( 'Failed to create series "%s": %s', $data['name'], $error_msg ),
				'error'
			);
			return new WP_Error( 'db_insert_error', __( 'Failed to create series.', 'freefactu' ), array( 'wpdb_error' => $error_msg ) );
		}

		$series_id = $wpdb->insert_id;

		self::clean_cache( $data['type'] );

		Freefactu_Logger::log(
			sprintf( 'Created new series: %s (ID: %d, Type: %s, Active: %s)', $data['name'], $series_id, $data['type'], $is_active ? 'Yes' : 'No' ),
			'info'
		);

		return $series_id;
	}

	/**
	 * Get series by ID.
	 *
	 * @param int $id Series ID.
	 * @return object|null Series object or null if not found.
	 */
	public static function get_series( $id ) {
		global $wpdb;
		$table_name = esc_sql( self::get_table_name() );
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $table_name WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get all series by type.
	 *
	 * @param string $type Series type: 'normal' or 'rectificativa'.
	 * @return array Array of series objects.
	 */
	public static function get_series_by_type( $type ) {
		global $wpdb;

		$cache_key = 'series_by_type_' . $type;
		$series    = wp_cache_get( $cache_key, 'freefactu_series' );

		if ( false === $series ) {
			$table_name = esc_sql( self::get_table_name() );
			$series     = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT * FROM {$table_name} WHERE type = %s ORDER BY is_active DESC, created_at ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$type
			) );
			wp_cache_set( $cache_key, $series, 'freefactu_series' );
		}

		return $series;
	}

	/**
	 * Get the active series for a type.
	 *
	 * @param string $type Series type: 'normal' or 'rectificativa'.
	 * @return object|null Active series object or null if none active.
	 */
	public static function get_active_series( $type ) {
		global $wpdb;

		$cache_key = 'active_series_' . $type;
		$series    = wp_cache_get( $cache_key, 'freefactu_series' );

		if ( false === $series ) {
			$table_name = esc_sql( self::get_table_name() );
			$series     = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT * FROM {$table_name} WHERE type = %s AND is_active = 1 LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$type
			) );
			wp_cache_set( $cache_key, $series, 'freefactu_series' );
		}

		return $series;
	}

	/**
	 * Set a series as active (deactivates others of the same type).
	 *
	 * @param int $series_id Series ID to activate.
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public static function set_active_series( $series_id ) {
		global $wpdb;
		$table_name = self::get_table_name();

		// Get series to validate and determine type
		$series = self::get_series( $series_id );
		if ( ! $series ) {
			return new WP_Error( 'series_not_found', __( 'Series not found.', 'freefactu' ) );
		}

		// Start transaction
		$wpdb->query( 'START TRANSACTION' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching


		// Deactivate all series of the same type
		$result1 = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$table_name,
			array( 'is_active' => 0 ),
			array( 'type' => $series->type ),
			array( '%d' ),
			array( '%s' )
		);

		// Activate the specified series
		$result2 = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$table_name,
			array( 'is_active' => 1 ),
			array( 'id' => $series_id ),
			array( '%d' ),
			array( '%d' )
		);

		if ( false === $result1 || false === $result2 ) {
			$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return new WP_Error( 'db_update_error', __( 'Failed to update active series.', 'freefactu' ) );
		}

		$wpdb->query( 'COMMIT' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		self::clean_cache( $series->type );

		Freefactu_Logger::log(
			sprintf( 'Activated series ID %d (%s) for type: %s', $series_id, $series->name, $series->type ),
			'info'
		);

		return true;
	}

	/**
	 * Count series by type.
	 *
	 * @param string $type Series type: 'normal' or 'rectificativa'.
	 * @return int Count of series.
	 */
	public static function count_series_by_type( $type ) {
		global $wpdb;

		$cache_key = 'count_series_' . $type;
		$count     = wp_cache_get( $cache_key, 'freefactu_series' );

		if ( false === $count ) {
			$table_name = esc_sql( self::get_table_name() );
			$count      = (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				"SELECT COUNT(*) FROM {$table_name} WHERE type = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$type
			) );
			wp_cache_set( $cache_key, $count, 'freefactu_series' );
		}

		return $count;
	}

	/**
	 * Check if a series with the same prefix, suffix, and type already exists.
	 *
	 * @param string $prefix Series prefix.
	 * @param string $suffix Series suffix.
	 * @param string $type   Series type: 'normal' or 'rectificativa'.
	 * @return bool True if exists, false otherwise.
	 */
	public static function series_exists( $prefix, $suffix, $type ) {
		global $wpdb;
		$table_name = esc_sql( self::get_table_name() );
		return (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			"SELECT COUNT(*) FROM {$table_name} WHERE prefix = %s AND suffix = %s AND type = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$prefix,
			$suffix,
			$type
		) ) > 0;
	}

	/**
	 * Increment and return the next number for a series.
	 *
	 * This is an atomic operation that increments the counter and returns the value.
	 *
	 * @param int $series_id Series ID.
	 * @return int|WP_Error The number that was consumed, or WP_Error on failure.
	 */
	public static function increment( $series_id ) {
		global $wpdb;
		$table_name = esc_sql( self::get_table_name() );

		// Lock the row for update
		$series = $wpdb->get_row( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			"SELECT * FROM {$table_name} WHERE id = %d FOR UPDATE", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$series_id
		) );

		if ( ! $series ) {
			return new WP_Error( 'series_not_found', __( 'Series not found.', 'freefactu' ) );
		}

		$current_number = $series->next_number;

		// Increment the counter
		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$table_name,
			array( 'next_number' => $current_number + 1 ),
			array( 'id' => $series_id ),
			array( '%d' ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error( 'db_update_error', __( 'Failed to increment series number.', 'freefactu' ) );
		}

		self::clean_cache( $series->type );

		return $current_number;
	}

	/**
	 * Clean cache for a series type.
	 *
	 * @param string $type Series type.
	 */
	private static function clean_cache( $type ) {
		wp_cache_delete( 'series_by_type_' . $type, 'freefactu_series' );
		wp_cache_delete( 'active_series_' . $type, 'freefactu_series' );
		wp_cache_delete( 'count_series_' . $type, 'freefactu_series' );
	}

	/**
	 * Get formatted invoice number for a series.
	 *
	 * Applies prefix, suffix, padding, and date placeholders.
	 *
	 * @param object      $series The series object.
	 * @param int         $number The raw invoice number.
	 * @param WC_Order|null $order  Optional order for date placeholders.
	 * @return string Formatted invoice number.
	 */
	public static function format_invoice_number( $series, $number, $order = null ) {
		$prefix = $series->prefix;
		$suffix = $series->suffix;
		$padding = (int) $series->padding;

		// Get date for placeholders
		// Use native DateTime instead of WC_DateTime to avoid WooCommerce dependency
		// for basic date formatting (Y, m, d placeholders)
		if ( $order && $order instanceof WC_Order ) {
			$wc_date = $order->get_date_created();
			// Convert WC_DateTime to native DateTime or fallback to current time
			if ( $wc_date ) {
				$date = new DateTime( $wc_date->format( 'Y-m-d H:i:s' ) );
			} else {
				$date = new DateTime();
			}
		} else {
			$date = new DateTime();
		}

		// Replace date placeholders
		$replacements = array(
			'[year]'  => $date->format( 'Y' ),
			'[month]' => $date->format( 'm' ),
			'[day]'   => $date->format( 'd' ),
		);

		$prefix = str_replace( array_keys( $replacements ), array_values( $replacements ), $prefix );
		$suffix = str_replace( array_keys( $replacements ), array_values( $replacements ), $suffix );

		// Pad number
		$number_str = (string) $number;
		if ( $padding > 0 ) {
			$number_str = str_pad( $number_str, $padding, '0', STR_PAD_LEFT );
		}

		return $prefix . $number_str . $suffix;
	}

	/**
	 * Ensure default series exist for both normal and rectificativa invoices.
	 *
	 * Creates default series if none exist. Called during plugin activation.
	 *
	 * @return void
	 */
	public static function ensure_default_series() {
		// Check if we already have series
		if ( self::count_series_by_type( 'normal' ) > 0 && self::count_series_by_type( 'rectificativa' ) > 0 ) {
			Freefactu_Logger::log( 'Default series already exist.', 'debug' );
			return;
		}

		// Create default normal invoice series if needed
		if ( self::count_series_by_type( 'normal' ) === 0 ) {
			$options = get_option( 'freefactu_settings' );
			
			$normal_prefix = isset( $options['invoice_number_prefix'] ) ? $options['invoice_number_prefix'] : '';
			$normal_suffix = isset( $options['invoice_number_suffix'] ) ? $options['invoice_number_suffix'] : '';
			$normal_padding = isset( $options['invoice_number_padding'] ) ? absint( $options['invoice_number_padding'] ) : 6;

			self::create_series( array(
				'name'        => __( 'Serie Principal', 'freefactu' ),
				'prefix'      => $normal_prefix,
				'suffix'      => $normal_suffix,
				'padding'     => $normal_padding,
				'type'        => 'normal',
				'next_number' => 1,
			) );
			
			Freefactu_Logger::log( 'Created default normal invoice series.', 'info' );
		}

		// Create default rectificativa series if needed
		if ( self::count_series_by_type( 'rectificativa' ) === 0 ) {
			$options = get_option( 'freefactu_settings' );
			
			$rect_prefix = isset( $options['rectificativa_prefix'] ) ? $options['rectificativa_prefix'] : 'R-';
			$rect_suffix = isset( $options['rectificativa_suffix'] ) ? $options['rectificativa_suffix'] : '';
			$rect_padding = isset( $options['rectificativa_padding'] ) ? absint( $options['rectificativa_padding'] ) : 6;

			self::create_series( array(
				'name'        => __( 'Serie Rectificativas', 'freefactu' ),
				'prefix'      => $rect_prefix,
				'suffix'      => $rect_suffix,
				'padding'     => $rect_padding,
				'type'        => 'rectificativa',
				'next_number' => 1,
			) );
			
			Freefactu_Logger::log( 'Created default rectificativa series.', 'info' );
		}
	}

	/**
	 * Delete a series if it has no invoices.
	 *
	 * @param int $series_id Series ID.
	 * @return bool|WP_Error True on success, WP_Error if series has invoices or on failure.
	 */
	public static function delete_series( $series_id ) {
		global $wpdb;
		$table_name = self::get_table_name();

		$series = self::get_series( $series_id );
		if ( ! $series ) {
			return new WP_Error( 'series_not_found', __( 'Series not found.', 'freefactu' ) );
		}

		// Check if series has invoices
		$invoice_count = self::count_invoices_for_series( $series_id );
		if ( $invoice_count > 0 ) {
			return new WP_Error(
				'series_has_invoices',
				/* translators: %d: Number of invoices */
				sprintf( __( 'Cannot delete series with %d invoices.', 'freefactu' ), $invoice_count )
			);
		}

		// Cannot delete active series
		if ( $series->is_active ) {
			return new WP_Error( 'cannot_delete_active', __( 'Cannot delete the active series.', 'freefactu' ) );
		}

		$result = $wpdb->delete( $table_name, array( 'id' => $series_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		self::clean_cache( $series->type );

		if ( false === $result ) {
			return new WP_Error( 'db_delete_error', __( 'Failed to delete series.', 'freefactu' ) );
		}

		Freefactu_Logger::log(
			sprintf( 'Deleted series ID %d (%s)', $series_id, $series->name ),
			'info'
		);

		return true;
	}

	/**
	 * Count invoices for a series.
	 *
	 * Since we don't store series_id in the invoices table, we check if the series
	 * has been used by seeing if next_number > 1 (meaning at least one number was consumed).
	 *
	 * @param int $series_id Series ID.
	 * @return int Count of invoices (estimated from next_number - 1).
	 */
	public static function count_invoices_for_series( $series_id ) {
		$series = self::get_series( $series_id );
		if ( ! $series ) {
			return 0;
		}
		
		// If next_number is greater than 1, the series has been used
		// The count is next_number - 1 (number of consumed numbers)
		return max( 0, (int) $series->next_number - 1 );
	}

	/**
	 * Get preview of the next invoice number for a series.
	 *
	 * @param object $series Series object.
	 * @return string Preview of the next formatted invoice number.
	 */
	public static function get_preview( $series ) {
		return self::format_invoice_number( $series, $series->next_number, null );
	}
}
