<?php
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * @return int Počet měsíců, po kterých se zaplacené objednávky přesunou do archivu.
 */
function elementsell_get_archive_retention_months() {
    return (int) apply_filters( 'elementsell_archive_retention_months', 18 );
}

/**
 * @return int
 */
function elementsell_get_archive_batch_size() {
    return (int) apply_filters( 'elementsell_archive_batch_size', 150 );
}

/**
 * @return bool
 */
function elementsell_orders_archive_table_exists() {
    global $wpdb;

    $table = $wpdb->prefix . 'elementsell_orders_archive';

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

/**
 * @return string
 */
function elementsell_get_orders_archive_table_name() {
    global $wpdb;

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

/**
 * @return string
 */
function elementsell_get_order_fields_archive_table_name() {
    global $wpdb;

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

/**
 * @return string
 */
function elementsell_get_invoices_archive_table_name() {
    global $wpdb;

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

/**
 * @param int $order_id
 * @return bool
 */
function elementsell_is_order_archived( $order_id ) {
    global $wpdb;

    if ( ! elementsell_orders_archive_table_exists() || (int) $order_id <= 0 ) {
        return false;
    }

    $table = elementsell_get_orders_archive_table_name();

    return (bool) $wpdb->get_var(
        $wpdb->prepare( "SELECT id FROM {$table} WHERE id = %d LIMIT 1", (int) $order_id )
    );
}

/**
 * @param int $order_id
 * @return string
 */
function elementsell_get_orders_storage_table_for_id( $order_id ) {
    global $wpdb;

    return elementsell_is_order_archived( $order_id )
        ? elementsell_get_orders_archive_table_name()
        : $wpdb->prefix . 'elementsell_orders';
}

/**
 * @param int $invoice_id
 * @return string
 */
function elementsell_get_invoices_storage_table_for_id( $invoice_id ) {
    global $wpdb;

    if ( ! elementsell_orders_archive_table_exists() || (int) $invoice_id <= 0 ) {
        return elementsell_get_invoices_table_name();
    }

    $archive = elementsell_get_invoices_archive_table_name();

    $in_archive = $wpdb->get_var(
        $wpdb->prepare( "SELECT id FROM {$archive} WHERE id = %d LIMIT 1", (int) $invoice_id )
    );

    return $in_archive ? $archive : elementsell_get_invoices_table_name();
}

/**
 * @param int $order_id
 * @return bool
 */
function elementsell_archive_order_by_id( $order_id ) {
    global $wpdb;

    $order_id = (int) $order_id;

    if ( $order_id <= 0 || ! elementsell_orders_archive_table_exists() ) {
        return false;
    }

    if ( elementsell_is_order_archived( $order_id ) ) {
        return true;
    }

    $orders_table   = $wpdb->prefix . 'elementsell_orders';
    $fields_table   = $wpdb->prefix . 'elementsell_order_fields';
    $invoices_table = elementsell_get_invoices_table_name();
    $orders_archive = elementsell_get_orders_archive_table_name();
    $fields_archive = elementsell_get_order_fields_archive_table_name();
    $invoice_archive = elementsell_get_invoices_archive_table_name();
    $now            = current_time( 'mysql' );

    $order = $wpdb->get_row(
        $wpdb->prepare( "SELECT * FROM {$orders_table} WHERE id = %d LIMIT 1", $order_id )
    );

    if ( ! $order || (string) $order->payment_status !== 'paid' ) {
        return false;
    }

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

    $order_row               = (array) $order;
    $order_row['archived_at'] = $now;

    if ( false === $wpdb->insert( $orders_archive, $order_row ) ) {
        $wpdb->query( 'ROLLBACK' );
        return false;
    }

    $fields = $wpdb->get_results(
        $wpdb->prepare( "SELECT * FROM {$fields_table} WHERE order_id = %d", $order_id ),
        ARRAY_A
    );

    foreach ( (array) $fields as $field ) {
        unset( $field['id'] );
        $wpdb->insert( $fields_archive, $field );
    }

    $invoice = $wpdb->get_row(
        $wpdb->prepare( "SELECT * FROM {$invoices_table} WHERE order_id = %d LIMIT 1", $order_id ),
        ARRAY_A
    );

    if ( $invoice ) {
        $invoice['archived_at'] = $now;
        $wpdb->insert( $invoice_archive, $invoice );
        $wpdb->delete( $invoices_table, [ 'order_id' => $order_id ], [ '%d' ] );
    }

    $wpdb->delete( $fields_table, [ 'order_id' => $order_id ], [ '%d' ] );
    $wpdb->delete( $orders_table, [ 'id' => $order_id ], [ '%d' ] );

    $wpdb->query( 'COMMIT' );

    return true;
}

/**
 * @return int
 */
function elementsell_archive_old_orders_batch() {
    global $wpdb;

    if ( ! elementsell_orders_archive_table_exists() ) {
        return 0;
    }

    $orders_table = $wpdb->prefix . 'elementsell_orders';
    $months       = max( 6, elementsell_get_archive_retention_months() );
    $batch        = max( 10, elementsell_get_archive_batch_size() );
    $cutoff       = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $months . ' months', current_time( 'timestamp' ) ) );

    $order_ids = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT id
             FROM {$orders_table}
             WHERE payment_status = 'paid'
             AND created_at < %s
             ORDER BY created_at ASC
             LIMIT %d",
            $cutoff,
            $batch
        )
    );

    $archived = 0;

    foreach ( (array) $order_ids as $order_id ) {
        if ( elementsell_archive_order_by_id( (int) $order_id ) ) {
            ++$archived;
        }
    }

    if ( $archived > 0 ) {
        update_option( 'elementsell_archive_last_run', current_time( 'mysql' ), false );
    }

    return $archived;
}

/**
 * @param string $query
 * @param int    $limit
 * @return array<int, object>
 */
function elementsell_search_sales_records( $query, $limit = 50 ) {
    global $wpdb;

    $query = trim( (string) $query );
    $limit = max( 1, min( 100, (int) $limit ) );

    if ( $query === '' ) {
        return [];
    }

    $orders_table     = $wpdb->prefix . 'elementsell_orders';
    $archive_orders   = elementsell_get_orders_archive_table_name();
    $invoices_table   = elementsell_get_invoices_table_name();
    $archive_invoices = elementsell_get_invoices_archive_table_name();
    $like             = '%' . $wpdb->esc_like( $query ) . '%';
    $search_parts     = [
        'o.email LIKE %s',
        'o.first_name LIKE %s',
        'o.surname LIKE %s',
        'o.phone LIKE %s',
        'o.product_name LIKE %s',
        'o.order_uid LIKE %s',
        'o.payment_uid LIKE %s',
        'o.vs LIKE %s',
    ];
    $base_params      = array_fill( 0, count( $search_parts ), $like );

    if ( ctype_digit( $query ) ) {
        $search_parts[] = 'o.id = %d';
        $base_params[]  = (int) $query;
    }

    $where   = implode( ' OR ', $search_parts );
    $results = [];

    foreach ( [
        [ $orders_table, $invoices_table, 0 ],
        [ $archive_orders, $archive_invoices, 1 ],
    ] as $source ) {
        [ $order_table, $invoice_table, $is_archived ] = $source;

        if ( $is_archived && ! elementsell_orders_archive_table_exists() ) {
            continue;
        }

        $sql = $wpdb->prepare(
            "
            SELECT o.*, %d AS is_archived, i.id AS invoice_id, i.invoice_number, i.issue_date
            FROM {$order_table} o
            LEFT JOIN {$invoice_table} i ON i.order_id = o.id
            WHERE {$where}
            ORDER BY o.created_at DESC
            LIMIT %d
            ",
            $is_archived,
            ...array_merge( $base_params, [ $limit ] )
        );

        $results = array_merge( $results, (array) $wpdb->get_results( $sql ) );

        $sql = $wpdb->prepare(
            "
            SELECT o.*, %d AS is_archived, i.id AS invoice_id, i.invoice_number, i.issue_date
            FROM {$invoice_table} i
            INNER JOIN {$order_table} o ON o.id = i.order_id
            WHERE i.invoice_number LIKE %s OR i.product_name LIKE %s
            ORDER BY o.created_at DESC
            LIMIT %d
            ",
            $is_archived,
            $like,
            $like,
            $limit
        );

        $results = array_merge( $results, (array) $wpdb->get_results( $sql ) );
    }

    $unique = [];

    foreach ( $results as $row ) {
        $key = (int) $row->id . ':' . (int) $row->is_archived;

        if ( ! isset( $unique[ $key ] ) ) {
            $unique[ $key ] = $row;
        }
    }

    usort(
        $unique,
        static function ( $a, $b ) {
            return strcmp( (string) $b->created_at, (string) $a->created_at );
        }
    );

    return array_slice( array_values( $unique ), 0, $limit );
}

add_action( 'elementsell_daily_billing_check', 'elementsell_archive_old_orders_batch', 40 );
