<?php
/**
 * FajaedeSEO Helpers
 * Core helper functions for statistics and utilities
 */

defined('ABSPATH') or exit;

/**
 * Count all published pages
 */
if (!function_exists('fajaedeseo_get_indexed_pages_count')) {
function fajaedeseo_get_indexed_pages_count() {
    $cached = get_transient('fajaedeseo_indexed_pages_count');
    if (false !== $cached) {
        return intval($cached);
    }

    $indexed = 0;
    $post_types = get_post_types(['public' => true]);
    unset($post_types['attachment']);
    if (!empty($post_types)) {
        foreach ($post_types as $post_type) {
            $counts = wp_count_posts($post_type);
            $indexed += $counts->publish ?? 0;
        }
    }
    set_transient('fajaedeseo_indexed_pages_count', $indexed, 12 * HOUR_IN_SECONDS);
    return $indexed;
}
}

/**
 * Format a single sales row for CSV export
 */
if (!function_exists('fajaedeseo_get_sales_csv_row')) {
function fajaedeseo_get_sales_csv_row(array $sale) {
    // Use stored VAT data if available, otherwise calculate
    $vat_rate = floatval($sale['vat_rate'] ?? 0.21);
    $total = (float) ($sale['amount'] ?? 0);
    
    // Calculate excl VAT and VAT amount
    if ($vat_rate > 0) {
        $ex_vat = $total / (1 + $vat_rate);
        $vat = $total - $ex_vat;
    } else {
        // No VAT (export or reverse charge)
        $ex_vat = $total;
        $vat = 0;
    }
    
    $invoice = (string)($sale['invoice_number'] ?? '');
    if (isset($invoice[0]) && in_array($invoice[0], ['=', '+', '-', '@'], true)) $invoice = "'" . $invoice;
    
    $payment_id = (string)($sale['payment_id'] ?? '');
    if (isset($payment_id[0]) && in_array($payment_id[0], ['=', '+', '-', '@'], true)) $payment_id = "'" . $payment_id;
    
    $country_code = strtoupper($sale['country_code'] ?? 'BE');
    $customer_type = $sale['customer_type'] ?? 'particulier';
    
    // Get country name
    $country_name = \FajaedeSEO_VAT_Calculator::get_country_name($country_code);
    
    $date = $sale['created_at'] ?? '';
    if (!empty($date)) {
        $date = gmdate('d-m-Y H:i', strtotime($date));
    }
    
    return [
        $invoice, 
        $payment_id, 
        $sale['plan_level'] ?? '',
        $country_code,
        $country_name,
        $customer_type,
        $customer_type === 'particulier' && $country_code !== 'BE' && \FajaedeSEO_VAT_Calculator::is_eu_country($country_code) ? 'Ja' : 'Nee',
        number_format($ex_vat, 2, ',', ''), 
        (int) ($vat_rate * 100) . '%',
        number_format($vat, 2, ',', ''), 
        number_format($total, 2, ',', ''), 
        $sale['currency'] ?? '',
        $date
    ];
}
}

/**
 * Calculate page speed score
 */
if (!function_exists('fajaedeseo_get_page_speed_score')) {
function fajaedeseo_get_page_speed_score() {
    $score = get_transient('fajaedeseo_page_speed_score');
    if (false === $score) {
        $start = microtime(true);
        $response = wp_remote_get(home_url(), [
            'timeout' => 5,
            'sslverify' => true,
            'headers' => ['Cache-Control' => 'no-cache']
        ]);
        $end = microtime(true);
        
        if (is_wp_error($response)) {
            $score = 0;
        } else {
            $duration = $end - $start;
            $score = ($duration <= 0.2) ? 100 : (($duration >= 2.0) ? 0 : intval(100 - (($duration - 0.2) / 1.8 * 100)));
        }
        set_transient('fajaedeseo_page_speed_score', $score, HOUR_IN_SECONDS);
    }
    return $score;
}

/**
 * Generates the privacy policy content based on user settings.
 * This function is now a wrapper for the method in the main plugin class.
 *
 * @return string The generated privacy policy HTML.
 */
function fajaedeseo_generate_privacy_policy_content() {
    return \FajaedeSEO::get_instance()->generate_policy_content('privacy_policy');
}
}

/**
 * Calculate overall SEO score
 */
if (!function_exists('fajaedeseo_get_overall_seo_score')) {
function fajaedeseo_get_overall_seo_score() {
    $cached_score = get_transient('fajaedeseo_overall_seo_score');
    if (false !== $cached_score) {
        return intval($cached_score);
    }
    
    $total_score = 0;
    $post_count = 0;
    $post_types = get_post_types(['public' => true]);
    unset($post_types['attachment']);
    
    if (!empty($post_types)) {
        $args = [
            'post_type' => $post_types,
            'posts_per_page' => 50,
            'fields' => 'ids',
            'post_status' => 'publish'
        ];
        $posts = get_posts($args);
        
        foreach ($posts as $post_id) {
            $title = get_post_meta($post_id, '_fajaedeseo_meta_title', true);
            $desc = get_post_meta($post_id, '_fajaedeseo_meta_desc', true);
            
            $score = 0;
            if (!empty($title) && strlen($title) >= 40 && strlen($title) <= 60) $score += 40;
            if (!empty($desc) && strlen($desc) >= 80 && strlen($desc) <= 160) $score += 40;
            if (!empty($title) && !empty($desc)) $score += 20;
            
            $total_score += $score;
            $post_count++;
        }
    }
    
    $overall = $post_count > 0 ? intval($total_score / $post_count) : 50;
    set_transient('fajaedeseo_overall_seo_score', $overall, HOUR_IN_SECONDS);
    return $overall;
}
}

/**
 * Get site context for content recommendations
 */
if (!function_exists('fajaedeseo_get_site_context')) {
function fajaedeseo_get_site_context($limit = 3) {
    $posts = get_posts([
        'posts_per_page' => $limit,
        'fields' => 'ids',
        'post_status' => 'publish',
        'orderby' => 'date',
        'order' => 'DESC'
    ]);
    
    $context = [];
    foreach ($posts as $post_id) {
        $context[] = get_the_title($post_id);
    }
    return $context;
}
}

/**
 * Get the real client IP address (Supports proxies & Cloudflare)
 *
 * @return string
 */
if (!function_exists('fajaedeseo_get_client_ip')) {
function fajaedeseo_get_client_ip() {
    $ip = '';
    if ( ! empty( $_SERVER['HTTP_CF_CONNECTING_IP'] ) ) {
        $ip = sanitize_text_field( wp_unslash( $_SERVER['HTTP_CF_CONNECTING_IP'] ) );
    } elseif ( ! empty( $_SERVER['HTTP_X_FORWARDED_FOR'] ) ) {
        $forwarded_for = sanitize_text_field( wp_unslash( $_SERVER['HTTP_X_FORWARDED_FOR'] ) );
        $ip_list       = explode( ',', $forwarded_for );
        $ip            = trim( $ip_list[0] );
    } elseif ( ! empty( $_SERVER['REMOTE_ADDR'] ) ) {
        $ip = sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) );
    }
    return $ip;
}
}

/**
 * Haal het totale aantal bezoekers van de afgelopen 30 dagen op
 */
if (!function_exists('fajaedeseo_get_total_visitors')) {
function fajaedeseo_get_total_visitors() {
    $cached = get_transient('fajaedeseo_total_visitors_30d');
    if (false !== $cached) {
        return (int) $cached;
    }

    global $wpdb;
    $table = $wpdb->prefix . 'fajaedeseo_visitors';
    
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) !== $table) {
        return 0;
    }

    $thirty_days_ago = gmdate('Y-m-d H:i:s', strtotime('-30 days'));
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $count = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$table}` WHERE `timestamp` >= %s", $thirty_days_ago));
    set_transient('fajaedeseo_total_visitors_30d', $count, 15 * MINUTE_IN_SECONDS);
    return $count;
}
}

/**
 * Haal de GEO data op (en bereken direct uit database als de cache ontbreekt)
 */
if (!function_exists('fajaedeseo_get_geo_data')) {
function fajaedeseo_get_geo_data() {
    $cached = get_transient('fajaedeseo_globe_visitor_data');
    if (false !== $cached) {
        return $cached;
    }

    global $wpdb;
    $table = $wpdb->prefix . 'fajaedeseo_visitors';
    
    // Controleer of de tabel bestaat om SQL fouten te voorkomen
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) !== $table) {
        return wp_json_encode(['countries' => [], 'regions' => [], 'cities' => []]);
    }

    $get_top = function($col) use ($wpdb, $table) {
        $allowed = ['country', 'region', 'city'];
        if (!in_array($col, $allowed, true)) return [];
        
        // Gratis gebruikers zien alleen landen (beperking op regio's en steden)
        if ($col !== 'country' && (!class_exists('\FajaedeSEO_Pro_License') || !\FajaedeSEO_Pro_License::is_pro())) return [];

        $safe_col = esc_sql($col);
        $thirty_days_ago = gmdate('Y-m-d H:i:s', strtotime('-30 days'));
        $query = sprintf("SELECT `%1\$s` as name, COUNT(*) as count FROM `%2\$s` WHERE `%1\$s` IS NOT NULL AND `%1\$s` != '' AND `timestamp` >= '%3\$s' GROUP BY `%1\$s` ORDER BY count DESC LIMIT 5", $safe_col, $table, esc_sql($thirty_days_ago));
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($query, ARRAY_A) ?: [];
    };

    $stats = ['countries' => $get_top('country'), 'regions' => $get_top('region'), 'cities' => $get_top('city')];
    $json = wp_json_encode($stats);
    set_transient('fajaedeseo_globe_visitor_data', $json, 12 * HOUR_IN_SECONDS);
    return $json;
}
}

/**
 * Generate Sales Data as a CSV formatted string.
 * Centralized logic for both file download and API response.
 *
 * @return string|false CSV content as a string, or false if no data.
 */
if (!function_exists('fajaedeseo_generate_sales_csv_string')) {
function fajaedeseo_generate_sales_csv_string() {
    global $wpdb;
    $table = $wpdb->prefix . 'fajaedeseo_licenses';

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table)) !== $table) {
        return false;
    }
    
    $cache_key = 'fajaedeseo_export_sales_data';
    $sales = wp_cache_get($cache_key, 'fajaedeseo');
    
    if (false === $sales) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $sales = $wpdb->get_results("SELECT * FROM `" . esc_sql($table) . "` WHERE payment_id IS NOT NULL ORDER BY created_at DESC", ARRAY_A);
        wp_cache_set($cache_key, $sales, 'fajaedeseo', 5 * MINUTE_IN_SECONDS);
    }

    if (empty($sales)) {
        return false;
    }

    // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
    $output = fopen('php://temp', 'r+');
    
    // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
    fwrite($output, "\xEF\xBB\xBF"); // UTF-8 BOM for Excel

    fputcsv($output, ['Factuurnummer', 'Transactie ID', 'Plan', 'Land Code', 'Land', 'Klant Type', 'OSS Plichtig', 'Bedrag Excl BTW', 'BTW %', 'BTW Bedrag', 'Totaal', 'Valuta', 'Datum'], ';');

    foreach ($sales as $sale) {
        fputcsv($output, fajaedeseo_get_sales_csv_row($sale), ';');
    }

    rewind($output);
    $csv = stream_get_contents($output);
    // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
    fclose($output);

    return $csv;
}
}

/**
 * Send Weekly Thursday Accounting Report
 */
if (!function_exists('fajaedeseo_send_weekly_accounting_report')) {
function fajaedeseo_send_weekly_accounting_report() {
    // WP Cron is afhankelijk van websitebezoekers. Als niemand op donderdag de site bezoekt,
    // wordt de taak uitgesteld. We moeten voorkomen dat de mail daardoor compleet wordt overgeslagen.
    
    $last_run = (int) get_option('fajaedeseo_last_accounting_report_time', 0);
    $now = time();

    // Minimaal 6 dagen wachten tussen rapportages om dubbele e-mails te voorkomen
    if (($now - $last_run) < (6 * DAY_IN_SECONDS)) {
        return;
    }

    // 0=Zon, 1=Ma, 2=Di, 3=Wo, 4=Do, 5=Vr, 6=Zat.
    // We sturen vanaf Donderdag (4). Is het Ma, Di, of Wo? Dan wachten we braaf nog even.
    $day_of_week = (int) gmdate('w', $now);
    if ($day_of_week >= 1 && $day_of_week <= 3) {
        return;
    }

    $accountant_email = get_option('fajaedeseo_accountant_email');
    if (empty($accountant_email)) return;

    global $wpdb;
    $table = $wpdb->prefix . 'fajaedeseo_licenses';
    // Get last 7 days of sales
    $week_ago = gmdate('Y-m-d H:i:s', strtotime('-7 days'));
    
    $cache_key = 'fajaedeseo_weekly_sales_report_' . gmdate('Y_W'); // Weekly cache key
    $sales = wp_cache_get($cache_key, 'fajaedeseo');
    
    if (false === $sales) {
        $sales = $wpdb->get_results($wpdb->prepare("SELECT * FROM `" . esc_sql($table) . "` WHERE payment_id IS NOT NULL AND created_at >= %s", $week_ago), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        wp_cache_set($cache_key, $sales, 'fajaedeseo', 7 * 24 * HOUR_IN_SECONDS);
    }

    if (empty($sales)) {
        // Send email even if no sales (to confirm system is working)
        wp_mail($accountant_email, 'FajaedeSEO - Weekly Sales Report (No sales this week)', 'No sales transactions this week for FajaedeSEO licenses.', ['Content-Type: text/html; charset=UTF-8']);
        return;
    }

    if (!function_exists('wp_tempnam')) {
        require_once(ABSPATH . 'wp-admin/includes/file.php');
    }

    // Genereer een echt .csv bestand in plaats van een .tmp bestand om spamfilters te vermijden
    $upload_dir = wp_upload_dir();
    $csv_file = wp_normalize_path(trailingslashit($upload_dir['basedir']) . 'fseo_weekly_sales_' . gmdate('Y-m-d') . '.csv');
    
    // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
    $output = fopen($csv_file, 'w');
    
    // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
    fwrite($output, "\xEF\xBB\xBF"); // UTF-8 BOM voor Excel
    fputcsv($output, ['Factuurnummer', 'Transactie ID', 'Plan', 'Land Code', 'Land', 'Klant Type', 'OSS Plichtig', 'Bedrag Excl BTW', 'BTW %', 'BTW Bedrag', 'Totaal', 'Valuta', 'Datum'], ';');
    
    foreach ($sales as $sale) {
        fputcsv($output, fajaedeseo_get_sales_csv_row($sale), ';');
    }
    
    // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
    fclose($output);

    $report_date = gmdate('Y-m-d', strtotime('-7 days')) . ' tot ' . gmdate('Y-m-d');
    wp_mail($accountant_email, 'FajaedeSEO - Weekly Sales Report (' . $report_date . ')', 'Attached is the weekly sales CSV report for FajaedeSEO licenses (' . $report_date . ').', ['Content-Type: text/html; charset=UTF-8'], [$csv_file]);
    wp_delete_file($csv_file);

    // Sla het huidige tijdstip op zodat we weten dat we deze week gehad hebben
    update_option('fajaedeseo_last_accounting_report_time', $now);
}
}