<?php
/**
 * Universal Analytics Service
 *
 * Central service for managing the universal analytics system
 * Handles categories, metrics, sources, and data aggregation
 */

namespace FleekDash\Services;

// Load debug helpers
if (!function_exists('fleekdash_log_error')) {
    require_once FLEEKDASH_PLUGIN_DIR . 'includes/Core/debug-helpers.php';
}

class UniversalAnalyticsService
{
    private static $instance = null;
    private $wpdb;
    private $cache_service;

    // Table names
    private $table_categories;
    private $table_metrics;
    private $table_sources;
    private $table_source_metrics;
    private $table_universal_data;
    private $table_dashboard_templates;
    private $table_user_dashboards;

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;

        // Initialize table names
        $this->table_categories = $wpdb->prefix . 'fleekdash_analytics_categories';
        $this->table_metrics = $wpdb->prefix . 'fleekdash_analytics_metrics';
        $this->table_sources = $wpdb->prefix . 'fleekdash_analytics_sources';
        $this->table_source_metrics = $wpdb->prefix . 'fleekdash_analytics_source_metrics';
        $this->table_universal_data = $wpdb->prefix . 'fleekdash_analytics_universal_data';
        $this->table_dashboard_templates = $wpdb->prefix . 'fleekdash_analytics_dashboard_templates';
        $this->table_user_dashboards = $wpdb->prefix . 'fleekdash_analytics_user_dashboards';

        // Initialize cache service
        $this->cache_service = new AnalyticsCacheService();
    }

    /**
     * Get singleton instance
     */
    public static function getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    // ========================================
    // CATEGORIES MANAGEMENT
    // ========================================

    /**
     * Get all analytics categories
     */
    public function getCategories($active_only = true)
    {
        $cache_key = "universal_categories_" . ($active_only ? 'active' : 'all');

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($active_only) {
                $where = $active_only ? "WHERE is_active = 1" : "";
                $sql = "SELECT * FROM {$this->table_categories} {$where} ORDER BY sort_order ASC, name ASC";
                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            3600 // 1 hour cache
        );
    }

    /**
     * Get category by slug
     */
    public function getCategoryBySlug($slug)
    {
        $cache_key = "universal_category_{$slug}";

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($slug) {
                $sql = $this->wpdb->prepare(
                    "SELECT * FROM {$this->table_categories} WHERE slug = %s AND is_active = 1",
                    $slug,
                );
                return $this->wpdb->get_row($sql, ARRAY_A);
            },
            3600,
        );
    }

    /**
     * Create or update category
     */
    public function saveCategory($data)
    {
        $defaults = [
            'is_active' => 1,
            'sort_order' => 0,
            'color' => '#3858e9'
        ];

        $data = array_merge($defaults, $data);

        if (isset($data['id'])) {
            // Update existing
            $result = $this->wpdb->update(
                $this->table_categories,
                $data,
                ['id' => $data['id']],
            );
        } else {
            // Create new
            $result = $this->wpdb->insert($this->table_categories, $data);
        }

        if ($result !== false) {
            $this->cache_service->invalidate_cache("universal_categories");
            return true;
        }

        return false;
    }

    // ========================================
    // METRICS MANAGEMENT
    // ========================================

    /**
     * Get metrics by category
     */
    public function getMetricsByCategory($category_slug, $active_only = true)
    {
        $cache_key = "universal_metrics_{$category_slug}_" . ($active_only ? 'active' : 'all');

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($category_slug, $active_only) {
                $where = $active_only ? "AND m.is_active = 1" : "";
                $sql = $this->wpdb->prepare(
                    "SELECT m.*, c.name as category_name, c.slug as category_slug
                     FROM {$this->table_metrics} m
                     JOIN {$this->table_categories} c ON m.category_id = c.id
                     WHERE c.slug = %s {$where}
                     ORDER BY m.sort_order ASC, m.name ASC",
                    $category_slug,
                );
                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            3600,
        );
    }

    /**
     * Get metric by slug
     */
    public function getMetricBySlug($slug)
    {
        $cache_key = "universal_metric_{$slug}";

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($slug) {
                $sql = $this->wpdb->prepare(
                    "SELECT m.*, c.name as category_name, c.slug as category_slug
                     FROM {$this->table_metrics} m
                     JOIN {$this->table_categories} c ON m.category_id = c.id
                     WHERE m.slug = %s AND m.is_active = 1",
                    $slug,
                );
                return $this->wpdb->get_row($sql, ARRAY_A);
            },
            3600,
        );
    }

    /**
     * Get all available metrics
     */
    public function getAllMetrics($active_only = true)
    {
        $cache_key = "universal_all_metrics_" . ($active_only ? 'active' : 'all');

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($active_only) {
                $where = $active_only ? "WHERE m.is_active = 1" : "";
                $sql = "SELECT m.*, c.name as category_name, c.slug as category_slug
                        FROM {$this->table_metrics} m
                        JOIN {$this->table_categories} c ON m.category_id = c.id
                        {$where}
                        ORDER BY c.sort_order ASC, m.sort_order ASC, m.name ASC";
                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            3600,
        );
    }

    // ========================================
    // DATA SOURCES MANAGEMENT
    // ========================================

    /**
     * Get all data sources
     */
    public function getDataSources($active_only = true)
    {
        $cache_key = "universal_sources_" . ($active_only ? 'active' : 'all');

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($active_only) {
                $where = $active_only ? "WHERE is_active = 1" : "";
                $sql = "SELECT * FROM {$this->table_sources} {$where} ORDER BY name ASC";
                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            1800 // 30 minutes cache
        );
    }

    /**
     * Get data source by slug
     */
    public function getDataSourceBySlug($slug)
    {
        $cache_key = "universal_source_{$slug}";

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($slug) {
                $sql = $this->wpdb->prepare(
                    "SELECT * FROM {$this->table_sources} WHERE slug = %s AND is_active = 1",
                    $slug,
                );
                return $this->wpdb->get_row($sql, ARRAY_A);
            },
            1800,
        );
    }

    /**
     * Get metrics available for a specific source
     */
    public function getSourceMetrics($source_slug)
    {
        $cache_key = "universal_source_metrics_{$source_slug}";

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($source_slug) {
                $sql = $this->wpdb->prepare(
                    "SELECT sm.*, m.name as metric_name, m.slug as metric_slug,
                            m.data_type, m.aggregation_type, c.name as category_name
                     FROM {$this->table_source_metrics} sm
                     JOIN {$this->table_metrics} m ON sm.metric_id = m.id
                     JOIN {$this->table_categories} c ON m.category_id = c.id
                     JOIN {$this->table_sources} s ON sm.source_id = s.id
                     WHERE s.slug = %s AND sm.is_active = 1 AND m.is_active = 1
                     ORDER BY c.sort_order ASC, m.sort_order ASC",
                    $source_slug,
                );
                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            1800,
        );
    }

    // ========================================
    // UNIVERSAL DATA MANAGEMENT
    // ========================================

    /**
     * Get aggregated data for a specific metric and time range
     */
    public function getMetricData($metric_slug, $start_date, $end_date, $sources = null, $aggregation = 'daily')
    {
        $metric = $this->getMetricBySlug($metric_slug);
        if (!$metric) {
            return [];
        }

        $cache_key = "universal_data_{$metric_slug}_{$start_date}_{$end_date}";
        if ($sources) {
            $cache_key .= "_" . implode('_', $sources);
        }

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($metric, $start_date, $end_date, $sources) {
                $where_conditions = [
                    "ud.metric_id = {$metric['id']}",
                    "ud.date BETWEEN '{$start_date}' AND '{$end_date}'"
                ];

                if ($sources) {
                    $source_ids = $this->getSourceIdsBySlugs($sources);
                    if (!empty($source_ids)) {
                        $source_ids_str = implode(',', $source_ids);
                        $where_conditions[] = "ud.source_id IN ({$source_ids_str})";
                    }
                }

                $where_clause = implode(' AND ', $where_conditions);

                $sql = "SELECT ud.*, s.name as source_name, s.slug as source_slug
                        FROM {$this->table_universal_data} ud
                        JOIN {$this->table_sources} s ON ud.source_id = s.id
                        WHERE {$where_clause}
                        ORDER BY ud.date ASC, s.name ASC";

                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            900 // 15 minutes cache
        );
    }

    /**
     * Get aggregated data for multiple metrics
     */
    public function getMultipleMetricsData($metrics, $start_date, $end_date, $sources = null)
    {
        $results = [];

        foreach ($metrics as $metric_slug) {
            $results[$metric_slug] = $this->getMetricData($metric_slug, $start_date, $end_date, $sources);
        }

        return $results;
    }

    /**
     * Insert or update universal data
     */
    public function saveMetricData($metric_slug, $source_slug, $date, $value, $metadata = null)
    {
        $metric = $this->getMetricBySlug($metric_slug);
        $source = $this->getDataSourceBySlug($source_slug);

        if (!$metric || !$source) {
            return false;
        }

        $data = [
            'metric_id' => $metric['id'],
            'source_id' => $source['id'],
            'date' => $date,
            'value' => $value,
            'metadata' => $metadata ? json_encode($metadata) : null
        ];

        // Check if record exists
        $existing = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT id FROM {$this->table_universal_data}
                 WHERE metric_id = %d AND source_id = %d AND date = %s",
                $metric['id'],
                $source['id'],
                $date,
            ),
        );

        if ($existing) {
            // Update existing
            $result = $this->wpdb->update(
                $this->table_universal_data,
                $data,
                ['id' => $existing->id],
            );
        } else {
            // Insert new
            $result = $this->wpdb->insert($this->table_universal_data, $data);
        }

        if ($result !== false) {
            // Invalidate related caches
            $this->cache_service->invalidate_cache("universal_data_{$metric_slug}");
            return true;
        }

        return false;
    }

    // ========================================
    // DASHBOARD TEMPLATES
    // ========================================

    /**
     * Get dashboard templates by category
     */
    public function getDashboardTemplates($category_slug = null)
    {
        $cache_key = "dashboard_templates" . ($category_slug ? "_{$category_slug}" : "");

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($category_slug) {
                $where = $category_slug ?
                    $this->wpdb->prepare("WHERE dt.category_id = (SELECT id FROM {$this->table_categories} WHERE slug = %s)", $category_slug) :
                    "WHERE dt.is_active = 1";

                $sql = "SELECT dt.*, c.name as category_name, c.slug as category_slug
                        FROM {$this->table_dashboard_templates} dt
                        LEFT JOIN {$this->table_categories} c ON dt.category_id = c.id
                        {$where}
                        ORDER BY dt.is_default DESC, dt.name ASC";

                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            3600,
        );
    }

    /**
     * Get user dashboard configurations
     */
    public function getUserDashboards($user_id)
    {
        $cache_key = "user_dashboards_{$user_id}";

        return $this->cache_service->get_cached_data(
            $cache_key,
            [],
            function () use ($user_id) {
                $sql = $this->wpdb->prepare(
                    "SELECT * FROM {$this->table_user_dashboards}
                     WHERE user_id = %d
                     ORDER BY is_default DESC, name ASC",
                    $user_id,
                );
                return $this->wpdb->get_results($sql, ARRAY_A);
            },
            1800,
        );
    }

    /**
     * Save user dashboard configuration
     */
    public function saveUserDashboard($user_id, $data)
    {
        $data['user_id'] = $user_id;

        if (isset($data['id'])) {
            // Update existing
            $result = $this->wpdb->update(
                $this->table_user_dashboards,
                $data,
                ['id' => $data['id']],
            );
        } else {
            // Create new
            $result = $this->wpdb->insert($this->table_user_dashboards, $data);
        }

        if ($result !== false) {
            $this->cache_service->invalidate_cache("user_dashboards_{$user_id}");
            return true;
        }

        return false;
    }

    // ========================================
    // HELPER METHODS
    // ========================================

    /**
     * Get source IDs by slugs
     */
    private function getSourceIdsBySlugs($slugs)
    {
        if (empty($slugs)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($slugs), '%s'));
        $sql = $this->wpdb->prepare(
            "SELECT id FROM {$this->table_sources} WHERE slug IN ({$placeholders})",
            $slugs,
        );

        return $this->wpdb->get_col($sql);
    }

    /**
     * Get system statistics
     */
    public function getSystemStats()
    {
        $stats = [
            'categories' => $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->table_categories} WHERE is_active = 1"),
            'metrics' => $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->table_metrics} WHERE is_active = 1"),
            'sources' => $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->table_sources} WHERE is_active = 1"),
            'data_points' => $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->table_universal_data}"),
            'templates' => $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->table_dashboard_templates} WHERE is_active = 1")
        ];

        return $stats;
    }

    /**
     * Check if universal analytics system is installed
     */
    public function isInstalled()
    {
        $table_exists = $this->wpdb->get_var(
            "SHOW TABLES LIKE '{$this->table_categories}'",
        );

        return $table_exists === $this->table_categories;
    }

    /**
     * Install universal analytics system
     */
    public function install()
    {
        if ($this->isInstalled()) {
            return true;
        }

        // Use the correct migration file
        $migration_file = FLEEKDASH_DIR . '/database/Migrations/2025_01_08_000001_create_analytics_tables.php';

        if (file_exists($migration_file)) {
            $migration = require_once $migration_file;

            if ($migration && method_exists($migration, 'up')) {
                $migration->up();
            }
        } else {
            fleekdash_log_error('FleekDash: Analytics migration file not found');
            return false;
        }

        return $this->isInstalled();
    }
}
