<?php
/**
 * Time Entry Model
 *
 * @package Freelance_Studio
 */

class FS_Time_Entry {

    // Activity types
    const ACTIVITY_DEVELOPMENT = 'development';
    const ACTIVITY_DESIGN = 'design';
    const ACTIVITY_MEETING = 'meeting';
    const ACTIVITY_RESEARCH = 'research';
    const ACTIVITY_ADMIN = 'admin';
    const ACTIVITY_OTHER = 'other';

    /**
     * Get a time entry by ID
     *
     * @param int $id Time entry ID
     * @return object|null Time entry object
     */
    public static function get($id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table_name WHERE id = %d",
            $id
        ));
    }

    /**
     * Get all time entries with optional filtering
     *
     * @param array $args Query arguments
     * @return array Array of time entry objects
     */
    public static function get_all($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';

        $defaults = array(
            'project_id' => null,
            'client_id' => null,
            'user_id' => null,
            'activity' => null,
            'billable' => null,
            'invoiced' => null,
            'task_id' => null,
            'date_from' => null,
            'date_to' => null,
            'running' => null,
            'orderby' => 'start_time',
            'order' => 'DESC',
            'limit' => null,
            'offset' => 0,
        );

        $args = wp_parse_args($args, $defaults);

        $select = "SELECT t.*, p.name as project_name, p.client_id, c.name as client_name, c.company_name, tk.title as task_name";
        $from = " FROM $table_name t";
        $join = " LEFT JOIN $projects_table p ON t.project_id = p.id";
        $join .= " LEFT JOIN $clients_table c ON p.client_id = c.id";
        $tasks_table = $wpdb->prefix . 'fs_tasks';
        $join .= " LEFT JOIN $tasks_table tk ON t.task_id = tk.id";
        
        $where = array();
        $values = array();

        if (!empty($args['project_id'])) {
            $where[] = 't.project_id = %d';
            $values[] = $args['project_id'];
        }

        if (!empty($args['client_id'])) {
            $where[] = 'p.client_id = %d';
            $values[] = $args['client_id'];
        }

        if (!empty($args['user_id'])) {
            $where[] = 't.user_id = %d';
            $values[] = $args['user_id'];
        }

        if (!empty($args['activity'])) {
            $where[] = 't.activity = %s';
            $values[] = $args['activity'];
        }

        if ($args['billable'] !== null) {
            $where[] = 't.billable = %d';
            $values[] = $args['billable'] ? 1 : 0;
        }

        if ($args['invoiced'] !== null) {
            $where[] = 't.invoiced = %d';
            $values[] = $args['invoiced'] ? 1 : 0;
        }

        if (!empty($args['task_id'])) {
            $where[] = 't.task_id = %d';
            $values[] = $args['task_id'];
        }

        if (!empty($args['date_from'])) {
            $where[] = 'DATE(t.start_time) >= %s';
            $values[] = $args['date_from'];
        }

        if (!empty($args['date_to'])) {
            $where[] = 'DATE(t.start_time) <= %s';
            $values[] = $args['date_to'];
        }

        if ($args['running'] !== null) {
            if ($args['running']) {
                $where[] = 't.end_time IS NULL';
            } else {
                $where[] = 't.end_time IS NOT NULL';
            }
        }

        $where_clause = '';
        if (!empty($where)) {
            $where_clause = ' WHERE ' . implode(' AND ', $where);
        }

        $allowed_orderby = array('start_time', 'end_time', 'duration', 'project_name', 'client_name', 'created_at');
        $orderby = in_array($args['orderby'], $allowed_orderby) ? $args['orderby'] : 'start_time';
        if ($orderby === 'project_name') $orderby = 'p.name';
        if ($orderby === 'client_name') $orderby = 'c.name';
        else $orderby = 't.' . $orderby;
        
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        $sql = $select . $from . $join . $where_clause . " ORDER BY $orderby $order";

        if ($args['limit']) {
            $sql .= $wpdb->prepare(" LIMIT %d OFFSET %d", $args['limit'], $args['offset']);
        }

        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }

        return $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Count time entries matching filters (for pagination).
     */
    public static function count_all($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';

        $defaults = array(
            'project_id' => null,
            'client_id' => null,
            'user_id' => null,
            'activity' => null,
            'billable' => null,
            'invoiced' => null,
            'task_id' => null,
            'date_from' => null,
            'date_to' => null,
            'running' => null,
        );
        $args = wp_parse_args($args, $defaults);

        $from = " FROM $table_name t";
        $join = " LEFT JOIN $projects_table p ON t.project_id = p.id";
        $where = array();
        $values = array();

        if (!empty($args['project_id'])) { $where[] = 't.project_id = %d'; $values[] = $args['project_id']; }
        if (!empty($args['client_id'])) { $where[] = 'p.client_id = %d'; $values[] = $args['client_id']; }
        if (!empty($args['user_id'])) { $where[] = 't.user_id = %d'; $values[] = $args['user_id']; }
        if (!empty($args['activity'])) { $where[] = 't.activity = %s'; $values[] = $args['activity']; }
        if ($args['billable'] !== null) { $where[] = 't.billable = %d'; $values[] = $args['billable'] ? 1 : 0; }
        if ($args['invoiced'] !== null) { $where[] = 't.invoiced = %d'; $values[] = $args['invoiced'] ? 1 : 0; }
        if (!empty($args['task_id'])) { $where[] = 't.task_id = %d'; $values[] = $args['task_id']; }
        if (!empty($args['date_from'])) { $where[] = 'DATE(t.start_time) >= %s'; $values[] = $args['date_from']; }
        if (!empty($args['date_to'])) { $where[] = 'DATE(t.start_time) <= %s'; $values[] = $args['date_to']; }
        if ($args['running'] !== null) {
            $where[] = $args['running'] ? 't.end_time IS NULL' : 't.end_time IS NOT NULL';
        }

        $where_clause = !empty($where) ? ' WHERE ' . implode(' AND ', $where) : '';
        $sql = "SELECT COUNT(*)" . $from . $join . $where_clause;

        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }

        return (int) $wpdb->get_var($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Create a new time entry
     *
     * @param array $data Time entry data
     * @return int|false Time entry ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        $insert_data = array(
            'project_id' => isset($data['project_id']) ? intval($data['project_id']) : 0,
            'user_id' => isset($data['user_id']) ? intval($data['user_id']) : get_current_user_id(),
            'description' => isset($data['description']) ? sanitize_textarea_field($data['description']) : '',
            'activity' => isset($data['activity']) ? sanitize_text_field($data['activity']) : self::ACTIVITY_OTHER,
            'start_time' => isset($data['start_time']) ? $data['start_time'] : current_time('mysql'),
            'end_time' => isset($data['end_time']) ? $data['end_time'] : null,
            'duration' => isset($data['duration']) ? intval($data['duration']) : 0,
            'billable' => isset($data['billable']) ? ($data['billable'] ? 1 : 0) : 1,
            'hourly_rate' => isset($data['hourly_rate']) ? floatval($data['hourly_rate']) : null,
            'invoiced' => 0,
            'invoice_id' => null,
        );

        // Only include task_id if it has a value (let DB default to NULL otherwise)
        if (!empty($data['task_id'])) {
            $insert_data['task_id'] = intval($data['task_id']);
        }

        // Get hourly rate from project's hourly services if not specified
        if ($insert_data['hourly_rate'] === null && $insert_data['project_id']) {
            $project = FS_Project::get($insert_data['project_id']);
            $rate_found = false;
            
            if ($project) {
                // Get rate from project's hourly services
                $services = $project->services;
                if (is_string($services)) {
                    $services = json_decode($services ?? '', true);
                }
                if (!empty($services) && is_array($services)) {
                    foreach ($services as $svc) {
                        if (isset($svc['pricing_type']) && $svc['pricing_type'] === 'hourly' && !empty($svc['rate'])) {
                            $insert_data['hourly_rate'] = floatval($svc['rate']);
                            $rate_found = true;
                            break;
                        }
                    }
                }
            }
            
            // Fall back to system default
            if (!$rate_found) {
                $insert_data['hourly_rate'] = get_option('fs_default_hourly_rate', 75);
            }
        }

        $result = $wpdb->insert($table_name, $insert_data);

        if ($result === false) {
            error_log('Freelance Studio - Time entry creation failed: ' . $wpdb->last_error);
            return false;
        }

        return $wpdb->insert_id;
    }

    /**
     * Update a time entry
     *
     * @param int $id Time entry ID
     * @param array $data Time entry data
     * @return bool Success
     */
    public static function update($id, $data) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        $update_data = array();
        $allowed_fields = array(
            'project_id', 'description', 'activity',
            'start_time', 'end_time', 'duration', 'billable',
            'hourly_rate', 'invoiced', 'invoice_id'
        );

        foreach ($allowed_fields as $field) {
            if (isset($data[$field])) {
                $update_data[$field] = $data[$field];
            }
        }

        // Handle task_id separately since it can be legitimately null
        $nullable_fks = array();
        if (array_key_exists('task_id', $data)) {
            if (!empty($data['task_id'])) {
                $update_data['task_id'] = intval($data['task_id']);
            } else {
                $nullable_fks['task_id'] = null;
            }
        }

        if (empty($update_data) && empty($nullable_fks)) {
            return true;
        }

        $result = true;
        if (!empty($update_data)) {
            $result = $wpdb->update($table_name, $update_data, array('id' => $id)) !== false;
        }
        
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table_name, $id, $nullable_fks);
        }
        
        return $result;
    }

    /**
     * Delete a time entry
     *
     * @param int $id Time entry ID
     * @return bool Success
     */
    public static function delete($id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        return $wpdb->delete($table_name, array('id' => $id)) !== false;
    }

    /**
     * Start a timer (create entry with no end time)
     *
     * @param array $data Timer data
     * @return int|false Time entry ID or false
     */
    /**
     * Convert UTC datetime to local datetime
     *
     * @param string $utc_datetime UTC datetime string
     * @param string $format Output format (default: Y-m-d H:i:s)
     * @return string Local datetime string
     */
    public static function utc_to_local($utc_datetime, $format = 'Y-m-d H:i:s') {
        if (empty($utc_datetime)) {
            return '';
        }
        
        // Get WordPress timezone
        $wp_timezone = wp_timezone();
        
        // Parse the UTC time
        $utc_tz = new DateTimeZone('UTC');
        $datetime = new DateTime($utc_datetime, $utc_tz);
        
        // Convert to local timezone
        $datetime->setTimezone($wp_timezone);
        
        return $datetime->format($format);
    }

    /**
     * Convert local datetime to UTC datetime
     *
     * @param string $local_datetime Local datetime string
     * @param string $format Output format (default: Y-m-d H:i:s)
     * @return string UTC datetime string
     */
    public static function local_to_utc($local_datetime, $format = 'Y-m-d H:i:s') {
        if (empty($local_datetime)) {
            return '';
        }
        
        // Get WordPress timezone
        $wp_timezone = wp_timezone();
        
        // Parse the local time
        $datetime = new DateTime($local_datetime, $wp_timezone);
        
        // Convert to UTC
        $utc_tz = new DateTimeZone('UTC');
        $datetime->setTimezone($utc_tz);
        
        return $datetime->format($format);
    }
    
    /**
     * Get local date from UTC datetime
     *
     * @param string $utc_datetime UTC datetime string
     * @return string Local date (Y-m-d)
     */
    public static function get_local_date($utc_datetime) {
        return self::utc_to_local($utc_datetime, 'Y-m-d');
    }

    public static function start_timer($data) {
        // Stop any running timers first
        self::stop_all_running(get_current_user_id());

        // Use UTC for storage to avoid timezone confusion in duration calculations
        // Display formatting will handle timezone conversion separately
        $data['start_time'] = gmdate('Y-m-d H:i:s');
        $data['end_time'] = null;
        $data['duration'] = 0;
        $data['paused_at'] = null;
        $data['paused_duration'] = 0;

        return self::create($data);
    }

    /**
     * Pause a running timer
     *
     * @param int $entry_id Time entry ID
     * @return bool Success
     */
    public static function pause_timer($entry_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        
        $entry = self::get($entry_id);
        if (!$entry || $entry->end_time !== null || $entry->paused_at !== null) {
            return false; // Not running or already paused
        }
        
        // Calculate time elapsed since start (using UTC)
        $start = strtotime($entry->start_time . ' UTC');
        $now = time();
        $elapsed = $now - $start;
        
        // Add to paused_duration and set paused_at
        $new_paused_duration = intval($entry->paused_duration) + max(0, $elapsed);
        
        return $wpdb->update(
            $table_name,
            array(
                'paused_at' => gmdate('Y-m-d H:i:s'),
                'paused_duration' => $new_paused_duration
            ),
            array('id' => $entry_id)
        ) !== false;
    }

    /**
     * Resume a paused timer
     *
     * @param int $entry_id Time entry ID
     * @return bool Success
     */
    public static function resume_timer($entry_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        
        $entry = self::get($entry_id);
        if (!$entry || $entry->end_time !== null || $entry->paused_at === null) {
            return false; // Not paused
        }
        
        // Reset start_time to now in UTC (paused_duration holds the accumulated time)
        return $wpdb->update(
            $table_name,
            array(
                'start_time' => gmdate('Y-m-d H:i:s'),
                'paused_at' => null
            ),
            array('id' => $entry_id)
        ) !== false;
    }

    /**
     * Check if a timer is paused
     *
     * @param int $entry_id Time entry ID
     * @return bool Is paused
     */
    public static function is_paused($entry_id) {
        $entry = self::get($entry_id);
        return $entry && $entry->paused_at !== null;
    }

    /**
     * Stop a running timer
     *
     * @param int $id Time entry ID
     * @return bool Success
     */
    public static function stop_timer($id) {
        $entry = self::get($id);
        if (!$entry || $entry->end_time !== null) {
            return false;
        }

        // Use UTC for consistency
        $end_time = gmdate('Y-m-d H:i:s');
        
        // If timer is paused, just use paused_duration
        if ($entry->paused_at !== null) {
            $duration = intval($entry->paused_duration);
        } else {
            // Timer is running - calculate current session + paused_duration
            $start = strtotime($entry->start_time . ' UTC');
            $end = time();
            $current_session = max(0, $end - $start);
            $duration = $current_session + intval($entry->paused_duration);
        }

        return self::update($id, array(
            'end_time' => $end_time,
            'duration' => $duration,
            'paused_at' => null,
        ));
    }

    /**
     * Stop all running timers for a user
     *
     * @param int $user_id User ID
     * @return int Number of timers stopped
     */
    public static function stop_all_running($user_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        $running = $wpdb->get_results($wpdb->prepare(
            "SELECT id, start_time FROM $table_name WHERE user_id = %d AND end_time IS NULL",
            $user_id
        ));

        $stopped = 0;
        foreach ($running as $entry) {
            if (self::stop_timer($entry->id)) {
                $stopped++;
            }
        }

        return $stopped;
    }

    /**
     * Get currently running timer for user
     *
     * @param int $user_id User ID (defaults to current user)
     * @return object|null Running timer or null
     */
    public static function get_running_timer($user_id = null) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';

        if ($user_id === null) {
            $user_id = get_current_user_id();
        }

        return $wpdb->get_row($wpdb->prepare(
            "SELECT t.*, p.name as project_name 
             FROM $table_name t 
             LEFT JOIN $projects_table p ON t.project_id = p.id 
             WHERE t.user_id = %d AND t.end_time IS NULL 
             ORDER BY t.start_time DESC 
             LIMIT 1",
            $user_id
        ));
    }

    /**
     * Get time entries for a date range (for week view)
     *
     * @param string $start_date Start date (Y-m-d)
     * @param string $end_date End date (Y-m-d)
     * @param int $user_id User ID
     * @return array Time entries grouped by date
     */
    public static function get_by_date_range($start_date, $end_date, $user_id = null) {
        if ($user_id === null) {
            $user_id = get_current_user_id();
        }

        $entries = self::get_all(array(
            'user_id' => $user_id,
            'date_from' => $start_date,
            'date_to' => $end_date,
            'orderby' => 'start_time',
            'order' => 'ASC',
        ));

        // Group by date (convert UTC to local date)
        $grouped = array();
        foreach ($entries as $entry) {
            $date = self::get_local_date($entry->start_time);
            if (!isset($grouped[$date])) {
                $grouped[$date] = array();
            }
            $grouped[$date][] = $entry;
        }

        return $grouped;
    }

    /**
     * Get total time for a project
     *
     * @param int $project_id Project ID
     * @param bool $billable_only Only count billable time
     * @return int Total seconds
     */
    public static function get_project_total_time($project_id, $billable_only = false) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        $where = "project_id = %d AND end_time IS NOT NULL";
        if ($billable_only) {
            $where .= " AND billable = 1";
        }

        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(duration) FROM $table_name WHERE $where",
            $project_id
        ));
    }

    /**
     * Get unbilled time entries for a project
     *
     * @param int $project_id Project ID
     * @return array Time entries
     */
    public static function get_unbilled($project_id) {
        return self::get_all(array(
            'project_id' => $project_id,
            'billable' => true,
            'invoiced' => false,
            'running' => false,
        ));
    }

    /**
     * Mark time entries as invoiced
     *
     * @param array $entry_ids Array of time entry IDs
     * @param int $invoice_id Invoice ID
     * @return bool Success
     */
    public static function mark_invoiced($entry_ids, $invoice_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        if (empty($entry_ids)) {
            return false;
        }

        $ids = implode(',', array_map('intval', $entry_ids));
        
        return $wpdb->query($wpdb->prepare(
            "UPDATE $table_name SET invoiced = 1, invoice_id = %d WHERE id IN ($ids)",
            $invoice_id
        )) !== false;
    }

    /**
     * Mark time entries as uninvoiced (reset when invoice is deleted)
     *
     * @param int $invoice_id Invoice ID to unlink
     * @return bool Success
     */
    public static function mark_uninvoiced_by_invoice($invoice_id) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';

        if (empty($invoice_id)) {
            return false;
        }

        return $wpdb->query($wpdb->prepare(
            "UPDATE $table_name SET invoiced = 0, invoice_id = NULL WHERE invoice_id = %d",
            $invoice_id
        )) !== false;
    }

    /**
     * Get summary stats for time entries
     *
     * @param array $args Filter arguments
     * @return object Summary stats
     */
    public static function get_summary($args = array()) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';

        $where = array('t.end_time IS NOT NULL');
        $values = array();

        if (!empty($args['project_id'])) {
            $where[] = 't.project_id = %d';
            $values[] = $args['project_id'];
        }

        if (!empty($args['client_id'])) {
            $where[] = 'p.client_id = %d';
            $values[] = $args['client_id'];
        }

        if (!empty($args['user_id'])) {
            $where[] = 't.user_id = %d';
            $values[] = $args['user_id'];
        }

        if (!empty($args['activity'])) {
            $where[] = 't.activity = %s';
            $values[] = $args['activity'];
        }

        if (!empty($args['task_id'])) {
            $where[] = 't.task_id = %d';
            $values[] = $args['task_id'];
        }

        if (isset($args['invoiced']) && $args['invoiced'] !== '' && $args['invoiced'] !== null) {
            if ($args['invoiced'] === 'invoiced' || $args['invoiced'] === '1' || $args['invoiced'] === 1) {
                $where[] = 't.invoiced = 1';
            } elseif ($args['invoiced'] === 'uninvoiced' || $args['invoiced'] === '0' || $args['invoiced'] === 0) {
                $where[] = 't.invoiced = 0';
            }
        }

        if (!empty($args['date_from'])) {
            $where[] = 'DATE(t.start_time) >= %s';
            $values[] = $args['date_from'];
        }

        if (!empty($args['date_to'])) {
            $where[] = 'DATE(t.start_time) <= %s';
            $values[] = $args['date_to'];
        }

        $where_clause = implode(' AND ', $where);

        // Determine which rate to use for calculations
        // Priority: Entry's own hourly_rate (set at creation from project service rate) → System default
        // This matches the invoice builder logic in build_time_entry_items()
        $default_rate = floatval(get_option('fs_default_hourly_rate', 75));
        $rate_expression = "COALESCE(NULLIF(t.hourly_rate, 0), {$default_rate})";

        // Build SQL expression for rounded duration (applies user's time rounding setting)
        $rounding_minutes = intval(get_option('fs_time_rounding', 0));
        $minimum_minutes = intval(get_option('fs_minimum_billable', 0));
        
        // Build a SQL expression that mirrors round_duration() logic
        // Start with raw duration, apply minimum, then apply rounding via CEIL
        if ($minimum_minutes > 0) {
            $min_seconds = $minimum_minutes * 60;
            $duration_expr = "GREATEST(t.duration, {$min_seconds})";
        } else {
            $duration_expr = "t.duration";
        }
        
        if ($rounding_minutes > 0) {
            $rounding_seconds = $rounding_minutes * 60;
            $rounded_duration = "CEIL({$duration_expr} / {$rounding_seconds}) * {$rounding_seconds}";
        } else {
            $rounded_duration = $duration_expr;
        }

        // Convert rounded seconds to hours with 2dp rounding, matching PHP get_rounded_hours()
        // This ensures SQL calculations match invoice line item amounts exactly
        $rounded_hours_expr = "ROUND({$rounded_duration} / 3600.0, 2)";

        $sql = "SELECT 
                    COUNT(*) as total_entries,
                    SUM(t.duration) as total_seconds,
                    SUM(CASE WHEN t.billable = 1 THEN t.duration ELSE 0 END) as billable_seconds,
                    SUM(CASE WHEN t.billable = 0 THEN t.duration ELSE 0 END) as non_billable_seconds,
                    SUM(CASE WHEN t.invoiced = 1 THEN t.duration ELSE 0 END) as invoiced_seconds,
                    SUM(CASE WHEN t.billable = 1 AND t.invoiced = 0 THEN t.duration ELSE 0 END) as uninvoiced_billable_seconds,
                    SUM(CASE WHEN t.billable = 1 THEN {$rounded_hours_expr} ELSE 0 END) as rounded_billable_hours,
                    SUM(CASE WHEN t.billable = 1 AND t.invoiced = 0 THEN {$rounded_hours_expr} ELSE 0 END) as rounded_uninvoiced_hours,
                    SUM(CASE WHEN t.billable = 1 THEN {$rounded_hours_expr} * {$rate_expression} ELSE 0 END) as total_billable_amount,
                    SUM(CASE WHEN t.billable = 1 AND t.invoiced = 0 THEN {$rounded_hours_expr} * {$rate_expression} ELSE 0 END) as uninvoiced_amount
                FROM $table_name t
                LEFT JOIN $projects_table p ON t.project_id = p.id
                WHERE $where_clause";

        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }

        return $wpdb->get_row($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get activity types
     *
     * @return array Activity types with labels
     */
    public static function get_activity_types() {
        $types = array(
            self::ACTIVITY_DEVELOPMENT => __('Development', 'freelance-studio'),
            self::ACTIVITY_DESIGN => __('Design', 'freelance-studio'),
            self::ACTIVITY_MEETING => __('Meeting', 'freelance-studio'),
            self::ACTIVITY_RESEARCH => __('Research', 'freelance-studio'),
            self::ACTIVITY_ADMIN => __('Admin', 'freelance-studio'),
            self::ACTIVITY_OTHER => __('Other', 'freelance-studio'),
        );
        
        // Add custom activities
        $custom_activities = get_option('fs_custom_activities', array());
        if (is_array($custom_activities)) {
            foreach ($custom_activities as $activity) {
                $key = sanitize_key($activity);
                if (!empty($key) && !isset($types[$key])) {
                    $types[$key] = $activity;
                }
            }
        }
        
        return $types;
    }
    
    /**
     * Round duration based on settings
     *
     * @param int $seconds Duration in seconds
     * @return int Rounded duration in seconds
     */
    public static function round_duration($seconds) {
        $rounding = intval(get_option('fs_time_rounding', 0));
        $minimum = intval(get_option('fs_minimum_billable', 0));
        
        // Apply minimum first
        if ($minimum > 0) {
            $minimum_seconds = $minimum * 60;
            if ($seconds < $minimum_seconds) {
                $seconds = $minimum_seconds;
            }
        }
        
        // Apply rounding
        if ($rounding > 0) {
            $rounding_seconds = $rounding * 60;
            $seconds = ceil($seconds / $rounding_seconds) * $rounding_seconds;
        }
        
        return $seconds;
    }
    
    /**
     * Get rounded duration for billing
     *
     * @param int $seconds Raw duration in seconds
     * @return array Array with raw and rounded values
     */
    public static function get_billable_duration($seconds) {
        $rounded = self::round_duration($seconds);
        return array(
            'raw' => $seconds,
            'rounded' => $rounded,
            'raw_formatted' => self::format_duration($seconds),
            'rounded_formatted' => self::format_duration($rounded),
            'raw_hours' => round($seconds / 3600, 2),
            'rounded_hours' => round($rounded / 3600, 2),
        );
    }
    
    /**
     * Get rounded hours for billing from raw seconds
     * 
     * Applies the user's time rounding setting and converts to hours.
     * This is the single canonical method all billing/invoicing code should use.
     *
     * @param int $seconds Raw duration in seconds
     * @return float Rounded hours (2 decimal places)
     */
    public static function get_rounded_hours($seconds) {
        $rounded_seconds = self::round_duration(intval($seconds));
        return round($rounded_seconds / 3600, 2);
    }

    /**
     * Get recent timer sessions for current user
     *
     * @param int $limit Number of entries to return
     * @return array Recent time entries grouped by date
     */
    public static function get_recent_sessions($limit = 10) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $user_id = get_current_user_id();
        
        $entries = $wpdb->get_results($wpdb->prepare(
            "SELECT t.*, p.name as project_name, c.name as client_name, c.company_name
             FROM $table_name t
             LEFT JOIN $projects_table p ON t.project_id = p.id
             LEFT JOIN $clients_table c ON p.client_id = c.id
             WHERE t.user_id = %d AND t.end_time IS NOT NULL
             ORDER BY t.start_time DESC
             LIMIT %d",
            $user_id,
            $limit
        ));
        
        // Group by date (convert UTC to local date)
        $grouped = array();
        foreach ($entries as $entry) {
            $date = self::get_local_date($entry->start_time);
            $date_label = self::format_date_label($date);
            
            if (!isset($grouped[$date])) {
                $grouped[$date] = array(
                    'label' => $date_label,
                    'entries' => array(),
                    'total_seconds' => 0
                );
            }
            $grouped[$date]['entries'][] = $entry;
            $grouped[$date]['total_seconds'] += $entry->duration;
        }
        
        return $grouped;
    }
    
    /**
     * Format date label for display
     *
     * @param string $date Date in Y-m-d format
     * @return string Formatted label
     */
    private static function format_date_label($date) {
        $today = current_time('Y-m-d');
        $yesterday = gmdate('Y-m-d', strtotime('-1 day', strtotime($today)));
        
        if ($date === $today) {
            return __('Today', 'freelance-studio');
        } elseif ($date === $yesterday) {
            return __('Yesterday', 'freelance-studio');
        } else {
            return date_i18n('l, M j', strtotime($date));
        }
    }

    /**
     * Get the currently running time entry for the current user
     *
     * @return object|null Running time entry or null
     */
    public static function get_running() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'fs_time_entries';
        $projects_table = $wpdb->prefix . 'fs_projects';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $user_id = get_current_user_id();
        
        $entry = $wpdb->get_row($wpdb->prepare(
            "SELECT t.*, p.name as project_name, c.name as client_name, c.company_name
             FROM $table_name t
             LEFT JOIN $projects_table p ON t.project_id = p.id
             LEFT JOIN $clients_table c ON p.client_id = c.id
             WHERE t.user_id = %d AND t.end_time IS NULL
             ORDER BY t.start_time DESC
             LIMIT 1",
            $user_id
        ));
        
        if ($entry) {
            // Calculate current duration including paused time
            $paused_duration = intval($entry->paused_duration);
            
            if ($entry->paused_at !== null) {
                // Timer is paused - duration is just the paused_duration
                $entry->current_duration = $paused_duration;
                $entry->is_paused = true;
            } else {
                // Timer is running - calculate elapsed time
                // start_time is stored in UTC, use UTC for comparison
                $start_utc = strtotime($entry->start_time . ' UTC');
                $now_utc = time();
                $current_session = $now_utc - $start_utc;
                $entry->current_duration = max(0, $current_session) + $paused_duration;
                $entry->is_paused = false;
            }
        }
        
        return $entry;
    }

    /**
     * Format duration for display
     *
     * @param int $seconds Duration in seconds
     * @param string $format 'hms' for HH:MM:SS, 'decimal' for decimal hours
     * @return string Formatted duration
     */
    public static function format_duration($seconds, $format = 'hms') {
        if ($format === 'decimal') {
            return number_format($seconds / 3600, 2);
        }

        $hours = floor($seconds / 3600);
        $minutes = floor(($seconds % 3600) / 60);
        $secs = $seconds % 60;

        return sprintf('%02d:%02d:%02d', $hours, $minutes, $secs);
    }

    /**
     * Parse duration string to seconds
     *
     * @param string $duration Duration string (HH:MM:SS or HH:MM or decimal)
     * @return int Seconds
     */
    public static function parse_duration($duration) {
        // Check if it's a decimal (hours)
        if (is_numeric($duration)) {
            return intval(floatval($duration) * 3600);
        }

        // Parse HH:MM:SS or HH:MM format
        $parts = explode(':', $duration);
        $seconds = 0;

        if (count($parts) >= 2) {
            $seconds += intval($parts[0]) * 3600; // hours
            $seconds += intval($parts[1]) * 60;   // minutes
            if (isset($parts[2])) {
                $seconds += intval($parts[2]);    // seconds
            }
        }

        return $seconds;
    }
}
