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

class Holiday_model extends CI_Model {

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

    /**
     * Ensure holidays table exists with correct structure
     */
    private function _ensureHolidaysTable()
    {
        try {
            $debug = $this->db->db_debug;
            $this->db->db_debug = FALSE;
            
            $tableExists = $this->db->query("SELECT 1 FROM holidays LIMIT 1");
            
            if ($tableExists === FALSE) {
                // Table doesn't exist, create it
                $this->_createHolidaysTable();
            } else {
                // Table exists, check if it has the correct column (holiday_year instead of year)
                $columnCheck = $this->db->query("SELECT holiday_year FROM holidays LIMIT 1");
                if ($columnCheck === FALSE) {
                    // Column doesn't exist, drop and recreate table
                    $this->db->query("DROP TABLE IF EXISTS holidays");
                    $this->_createHolidaysTable();
                }
            }
            
            $this->db->db_debug = $debug;
        } catch (Exception $e) {
            // If table check fails, log and continue
            log_message('error', 'Holiday table check failed: ' . $e->getMessage());
        }
    }

    /**
     * Create the holidays table
     */
    private function _createHolidaysTable()
    {
        // Create table - use holiday_year instead of year (reserved word in PostgreSQL)
        $this->db->query("
            CREATE TABLE holidays (
                id SERIAL PRIMARY KEY,
                holiday_date DATE NOT NULL,
                holiday_name VARCHAR(255) NOT NULL,
                holiday_year INTEGER NOT NULL,
                country VARCHAR(100) DEFAULT 'India',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                is_deleted INTEGER DEFAULT 0
            )
        ");
        $this->db->query("CREATE INDEX IF NOT EXISTS idx_holiday_date ON holidays(holiday_date)");
        $this->db->query("CREATE INDEX IF NOT EXISTS idx_holiday_year ON holidays(holiday_year)");
        $this->db->query("CREATE INDEX IF NOT EXISTS idx_holiday_country ON holidays(country)");
    }
    
    /**
     * Ensure country column exists (for existing tables)
     */
    public function ensureCountryColumn()
    {
        try {
            $debug = $this->db->db_debug;
            $this->db->db_debug = FALSE;
            
            $columnCheck = $this->db->query("SELECT country FROM holidays LIMIT 1");
            if ($columnCheck === FALSE) {
                // Column doesn't exist, add it
                $this->db->query("ALTER TABLE holidays ADD COLUMN country VARCHAR(100) DEFAULT 'India'");
                $this->db->query("CREATE INDEX IF NOT EXISTS idx_holiday_country ON holidays(country)");
            }
            
            $this->db->db_debug = $debug;
        } catch (Exception $e) {
            log_message('error', 'Holiday country column check failed: ' . $e->getMessage());
        }
    }

    /**
     * Get all holidays for a year (optionally filtered by country)
     */
    public function getHolidaysByYear($year, $country = null)
    {
        $this->db->select('*')
            ->from('holidays')
            ->where('holiday_year', $year)
            ->where('is_deleted', 0);
        
        if (!empty($country)) {
            $this->db->where('country', $country);
        }
        
        return $this->db->order_by('holiday_date', 'ASC')
            ->get()
            ->result_array();
    }
    
    /**
     * Get all holidays for a year by company ID (uses company's country)
     */
    public function getHolidaysByYearAndCompany($year, $company_id)
    {
        // Get company's country
        $company = $this->db->select('country')->where('id', $company_id)->get('company')->row();
        $country = $company ? $company->country : null;
        
        return $this->getHolidaysByYear($year, $country);
    }

    /**
     * Get all holidays (with optional year filter)
     */
    public function getAllHolidays($year = null)
    {
        $this->db->select('*');
        $this->db->from('holidays');
        $this->db->where('is_deleted', 0);
        
        if ($year) {
            $this->db->where('holiday_year', $year);
        }
        
        $this->db->order_by('holiday_date', 'DESC');
        return $this->db->get()->result_array();
    }

    /**
     * Add a single holiday
     */
    public function addHoliday($date, $name, $country = 'India')
    {
        $year = date('Y', strtotime($date));
        
        // Check if already exists for this country
        $exists = $this->db->where([
            'holiday_date' => $date,
            'country' => $country,
            'is_deleted' => 0
        ])->get('holidays')->row();
        
        if ($exists) {
            return ['success' => false, 'message' => 'Holiday already exists for this date and country.'];
        }
        
        $this->db->insert('holidays', [
            'holiday_date' => $date,
            'holiday_name' => $name,
            'holiday_year' => $year,
            'country' => $country,
            'created_at' => date('Y-m-d H:i:s')
        ]);
        
        return ['success' => true, 'message' => 'Holiday added successfully.'];
    }

    /**
     * Delete a holiday
     */
    public function deleteHoliday($id)
    {
        $this->db->where('id', $id)->update('holidays', ['is_deleted' => 1]);
        return ['success' => true, 'message' => 'Holiday deleted.'];
    }

    /**
     * Import holidays from CSV/Excel data
     */
    public function importHolidays($holidays, $year, $country = 'India')
    {
        $added = 0;
        $skipped = 0;
        
        foreach ($holidays as $holiday) {
            $date = isset($holiday['date']) ? $holiday['date'] : null;
            $name = isset($holiday['name']) ? $holiday['name'] : '';
            
            if (!$date) continue;
            
            // Check if already exists for this country
            $exists = $this->db->where([
                'holiday_date' => $date,
                'country' => $country,
                'is_deleted' => 0
            ])->get('holidays')->row();
            
            if ($exists) {
                $skipped++;
                continue;
            }
            
            $this->db->insert('holidays', [
                'holiday_date' => $date,
                'holiday_name' => $name,
                'holiday_year' => $year,
                'country' => $country,
                'created_at' => date('Y-m-d H:i:s')
            ]);
            $added++;
        }
        
        return ['success' => true, 'added' => $added, 'skipped' => $skipped];
    }

    /**
     * Check if a specific date is a holiday (optionally for a specific country)
     */
    public function isHoliday($date, $country = null)
    {
        try {
            $this->db->from('holidays')
                ->where('holiday_date', $date)
                ->where('is_deleted', 0);
            
            if (!empty($country)) {
                $this->db->where('country', $country);
            }
            
            $count = $this->db->count_all_results();
            
            return $count > 0;
        } catch (Exception $e) {
            // If check fails, assume not a holiday
            return false;
        }
    }
    
    /**
     * Get list of unique countries from holidays
     */
    public function getCountries()
    {
        return $this->db->select('DISTINCT country')
            ->from('holidays')
            ->where('is_deleted', 0)
            ->where('country IS NOT NULL', null, false)
            ->order_by('country', 'ASC')
            ->get()
            ->result_array();
    }

    /**
     * Check if a date is Sunday
     */
    public function isSunday($date)
    {
        return date('w', strtotime($date)) == 0;
    }

    /**
     * Get the last valid day of a month
     * Handles cases like Feb 30, Apr 31, etc.
     */
    public function getLastDayOfMonth($month, $year)
    {
        return (int)date('t', mktime(0, 0, 0, $month, 1, $year));
    }

    /**
     * Calculate adjusted due date considering:
     * 1. If due day > last day of month, use last day
     * 2. If due date falls on Sunday, move to day before
     * 3. If due date falls on holiday, move to day before
     * 4. Repeat check for Sunday/holiday on the adjusted date
     * 
     * @param int $dueDay The original due day (1-31)
     * @param int $month The month (1-12)
     * @param int $year The year
     * @return array ['day' => adjusted_day, 'date' => 'Y-m-d', 'adjusted' => bool, 'reason' => string]
     */
    public function getAdjustedDueDate($dueDay, $month, $year)
    {
        try {
            // Step 1: Adjust for month length (natural adjustment, no badge needed)
            $lastDayOfMonth = $this->getLastDayOfMonth($month, $year);
            $adjustedDay = min($dueDay, $lastDayOfMonth);
            
            // Track the day after month-length adjustment (used to check if Sunday/Holiday caused change)
            $dayAfterMonthAdjust = $adjustedDay;
            $reasons = [];
            
            // Note: We DON'T add reason for month-length adjustment anymore
            // (per user request - only show badge for Sunday/Holiday)
            
            // Step 2: Check for Sunday and holidays (keep moving back until valid)
            $maxIterations = 10; // Prevent infinite loop
            $iterations = 0;
            
            while ($iterations < $maxIterations) {
                $dateStr = sprintf('%04d-%02d-%02d', $year, $month, $adjustedDay);
                $needsAdjustment = false;
                
                // Check if Sunday
                if ($this->isSunday($dateStr)) {
                    $reasons[] = "Falls on Sunday";
                    $needsAdjustment = true;
                }
                // Check if holiday
                elseif ($this->isHoliday($dateStr)) {
                    try {
                        $holiday = $this->db->get_where('holidays', [
                            'holiday_date' => $dateStr,
                            'is_deleted' => 0
                        ])->row();
                        $holidayName = $holiday ? $holiday->holiday_name : 'Holiday';
                    } catch (Exception $e) {
                        $holidayName = 'Holiday';
                    }
                    $reasons[] = "Falls on $holidayName";
                    $needsAdjustment = true;
                }
                
                if ($needsAdjustment) {
                    $adjustedDay--;
                    
                    // If we go to previous month, stop at day 1
                    if ($adjustedDay < 1) {
                        $adjustedDay = 1;
                        break;
                    }
                } else {
                    break;
                }
                
                $iterations++;
            }
            
            $finalDateStr = sprintf('%04d-%02d-%02d', $year, $month, $adjustedDay);
            
            // Only mark as adjusted (show badge) if it was adjusted due to Sunday/Holiday
            // NOT for natural month-length adjustments (like 31 -> 30 in April)
            $wasAdjustedForHolidayOrSunday = ($adjustedDay != $dayAfterMonthAdjust);
            
            return [
                'original_day' => $dueDay,
                'day' => $adjustedDay,
                'date' => $finalDateStr,
                'formatted' => date('j M Y', strtotime($finalDateStr)),
                'adjusted' => $wasAdjustedForHolidayOrSunday,
                'reason' => implode(', ', array_unique($reasons))
            ];
        } catch (Exception $e) {
            // Fallback - return unadjusted date (no badge since we can't check Sunday/Holiday)
            $lastDay = (int)date('t', mktime(0, 0, 0, $month, 1, $year));
            $day = min($dueDay, $lastDay);
            $dateStr = sprintf('%04d-%02d-%02d', $year, $month, $day);
            return [
                'original_day' => $dueDay,
                'day' => $day,
                'date' => $dateStr,
                'formatted' => date('j M Y', strtotime($dateStr)),
                'adjusted' => false,  // No badge in fallback - only show for confirmed Sunday/Holiday
                'reason' => ''
            ];
        }
    }

    /**
     * Get adjusted due date for display (formatted string)
     */
    public function getAdjustedDueDateFormatted($dueDay, $month, $year)
    {
        $result = $this->getAdjustedDueDate($dueDay, $month, $year);
        
        if ($result['adjusted']) {
            return $result['formatted'] . ' <small class="text-muted">(Adjusted: ' . $result['reason'] . ')</small>';
        }
        
        return $result['formatted'];
    }

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

    /**
     * Check if today is the due date (or before, for pending)
     */
    public function isDueToday($dueDay, $month, $year)
    {
        $adjustedDue = $this->getAdjustedDueDate($dueDay, $month, $year);
        $today = date('Y-m-d');
        
        return $today == $adjustedDue['date'];
    }

    /**
     * Clear all holidays for a year
     */
    public function clearHolidaysForYear($year)
    {
        $this->db->where('holiday_year', $year)->update('holidays', ['is_deleted' => 1]);
        return ['success' => true];
    }
}
