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

class Dashboard_model extends CI_Model {

    private $holiday_model = null;

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
        $this->_ensureFavouritesTable();
        $this->_loadHolidayModel();
    }

    /**
     * Load Holiday model for due date adjustments
     */
    private function _loadHolidayModel()
    {
        $CI =& get_instance();
        $CI->load->model('Holiday_model');
        $this->holiday_model = $CI->Holiday_model;
    }

    /**
     * Load DocumentGroup model for child document handling
     */
    private function _loadDocumentGroupModel()
    {
        $CI =& get_instance();
        if (!isset($CI->DocumentGroup_model)) {
            $CI->load->model('DocumentGroup_model');
        }
        return $CI->DocumentGroup_model;
    }

    /**
     * Get mandatory child documents that are pending for dashboard
     * This method finds all parent documents that are uploaded and have mandatory children that are NOT uploaded
     * 
     * @param int $companyId Company ID
     * @param int $typeId Type/Document Head ID
     * @param string $month Document month
     * @param string $year Document year
     * @return array List of mandatory child documents that are pending
     */
    public function getMandatoryChildDocumentsPending($companyId, $typeId, $month, $year)
    {
        try {
            $groupModel = $this->_loadDocumentGroupModel();
            
            // Get all document groups
            $groups = $this->db->get('document_groups')->result_array();
            if (empty($groups)) {
                return [];
            }

            $pendingChildren = [];

            foreach ($groups as $group) {
                $parentDocId = (int)$group['document_id'];
                
                // Check if parent document is uploaded for this type/company/period
                $parentUploaded = $this->db->where([
                    'document_id' => $parentDocId,
                    'company_id' => $companyId,
                    'type_id' => $typeId,
                    'document_month' => $month,
                    'document_year' => $year,
                    'is_deleted' => 0
                ])->get('uploaded_documents')->row_array();

                if (empty($parentUploaded)) {
                    continue; // Parent not uploaded, skip children
                }

                // Get mandatory status for children of this parent
                $mandatoryChildren = $groupModel->getMandatoryChildDocuments($companyId, $typeId, $month, $year);
                
                foreach ($mandatoryChildren as $child) {
                    if ($child['parent_document_id'] != $parentDocId) {
                        continue;
                    }

                    $childDocId = $child['child_document_id'];

                    // Check if this child document is uploaded
                    $childUploaded = $this->db->where([
                        'document_id' => $childDocId,
                        'company_id' => $companyId,
                        'type_id' => $typeId,
                        'document_month' => $month,
                        'document_year' => $year,
                        'is_deleted' => 0
                    ])->get('uploaded_documents')->row_array();

                    if (empty($childUploaded)) {
                        // Child is mandatory but not uploaded
                        $childDoc = $this->db->where('id', $childDocId)->get('documents')->row_array();
                        if ($childDoc) {
                            $pendingChildren[] = [
                                'document_id' => $childDocId,
                                'document_name' => $childDoc['document_name'],
                                'parent_document_id' => $parentDocId,
                                'is_mandatory_child' => true
                            ];
                        }
                    }
                }
            }

            return $pendingChildren;
        } catch (Exception $e) {
            log_message('error', 'Error getting mandatory child documents: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * Get adjusted due date considering holidays and Sundays
     * @param int $dueDay Original due day (1-31)
     * @param int $month Month (1-12)
     * @param int $year Year
     * @return array Adjusted due date info
     */
    public function getAdjustedDueDate($dueDay, $month, $year)
    {
        if ($this->holiday_model) {
            return $this->holiday_model->getAdjustedDueDate($dueDay, $month, $year);
        }
        
        // Fallback if holiday model not available
        $lastDay = (int)date('t', mktime(0, 0, 0, $month, 1, $year));
        $adjustedDay = min($dueDay, $lastDay);
        return [
            'original_day' => $dueDay,
            'day' => $adjustedDay,
            'date' => sprintf('%04d-%02d-%02d', $year, $month, $adjustedDay),
            'adjusted' => ($adjustedDay != $dueDay),
            'reason' => ''
        ];
    }

    /**
     * Check if a document is overdue based on adjusted due date
     */
    public function isOverdueWithHolidays($dueDay, $month, $year)
    {
        $adjusted = $this->getAdjustedDueDate($dueDay, $month, $year);
        $today = date('Y-m-d');
        return $today > $adjusted['date'];
    }

    /**
     * Check if a document is pending (due today or in future) based on adjusted due date
     */
    public function isPendingWithHolidays($dueDay, $month, $year)
    {
        $adjusted = $this->getAdjustedDueDate($dueDay, $month, $year);
        $today = date('Y-m-d');
        return $today <= $adjusted['date'];
    }

    /**
     * Ensure favourites table exists
     */
    private function _ensureFavouritesTable()
    {
        // Temporarily disable db_debug to prevent errors during table check
        $debug = $this->db->db_debug;
        $this->db->db_debug = FALSE;
        
        // Check if table exists
        $query = $this->db->query("SELECT 1 FROM favourites LIMIT 1");
        
        if ($query === FALSE) {
            // Table doesn't exist, create it
            $this->db->query("
                CREATE TABLE favourites (
                    id SERIAL PRIMARY KEY,
                    upload_id INTEGER NOT NULL,
                    user_id VARCHAR(50) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ");
            $this->db->query("CREATE UNIQUE INDEX IF NOT EXISTS idx_fav_unique ON favourites(upload_id, user_id)");
            $this->db->query("CREATE INDEX IF NOT EXISTS idx_fav_user ON favourites(user_id)");
            $this->db->query("CREATE INDEX IF NOT EXISTS idx_fav_upload ON favourites(upload_id)");
        }
        
        // Restore db_debug setting
        $this->db->db_debug = $debug;
    }

    /**
     * Get user's role information
     * Role IDs: 1 = Super Admin, 2 = Admin, 3 = User
     */
    public function getUserRole($user_id)
    {
        $user = $this->db->select('role_id')
            ->from('users')
            ->where('id', $user_id)
            ->get()
            ->row_array();
        
        return isset($user['role_id']) ? (int)$user['role_id'] : 3; // Default to User if not found
    }

    /**
     * Get role name by role ID
     */
    public function getRoleName($role_id)
    {
        $roles = [
            1 => 'Super Admin',
            2 => 'Admin',
            3 => 'User'
        ];
        return isset($roles[$role_id]) ? $roles[$role_id] : 'User';
    }

    /**
     * Check if user is admin (Super Admin or Admin)
     */
    public function isAdmin($role_id)
    {
        return in_array((int)$role_id, [1, 2]);
    }

    /**
     * Get companies that user is allowed to access based on role
     * Super Admin (1) & Admin (2): All active companies
     * User (3): Only assigned companies from user_company column
     * 
     * @param int $user_id User ID
     * @param int $role_id Role ID (optional, will be fetched if not provided)
     * @return array List of companies with id and company_name
     */
    public function getUserAllowedCompanies($user_id, $role_id = null)
    {
        if ($role_id === null) {
            $role_id = $this->getUserRole($user_id);
        }
        
        if ($this->isAdmin($role_id)) {
            // Admin/Super Admin: Return all active companies
            return $this->db->select('id, company_name')
                ->from('company')
                ->where('status', '1')
                ->order_by('company_name', 'ASC')
                ->get()
                ->result_array();
        } else {
            // User (role 3): Return only assigned companies
            $user = $this->db->select('user_company')
                ->from('users')
                ->where('id', $user_id)
                ->get()
                ->row_array();
            
            if (!empty($user['user_company'])) {
                $company_ids = array_filter(explode(',', $user['user_company']));
                
                if (!empty($company_ids)) {
                    return $this->db->select('id, company_name')
                        ->from('company')
                        ->where('status', '1')
                        ->where_in('id', $company_ids)
                        ->order_by('company_name', 'ASC')
                        ->get()
                        ->result_array();
                }
            }
            
            return []; // No companies assigned
        }
    }

    /**
     * Get company IDs that user is allowed to access
     * 
     * @param int $user_id User ID
     * @param int $role_id Role ID (optional)
     * @return array|null Array of company IDs, or null for all companies (admin)
     */
    public function getUserAllowedCompanyIds($user_id, $role_id = null)
    {
        if ($role_id === null) {
            $role_id = $this->getUserRole($user_id);
        }
        
        if ($this->isAdmin($role_id)) {
            // Admin/Super Admin: null means no restriction
            return null;
        } else {
            // User: Return only assigned company IDs
            $user = $this->db->select('user_company')
                ->from('users')
                ->where('id', $user_id)
                ->get()
                ->row_array();
            
            if (!empty($user['user_company'])) {
                $company_ids = array_filter(explode(',', $user['user_company']));
                return array_map('intval', $company_ids);
            }
            
            return []; // No companies assigned - return empty array
        }
    }

    /**
     * Get favourite documents for a user
     */
    public function getFavourites($user_id = null, $companyIds = null)
    {
        $this->db->select('
            f.id as favourite_id,
            f.created_at as favourited_at,
            ud.id as upload_id,
            ud.file_name,
            ud.file_path,
            ud.document_month,
            ud.document_year,
            ud.uploaded_at,
            c.id as company_id,
            c.company_name,
            st.id as type_id,
            st.type_name,
            st.frequency,
            st.frequency_start_date,
            a.authority_name,
            d.document_name
        ', FALSE);
        
        $this->db->from('favourites f');
        $this->db->join('uploaded_documents ud', 'f.upload_id = ud.id', 'inner');
        $this->db->join('company c', 'ud.company_id = c.id', 'left');
        $this->db->join('sub_type st', 'ud.type_id = st.id', 'left');
        $this->db->join('authority a', 'CAST(st.authority_id AS INT) = a.id', 'left');
        $this->db->join('documents d', 'ud.document_id = d.id', 'left');
        $this->db->where('ud.is_deleted', 0);
        
        if ($user_id) {
            $this->db->where('f.user_id', $user_id);
        }
        
        // Filter by companies
        if (!empty($companyIds) && is_array($companyIds)) {
            $this->db->where_in('c.id', $companyIds);
        }
        
        $this->db->order_by('f.created_at', 'DESC');
        
        return $this->db->get()->result_array();
    }

    /**
     * Get pending documents - documents due this month but not yet overdue
     * Considers due_in_same_next_month column to calculate actual due date
     * Handles Monthly frequency (Quarterly/Half-Yearly/Yearly handled separately)
     * Returns both uploaded and pending documents with status flag
     */
    public function getPendingDocuments($companyIds = null, $filterYear = null)
    {
        $currentDay = (int)date('j'); // 1-31
        $currentMonth = (int)date('n'); // 1-12
        $currentDate = date('Y-m-d');
        $actualCurrentYear = (int)date('Y');

        // If "all" is selected, iterate through multiple years
        if ($filterYear === 'all' || empty($filterYear)) {
            $allResults = [];
            // Get years from 2024 to current year
            for ($year = 2024; $year <= $actualCurrentYear; $year++) {
                $yearResults = $this->_getPendingForYear($companyIds, $year, $currentDay, $currentMonth, $currentDate);
                $allResults = array_merge($allResults, $yearResults);
            }
            return $this->_deduplicatePendingResults($allResults);
        }
        
        // Single year processing
        $results = $this->_getPendingForYear($companyIds, (int)$filterYear, $currentDay, $currentMonth, $currentDate);
        return $this->_deduplicatePendingResults($results);
    }
    
    /**
     * Get pending documents for a specific year
     * Pending = documents due this month but due day has not passed yet
     * Only relevant for the ACTUAL current year (past years have no pending - all is overdue or uploaded)
     */
    private function _getPendingForYear($companyIds, $filterYear, $currentDay, $currentMonth, $currentDate)
    {
        $actualCurrentYear = (int)date('Y');
        
        // Pending only makes sense for the actual current year
        // For past years, there's nothing "pending" - everything is either uploaded or overdue
        if ($filterYear != $actualCurrentYear) {
            return [];
        }
        
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        // Calculate previous month (for documents due this month from previous period)
        $prevMonth = $currentMonth - 1;
        $prevYear = $actualCurrentYear;
        if ($prevMonth < 1) {
            $prevMonth = 12;
            $prevYear--;
        }
        
        // For Monthly frequency: documents due this month (due_day >= current_day)
        $sql = "
            SELECT 
                st.id as type_id,
                st.type_name,
                st.frequency,
                st.frequency_start_date,
                COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                st.due_in_same_next_month,
                st.document_name as doc_sequence,
                CAST(st.authority_id AS INTEGER) as authority_id,
                a.authority_name,
                c.id as company_id,
                c.company_name,
                c.fiscal_year,
                md.document_id,
                d.document_name,
                CASE 
                    WHEN st.frequency_start_date ~ '^[0-9]+$' 
                    THEN CAST(st.frequency_start_date AS INTEGER)
                    ELSE NULL 
                END as due_day,
                -- Calculate the document period (which month's document is this for)
                CASE 
                    WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevMonth . "
                    ELSE " . $currentMonth . "
                END as doc_period_month,
                CASE 
                    WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevYear . "
                    ELSE " . $actualCurrentYear . "
                END as doc_period_year,
                COALESCE(
                    NULLIF(
                        position(',' || md.document_id::text || ',' in ',' || st.document_name || ','),
                        0
                    ),
                    9999
                ) as doc_order,
                CASE WHEN ud.id IS NOT NULL THEN 1 ELSE 0 END as is_uploaded
            FROM sub_type st
            INNER JOIN mandatory_documents md ON st.id = md.type_id
            INNER JOIN documents d ON md.document_id = d.id
            INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
            INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
            LEFT JOIN uploaded_documents ud ON ud.type_id = st.id
                AND ud.document_id = md.document_id
                AND ud.company_id = c.id
                AND CAST(NULLIF(TRIM(ud.document_month::text), '') AS INTEGER) = 
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevMonth . "
                        ELSE " . $currentMonth . "
                    END
                AND CAST(NULLIF(TRIM(ud.document_year::text), '') AS INTEGER) = 
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevYear . "
                        ELSE " . $actualCurrentYear . "
                    END
                AND ud.is_deleted = 0
            WHERE st.status = '1'
            AND LOWER(st.frequency) = 'monthly'
            AND st.frequency_start_date ~ '^[0-9]+\$'
            " . $companyFilter . "
            -- Document period must be >= document_start_date (company custom or master)
            AND (
                COALESCE(cdh.custom_start_date, st.document_start_date) IS NULL 
                OR TRIM(COALESCE(cdh.custom_start_date, st.document_start_date)::text) = ''
                OR (
                    MAKE_DATE(
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevYear . "
                            ELSE " . $actualCurrentYear . "
                        END,
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevMonth . "
                            ELSE " . $currentMonth . "
                        END,
                        1
                    ) >= DATE_TRUNC('month', COALESCE(cdh.custom_start_date, st.document_start_date)::date)
                )
            )
            -- Due day is today or in the future (this month) - not yet overdue
            AND CAST(st.frequency_start_date AS INTEGER) >= " . $currentDay . "
            ORDER BY c.company_name, a.authority_name, st.type_name, doc_order
        ";
        
        $query = $this->db->query($sql);
        $monthlyPending = $this->_groupDocumentsWithStatus($query->result_array());
        
        // Get non-monthly pending documents (Quarterly, Half Yearly, Yearly)
        $nonMonthlyPending = $this->_getNonMonthlyPending($currentDate, $currentDay, $currentMonth, $actualCurrentYear, $companyIds, $filterYear);
        
        return array_merge($monthlyPending, $nonMonthlyPending);
    }
    
    /**
     * Get pending documents for non-monthly frequencies (Quarterly, Half Yearly, Yearly)
     * Pending = due this month but due date not passed yet
     * Only relevant for the actual current year
     */
    private function _getNonMonthlyPending($currentDate, $currentDay, $currentMonth, $currentYear, $companyIds = null, $filterYear = null)
    {
        $actualCurrentYear = (int)date('Y');
        $actualCurrentMonth = (int)date('n');
        
        // Pending only makes sense for actual current year
        if ($filterYear && $filterYear !== 'all' && (int)$filterYear != $actualCurrentYear) {
            return [];
        }
        
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        $sql = "
            SELECT 
                st.id as type_id,
                st.type_name,
                st.frequency,
                st.frequency_start_date,
                COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                st.due_in_same_next_month,
                st.document_name as doc_sequence,
                CAST(st.authority_id AS INTEGER) as authority_id,
                a.authority_name,
                c.id as company_id,
                c.company_name,
                c.fiscal_year,
                md.document_id,
                d.document_name,
                CASE 
                    WHEN st.frequency_start_date ~ '^[0-9]+$' 
                    THEN CAST(st.frequency_start_date AS INTEGER)
                    ELSE NULL 
                END as due_day
            FROM sub_type st
            INNER JOIN mandatory_documents md ON st.id = md.type_id
            INNER JOIN documents d ON md.document_id = d.id
            INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
            INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
            WHERE st.status = '1'
            AND LOWER(st.frequency) IN ('quarterly', 'half yearly', 'yearly')
            AND st.frequency_start_date ~ '^[0-9]+$'
            " . $companyFilter . "
            ORDER BY c.company_name, a.authority_name, st.type_name
        ";
        
        $query = $this->db->query($sql);
        $results = $query->result_array();
        
        $pending = [];
        $today = date('Y-m-d');
        
        foreach ($results as $row) {
            // Use actual current year for period generation
            $periods = $this->_getPeriodsForFrequency($row['frequency'], $row['fiscal_year'], $actualCurrentMonth, $actualCurrentYear);
            $dueDay = (int)$row['due_day'];
            $dueInNext = (int)($row['due_in_same_next_month'] ?? 0);
            
            // Parse document_start_date for period comparison
            $docStartMonth = null;
            $docStartYear = null;
            if (!empty($row['document_start_date']) && trim($row['document_start_date']) !== '') {
                $docStartDate = strtotime($row['document_start_date']);
                if ($docStartDate !== false) {
                    $docStartMonth = (int)date('n', $docStartDate);
                    $docStartYear = (int)date('Y', $docStartDate);
                }
            }
            
            foreach ($periods as $period) {
                // Calculate actual due month/year
                $dueMonth = $dueInNext == 1 ? $period['due_next_month'] : $period['end_month'];
                $dueYear = $dueInNext == 1 ? $period['due_next_year'] : $period['end_year'];
                
                // Check if period is on or after document_start_date
                if ($docStartYear !== null) {
                    $periodYearMonth = $period['end_year'] * 100 + $period['end_month'];
                    $startYearMonth = $docStartYear * 100 + $docStartMonth;
                    if ($periodYearMonth < $startYearMonth) {
                        continue;
                    }
                }
                
                // Check if due date is this month and not passed (using adjusted due date)
                $adjustedDue = $this->getAdjustedDueDate($dueDay, $dueMonth, $dueYear);
                $isPending = ($dueMonth == $actualCurrentMonth && $dueYear == $actualCurrentYear && $today <= $adjustedDue['date']);
                
                if ($isPending) {
                    // Check if document is uploaded
                    $isUploaded = $this->_isDocumentUploaded(
                        $row['type_id'], 
                        $row['document_id'], 
                        $row['company_id'], 
                        $period['end_month'], 
                        $period['end_year']
                    );
                    
                    $key = $row['company_id'] . '|' . $row['type_id'] . '|' . $period['end_month'] . '|' . $period['end_year'];
                    if (!isset($pending[$key])) {
                        $pending[$key] = [
                            'company_id' => $row['company_id'],
                            'company_name' => $row['company_name'],
                            'type_id' => $row['type_id'],
                            'type_name' => $row['type_name'],
                            'frequency' => $row['frequency'],
                            'authority_id' => $row['authority_id'] ?? null,
                            'authority_name' => $row['authority_name'],
                            'due_day' => $dueDay,
                            'doc_period_month' => $period['end_month'],
                            'doc_period_year' => $period['end_year'],
                            'documents' => [],
                            'uploaded_count' => 0,
                            'pending_count' => 0,
                            'total_count' => 0
                        ];
                    }
                    
                    $pending[$key]['documents'][] = [
                        'document_id' => $row['document_id'],
                        'document_name' => $row['document_name'],
                        'is_uploaded' => $isUploaded
                    ];
                    $pending[$key]['total_count']++;
                    if ($isUploaded) {
                        $pending[$key]['uploaded_count']++;
                    } else {
                        $pending[$key]['pending_count']++;
                    }
                }
            }
        }
        
        // Filter out fully uploaded groups
        return array_values(array_filter($pending, function($group) {
            return $group['pending_count'] > 0;
        }));
    }
    
    /**
     * Get overdue documents for non-monthly frequencies (Quarterly, Half Yearly, Yearly)
     * Iterates through all years from document_start_date to current date
     */
    private function _getNonMonthlyOverdue($currentDate, $currentDay, $currentMonth, $currentYear, $companyIds = null, $filterYear = null)
    {
        $actualCurrentYear = (int)date('Y');
        $actualCurrentMonth = (int)date('n');
        
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        $sql = "
            SELECT 
                st.id as type_id,
                st.type_name,
                st.frequency,
                st.frequency_start_date,
                COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                st.due_in_same_next_month,
                st.document_name as doc_sequence,
                CAST(st.authority_id AS INTEGER) as authority_id,
                a.authority_name,
                c.id as company_id,
                c.company_name,
                c.fiscal_year,
                md.document_id,
                d.document_name,
                CASE 
                    WHEN st.frequency_start_date ~ '^[0-9]+\$' 
                    THEN CAST(st.frequency_start_date AS INTEGER)
                    ELSE NULL 
                END as due_day
            FROM sub_type st
            INNER JOIN mandatory_documents md ON st.id = md.type_id
            INNER JOIN documents d ON md.document_id = d.id
            INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
            INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
            WHERE st.status = '1'
            AND LOWER(st.frequency) IN ('quarterly', 'half yearly', 'yearly')
            AND st.frequency_start_date ~ '^[0-9]+\$'
            " . $companyFilter . "
            ORDER BY c.company_name, a.authority_name, st.type_name
        ";
        
        $query = $this->db->query($sql);
        $results = $query->result_array();
        
        $overdue = [];
        $today = date('Y-m-d');
        
        foreach ($results as $row) {
            $dueDay = (int)$row['due_day'];
            $dueInNext = (int)($row['due_in_same_next_month'] ?? 0);
            
            // Parse document_start_date for period comparison
            $docStartYear = 2024; // Default start year
            $docStartMonth = 1;
            if (!empty($row['document_start_date']) && trim($row['document_start_date']) !== '') {
                $docStartDate = strtotime($row['document_start_date']);
                if ($docStartDate !== false) {
                    $docStartMonth = (int)date('n', $docStartDate);
                    $docStartYear = (int)date('Y', $docStartDate);
                }
            }
            
            // Iterate through all years from document_start_date to current year
            for ($year = $docStartYear; $year <= $actualCurrentYear; $year++) {
                // Generate periods for this year
                $periods = $this->_getPeriodsForFrequency($row['frequency'], $row['fiscal_year'], $actualCurrentMonth, $year);
                
                foreach ($periods as $period) {
                    // Calculate actual due month/year
                    $dueMonth = $dueInNext == 1 ? $period['due_next_month'] : $period['end_month'];
                    $dueYear = $dueInNext == 1 ? $period['due_next_year'] : $period['end_year'];
                    
                    // Check if period is on or after document_start_date
                    $periodYearMonth = $period['end_year'] * 100 + $period['end_month'];
                    $startYearMonth = $docStartYear * 100 + $docStartMonth;
                    if ($periodYearMonth < $startYearMonth) {
                        continue; // Skip periods before document_start_date
                    }
                    
                    // Apply year filter (filter by document period year, not due year)
                    if ($filterYear && $filterYear !== 'all') {
                        if ($period['end_year'] != (int)$filterYear) {
                            continue;
                        }
                    }
                    
                    // Check if due date has passed (overdue) using adjusted due date
                    $adjustedDue = $this->getAdjustedDueDate($dueDay, $dueMonth, $dueYear);
                    
                    if ($today > $adjustedDue['date']) {
                        // Check if document is uploaded
                        $isUploaded = $this->_isDocumentUploaded(
                            $row['type_id'], 
                            $row['document_id'], 
                            $row['company_id'], 
                            $period['end_month'], 
                            $period['end_year']
                        );
                        
                        $key = $row['company_id'] . '|' . $row['type_id'] . '|' . $period['end_month'] . '|' . $period['end_year'];
                        if (!isset($overdue[$key])) {
                            $overdue[$key] = [
                                'company_id' => $row['company_id'],
                                'company_name' => $row['company_name'],
                                'type_id' => $row['type_id'],
                                'type_name' => $row['type_name'],
                                'frequency' => $row['frequency'],
                                'authority_id' => $row['authority_id'] ?? null,
                                'authority_name' => $row['authority_name'],
                                'due_day' => $dueDay,
                                'adjusted_due_day' => $adjustedDue['day'],
                                'adjusted_due_reason' => $adjustedDue['reason'],
                                'overdue_month' => $period['end_month'],
                                'overdue_year' => $period['end_year'],
                                'documents' => [],
                                'uploaded_count' => 0,
                                'pending_count' => 0,
                                'total_count' => 0
                            ];
                        }
                        
                        $overdue[$key]['documents'][] = [
                            'document_id' => $row['document_id'],
                            'document_name' => $row['document_name'],
                            'is_uploaded' => $isUploaded
                        ];
                        $overdue[$key]['total_count']++;
                        if ($isUploaded) {
                            $overdue[$key]['uploaded_count']++;
                        } else {
                            $overdue[$key]['pending_count']++;
                        }
                    }
                }
            }
        }
        
        // Filter out fully uploaded groups
        return array_values(array_filter($overdue, function($group) {
            return $group['pending_count'] > 0;
        }));
    }
    
    /**
     * Get upcoming documents for non-monthly frequencies (Quarterly, Half Yearly, Yearly)
     * Upcoming = due next month (from actual current date)
     */
    private function _getNonMonthlyUpcoming($currentDate, $currentDay, $currentMonth, $currentYear, $companyIds = null, $filterYear = null)
    {
        $actualCurrentYear = (int)date('Y');
        $actualCurrentMonth = (int)date('n');
        
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        // Calculate next month based on ACTUAL current date
        $nextMonth = $actualCurrentMonth + 1;
        $nextYear = $actualCurrentYear;
        if ($nextMonth > 12) {
            $nextMonth = 1;
            $nextYear++;
        }
        
        $sql = "
            SELECT 
                st.id as type_id,
                st.type_name,
                st.frequency,
                st.frequency_start_date,
                COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                st.due_in_same_next_month,
                st.document_name as doc_sequence,
                CAST(st.authority_id AS INTEGER) as authority_id,
                a.authority_name,
                c.id as company_id,
                c.company_name,
                c.fiscal_year,
                md.document_id,
                d.document_name,
                CASE 
                    WHEN st.frequency_start_date ~ '^[0-9]+\$' 
                    THEN CAST(st.frequency_start_date AS INTEGER)
                    ELSE NULL 
                END as due_day
            FROM sub_type st
            INNER JOIN mandatory_documents md ON st.id = md.type_id
            INNER JOIN documents d ON md.document_id = d.id
            INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
            INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
            WHERE st.status = '1'
            AND LOWER(st.frequency) IN ('quarterly', 'half yearly', 'yearly')
            AND st.frequency_start_date ~ '^[0-9]+\$'
            " . $companyFilter . "
            ORDER BY c.company_name, a.authority_name, st.type_name
        ";
        
        $query = $this->db->query($sql);
        $results = $query->result_array();
        
        $upcoming = [];
        foreach ($results as $row) {
            // Use actual current year for period generation
            $periods = $this->_getPeriodsForFrequency($row['frequency'], $row['fiscal_year'], $actualCurrentMonth, $actualCurrentYear);
            $dueDay = (int)$row['due_day'];
            $dueInNext = (int)($row['due_in_same_next_month'] ?? 0);
            
            // Parse document_start_date for period comparison
            $docStartMonth = null;
            $docStartYear = null;
            if (!empty($row['document_start_date']) && trim($row['document_start_date']) !== '') {
                $docStartDate = strtotime($row['document_start_date']);
                if ($docStartDate !== false) {
                    $docStartMonth = (int)date('n', $docStartDate);
                    $docStartYear = (int)date('Y', $docStartDate);
                }
            }
            
            foreach ($periods as $period) {

                // Calculate actual due month/year
                $dueMonth = $dueInNext == 1 ? $period['due_next_month'] : $period['end_month'];
                $dueYear = $dueInNext == 1 ? $period['due_next_year'] : $period['end_year'];
                
                // Check if period is on or after document_start_date
                if ($docStartYear !== null) {
                    $periodYearMonth = $period['end_year'] * 100 + $period['end_month'];
                    $startYearMonth = $docStartYear * 100 + $docStartMonth;
                    if ($periodYearMonth < $startYearMonth) {
                        continue;
                    }
                }
                
                // Apply year filter (for upcoming, we check the period year)
                if ($filterYear && $filterYear !== 'all') {
                    if ($period['end_year'] != (int)$filterYear) {
                        continue;
                    }
                }
                
                // Check if due date is next month (upcoming)
                if ($dueMonth == $nextMonth && $dueYear == $nextYear) {
                    // Get adjusted due date for upcoming documents
                    $adjustedDue = $this->getAdjustedDueDate($dueDay, $dueMonth, $dueYear);
                    
                    // Check if document is uploaded
                    $isUploaded = $this->_isDocumentUploaded(
                        $row['type_id'], 
                        $row['document_id'], 
                        $row['company_id'], 
                        $period['end_month'], 
                        $period['end_year']
                    );
                    
                    $key = $row['company_id'] . '|' . $row['type_id'] . '|' . $period['end_month'] . '|' . $period['end_year'];
                    if (!isset($upcoming[$key])) {
                        $upcoming[$key] = [
                            'company_id' => $row['company_id'],
                            'company_name' => $row['company_name'],
                            'type_id' => $row['type_id'],
                            'type_name' => $row['type_name'],
                            'frequency' => $row['frequency'],
                            'authority_id' => $row['authority_id'] ?? null,
                            'authority_name' => $row['authority_name'],
                            'due_day' => $dueDay,
                            'adjusted_due_day' => $adjustedDue['day'],
                            'adjusted_due_reason' => $adjustedDue['reason'],
                            'doc_period_month' => $period['end_month'],
                            'doc_period_year' => $period['end_year'],
                            'documents' => [],
                            'uploaded_count' => 0,
                            'pending_count' => 0,
                            'total_count' => 0
                        ];
                    }
                    
                    $upcoming[$key]['documents'][] = [
                        'document_id' => $row['document_id'],
                        'document_name' => $row['document_name'],
                        'is_uploaded' => $isUploaded
                    ];
                    $upcoming[$key]['total_count']++;
                    if ($isUploaded) {
                        $upcoming[$key]['uploaded_count']++;
                    } else {
                        $upcoming[$key]['pending_count']++;
                    }
                }
            }
        }
        
        // Filter out fully uploaded groups
        return array_values(array_filter($upcoming, function($group) {
            return $group['pending_count'] > 0;
        }));
    }
    
    /**
     * Get periods based on frequency and fiscal year
     */
    private function _getPeriodsForFrequency($frequency, $fiscalYear, $currentMonth, $currentYear)
    {
        $frequency = strtolower(trim($frequency));
        $fiscalStart = $this->_getFiscalStartMonth($fiscalYear);
        
        $periods = [];
        
        if ($frequency == 'yearly') {
            // One period per fiscal year - last month of fiscal year
            $endMonth = $fiscalStart == 1 ? 12 : $fiscalStart - 1;
            $endYear = $fiscalStart == 1 ? $currentYear : ($currentMonth >= $fiscalStart ? $currentYear + 1 : $currentYear);
            $periods[] = [
                'end_month' => $endMonth,
                'end_year' => $endYear,
                'due_next_month' => $endMonth == 12 ? 1 : $endMonth + 1,
                'due_next_year' => $endMonth == 12 ? $endYear + 1 : $endYear
            ];
        /*} elseif ($frequency == 'half yearly') {
            // 2 periods per fiscal year
            for ($i = 0; $i < 2; $i++) {
                $endMonth = (($fiscalStart - 1 + ($i + 1) * 6) % 12) + 1;
                if ($endMonth == 0) $endMonth = 12;
                $endYear = $currentYear;
                if ($fiscalStart > 1 && $endMonth < $fiscalStart) {
                    $endYear = $currentMonth >= $fiscalStart ? $currentYear + 1 : $currentYear;
                }
                $periods[] = [
                    'end_month' => $endMonth,
                    'end_year' => $endYear,
                    'due_next_month' => $endMonth == 12 ? 1 : $endMonth + 1,
                    'due_next_year' => $endMonth == 12 ? $endYear + 1 : $endYear
                ];
            }
        } */
    } elseif ($frequency == 'half yearly') {

        // Half 1: fiscalStart → fiscalStart + 5
        $h1EndMonth = (($fiscalStart + 5 - 1) % 12) + 1;
        $h1EndYear  = ($h1EndMonth < $fiscalStart) ? $currentYear + 1 : $currentYear;
    
        // Half 2: fiscalStart + 6 → fiscalStart + 11
        $h2EndMonth = (($fiscalStart + 11 - 1) % 12) + 1;
        $h2EndYear  = ($h2EndMonth < $fiscalStart) ? $currentYear + 1 : $currentYear;
    
        $periods[] = [
            'end_month' => $h1EndMonth,
            'end_year'  => $h1EndYear,
            'due_next_month' => $h1EndMonth == 12 ? 1 : $h1EndMonth + 1,
            'due_next_year'  => $h1EndMonth == 12 ? $h1EndYear + 1 : $h1EndYear
        ];
    
        $periods[] = [
            'end_month' => $h2EndMonth,
            'end_year'  => $h2EndYear,
            'due_next_month' => $h2EndMonth == 12 ? 1 : $h2EndMonth + 1,
            'due_next_year'  => $h2EndMonth == 12 ? $h2EndYear + 1 : $h2EndYear
        ];
    }
    
        elseif ($frequency == 'quarterly') {
            // 4 periods per fiscal year
            for ($i = 0; $i < 4; $i++) {
                $endMonth = (($fiscalStart - 1 + ($i + 1) * 3) % 12);
                if ($endMonth == 0) $endMonth = 12;
                $endYear = $currentYear;
                if ($fiscalStart > 1 && $endMonth < $fiscalStart) {
                    $endYear = $currentMonth >= $fiscalStart ? $currentYear + 1 : $currentYear;
                }
                $periods[] = [
                    'end_month' => $endMonth,
                    'end_year' => $endYear,
                    'due_next_month' => $endMonth == 12 ? 1 : $endMonth + 1,
                    'due_next_year' => $endMonth == 12 ? $endYear + 1 : $endYear
                ];
            }
        }
        
        return $periods;
    }
    
    /**
     * Get fiscal year start month from fiscal_year string
     * Supports patterns like: jan-dec, apr-mar, sep-aug, etc.
     */
    private function _getFiscalStartMonth($fiscalYear)
    {
        $fiscalYear = strtolower(trim($fiscalYear ?? 'jan-dec'));
        
        // Map month abbreviations to numbers
        $monthMap = [
            'jan' => 1, 'january' => 1,
            'feb' => 2, 'february' => 2,
            'mar' => 3, 'march' => 3,
            'apr' => 4, 'april' => 4,
            'may' => 5,
            'jun' => 6, 'june' => 6,
            'jul' => 7, 'july' => 7,
            'aug' => 8, 'august' => 8,
            'sep' => 9, 'sept' => 9, 'september' => 9,
            'oct' => 10, 'october' => 10,
            'nov' => 11, 'november' => 11,
            'dec' => 12, 'december' => 12
        ];
        
        // Extract the first month from patterns like "apr-mar", "sep-aug", etc.
        foreach ($monthMap as $abbr => $monthNum) {
            if (strpos($fiscalYear, $abbr) === 0) {
                return $monthNum;
            }
        }
        
        // If the string starts with a number (e.g., "4-3" or "04-03")
        $parts = preg_split('/[-\/]/', $fiscalYear);
        if (!empty($parts) && is_numeric(trim($parts[0]))) {
            $month = (int)trim($parts[0]);
            if ($month >= 1 && $month <= 12) {
                return $month;
            }
        }
        
        return 1; // January (default)
    }
    
    /**
     * Check if a document is uploaded for a specific period
     */
    private function _isDocumentUploaded($typeId, $documentId, $companyId, $month, $year)
    {
        $this->db->where('type_id', $typeId);
        $this->db->where('document_id', $documentId);
        $this->db->where('company_id', $companyId);
        $this->db->where('CAST(NULLIF(TRIM(document_month::text), \'\') AS INTEGER) =', $month, false);
        $this->db->where('CAST(NULLIF(TRIM(document_year::text), \'\') AS INTEGER) =', $year, false);
        $this->db->where('is_deleted', 0);
        return $this->db->count_all_results('uploaded_documents') > 0;
    }

    /**
     * Get overdue documents (due date has passed)
     * Considers due_in_same_next_month column to calculate actual due date
     * Returns both uploaded and pending documents with status flag
     */
    public function getOverdueDocuments($companyIds = null, $filterYear = null)
    {
        $currentDay = (int)date('j'); // 1-31
        $currentMonth = (int)date('n'); // 1-12
        $currentDate = date('Y-m-d');
        $actualCurrentYear = (int)date('Y');

        // If "all" is selected, iterate through all years (keeping original behavior for accuracy)
        if ($filterYear === 'all' || empty($filterYear)) {
            $allResults = [];
            // Get years from 2024 to current year
            for ($year = 2024; $year <= $actualCurrentYear; $year++) {
                $yearResults = $this->_getOverdueForYear($companyIds, $year, $currentDay, $currentMonth, $currentDate);
                $allResults = array_merge($allResults, $yearResults);
            }
            return $allResults;
        }
        
        // Single year processing
        return $this->_getOverdueForYear($companyIds, (int)$filterYear, $currentDay, $currentMonth, $currentDate);
    }
    
    /**
     * Get overdue documents for a specific year
     * 
     * @param array|null $companyIds Company IDs to filter
     * @param int $filterYear The year to filter document periods by
     * @param int $currentDay Actual current day (1-31)
     * @param int $currentMonth Actual current month (1-12)
     * @param string $currentDate Actual current date (Y-m-d)
     */
    private function _getOverdueForYear($companyIds, $filterYear, $currentDay, $currentMonth, $currentDate)
    {
        $actualCurrentYear = (int)date('Y');
        
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        $currentMonthOverdue = [];
        
        // Only check current month overdue if filter year = actual current year
        // (Current month check only makes sense for the actual current month)
        if ($filterYear == $actualCurrentYear) {
            // Calculate previous month (for documents due this month from previous period)
            $prevMonth = $currentMonth - 1;
            $prevYear = $actualCurrentYear;
            if ($prevMonth < 1) {
                $prevMonth = 12;
                $prevYear--;
            }
            
            // Get overdue from current month (due day has passed)
            $sql = "
                SELECT 
                    st.id as type_id,
                    st.type_name,
                    st.frequency,
                    st.frequency_start_date,
                    COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                    st.due_in_same_next_month,
                    st.document_name as doc_sequence,
                    CAST(st.authority_id AS INTEGER) as authority_id,
                    a.authority_name,
                    c.id as company_id,
                    c.company_name,
                    md.document_id,
                    d.document_name,
                    -- Document period (which month's document is this for)
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevMonth . "
                        ELSE " . $currentMonth . "
                    END as overdue_month,
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevYear . "
                        ELSE " . $actualCurrentYear . "
                    END as overdue_year,
                    CASE 
                        WHEN st.frequency_start_date ~ '^[0-9]+\$' 
                        THEN CAST(st.frequency_start_date AS INTEGER)
                        ELSE NULL 
                    END as due_day,
                    COALESCE(
                        NULLIF(
                            position(',' || md.document_id::text || ',' in ',' || st.document_name || ','),
                            0
                        ),
                        9999
                    ) as doc_order,
                    CASE WHEN ud.id IS NOT NULL THEN 1 ELSE 0 END as is_uploaded
                FROM sub_type st
                INNER JOIN mandatory_documents md ON st.id = md.type_id
                INNER JOIN documents d ON md.document_id = d.id
                INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
                INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
                LEFT JOIN uploaded_documents ud ON ud.type_id = st.id
                    AND ud.document_id = md.document_id
                    AND ud.company_id = c.id
                    AND CAST(NULLIF(TRIM(ud.document_month::text), '') AS INTEGER) = 
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevMonth . "
                            ELSE " . $currentMonth . "
                        END
                    AND CAST(NULLIF(TRIM(ud.document_year::text), '') AS INTEGER) = 
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevYear . "
                            ELSE " . $actualCurrentYear . "
                        END
                    AND ud.is_deleted = 0
                WHERE st.status = '1'
                AND LOWER(st.frequency) = 'monthly'
                AND st.frequency_start_date ~ '^[0-9]+\$'
                " . $companyFilter . "
                -- Document period must be >= document_start_date (company custom or master)
                AND (
                    COALESCE(cdh.custom_start_date, st.document_start_date) IS NULL 
                    OR TRIM(COALESCE(cdh.custom_start_date, st.document_start_date)::text) = ''
                    OR (
                        MAKE_DATE(
                            CASE 
                                WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevYear . "
                                ELSE " . $actualCurrentYear . "
                            END,
                            CASE 
                                WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $prevMonth . "
                                ELSE " . $currentMonth . "
                            END,
                            1
                        ) >= DATE_TRUNC('month', COALESCE(cdh.custom_start_date, st.document_start_date)::date)
                    )
                )
                -- Due day has already passed this month
                AND CAST(st.frequency_start_date AS INTEGER) < " . $currentDay . "
                ORDER BY c.company_name, st.type_name, doc_order
            ";
            
            $query = $this->db->query($sql);
            $currentMonthOverdue = $this->_groupDocumentsWithStatusAndPeriod($query->result_array());
        }
        
        // Get overdue from previous months (all months from document_start_date to last month)
        // This handles ALL historical overdue, filtered by the specified year
        $previousOverdue = $this->_getPreviousMonthsOverdue($currentDate, $companyIds, $filterYear);
        
        // Get non-monthly overdue documents (Quarterly, Half Yearly, Yearly)
        $nonMonthlyOverdue = $this->_getNonMonthlyOverdue($currentDate, $currentDay, $currentMonth, $actualCurrentYear, $companyIds, $filterYear);
        
        // Merge and deduplicate results
        // (Prevents duplicates when due_in_same_next_month=1 causes overlap between currentMonth and previousMonths)
        $allResults = array_merge($currentMonthOverdue, $previousOverdue, $nonMonthlyOverdue);
        
        return $this->_deduplicateOverdueResults($allResults);
    }
    
    /**
     * Deduplicate overdue results based on company_id|type_id|overdue_month|overdue_year
     * Merges documents from duplicate entries and recalculates counts
     */
    private function _deduplicateOverdueResults($allResults)
    {
        $deduped = [];
        
        foreach ($allResults as $item) {
            $key = $item['company_id'] . '|' . $item['type_id'] . '|' . $item['overdue_month'] . '|' . $item['overdue_year'];
            
            if (!isset($deduped[$key])) {
                $deduped[$key] = $item;
            } else {
                // Merge documents from duplicate entry
                foreach ($item['documents'] as $doc) {
                    $exists = false;
                    foreach ($deduped[$key]['documents'] as $existingDoc) {
                        if ($existingDoc['document_id'] == $doc['document_id']) {
                            $exists = true;
                            break;
                        }
                    }
                    if (!$exists) {
                        $deduped[$key]['documents'][] = $doc;
                        $deduped[$key]['total_count']++;
                        if ($doc['is_uploaded']) {
                            $deduped[$key]['uploaded_count']++;
                        } else {
                            $deduped[$key]['pending_count']++;
                        }
                    }
                }
            }
        }
        
        // Filter out groups where all documents are uploaded (no pending)
        $result = [];
        foreach ($deduped as $group) {
            if ($group['pending_count'] > 0) {
                $result[] = $group;
            }
        }
        
        return $result;
    }
    
    /**
     * Deduplicate pending results based on company_id|type_id|period_month|period_year
     * Merges documents from duplicate entries and recalculates counts
     */
    private function _deduplicatePendingResults($allResults)
    {
        $deduped = [];
        
        foreach ($allResults as $item) {
            // Use period_month/period_year if available, fall back to doc_period_month/doc_period_year
            $month = isset($item['period_month']) ? $item['period_month'] : (isset($item['doc_period_month']) ? $item['doc_period_month'] : '');
            $year = isset($item['period_year']) ? $item['period_year'] : (isset($item['doc_period_year']) ? $item['doc_period_year'] : '');
            $key = $item['company_id'] . '|' . $item['type_id'] . '|' . $month . '|' . $year;
            
            if (!isset($deduped[$key])) {
                $deduped[$key] = $item;
            } else {
                // Merge documents from duplicate entry
                if (isset($item['documents']) && is_array($item['documents'])) {
                    foreach ($item['documents'] as $doc) {
                        $exists = false;
                        if (isset($deduped[$key]['documents'])) {
                            foreach ($deduped[$key]['documents'] as $existingDoc) {
                                if ($existingDoc['document_id'] == $doc['document_id']) {
                                    $exists = true;
                                    break;
                                }
                            }
                        }
                        if (!$exists) {
                            $deduped[$key]['documents'][] = $doc;
                            $deduped[$key]['total_count'] = isset($deduped[$key]['total_count']) ? $deduped[$key]['total_count'] + 1 : 1;
                            if ($doc['is_uploaded']) {
                                $deduped[$key]['uploaded_count'] = isset($deduped[$key]['uploaded_count']) ? $deduped[$key]['uploaded_count'] + 1 : 1;
                            } else {
                                $deduped[$key]['pending_count'] = isset($deduped[$key]['pending_count']) ? $deduped[$key]['pending_count'] + 1 : 1;
                            }
                        }
                    }
                }
            }
        }
        
        return array_values($deduped);
    }
    
    /**
     * Deduplicate upcoming results based on company_id|type_id|period_month|period_year
     * Merges documents from duplicate entries and recalculates counts
     */
    private function _deduplicateUpcomingResults($allResults)
    {
        $deduped = [];
        
        foreach ($allResults as $index => $item) {

            log_message(
                'error',
                'DEDUP INPUT #' . $index
                . ' | company=' . ($item['company_id'] ?? 'NA')
                . ' type=' . ($item['type_id'] ?? 'NA')
                . ' doc_month=' . ($item['doc_period_month'] ?? 'NULL')
                . ' doc_year=' . ($item['doc_period_year'] ?? 'NULL')
                . ' period_month=' . ($item['period_month'] ?? 'NULL')
                . ' period_year=' . ($item['period_year'] ?? 'NULL')
                . ' freq=' . ($item['frequency'] ?? 'NA')
            );

            // Use period_month/period_year if available, fall back to doc_period_month/doc_period_year
            //$month = isset($item['period_month']) ? $item['period_month'] : (isset($item['doc_period_month']) ? $item['doc_period_month'] : '');
            //$year = isset($item['period_year']) ? $item['period_year'] : (isset($item['doc_period_year']) ? $item['doc_period_year'] : '');
            $month = $item['doc_period_month'] ?? 0;
            $year  = $item['doc_period_year'] ?? 0;
            $key = $item['company_id'] . '|' . $item['type_id'] . '|' . $month . '|' . $year;

            log_message(
                'error',
                'DEDUP KEY => ' . $key
            );

            if (!isset($deduped[$key])) {
                log_message('error', 'DEDUP ACTION => NEW');
                $deduped[$key] = $item;
            } else {
                log_message('error', 'DEDUP ACTION => MERGE');
                // Merge documents from duplicate entry
                if (isset($item['documents']) && is_array($item['documents'])) {
                    foreach ($item['documents'] as $doc) {
                        $exists = false;
                        if (isset($deduped[$key]['documents'])) {
                            foreach ($deduped[$key]['documents'] as $existingDoc) {
                                if ($existingDoc['document_id'] == $doc['document_id']) {
                                    $exists = true;
                                    break;
                                }
                            }
                        }
                        if (!$exists) {
                            $deduped[$key]['documents'][] = $doc;
                            $deduped[$key]['total_count'] = isset($deduped[$key]['total_count']) ? $deduped[$key]['total_count'] + 1 : 1;
                            if ($doc['is_uploaded']) {
                                $deduped[$key]['uploaded_count'] = isset($deduped[$key]['uploaded_count']) ? $deduped[$key]['uploaded_count'] + 1 : 1;
                            } else {
                                $deduped[$key]['pending_count'] = isset($deduped[$key]['pending_count']) ? $deduped[$key]['pending_count'] + 1 : 1;
                            }
                        }
                    }
                }
            }
        }
        log_message(
            'error',
            'DEDUP FINAL COUNT => ' . count($deduped)
        );
        
        return array_values($deduped);
    }

    /**
     * Get overdue documents from previous months (from document_start_date till last month)
     * Considers due_in_same_next_month column
     * Returns both uploaded and pending documents with status flag
     */
    private function _getPreviousMonthsOverdue($currentDate, $companyIds = null, $filterYear = null)
    {
        // ALWAYS use actual current date for due date comparisons
        $currentMonth = (int)date('n');
        $currentYear = (int)date('Y');  // ACTUAL current year, not filter year
        $currentDay = (int)date('j');
        
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        // Build year filter for previous months query - filters DOCUMENT PERIOD, not due date
        $yearFilter = "";
        if ($filterYear && $filterYear !== 'all') {
            $yearFilter = " AND pg.check_year = " . (int)$filterYear;
        }

        $sql = "
            WITH date_params AS (
                SELECT 
                    ?::date as current_date_val,
                    ?::int as current_month,
                    ?::int as current_year,
                    ?::int as current_day
            ),
            period_generator AS (
                SELECT 
                    st.id as type_id,
                    st.type_name,
                    st.frequency,
                    st.frequency_start_date,
                    COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                    st.due_in_same_next_month,
                    st.document_name as doc_sequence,
                    CAST(st.authority_id AS INTEGER) as authority_id,
                    a.authority_name,
                    c.id as company_id,
                    c.company_name,
                    md.document_id,
                    d.document_name,
                    periods.check_month,
                    periods.check_year,
                    CASE 
                        WHEN st.frequency_start_date ~ '^[0-9]+$' 
                        THEN CAST(st.frequency_start_date AS INTEGER)
                        ELSE NULL 
                    END as due_day,
                    -- Calculate actual due month/year based on due_in_same_next_month
                    -- due_in_same_next_month: 0 = same month, 1 = next month
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN
                            CASE WHEN periods.check_month = 12 THEN 1 ELSE periods.check_month + 1 END
                        ELSE periods.check_month
                    END as due_month,
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN
                            CASE WHEN periods.check_month = 12 THEN periods.check_year + 1 ELSE periods.check_year END
                        ELSE periods.check_year
                    END as due_year
                FROM sub_type st
                INNER JOIN mandatory_documents md ON st.id = md.type_id
                INNER JOIN documents d ON md.document_id = d.id
                INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
                INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
                CROSS JOIN date_params dp
                -- Generate months from document_start_date to LAST month (exclude current month)
                CROSS JOIN LATERAL (
                    SELECT 
                        EXTRACT(MONTH FROM generate_series)::int as check_month,
                        EXTRACT(YEAR FROM generate_series)::int as check_year
                    FROM generate_series(
                        GREATEST(
                            COALESCE(
                                CASE 
                                    WHEN COALESCE(cdh.custom_start_date, st.document_start_date) IS NULL OR TRIM(COALESCE(cdh.custom_start_date, st.document_start_date)::text) = '' 
                                    THEN NULL 
                                    ELSE COALESCE(cdh.custom_start_date, st.document_start_date)::date 
                                END, 
                                '2024-01-01'::date
                            ),
                            '2024-01-01'::date
                        ),
                        -- Stop at last month (exclude current month to avoid duplicates)
                        (DATE_TRUNC('month', dp.current_date_val) - INTERVAL '1 day')::date,
                        INTERVAL '1 month'
                    ) as generate_series
                ) as periods
                WHERE st.status = '1'
                AND LOWER(st.frequency) = 'monthly'
                AND st.frequency_start_date ~ '^[0-9]+$'
                " . $companyFilter . "
            )
            SELECT 
                pg.*,
                pg.check_month as overdue_month,
                pg.check_year as overdue_year,
                -- Get position in sequence for ordering
                COALESCE(
                    NULLIF(
                        position(',' || pg.document_id::text || ',' in ',' || pg.doc_sequence || ','),
                        0
                    ),
                    9999
                ) as doc_order,
                -- Check if this document is uploaded
                CASE WHEN ud.id IS NOT NULL THEN 1 ELSE 0 END as is_uploaded
            FROM period_generator pg
            CROSS JOIN date_params dp
            LEFT JOIN uploaded_documents ud ON ud.type_id = pg.type_id
                AND ud.document_id = pg.document_id
                AND ud.company_id = pg.company_id
                AND CAST(NULLIF(TRIM(ud.document_month::text), '') AS INTEGER) = pg.check_month
                AND CAST(NULLIF(TRIM(ud.document_year::text), '') AS INTEGER) = pg.check_year
                AND ud.is_deleted = 0
            WHERE 
                -- Only include if the due date has passed
                (
                    (pg.due_year < dp.current_year) 
                    OR (pg.due_year = dp.current_year AND pg.due_month < dp.current_month)
                    OR (pg.due_year = dp.current_year AND pg.due_month = dp.current_month AND pg.due_day < dp.current_day)
                )
                " . $yearFilter . "
            ORDER BY pg.check_year DESC, pg.check_month DESC, pg.company_name, pg.type_name, doc_order
        ";
        
        $query = $this->db->query($sql, [$currentDate, $currentMonth, $currentYear, $currentDay]);
        
        if ($query === FALSE) {
            return [];
        }
        
        return $this->_groupDocumentsWithStatusAndPeriod($query->result_array());
    }

    /**
     * Get upcoming documents (due next month)
     * Considers due_in_same_next_month column to calculate actual due date
     * Returns both uploaded and pending documents with status flag
     */
    public function getUpcomingDocuments($companyIds = null, $filterYear = null)
    {
        $currentMonth = (int)date('n');
        $currentDate = date('Y-m-d');
        $actualCurrentYear = (int)date('Y');

        // If "all" is selected, iterate through multiple years
        if ($filterYear === 'all' || empty($filterYear)) {
            $allResults = [];
            // Get years from 2024 to current year + 1 (for upcoming next month crossing year boundary)
            for ($year = 2024; $year <= $actualCurrentYear + 1; $year++) {
                $yearResults = $this->_getUpcomingForYear($companyIds, $year, $currentMonth, $currentDate);
                $allResults = array_merge($allResults, $yearResults);
            }
            return $this->_deduplicateUpcomingResults($allResults);
        }
        
        // Single year processing
        $results = $this->_getUpcomingForYear($companyIds, (int)$filterYear, $currentMonth, $currentDate);
        return $this->_deduplicateUpcomingResults($results);
    }
    
    /**
     * Get upcoming documents for a specific year
     */
    private function _getUpcomingForYear($companyIds, $filterYear, $currentMonth, $currentDate)
    {
        // Build company filter
        $companyFilter = "";
        if (!empty($companyIds) && is_array($companyIds)) {
            $companyFilter = " AND c.id IN (" . implode(',', array_map('intval', $companyIds)) . ")";
        }
        
        // Use actual current year for calculating upcoming month
        $actualCurrentYear = (int)date('Y');
        $nextMonth = $currentMonth + 1;
        $nextYear = $actualCurrentYear;
        if ($nextMonth > 12) {
            $nextMonth = 1;
            $nextYear++;
        }
        
        // Year filter: Check if the document period year matches the filter year
        // For "all" years filter or current year filter, we don't apply year restriction
        // because upcoming documents might cross into the next year (Dec -> Jan)
        $yearFilterUpcoming = "";
        if ($filterYear !== null && $filterYear !== 'all' && (int)$filterYear !== $actualCurrentYear) {
            // Only apply year filter if it's not the current year
            // When filtering by a past year, no upcoming documents should be shown
            // because "upcoming" means next month from NOW
            $yearFilterUpcoming = " AND 1=0"; // This effectively returns no results for past years
        }
        // Note: When filterYear equals current year or is 'all', we show actual upcoming documents
        // without year restriction
        
        $startDateCheckDateUpcoming = $actualCurrentYear . "-12-31";
        
        // Embed values directly in SQL to avoid bind parameter issues in CASE expressions
        /*$sql = "
                SELECT DISTINCT ON (
                    c.id,
                    st.id,
                    md.document_id,
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $currentMonth . "
                        ELSE " . $nextMonth . "
                    END,
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $actualCurrentYear . "
                        ELSE " . $nextYear . "
                    END
                )* from SELECT 
                st.id as type_id,
                st.type_name,
                st.frequency,
                st.frequency_start_date,
                COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                st.due_in_same_next_month,
                st.document_name as doc_sequence,
                CAST(st.authority_id AS INTEGER) as authority_id,
                a.authority_name,
                c.id as company_id,
                c.company_name,
                md.document_id,
                d.document_name,
                -- For due_in_same_next_month=1: document for current month is due next month
                -- For due_in_same_next_month=0: document for next month is due next month
                -- due_in_same_next_month: 0 = same month, 1 = next month
                CASE 
                    WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $currentMonth . "
                    ELSE " . $nextMonth . "
                END as doc_period_month,
                CASE 
                    WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $actualCurrentYear . "
                    ELSE " . $nextYear . "
                END as doc_period_year,
                CASE 
                    WHEN st.frequency_start_date ~ '^[0-9]+\$' 
                    THEN CAST(st.frequency_start_date AS INTEGER)
                    ELSE NULL 
                END as due_day,
                -- Get position in sequence for ordering
                COALESCE(
                    NULLIF(
                        position(',' || md.document_id::text || ',' in ',' || st.document_name || ','),
                        0
                    ),
                    9999
                ) as doc_order,
                -- Check if this document is uploaded
                CASE WHEN ud.id IS NOT NULL THEN 1 ELSE 0 END as is_uploaded
            FROM sub_type st
            INNER JOIN mandatory_documents md ON st.id = md.type_id
            INNER JOIN documents d ON md.document_id = d.id
            INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
            INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
            LEFT JOIN uploaded_documents ud ON ud.type_id = st.id
                AND ud.document_id = md.document_id
                AND ud.company_id = c.id
                AND CAST(NULLIF(TRIM(ud.document_month::text), '') AS INTEGER) = 
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $currentMonth . "
                        ELSE " . $nextMonth . "
                    END
                AND CAST(NULLIF(TRIM(ud.document_year::text), '') AS INTEGER) = 
                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $actualCurrentYear . "
                        ELSE " . $nextYear . "
                    END
                AND ud.is_deleted = 0
            WHERE st.status = '1'
            AND LOWER(st.frequency) = 'monthly'
            AND st.frequency_start_date ~ '^[0-9]+\$'
            " . $companyFilter . "
            " . $yearFilterUpcoming . "
            -- Document period (month/year) must be >= document_start_date (company custom or master)
            AND (
                COALESCE(cdh.custom_start_date, st.document_start_date) IS NULL 
                OR TRIM(COALESCE(cdh.custom_start_date, st.document_start_date)::text) = ''
                OR (
                    MAKE_DATE(
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $actualCurrentYear . "
                            ELSE " . $nextYear . "
                        END,
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1' THEN " . $currentMonth . "
                            ELSE " . $nextMonth . "
                        END,
                        1
                    ) >= DATE_TRUNC('month', COALESCE(cdh.custom_start_date, st.document_start_date)::date)
                )
            )
            --ORDER BY c.company_name, st.type_name, doc_order
            ORDER BY c.id,st.id,md.document_id,doc_order
        ";*/

        $sql = "SELECT DISTINCT ON (
                company_id,
                type_id,
                doc_period_month,
                doc_period_year
            )
            *
            FROM (
                SELECT
                    st.id AS type_id,
                    st.type_name,
                    st.frequency,
                    st.frequency_start_date,
                    COALESCE(cdh.custom_start_date, st.document_start_date) as document_start_date,
                    st.due_in_same_next_month,
                    st.document_name AS doc_sequence,
                    CAST(st.authority_id AS INTEGER) AS authority_id,
                    a.authority_name,
                    c.id AS company_id,
                    c.company_name,
                    md.document_id,
                    d.document_name,

                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1'
                            THEN " . $currentMonth . "
                        ELSE " . $nextMonth . "
                    END AS doc_period_month,

                    CASE 
                        WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1'
                            THEN " . $actualCurrentYear . "
                        ELSE " . $nextYear . "
                    END AS doc_period_year,

                    CASE 
                        WHEN st.frequency_start_date ~ '^[0-9]+$'
                            THEN CAST(st.frequency_start_date AS INTEGER)
                        ELSE NULL
                    END AS due_day,

                    COALESCE(
                        NULLIF(
                            position(',' || md.document_id::text || ',' 
                            in ',' || st.document_name || ','),
                            0
                        ),
                        9999
                    ) AS doc_order,

                    CASE WHEN ud.id IS NOT NULL THEN 1 ELSE 0 END AS is_uploaded

                FROM sub_type st
                INNER JOIN mandatory_documents md ON st.id = md.type_id
                INNER JOIN documents d ON md.document_id = d.id
                INNER JOIN authority a ON CAST(st.authority_id AS INT) = a.id
                INNER JOIN company c ON c.status = '1'
            INNER JOIN company_document_heads cdh ON cdh.type_id = st.id AND cdh.company_id = c.id AND cdh.is_enabled = TRUE
                LEFT JOIN uploaded_documents ud ON ud.type_id = st.id
                    AND ud.document_id = md.document_id
                    AND ud.company_id = c.id
                    AND CAST(NULLIF(TRIM(ud.document_month::text), '') AS INTEGER) =
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1'
                                THEN " . $currentMonth . "
                            ELSE " . $nextMonth . "
                        END
                    AND CAST(NULLIF(TRIM(ud.document_year::text), '') AS INTEGER) =
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1'
                                THEN " . $actualCurrentYear . "
                            ELSE " . $nextYear . "
                        END
                    AND ud.is_deleted = 0
                WHERE st.status = '1'
                AND LOWER(st.frequency) = 'monthly'
                AND st.frequency_start_date ~ '^[0-9]+$'
                " . $companyFilter . "
                " . $yearFilterUpcoming . "
                AND (
                    COALESCE(cdh.custom_start_date, st.document_start_date) IS NULL
                    OR TRIM(COALESCE(cdh.custom_start_date, st.document_start_date)::text) = ''
                    OR MAKE_DATE(
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1'
                                THEN " . $actualCurrentYear . "
                            ELSE " . $nextYear . "
                        END,
                        CASE 
                            WHEN COALESCE(st.due_in_same_next_month::text, '0') = '1'
                                THEN " . $currentMonth . "
                            ELSE " . $nextMonth . "
                        END,
                        1
                    ) >= DATE_TRUNC('month', COALESCE(cdh.custom_start_date, st.document_start_date)::date)
                )
            ) t
            ORDER BY company_id,type_id,doc_period_month,doc_period_year,doc_order
            ";
        
        $query = $this->db->query($sql);
        $monthlyUpcoming = $this->_groupDocumentsWithStatus($query->result_array());
        
        // Get non-monthly upcoming documents (Quarterly, Half Yearly, Yearly)
        // Pass specific year, not 'all', since we're already iterating
        $currentDay = (int)date('j');
        $nonMonthlyUpcoming = $this->_getNonMonthlyUpcoming($currentDate, $currentDay, $currentMonth, $actualCurrentYear, $companyIds, $filterYear);
        
        return array_merge($monthlyUpcoming, $nonMonthlyUpcoming);
    }

    /**
     * Helper: Group documents with status (uploaded/pending)
     * Also includes mandatory child documents in the pending count
     */
    private function _groupDocumentsWithStatus($documents)
    {
        $grouped = [];
        
        foreach ($documents as $doc) {
            // Include period in key to avoid duplicates across different months/years
            $periodMonth = $doc['doc_period_month'] ?? '';
            $periodYear = $doc['doc_period_year'] ?? '';
            $key = $doc['company_id'] . '|' . $doc['type_id'] . '|' . $periodMonth . '|' . $periodYear;
            
            if (!isset($grouped[$key])) {
                // Calculate adjusted due date for this document
                $dueDay = (int)($doc['due_day'] ?? 1);
                $dueMonth = (int)($doc['doc_period_month'] ?? date('n'));
                $dueYear = (int)($doc['doc_period_year'] ?? date('Y'));
                
                // Consider due_in_same_next_month
                if (isset($doc['due_in_same_next_month']) && $doc['due_in_same_next_month'] == 1) {
                    $dueMonth++;
                    if ($dueMonth > 12) {
                        $dueMonth = 1;
                        $dueYear++;
                    }
                }
                
                $adjustedDue = $this->getAdjustedDueDate($dueDay, $dueMonth, $dueYear);
                
                $grouped[$key] = [
                    'company_id' => $doc['company_id'],
                    'company_name' => $doc['company_name'],
                    'type_id' => $doc['type_id'],
                    'type_name' => $doc['type_name'],
                    'frequency' => $doc['frequency'],
                    'authority_id' => $doc['authority_id'] ?? null,
                    'authority_name' => $doc['authority_name'],
                    'due_day' => $doc['due_day'],
                    'adjusted_due_day' => $adjustedDue['day'],
                    'adjusted_due_reason' => $adjustedDue['reason'],
                    'due_in_same_next_month' => $doc['due_in_same_next_month'] ?? 'same',
                    'doc_period_month' => $doc['doc_period_month'] ?? null,
                    'doc_period_year' => $doc['doc_period_year'] ?? null,
                    'period_month' => $periodMonth,
                    'period_year' => $periodYear,
                    'documents' => [],
                    'uploaded_count' => 0,
                    'pending_count' => 0,
                    'total_count' => 0,
                    'mandatory_child_pending' => 0
                ];
            }
            
            $isUploaded = isset($doc['is_uploaded']) && $doc['is_uploaded'] == 1;
            
            $grouped[$key]['documents'][] = [
                'document_id' => $doc['document_id'],
                'document_name' => $doc['document_name'],
                'is_uploaded' => $isUploaded,
                'is_mandatory_child' => false
            ];
            
            $grouped[$key]['total_count']++;
            if ($isUploaded) {
                $grouped[$key]['uploaded_count']++;
            } else {
                $grouped[$key]['pending_count']++;
            }
        }

        // Add mandatory child documents to each group
        foreach ($grouped as $key => &$group) {
            $docMonth = $group['doc_period_month'] ?? date('n');
            $docYear = $group['doc_period_year'] ?? date('Y');
            
            // Format month with leading zero if needed
            $monthStr = str_pad($docMonth, 2, '0', STR_PAD_LEFT);
            
            $pendingChildren = $this->getMandatoryChildDocumentsPending(
                $group['company_id'],
                $group['type_id'],
                $monthStr,
                $docYear
            );
            
            foreach ($pendingChildren as $child) {
                // Check if this child is not already in the documents list
                $exists = false;
                foreach ($group['documents'] as $existingDoc) {
                    if ($existingDoc['document_id'] == $child['document_id']) {
                        $exists = true;
                        break;
                    }
                }
                
                if (!$exists) {
                    $group['documents'][] = [
                        'document_id' => $child['document_id'],
                        'document_name' => $child['document_name'] . ' (Child)',
                        'is_uploaded' => false,
                        'is_mandatory_child' => true
                    ];
                    $group['total_count']++;
                    $group['pending_count']++;
                    $group['mandatory_child_pending']++;
                }
            }
        }
        
        // Filter out groups where all documents are uploaded
        $result = [];
        foreach ($grouped as $group) {
            if ($group['pending_count'] > 0) {
                $result[] = $group;
            }
        }
        
        return $result;
    }

    /**
     * Helper: Group documents with period info and status
     */
    private function _groupDocumentsWithStatusAndPeriod($documents)
    {
        $grouped = [];
        
        foreach ($documents as $doc) {
            $key = $doc['company_id'] . '|' . $doc['type_id'] . '|' . $doc['overdue_month'] . '|' . $doc['overdue_year'];
            
            if (!isset($grouped[$key])) {
                // Calculate adjusted due date for overdue documents
                $dueDay = (int)($doc['due_day'] ?? 1);
                $overdueMonth = (int)($doc['overdue_month'] ?? date('n'));
                $overdueYear = (int)($doc['overdue_year'] ?? date('Y'));
                
                // Consider due_in_same_next_month for due calculation
                $dueMonth = $overdueMonth;
                $dueYear = $overdueYear;
                if (isset($doc['due_in_same_next_month']) && $doc['due_in_same_next_month'] == 1) {
                    $dueMonth++;
                    if ($dueMonth > 12) {
                        $dueMonth = 1;
                        $dueYear++;
                    }
                }
                
                $adjustedDue = $this->getAdjustedDueDate($dueDay, $dueMonth, $dueYear);
                
                $grouped[$key] = [
                    'company_id' => $doc['company_id'],
                    'company_name' => $doc['company_name'],
                    'type_id' => $doc['type_id'],
                    'type_name' => $doc['type_name'],
                    'frequency' => $doc['frequency'],
                    'authority_id' => $doc['authority_id'] ?? null,
                    'authority_name' => $doc['authority_name'],
                    'due_day' => $doc['due_day'],
                    'adjusted_due_day' => $adjustedDue['day'],
                    'adjusted_due_reason' => $adjustedDue['reason'],
                    'due_in_same_next_month' => $doc['due_in_same_next_month'] ?? 'same',
                    'overdue_month' => $doc['overdue_month'],
                    'overdue_year' => $doc['overdue_year'],
                    'documents' => [],
                    'uploaded_count' => 0,
                    'pending_count' => 0,
                    'total_count' => 0
                ];
            }
            
            $isUploaded = isset($doc['is_uploaded']) && $doc['is_uploaded'] == 1;
            
            $grouped[$key]['documents'][] = [
                'document_id' => $doc['document_id'],
                'document_name' => $doc['document_name'],
                'is_uploaded' => $isUploaded,
                'is_mandatory_child' => false
            ];
            
            $grouped[$key]['total_count']++;
            if ($isUploaded) {
                $grouped[$key]['uploaded_count']++;
            } else {
                $grouped[$key]['pending_count']++;
            }
        }

        // Add mandatory child documents to each group
        foreach ($grouped as $key => &$group) {
            $overdueMonth = $group['overdue_month'] ?? date('n');
            $overdueYear = $group['overdue_year'] ?? date('Y');
            
            // Format month with leading zero if needed
            $monthStr = str_pad($overdueMonth, 2, '0', STR_PAD_LEFT);
            
            $pendingChildren = $this->getMandatoryChildDocumentsPending(
                $group['company_id'],
                $group['type_id'],
                $monthStr,
                $overdueYear
            );
            
            foreach ($pendingChildren as $child) {
                // Check if this child is not already in the documents list
                $exists = false;
                foreach ($group['documents'] as $existingDoc) {
                    if ($existingDoc['document_id'] == $child['document_id']) {
                        $exists = true;
                        break;
                    }
                }
                
                if (!$exists) {
                    $group['documents'][] = [
                        'document_id' => $child['document_id'],
                        'document_name' => $child['document_name'] . ' (Child)',
                        'is_uploaded' => false,
                        'is_mandatory_child' => true
                    ];
                    $group['total_count']++;
                    $group['pending_count']++;
                }
            }
        }
        unset($group); // Important: unset reference after by-reference loop
        
        // Filter out groups where all documents are uploaded
        $result = [];
        foreach ($grouped as $group) {
            if ($group['pending_count'] > 0) {
                $result[] = $group;
            }
        }
        
        return $result;
    }

    /**
     * Helper: Group documents with period info (for overdue - legacy)
     */
    private function _groupDocumentsWithPeriod($documents)
    {
        return $this->_groupDocumentsWithStatusAndPeriod($documents);
    }

    /**
     * Get all uploaded documents
     */
    public function getUploadedDocuments($limit = null, $offset = 0, $companyIds = null, $filterYear = null)
    {
        $this->db->select('
            ud.id as upload_id,
            ud.file_name,
            ud.file_path,
            ud.document_month,
            ud.document_year,
            ud.uploaded_at,
            c.id as company_id,
            c.company_name,
            st.id as type_id,
            st.type_name,
            st.frequency,
            st.frequency_start_date,
            a.authority_name,
            d.id as document_id,
            d.document_name,
            CASE WHEN f.id IS NOT NULL THEN 1 ELSE 0 END as is_favourite
        ', FALSE);
        
        $this->db->from('uploaded_documents ud');
        $this->db->join('company c', 'ud.company_id = c.id', 'left');
        $this->db->join('sub_type st', 'ud.type_id = st.id', 'left');
        $this->db->join('authority a', 'CAST(st.authority_id AS INT) = a.id', 'left');
        $this->db->join('documents d', 'ud.document_id = d.id', 'left');
        $this->db->join('favourites f', 'f.upload_id = ud.id', 'left');
        $this->db->where('ud.is_deleted', 0);
        
        // Filter by companies
        if (!empty($companyIds) && is_array($companyIds)) {
            $this->db->where_in('c.id', $companyIds);
        }
        
        // Filter by year (skip if "all" is selected)
        if (!empty($filterYear) && $filterYear !== 'all') {
            $this->db->where('CAST(NULLIF(TRIM(ud.document_year::text), \'\') AS INTEGER) =', (int)$filterYear, false);
        }
        
        $this->db->order_by('ud.uploaded_at', 'DESC');
        
        if ($limit) {
            $this->db->limit($limit, $offset);
        }
        
        return $this->db->get()->result_array();
    }

    /**
     * Get uploaded documents grouped by type
     */
    /*public function getUploadedDocumentsGrouped($companyIds = null, $filterYear = null)
    {
        $this->db->select('
            c.id as company_id,
            c.company_name,
            st.id as type_id,
            st.type_name,
            st.frequency,
            st.due_in_same_next_month,
            st.frequency_start_date,
            a.id as authority_id,
            a.authority_name,
            d.id as document_id,
            d.document_name,
            ud.id as upload_id,
            ud.document_year,
            ud.document_month,
            ud.uploaded_at,
            ud.file_path
        ', FALSE);

        $this->db->from('uploaded_documents ud');
        $this->db->join('company c', 'ud.company_id = c.id', 'left');
        $this->db->join('sub_type st', 'ud.type_id = st.id', 'left');
        $this->db->join('authority a', 'CAST(st.authority_id AS INT) = a.id', 'left');
        $this->db->join('documents d', 'ud.document_id = d.id', 'left');
        $this->db->where('ud.is_deleted', 0);
        
        // Filter by companies
        if (!empty($companyIds) && is_array($companyIds)) {
            $this->db->where_in('c.id', $companyIds);
        }
        
        // Filter by year (skip if "all" is selected)
        if (!empty($filterYear) && $filterYear !== 'all') {
            $this->db->where('CAST(NULLIF(TRIM(ud.document_year::text), \'\') AS INTEGER) =', (int)$filterYear, false);
        }

        $this->db->order_by('ud.uploaded_at', 'DESC');
        $this->db->order_by('c.company_name', 'ASC');
        $this->db->order_by('st.type_name', 'ASC');

        $query = $this->db->get();
        $resultDocComplete = $query->result();

        $groupedData = [];

        if (!empty($resultDocComplete)) {
            foreach ($resultDocComplete as $doc) {
                $key = $doc->company_id . '|' . $doc->type_id . '|' . $doc->document_year . '|' . $doc->document_month;

                if (!isset($groupedData[$key])) {
                    $groupedData[$key] = [
                        'company_id' => $doc->company_id,
                        'company_name' => $doc->company_name,
                        'type_id' => $doc->type_id,
                        'type_name' => $doc->type_name,
                        'authority_id' => $doc->authority_id,
                        'authority_name' => $doc->authority_name,
                        'document_year' => $doc->document_year,
                        'document_month' => $doc->document_month,
                        'documents' => [],
                        'uploaded_date' => $doc->uploaded_at,
                        'frequency' => $doc->frequency,
                        'due_in_same_next_month' => $doc->due_in_same_next_month,
                        'frequency_start_date' => $doc->frequency_start_date,
                        'uploaded_count' => 0,
                    ];
                }
                $groupedData[$key]['documents'][] = [
                    'document_id' => $doc->document_id,
                    'document_name' => $doc->document_name,
                    'upload_id' => $doc->upload_id,
                    'file_path' => $doc->file_path,
                    'uploaded_at' => $doc->uploaded_at
                ];
                $groupedData[$key]['uploaded_count']++;
            }
        }
        
        return array_values($groupedData);
    }*/

    public function getUploadedDocumentsGrouped($companyIds = null, $filterYear = null)
    {
        $this->db->select('
            c.id as company_id,
            c.company_name,
            st.id as type_id,
            st.type_name,
            st.document_name as required_documents,
            st.frequency,
            st.due_in_same_next_month,
            st.frequency_start_date,
            a.id as authority_id,
            a.authority_name,
            d.id as document_id,
            d.document_name,
            ud.id as upload_id,
            ud.document_year,
            ud.document_month,
            ud.uploaded_at,
            ud.file_path
        ', FALSE);

        $this->db->from('uploaded_documents ud');
        $this->db->join('company c', 'ud.company_id = c.id', 'left');
        $this->db->join('sub_type st', 'ud.type_id = st.id', 'left');
        $this->db->join('authority a', 'CAST(st.authority_id AS INT) = a.id', 'left');
        $this->db->join('documents d', 'ud.document_id = d.id', 'left');
        $this->db->where('ud.is_deleted', 0);

        if (!empty($companyIds) && is_array($companyIds)) {
            $this->db->where_in('c.id', $companyIds);
        }

        if (!empty($filterYear) && $filterYear !== 'all') {
            $this->db->where(
                'CAST(NULLIF(TRIM(ud.document_year::text), \'\') AS INTEGER) =',
                (int)$filterYear,
                false
            );
        }

        $this->db->order_by('ud.uploaded_at', 'DESC');
        $this->db->order_by('c.company_name', 'ASC');
        $this->db->order_by('st.type_name', 'ASC');

        $query = $this->db->get();
        $resultDocComplete = $query->result();

        $groupedData = [];

        if (!empty($resultDocComplete)) {
            foreach ($resultDocComplete as $doc) {

                $key = $doc->company_id . '|' . $doc->type_id . '|' .
                    $doc->document_year . '|' . $doc->document_month;

                if (!isset($groupedData[$key])) {

                    // 🔑 Required documents from sub_type
                    /*$requiredDocs = array_filter(
                        array_map('intval', explode(',', $doc->required_documents))
                    );*/

                    $requiredDocs = array_values(array_unique(array_filter(array_map(
                        function ($v) {
                            return is_numeric(trim($v)) ? (int)trim($v) : null;
                        },
                        explode(',', $doc->required_documents)
                    ))));

                    $groupedData[$key] = [
                        'company_id' => $doc->company_id,
                        'company_name' => $doc->company_name,
                        'type_id' => $doc->type_id,
                        'type_name' => $doc->type_name,
                        'authority_id' => $doc->authority_id,
                        'authority_name' => $doc->authority_name,
                        'document_year' => $doc->document_year,
                        'document_month' => $doc->document_month,
                        'documents' => [],
                        'uploaded_date' => $doc->uploaded_at,
                        'frequency' => $doc->frequency,
                        'due_in_same_next_month' => $doc->due_in_same_next_month,
                        'frequency_start_date' => $doc->frequency_start_date,

                        'required_document_ids' => $requiredDocs,
                        'uploaded_document_ids' => [],
                        'uploaded_count' => 0
                    ];
                }

                $groupedData[$key]['documents'][] = [
                    'document_id' => $doc->document_id,
                    'document_name' => $doc->document_name,
                    'upload_id' => $doc->upload_id,
                    'file_path' => $doc->file_path,
                    'uploaded_at' => $doc->uploaded_at
                ];

                $groupedData[$key]['uploaded_document_ids'][] = (int)$doc->document_id;
                $groupedData[$key]['uploaded_count']++;
            }
        }

        // ✅ FINAL STATUS CALCULATION (NO PENDING LOGIC TOUCHED)
        /*foreach ($groupedData as &$group) {
            $required = $group['required_document_ids'];
            //$uploaded = array_unique($group['uploaded_document_ids']);

            $uploaded = array_values(array_unique(array_map('intval', $group['uploaded_document_ids'])));

            $missing = array_diff($required, $uploaded);

            $group['total_required'] = count($required);
            $group['pending_count'] = count($missing);
            $group['is_complete'] = empty($missing) ? 1 : 0;

            unset($group['required_document_ids'], $group['uploaded_document_ids']);
        }*/

        foreach ($groupedData as &$group) {

            $required = array_values(array_unique(array_filter(array_map(
                function ($v) {
                    return is_numeric(trim($v)) ? (int)trim($v) : null;
                },
                explode(',', implode(',', $group['required_document_ids']))
            ))));
        
            $uploaded = array_values(array_unique(array_map(
                'intval',
                $group['uploaded_document_ids']
            )));
        
            $missing = array_diff($required, $uploaded);

            $group['total_required'] = count($required);
            $group['uploaded_count'] = count($uploaded);
            $group['pending_count'] = count($missing);
            $group['missing_document_ids'] = array_values($missing);
        
            $group['is_complete'] = empty($missing) ? 1 : 0;
        }
        
        //return array_values($groupedData);
        return array_values(array_filter($groupedData, function ($group) {
            return $group['is_complete'] === 1;
        }));
    }

    /**
     * Get dashboard counts
     */
    public function getDashboardCounts($user_id = null, $companyIds = null, $filterYear = null)
    {
        $counts = [
            'favourites' => 0,
            'pending' => 0,
            'overdue' => 0,
            'upcoming' => 0,
            'uploaded' => 0
        ];
        
        // Favourites count - check if table exists first
        try {
            $this->db->from('favourites f');
            $this->db->join('uploaded_documents ud', 'f.upload_id = ud.id', 'inner');
            $this->db->join('company c', 'ud.company_id = c.id', 'left');
            $this->db->where('ud.is_deleted', 0);
            if ($user_id) {
                $this->db->where('f.user_id', $user_id);
            }
            if (!empty($companyIds) && is_array($companyIds)) {
                $this->db->where_in('c.id', $companyIds);
            }
            $counts['favourites'] = $this->db->count_all_results();
        } catch (Exception $e) {
            $counts['favourites'] = 0;
        }
        
        // Pending count
        $pending = $this->getPendingDocuments($companyIds, $filterYear);
        $counts['pending'] = count($pending);
        
        // Overdue count
        $overdue = $this->getOverdueDocuments($companyIds, $filterYear);
        $counts['overdue'] = count($overdue);
        
        // Upcoming count
        $upcoming = $this->getUpcomingDocuments($companyIds, $filterYear);
        $counts['upcoming'] = count($upcoming);
        
        // Uploaded count (grouped by document head + period)
        $uploaded = $this->getUploadedDocumentsGrouped($companyIds, $filterYear);
        $counts['uploaded'] = count($uploaded);
        
        return $counts;
    }

    /**
     * Toggle favourite status
     */
    public function toggleFavourite($upload_id, $user_id)
    {
        // Check if already favourite
        $existing = $this->db->get_where('favourites', [
            'upload_id' => $upload_id,
            'user_id' => $user_id
        ])->row();
        
        if ($existing) {
            // Remove from favourites
            $this->db->where('id', $existing->id)->delete('favourites');
            return ['status' => 'removed', 'is_favourite' => false];
        } else {
            // Add to favourites
            $this->db->insert('favourites', [
                'upload_id' => $upload_id,
                'user_id' => $user_id,
                'created_at' => date('Y-m-d H:i:s')
            ]);
            return ['status' => 'added', 'is_favourite' => true];
        }
    }

    /**
     * Add to favourites (only adds, doesn't toggle)
     * Used for bulk adding from View Documents page
     */
    public function addToFavourite($upload_id, $user_id)
    {
        // Check if already favourite
        $existing = $this->db->get_where('favourites', [
            'upload_id' => $upload_id,
            'user_id' => $user_id
        ])->row();
        
        if ($existing) {
            // Already a favourite, return current status
            return ['status' => 'already_exists', 'is_favourite' => true];
        } else {
            // Add to favourites
            $this->db->insert('favourites', [
                'upload_id' => $upload_id,
                'user_id' => $user_id,
                'created_at' => date('Y-m-d H:i:s')
            ]);
            return ['status' => 'added', 'is_favourite' => true];
        }
    }

    /**
     * Check if document is favourite
     */
    public function isFavourite($upload_id, $user_id)
    {
        return $this->db->get_where('favourites', [
            'upload_id' => $upload_id,
            'user_id' => $user_id
        ])->num_rows() > 0;
    }

    /**
     * Helper: Group documents by company and type
     */
    private function _groupDocuments($documents)
    {
        $grouped = [];
        
        foreach ($documents as $doc) {
            $key = $doc['company_id'] . '|' . $doc['type_id'];
            
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'company_id' => $doc['company_id'],
                    'company_name' => $doc['company_name'],
                    'type_id' => $doc['type_id'],
                    'type_name' => $doc['type_name'],
                    'frequency' => $doc['frequency'],
                    'authority_name' => $doc['authority_name'],
                    'due_day' => $doc['due_day'],
                    'documents' => []
                ];
            }
            
            $grouped[$key]['documents'][] = [
                'document_id' => $doc['document_id'],
                'document_name' => $doc['document_name']
            ];
        }
        
        return array_values($grouped);
    }
}

