<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Session;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class BaseModel extends Model
{
    // Remove the static casts array - we'll make it dynamic
    // protected $casts = [...];

    /**
     * Get the casts array with automatically detected date columns
     */
    public function getCasts()
    {
        $casts = parent::getCasts();
        
        // Get table name
        $table = $this->getTable();
        
        // Create cache key for this table's date columns
        $cacheKey = "date_columns_{$table}";
        
                // Get cached date columns or detect them
        $dateColumns = Cache::remember($cacheKey, 3600, function() use ($table) {
            $dateColumns = [];
            
            try {
                // Get all columns for this table
                $columns = Schema::getColumnListing($table);
                
                foreach ($columns as $column) {
                    try {
                        // Get column type
                        $columnType = Schema::getColumnType($table, $column);
                        
                        // Check if it's a date/datetime/timestamp column
                        if (in_array(strtolower($columnType), ['date', 'datetime', 'timestamp'])) {
                            // Cast timestamps as datetime, others as date
                            if (in_array($column, ['created_at', 'updated_at', 'deleted_at'])) {
                                $dateColumns[$column] = 'datetime';
                            } else {
                                $dateColumns[$column] = 'date';
                            }
                        }
                    } catch (\Exception $e) {
                        // If Schema::getColumnType fails, use direct database query
                        try {
                            $result = DB::select("DESCRIBE {$table} {$column}");
                            if (!empty($result)) {
                                $dbColumnType = strtolower($result[0]->Type);
                                if (str_contains($dbColumnType, 'date') || str_contains($dbColumnType, 'timestamp')) {
                                    // Cast timestamps as datetime, others as date
                                    if (in_array($column, ['created_at', 'updated_at', 'deleted_at'])) {
                                        $dateColumns[$column] = 'datetime';
                                    } else {
                                        $dateColumns[$column] = 'date';
                                    }
                                }
                            }
                        } catch (\Exception $e2) {
                            // Ignore and continue
                        }
                    }
                    
                    // Use the centralized field classification system
                    $classification = $this->getFieldClassification();
                    
                    // If this is a string column that stores DATES ONLY, cast it as date
                    // BUT exclude the 'date' field to prevent automatic Carbon casting
                    // Also exclude issue_date and revision_date for incident_reports table since they are stored as strings
                    if (in_array($column, $classification['date_fields']) && !$this->isFieldExcludedFromFormatting($column) && $column !== 'date') {
                        // Special handling for incident_reports table where issue_date and revision_date are strings
                        if ($table === 'incident_reports' && in_array($column, ['issue_date', 'revision_date'])) {
                            // Don't cast these as dates for incident_reports table
                            continue;
                        }
                        
                        // Cast timestamps as datetime, others as date
                        if (in_array($column, ['created_at', 'updated_at', 'deleted_at'])) {
                            $dateColumns[$column] = 'datetime';
                        } else {
                            $dateColumns[$column] = 'date';
                        }
                    }
                }
            } catch (\Exception $e) {
                // If schema detection fails, return empty array
                // Log error silently and continue
            }
            
            return $dateColumns;
        });
        
        // Merge with parent casts and detected date columns
        return array_merge($casts, $dateColumns);
    }

    /**
     * Get the mutated attributes for a given instance.
     * We handle date formatting in getAttribute() instead of using traditional accessors
     */
    public function getMutatedAttributes()
    {
        // Just return parent's mutated attributes - we don't need to add our date columns here
        // since we handle date formatting in getAttribute() method directly
        return parent::getMutatedAttributes();
    }

    /**
     * Get the attributes that should be converted to dates.
     * This is crucial for Laravel's date handling
     */
    public function getDates()
    {
        $dates = parent::getDates();
        
        // Get dynamically detected date columns
        $casts = $this->getCasts();
        $dateColumns = [];
        
        foreach ($casts as $column => $type) {
            if ($type === 'date') {
                // Exclude the 'date' field from automatic Carbon casting
                // We'll handle it manually in getAttribute() method
                if ($column !== 'date') {
                    $dateColumns[] = $column;
                }
            }
        }
        
        return array_unique(array_merge($dates, $dateColumns));
    }

    /**
     * Get custom date fields for date formatting
     * Same as getDates but excludes Laravel's default timestamps
     */
    public function getCustomDateFields()
    {
        $allDates = $this->getDates();
        $defaultTimestamps = ['created_at', 'updated_at', 'deleted_at'];
        
        // Get custom date fields, excluding default timestamps
        $customDates = array_diff($allDates, $defaultTimestamps);
        
        // Special handling for incident_reports table where issue_date and revision_date are strings
        if ($this->getTable() === 'incident_reports') {
            $customDates = array_diff($customDates, ['issue_date', 'revision_date']);
        }
        
        // Manually add the 'date' field since we excluded it from automatic casting
        // to prevent Laravel from trying to parse it automatically
        // Include for all tables that have a 'date' field
        $tablesWithDateField = [
            'attendance_logs',
            'employee_attendances', 
            'employee_breaks',
            'fines',
            'overtimes',
            'incident_reports',
            'incident_final_classifications',
            'incident_injury_managements',
            'incident_signoffs',
            'incident_witness',
            'incident_notified_tos',
            'meeting_document_notes',
            'meeting_note_signatures',
            'inspection_plan_signatures',
            'safety_data_sheets',
            'swms_developed_bies',
            'swms_approved_bies',
            'porfile_submitted_logs',
            'whsq_reports',
            'whs_reports',
            'employee_signed_documents'
        ];
        
        if (in_array($this->getTable(), $tablesWithDateField)) {
            $customDates[] = 'date';
        }
        
        // Add referral system date fields for referral_payouts table
        if ($this->getTable() === 'referral_payouts') {
            $customDates[] = 'requested_at';
            $customDates[] = 'processed_at';
        }
        
        // Add referral system date fields for referral_commission_payouts table
        if ($this->getTable() === 'referral_commission_payouts') {
            $customDates[] = 'included_at';
            $customDates[] = 'processed_at';
        }
        
        return $customDates;
    }

    /**
     * Get custom time fields for time formatting
     */
    public function getCustomTimeFields()
    {
        $table = $this->getTable();
        $cacheKey = "time_columns_{$table}";
        
        // Get classification outside the closure
        $classification = $this->getFieldClassification();
        
        $timeColumns = Cache::remember($cacheKey, 3600, function() use ($table, $classification) {
            $timeColumns = [];
            
            try {
                // Get all columns for this table
                $columns = Schema::getColumnListing($table);
                
                foreach ($columns as $column) {
                    try {
                        // Get column type
                        $columnType = Schema::getColumnType($table, $column);
                        
                        // Check if it's a time column
                        if (in_array(strtolower($columnType), ['time'])) {
                            $timeColumns[] = $column;
                        }
                    } catch (\Exception $e) {
                        // If Schema::getColumnType fails, use direct database query
                        try {
                            $result = DB::select("DESCRIBE {$table} {$column}");
                            if (!empty($result)) {
                                $dbColumnType = strtolower($result[0]->Type);
                                if (str_contains($dbColumnType, 'time') && !str_contains($dbColumnType, 'datetime') && !str_contains($dbColumnType, 'timestamp')) {
                                    $timeColumns[] = $column;
                                }
                            }
                        } catch (\Exception $e2) {
                            // Ignore and continue
                        }
                    }
                    
                    // Use the centralized field classification system
                    // If this is a string column that stores TIMES ONLY, add it to time columns
                    if (in_array($column, $classification['time_fields']) && !$this->isFieldExcludedFromFormatting($column)) {
                        $timeColumns[] = $column;
                    }
                }
            } catch (\Exception $e) {
                // If schema detection fails, return empty array
            }
            
            return array_unique($timeColumns);
        });
        
        return $timeColumns;
    }

    /**
     * Comprehensive field classification system to prevent future errors
     * This method provides a centralized way to classify fields by their purpose
     */
    public function getFieldClassification()
    {
        return [
            // DATE-ONLY FIELDS (string columns that store only dates)
            'date_fields' => [
                // Employee Details
                'employment_start_date', 'employment_end_date', 'attendance_effective_from',
                'date_of_birth',
                
                // Incident Reports
                'issue_date', 'revision_date', 'date', 'incident_date', 'date_incident_reported',
                
                // Incident Related
                'return_to_duty_date', 'rtw_date', 'previous_injury_date',
                
                // SWMS
                'start_date', 'inductance_date', 'induction_date', 'recieved_date', 'date_recieved_by_reviewer',
                
                // Inspection Plans
                'partial_handover_date', 'complete_handover_date',
                
                // Meeting Documents
                'meeting_date',
                
                // Referral System
                'requested_at', 'processed_at', 'included_at',
                
                // Attendance Logs (note: 'string' field is actually a date field due to migration typo)
                'string'
            ],
            
            // TIME-ONLY FIELDS (string columns that store only times)
            'time_fields' => [
                // Incident Reports
                'incident_time', 'time_incident_reported',
                
                // Incident Related
                'shift_time',
                
                // Inspection Plans
                'partial_handover_time', 'complete_handover_time',
                
                // Meeting Documents
                'time_start', 'time_finish',
                
                // Generic time fields
                'time'
            ],
            
            // FILE/ATTACHMENT FIELDS (should NEVER be formatted as date/time)
            'file_fields' => [
                'signature', 'signatures', 'signature_type', 'signature_path',
                'file_path', 'image_path', 'document_path', 'upload_path',
                'attachment', 'file', 'image', 'document', 'upload',
                'photo', 'avatar', 'logo', 'banner', 'icon',
                'certificate', 'license', 'permit', 'contract',
                'invoice', 'receipt', 'report', 'form',
                'template', 'sample', 'example', 'backup'
            ],
            
            // ID/REFERENCE FIELDS (should NEVER be formatted as date/time)
            'id_fields' => [
                'id', 'uuid', 'reference', 'code', 'number',
                'tracking_id', 'order_id', 'invoice_id', 'receipt_id',
                'serial_number', 'batch_number', 'lot_number',
                'is_notified_to', 'is_approved_by', 'is_reviewed_by'
            ],
            
            // STATUS/FLAG FIELDS (should NEVER be formatted as date/time)
            'status_fields' => [
                'status', 'state', 'condition', 'type', 'category',
                'priority', 'level', 'grade', 'rating', 'score',
                'flag', 'mark', 'indicator', 'signal', 'alert'
            ]
        ];
    }

    /**
     * Check if a field should be excluded from date/time formatting
     */
    public function isFieldExcludedFromFormatting($fieldName)
    {
        $classification = $this->getFieldClassification();
        
        // Check if field is in any exclusion category
        $excludedCategories = ['file_fields', 'id_fields', 'status_fields'];
        
        foreach ($excludedCategories as $category) {
            if (in_array($fieldName, $classification[$category])) {
                return true;
            }
        }
        
        return false;
    }

    /**
     * Validate field classification to catch potential issues early
     */
    public function validateFieldClassification()
    {
        $classification = $this->getFieldClassification();
        $errors = [];
        
        // Check for duplicates across categories
        $allFields = [];
        foreach ($classification as $category => $fields) {
            foreach ($fields as $field) {
                if (in_array($field, $allFields)) {
                    $errors[] = "Duplicate field '{$field}' found in multiple categories";
                }
                $allFields[] = $field;
            }
        }
        
        // Check for conflicting field names
        $dateFields = $classification['date_fields'];
        $timeFields = $classification['time_fields'];
        $fileFields = $classification['file_fields'];
        
        $conflicts = array_intersect($dateFields, $timeFields);
        if (!empty($conflicts)) {
            $errors[] = "Conflicting fields found in both date and time categories: " . implode(', ', $conflicts);
        }
        
        $conflicts = array_intersect($dateFields, $fileFields);
        if (!empty($conflicts)) {
            $errors[] = "Conflicting fields found in both date and file categories: " . implode(', ', $conflicts);
        }
        
        $conflicts = array_intersect($timeFields, $fileFields);
        if (!empty($conflicts)) {
            $errors[] = "Conflicting fields found in both time and file categories: " . implode(', ', $conflicts);
        }
        
        return $errors;
    }

    /**
     * Static helper for safely parsing datetime strings (for use in controllers)
     */
    public static function safeCarbonParse($value, $context = '')
    {
        if (empty($value)) {
            return null;
        }

        // Fix malformed timezone offset before parsing
        if (is_string($value) && preg_match('/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}) ([+-]?)(\d{2}:\d{2})$/', $value, $matches)) {
            $dateTimePart = $matches[1];
            $sign = $matches[2];
            $timezoneOffset = $matches[3];
            
            // Add the '+' sign for positive timezone offsets if missing
            if (empty($sign)) {
                $sign = '+';
            }
            
            $fixedValue = $dateTimePart . $sign . $timezoneOffset;
            Log::info("Fixed malformed timezone offset in {$context}: '{$value}' -> '{$fixedValue}'");
            $value = $fixedValue;
        }

        // Fix double date specification (e.g., "2025-07-28 2025-07-28 10:00:00")
        if (is_string($value) && preg_match('/^(\d{4}-\d{2}-\d{2}) (\d{4}-\d{2}-\d{2}) (\d{2}:\d{2}:\d{2})$/', $value, $matches)) {
            $date = $matches[1];
            $time = $matches[3];
            $fixedValue = $date . ' ' . $time;
            Log::info("Fixed double date specification in {$context}: '{$value}' -> '{$fixedValue}'");
            $value = $fixedValue;
        }

        // Handle specific date formats that might cause issues
        if (is_string($value)) {
            $value = trim($value);

            // Try to resolve the user's preferred date format once
            $userFormat = null;
            try {
                $instance = new static();
                $userFormat = $instance->getUserDateFormat();
            } catch (\Exception $e) {
                // Ignore - we'll fall back to defaults
            }

            $userFormat = $userFormat ?: 'd-m-Y';

            // Handle NN-NN-NNNN formats (e.g., '11-28-2025')
            if (preg_match('/^\d{2}-\d{2}-\d{4}$/', $value)) {
                $formatsToTry = array_unique(array_filter([
                    str_replace('/', '-', $userFormat),
                    'd-m-Y',
                    'm-d-Y',
                    'Y-m-d',
                ]));

                foreach ($formatsToTry as $format) {
                    try {
                        return \Carbon\Carbon::createFromFormat($format, $value);
                    } catch (\Exception $e) {
                        // Try next format
                    }
                }

                Log::warning("Failed to parse hyphenated date in {$context}: '{$value}'", ['formats_tried' => $formatsToTry]);
            }

            // Handle NN/NN/NNNN formats (e.g., '07/27/2025' or '27/07/2025')
            if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $value)) {
                $formatsToTry = array_unique(array_filter([
                    str_replace('-', '/', $userFormat),
                    'm/d/Y',
                    'd/m/Y',
                    'Y/m/d',
                ]));

                foreach ($formatsToTry as $format) {
                    try {
                        return \Carbon\Carbon::createFromFormat($format, $value);
                    } catch (\Exception $e) {
                        // Try next format
                    }
                }

                Log::warning("Failed to parse slashed date in {$context}: '{$value}'", ['formats_tried' => $formatsToTry]);
            }
        }

        try {
            return \Carbon\Carbon::parse($value);
        } catch (\Exception $e) {
            Log::error("Failed to parse datetime in {$context}: '{$value}' - " . $e->getMessage());
            return $value; // Return original value instead of throwing exception
        }
    }

    /**
     * Enhanced error handling for date/time parsing with detailed logging
     */
    public function safeDateParse($value, $fieldName, $format = null)
    {
        if (empty($value)) {
            return $value;
        }

        // Skip parsing if this field should be excluded
        if ($this->isFieldExcludedFromFormatting($fieldName)) {
            return $value;
        }

        // Check if value looks like a file path (common false positive)
        if ($this->looksLikeFilePath($value)) {
            Log::warning("Attempted to parse file path as date/time: {$fieldName} = {$value}");
            return $value;
        }

        // Check if value looks like an ID or reference
        if ($this->looksLikeIdOrReference($value)) {
            Log::warning("Attempted to parse ID/reference as date/time: {$fieldName} = {$value}");
            return $value;
        }

        // Handle common date formats that might cause issues
        if (is_string($value)) {
            // Fix malformed timezone offset (e.g., "2025-07-23 00:00:00 08:00" -> "2025-07-23 00:00:00+08:00")
            if (preg_match('/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}) ([+-]?)(\d{2}:\d{2})$/', $value, $matches)) {
                $dateTimePart = $matches[1];
                $sign = $matches[2];
                $timezoneOffset = $matches[3];
                
                // Add the '+' sign for positive timezone offsets if missing
                if (empty($sign)) {
                    $sign = '+';
                }
                
                $value = $dateTimePart . $sign . $timezoneOffset;
                Log::info("Fixed malformed timezone offset for field '{$fieldName}': {$value}");
            }
            
            // Handle dd/mm/yy format (like '23/06/25')
            if (preg_match('/^\d{2}\/\d{2}\/\d{2}$/', $value)) {
                try {
                    // Convert to dd/mm/yyyy format by assuming 20xx for years
                    $parts = explode('/', $value);
                    $day = $parts[0];
                    $month = $parts[1];
                    $year = '20' . $parts[2]; // Assume 20xx for 2-digit years
                    $formattedValue = "{$day}/{$month}/{$year}";
                    return \Carbon\Carbon::createFromFormat('d/m/Y', $formattedValue);
                } catch (\Exception $e) {
                    Log::warning("Failed to parse dd/mm/yy format for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
            
            // Handle dd/mm/yyyy format
            if (preg_match('/^\d{2}\/\d{2}\/\d{4}$/', $value)) {
                try {
                    return \Carbon\Carbon::createFromFormat('d/m/Y', $value);
                } catch (\Exception $e) {
                    Log::warning("Failed to parse dd/mm/yyyy format for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
            
            // Handle XX-XX-YYYY format - use user's preferred date format to resolve ambiguity
            if (preg_match('/^\d{2}-\d{2}-\d{4}$/', $value)) {
                // Try user's preferred format first
                $userFormat = $this->getUserDateFormat();
                try {
                    return \Carbon\Carbon::createFromFormat($userFormat, $value);
                } catch (\Exception $e) {
                    // If user format fails, try common alternatives
                    $alternatives = ['d-m-Y', 'm-d-Y', 'Y-m-d'];
                    foreach ($alternatives as $format) {
                        if ($format !== $userFormat) {
                            try {
                                return \Carbon\Carbon::createFromFormat($format, $value);
                            } catch (\Exception $e2) {
                                // Continue to next format
                            }
                        }
                    }
                    Log::warning("Failed to parse date format {$userFormat} for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
            
            // Handle yyyy-mm-dd format
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) {
                try {
                    return \Carbon\Carbon::createFromFormat('Y-m-d', $value);
                } catch (\Exception $e) {
                    Log::warning("Failed to parse yyyy-mm-dd format for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
            
            // Handle mm-dd-yyyy hh:mm format
            if (preg_match('/^\d{2}-\d{2}-\d{4} \d{2}:\d{2}$/', $value)) {
                try {
                    return \Carbon\Carbon::createFromFormat('m-d-Y H:i', $value);
                } catch (\Exception $e) {
                    Log::warning("Failed to parse mm-dd-yyyy hh:mm format for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
            
            // Handle yyyy-mm-dd hh:mm:ss format
            if (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $value)) {
                try {
                    return \Carbon\Carbon::createFromFormat('Y-m-d H:i:s', $value);
                } catch (\Exception $e) {
                    Log::warning("Failed to parse yyyy-mm-dd hh:mm:ss format for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
        }

        try {
            if ($format) {
                return \Carbon\Carbon::createFromFormat($format, $value);
            } else {
                return \Carbon\Carbon::parse($value);
            }
        } catch (\Exception $e) {
            Log::error("Date/time parsing failed for field '{$fieldName}' with value '{$value}': " . $e->getMessage());
            return $value; // Return original value on failure
        }
    }

    /**
     * Check if a value looks like a file path
     */
    public function looksLikeFilePath($value)
    {
        if (!is_string($value)) {
            return false;
        }

        $filePathPatterns = [
            '/\.(png|jpg|jpeg|gif|pdf|doc|docx|xls|xlsx|txt|csv|zip|rar)$/i',
            '/^(upload|uploads|public|storage|files|documents|images|signatures?)\//i',
            '/^.*\/.*\.(png|jpg|jpeg|gif|pdf|doc|docx|xls|xlsx|txt|csv|zip|rar)$/i'
        ];

        foreach ($filePathPatterns as $pattern) {
            if (preg_match($pattern, $value)) {
                return true;
            }
        }

        return false;
    }

    /**
     * Check if a value looks like an ID or reference
     */
    public function looksLikeIdOrReference($value)
    {
        if (!is_string($value) && !is_numeric($value)) {
            return false;
        }

        $idPatterns = [
            '/^[A-Z]{2,4}-\d{4,8}$/i', // Format: ABC-12345
            '/^[A-Z]{2,4}\d{4,8}$/i',  // Format: ABC12345
            '/^\d{6,12}$/',            // Numeric IDs
            '/^[A-Z0-9]{8,16}$/i',     // Alphanumeric IDs
            '/^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$/i' // UUIDs
        ];

        foreach ($idPatterns as $pattern) {
            if (preg_match($pattern, $value)) {
                return true;
            }
        }

        return false;
    }

    /**
     * Enhanced field validation before formatting
     */
    protected function validateFieldBeforeFormatting($fieldName, $value)
    {
        // Skip validation for excluded fields
        if ($this->isFieldExcludedFromFormatting($fieldName)) {
            return true;
        }

        // Validate date fields
        $classification = $this->getFieldClassification();
        if (in_array($fieldName, $classification['date_fields'])) {
            return $this->validateDateField($fieldName, $value);
        }

        // Validate time fields
        if (in_array($fieldName, $classification['time_fields'])) {
            return $this->validateTimeField($fieldName, $value);
        }

        return true;
    }

    /**
     * Validate date field values
     */
    protected function validateDateField($fieldName, $value)
    {
        if (empty($value)) {
            return true;
        }

        // Check for obvious non-date values
        if ($this->looksLikeFilePath($value) || $this->looksLikeIdOrReference($value)) {
            Log::warning("Invalid date value detected in field '{$fieldName}': {$value}");
            return false;
        }

        return true;
    }

    /**
     * Validate time field values
     */
    protected function validateTimeField($fieldName, $value)
    {
        if (empty($value)) {
            return true;
        }

        // Check for obvious non-time values
        if ($this->looksLikeFilePath($value) || $this->looksLikeIdOrReference($value)) {
            Log::warning("Invalid time value detected in field '{$fieldName}': {$value}");
            return false;
        }

        return true;
    }

    /**
     * Get user preferred date format from settings
     * This reads from user's actual date format preferences
     */
    public function getUserDateFormat()
    {
        // Try to get from session first (set during login)
        $sessionFormat = Session::get('user_date_format');
        if ($sessionFormat) {
            return $sessionFormat;
        }

        // Fallback: Get from database if not in session
        try {
            $user = auth()->user();
            if (!$user) {
                return 'd-m-Y'; // Default if no user
            }

            // Determine customer_id and workspace_id based on user type
            if ($user->access_role) {
                // Employee user
                $customerId = $user->customer_id;
                $workspaceId = $user->workspace_id;
            } else {
                // Customer user
                $customerId = $user->id;
                $workspaceId = $user->current_workspace_id;
            }

            $setting = \App\Models\Adminsettings::where('customer_id', $customerId)
                ->where('key', 'system_date_format')
                ->when($workspaceId, function ($query) use ($workspaceId) {
                    return $query->where('workspace', $workspaceId);
                })
                ->first();

            if ($setting && $setting->value) {
                // Convert system format to Carbon format
                $formatMap = [
                    'mm-dd-yyyy' => 'm-d-Y',
                    'dd-mm-yyyy' => 'd-m-Y',
                    'yyyy-mm-dd' => 'Y-m-d',
                    'mm/dd/yyyy' => 'm/d/Y',
                    'dd/mm/yyyy' => 'd/m/Y',
                    'yyyy/mm/dd' => 'Y/m/d',
                ];
                $format = $formatMap[strtolower($setting->value)] ?? 'd-m-Y';
                
                // Store in session for future use
                Session::put('user_date_format', $format);
                return $format;
            }
        } catch (\Exception $e) {
            // Log error but continue with default
        }

        return 'd-m-Y'; // Default format
    }

    /**
     * Get user preferred time format from settings
     * This reads from user's actual time format preferences
     */
    public function getUserTimeFormat()
    {
        // Try to get from session first (set during login)
        $sessionFormat = Session::get('user_time_format');
        if ($sessionFormat) {
            return $sessionFormat;
        }

        // Fallback: Get from database if not in session
        try {
            $user = auth()->user();
            if (!$user) {
                return 'H:i'; // Default 24-hour format if no user
            }

            // Determine customer_id and workspace_id based on user type
            if ($user->access_role) {
                // Employee user
                $customerId = $user->customer_id;
                $workspaceId = $user->workspace_id;
            } else {
                // Customer user
                $customerId = $user->id;
                $workspaceId = $user->current_workspace_id;
            }

            $setting = \App\Models\Adminsettings::where('customer_id', $customerId)
                ->where('key', 'system_time_format')
                ->when($workspaceId, function ($query) use ($workspaceId) {
                    return $query->where('workspace', $workspaceId);
                })
                ->first();

            if ($setting && $setting->value) {
                // Convert system format to Carbon format
                $format = 'H:i'; // Default 24-hour
                
                // Check if the format contains AM/PM
                if (str_contains(strtoupper($setting->value), 'AM') || str_contains(strtoupper($setting->value), 'PM')) {
                    $format = 'g:i A'; // 12-hour format with AM/PM
                }
                
                // Store in session for future use
                Session::put('user_time_format', $format);
                return $format;
            }
        } catch (\Exception $e) {
            // Log error but continue with default
        }

        return 'H:i'; // Default 24-hour format
    }

    /**
     * Get user preferred combined date-time format from settings
     * This combines the date and time formats for timestamps like created_at and updated_at
     */
    public function getUserDateTimeFormat()
    {
        $dateFormat = $this->getUserDateFormat();
        $timeFormat = $this->getUserTimeFormat();
        
        return $dateFormat . ' ' . $timeFormat;
    }
    
    public function setAttribute($key, $value)
    {
        if (in_array($key, $this->getCustomDateFields()) && $value) {
            try {
                // Try d/m/y first (with two-digit year)
                $value = \Carbon\Carbon::createFromFormat('d/m/y', $value)->format('Y-m-d');
            } catch (\Exception $e) {
                try {
                    // Try d-m-Y (day-month-year with four-digit year)
                    $value = \Carbon\Carbon::createFromFormat('d-m-Y', $value)->format('Y-m-d');
                } catch (\Exception $e) {
                    try {
                        // Try m/d/Y (month/day/year)
                        $value = \Carbon\Carbon::createFromFormat('m/d/Y', $value)->format('Y-m-d');
                    } catch (\Exception $e) {
                        try {
                            // Try Y/m/d (year/month/day)
                            $value = \Carbon\Carbon::createFromFormat('Y/m/d', $value)->format('Y-m-d');
                        } catch (\Exception $e) {
                            try {
                                // Try Y-m-d (already correct format)
                                $value = \Carbon\Carbon::createFromFormat('Y-m-d', $value)->format('Y-m-d');
                            } catch (\Exception $e) {
                                try {
                                    // As a last resort, let Carbon try to parse it
                                    $value = \Carbon\Carbon::parse($value)->format('Y-m-d');
                                } catch (\Exception $e) {
                                    // Keep original if all fail
                                }
                            }
                        }
                    }
                }
            }
        }
        
        return parent::setAttribute($key, $value);
    }
    

    /**
     * Get dates (formatted according to user preference)
     */
    public function getAttribute($key)
    {
        $value = parent::getAttribute($key);

        // Format created_at and updated_at timestamps according to user preferences (date only)
        if (in_array($key, ['created_at', 'updated_at']) && $value) {
            try {
                // Get the raw timestamp value to avoid any timezone conversion issues
                $rawTimestamp = $this->getRawOriginal($key);
                if ($rawTimestamp) {
                    $carbonDate = \Carbon\Carbon::parse($rawTimestamp);
                    $userDateFormat = $this->getUserDateFormat();
                    return $carbonDate->format($userDateFormat);
                }
            } catch (\Exception $e) {
                Log::warning("Timestamp formatting failed for field '{$key}' with value '{$value}': " . $e->getMessage());
                // Fall through to return original value
            }
        }

        // Special handling for incident_reports table where issue_date and revision_date are strings but should be formatted as dates
        if ($this->getTable() === 'incident_reports' && in_array($key, ['issue_date', 'revision_date']) && $value) {
            // Parse the datetime string and format as date only
            $parsedDate = $this->safeDateParse($value, $key);
            if ($parsedDate instanceof \Carbon\Carbon) {
                try {
                    $userFormat = $this->getUserDateFormat();
                    return $parsedDate->format($userFormat);
                } catch (\Exception $e) {
                    Log::warning("Date formatting failed for field '{$key}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            } else {
                // If safeDateParse returned the original value, try to parse it manually
                try {
                    $carbonDate = \Carbon\Carbon::parse($value);
                    $userFormat = $this->getUserDateFormat();
                    return $carbonDate->format($userFormat);
                } catch (\Exception $e) {
                    Log::warning("Manual date parsing failed for field '{$key}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            }
        }

        // Only format if it's a custom date field (not timestamps like created_at, updated_at)
        $customDateFields = $this->getCustomDateFields();
        $customTimeFields = $this->getCustomTimeFields();
        
        // If this attribute is a custom date column and has a value, format it for output
        if (in_array($key, $customDateFields) && $value) {
            // Use the safe date parsing method with protection
            $parsedDate = $this->safeDateParse($value, $key);
            if ($parsedDate instanceof \Carbon\Carbon) {
                try {
                    $userFormat = $this->getUserDateFormat();
                    return $parsedDate->format($userFormat);
                } catch (\Exception $e) {
                    Log::warning("Date formatting failed for field '{$key}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            } else {
                // If safeDateParse returned the original value (not a Carbon instance), return it as is
                return $value;
            }
        }

        // If this attribute is a custom time column and has a value, format it for output
        if (in_array($key, $customTimeFields) && $value) {
            // Use the safe date parsing method with protection
            $parsedTime = $this->safeDateParse($value, $key);
            if ($parsedTime instanceof \Carbon\Carbon) {
                try {
                    $userTimeFormat = $this->getUserTimeFormat();
                    return $parsedTime->format($userTimeFormat);
                } catch (\Exception $e) {
                    Log::warning("Time formatting failed for field '{$key}' with value '{$value}': " . $e->getMessage());
                    return $value;
                }
            } else {
                // If safeDateParse returned the original value (not a Carbon instance), return it as is
                return $value;
            }
        }

        return $value;
    }

    /**
     * Serialize date fields for JSON output
     * Only format custom date fields, leave timestamps as default
     */
    public function serializeDate(\DateTimeInterface $date)
    {
        // Return standard format for timestamps (created_at, updated_at)
        // Ensure we preserve the exact time from the database without timezone conversion
        if ($date instanceof \Carbon\Carbon) {
            // Use the raw database value to avoid timezone conversion issues
            return $date->format('Y-m-d H:i:s');
        }
        return $date->format('Y-m-d H:i:s');
    }

    /**
     * Override toArray to handle custom date formatting
     */
    public function toArray()
    {
        $array = parent::toArray();
        
        // Get custom date and time fields
        $customDateFields = $this->getCustomDateFields();
        $customTimeFields = $this->getCustomTimeFields();
        $userDateFormat = $this->getUserDateFormat();
        $userTimeFormat = $this->getUserTimeFormat();
        $userDateTimeFormat = $this->getUserDateTimeFormat();
        
        // Format created_at and updated_at timestamps according to user preferences (date only)
        foreach (['created_at', 'updated_at'] as $timestampField) {
            if (isset($array[$timestampField]) && $array[$timestampField]) {
                try {
                    // Get the raw timestamp value to avoid any timezone conversion issues
                    $rawTimestamp = $this->getRawOriginal($timestampField);
                    if ($rawTimestamp) {
                        $carbonDate = \Carbon\Carbon::parse($rawTimestamp);
                        $array[$timestampField] = $carbonDate->format($userDateFormat);
                    }
                } catch (\Exception $e) {
                    Log::warning("Timestamp formatting failed in toArray for field '{$timestampField}' with value '{$array[$timestampField]}': " . $e->getMessage());
                    // Keep original value if parsing fails
                }
            }
        }
        
        // Special handling for incident_reports table where issue_date and revision_date are strings but should be formatted as dates
        if ($this->getTable() === 'incident_reports') {
            foreach (['issue_date', 'revision_date'] as $field) {
                if (isset($array[$field]) && $array[$field]) {
                    // Parse the datetime string and format as date only
                    $parsedDate = $this->safeDateParse($array[$field], $field);
                    if ($parsedDate instanceof \Carbon\Carbon) {
                        try {
                            $array[$field] = $parsedDate->format($userDateFormat);
                        } catch (\Exception $e) {
                            Log::warning("Date formatting failed in toArray for field '{$field}' with value '{$array[$field]}': " . $e->getMessage());
                            // Keep original value if parsing fails
                        }
                    } else {
                        // If safeDateParse returned the original value, try to parse it manually
                        try {
                            $carbonDate = \Carbon\Carbon::parse($array[$field]);
                            $array[$field] = $carbonDate->format($userDateFormat);
                        } catch (\Exception $e) {
                            Log::warning("Manual date parsing failed in toArray for field '{$field}' with value '{$array[$field]}': " . $e->getMessage());
                            // Keep original value if parsing fails
                        }
                    }
                }
            }
        }
        
        // Format date fields
        foreach ($customDateFields as $dateField) {
            if (isset($array[$dateField]) && $array[$dateField]) {
                // Use the safe date parsing method with protection
                $parsedDate = $this->safeDateParse($array[$dateField], $dateField);
                if ($parsedDate instanceof \Carbon\Carbon) {
                    try {
                        $array[$dateField] = $parsedDate->format($userDateFormat);
                    } catch (\Exception $e) {
                        Log::warning("Date formatting failed in toArray for field '{$dateField}' with value '{$array[$dateField]}': " . $e->getMessage());
                        // Keep original value if parsing fails
                    }
                }
                // If safeDateParse returned the original value, keep it as is
            }
        }

        // Format time fields
        foreach ($customTimeFields as $timeField) {
            if (isset($array[$timeField]) && $array[$timeField]) {
                // Use the safe date parsing method with protection
                $parsedTime = $this->safeDateParse($array[$timeField], $timeField);
                if ($parsedTime instanceof \Carbon\Carbon) {
                    try {
                        $array[$timeField] = $parsedTime->format($userTimeFormat);
                    } catch (\Exception $e) {
                        Log::warning("Time formatting failed in toArray for field '{$timeField}' with value '{$array[$timeField]}': " . $e->getMessage());
                        // Keep original value if parsing fails
                    }
                }
                // If safeDateParse returned the original value, keep it as is
            }
        }

        return $array;
    }


    public static function generateServerDate()
    {
        return now()->format('Y-m-d');
    }

    /**
     * Generate server time in H:i:s format (for database queries)
     * This ensures consistent time format regardless of user preferences
     */
    public static function generateServerTime()
    {
        return now()->format('H:i:s');
    }

    /**
     * Generate server datetime in Y-m-d H:i:s format (for database queries)
     * This ensures consistent datetime format regardless of user preferences
     */
    public static function generateServerDateTime()
    {
        return now()->format('Y-m-d H:i:s');
    }

    /**
     * Generate server date in Y-m-d format (for database queries)
     * This ensures consistent date format regardless of user preferences
     */
    public static function clearDateColumnsCache($table = null)
    {
        if ($table) {
            Cache::forget("date_columns_{$table}");
            Cache::forget("time_columns_{$table}");
        } else {
            // Clear cache for all tables (you might want to implement this based on your cache strategy)
            Cache::flush(); // This clears all cache - use carefully
        }
    }
}
