<?php

class Company_Model extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
        $this->load->database();
    }
	public function getCompanies() {
		$role = $this->session->userdata('role_id');
		$user_id = $this->session->userdata('id');
		
		// Super Admin (role 1) gets all companies
		if ($role == 1 || $role == '1') {
			return $this->db->select('id, company_name')
				->from('company')
				->where('status', '1')
				->order_by('company_name', 'ASC')
				->get()
				->result_array();
		}
		
		// Admin (role 2) and User (role 3) get 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
	}
    public function get_all_types()
	{
		return $this->db->get('sub_type')->result_array();
	}

	public function get_all_files()
	{
		return $this->db->get('company_files')->result_array();
	}
	public function getSubTypeDetails($type_id) {
		$this->db->select('st.frequency, d.document_name');
		$this->db->from('sub_type st');
		$this->db->join('documents d', 'd.id = st.document_id', 'left');
		$this->db->where('st.id', $type_id);
		return $this->db->get()->row_array();
	}
	public function getFilteredDocuments($company_id, $authority = null, $month = null, $year = null, $allowedAuthorityIds = null)
    {
        //original
        $this->db->select('
        ud.id as uploaded_id,
        ud.company_id,
        ud.type_id,
        ud.document_id,
        ud.document_name as uploaded_doc_name,
        ud.file_path,
        ud.document_month,
        ud.document_year,
		ud.uploaded_at,
        st.type_name,
        dm.document_name,
        at.authority_name,
        st.authority_id
    ');
    $this->db->from('uploaded_documents ud');
    $this->db->join('sub_type st', 'st.id = ud.type_id', 'left');
    $this->db->join('documents dm', 'dm.id = ud.document_id', 'left');
	//$this->db->join('authority at', 'CAST(at.id AS TEXT) = st.authority_id', 'left');
    $this->db->join('authority at', 'at.id = CAST(st.authority_id AS INTEGER)', 'left');
	$this->db->where('ud.company_id',$company_id);
    $this->db->where('ud.is_deleted',0);
    // Filters
    if (!empty($authority)) {
        //$this->db->where('st.authority_id', $authority);
        $this->db->where('CAST(st.authority_id AS INTEGER)', (int)$authority, false);
    }
    // Filter by allowed authority IDs for User role
    if (!empty($allowedAuthorityIds) && is_array($allowedAuthorityIds)) {
        $this->db->where_in('CAST(st.authority_id AS INTEGER)', $allowedAuthorityIds, false);
    }
    if (!empty($month)) {
        $this->db->where('ud.document_month', $month);
    }
    if (!empty($year)) {
        $this->db->where('ud.document_year', $year);
    }

    $this->db->order_by('st.type_name');
    $this->db->order_by('ud.document_year', 'DESC');
    $this->db->order_by('ud.document_month', 'DESC');

    $query = $this->db->get();
    //echo '<pre>';
    log_message('debug', 'Executed SQL: ' . $this->db->last_query());
	
    $results = $query->result_array(); 

    //print_r($results); // exit;

    // Group by type_name
    $grouped = [];
    foreach ($results as $row) {
        $type = $row['type_name'] ?? 'Others';
        $grouped[$type]['type_name'] = $type;
        $grouped[$type]['authority_name'] = $row['authority_name'];
		 $grouped[$type]['doc_count'] = isset($grouped[$type]['doc_count']) 
            ? $grouped[$type]['doc_count'] + 1 
            : 1;
        $grouped[$type]['documents'][] = [
            'doc_id'     => $row['uploaded_id'],
            'type_id'     => $row['type_id'],
            'doc_name'     => $row['document_name'] ?: $row['uploaded_doc_name'],
            'file_name'    => basename($row['file_path']),
            'file_path'    => $row['file_path'],
            'month'        => $row['document_month'],
            'year'         => $row['document_year'],
            'uploaded_at'         => date('d-M-Y',strtotime($row['uploaded_at']))
        ];
    }
	//echo '<pre>'; print_R($grouped);exit;
    return $grouped;
    }

	public function getFrequencyAndDocumentsByType($type_id)
	{
		// Make sure type_id is integer
		$type_id = (int)$type_id;

		$sql = "
			SELECT st.frequency, d.document_name, d.id as document_id
			FROM sub_type st
			LEFT JOIN documents d
			ON d.id = ANY(
				string_to_array(trim(st.document_name), ',')::int[]
			)
			WHERE st.id = ?
		";

		$query = $this->db->query($sql, [$type_id]);

		if ($query->num_rows() > 0) {
			return $query->result_array(); // returns multiple documents
		}
		return [];
	}
	public function getAuthorities(){
        return $this->db->where('status',1)->get('authority')->result_array();
    }

    // Get types by authority
    public function getTypesByAuthority($authority_id){
        return $this->db->where('authority_id',$authority_id)
                        ->where('status',1)
                        ->get('sub_type')
                        ->result_array();
    }

    // Get frequency by type
    public function getFrequencyByType($type_id){
        $row = $this->db->select('frequency')
                        ->where('id',$type_id)
                        ->get('sub_type')
                        ->row();
        return $row ? $row->frequency : '';
    }

    // Get mandatory documents by type
    public function getDocumentsByType($type_id){
        // Get document sequence from sub_type.document_name
        $sub_type = $this->db->select('document_name')
                             ->where('id', $type_id)
                             ->get('sub_type')
                             ->row();
        $doc_sequence = [];
        if ($sub_type && !empty($sub_type->document_name)) {
            $doc_sequence = array_map('intval', array_filter(explode(',', $sub_type->document_name)));
        }

		$this->db->select('d.id, d.document_name, md.mandatory');
		$this->db->from('mandatory_documents md');
		$this->db->join('documents d', 'CAST(md.document_id AS INTEGER) = d.id', 'left', false);
		$this->db->where('md.type_id', $type_id);
		$this->db->where('d.status', 1);
		$docs = $this->db->get()->result_array();

        // Sort according to sequence from sub_type.document_name
        if (!empty($doc_sequence)) {
            usort($docs, function($a, $b) use ($doc_sequence) {
                $posA = array_search((int)$a['id'], $doc_sequence);
                $posB = array_search((int)$b['id'], $doc_sequence);
                if ($posA === false) $posA = PHP_INT_MAX;
                if ($posB === false) $posB = PHP_INT_MAX;
                return $posA - $posB;
            });
        }

        // Convert back to objects to maintain backward compatibility
        return array_map(function($row) {
            return (object) $row;
        }, $docs);
	}

    // Alias for getDocumentsByType (used in some controllers)
    public function getDocumentsForType($type_id){
        return $this->getDocumentsByType($type_id);
    }
	
	public function getAuthorityByType($type_id)
	{
		// Join documents table with mandatory_documents
		$this->db->select('t.id, t.type_name, a.id as aid, a.authority_name');
		$this->db->from('sub_type t');
		$this->db->join('authority a', 'a.id = t.authority_id', 'left', FALSE); // cast to integer
		$this->db->where('t.id', $type_id);
		$query = $this->db->get();
		
		return $query->result_array(); // array of documents with mandatory info
	}
	
	public function getDocuments($authority_ids, $company_id = null, $years = null) {
        if (empty($authority_ids)) return [];

        // CI automatically escapes IN arrays
        $this->db->select('a.type_name AS authority,
                            a.id as authority_id,
							ud.id as doc_id,
							ud.file_name as file_name,
                           ud.document_year AS year,
                           ud.document_month AS month,
                           d.document_name AS name,
                           ud.is_deleted as data_deleted,
                           ud.uploaded_at as uploaded_at,
                           a.alias_name as doc_alias,
                           u.first_name as first_name,
                           u.last_name as last_name')
                 ->from('uploaded_documents ud')
                 ->join('documents d', 'd.id = ud.document_id', 'left')
                 ->join('sub_type a', 'a.id = ud.type_id', 'left')
                 ->join('users u', 'u.id = CAST(ud.user_id AS INTEGER)', 'left');
				
					$this->db->where_in('ud.type_id', $authority_ids);
                    $this->db->where('ud.is_deleted', 0);

					if (!empty($company_id)) {
						$this->db->where('ud.company_id', $company_id);
					}
					
					// Year filter
					if (!empty($years) && is_array($years)) {
						$this->db->where_in('ud.document_year', $years);
					}

                    // Order by year descending, month ascending
                    $this->db->order_by('ud.document_year', 'DESC');
                    $this->db->order_by('ud.document_month', 'ASC');

                //$this->db->order_by('a.type_name ASC, ud.document_year DESC, ud.document_month DESC');
                $this->db->order_by('
                CASE 
                    WHEN position(ud.document_id::text in a.document_name) > 0 
                    THEN position(ud.document_id::text in a.document_name)
                    ELSE 9999 
                END
            ', '', false);

        log_message('error', 'inside get Documents method: ' . print_r($authority_ids, TRUE));
        log_message('error', 'inside get Documents method: ' . $company_id);
        
        //return $this->db->get()->result_array();
        $result = $this->db->get()->result_array();

        foreach ($result as &$row) {
            $filepath = FCPATH . 'uploads/documents/' . $row['file_name'];

            if (!empty($row['file_name']) && file_exists($filepath)) {
                $row['file_size'] = filesize($filepath); // size in bytes
            } else {
                $row['file_size'] = null;
            }

            // full name
            //$row['uploaded_by'] = trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? ''));
            $row['uploaded_by'] = trim(($row['first_name'] ?? ''));
        }

        return $result;
    }
	public function getAllDocuments($company_id = null, $years = null)
	{
		$this->db->select('
			a.type_name AS authority,
            a.id as authority_type_id,
			ud.id as doc_id,
			ud.file_name as file_name,
			ud.document_year AS year,
			ud.document_month AS month,
            ud.is_deleted as data_deleted,
			d.document_name AS name,
            a.alias_name as doc_alias,
            ud.uploaded_at as uploaded_at,
            u.first_name as first_name,
            u.last_name as last_name
		')
		->from('uploaded_documents ud')
		->join('documents d', 'd.id = ud.document_id', 'left')
		->join('sub_type a', 'a.id = ud.type_id', 'left')
        ->join('users u', 'u.id = CAST(ud.user_id AS INTEGER)', 'left');
        
		// Filter by company if needed
		if (!empty($company_id)) {
			$this->db->where('ud.company_id', $company_id);
		}
		
		// Filter by years if provided
		if (!empty($years) && is_array($years)) {
			$this->db->where_in('ud.document_year', $years);
		}
		
        $this->db->where('ud.is_deleted', 0);
        // Order by year descending, month ascending
        $this->db->order_by('ud.document_year', 'DESC');
        $this->db->order_by('ud.document_month', 'ASC');
		//$this->db->order_by('a.type_name ASC, ud.document_year DESC, ud.document_month DESC');
        $this->db->order_by('
            CASE 
                WHEN position(ud.document_id::text in a.document_name) > 0 
                THEN position(ud.document_id::text in a.document_name)
                ELSE 9999 
            END
        ', '', false);
        //log_message('error', 'inside Get All Documents Model: ' . $company_id);
        //log_message('error', 'inside get Documents method: ' . print_r($this->db->get()->result_array(), TRUE));
		
		$result = $this->db->get()->result_array();

        foreach ($result as &$row) {
            $filepath = FCPATH . 'uploads/documents/' . $row['file_name'];

            if (!empty($row['file_name']) && file_exists($filepath)) {
                $sizeInBytes = filesize($filepath);
                $row['file_size'] = number_format($sizeInBytes / 1024, 2) . ' KB';
            } else {
                $row['file_size'] = null;
            }

            // full name
            //$row['uploaded_by'] = trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? ''));
            $row['uploaded_by'] = trim(($row['first_name'] ?? ''));
        }

        return $result;
	}
	
	 public function send_reminders()
    {
        $current_date = date('Y-m-d');
        $reminders = $this->db->where('reminder_date', $today)->get('reminders')->result();

        $this->load->library('email');
        foreach ($reminders as $reminder) {
            // Load Email library
            //$this->load->library('email');

            $this->email->from('your@email.com', 'Your App Name');
            $this->email->to($reminder->email);
            $this->email->subject('Reminder');
            $this->email->message("Hello {$reminder->name},\n\nThis is your reminder for today.");

            if ($this->email->send()) {
                log_message('info', "Reminder sent to {$reminder->email}");
            } else {
                log_message('error', "Failed to send to {$reminder->email}");
            }
        }
    }

    public function getReminders()
    {
        $sql = "
            SELECT *,
                CASE
                    WHEN frequency_start_date::int = EXTRACT(DAY FROM CURRENT_DATE)::int THEN 'today'
                    WHEN frequency_start_date::int = (
                        CASE
                            WHEN EXTRACT(DAY FROM CURRENT_DATE)::int < 31 THEN EXTRACT(DAY FROM CURRENT_DATE)::int + 1
                            ELSE 1
                        END
                    ) THEN '1_day'

                    WHEN (
                        EXTRACT(DAY FROM CURRENT_DATE)::int <= 26
                        AND frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND (EXTRACT(DAY FROM CURRENT_DATE)::int + 5)
                    )
                    OR (
                        EXTRACT(DAY FROM CURRENT_DATE)::int > 26
                        AND (
                            frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND 31
                            OR frequency_start_date::int BETWEEN 1 AND ((EXTRACT(DAY FROM CURRENT_DATE)::int + 5) - 31)
                        )
                    ) THEN '5_days'

                    WHEN (
                        EXTRACT(DAY FROM CURRENT_DATE)::int <= 21
                        AND frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND (EXTRACT(DAY FROM CURRENT_DATE)::int + 10)
                    )
                    OR (
                        EXTRACT(DAY FROM CURRENT_DATE)::int > 21
                        AND (
                            frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND 31
                            OR frequency_start_date::int BETWEEN 1 AND ((EXTRACT(DAY FROM CURRENT_DATE)::int + 10) - 31)
                        )
                    ) THEN '10_days'

                    ELSE 'unknown'
                END AS reminder_type
            FROM sub_type
            WHERE frequency = 'Monthly'
            AND status = '1'
            AND (
                frequency_start_date::int = EXTRACT(DAY FROM CURRENT_DATE)::int
                OR frequency_start_date::int = (
                    CASE
                        WHEN EXTRACT(DAY FROM CURRENT_DATE)::int < 31 THEN EXTRACT(DAY FROM CURRENT_DATE)::int + 1
                        ELSE 1
                    END
                )
                OR (
                    EXTRACT(DAY FROM CURRENT_DATE)::int <= 26
                    AND frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND (EXTRACT(DAY FROM CURRENT_DATE)::int + 5)
                )
                OR (
                    EXTRACT(DAY FROM CURRENT_DATE)::int > 26
                    AND (
                        frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND 31
                        OR frequency_start_date::int BETWEEN 1 AND ((EXTRACT(DAY FROM CURRENT_DATE)::int + 5) - 31)
                    )
                )
                OR (
                    EXTRACT(DAY FROM CURRENT_DATE)::int <= 21
                    AND frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND (EXTRACT(DAY FROM CURRENT_DATE)::int + 10)
                )
                OR (
                    EXTRACT(DAY FROM CURRENT_DATE)::int > 21
                    AND (
                        frequency_start_date::int BETWEEN EXTRACT(DAY FROM CURRENT_DATE)::int AND 31
                        OR frequency_start_date::int BETWEEN 1 AND ((EXTRACT(DAY FROM CURRENT_DATE)::int + 10) - 31)
                    )
                )
            );
        ";

        //echo '<pre>';
        return $this->db->query($sql)->result_array();
        //exit;
    }

    public function getUnuploadedDocuments($documentList){ 
        //echo $documentList; exit;
        //to check which documents are uploaded and which are pending.
         $sql = "
            SELECT doc_id::int
            FROM unnest(string_to_array('$documentList', ',')) AS doc_id
            WHERE doc_id::int NOT IN (
                SELECT document_id FROM uploaded_documents where is_deleted=0
            );";

        //echo '<pre> Test ';
        return $this->db->query($sql)->result_array();
        //exit;
    }

     /**
     * Get documents grouped by Authority, Year, Month
     * Documents are sorted by the sequence defined in sub_type.document_name
     */
    public function getDocumentsZip($type_id = null, $company_id = null, $year = null, $month = null)   {
        
        $this->db->select("
            a.type_name AS authority,
            ud.type_id,
            ud.document_id,
            ud.document_year AS year,
            ud.document_month AS month,
            ud.file_name,
            d.document_name as doc_name,
            ud.is_deleted as del_doc,
            a.alias_name as doc_alias,
            a.document_name as doc_sequence
        ");
        $this->db->from('uploaded_documents ud');
        $this->db->join('sub_type a', 'a.id = ud.type_id', 'left');
        $this->db->join('documents d', 'd.id = ud.document_id', 'left');
        $this->db->where('ud.is_deleted', 0);
        
        // ✅ Apply filters
        if ($type_id) {
            $this->db->where('ud.type_id', $type_id);
        }
        if (!empty($company_id)) {
            $this->db->where('ud.company_id', $company_id);
        }
        if (!empty($year)) {
            $this->db->where('ud.document_year', $year);
        }
        if (!empty($month)) {
            $this->db->where('ud.document_month', $month);
        }

        // Order by authority, year, month first
        $this->db->order_by('a.type_name', 'ASC');
        $this->db->order_by('ud.document_year', 'DESC');
        $this->db->order_by('ud.document_month', 'ASC');

        $results = $this->db->get()->result();
        
        // Sort documents within each group by the sequence defined in sub_type.document_name
        $grouped = [];
        foreach ($results as $row) {
            $key = $row->authority . '_' . $row->year . '_' . $row->month;
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'sequence' => $row->doc_sequence ? array_map('trim', explode(',', $row->doc_sequence)) : [],
                    'docs' => []
                ];
            }
            $grouped[$key]['docs'][] = $row;
        }
        
        // Sort each group by document sequence
        $sorted_results = [];
        foreach ($grouped as $key => $group) {
            $sequence = $group['sequence'];
            $docs = $group['docs'];
            
            if (!empty($sequence)) {
                usort($docs, function($a, $b) use ($sequence) {
                    $posA = array_search((string)$a->document_id, $sequence);
                    $posB = array_search((string)$b->document_id, $sequence);
                    if ($posA === false) $posA = PHP_INT_MAX;
                    if ($posB === false) $posB = PHP_INT_MAX;
                    return $posA - $posB;
                });
            }
            
            foreach ($docs as $doc) {
                $sorted_results[] = $doc;
            }
        }
        
        return $sorted_results;
    }
}

?>