<?php

namespace Kitgenix\PDF_Invoicing\Modules\Invoicing;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
// -- Every query here targets this plugin's own fixed table name, built from
// $wpdb->prefix and never from user input, via get_table_name(); every actual
// value is passed through $wpdb->prepare(). This is a plugin-owned custom table
// with no WordPress core object-cache group (its existence check is already
// explicitly cached above, in table_exists()); the atomic upsert-increment
// queries themselves must run fresh every call, so caching them would defeat
// the whole point of this class.

/**
 * Atomic document numbering sequences.
 *
 * Uses a dedicated table with a UNIQUE (doc_type, period_key) key and the
 * MySQL/MariaDB `INSERT ... ON DUPLICATE KEY UPDATE counter = LAST_INSERT_ID(counter + 1)`
 * pattern. That statement takes a row lock for its duration, so concurrent
 * requests (two staff members, or a customer download racing a cron batch)
 * are serialized by the database instead of racing in PHP – a plain
 * `get_option()` → increment → `update_option()` round trip cannot guarantee
 * that, because two requests can read the same value before either writes it
 * back (last write wins, silently dropping one increment).
 *
 * Falls back to the legacy option-based counter (non-atomic) only if the
 * table is unavailable, e.g. a host that denies CREATE TABLE to the DB user.
 */
final class SequenceManager {

    private const LEGACY_OPTION_KEY = 'kitgenix_pdf_invoicing_for_woocommerce_number_sequences';

    public static function get_table_name(): string {
        global $wpdb;

        return $wpdb->prefix . 'kitgenix_pdf_sequences';
    }

    public static function table_exists(): bool {
        global $wpdb;

        $table = self::get_table_name();
        $cached = wp_cache_get( 'kitgenix_pdf_sequences_table_exists', 'kitgenix-pdf-invoicing' );
        if ( is_bool( $cached ) ) {
            return $cached;
        }

        $found = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) )
        );

        $exists = ( $found === $table );
        wp_cache_set( 'kitgenix_pdf_sequences_table_exists', $exists, 'kitgenix-pdf-invoicing', MINUTE_IN_SECONDS );

        return $exists;
    }

    public static function create_table(): void {
        global $wpdb;

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

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            doc_type VARCHAR(40) NOT NULL,
            period_key VARCHAR(40) NOT NULL,
            counter BIGINT UNSIGNED NOT NULL DEFAULT 0,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY doc_period (doc_type, period_key)
        ) {$charset_collate};";

        dbDelta( $sql );

        wp_cache_delete( 'kitgenix_pdf_sequences_table_exists', 'kitgenix-pdf-invoicing' );
    }

    /**
     * Atomically return the next counter value for a (type, period) pair.
     *
     * @param string $type       Document type slug.
     * @param string $period_key Period bucket, e.g. "global", "year-2026".
     */
    public static function next( string $type, string $period_key ): int {
        $use_table = (bool) apply_filters( 'kitgenix_pdf_sequence_use_table', true, $type, $period_key );

        if ( $use_table && self::table_exists() ) {
            $value = self::next_from_table( $type, $period_key );
            if ( null !== $value ) {
                return $value;
            }
        }

        return self::next_from_legacy_option( $type, $period_key );
    }

    /**
     * Read-only peek at the current counter (does not increment). Used by
     * migrations to seed the table without consuming a number.
     */
    public static function peek( string $type, string $period_key ): int {
        if ( self::table_exists() ) {
            global $wpdb;
            $table = self::get_table_name();

            $value = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT counter FROM {$table} WHERE doc_type = %s AND period_key = %s",
                    $type,
                    $period_key
                )
            );

            if ( null !== $value ) {
                return (int) $value;
            }
        }

        $counters = self::get_legacy_counters();

        return isset( $counters[ $type ][ $period_key ] ) ? (int) $counters[ $type ][ $period_key ] : 0;
    }

    /**
     * Seed (or raise) the table counter for a (type, period) pair without
     * going through the normal atomic-increment path. Only ever raises the
     * stored value – never lowers it – so re-running a migration is safe.
     */
    public static function seed_at_least( string $type, string $period_key, int $minimum ): void {
        if ( $minimum < 1 ) {
            return;
        }

        if ( ! self::table_exists() ) {
            self::create_table();
        }

        if ( ! self::table_exists() ) {
            return;
        }

        global $wpdb;
        $table = self::get_table_name();
        $now   = current_time( 'mysql', true );

        $wpdb->query(
            $wpdb->prepare(
                "INSERT INTO {$table} (doc_type, period_key, counter, updated_at) VALUES (%s, %s, %d, %s)
                 ON DUPLICATE KEY UPDATE counter = GREATEST(counter, VALUES(counter)), updated_at = VALUES(updated_at)",
                $type,
                $period_key,
                $minimum,
                $now
            )
        );
    }

    private static function next_from_table( string $type, string $period_key ): ?int {
        global $wpdb;
        $table = self::get_table_name();
        $now   = current_time( 'mysql', true );

        // Atomic upsert-increment: the UPDATE branch takes a row lock for the
        // duration of the statement, so concurrent callers are serialized by
        // MySQL/MariaDB rather than racing in PHP.
        $result = $wpdb->query(
            $wpdb->prepare(
                "INSERT INTO {$table} (doc_type, period_key, counter, updated_at) VALUES (%s, %s, 1, %s)
                 ON DUPLICATE KEY UPDATE counter = LAST_INSERT_ID(counter + 1), updated_at = VALUES(updated_at)",
                $type,
                $period_key,
                $now
            )
        );

        if ( false === $result ) {
            return null;
        }

        $insert_id = (int) $wpdb->insert_id;

        return $insert_id > 0 ? $insert_id : null;
    }

    private static function next_from_legacy_option( string $type, string $period_key ): int {
        // Non-atomic fallback only used when the sequences table could not
        // be created. Documented as a known limitation in that scenario.
        $counters = self::get_legacy_counters();
        $current  = isset( $counters[ $type ][ $period_key ] ) ? (int) $counters[ $type ][ $period_key ] : 0;
        $next     = $current + 1;

        if ( ! isset( $counters[ $type ] ) || ! is_array( $counters[ $type ] ) ) {
            $counters[ $type ] = [];
        }

        $counters[ $type ][ $period_key ] = $next;
        update_option( self::LEGACY_OPTION_KEY, $counters, false );

        return $next;
    }

    /**
     * @return array<string,array<string,int>>
     */
    public static function get_legacy_counters(): array {
        $counters = get_option( self::LEGACY_OPTION_KEY, [] );

        return is_array( $counters ) ? $counters : [];
    }
}
