<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Analytics_model extends CI_Model {

    private $click_logs_table = 'click_logs';

    public function __construct() {
        parent::__construct();
        $this->create_tables_if_not_exist();
    }

    /**
     * Create analytics tables if they don't exist
     */
    private function create_tables_if_not_exist() {
        // Check if click_logs table exists
        if (!$this->db->table_exists($this->click_logs_table)) {
            $this->create_click_logs_table();
        }
    }

    /**
     * Create click_logs table
     */
    private function create_click_logs_table() {
        $sql = "
            CREATE TABLE IF NOT EXISTS {$this->click_logs_table} (
                id INT AUTO_INCREMENT PRIMARY KEY,
                event_type VARCHAR(50) DEFAULT 'click',
                element_type VARCHAR(100) NOT NULL,
                element_id VARCHAR(255),
                url TEXT,
                section VARCHAR(100),
                label VARCHAR(255),
                user_id INT NULL,
                user_agent TEXT,
                referrer TEXT,
                page_url TEXT,
                ip_address VARCHAR(45),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_element_type (element_type),
                INDEX idx_section (section),
                INDEX idx_created_at (created_at),
                INDEX idx_url (url(100))
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        $this->db->query($sql);
    }

    /**
     * Log a click event
     */
    public function log_click($data) {
        $this->db->insert($this->click_logs_table, $data);
        return $this->db->insert_id();
    }

    /**
     * Get click data with filters
     */
    public function get_clicks($filters = []) {
        $this->db->select('*');
        $this->db->from($this->click_logs_table);
        
        if (!empty($filters['start_date'])) {
            $this->db->where('created_at >=', $filters['start_date']);
        }
        
        if (!empty($filters['end_date'])) {
            $this->db->where('created_at <=', $filters['end_date']);
        }
        
        if (!empty($filters['element_type'])) {
            $this->db->where('element_type', $filters['element_type']);
        }
        
        if (!empty($filters['section'])) {
            $this->db->where('section', $filters['section']);
        }
        
        $this->db->order_by('created_at', 'DESC');
        
        if (isset($filters['limit'])) {
            $this->db->limit($filters['limit'], $filters['offset'] ?? 0);
        }
        
        return $this->db->get()->result_array();
    }

    /**
     * Count clicks with filters
     */
    public function count_clicks($filters = []) {
        $this->db->from($this->click_logs_table);
        
        if (!empty($filters['start_date'])) {
            $this->db->where('created_at >=', $filters['start_date']);
        }
        
        if (!empty($filters['end_date'])) {
            $this->db->where('created_at <=', $filters['end_date']);
        }
        
        if (!empty($filters['element_type'])) {
            $this->db->where('element_type', $filters['element_type']);
        }
        
        if (!empty($filters['section'])) {
            $this->db->where('section', $filters['section']);
        }
        
        return $this->db->count_all_results();
    }

    /**
     * Get monthly click statistics
     */
    public function get_monthly_stats($year, $element_type = null) {
        $sql = "
            SELECT 
                MONTH(created_at) as month,
                MONTHNAME(created_at) as month_name,
                COUNT(*) as total_clicks,
                COUNT(DISTINCT ip_address) as unique_visitors,
                element_type
            FROM {$this->click_logs_table} 
            WHERE YEAR(created_at) = ?
        ";
        
        $params = [$year];
        
        if ($element_type) {
            $sql .= " AND element_type = ?";
            $params[] = $element_type;
        }
        
        $sql .= " GROUP BY MONTH(created_at), element_type ORDER BY month ASC";
        
        return $this->db->query($sql, $params)->result_array();
    }

    /**
     * Get top clicked elements
     */
    public function get_top_clicked_elements($year, $element_type = null, $limit = 10) {
        $sql = "
            SELECT 
                element_id,
                element_type,
                label,
                url,
                section,
                COUNT(*) as click_count,
                COUNT(DISTINCT ip_address) as unique_clicks
            FROM {$this->click_logs_table} 
            WHERE YEAR(created_at) = ?
        ";
        
        $params = [$year];
        
        if ($element_type) {
            $sql .= " AND element_type = ?";
            $params[] = $element_type;
        }
        
        $sql .= " 
            GROUP BY element_id, element_type 
            ORDER BY click_count DESC 
            LIMIT ?
        ";
        
        $params[] = $limit;
        
        return $this->db->query($sql, $params)->result_array();
    }

    /**
     * Get statistics for specific month and year
     */
    public function get_monthly_specific_stats($year, $month, $element_type = null) {
        $sql = "
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as total_clicks,
                COUNT(DISTINCT ip_address) as unique_visitors,
                element_type,
                element_id,
                label,
                url,
                section,
                HOUR(created_at) as hour
            FROM {$this->click_logs_table} 
            WHERE YEAR(created_at) = ? AND MONTH(created_at) = ?
        ";
        
        $params = [$year, $month];
        
        if ($element_type) {
            $sql .= " AND element_type = ?";
            $params[] = $element_type;
        }
        
        $sql .= " 
            GROUP BY DATE(created_at), element_type, element_id 
            ORDER BY date DESC, total_clicks DESC
        ";
        
        return $this->db->query($sql, $params)->result_array();
    }

    /**
     * Get YouTube specific statistics
     */
    public function get_youtube_stats($period = 'month', $specific_year = null, $specific_month = null) {
        $date_condition = '';
        $params = [];
        
        if ($specific_year && $specific_month) {
            // Specific month and year
            $date_condition = "AND YEAR(created_at) = ? AND MONTH(created_at) = ?";
            $params = [$specific_year, $specific_month];
        } else {
            // Period-based filtering
            switch ($period) {
                case 'month':
                    $date_condition = "AND created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";
                    break;
                case 'year':
                    $date_condition = "AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
                    break;
                case 'all':
                default:
                    $date_condition = '';
            }
        }
        
        $sql = "
            SELECT 
                element_id,
                label,
                url,
                COUNT(*) as total_clicks,
                COUNT(DISTINCT ip_address) as unique_clicks,
                DATE(created_at) as click_date,
                COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_clicks
            FROM {$this->click_logs_table} 
            WHERE element_type LIKE '%youtube%' 
            {$date_condition}
            GROUP BY element_id, DATE(created_at)
            ORDER BY total_clicks DESC, click_date DESC
        ";
        
        $results = $this->db->query($sql, $params)->result_array();
        
        // Get summary stats
        $summary_sql = "
            SELECT 
                COUNT(*) as total_youtube_clicks,
                COUNT(DISTINCT ip_address) as total_unique_visitors,
                COUNT(DISTINCT DATE(created_at)) as days_with_clicks,
                MAX(created_at) as last_click_date
            FROM {$this->click_logs_table} 
            WHERE element_type LIKE '%youtube%'
            {$date_condition}
        ";
        
        $summary = $this->db->query($summary_sql, $params)->row_array();
        
        return [
            'period' => $period,
            'summary' => $summary,
            'daily_stats' => $results
        ];
    }

    /**
     * Get click trends by day
     */
    public function get_daily_trends($days = 30, $element_type = null) {
        $sql = "
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as clicks,
                COUNT(DISTINCT ip_address) as unique_visitors
            FROM {$this->click_logs_table} 
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
        ";
        
        $params = [$days];
        
        if ($element_type) {
            $sql .= " AND element_type = ?";
            $params[] = $element_type;
        }
        
        $sql .= " GROUP BY DATE(created_at) ORDER BY date DESC";
        
        return $this->db->query($sql, $params)->result_array();
    }
}