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

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

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

/**
 * @return int
 */
function elementsell_get_sales_stats_retention_months() {
    return (int) apply_filters( 'elementsell_sales_stats_retention_months', 120 );
}

/**
 * SQL podmínka pro statistiky (bez DEMO / testovacích plateb).
 *
 * @param string $orders_alias
 * @return string
 */
function elementsell_sales_stats_get_orders_exclusion_sql( $orders_alias = '' ) {
    global $wpdb;

    $prefix       = $orders_alias !== '' ? $orders_alias . '.' : '';
    $fields_table = $wpdb->prefix . 'elementsell_order_fields';

    return " AND {$prefix}id NOT IN (
        SELECT order_id
        FROM {$fields_table}
        WHERE ( field_name = 'environment' AND field_value = 'demo' )
           OR ( field_name = 'landing_demo' AND field_value = '1' )
           OR ( field_name = 'billing_test' AND field_value = '1' )
           OR ( field_name = 'fio_test' AND field_value = '1' )
           OR ( field_name = 'api_url' AND field_value LIKE '%demo.%' )
           OR ( field_name = 'gate_url' AND field_value LIKE '%demo.%' )
    )";
}

/**
 * @param object $order
 * @return void
 */
function elementsell_sales_stats_record_order( $order ) {
    if ( ! $order || (string) $order->payment_status !== 'paid' ) {
        return;
    }

    // Demo ThePay / testovací platby nepatří do příjmů (rebuild to filtruje, live zápis dřív ne).
    if ( function_exists( 'elementsell_is_demo_order' ) && elementsell_is_demo_order( $order ) ) {
        return;
    }

    if ( function_exists( 'elementsell_get_order_field' ) ) {
        $order_id = (int) $order->id;

        if (
            elementsell_get_order_field( $order_id, 'billing_test' ) === '1'
            || elementsell_get_order_field( $order_id, 'fio_test' ) === '1'
        ) {
            return;
        }
    }

    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();
    $year  = (int) mysql2date( 'Y', $order->created_at, false );
    $month = (int) mysql2date( 'n', $order->created_at, false );

    if ( $year <= 0 || $month <= 0 ) {
        return;
    }

    $product_name = (string) $order->product_name;
    $product_name = $product_name !== '' ? $product_name : '—';
    $amount       = (float) $order->amount;

    $existing_id = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT id FROM {$table}
             WHERE stat_year = %d AND stat_month = %d AND product_name = %s AND amount = %f
             LIMIT 1",
            $year,
            $month,
            $product_name,
            $amount
        )
    );

    if ( $existing_id ) {
        $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table}
                 SET sales_count = sales_count + 1,
                     revenue_sum = revenue_sum + %f
                 WHERE id = %d",
                $amount,
                (int) $existing_id
            )
        );
        return;
    }

    $wpdb->insert(
        $table,
        [
            'stat_year'    => $year,
            'stat_month'   => $month,
            'product_name' => $product_name,
            'amount'       => $amount,
            'sales_count'  => 1,
            'revenue_sum'  => $amount,
        ],
        [ '%d', '%d', '%s', '%f', '%d', '%f' ]
    );
}

/**
 * @param int $order_id
 * @return void
 */
function elementsell_sales_stats_record_order_by_id( $order_id ) {
    $order = elementsell_get_order( (int) $order_id );

    if ( $order ) {
        elementsell_sales_stats_record_order( $order );
    }
}

/**
 * @return void
 */
function elementsell_sales_stats_rebuild_all() {
    global $wpdb;

    $stats_table    = elementsell_get_sales_stats_table_name();
    $orders_table   = $wpdb->prefix . 'elementsell_orders';
    $archive_table  = $wpdb->prefix . 'elementsell_orders_archive';
    $exclusion      = elementsell_sales_stats_get_orders_exclusion_sql( 'o' );
    $archive_exists = elementsell_orders_archive_table_exists();

    $wpdb->query( "TRUNCATE TABLE {$stats_table}" );

    $sources = [ $orders_table ];

    if ( $archive_exists ) {
        $sources[] = $archive_table;
    }

    foreach ( $sources as $source_table ) {
        $wpdb->query(
            "
            INSERT INTO {$stats_table} (stat_year, stat_month, product_name, amount, sales_count, revenue_sum)
            SELECT
                YEAR(o.created_at) AS stat_year,
                MONTH(o.created_at) AS stat_month,
                COALESCE(NULLIF(o.product_name, ''), '—') AS product_name,
                o.amount,
                COUNT(*) AS sales_count,
                SUM(o.amount) AS revenue_sum
            FROM {$source_table} o
            WHERE o.payment_status = 'paid'
            {$exclusion}
            GROUP BY stat_year, stat_month, product_name, amount
            ON DUPLICATE KEY UPDATE
                sales_count = sales_count + VALUES(sales_count),
                revenue_sum = revenue_sum + VALUES(revenue_sum)
            "
        );
    }

    update_option( 'elementsell_sales_stats_built', PF_VERSION, false );
}

/**
 * @return void
 */
function elementsell_sales_stats_maybe_rebuild() {
    $built = get_option( 'elementsell_sales_stats_built', '' );

    if ( (string) $built === (string) PF_VERSION ) {
        return;
    }

    elementsell_sales_stats_rebuild_all();
}

/**
 * @return array{revenue: float, count: int}
 */
function elementsell_sales_stats_get_totals() {
    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();
    $row   = $wpdb->get_row(
        "SELECT COALESCE(SUM(revenue_sum), 0) AS revenue, COALESCE(SUM(sales_count), 0) AS sales_count
         FROM {$table}"
    );

    return [
        'revenue' => (float) ( $row->revenue ?? 0 ),
        'count'   => (int) ( $row->sales_count ?? 0 ),
    ];
}

/**
 * @param int $year
 * @param int $month
 * @return float
 */
function elementsell_sales_stats_get_month_revenue( $year, $month ) {
    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();

    return (float) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COALESCE(SUM(revenue_sum), 0) FROM {$table} WHERE stat_year = %d AND stat_month = %d",
            (int) $year,
            (int) $month
        )
    );
}

/**
 * @param int $year
 * @return array<int, float> Index 0–11 => součet za měsíc.
 */
function elementsell_sales_stats_get_year_month_totals( $year ) {
    global $wpdb;

    $table  = elementsell_get_sales_stats_table_name();
    $totals = array_fill( 0, 12, 0.0 );
    $rows   = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT stat_month, SUM(revenue_sum) AS total
             FROM {$table}
             WHERE stat_year = %d
             GROUP BY stat_month
             ORDER BY stat_month ASC",
            (int) $year
        )
    );

    foreach ( (array) $rows as $row ) {
        $index = (int) $row->stat_month - 1;

        if ( $index >= 0 && $index < 12 ) {
            $totals[ $index ] = round( (float) $row->total );
        }
    }

    return $totals;
}

/**
 * @param int $before_year
 * @return array<int, array<int, float>> Index měsíc => [ rok => součet ].
 */
function elementsell_sales_stats_get_historical_by_month( $before_year ) {
    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();
    $rows  = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT stat_year, stat_month, SUM(revenue_sum) AS total
             FROM {$table}
             WHERE stat_year < %d
             GROUP BY stat_year, stat_month
             ORDER BY stat_year ASC, stat_month ASC",
            (int) $before_year
        )
    );

    $historical = array_fill( 0, 12, [] );

    foreach ( (array) $rows as $row ) {
        $index = (int) $row->stat_month - 1;
        $year  = (int) $row->stat_year;

        if ( $index < 0 || $index > 11 ) {
            continue;
        }

        if ( ! isset( $historical[ $index ][ $year ] ) ) {
            $historical[ $index ][ $year ] = 0;
        }

        $historical[ $index ][ $year ] += round( (float) $row->total );
    }

    return $historical;
}

/**
 * @return float
 */
function elementsell_sales_stats_get_last_12_months_revenue() {
    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();
    $now   = current_time( 'timestamp' );
    $from  = gmdate( 'Y-m-01 00:00:00', strtotime( '-11 months', $now ) );
    $rows  = $wpdb->get_results(
        "SELECT stat_year, stat_month, SUM(revenue_sum) AS total
         FROM {$table}
         GROUP BY stat_year, stat_month"
    );

    $sum   = 0.0;
    $start = strtotime( $from );

    foreach ( (array) $rows as $row ) {
        $month_ts = mktime( 0, 0, 0, (int) $row->stat_month, 1, (int) $row->stat_year );

        if ( $month_ts >= $start ) {
            $sum += (float) $row->total;
        }
    }

    return $sum;
}

/**
 * @return array<string, array{label: string, total: float, items: array<int, object>}>
 */
function elementsell_sales_stats_get_grouped_by_month() {
    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();
    $rows  = $wpdb->get_results(
        "
        SELECT stat_year, stat_month, product_name, amount, sales_count AS qty, revenue_sum AS line_total
        FROM {$table}
        ORDER BY stat_year DESC, stat_month DESC, sales_count DESC, product_name ASC
        "
    );

    $months = [];

    foreach ( (array) $rows as $row ) {
        $key = sprintf( '%04d-%02d', (int) $row->stat_year, (int) $row->stat_month );

        if ( ! isset( $months[ $key ] ) ) {
            $months[ $key ] = [
                'label' => $key,
                'total' => 0.0,
                'items' => [],
            ];
        }

        $months[ $key ]['items'][] = $row;
        $months[ $key ]['total']  += (float) $row->line_total;
    }

    return $months;
}

/**
 * @return array<int, object>
 */
function elementsell_sales_stats_get_products_total() {
    global $wpdb;

    $table = elementsell_get_sales_stats_table_name();

    return (array) $wpdb->get_results(
        "
        SELECT product_name, amount, SUM(sales_count) AS qty, SUM(revenue_sum) AS line_total
        FROM {$table}
        GROUP BY product_name, amount
        ORDER BY qty DESC, product_name ASC, amount DESC
        "
    );
}

add_action( 'elementsell_order_paid', 'elementsell_sales_stats_record_order_by_id', 20 );
add_action( 'plugins_loaded', 'elementsell_sales_stats_maybe_rebuild', 25 );
