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

/**
 * Portal Model for Super Admin
 */
class Portal_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    public function get_all($status = null) {
        if ($status) {
            $this->db->where('status', $status);
        }
        $this->db->select('id, portal_code as code, portal_name as name, portal_url as url, logo_url, primary_color, status, created_at, updated_at');
        $this->db->order_by('portal_name', 'ASC');
        return $this->db->get('portals')->result();
    }
    
    public function get_all_active() {
        return $this->get_all('active');
    }

    public function get($id) {
        $this->db->select('id, portal_code as code, portal_name as name, portal_url as url, logo_url, primary_color, status, created_at, updated_at');
        return $this->db->where('id', $id)->get('portals')->row();
    }

    public function get_by_code($code) {
        $this->db->select('id, portal_code as code, portal_name as name, portal_url as url, logo_url, primary_color, status, created_at, updated_at');
        return $this->db->where('portal_code', $code)->get('portals')->row();
    }

    public function create($data) {
        $this->db->insert('portals', $data);
        return $this->db->insert_id();
    }

    public function update($id, $data) {
        $this->db->where('id', $id);
        return $this->db->update('portals', $data);
    }

    public function delete($id) {
        $this->db->where('id', $id);
        return $this->db->delete('portals');
    }

    public function get_stats($portal_code) {
        $stats = new stdClass();
        
        $this->db->where('portal_id', $portal_code);
        $stats->total_users = $this->db->count_all_results('users');

        $this->db->where('portal_id', $portal_code);
        $stats->total_campaigns = $this->db->count_all_results('campaigns');

        $this->db->where('portal_id', $portal_code);
        $stats->total_master_data = $this->db->count_all_results('master_data');

        $query = $this->db->query("
            SELECT 
                SUM(CASE WHEN ms.status = 'sent' THEN 1 ELSE 0 END) as total_sent,
                SUM(CASE WHEN ms.status = 'failed' THEN 1 ELSE 0 END) as total_failed,
                SUM(CASE WHEN ms.status = 'pending' THEN 1 ELSE 0 END) as total_pending
            FROM message_schedules ms
            JOIN campaigns c ON ms.campaign_id = c.id
            WHERE c.portal_id = ?
        ", [$portal_code]);
        
        $send_stats = $query->row();
        $stats->total_sent = $send_stats->total_sent ?? 0;
        $stats->total_failed = $send_stats->total_failed ?? 0;
        $stats->total_pending = $send_stats->total_pending ?? 0;

        return $stats;
    }

    public function get_all_with_stats() {
        $query = $this->db->query("
            SELECT 
                p.id,
                p.portal_code,
                p.portal_name,
                p.portal_url,
                p.logo_url,
                p.primary_color,
                p.status,
                p.created_at,
                (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) as message_count
            FROM portals p
            ORDER BY p.portal_name ASC
        ");
        
        return $query->result_array();
    }

    public function get_dashboard_summary() {
        $summary = new stdClass();

        $summary->total_portals = $this->db->count_all('portals');

        $this->db->where('status', 'active');
        $summary->active_portals = $this->db->count_all_results('portals');

        $summary->total_users = $this->db->count_all('users');
        $summary->total_campaigns = $this->db->count_all('campaigns');
        $summary->total_master_data = $this->db->count_all('master_data');

        $query = $this->db->query("
            SELECT 
                SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as total_sent,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as total_failed,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as total_pending
            FROM message_schedules
        ");
        $stats = $query->row();
        
        $summary->total_sent = $stats->total_sent ?? 0;
        $summary->total_failed = $stats->total_failed ?? 0;
        $summary->total_pending = $stats->total_pending ?? 0;

        return $summary;
    }
}
