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

class Campaign_model extends CI_Model {

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

    /**
     * Get campaign by ID
     */
    public function get_by_id($id) {
        $this->db->select('campaigns.*, users.username as creator_name');
        $this->db->from('campaigns');
        $this->db->join('users', 'users.id = campaigns.created_by');
        $this->db->where('campaigns.id', $id);
        
        return $this->db->get()->row();
    }

    /**
     * Get all campaigns with pagination
     */
    public function get_all_campaigns($limit = 10, $offset = 0, $status = null) {
        $this->db->select('campaigns.*, users.username as creator_name');
        $this->db->from('campaigns');
        $this->db->join('users', 'users.id = campaigns.created_by');
        
        if ($status) {
            $this->db->where('campaigns.status', $status);
        }
        
        $this->db->limit($limit, $offset);
        $this->db->order_by('campaigns.created_at', 'DESC');
        
        return $this->db->get()->result();
    }

    /**
     * Count total campaigns
     */
    public function count_campaigns($status = null) {
        if ($status) {
            $this->db->where('status', $status);
        }
        
        return $this->db->count_all_results('campaigns');
    }

    /**
     * Create new campaign
     */
    public function create_campaign($data) {
        $insert_data = [
            'name' => $data['name'],
            'type' => $data['type'],
            'fail_percentage' => $data['fail_percentage'],
            'status' => isset($data['status']) ? $data['status'] : 'active',
            'created_by' => $data['created_by']
        ];
        
        $this->db->insert('campaigns', $insert_data);
        return $this->db->insert_id();
    }

    /**
     * Update campaign
     */
    public function update_campaign($id, $data) {
        $this->db->where('id', $id);
        return $this->db->update('campaigns', $data);
    }

    /**
     * Delete campaign
     */
    public function delete_campaign($id) {
        return $this->db->delete('campaigns', ['id' => $id]);
    }

    /**
     * Update campaign status
     */
    public function update_status($id, $status) {
        return $this->update_campaign($id, ['status' => $status]);
    }

    /**
     * Update campaign counters
     */
    public function update_counters($campaign_id) {
        // Count total assigned data
        $this->db->select('COUNT(*) as total');
        $this->db->from('campaign_user_data');
        $this->db->where('campaign_id', $campaign_id);
        $total = $this->db->get()->row()->total;
        
        // Count sent
        $this->db->select('COUNT(*) as sent');
        $this->db->from('campaign_user_data');
        $this->db->where('campaign_id', $campaign_id);
        $this->db->where('status', 'sent');
        $sent = $this->db->get()->row()->sent;
        
        // Count failed
        $this->db->select('COUNT(*) as failed');
        $this->db->from('campaign_user_data');
        $this->db->where('campaign_id', $campaign_id);
        $this->db->where('status', 'failed');
        $failed = $this->db->get()->row()->failed;
        
        // Count remaining (pending)
        $this->db->select('COUNT(*) as remaining');
        $this->db->from('campaign_user_data');
        $this->db->where('campaign_id', $campaign_id);
        $this->db->where('status', 'pending');
        $remaining = $this->db->get()->row()->remaining;
        
        // Update campaign
        $update_data = [
            'total_assigned' => $total,
            'total_sent' => $sent,
            'total_failed' => $failed,
            'total_remaining' => $remaining
        ];
        
        return $this->update_campaign($campaign_id, $update_data);
    }

    /**
     * Assign users to campaign
     */
    public function assign_users($campaign_id, $user_ids) {
        $this->db->trans_start();
        
        foreach ($user_ids as $user_id) {
            $this->db->insert('campaign_users', [
                'campaign_id' => $campaign_id,
                'user_id' => $user_id
            ]);
        }
        
        $this->db->trans_complete();
        return $this->db->trans_status();
    }

    /**
     * Remove user from campaign
     */
    public function remove_user($campaign_id, $user_id) {
        return $this->db->delete('campaign_users', [
            'campaign_id' => $campaign_id,
            'user_id' => $user_id
        ]);
    }

    /**
     * Get campaigns for specific user
     */
    public function get_user_campaigns($user_id, $limit = 10, $offset = 0) {
        $this->db->select('campaigns.*, COUNT(cud.id) as my_assigned, 
                          SUM(CASE WHEN cud.status = "sent" THEN 1 ELSE 0 END) as my_sent,
                          SUM(CASE WHEN cud.status = "failed" THEN 1 ELSE 0 END) as my_failed,
                          SUM(CASE WHEN cud.status = "pending" THEN 1 ELSE 0 END) as my_remaining');
        $this->db->from('campaigns');
        $this->db->join('campaign_users cu', 'cu.campaign_id = campaigns.id');
        $this->db->join('campaign_user_data cud', 'cud.campaign_id = campaigns.id AND cud.user_id = cu.user_id', 'left');
        $this->db->where('cu.user_id', $user_id);
        $this->db->group_by('campaigns.id');
        $this->db->limit($limit, $offset);
        $this->db->order_by('campaigns.created_at', 'DESC');
        
        return $this->db->get()->result();
    }

    /**
     * Count user campaigns
     */
    public function count_user_campaigns($user_id) {
        $this->db->from('campaign_users');
        $this->db->where('user_id', $user_id);
        
        return $this->db->count_all_results();
    }

    /**
     * Get campaign stats for dashboard
     */
    public function get_dashboard_stats() {
        $stats = [
            'total_campaigns' => $this->count_campaigns(),
            'active_campaigns' => $this->count_campaigns('active'),
            'paused_campaigns' => $this->count_campaigns('paused'),
            'completed_campaigns' => $this->count_campaigns('completed')
        ];
        
        // Total messages sent today
        $this->db->select('COUNT(*) as total');
        $this->db->from('send_logs');
        $this->db->where('DATE(created_at)', date('Y-m-d'));
        $stats['today_sent'] = $this->db->get()->row()->total;
        
        // Total sent
        $this->db->select('SUM(total_sent) as total');
        $this->db->from('campaigns');
        $stats['total_sent'] = $this->db->get()->row()->total ?? 0;
        
        // Total failed
        $this->db->select('SUM(total_failed) as total');
        $this->db->from('campaigns');
        $stats['total_failed'] = $this->db->get()->row()->total ?? 0;
        
        return $stats;
    }

    /**
     * Get campaign performance data for charts
     */
    public function get_performance_chart_data($days = 7) {
        $this->db->select('DATE(created_at) as date, 
                          SUM(CASE WHEN status = "sent" THEN 1 ELSE 0 END) as sent,
                          SUM(CASE WHEN status = "failed" THEN 1 ELSE 0 END) as failed');
        $this->db->from('send_logs');
        $this->db->where('created_at >=', date('Y-m-d', strtotime("-$days days")));
        $this->db->group_by('DATE(created_at)');
        $this->db->order_by('date', 'ASC');
        
        return $this->db->get()->result();
    }
    
    /**
     * Update counters using optimized SQL - For 2.1M+ records
     */
    public function update_counters_optimized($campaign_id) {
        $sql = "UPDATE campaigns c
                SET 
                    c.total_assigned = (SELECT COUNT(*) FROM campaign_user_data WHERE campaign_id = ?),
                    c.total_sent = (SELECT COUNT(*) FROM campaign_user_data WHERE campaign_id = ? AND status = 'sent'),
                    c.total_failed = (SELECT COUNT(*) FROM campaign_user_data WHERE campaign_id = ? AND status = 'failed'),
                    c.total_remaining = (SELECT COUNT(*) FROM campaign_user_data WHERE campaign_id = ? AND status = 'pending')
                WHERE c.id = ?";
        
        return $this->db->query($sql, [$campaign_id, $campaign_id, $campaign_id, $campaign_id, $campaign_id]);
    }
    
    /**
     * Get all campaigns with stats - Optimized for large datasets
     * Uses direct SQL for better performance with 2.1M+ records
     */
    public function get_all_campaigns_optimized($limit = 10, $offset = 0, $status = null) {
        $where_status = $status ? "AND c.status = " . $this->db->escape($status) : "";
        
        $sql = "SELECT c.*, u.username as creator_name
                FROM campaigns c
                LEFT JOIN users u ON u.id = c.created_by
                WHERE 1=1 $where_status
                ORDER BY c.created_at DESC
                LIMIT ?, ?";
        
        return $this->db->query($sql, [(int)$offset, (int)$limit])->result();
    }
    
    /**
     * Get user campaigns with stats - Optimized for large datasets
     */
    public function get_user_campaigns_optimized($user_id, $limit = 10, $offset = 0) {
        $sql = "SELECT c.*,
                    (SELECT COUNT(*) FROM campaign_user_data cud WHERE cud.campaign_id = c.id AND cud.user_id = ?) as my_assigned,
                    (SELECT COUNT(*) FROM campaign_user_data cud WHERE cud.campaign_id = c.id AND cud.user_id = ? AND cud.status = 'sent') as my_sent,
                    (SELECT COUNT(*) FROM campaign_user_data cud WHERE cud.campaign_id = c.id AND cud.user_id = ? AND cud.status = 'failed') as my_failed,
                    (SELECT COUNT(*) FROM campaign_user_data cud WHERE cud.campaign_id = c.id AND cud.user_id = ? AND cud.status = 'pending') as my_remaining
                FROM campaigns c
                INNER JOIN campaign_users cu ON cu.campaign_id = c.id AND cu.user_id = ?
                ORDER BY c.created_at DESC
                LIMIT ?, ?";
        
        return $this->db->query($sql, [$user_id, $user_id, $user_id, $user_id, $user_id, (int)$offset, (int)$limit])->result();
    }
}
