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

/**
 * Super Admin Reports Controller
 */
class Reports extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->model('superadmin/Portal_model');
        $this->load->model('superadmin/Super_admin_model');
        $this->_check_super_admin();
    }

    private function _check_super_admin() {
        if ($this->session->userdata('super_admin_logged_in') !== TRUE) {
            redirect('superadmin/auth/login');
        }
    }

    public function index() {
        $data['title'] = 'Reports & Analytics';
        $data['current_page'] = 'reports';
        $data['portals'] = $this->Portal_model->get_all_active();
        
        $data['overall_stats'] = $this->get_overall_stats();
        $data['portal_comparison'] = $this->get_portal_comparison();
        $data['monthly_trends'] = $this->get_monthly_trends();
        
        $this->load->view('superadmin/layouts/header', $data);
        $this->load->view('superadmin/reports/index', $data);
        $this->load->view('superadmin/layouts/footer');
    }
    
    private function get_overall_stats() {
        $stats = [];
        
        $query = $this->db->query("SELECT COUNT(*) as total FROM message_schedules WHERE status = 'sent'");
        $stats['total_sent'] = $query->row()->total;
        
        $query = $this->db->query("SELECT COUNT(*) as total FROM message_schedules WHERE status = 'failed'");
        $stats['total_failed'] = $query->row()->total;
        
        $query = $this->db->query("SELECT COUNT(*) as total FROM message_schedules WHERE status = 'pending'");
        $stats['total_pending'] = $query->row()->total;
        
        $total = $stats['total_sent'] + $stats['total_failed'];
        $stats['success_rate'] = $total > 0 ? round(($stats['total_sent'] / $total) * 100, 2) : 0;
        
        $query = $this->db->query("SELECT COUNT(*) as total FROM users WHERE role != 'super_admin'");
        $stats['total_users'] = $query->row()->total;
        
        $query = $this->db->query("SELECT COUNT(*) as total FROM master_data");
        $stats['total_data'] = $query->row()->total;
        
        return $stats;
    }
    
    private function get_portal_comparison() {
        return $this->db->query("
            SELECT 
                p.portal_code as code,
                p.portal_name as name,
                (SELECT COUNT(*) FROM users WHERE portal_id = p.portal_code) as user_count,
                (SELECT COUNT(*) FROM campaigns WHERE portal_id = p.portal_code) as campaign_count,
                (SELECT COUNT(*) FROM master_data WHERE portal_id = p.portal_code) as data_count,
                (SELECT COUNT(*) FROM message_schedules ms 
                 JOIN campaigns c ON ms.campaign_id = c.id 
                 WHERE c.portal_id = p.portal_code AND ms.status = 'sent') as sent_count
            FROM portals p
            WHERE p.status = 'active'
            ORDER BY sent_count DESC
        ")->result();
    }
    
    private function get_monthly_trends() {
        return $this->db->query("
            SELECT 
                DATE_FORMAT(ms.created_at, '%Y-%m') as month,
                COUNT(*) as total_scheduled,
                SUM(CASE WHEN ms.status = 'sent' THEN 1 ELSE 0 END) as sent,
                SUM(CASE WHEN ms.status = 'failed' THEN 1 ELSE 0 END) as failed
            FROM message_schedules ms
            WHERE ms.created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
            GROUP BY DATE_FORMAT(ms.created_at, '%Y-%m')
            ORDER BY month DESC
            LIMIT 12
        ")->result();
    }

    public function portal($code) {
        $portal = $this->Portal_model->get_by_code($code);
        
        if (!$portal) {
            show_404();
        }
        
        $data['title'] = $portal->name . ' - Detailed Report';
        $data['current_page'] = 'reports';
        $data['portal'] = $portal;
        $data['portals'] = $this->Portal_model->get_all_active();
        
        $data['user_stats'] = $this->db->query("
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admins,
                SUM(CASE WHEN role = 'user' THEN 1 ELSE 0 END) as users
            FROM users
            WHERE portal_id = ?
        ", [$code])->row();
        
        $data['campaign_stats'] = $this->db->query("
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft
            FROM campaigns
            WHERE portal_id = ?
        ", [$code])->row();
        
        $data['message_stats'] = $this->db->query("
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN ms.status = 'sent' THEN 1 ELSE 0 END) as sent,
                SUM(CASE WHEN ms.status = 'failed' THEN 1 ELSE 0 END) as failed,
                SUM(CASE WHEN ms.status = 'pending' THEN 1 ELSE 0 END) as pending
            FROM message_schedules ms
            JOIN campaigns c ON ms.campaign_id = c.id
            WHERE c.portal_id = ?
        ", [$code])->row();
        
        $data['daily_activity'] = $this->db->query("
            SELECT 
                DATE(ms.created_at) as date,
                COUNT(*) as scheduled,
                SUM(CASE WHEN ms.status = 'sent' THEN 1 ELSE 0 END) as sent
            FROM message_schedules ms
            JOIN campaigns c ON ms.campaign_id = c.id
            WHERE c.portal_id = ? AND ms.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
            GROUP BY DATE(ms.created_at)
            ORDER BY date DESC
        ", [$code])->result();
        
        $this->load->view('superadmin/layouts/header', $data);
        $this->load->view('superadmin/reports/portal', $data);
        $this->load->view('superadmin/layouts/footer');
    }
}
