<?php
/**
 * CLAWS Database Class
 * Handles all database operations for colonies, intakes, discharges, and neonate care
 */

if (!defined('ABSPATH')) {
    exit;
}

class CLAWS_Database {
    
    private static $instance = null;
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    private function __construct() {
        // Initialize database operations
    }
    
    /**
     * Create custom tables for CLAWS data
     */
    public static function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Feeding records table for neonate care
        $table_name = $wpdb->prefix . 'claws_feeding_records';
        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            kitten_name varchar(100) NOT NULL,
            feeding_date date NOT NULL,
            feeding_time time NOT NULL,
            pre_weight decimal(6,2) DEFAULT NULL,
            post_weight decimal(6,2) DEFAULT NULL,
            notes text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY user_id (user_id),
            KEY feeding_date (feeding_date)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        
        // Colony history will use custom post type instead of custom table
        
        claws_log('Database tables created successfully');
    }
    
    /**
     * Remove unused columns from feeding records table
     */
    public static function migrate_feeding_records_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'claws_feeding_records';
        
        // Check if table exists first
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name));
        if (!$table_exists) {
            claws_log('Feeding records table does not exist, skipping migration');
            return;
        }
        
        // Check if columns exist before trying to drop them
        $columns_to_remove = ['urination', 'defecation', 'age_range'];
        
        foreach ($columns_to_remove as $column) {
            $column_exists = $wpdb->get_results($wpdb->prepare(
                "SHOW COLUMNS FROM {$table_name} LIKE %s", 
                $column
            ));
            
            if ($column_exists) {
                $result = $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN {$column}");
                if ($result === false) {
                    claws_log("Failed to drop column {$column} from feeding records table: " . $wpdb->last_error, 'error');
                } else {
                    claws_log("Successfully dropped column {$column} from feeding records table");
                }
            }
        }
        
        claws_log('Feeding records table migration completed');
    }
    
    /**
     * Get colonies based on user role permissions
     * Contributors: Only own colonies
     * Authors: All colonies 
     * Admins: All colonies
     */
    public function get_colonies($user_id = null) {
        // Get current user if not provided
        if ($user_id === null) {
            $user_id = get_current_user_id();
        }
        
        // Simple approach: If user can't edit others' posts, only show their own
        $query_args = array(
            'post_type' => 'claws_colony',
            'numberposts' => 100,
            'post_status' => 'publish',
            'orderby' => 'date',
            'order' => 'DESC'
        );
        
        // Contributors see only their own colonies, Authors and Admins see all
        $current_user = wp_get_current_user();
        $user_roles = $current_user->roles;
        
        // Check if user should see limited colonies
        $is_pure_contributor = in_array('contributor', $user_roles) && !in_array('author', $user_roles) && !in_array('administrator', $user_roles);
        $is_subscriber = in_array('subscriber', $user_roles) && !in_array('contributor', $user_roles) && !in_array('author', $user_roles) && !in_array('administrator', $user_roles);
        
        if ($is_pure_contributor) {
            // Pure contributors see only their own colonies
            $query_args['author'] = $user_id;
        } elseif ($is_subscriber) {
            // Subscribers see NO colonies whatsoever
            $query_args['author'] = -1; // Non-existent user ID to return no results
        }
        
        $colonies = get_posts($query_args);
        
        $colony_data = array();
        foreach ($colonies as $colony) {
            $added_by_id = get_post_meta($colony->ID, 'added_by_user', true);
            $added_by_name = 'Unknown';
            
            if ($added_by_id) {
                $user = get_userdata($added_by_id);
                $added_by_name = $user ? $user->display_name : 'Unknown';
            }
            
            // Get colony population - use LIVE metadata, not historical records
            // Historical records are for tracking changes over time, not current state
            $colony_info = array(
                'id' => $colony->ID,
                'name' => $colony->post_title,
                'lat' => floatval(get_post_meta($colony->ID, 'latitude', true)),
                'lng' => floatval(get_post_meta($colony->ID, 'longitude', true)),
                'cats' => absint(get_post_meta($colony->ID, 'cats_total', true)),
                'babies' => absint(get_post_meta($colony->ID, 'cats_babies', true)),
                'kittens' => absint(get_post_meta($colony->ID, 'cats_kittens', true)),
                'adults' => absint(get_post_meta($colony->ID, 'cats_adults', true)),
                'seniors' => absint(get_post_meta($colony->ID, 'cats_seniors', true)),
                'tippedEars' => absint(get_post_meta($colony->ID, 'tipped_ears', true)),
                'winterShelters' => absint(get_post_meta($colony->ID, 'winter_shelters', true)),
                'foodSecurity' => sanitize_text_field(get_post_meta($colony->ID, 'food_security', true)),
                'waterSecurity' => sanitize_text_field(get_post_meta($colony->ID, 'water_security', true)),
                'safeLocation' => sanitize_text_field(get_post_meta($colony->ID, 'safe_location', true)),
                'captureMethod' => sanitize_text_field(get_post_meta($colony->ID, 'capture_method', true)),
                'vaccinatedCount' => get_post_meta($colony->ID, 'vaccinated_count', true) ?: 'unknown',
                'microchipCount' => get_post_meta($colony->ID, 'microchip_count', true) ?: 'unknown',
                'tnrNoTipping' => (bool)get_post_meta($colony->ID, 'tnr_no_tipping', true),
                'volunteerHelp' => json_decode(get_post_meta($colony->ID, 'volunteer_help', true), true) ?: [],
                'assignedUserId' => get_post_meta($colony->ID, 'assigned_user_id', true) ?: null,
                
                // Address fields (both combined and individual)
                'address' => sanitize_text_field(get_post_meta($colony->ID, 'address', true)),
                'street' => sanitize_text_field(get_post_meta($colony->ID, 'street', true)),
                'city' => sanitize_text_field(get_post_meta($colony->ID, 'city', true)),
                'state' => sanitize_text_field(get_post_meta($colony->ID, 'state', true)),
                'zip' => sanitize_text_field(get_post_meta($colony->ID, 'zip', true)),
                
                'addedBy' => $added_by_name,
                'addedByUserId' => $added_by_id,
                'lastUpdated' => get_post_meta($colony->ID, 'last_updated', true),
                'dateAdded' => get_post_meta($colony->ID, 'date_added', true) ?: $colony->post_date,
                
                // CAREGIVER DATA - CRITICAL FOR PERSISTENCE
                'caregiver' => sanitize_text_field(get_post_meta($colony->ID, 'caregiver', true)),
                'caregiverId' => get_post_meta($colony->ID, 'caregiver_id', true) ?: null,
                'caregivers' => json_decode(get_post_meta($colony->ID, 'caregivers', true), true) ?: [],
                'photos' => $this->get_colony_photos($colony->ID),
                
                // NEW STANDARDIZED FIELD NAMES for frontend compatibility (use live metadata)
                'cats_newborn' => absint(get_post_meta($colony->ID, 'cats_babies', true)),
                'cats_kitten' => absint(get_post_meta($colony->ID, 'cats_kittens', true)),
                'cats_cat' => absint(get_post_meta($colony->ID, 'cats_adults', true)),
                'cats_senior' => absint(get_post_meta($colony->ID, 'cats_seniors', true)),
                'newborn' => absint(get_post_meta($colony->ID, 'cats_babies', true)),
                'kitten' => absint(get_post_meta($colony->ID, 'cats_kittens', true)),
                'cat' => absint(get_post_meta($colony->ID, 'cats_adults', true)),
                'senior' => absint(get_post_meta($colony->ID, 'cats_seniors', true)),
            );
            
            $colony_data[] = $colony_info;
        }
        
        return $colony_data;
    }
    
    /**
     * Clean up duplicate colonies (keep the most recent version of each)
     */
    public function cleanup_duplicate_colonies() {
        $query_args = array(
            'post_type' => 'claws_colony',
            'numberposts' => -1,
            'post_status' => 'publish',
            'orderby' => 'date',
            'order' => 'DESC'
        );
        
        $all_colonies = get_posts($query_args);
        $colony_groups = array();
        
        // Group colonies by name
        foreach ($all_colonies as $colony) {
            $name = trim(strtolower($colony->post_title));
            if (!isset($colony_groups[$name])) {
                $colony_groups[$name] = array();
            }
            $colony_groups[$name][] = $colony;
        }
        
        $deleted_count = 0;
        
        // For each group, keep the most recent and delete the rest
        foreach ($colony_groups as $name => $colonies) {
            if (count($colonies) > 1) {
                claws_log("Found " . count($colonies) . " duplicates for colony: {$name}");
                
                // Sort by date (most recent first)
                usort($colonies, function($a, $b) {
                    return strtotime($b->post_date) - strtotime($a->post_date);
                });
                
                // Keep the first (most recent), delete the rest
                for ($i = 1; $i < count($colonies); $i++) {
                    $duplicate = $colonies[$i];
                    claws_log("Deleting duplicate colony: {$duplicate->post_title} (ID: {$duplicate->ID}, Date: {$duplicate->post_date})");
                    wp_delete_post($duplicate->ID, true);
                    $deleted_count++;
                }
                
                claws_log("Kept most recent: {$colonies[0]->post_title} (ID: {$colonies[0]->ID}, Date: {$colonies[0]->post_date})");
            }
        }
        
        claws_log("Cleanup complete: Deleted {$deleted_count} duplicate colonies");
        
        return array(
            'deleted_count' => $deleted_count,
            'remaining_colonies' => count($colony_groups)
        );
    }
    
    /**
     * Save a new colony
     */
    public function save_colony($colony_data, $user_id) {
        // Validate required fields
        if (empty($colony_data['name'])) {
            return new WP_Error('missing_name', 'Colony name is required');
        }
        
        $post_data = array(
            'post_type' => 'claws_colony',
            'post_title' => sanitize_text_field($colony_data['name']),
            'post_status' => 'publish',
            'post_author' => $user_id
        );
        
        $post_id = wp_insert_post($post_data, true);
        
        if (is_wp_error($post_id)) {
            return $post_id;
        }
        
        // Save metadata
        $meta_fields = array(
            'latitude' => isset($colony_data['lat']) ? floatval($colony_data['lat']) : 0,
            'longitude' => isset($colony_data['lng']) ? floatval($colony_data['lng']) : 0,
            'cats_total' => isset($colony_data['cats']) ? absint($colony_data['cats']) : 0,
            'cats_babies' => isset($colony_data['babies']) ? absint($colony_data['babies']) : 0,
            'cats_kittens' => isset($colony_data['kittens']) ? absint($colony_data['kittens']) : 0,
            'cats_adults' => isset($colony_data['adults']) ? absint($colony_data['adults']) : 0,
            'cats_seniors' => isset($colony_data['seniors']) ? absint($colony_data['seniors']) : 0,
            'tipped_ears' => isset($colony_data['tippedEars']) ? absint($colony_data['tippedEars']) : 0,
            'winter_shelters' => isset($colony_data['winterShelters']) ? absint($colony_data['winterShelters']) : 0,
            'food_security' => isset($colony_data['foodSecurity']) ? sanitize_text_field($colony_data['foodSecurity']) : '',
            'water_security' => isset($colony_data['waterSecurity']) ? sanitize_text_field($colony_data['waterSecurity']) : '',
            'safe_location' => isset($colony_data['safeLocation']) ? sanitize_text_field($colony_data['safeLocation']) : '',
            'capture_method' => isset($colony_data['captureMethod']) ? sanitize_text_field($colony_data['captureMethod']) : '',
            'vaccinated_count' => isset($colony_data['vaccinatedCount']) ? ($colony_data['vaccinatedCount'] === 'unknown' ? 'unknown' : absint($colony_data['vaccinatedCount'])) : 'unknown',
            'microchip_count' => isset($colony_data['microchipCount']) ? ($colony_data['microchipCount'] === 'unknown' ? 'unknown' : absint($colony_data['microchipCount'])) : 'unknown',
            'tnr_no_tipping' => isset($colony_data['tnrNoTipping']) ? ($colony_data['tnrNoTipping'] ? 1 : 0) : 0,
            'volunteer_help' => isset($colony_data['volunteerHelp']) ? wp_json_encode($colony_data['volunteerHelp']) : '',
            'colony_help' => isset($colony_data['colonyHelp']) ? wp_json_encode($colony_data['colonyHelp']) : '',
            'assigned_user_id' => isset($colony_data['assignedUserId']) ? intval($colony_data['assignedUserId']) : null,
            'address' => isset($colony_data['address']) ? sanitize_text_field($colony_data['address']) : '',
            'street' => isset($colony_data['street']) ? sanitize_text_field($colony_data['street']) : '',
            'city' => isset($colony_data['city']) ? sanitize_text_field($colony_data['city']) : '',
            'state' => isset($colony_data['state']) ? sanitize_text_field($colony_data['state']) : '',
            'zip' => isset($colony_data['zip']) ? sanitize_text_field($colony_data['zip']) : '',
            'added_by_user' => $user_id,
            'last_updated' => current_time('mysql'),
            'last_updated_by' => $user_id,
            'date_added' => isset($colony_data['date']) ? sanitize_text_field($colony_data['date']) : current_time('Y-m-d'),
            'caregivers' => isset($colony_data['caregivers']) ? wp_json_encode($colony_data['caregivers']) : ''
        );
        
        foreach ($meta_fields as $key => $value) {
            update_post_meta($post_id, $key, $value);
        }
        
        claws_log("Colony '{$colony_data['name']}' saved successfully (ID: {$post_id})");
        
        // Create initial historical record using custom post type with user-selected date
        $effective_date = isset($colony_data['date']) ? $colony_data['date'] : current_time('Y-m-d');
        $this->create_colony_history_post($post_id, $colony_data, $user_id, $effective_date);
        
        // Send colony help emails if help was requested
        if (isset($colony_data['colonyHelp']) && is_array($colony_data['colonyHelp'])) {
            $this->send_colony_help_emails($colony_data, $user_id);
        }
        
        return $post_id;
    }
    
    
    /**
     * Send colony help emails when help is requested
     */
    public function send_colony_help_emails($colony_data, $user_id) {
        // Get the help options configuration
        $colony_help_options = get_option('claws_colony_help_options', array());
        $email_template = get_option('claws_colony_help_email_template', '{{username}} has requested assistance with {{help_type}}. Please contact {{username}} at {{contact_info}}');
        
        // Get user information
        $user = get_userdata($user_id);
        if (!$user) {
            claws_log('Unable to get user data for colony help email', 'error');
            return false;
        }
        
        // Collect contact information
        $contact_info = array();
        if (isset($colony_data['caregivers']) && is_array($colony_data['caregivers'])) {
            foreach ($colony_data['caregivers'] as $caregiver) {
                if (!empty($caregiver['email'])) $contact_info[] = 'Email: ' . $caregiver['email'];
                if (!empty($caregiver['phone'])) $contact_info[] = 'Phone: ' . $caregiver['phone'];
            }
        }
        $contact_info_text = !empty($contact_info) ? implode(', ', $contact_info) : $user->user_email;
        
        // Send emails for each selected help option
        foreach ($colony_data['colonyHelp'] as $help_key => $selected) {
            if ($selected && isset($colony_help_options[$help_key])) {
                $option = $colony_help_options[$help_key];
                
                // Skip if no email is configured for this option
                if (empty($option['email'])) {
                    claws_log("No email configured for colony help option: {$help_key}", 'warning');
                    continue;
                }
                
                // Prepare email content
                $placeholders = array(
                    '{{username}}' => $user->display_name,
                    '{{help_type}}' => $option['label'],
                    '{{contact_info}}' => $contact_info_text,
                    '{{colony_name}}' => isset($colony_data['name']) ? $colony_data['name'] : 'Unknown',
                    '{{colony_location}}' => isset($colony_data['address']) ? $colony_data['address'] : 'Location not specified'
                );
                
                $subject = 'Colony Help Request: ' . $option['label'];
                $message = str_replace(array_keys($placeholders), array_values($placeholders), $email_template);
                
                // Send the email
                $email_sent = wp_mail($option['email'], $subject, $message);
                
                if ($email_sent) {
                    claws_log("Colony help email sent for '{$option['label']}' to {$option['email']}");
                } else {
                    claws_log("Failed to send colony help email for '{$option['label']}' to {$option['email']}", 'error');
                }
            }
        }
        
        return true;
    }
    
    
    
    
    
    
    
    
    
    /**
     * Get user's intake records based on role permissions
     * Contributors: Only own intakes
     * Authors: Only own intakes  
     * Admins: All intakes (but typically accessed via backend)
     */
    public function get_user_intakes($user_id) {
        // Always filter to user's own intakes for frontend
        // Admins can see all via backend admin area
        $intakes = get_posts(array(
            'post_type' => 'claws_intake',
            'author' => $user_id,
            'numberposts' => 50,
            'post_status' => 'publish',
            'orderby' => 'date',
            'order' => 'DESC'
        ));
        
        $intake_data = array();
        foreach ($intakes as $intake) {
            $meta = get_post_meta($intake->ID);
            $intake_info = array(
                'id' => $intake->ID,
                'title' => $intake->post_title,
                'date' => $intake->post_date
            );
            
            // Add meta fields
            foreach ($meta as $key => $value) {
                $intake_info[$key] = is_array($value) ? $value[0] : $value;
            }
            
            // Add photo information
            $intake_info['photo'] = $this->get_record_photo($intake->ID, 'intake_photo');
            
            $intake_data[] = $intake_info;
        }
        
        return $intake_data;
    }
    
    /**
     * Save intake record
     */
    public function save_intake($intake_data, $user_id) {
        
        // Handle date field name inconsistency
        $date_field = isset($intake_data['date']) ? $intake_data['date'] : (isset($intake_data['intake_date']) ? $intake_data['intake_date'] : '');
        
        $post_id = wp_insert_post(array(
            'post_type' => 'claws_intake',
            'post_title' => sanitize_text_field($intake_data['cat_name'] . ' - ' . $date_field),
            'post_status' => 'publish',
            'post_author' => $user_id
        ));
        
        if ($post_id && !is_wp_error($post_id)) {
            // Save all intake fields as meta
            foreach ($intake_data as $key => $value) {
                if (is_array($value)) {
                    update_post_meta($post_id, sanitize_key($key), wp_json_encode($value));
                } else {
                    update_post_meta($post_id, sanitize_key($key), sanitize_text_field($value));
                }
            }
            
            // Ensure both 'date' and 'intake_date' are saved for compatibility
            if (isset($intake_data['date']) && !isset($intake_data['intake_date'])) {
                update_post_meta($post_id, 'intake_date', sanitize_text_field($intake_data['date']));
            }
            if (isset($intake_data['intake_date']) && !isset($intake_data['date'])) {
                update_post_meta($post_id, 'date', sanitize_text_field($intake_data['intake_date']));
            }
            
            update_post_meta($post_id, 'intake_timestamp', current_time('mysql'));
            
            claws_log("Intake record saved for {$intake_data['cat_name']} (ID: {$post_id})");
            
            return $post_id;
        }
        
        return new WP_Error('save_failed', 'Could not save intake record');
    }
    
    /**
     * Update intake record
     */
    public function update_intake_record($record_id, $intake_data, $user_id) {
        // Verify the record exists and belongs to the user
        $post = get_post($record_id);
        if (!$post || $post->post_type !== 'claws_intake' || $post->post_author != $user_id) {
            return new WP_Error('invalid_record', 'Intake record not found or access denied');
        }
        
        // Update the post title
        $updated = wp_update_post(array(
            'ID' => $record_id,
            'post_title' => sanitize_text_field($intake_data['cat_name'] . ' - ' . $intake_data['intake_date'])
        ), true);
        
        if (is_wp_error($updated)) {
            return new WP_Error('update_failed', 'Could not update intake record');
        }
        
        // Update all meta fields
        foreach ($intake_data as $key => $value) {
            if (is_array($value)) {
                update_post_meta($record_id, sanitize_key($key), wp_json_encode($value));
            } else {
                update_post_meta($record_id, sanitize_key($key), sanitize_text_field($value));
            }
        }
        
        update_post_meta($record_id, 'last_updated', current_time('mysql'));
        
        claws_log("Intake record updated for {$intake_data['cat_name']} (ID: {$record_id})");
        
        return $record_id;
    }

    /**
     * Get user's discharge records based on role permissions
     * Contributors: Only own discharges
     * Authors: Only own discharges
     * Admins: All discharges (but typically accessed via backend)
     */
    public function get_user_discharges($user_id) {
        // Always filter to user's own discharges for frontend
        // Admins can see all via backend admin area
        $discharges = get_posts(array(
            'post_type' => 'claws_discharge',
            'author' => $user_id,
            'numberposts' => 50,
            'post_status' => 'publish',
            'orderby' => 'date',
            'order' => 'DESC'
        ));
        
        $discharge_data = array();
        foreach ($discharges as $discharge) {
            $meta = get_post_meta($discharge->ID);
            $discharge_info = array(
                'id' => $discharge->ID,
                'title' => $discharge->post_title,
                'date' => $discharge->post_date
            );
            
            foreach ($meta as $key => $value) {
                $discharge_info[$key] = is_array($value) ? $value[0] : $value;
            }
            
            // Add photo information
            $discharge_info['photo'] = $this->get_record_photo($discharge->ID, 'discharge_photo');
            
            $discharge_data[] = $discharge_info;
        }
        
        return $discharge_data;
    }
    
    /**
     * Save discharge record
     */
    public function save_discharge($discharge_data, $user_id) {
        $post_id = wp_insert_post(array(
            'post_type' => 'claws_discharge',
            'post_title' => sanitize_text_field($discharge_data['cat_name'] . ' - ' . $discharge_data['outcome']),
            'post_status' => 'publish',
            'post_author' => $user_id
        ));
        
        if ($post_id && !is_wp_error($post_id)) {
            foreach ($discharge_data as $key => $value) {
                update_post_meta($post_id, sanitize_key($key), sanitize_text_field($value));
            }
            
            update_post_meta($post_id, 'discharge_timestamp', current_time('mysql'));
            
            claws_log("Discharge record saved for {$discharge_data['cat_name']} (ID: {$post_id})");
            
            return $post_id;
        }
        
        return new WP_Error('save_failed', 'Could not save discharge record');
    }
    
    /**
     * Handle colony adjustments after discharge
     */
    public function handle_discharge_colony_adjustment($discharge_data, $user_id) {
        claws_log("Discharge colony adjustment: Cat {$discharge_data['cat_name']}, Outcome: {$discharge_data['outcome']}");
        
        // Find the intake record for this cat to see if it came from a colony
        $intake_record = $this->find_intake_by_cat_name($discharge_data['cat_name']);
        
        if (!$intake_record) {
            return false;
        }
        
        // Check if the cat came from a colony
        $intake_colony_id = get_post_meta($intake_record->ID, 'colony_id', true);
        $intake_type = get_post_meta($intake_record->ID, 'type', true);
        
        if (!$intake_colony_id || $intake_type !== 'Colony Cat') {
            return false;
        }
        
        // Check if the outcome requires colony count reduction
        // TNR means the cat goes back to the colony, so no reduction
        $outcome = $discharge_data['outcome'];
        $reduce_count_outcomes = array(
            'Adoption',
            'Transfer', 
            'Died',
            'Euthanasia',
            'Returned to Owner',
            'Escaped',
            'Foster to Adopt'
        );
        
        if (!in_array($outcome, $reduce_count_outcomes)) {
            return false;
        }
        
        // Get current colony data
        $colony_post = get_post($intake_colony_id);
        if (!$colony_post || $colony_post->post_type !== 'claws_colony') {
            claws_log("ERROR: Colony not found for ID: {$intake_colony_id}", 'error');
            return false;
        }
        
        // Get current colony metadata
        $current_total = intval(get_post_meta($intake_colony_id, 'cats_total', true));
        $current_babies = intval(get_post_meta($intake_colony_id, 'cats_babies', true));
        $current_kittens = intval(get_post_meta($intake_colony_id, 'cats_kittens', true));
        $current_adults = intval(get_post_meta($intake_colony_id, 'cats_adults', true));
        $current_seniors = intval(get_post_meta($intake_colony_id, 'cats_seniors', true));
        
        // Determine which age category to reduce based on intake age
        $intake_age = get_post_meta($intake_record->ID, 'age', true);
        
        $new_babies = $current_babies;
        $new_kittens = $current_kittens;
        $new_adults = $current_adults;
        $new_seniors = $current_seniors;
        
        // Reduce the appropriate age category
        if (strpos($intake_age, 'Newborn') !== false || strpos($intake_age, '0-8 weeks') !== false) {
            $new_babies = max(0, $current_babies - 1);
        } elseif (strpos($intake_age, 'Kitten') !== false || strpos($intake_age, '2-5 months') !== false) {
            $new_kittens = max(0, $current_kittens - 1);
        } elseif (strpos($intake_age, 'Senior') !== false || strpos($intake_age, '8 years') !== false) {
            $new_seniors = max(0, $current_seniors - 1);
        } else {
            $new_adults = max(0, $current_adults - 1);
        }
        
        // Calculate new total as sum of age categories (matches frontend calculation)
        $new_total = $new_babies + $new_kittens + $new_adults + $new_seniors;
        
        // Update colony metadata
        update_post_meta($intake_colony_id, 'cats_total', $new_total);
        update_post_meta($intake_colony_id, 'cats_babies', $new_babies);
        update_post_meta($intake_colony_id, 'cats_kittens', $new_kittens);
        update_post_meta($intake_colony_id, 'cats_adults', $new_adults);
        update_post_meta($intake_colony_id, 'cats_seniors', $new_seniors);
        update_post_meta($intake_colony_id, 'last_updated', current_time('mysql'));
        update_post_meta($intake_colony_id, 'last_updated_by', $user_id);
        
        // Create historical record with the new population
        $history_data = array(
            'cats' => $new_total,
            'babies' => $new_babies,
            'kittens' => $new_kittens,
            'adults' => $new_adults,
            'seniors' => $new_seniors,
            'tippedEars' => intval(get_post_meta($intake_colony_id, 'tipped_ears', true)),
            'winterShelters' => intval(get_post_meta($intake_colony_id, 'winter_shelters', true)),
            'foodSecurity' => get_post_meta($intake_colony_id, 'food_security', true),
            'waterSecurity' => get_post_meta($intake_colony_id, 'water_security', true),
            'safeLocation' => get_post_meta($intake_colony_id, 'safe_location', true)
        );
        
        $history_result = $this->create_colony_history_post($intake_colony_id, $history_data, $user_id);
        
        claws_log("Colony adjustment complete: {$colony_post->post_title} reduced from {$current_total} to {$new_total} cats");
        
        return array(
            'colony_id' => $intake_colony_id,
            'colony_name' => $colony_post->post_title,
            'old_count' => $current_total,
            'new_count' => $new_total
        );
    }
    
    /**
     * Find intake record by cat name
     */
    public function find_intake_by_cat_name($cat_name) {
        $intakes = get_posts(array(
            'post_type' => 'claws_intake',
            'meta_query' => array(
                array(
                    'key' => 'cat_name',
                    'value' => sanitize_text_field($cat_name),
                    'compare' => '='
                )
            ),
            'posts_per_page' => 1,
            'orderby' => 'date',
            'order' => 'DESC'
        ));
        
        return !empty($intakes) ? $intakes[0] : null;
    }
    
    /**
     * Save feeding record
     */
    public function save_feeding_record($feeding_data, $user_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'claws_feeding_records';
        
        // Ensure we have proper date/time - use current if not provided
        $feeding_date = !empty($feeding_data['feeding_date']) ? $feeding_data['feeding_date'] : 
                        (!empty($feeding_data['date']) ? $feeding_data['date'] : current_time('Y-m-d'));
        $feeding_time = !empty($feeding_data['feeding_time']) ? $feeding_data['feeding_time'] : 
                        (!empty($feeding_data['time']) ? $feeding_data['time'] : current_time('H:i:s'));
        
        $result = $wpdb->insert(
            $table_name,
            array(
                'user_id' => $user_id,
                'kitten_name' => sanitize_text_field($feeding_data['kitten_name']),
                'feeding_date' => $feeding_date,
                'feeding_time' => $feeding_time,
                'pre_weight' => floatval($feeding_data['pre_weight']),
                'post_weight' => floatval($feeding_data['post_weight']),
                'notes' => sanitize_textarea_field($feeding_data['notes'])
            ),
            array('%d', '%s', '%s', '%s', '%f', '%f', '%s')
        );
        
        if ($result === false) {
            claws_log("Database error saving feeding record: " . $wpdb->last_error, 'error');
            return new WP_Error('save_failed', 'Could not save feeding record: ' . $wpdb->last_error);
        }
        
        return $wpdb->insert_id;
    }
    
    /**
     * Get user's feeding records (excludes records for discharged kittens)
     */
    public function get_user_feeding_records($user_id) {
        global $wpdb;
        
        try {
            $table_name = $wpdb->prefix . 'claws_feeding_records';
            
            // Validate user_id
            if (!$user_id || !is_numeric($user_id)) {
                return array();
            }
            
            // Get all feeding records first (no limit)
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM {$table_name} WHERE user_id = %d ORDER BY feeding_date DESC, feeding_time DESC",
                $user_id
            ), ARRAY_A);
            
            // If no results, return empty array
            if (!$results) {
                return array();
            }
            
            // Get discharge records to filter out discharged kittens
            $all_discharges = get_posts(array(
                'post_type' => 'claws_discharge',
                'author' => $user_id,
                'numberposts' => 500,
                'post_status' => 'publish'
            ));
            
            // If no discharges, return all results
            if (!$all_discharges || empty($all_discharges)) {
                return $results;
            }
            
            // Create a lookup array of discharged cat names
            $discharged_cats = array();
            foreach ($all_discharges as $discharge) {
                $discharged_cat_name = get_post_meta($discharge->ID, 'cat_name', true);
                if ($discharged_cat_name) {
                    $discharged_cats[strtolower(trim($discharged_cat_name))] = true;
                }
            }
            
            // If no discharged cats found, return all results
            if (empty($discharged_cats)) {
                return $results;
            }
            
            // Filter out records for discharged kittens
            $filtered_results = array();
            foreach ($results as $record) {
                if (isset($record['kitten_name'])) {
                    $kitten_name_lower = strtolower(trim($record['kitten_name']));
                    // Only include if kitten hasn't been discharged
                    if (!isset($discharged_cats[$kitten_name_lower])) {
                        $filtered_results[] = $record;
                    }
                } else {
                    // Include records without kitten names (safety fallback)
                    $filtered_results[] = $record;
                }
            }
            
            return $filtered_results;
            
        } catch (Exception $e) {
            claws_log('Error in get_user_feeding_records: ' . $e->getMessage(), 'error');
            return array();
        }
    }
    
    /**
     * Get single feeding record by ID
     */
    public function get_feeding_record($record_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'claws_feeding_records';
        
        $result = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE id = %d",
            $record_id
        ), ARRAY_A);
        
        return $result;
    }
    
    /**
     * Update feeding record
     */
    public function update_feeding_record($record_id, $feeding_data, $user_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'claws_feeding_records';
        
        // Handle date/time if provided (for updates that include them)
        $update_data = array(
            'kitten_name' => sanitize_text_field($feeding_data['kitten_name']),
            'pre_weight' => floatval($feeding_data['pre_weight']),
            'post_weight' => floatval($feeding_data['post_weight']),
            'notes' => sanitize_textarea_field($feeding_data['notes'])
        );
        
        // Add date/time if provided
        if (isset($feeding_data['date']) && !empty($feeding_data['date'])) {
            $update_data['feeding_date'] = $feeding_data['date'];
        }
        if (isset($feeding_data['time']) && !empty($feeding_data['time'])) {
            $update_data['feeding_time'] = $feeding_data['time'];
        }
        
        $format_array = array('%s', '%f', '%f', '%s');
        if (isset($feeding_data['date'])) $format_array[] = '%s';
        if (isset($feeding_data['time'])) $format_array[] = '%s';
        
        $result = $wpdb->update(
            $table_name,
            $update_data,
            array(
                'id' => $record_id,
                'user_id' => $user_id // Ensure users can only update their own records
            ),
            $format_array,
            array('%d', '%d')
        );
        
        if ($result === false) {
            claws_log("Database error updating feeding record: " . $wpdb->last_error, 'error');
            return new WP_Error('update_failed', 'Could not update feeding record: ' . $wpdb->last_error);
        }
        
        claws_log("Feeding record updated for {$feeding_data['kitten_name']} (ID: {$record_id})");
        
        return $record_id;
    }

    /**
     * Delete feeding record
     */
    public function delete_feeding_record($record_id, $user_id) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'claws_feeding_records';
        
        $result = $wpdb->delete(
            $table_name,
            array(
                'id' => $record_id,
                'user_id' => $user_id // Ensure users can only delete their own records
            ),
            array('%d', '%d')
        );
        
        return $result !== false;
    }
    
    /**
     * Delete feeding records by date range with optional kitten filter
     */
    public function delete_feeding_records_by_date_range($user_id, $start_date, $end_date, $exclude_active_kittens = true) {
        global $wpdb;
        
        try {
            $table_name = $wpdb->prefix . 'claws_feeding_records';
            
            // Validate inputs
            if (!$user_id || !is_numeric($user_id)) {
                return false;
            }
            
            if (empty($start_date) || empty($end_date)) {
                return false;
            }
            
            // Base query parameters
            $kitten_filter = '';
            $params = array($user_id, $start_date, $end_date);
            
            if ($exclude_active_kittens) {
                // Get discharged kitten names to limit deletion to only those
                $all_discharges = get_posts(array(
                    'post_type' => 'claws_discharge',
                    'author' => $user_id,
                    'numberposts' => 500,
                    'post_status' => 'publish'
                ));
                
                $discharged_cats = array();
                if ($all_discharges && !empty($all_discharges)) {
                    foreach ($all_discharges as $discharge) {
                        $discharged_cat_name = get_post_meta($discharge->ID, 'cat_name', true);
                        if ($discharged_cat_name) {
                            $discharged_cats[] = sanitize_text_field($discharged_cat_name);
                        }
                    }
                }
                
                if (!empty($discharged_cats)) {
                    $placeholders = str_repeat('%s,', count($discharged_cats) - 1) . '%s';
                    $kitten_filter = " AND kitten_name IN ($placeholders)";
                    $params = array_merge($params, $discharged_cats);
                } else {
                    // No discharged kittens, so don't delete anything when protection is enabled
                    claws_log("No discharged kittens found, skipping deletion with protection enabled");
                    return 0;
                }
            }
            
            $query = "DELETE FROM {$table_name} WHERE user_id = %d AND feeding_date >= %s AND feeding_date <= %s" . $kitten_filter;
            
            $result = $wpdb->query($wpdb->prepare($query, $params));
            
            if ($result === false) {
                claws_log("Database error deleting feeding records by date range: " . $wpdb->last_error, 'error');
                return false;
            }
            
            claws_log("Deleted {$result} feeding records for date range {$start_date} to {$end_date}");
            
            return $result;
            
        } catch (Exception $e) {
            claws_log('Error in delete_feeding_records_by_date_range: ' . $e->getMessage(), 'error');
            return false;
        }
    }
    
    /**
     * Get all feeding records across all users (admin only)
     */
    public function get_all_feeding_records($limit = 50) {
        global $wpdb;
        
        try {
            $table_name = $wpdb->prefix . 'claws_feeding_records';
            
            // Get feeding records with user information
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT fr.*, u.display_name as username 
                 FROM {$table_name} fr 
                 LEFT JOIN {$wpdb->users} u ON fr.user_id = u.ID 
                 ORDER BY fr.feeding_date DESC, fr.feeding_time DESC 
                 LIMIT %d",
                $limit
            ), ARRAY_A);
            
            if (!$results) {
                return array();
            }
            
            return $results;
            
        } catch (Exception $e) {
            claws_log('Error in get_all_feeding_records: ' . $e->getMessage(), 'error');
            return array();
        }
    }
    
    /**
     * Get baby kittens from intake records for neonate care dropdown
     */
    public function get_baby_kittens($user_id) {
        try {
            // Validate user_id
            if (!$user_id || !is_numeric($user_id)) {
                return array();
            }
            
            // Get all intake records first
            $all_intakes = get_posts(array(
                'post_type' => 'claws_intake',
                'author' => $user_id,
                'numberposts' => 100,
                'post_status' => 'publish'
            ));
            
            claws_log("GET_BABY_KITTENS: Found " . count($all_intakes) . " total intakes for user {$user_id}");
            
            if (!$all_intakes || empty($all_intakes)) {
                return array();
            }
            
            // Get all discharge records to check for discharged kittens
            $all_discharges = get_posts(array(
                'post_type' => 'claws_discharge',
                'author' => $user_id,
                'numberposts' => 500,
                'post_status' => 'publish'
            ));
            
            // Create a lookup array of discharged cat names
            $discharged_cats = array();
            if ($all_discharges && !empty($all_discharges)) {
                foreach ($all_discharges as $discharge) {
                    $discharged_cat_name = get_post_meta($discharge->ID, 'cat_name', true);
                    if ($discharged_cat_name) {
                        $discharged_cats[strtolower(trim($discharged_cat_name))] = true;
                    }
                }
            }
            
            $kittens = array();
            foreach ($all_intakes as $intake) {
                $cat_name = get_post_meta($intake->ID, 'cat_name', true);
                $intake_date = get_post_meta($intake->ID, 'intake_date', true) ?: get_post_meta($intake->ID, 'date', true);
                $age = get_post_meta($intake->ID, 'age', true);
                $outcome = get_post_meta($intake->ID, 'outcome', true);
                
                claws_log("GET_BABY_KITTENS: Processing intake {$intake->ID} - name: '{$cat_name}', age: '{$age}', outcome: '{$outcome}'");
                
                // Only include kittens that:
                // 1. Have a name
                // 2. Are babies (age contains "Baby" or "0-" for 0-8 weeks)
                // 3. Have NOT been discharged/processed out
                if ($cat_name && $age && empty($outcome)) {
                    // Check if this kitten has been discharged
                    $cat_name_lower = strtolower(trim($cat_name));
                    $has_discharge_record = isset($discharged_cats[$cat_name_lower]);
                    
                    if (!$has_discharge_record) {
                        // Check if age indicates baby kitten
                        $is_baby = (
                            stripos($age, 'Baby') !== false || 
                            stripos($age, 'Newborn') !== false ||
                            stripos($age, '0-') !== false ||
                            stripos($age, '0 ') !== false ||
                            preg_match('/^[0-8]\s*week/i', $age) ||
                            stripos($age, 'kitten') !== false
                        );
                        
                        if ($is_baby) {
                            $kittens[] = array(
                                'id' => $intake->ID,
                                'name' => $cat_name,
                                'intake_date' => $intake_date ?: '',
                                'age' => $age,
                                'display_name' => $cat_name
                            );
                        }
                    }
                }
            }
            
            return $kittens;
            
        } catch (Exception $e) {
            claws_log('Error in get_baby_kittens: ' . $e->getMessage(), 'error');
            return array();
        }
    }
    
    /**
     * Get colony photos
     */
    private function get_colony_photos($colony_id) {
        $photo_ids = get_post_meta($colony_id, 'colony_photos', true);
        if (!is_array($photo_ids)) {
            return array();
        }
        
        $photos = array();
        foreach ($photo_ids as $photo_id) {
            $photo_url = wp_get_attachment_url($photo_id);
            $thumbnail_url = wp_get_attachment_thumb_url($photo_id);
            
            if ($photo_url) {
                $photos[] = array(
                    'id' => $photo_id,
                    'url' => $photo_url,
                    'thumbnail' => $thumbnail_url ?: $photo_url,
                    'alt' => get_post_meta($photo_id, '_wp_attachment_image_alt', true)
                );
            }
        }
        
        return $photos;
    }
    
    /**
     * Get single photo for intake/discharge records
     */
    private function get_record_photo($record_id, $meta_key) {
        $photo_id = get_post_meta($record_id, $meta_key, true);
        if (!$photo_id) {
            return null;
        }
        
        $photo_url = wp_get_attachment_url($photo_id);
        $thumbnail_url = wp_get_attachment_thumb_url($photo_id);
        
        if ($photo_url) {
            return array(
                'id' => $photo_id,
                'url' => $photo_url,
                'thumbnail' => $thumbnail_url ?: $photo_url,
                'alt' => get_post_meta($photo_id, '_wp_attachment_image_alt', true)
            );
        }
        
        return null;
    }
    
    /**
     * Get aggregate intake analytics across all users (admin only)
     */
    public function get_aggregate_intake_analytics() {
        $intakes = get_posts(array(
            'post_type' => 'claws_intake',
            'numberposts' => -1,
            'post_status' => 'publish'
        ));
        
        $all_intake_data = array();
        
        foreach ($intakes as $intake) {
            $intake_info = array(
                'id' => $intake->ID,
                'cat_name' => get_post_meta($intake->ID, 'cat_name', true),
                'intake_date' => get_post_meta($intake->ID, 'intake_date', true),
                'type' => get_post_meta($intake->ID, 'type', true),
                'gender' => get_post_meta($intake->ID, 'gender', true),
                'age' => get_post_meta($intake->ID, 'age', true),
                'author_id' => $intake->post_author,
                'author_name' => get_userdata($intake->post_author)->display_name ?? 'Unknown'
            );
            
            $all_intake_data[] = $intake_info;
        }
        
        // Calculate aggregate statistics
        $week_ago = date('Y-m-d', strtotime('-7 days'));
        $month_ago = date('Y-m-d', strtotime('-30 days'));
        
        $total = count($all_intake_data);
        $this_week = array_filter($all_intake_data, function($record) use ($week_ago) {
            return $record['intake_date'] >= $week_ago;
        });
        $this_month = array_filter($all_intake_data, function($record) use ($month_ago) {
            return $record['intake_date'] >= $month_ago;
        });
        
        // Count by type
        $type_counts = array();
        foreach ($all_intake_data as $record) {
            $type = $record['type'] ?: 'Unknown';
            $type_counts[$type] = ($type_counts[$type] ?? 0) + 1;
        }
        
        // Count by gender
        $gender_counts = array();
        foreach ($all_intake_data as $record) {
            $gender = $record['gender'] ?: 'Unknown';
            $gender_counts[$gender] = ($gender_counts[$gender] ?? 0) + 1;
        }
        
        // Count by age
        $age_counts = array();
        foreach ($all_intake_data as $record) {
            $age = $record['age'] ?: 'Unknown';
            $age_counts[$age] = ($age_counts[$age] ?? 0) + 1;
        }
        
        // Count by volunteer
        $volunteer_counts = array();
        foreach ($all_intake_data as $record) {
            $volunteer = $record['author_name'];
            $volunteer_counts[$volunteer] = ($volunteer_counts[$volunteer] ?? 0) + 1;
        }
        
        return array(
            'total' => $total,
            'this_week' => count($this_week),
            'this_month' => count($this_month),
            'type_counts' => $type_counts,
            'gender_counts' => $gender_counts,
            'age_counts' => $age_counts,
            'volunteer_counts' => $volunteer_counts,
            'records' => $all_intake_data
        );
    }
    
    /**
     * Get aggregate discharge analytics across all users (admin only)
     */
    public function get_aggregate_discharge_analytics() {
        $discharges = get_posts(array(
            'post_type' => 'claws_discharge',
            'numberposts' => -1,
            'post_status' => 'publish'
        ));
        
        $all_discharge_data = array();
        
        foreach ($discharges as $discharge) {
            $cat_name_meta = get_post_meta($discharge->ID, 'cat_name', true);
            
            // Extract cat name from post title if meta field is empty
            $cat_name_from_title = '';
            if (empty($cat_name_meta) && !empty($discharge->post_title)) {
                $title_parts = explode(' - ', $discharge->post_title);
                if (!empty($title_parts[0])) {
                    $cat_name_from_title = $title_parts[0];
                }
            }
            
            $discharge_info = array(
                'id' => $discharge->ID,
                'cat_name' => !empty($cat_name_meta) ? $cat_name_meta : $cat_name_from_title,
                'discharge_date' => get_post_meta($discharge->ID, 'discharge_date', true),
                'outcome' => get_post_meta($discharge->ID, 'outcome', true),
                'author_id' => $discharge->post_author,
                'author_name' => get_userdata($discharge->post_author)->display_name ?? 'Unknown',
                'post_title' => $discharge->post_title // For debugging
            );
            
            $all_discharge_data[] = $discharge_info;
        }
        
        // Calculate aggregate statistics
        $week_ago = date('Y-m-d', strtotime('-7 days'));
        $month_ago = date('Y-m-d', strtotime('-30 days'));
        
        $total = count($all_discharge_data);
        $this_week = array_filter($all_discharge_data, function($record) use ($week_ago) {
            return $record['discharge_date'] >= $week_ago;
        });
        $this_month = array_filter($all_discharge_data, function($record) use ($month_ago) {
            return $record['discharge_date'] >= $month_ago;
        });
        
        // Count adoptions
        $adoptions = array_filter($all_discharge_data, function($record) {
            return stripos($record['outcome'], 'adopt') !== false;
        });
        
        // Count by outcome
        $outcome_counts = array();
        foreach ($all_discharge_data as $record) {
            $outcome = $record['outcome'] ?: 'Unknown';
            $outcome_counts[$outcome] = ($outcome_counts[$outcome] ?? 0) + 1;
        }
        
        // Count by volunteer
        $volunteer_counts = array();
        foreach ($all_discharge_data as $record) {
            $volunteer = $record['author_name'];
            $volunteer_counts[$volunteer] = ($volunteer_counts[$volunteer] ?? 0) + 1;
        }
        
        return array(
            'total' => $total,
            'this_week' => count($this_week),
            'this_month' => count($this_month),
            'adoptions' => count($adoptions),
            'outcome_counts' => $outcome_counts,
            'volunteer_counts' => $volunteer_counts,
            'records' => $all_discharge_data
        );
    }
    
    
    
    /**
     * Get colony creation date
     */
    public function get_colony_creation_date($colony_id) {
        $colony_post = get_post($colony_id);
        if (!$colony_post || $colony_post->post_type !== 'claws_colony') {
            return false;
        }
        
        return $colony_post->post_date;
    }
    
    
    /**
     * NEW CLEAN IMPLEMENTATION - Create colony history using custom post type
     */
    public function create_colony_history_post($colony_id, $colony_data, $user_id, $effective_date = null) {
        if (!$effective_date) {
            $effective_date = current_time('Y-m-d');
        }
        
        // Add timestamp to make each record unique (important for multiple changes on same day)
        $effective_datetime = current_time('Y-m-d H:i:s');
        
        // Create the post
        $post_data = array(
            'post_title' => sprintf('Colony %d History - %s', $colony_id, $effective_datetime),
            'post_content' => '',
            'post_status' => 'publish',
            'post_type' => 'claws_colony_history',
            'post_author' => $user_id,
            'post_date' => date('Y-m-d H:i:s', strtotime($effective_date))
        );
        
        $post_id = wp_insert_post($post_data);
        
        if (is_wp_error($post_id)) {
            claws_log("Error creating colony history post: " . $post_id->get_error_message(), 'error');
            return $post_id;
        }
        
        // Save metadata (just like feeding records)
        update_post_meta($post_id, 'colony_id', $colony_id);
        update_post_meta($post_id, 'effective_date', $effective_date);
        update_post_meta($post_id, 'effective_datetime', $effective_datetime); // NEW: Full timestamp
        update_post_meta($post_id, 'cats_total', isset($colony_data['cats']) ? intval($colony_data['cats']) : 0);
        // Store both legacy and new field names for backward compatibility
        update_post_meta($post_id, 'cats_babies', isset($colony_data['babies']) ? intval($colony_data['babies']) : (isset($colony_data['newborn']) ? intval($colony_data['newborn']) : 0));
        update_post_meta($post_id, 'cats_kittens', isset($colony_data['kittens']) ? intval($colony_data['kittens']) : (isset($colony_data['kitten']) ? intval($colony_data['kitten']) : 0));
        update_post_meta($post_id, 'cats_adults', isset($colony_data['adults']) ? intval($colony_data['adults']) : (isset($colony_data['cat']) ? intval($colony_data['cat']) : 0));
        update_post_meta($post_id, 'cats_seniors', isset($colony_data['seniors']) ? intval($colony_data['seniors']) : (isset($colony_data['senior']) ? intval($colony_data['senior']) : 0));
        // Store new standardized field names
        update_post_meta($post_id, 'cats_newborn', isset($colony_data['newborn']) ? intval($colony_data['newborn']) : (isset($colony_data['babies']) ? intval($colony_data['babies']) : 0));
        update_post_meta($post_id, 'cats_kitten', isset($colony_data['kitten']) ? intval($colony_data['kitten']) : (isset($colony_data['kittens']) ? intval($colony_data['kittens']) : 0));
        update_post_meta($post_id, 'cats_cat', isset($colony_data['cat']) ? intval($colony_data['cat']) : (isset($colony_data['adults']) ? intval($colony_data['adults']) : 0));
        update_post_meta($post_id, 'cats_senior', isset($colony_data['senior']) ? intval($colony_data['senior']) : (isset($colony_data['seniors']) ? intval($colony_data['seniors']) : 0));
        update_post_meta($post_id, 'tipped_ears', isset($colony_data['tippedEars']) ? intval($colony_data['tippedEars']) : 0);
        update_post_meta($post_id, 'winter_shelters', isset($colony_data['winterShelters']) ? intval($colony_data['winterShelters']) : 0);
        update_post_meta($post_id, 'food_security', isset($colony_data['foodSecurity']) ? sanitize_text_field($colony_data['foodSecurity']) : '');
        update_post_meta($post_id, 'water_security', isset($colony_data['waterSecurity']) ? sanitize_text_field($colony_data['waterSecurity']) : '');
        update_post_meta($post_id, 'safe_location', isset($colony_data['safeLocation']) ? sanitize_text_field($colony_data['safeLocation']) : '');
        
        return $post_id;
    }
    
    /**
     * Get the most recent colony data from historical records
     */
    public function get_most_recent_colony_data($colony_id) {
        $args = array(
            'post_type' => 'claws_colony_history',
            'numberposts' => 1, // Only get the most recent
            'post_status' => 'publish',
            'meta_query' => array(
                array(
                    'key' => 'colony_id',
                    'value' => $colony_id,
                    'compare' => '='
                )
            ),
            'orderby' => 'meta_value',
            'order' => 'DESC', // Most recent first
            'meta_key' => 'effective_date'
        );
        
        $posts = get_posts($args);
        
        if (empty($posts)) {
            return null;
        }
        
        $post = $posts[0];
        
        // Return the same format as historical records
        return array(
            'cats_newborn' => get_post_meta($post->ID, 'cats_newborn', true) ?: get_post_meta($post->ID, 'cats_babies', true) ?: 0,
            'cats_kitten' => get_post_meta($post->ID, 'cats_kitten', true) ?: get_post_meta($post->ID, 'cats_kittens', true) ?: 0,
            'cats_cat' => get_post_meta($post->ID, 'cats_cat', true) ?: get_post_meta($post->ID, 'cats_adults', true) ?: 0,
            'cats_senior' => get_post_meta($post->ID, 'cats_senior', true) ?: get_post_meta($post->ID, 'cats_seniors', true) ?: 0,
            'tipped_ears' => get_post_meta($post->ID, 'tipped_ears', true) ?: 0,
            'winter_shelters' => get_post_meta($post->ID, 'winter_shelters', true) ?: 0,
            'food_security' => get_post_meta($post->ID, 'food_security', true),
            'water_security' => get_post_meta($post->ID, 'water_security', true),
            'safe_location' => get_post_meta($post->ID, 'safe_location', true),
            'effective_date' => get_post_meta($post->ID, 'effective_date', true),
            'cats_total' => (
                (get_post_meta($post->ID, 'cats_newborn', true) ?: get_post_meta($post->ID, 'cats_babies', true) ?: 0) +
                (get_post_meta($post->ID, 'cats_kitten', true) ?: get_post_meta($post->ID, 'cats_kittens', true) ?: 0) +
                (get_post_meta($post->ID, 'cats_cat', true) ?: get_post_meta($post->ID, 'cats_adults', true) ?: 0) +
                (get_post_meta($post->ID, 'cats_senior', true) ?: get_post_meta($post->ID, 'cats_seniors', true) ?: 0)
            )
        );
    }

    /**
     * NEW CLEAN IMPLEMENTATION - Get colony historical records using custom post type
     */
    public function get_colony_history_posts($colony_id, $user_id = null) {
        $args = array(
            'post_type' => 'claws_colony_history',
            'numberposts' => -1,
            'post_status' => 'publish',
            'meta_query' => array(
                array(
                    'key' => 'colony_id',
                    'value' => $colony_id,
                    'compare' => '='
                )
            ),
            'orderby' => 'meta_value',
            'order' => 'ASC',
            'meta_key' => 'effective_date'
        );
        
        // Filter by user if specified and not admin
        if ($user_id && !current_user_can('manage_options')) {
            $args['author'] = $user_id;
        }
        
        $posts = get_posts($args);
        
        // Convert to array format for API consistency
        $history_records = array();
        foreach ($posts as $post) {
            $history_records[] = array(
                'id' => $post->ID,
                'colony_id' => $colony_id,
                'user_id' => $post->post_author,
                'effective_date' => get_post_meta($post->ID, 'effective_date', true) ?: date('Y-m-d', strtotime($post->post_date)),
                'cats_total' => get_post_meta($post->ID, 'cats_total', true) ?: 0,
                'cats_babies' => get_post_meta($post->ID, 'cats_babies', true) ?: 0, // Legacy field
                'cats_kittens' => get_post_meta($post->ID, 'cats_kittens', true) ?: 0, // Legacy field  
                'cats_adults' => get_post_meta($post->ID, 'cats_adults', true) ?: 0, // Legacy field
                'cats_seniors' => get_post_meta($post->ID, 'cats_seniors', true) ?: 0, // Legacy field
                // New standardized field names
                'cats_newborn' => get_post_meta($post->ID, 'cats_newborn', true) ?: get_post_meta($post->ID, 'cats_babies', true) ?: 0,
                'cats_kitten' => get_post_meta($post->ID, 'cats_kitten', true) ?: get_post_meta($post->ID, 'cats_kittens', true) ?: 0,
                'cats_cat' => get_post_meta($post->ID, 'cats_cat', true) ?: get_post_meta($post->ID, 'cats_adults', true) ?: 0,
                'cats_senior' => get_post_meta($post->ID, 'cats_senior', true) ?: get_post_meta($post->ID, 'cats_seniors', true) ?: 0,
                'tipped_ears' => get_post_meta($post->ID, 'tipped_ears', true) ?: 0,
                'winter_shelters' => get_post_meta($post->ID, 'winter_shelters', true) ?: 0,
                'food_security' => get_post_meta($post->ID, 'food_security', true) ?: '',
                'water_security' => get_post_meta($post->ID, 'water_security', true) ?: '',
                'safe_location' => get_post_meta($post->ID, 'safe_location', true) ?: '',
                'created_at' => $post->post_date
            );
        }
        
        return $history_records;
    }
    
    /**
     * NEW CLEAN IMPLEMENTATION - Create initial historical records for existing colonies
     */
    public function create_initial_colony_history_posts() {
        $colonies = get_posts(array(
            'post_type' => 'claws_colony',
            'numberposts' => -1,
            'post_status' => 'publish'
        ));
        
        $created_count = 0;
        
        foreach ($colonies as $colony) {
            // Check if history already exists using new method
            $existing_history = $this->get_colony_history_posts($colony->ID);
            
            if (!empty($existing_history)) {
                continue; // Skip if history already exists
            }
            
            // Get colony meta data
            $meta = get_post_meta($colony->ID);
            
            $colony_data = array(
                'cats' => isset($meta['cats_total'][0]) ? $meta['cats_total'][0] : 0,
                'babies' => isset($meta['cats_babies'][0]) ? $meta['cats_babies'][0] : 0,
                'kittens' => isset($meta['cats_kittens'][0]) ? $meta['cats_kittens'][0] : 0,
                'adults' => isset($meta['cats_adults'][0]) ? $meta['cats_adults'][0] : 0,
                'seniors' => isset($meta['cats_seniors'][0]) ? $meta['cats_seniors'][0] : 0,
                'tippedEars' => isset($meta['tipped_ears'][0]) ? $meta['tipped_ears'][0] : 0,
                'winterShelters' => isset($meta['winter_shelters'][0]) ? $meta['winter_shelters'][0] : 0,
                'foodSecurity' => isset($meta['food_security'][0]) ? $meta['food_security'][0] : '',
                'waterSecurity' => isset($meta['water_security'][0]) ? $meta['water_security'][0] : '',
                'safeLocation' => isset($meta['safe_location'][0]) ? $meta['safe_location'][0] : ''
            );
            
            // Use colony creation date as effective date
            $creation_date = date('Y-m-d', strtotime($colony->post_date));
            
            $result = $this->create_colony_history_post($colony->ID, $colony_data, $colony->post_author, $creation_date);
            
            if (!is_wp_error($result)) {
                $created_count++;
            }
        }
        
        return $created_count;
    }
}
?>