<?php

namespace Macymed\MacymedLinkTracker\Utils;

use DateTime;
use InvalidArgumentException;

if (! defined('ABSPATH')) {
    exit; // Sécurité
}

class Stats
{
    /**
     * Holds the class instance.
     *
     * @var RegisterPlugin $instance
     */
    private static $instance = null;

    /**
     * Return an instance of the class
     *
     * Return an instance of the RegisterPlugin Class.
     *
     * @since 1.0.0
     *
     * @return RegisterPlugin class instance.
     */
    public static function get_instance()
    {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    public $table_name_main;
    public $table_name_clicks;
    public $wpdb;
    public function __construct()
    {
        global $wpdb;
        $this->wpdb              = $wpdb;
        $this->table_name_main   = $this->wpdb->prefix . MACYMED_LINK_TRACKER_TABLE_NAME;
        $this->table_name_clicks = $this->wpdb->prefix . MACYMED_LINK_TRACKER_CLICKS_TABLE_NAME;

        add_action('wp_ajax_get_data_for_stats', [$this, 'handle_get_data_for_stats']);
        add_action('wp_ajax_get_total_clicks', [$this, 'handle_get_total_clicks']);
        add_action('wp_ajax_nopriv_get_total_clicks', [$this, 'handle_get_total_clicks']);
    }
    public function handle_get_data_for_stats()
    {
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.NonceVerification.Missing
        $type_stat = isset($_POST['type_stat']) ? sanitize_text_field(wp_unslash($_POST['type_stat'])) : 'referer';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.NonceVerification.Missing
        $start_date = isset($_POST['start_date']) ? sanitize_text_field(wp_unslash($_POST['start_date'])) : null;
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended, WordPress.Security.NonceVerification.Missing
        $end_date = isset($_POST['end_date']) ? sanitize_text_field(wp_unslash($_POST['end_date'])) : null;
    
        // Construire la requête en fonction de type_stat et des dates
        $query = "SELECT * FROM {$this->table_name_clicks} WHERE type_url = %s";
        $params = [$type_stat];
    
        if ($start_date) {
            $query .= " AND date >= %s";
            $params[] = $start_date;
        }
    
        if ($end_date) {
            $query .= " AND date <= %s";
            $params[] = $end_date;
        }
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $this->wpdb->get_results($this->wpdb->prepare($query, $params));
    
        // Générer les données pour le graphique
        $graph_data = [];
        $history = '';
    
        foreach ($results as $result) {
            $graph_data[] = [
                'label' => $result->date,
                'value' => $result->clicks,
            ];
            $history .= "<div>{$result->date}: {$result->clicks} clics</div>";
        }
    
        wp_send_json([
            'graph' => wp_json_encode($graph_data),
            'history' => $history,
        ]);
    }
    
    public function getUrlId($url)
    {
        // get id from url inside database
        global $wpdb;
        $url_table   = $wpdb->prefix . MACYMED_LINK_TRACKER_TABLE_NAME;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $url_id = $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                'SELECT id FROM ' . $url_table . ' WHERE url = %s',
                $url
            )
        );
        return intval($url_id);
    }
    public function getClickFromUrl($url_id)
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $clicks = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                'SELECT * FROM ' . $this->table_name_clicks . ' WHERE url_id = %d',
                [intval($url_id)]
            )
        );
        return $clicks;
    }
    public function getTopReferer($url_id, $limit = 5)
    {
        $referers = [];
        $clicks   = $this->getClickFromUrl($url_id);
        foreach ($clicks as $click) {
            $data = json_decode($click->data, true);
            if (! empty($data['referer'])) {
                $referers[] = $data['referer'];
            }
        }

        // Calculer le top et le flop 10 des référents
        $referer_counts = array_count_values($referers);
        // Calculer le Top 10 (ce code reste inchangé)
        arsort($referer_counts); // Trier par clics décroissants
        $top_referers = array_slice($referer_counts, 0, $limit, true);

        return $top_referers;
    }
    public function getFlopReferer($url_id, $limit = 5)
    {
        $clicks   = $this->getClickFromUrl($url_id);
        $referers = [];
        foreach ($clicks as $click) {
            $data = json_decode($click->data, true);
            if (! empty($data['referer'])) {
                $referers[] = $data['referer'];
            }
        }

        // Calculer le top et le flop 10 des référents
        $referer_counts = array_count_values($referers);
        // Calculer le Top 10 (ce code reste inchangé)
        arsort($referer_counts); // Trier par clics décroissants
        $top_referers = array_slice($referer_counts, 0, $limit, true);

        // Exclure les URLs du Top 10
        $remaining_referers = array_diff_key($referer_counts, $top_referers);

        // Calculer le Flop 10
        asort($remaining_referers); // Trier par clics croissants
        $flop_referers = array_slice($remaining_referers, 0, $limit, true);

        return $flop_referers;
    }
    public function buildNetworkData($id)
    {
        $results = $this->getRefererResults($id);
        // print_r($results);die;
        $nodes = [];
        $links = [];

        foreach ($results as $row) {
            $referer = $row['referer'];
            if ($referer === null) {
                continue; // Retourne null si le referer est vide
            }

            // Décoder le JSON pour les valeurs extraites par JSON_EXTRACT
            $referer = trim($referer, '"'); // Supprimer les guillemets
            $referer = stripslashes($referer); // Retirer les backslashes
            $referer = filter_var($referer, FILTER_SANITIZE_URL); // Filtrer comme une URL

            // Si le résultat est invalide, retournez null ou une valeur par défaut
            if (($referer !== '/') && ! filter_var($referer, FILTER_VALIDATE_URL)) {
                continue;
            }
            // Ajouter les nœuds source et destination s'ils n'existent pas encore
            if (! isset($nodes[$referer])) {
                // $nodes[$row['referer']] = ['id' => $row['referer'], 'label' => 'Referer : ' . $row['referer']];
                $nodes[$referer] = [
                    'id'    => $referer,
                    'label' => $referer,
                ];
            }
            if (! isset($nodes[$row['source_url']])) {
                // $nodes[$row['source_url']] = ['id' => $row['source_url'], 'label' => 'Source : ' . $row['source_url']];
                $nodes[$row['source_url']] = [
                    'id'    => $row['source_url'],
                    'label' => urldecode($row['source_url']),
                ];
            }

            // Ajouter le lien
            $links[] = [
                'from'  => $referer,
                'to'    => $row['source_url'],
                'value' => $row['weight'],
                'label' => $row['weight'],
            ];
        }

        $dataNetwork = [
            'nodes' => array_values($nodes), // Conserver uniquement les valeurs
            'edges' => $links,
        ];
        return $dataNetwork;
    }
    public function getUrlFromID($id)
    {
        global $wpdb;

        // Vérification de l'ID
        if (!is_numeric($id)) {
            return null;
        }

        // Exécuter la requête
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                "SELECT url FROM " . $this->table_name_main . " WHERE id = %d LIMIT 1",
                $id
            )
        );

        // Retourner l'URL ou null si non trouvée
        return $result ?: null;
    }
    public function getRefererResults($id)
    {
        global $wpdb;
        $nodesQuery = "SELECT 
    e.url AS source_url,
    JSON_EXTRACT(c.data, '$.referer') AS referer,
    COUNT(*) AS weight
FROM 
    " . $this->table_name_clicks . " c
JOIN 
    " . $this->table_name_main . " e
ON 
    c.url_id = e.id
 WHERE url_id = %s

GROUP BY 
    referer, source_url
HAVING 
    referer IS NOT NULL;";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results($wpdb->prepare($nodesQuery, [$id]), ARRAY_A);
        return $results;
    }
    public function handle_get_total_clicks()
    {
        // Vérifier et obtenir les paramètres
        $input = json_decode(file_get_contents('php://input'), true);

        // Vérifier et récupérer les valeurs
        $type         = isset($input['type']) ? sanitize_text_field($input['type']) : 'daily';
        $start_period = isset($input['start_period']) ? sanitize_text_field($input['start_period']) : null;
        $end_period   = isset($input['end_period']) ? sanitize_text_field($input['end_period']) : null;
        $search_text  = isset($input['search_text']) ? sanitize_text_field($input['search_text']) : null;

        if ($type === 'none') {
            $type = $this->determine_period_format($start_period, $end_period);
        }
        // Appeler votre fonction getTotalClic
        $results = self::get_instance()->getTotalClic($type, $start_period, $end_period, $search_text);

        // Retourner les données sous forme de JSON
        wp_send_json($results);
    }
    public function determine_period_format($start_period = null, $end_period = null, $urlId = 0, $desired_points = 20)
    {
        global $wpdb;


        // Construction de la requête pour récupérer les informations pertinentes
        $where_clauses = [];
        $params        = [];
        if ($urlId) {
            $where_clauses[] = "url_id = %d";
            $params[] = $urlId;
        }


        if ($start_period) {
            $where_clauses[] = "last_click >= %s";
            $params[] = $start_period;
        }

        if ($end_period) {
            $where_clauses[] = "last_click <= %s";
            $params[] = $end_period;
        }



        // Exécuter la requête
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = false;
        if (count($where_clauses) > 0) {
            $sql =  "SELECT MIN(last_click) as min_date, MAX(last_click) as max_date, COUNT(*) as total_records
           FROM " . $this->table_name_clicks . " WHERE " . implode(' AND ', $where_clauses);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->get_row(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $sql,
                    $params
                ),
                ARRAY_A
            );
        } else {
            $sql = "SELECT MIN(last_click) as min_date, MAX(last_click) as max_date, COUNT(*) as total_records
           FROM " . $this->table_name_clicks;
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $result = $wpdb->get_row(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $sql,
                ARRAY_A
            );
        }


        // Si aucune donnée n'est trouvée, retourner 'daily' par défaut
        if (!$result || !$result['total_records']) {
            return 'daily';
        }

        // Extraire les données
        $start_period = $result['min_date'];
        $end_period = $result['max_date'];
        $total_records = $result['total_records'];

        // Calculer l'intervalle en jours
        $start_date = new DateTime($start_period);
        $end_date = new DateTime($end_period);
        $interval_days = $start_date->diff($end_date)->days;

        // Si peu d'enregistrements, ajuster le format à 'daily'
        if ($total_records <= $desired_points) {
            return 'daily';
        }

        // Ajuster en fonction de l'intervalle
        if ($interval_days <= $desired_points) {
            return 'daily';
        } elseif ($interval_days <= $desired_points * 7) {
            return 'weekly';
        } elseif ($interval_days <= $desired_points * 30) {
            return 'monthly';
        } else {
            return 'yearly';
        }
    }

    public function getTotalClic($type = 'daily', $start_period = null, $end_period = null, $search_text = null, $url_id = null)
    {
        global $wpdb;

        // Valider le format de GROUP BY
        switch ($type) {
            case 'daily':
                $group_by_format = '%Y-%m-%d'; // Grouper par jour
                break;
            case 'weekly':
                $group_by_format = '%Y-%u'; // Grouper par année et semaine
                break;
            case 'monthly':
                $group_by_format = '%Y-%m'; // Grouper par année et mois
                break;
            case 'yearly':
                $group_by_format = '%Y'; // Grouper par année
                break;
            default:
                throw new InvalidArgumentException('Invalid type provided for grouping');
        }

        // Préparation de la requête de base
        $sql = "SELECT DATE_FORMAT(last_click, %s) as period, COUNT(*) as total_clicks
        FROM {$this->table_name_clicks}
        JOIN {$this->table_name_main} ON {$this->table_name_clicks}.url_id = {$this->table_name_main}.id";

        // Tableau des paramètres pour la requête préparée
        $params = [$group_by_format];

        // Ajouter les conditions WHERE
        $where_clauses = [];
        if ($start_period && $end_period) {
            $where_clauses[] = 'last_click BETWEEN %s AND %s';
            $params[] = $start_period;
            $params[] = $end_period;
        } elseif ($start_period && !$end_period) {
            $where_clauses[] = 'last_click >= %s';
            $params[] = $start_period;
        } elseif (!$start_period && $end_period) {
            $where_clauses[] = 'last_click <= %s';
            $params[] = $end_period;
        }

        // Ajouter la condition de recherche
        if ($search_text) {
            $where_clauses[] = '`url` LIKE %s';
            $params[] = '%' . $wpdb->esc_like($search_text) . '%';
        }
        if ($url_id) {
            $where_clauses[] = 'url_id = %d';
            $params[] = $url_id;
        }

        // Combiner les clauses WHERE
        if (!empty($where_clauses)) {
            $sql .= ' WHERE ' . implode(' AND ', $where_clauses);
        }

        // Ajouter GROUP BY et ORDER BY
        $sql .= ' GROUP BY period ORDER BY period ASC';

        // Préparer et exécuter la requête
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);


        return $results; // Retourne un tableau des périodes et des totaux
    }
    public function getListUrls($type = null)
    {
        global $wpdb;


        // Préparation de la requête de base
        $sql = "SELECT DISTINCT(url), {$this->table_name_main}.id
        FROM {$this->table_name_main}
        
        " ;

        $params = [];
        $where = [];
        if($type ){
            $sql.= " JOIN {$this->table_name_clicks} ON {$this->table_name_main}.id = {$this->table_name_clicks}.url_id";
            // $sql .= " and type_url = %s";
            $where[] = 'type_url = %s';
            $params[] = $type;
        }
        $sql .= count($where) ? ' WHERE ' . implode(' AND ', $where) : '';

        // Préparer et exécuter la requête
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $results = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);


        return $results; // Retourne un tableau des périodes et des totaux
    }
    public static function init()
    {


        add_action(
            'wp_enqueue_scripts',
            function () {
                wp_enqueue_script(
                    'chartjs', // Handle du script
                    Utils::get_plugin_url() . 'dist/js/vendor/chart.min.js',
                    [], // Pas de dépendances
                    '4.4.7',
                    false
                );
                wp_enqueue_script(
                    'macymed-link-tracker-js', // Handle du script
                    Utils::get_plugin_url() . ('/dist/js/replace-external-links.js') . '?t=' . time(), // Chemin vers le fichier JS
                    ['chartjs', 'jquery'], // Dépendances (vide ici)
                    '1.0.1', // Version du script
                    true // Charge le script dans le footer pour des performances optimales
                );
            }
        );
    }
}
