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

/**
 * Reminder Model
 * Handles all reminder-related database operations
 */
class Reminder_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Get all pending reminders that need to be sent today
     * Based on document due dates and reminder settings
     */
    public function getPendingReminders($company_id = null)
    {
        $today = date('Y-m-d');
        $reminders = [];
        
        // Get reminder days configuration
        $reminder_days = $this->getReminderDays();
        
        // Get all enabled document heads with their reminder settings
        $sql = "
            SELECT 
                cdh.company_id,
                cdh.type_id,
                st.type_name,
                st.alias_name,
                st.authority_id,
                st.frequency,
                st.frequency_start_date,
                st.due_in_same_next_month,
                a.authority_name,
                c.company_name,
                c.contact_email as company_email,
                cdhr.reminder_no,
                cdhr.days_before,
                cdhr.reminder_to_user,
                cdhr.reminder_to_admin,
                cdhr.reminder_to_super_admin
            FROM company_document_heads cdh
            JOIN sub_type st ON st.id = cdh.type_id AND st.status = '1'
            JOIN company c ON c.id = cdh.company_id AND c.status = '1'
            LEFT JOIN authority a ON a.id::text = st.authority_id
            LEFT JOIN company_document_head_reminders cdhr ON cdhr.company_id = cdh.company_id 
                AND cdhr.type_id = cdh.type_id AND cdhr.is_enabled = true
            WHERE cdh.is_enabled = true
        ";
        
        if ($company_id) {
            $sql .= " AND cdh.company_id = " . intval($company_id);
        }
        
        $sql .= " ORDER BY cdh.company_id, cdh.type_id, cdhr.reminder_no";
        
        $result = $this->db->query($sql)->result_array();
        
        foreach ($result as $row) {
            // Calculate due dates for current and next periods
            $dueDates = $this->calculateDueDates($row);
            
            foreach ($dueDates as $dueInfo) {
                $dueDate = $dueInfo['due_date'];
                $periodMonth = $dueInfo['month'];
                $periodYear = $dueInfo['year'];
                
                // Check if document is already uploaded
                if ($this->isDocumentUploaded($row['company_id'], $row['type_id'], $periodMonth, $periodYear)) {
                    continue;
                }
                
                // Calculate days until due
                $daysUntilDue = (strtotime($dueDate) - strtotime($today)) / 86400;
                
                // Check if we should send a reminder today
                $days_before = intval($row['days_before'] ?? 0);
                
                // Also check default reminders (0 = on due date, -1 = after due date)
                $shouldRemind = false;
                
                if ($row['days_before'] !== null && $daysUntilDue == $days_before) {
                    $shouldRemind = true;
                } elseif ($daysUntilDue == 0) {
                    // On due date - always remind
                    $shouldRemind = true;
                    $days_before = 0;
                } elseif ($daysUntilDue == -1) {
                    // 1 day after due date - always remind
                    $shouldRemind = true;
                    $days_before = -1;
                }
                
                if ($shouldRemind) {
                    $reminders[] = [
                        'company_id' => $row['company_id'],
                        'company_name' => $row['company_name'],
                        'company_email' => $row['company_email'],
                        'type_id' => $row['type_id'],
                        'type_name' => $row['type_name'],
                        'alias_name' => $row['alias_name'],
                        'authority_id' => $row['authority_id'],
                        'authority_name' => $row['authority_name'],
                        'frequency' => $row['frequency'],
                        'document_month' => $periodMonth,
                        'document_year' => $periodYear,
                        'due_date' => $dueDate,
                        'days_before' => $days_before,
                        'reminder_to_user' => $row['reminder_to_user'] ?? false,
                        'reminder_to_admin' => $row['reminder_to_admin'] ?? false,
                        'reminder_to_super_admin' => $row['reminder_to_super_admin'] ?? true
                    ];
                }
            }
        }
        
        return $reminders;
    }

    /**
     * Calculate due dates for a document head based on frequency
     */
    private function calculateDueDates($docHead)
    {
        $dueDates = [];
        $frequency = strtolower($docHead['frequency']);
        $frequencyDay = intval($docHead['frequency_start_date'] ?? 1);
        $dueInNextMonth = intval($docHead['due_in_same_next_month'] ?? 0);
        
        $currentMonth = intval(date('m'));
        $currentYear = intval(date('Y'));
        
        // Calculate for current and next month
        for ($i = 0; $i <= 1; $i++) {
            $month = $currentMonth + $i;
            $year = $currentYear;
            
            if ($month > 12) {
                $month -= 12;
                $year++;
            }
            
            $periodMonth = $month;
            $periodYear = $year;
            
            // Calculate due date based on frequency
            if ($frequency === 'monthly') {
                $dueMonth = $dueInNextMonth ? ($month == 12 ? 1 : $month + 1) : $month;
                $dueYear = $dueInNextMonth && $month == 12 ? $year + 1 : $year;
                $dueDate = sprintf('%04d-%02d-%02d', $dueYear, $dueMonth, min($frequencyDay, cal_days_in_month(CAL_GREGORIAN, $dueMonth, $dueYear)));
            } elseif ($frequency === 'quarterly') {
                // Quarter end months: 3, 6, 9, 12
                $quarterMonth = ceil($month / 3) * 3;
                $dueMonth = $dueInNextMonth ? ($quarterMonth == 12 ? 1 : $quarterMonth + 1) : $quarterMonth;
                $dueYear = $dueInNextMonth && $quarterMonth == 12 ? $year + 1 : $year;
                $dueDate = sprintf('%04d-%02d-%02d', $dueYear, $dueMonth, min($frequencyDay, cal_days_in_month(CAL_GREGORIAN, $dueMonth, $dueYear)));
                $periodMonth = $quarterMonth;
            } elseif ($frequency === 'half yearly' || $frequency === 'half-yearly') {
                // Half year end months: 6, 12
                $halfMonth = $month <= 6 ? 6 : 12;
                $dueMonth = $dueInNextMonth ? ($halfMonth == 12 ? 1 : $halfMonth + 1) : $halfMonth;
                $dueYear = $dueInNextMonth && $halfMonth == 12 ? $year + 1 : $year;
                $dueDate = sprintf('%04d-%02d-%02d', $dueYear, $dueMonth, min($frequencyDay, cal_days_in_month(CAL_GREGORIAN, $dueMonth, $dueYear)));
                $periodMonth = $halfMonth;
            } elseif ($frequency === 'yearly' || $frequency === 'annual') {
                $dueMonth = 12;
                $dueYear = $dueInNextMonth ? $year + 1 : $year;
                $dueDate = sprintf('%04d-%02d-%02d', $dueYear, $dueMonth, min($frequencyDay, cal_days_in_month(CAL_GREGORIAN, $dueMonth, $dueYear)));
                $periodMonth = 12;
            } else {
                continue; // Skip one-time or unknown frequencies
            }
            
            $dueDates[] = [
                'due_date' => $dueDate,
                'month' => $periodMonth,
                'year' => $periodYear
            ];
        }
        
        return $dueDates;
    }

    /**
     * Check if document is already uploaded for the period
     */
    public function isDocumentUploaded($company_id, $type_id, $month, $year)
    {
        $count = $this->db->where([
            'company_id' => $company_id,
            'type_id' => $type_id,
            'document_month' => $month,
            'document_year' => $year
        ])->count_all_results('uploaded_documents');
        
        return $count > 0;
    }

    /**
     * Get recipients for a reminder based on settings
     * Uses existing 'mobile_number' column from users table for WhatsApp
     */
    public function getRecipients($company_id, $type_id, $authority_id, $reminder_to_user, $reminder_to_admin, $reminder_to_super_admin)
    {
        $recipients = [];
        
        // Get Super Admins (always notified if enabled)
        if ($reminder_to_super_admin) {
            $super_admins = $this->db->select('id, first_name, last_name, email_id, mobile_number')
                ->where('role_id', 1)
                ->where('status', '1')
                ->get('users')
                ->result_array();
            
            foreach ($super_admins as $user) {
                $recipients[] = [
                    'user_id' => $user['id'],
                    'name' => $user['first_name'] . ' ' . $user['last_name'],
                    'email' => $user['email_id'],
                    'phone' => $user['mobile_number'],
                    'role' => 'super_admin'
                ];
            }
        }
        
        // Get Admins with access to this company
        if ($reminder_to_admin) {
            $admins = $this->db->select('u.id, u.first_name, u.last_name, u.email_id, u.mobile_number')
                ->from('users u')
                ->join('user_companies uc', 'uc.user_id = u.id', 'inner')
                ->where('u.role_id', 2)
                ->where('u.status', '1')
                ->where('uc.company_id', $company_id)
                ->get()
                ->result_array();
            
            foreach ($admins as $user) {
                // Avoid duplicates
                $exists = array_filter($recipients, function($r) use ($user) {
                    return $r['user_id'] == $user['id'];
                });
                
                if (empty($exists)) {
                    $recipients[] = [
                        'user_id' => $user['id'],
                        'name' => $user['first_name'] . ' ' . $user['last_name'],
                        'email' => $user['email_id'],
                        'phone' => $user['mobile_number'],
                        'role' => 'admin'
                    ];
                }
            }
        }
        
        // Get Users assigned to this document head/authority
        if ($reminder_to_user) {
            $users = $this->db->select('u.id, u.first_name, u.last_name, u.email_id, u.mobile_number')
                ->from('users u')
                ->join('user_companies uc', 'uc.user_id = u.id', 'inner')
                ->join('user_authorities ua', 'ua.user_id = u.id', 'left')
                ->join('user_document_types udt', 'udt.user_id = u.id', 'left')
                ->where('u.role_id', 3)
                ->where('u.status', '1')
                ->where('uc.company_id', $company_id)
                ->group_start()
                    ->where('ua.authority_id', $authority_id)
                    ->or_where('udt.type_id', $type_id)
                ->group_end()
                ->group_by('u.id, u.first_name, u.last_name, u.email_id, u.mobile_number')
                ->get()
                ->result_array();
            
            foreach ($users as $user) {
                // Avoid duplicates
                $exists = array_filter($recipients, function($r) use ($user) {
                    return $r['user_id'] == $user['id'];
                });
                
                if (empty($exists)) {
                    $recipients[] = [
                        'user_id' => $user['id'],
                        'name' => $user['first_name'] . ' ' . $user['last_name'],
                        'email' => $user['email_id'],
                        'phone' => $user['mobile_number'],
                        'role' => 'user'
                    ];
                }
            }
        }
        
        return $recipients;
    }

    /**
     * Check if reminder was already sent
     */
    public function isReminderSent($company_id, $type_id, $user_id, $month, $year, $days_before, $reminder_type)
    {
        $exists = $this->db->where([
            'company_id' => $company_id,
            'type_id' => $type_id,
            'user_id' => $user_id,
            'document_month' => $month,
            'document_year' => $year,
            'days_before_due' => $days_before,
            'reminder_type' => $reminder_type,
            'status' => 'sent'
        ])->count_all_results('reminder_logs');
        
        return $exists > 0;
    }

    /**
     * Log a reminder
     */
    public function logReminder($data)
    {
        $log_data = [
            'company_id' => $data['company_id'],
            'type_id' => $data['type_id'],
            'authority_id' => $data['authority_id'] ?? null,
            'user_id' => $data['user_id'] ?? null,
            'reminder_type' => $data['reminder_type'],
            'recipient_email' => $data['recipient_email'] ?? null,
            'recipient_phone' => $data['recipient_phone'] ?? null,
            'recipient_name' => $data['recipient_name'] ?? null,
            'recipient_role' => $data['recipient_role'] ?? null,
            'document_month' => $data['document_month'],
            'document_year' => $data['document_year'],
            'due_date' => $data['due_date'],
            'days_before_due' => $data['days_before_due'],
            'status' => $data['status'] ?? 'pending',
            'error_message' => $data['error_message'] ?? null,
            'sent_at' => $data['status'] === 'sent' ? date('Y-m-d H:i:s') : null,
            'created_at' => date('Y-m-d H:i:s')
        ];
        
        // Use upsert to handle duplicates
        $this->db->query("
            INSERT INTO reminder_logs 
            (company_id, type_id, authority_id, user_id, reminder_type, recipient_email, recipient_phone, 
             recipient_name, recipient_role, document_month, document_year, due_date, days_before_due, 
             status, error_message, sent_at, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT (company_id, type_id, user_id, document_month, document_year, days_before_due, reminder_type)
            DO UPDATE SET 
                status = EXCLUDED.status,
                error_message = EXCLUDED.error_message,
                sent_at = EXCLUDED.sent_at
        ", [
            $log_data['company_id'],
            $log_data['type_id'],
            $log_data['authority_id'],
            $log_data['user_id'],
            $log_data['reminder_type'],
            $log_data['recipient_email'],
            $log_data['recipient_phone'],
            $log_data['recipient_name'],
            $log_data['recipient_role'],
            $log_data['document_month'],
            $log_data['document_year'],
            $log_data['due_date'],
            $log_data['days_before_due'],
            $log_data['status'],
            $log_data['error_message'],
            $log_data['sent_at'],
            $log_data['created_at']
        ]);
        
        return $this->db->insert_id();
    }

    /**
     * Update reminder status
     */
    public function updateReminderStatus($id, $status, $error_message = null)
    {
        $data = ['status' => $status];
        
        if ($status === 'sent') {
            $data['sent_at'] = date('Y-m-d H:i:s');
        }
        
        if ($error_message) {
            $data['error_message'] = $error_message;
        }
        
        return $this->db->where('id', $id)->update('reminder_logs', $data);
    }

    /**
     * Get reminder days configuration
     * Returns hardcoded default values (can be configured per document head in company_document_head_reminders table)
     */
    public function getReminderDays($company_id = null)
    {
        // Default reminder days: 30, 15, 7, 3, 2 days before, on due date (0), and 1 day after (-1)
        return [30, 15, 7, 3, 2, 0, -1];
    }

    /**
     * Get reminder report data
     */
    public function getReminderReport($filters = [])
    {
        $this->db->select('
            rl.*,
            c.company_name,
            st.type_name,
            a.authority_name
        ', FALSE)
        ->from('reminder_logs rl')
        ->join('company c', 'c.id = rl.company_id', 'left')
        ->join('sub_type st', 'st.id = rl.type_id', 'left')
        ->join('authority a', 'CAST(a.id AS TEXT) = st.authority_id', 'left', FALSE);
        
        // Apply filters
        if (!empty($filters['company_id'])) {
            $this->db->where('rl.company_id', $filters['company_id']);
        }
        
        if (!empty($filters['type_id'])) {
            $this->db->where('rl.type_id', $filters['type_id']);
        }
        
        if (!empty($filters['year'])) {
            $this->db->where('rl.document_year', $filters['year']);
        }
        
        if (!empty($filters['month'])) {
            $this->db->where('rl.document_month', $filters['month']);
        }
        
        if (!empty($filters['status'])) {
            $this->db->where('rl.status', $filters['status']);
        }
        
        if (!empty($filters['reminder_type'])) {
            $this->db->where('rl.reminder_type', $filters['reminder_type']);
        }
        
        if (!empty($filters['date_from'])) {
            $this->db->where('rl.created_at >=', $filters['date_from']);
        }
        
        if (!empty($filters['date_to'])) {
            $this->db->where('rl.created_at <=', $filters['date_to'] . ' 23:59:59');
        }
        
        $this->db->order_by('rl.created_at', 'DESC');
        
        return $this->db->get()->result_array();
    }

    /**
     * Get reminder statistics
     */
    public function getReminderStats($company_id = null)
    {
        $where = $company_id ? "WHERE company_id = " . intval($company_id) : "";
        
        $sql = "
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed,
                SUM(CASE WHEN reminder_type = 'email' THEN 1 ELSE 0 END) as email_count,
                SUM(CASE WHEN reminder_type = 'whatsapp' THEN 1 ELSE 0 END) as whatsapp_count
            FROM reminder_logs
            {$where}
        ";
        
        return $this->db->query($sql)->row_array();
    }

    /**
     * Get pending reminders count for today
     */
    public function getPendingRemindersCount($company_id = null)
    {
        $today = date('Y-m-d');
        
        $this->db->where('DATE(created_at)', $today)
            ->where('status', 'pending');
        
        if ($company_id) {
            $this->db->where('company_id', $company_id);
        }
        
        return $this->db->count_all_results('reminder_logs');
    }
}

