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

class DocumentGroup_model extends CI_Model {

    private $table_ready = false;

    public function __construct() {
        parent::__construct();
        $this->_ensureChildMandatoryTable();
    }

    /**
     * Ensure the child_document_mandatory table exists
     */
    private function _ensureChildMandatoryTable() {
        try {
            // Check if table exists using a simpler approach
            $result = @$this->db->query("SELECT 1 FROM child_document_mandatory LIMIT 1");
            
            if ($result === false) {
                // Table doesn't exist, create it
                $this->_createChildMandatoryTable();
            }
            
            $this->table_ready = true;
        } catch (Exception $e) {
            log_message('error', 'Error checking child_document_mandatory table: ' . $e->getMessage());
            // Try to create the table anyway
            try {
                $this->_createChildMandatoryTable();
                $this->table_ready = true;
            } catch (Exception $e2) {
                log_message('error', 'Error creating child_document_mandatory table: ' . $e2->getMessage());
                $this->table_ready = false;
            }
        }
    }

    /**
     * Create the child_document_mandatory table
     */
    private function _createChildMandatoryTable() {
        try {
            $sql = "
                CREATE TABLE IF NOT EXISTS child_document_mandatory (
                    id SERIAL PRIMARY KEY,
                    parent_document_id INTEGER NOT NULL,
                    child_document_id INTEGER NOT NULL,
                    is_mandatory INTEGER DEFAULT 0,
                    company_id INTEGER NOT NULL,
                    type_id INTEGER NOT NULL,
                    document_month VARCHAR(2) NOT NULL,
                    document_year VARCHAR(4) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(parent_document_id, child_document_id, company_id, type_id, document_month, document_year)
                )
            ";
            $this->db->query($sql);
            $this->table_ready = true;
        } catch (Exception $e) {
            log_message('error', 'Error creating child_document_mandatory table: ' . $e->getMessage());
        }
    }
    
    /**
     * Check if table is ready for use
     */
    public function isTableReady() {
        return $this->table_ready;
    }

    /**
     * Get all document groups with their child documents
     */
    public function getAllGroups() {
        return $this->db->select('document_id, grouped_doc_id')
                        ->get('document_groups')
                        ->result_array();
    }

    /**
     * Get child documents for a parent document
     * @param int $parent_document_id
     * @return array
     */
    public function getChildDocuments($parent_document_id) {
        $group = $this->db->where('document_id', $parent_document_id)
                          ->get('document_groups')
                          ->row_array();
        
        if (!$group || empty($group['grouped_doc_id'])) {
            return [];
        }

        $child_ids = array_map('trim', explode(',', $group['grouped_doc_id']));
        $child_ids = array_filter($child_ids); // Remove empty values
        
        if (empty($child_ids)) {
            return [];
        }

        // Get document names for the child IDs
        $children = $this->db->where_in('id', $child_ids)
                             ->get('documents')
                             ->result_array();
        
        return $children;
    }

    /**
     * Check if a document is a parent document (has child documents)
     * @param int $document_id
     * @return bool
     */
    public function isParentDocument($document_id) {
        $group = $this->db->where('document_id', $document_id)
                          ->get('document_groups')
                          ->row_array();
        
        return !empty($group) && !empty($group['grouped_doc_id']);
    }

    /**
     * Check if a document is a child document (part of a group)
     * @param int $document_id
     * @return array|false Returns parent info or false
     */
    public function isChildDocument($document_id) {
        $groups = $this->db->get('document_groups')->result_array();
        
        foreach ($groups as $group) {
            $child_ids = array_map('trim', explode(',', $group['grouped_doc_id']));
            if (in_array($document_id, $child_ids)) {
                // Get parent document details
                $parent = $this->db->where('id', $group['document_id'])
                                   ->get('documents')
                                   ->row_array();
                return [
                    'parent_id' => $group['document_id'],
                    'parent_name' => $parent['document_name'] ?? '',
                    'group_id' => $group['id']
                ];
            }
        }
        
        return false;
    }

    /**
     * Check if parent document is uploaded for a specific period
     * @param int $parent_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return bool
     */
    public function isParentUploaded($parent_document_id, $company_id, $type_id, $month, $year) {
        $result = $this->db->where('document_id', $parent_document_id)
                           ->where('company_id', $company_id)
                           ->where('type_id', $type_id)
                           ->where('document_month', $month)
                           ->where('document_year', $year)
                           ->where('is_deleted', 0)
                           ->get('uploaded_documents')
                           ->row_array();
        
        return !empty($result);
    }

    /**
     * Save child document mandatory status
     * @param int $parent_document_id
     * @param array $child_mandatory Array of child_id => is_mandatory (0 or 1)
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return bool
     */
    public function saveChildMandatoryStatus($parent_document_id, $child_mandatory, $company_id, $type_id, $month, $year) {
        try {
            // Ensure table exists
            if (!$this->table_ready) {
                $this->_ensureChildMandatoryTable();
            }
            
            if (!$this->table_ready) {
                log_message('error', 'child_document_mandatory table is not ready');
                return false;
            }
            
            log_message('debug', "Saving mandatory status: parent=$parent_document_id, type=$type_id, month=$month, year=$year, children=" . json_encode($child_mandatory));
            
            // Delete existing records for this combination
            $this->db->where('parent_document_id', $parent_document_id)
                     ->where('company_id', $company_id)
                     ->where('type_id', $type_id)
                     ->where('document_month', $month)
                     ->where('document_year', $year)
                     ->delete('child_document_mandatory');

            // Insert new records
            $inserted = 0;
            foreach ($child_mandatory as $child_id => $is_mandatory) {
                $data = [
                    'parent_document_id' => $parent_document_id,
                    'child_document_id' => $child_id,
                    'is_mandatory' => $is_mandatory ? 1 : 0,
                    'company_id' => $company_id,
                    'type_id' => $type_id,
                    'document_month' => $month,
                    'document_year' => $year,
                    'created_at' => date('Y-m-d H:i:s'),
                    'updated_at' => date('Y-m-d H:i:s')
                ];
                $this->db->insert('child_document_mandatory', $data);
                $inserted++;
            }
            
            log_message('debug', "Inserted $inserted mandatory status records");
            
            return true;
        } catch (Exception $e) {
            log_message('error', 'Error saving child mandatory status: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Get child document mandatory status for a parent document
     * @param int $parent_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return array
     */
    public function getChildMandatoryStatus($parent_document_id, $company_id, $type_id, $month, $year) {
        try {
            if (!$this->table_ready) {
                return [];
            }
            
            $results = $this->db->where('parent_document_id', $parent_document_id)
                                ->where('company_id', $company_id)
                                ->where('type_id', $type_id)
                                ->where('document_month', $month)
                                ->where('document_year', $year)
                                ->get('child_document_mandatory')
                                ->result_array();
            
            $status = [];
            foreach ($results as $row) {
                $status[$row['child_document_id']] = (int)$row['is_mandatory'];
            }
            
            return $status;
        } catch (Exception $e) {
            log_message('error', 'Error getting child mandatory status: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * Check if child document is mandatory for a specific parent upload
     * @param int $child_document_id
     * @param int $parent_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return bool|null Returns null if not set, true if mandatory, false if optional
     */
    public function isChildMandatory($child_document_id, $parent_document_id, $company_id, $type_id, $month, $year) {
        try {
            if (!$this->table_ready) {
                return null;
            }
            
            $result = $this->db->where('child_document_id', $child_document_id)
                               ->where('parent_document_id', $parent_document_id)
                               ->where('company_id', $company_id)
                               ->where('type_id', $type_id)
                               ->where('document_month', $month)
                               ->where('document_year', $year)
                               ->get('child_document_mandatory')
                               ->row_array();
            
            if (empty($result)) {
                return null; // Not set yet
            }
            
            return (int)$result['is_mandatory'] === 1;
        } catch (Exception $e) {
            log_message('error', 'Error checking if child is mandatory: ' . $e->getMessage());
            return null;
        }
    }

    /**
     * Get all mandatory child documents for dashboard/reports
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return array
     */
    public function getMandatoryChildDocuments($company_id, $type_id, $month, $year) {
        try {
            if (!$this->table_ready) {
                return [];
            }
            
            return $this->db->select('child_document_id, parent_document_id')
                            ->where('company_id', $company_id)
                            ->where('type_id', $type_id)
                            ->where('document_month', $month)
                            ->where('document_year', $year)
                            ->where('is_mandatory', 1)
                            ->get('child_document_mandatory')
                            ->result_array();
        } catch (Exception $e) {
            log_message('error', 'Error getting mandatory child documents: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * Get detailed child documents with mandatory status and upload info
     * @param int $parent_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return array
     */
    public function getChildDocumentsWithStatus($parent_document_id, $company_id, $type_id, $month, $year) {
        // Get child document IDs from the group
        $children = $this->getChildDocuments($parent_document_id);
        
        if (empty($children)) {
            return [];
        }

        // Get mandatory status
        $mandatoryStatus = $this->getChildMandatoryStatus($parent_document_id, $company_id, $type_id, $month, $year);
        
        // Get upload status for each child
        $child_ids = array_column($children, 'id');
        $uploaded = $this->db->select('document_id, file_name, file_path')
                             ->where_in('document_id', $child_ids)
                             ->where('company_id', $company_id)
                             ->where('type_id', $type_id)
                             ->where('document_month', $month)
                             ->where('document_year', $year)
                             ->where('is_deleted', 0)
                             ->get('uploaded_documents')
                             ->result_array();
        
        $uploadedMap = [];
        foreach ($uploaded as $u) {
            $uploadedMap[$u['document_id']] = $u;
        }

        // Build result
        $result = [];
        foreach ($children as $child) {
            $result[] = [
                'id' => $child['id'],
                'document_name' => $child['document_name'],
                'is_mandatory' => $mandatoryStatus[$child['id']] ?? null, // null = not set yet
                'is_uploaded' => isset($uploadedMap[$child['id']]),
                'file_name' => $uploadedMap[$child['id']]['file_name'] ?? null,
                'file_path' => $uploadedMap[$child['id']]['file_path'] ?? null
            ];
        }

        return $result;
    }

    /**
     * Check if mandatory status has been set for a parent document upload
     * @param int $parent_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return bool
     */
    public function hasMandatoryStatusSet($parent_document_id, $company_id, $type_id, $month, $year) {
        try {
            if (!$this->table_ready) {
                return false;
            }
            
            $count = $this->db->where('parent_document_id', $parent_document_id)
                              ->where('company_id', $company_id)
                              ->where('type_id', $type_id)
                              ->where('document_month', $month)
                              ->where('document_year', $year)
                              ->count_all_results('child_document_mandatory');
            
            return $count > 0;
        } catch (Exception $e) {
            log_message('error', 'Error checking mandatory status: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Check if a master document can be deleted (has no uploaded sub documents)
     * @param int $document_id - The document_id from uploaded_documents
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return array ['can_delete' => bool, 'reason' => string, 'sub_count' => int]
     */
    public function canDeleteMasterDocument($document_id, $company_id, $type_id, $month, $year) {
        try {
            // Check if this document is a parent/master
            if (!$this->isParentDocument($document_id)) {
                return ['can_delete' => true, 'reason' => '', 'sub_count' => 0];
            }

            // Get child document IDs
            $children = $this->getChildDocuments($document_id);
            if (empty($children)) {
                return ['can_delete' => true, 'reason' => '', 'sub_count' => 0];
            }

            $child_ids = array_column($children, 'id');

            // Check if any child documents are uploaded
            $uploadedCount = $this->db->where_in('document_id', $child_ids)
                                      ->where('company_id', $company_id)
                                      ->where('type_id', $type_id)
                                      ->where('document_month', $month)
                                      ->where('document_year', $year)
                                      ->where('is_deleted', 0)
                                      ->count_all_results('uploaded_documents');

            if ($uploadedCount > 0) {
                return [
                    'can_delete' => false, 
                    'reason' => "Cannot delete master document. $uploadedCount sub document(s) are still present. Please delete sub documents first.",
                    'sub_count' => $uploadedCount
                ];
            }

            return ['can_delete' => true, 'reason' => '', 'sub_count' => 0];
        } catch (Exception $e) {
            log_message('error', 'Error checking if master can be deleted: ' . $e->getMessage());
            return ['can_delete' => true, 'reason' => '', 'sub_count' => 0]; // Allow deletion on error
        }
    }

    /**
     * Clean up child_document_mandatory when a sub document is deleted
     * @param int $child_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return bool
     */
    public function cleanupOnSubDocumentDelete($child_document_id, $company_id, $type_id, $month, $year) {
        try {
            if (!$this->table_ready) {
                return true; // No table, nothing to clean
            }

            $this->db->where('child_document_id', $child_document_id)
                     ->where('company_id', $company_id)
                     ->where('type_id', $type_id)
                     ->where('document_month', $month)
                     ->where('document_year', $year)
                     ->delete('child_document_mandatory');

            log_message('debug', "Cleaned up child_document_mandatory for sub doc: $child_document_id");
            return true;
        } catch (Exception $e) {
            log_message('error', 'Error cleaning up on sub document delete: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Clean up child_document_mandatory when a master document is deleted
     * @param int $parent_document_id
     * @param int $company_id
     * @param int $type_id
     * @param string $month
     * @param string $year
     * @return bool
     */
    public function cleanupOnMasterDocumentDelete($parent_document_id, $company_id, $type_id, $month, $year) {
        try {
            if (!$this->table_ready) {
                return true; // No table, nothing to clean
            }

            $this->db->where('parent_document_id', $parent_document_id)
                     ->where('company_id', $company_id)
                     ->where('type_id', $type_id)
                     ->where('document_month', $month)
                     ->where('document_year', $year)
                     ->delete('child_document_mandatory');

            log_message('debug', "Cleaned up child_document_mandatory for master doc: $parent_document_id");
            return true;
        } catch (Exception $e) {
            log_message('error', 'Error cleaning up on master document delete: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Get document role info (master, sub, or neither)
     * @param int $document_id
     * @return array ['is_master' => bool, 'is_sub' => bool, 'parent_id' => int|null, 'child_ids' => array]
     */
    public function getDocumentRole($document_id) {
        $result = [
            'is_master' => false,
            'is_sub' => false,
            'parent_id' => null,
            'parent_name' => null,
            'child_ids' => []
        ];

        // Check if it's a master (parent)
        if ($this->isParentDocument($document_id)) {
            $result['is_master'] = true;
            $children = $this->getChildDocuments($document_id);
            $result['child_ids'] = array_column($children, 'id');
        }

        // Check if it's a sub (child)
        $parentInfo = $this->isChildDocument($document_id);
        if ($parentInfo !== false) {
            $result['is_sub'] = true;
            $result['parent_id'] = $parentInfo['parent_id'];
            $result['parent_name'] = $parentInfo['parent_name'];
        }

        return $result;
    }
}

