<?php
/**
 * Retainer Period Model
 * Tracks monthly retainer usage and rollover hours
 *
 * @package Freelance_Studio
 * @since 3.8.0
 */

class FS_Retainer_Period {

    /**
     * Get a single retainer period by ID
     *
     * @param int $id Period ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
    }

    /**
     * Get all retainer periods for a project
     *
     * @param int   $project_id Project ID
     * @param array $args       Optional arguments
     * @return array
     */
    public static function get_by_project($project_id, $args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        $defaults = array(
            'orderby' => 'period_start',
            'order' => 'DESC',
            'limit' => null,
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $query = "SELECT * FROM {$table} WHERE project_id = %d ORDER BY {$args['orderby']} {$args['order']}";
        
        if ($args['limit']) {
            $query .= $wpdb->prepare(" LIMIT %d", $args['limit']);
        }
        
        return $wpdb->get_results($wpdb->prepare($query, $project_id)); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get current period for a project
     *
     * @param int $project_id Project ID
     * @return object|null
     */
    public static function get_current_period($project_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        // Use wp_date() so timezone-aware sites (e.g. UTC-5) get the correct local date
        $today = wp_date('Y-m-d');
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE project_id = %d 
             AND period_start <= %s 
             AND period_end >= %s 
             LIMIT 1",
            $project_id,
            $today,
            $today
        ));
    }

    /**
     * Create a new retainer period
     *
     * @param array $data Period data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        $insert_data = array(
                'project_id' => intval($data['project_id']),
                'period_start' => sanitize_text_field($data['period_start']),
                'period_end' => sanitize_text_field($data['period_end']),
                'hours_included' => floatval($data['hours_included'] ?? 0),
                'hours_used' => floatval($data['hours_used'] ?? 0),
                'hours_rollover_in' => floatval($data['hours_rollover_in'] ?? 0),
                'hours_rollover_out' => floatval($data['hours_rollover_out'] ?? 0),
                'amount' => floatval($data['amount'] ?? 0),
                'invoiced' => intval($data['invoiced'] ?? 0),
                'invoice_id' => !empty($data['invoice_id']) ? intval($data['invoice_id']) : null,
                'notes' => isset($data['notes']) ? sanitize_textarea_field($data['notes']) : '',
        );
        
        $result = $wpdb->insert(
            $table,
            $insert_data,
            array('%d', '%s', '%s', '%f', '%f', '%f', '%f', '%f', '%d', '%d', '%s')
        );
        
        if ($result === false) {
            error_log('FS_Retainer_Period::create failed: ' . $wpdb->last_error);
            return false;
        }
        
        $new_id = $wpdb->insert_id;
        FS_DB_Helper::fix_null_fields($table, $new_id, array(
            'invoice_id' => $insert_data['invoice_id'],
        ));
        
        return $new_id;
    }

    /**
     * Update a retainer period
     *
     * @param int   $id   Period ID
     * @param array $data Data to update
     * @return bool
     */
    public static function update($id, $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        $update_data = array();
        $format = array();
        
        if (isset($data['hours_used'])) {
            $update_data['hours_used'] = floatval($data['hours_used']);
            $format[] = '%f';
        }
        
        if (isset($data['hours_rollover_out'])) {
            $update_data['hours_rollover_out'] = floatval($data['hours_rollover_out']);
            $format[] = '%f';
        }
        
        if (isset($data['invoiced'])) {
            $update_data['invoiced'] = intval($data['invoiced']);
            $format[] = '%d';
        }
        
        if (isset($data['invoice_id'])) {
            $update_data['invoice_id'] = $data['invoice_id'] ? intval($data['invoice_id']) : null;
            $format[] = '%d';
        }
        
        if (isset($data['notes'])) {
            $update_data['notes'] = sanitize_textarea_field($data['notes']);
            $format[] = '%s';
        }
        
        if (empty($update_data)) {
            return true;
        }
        
        // Track nullable foreign keys
        $nullable_fks = array();
        if (array_key_exists('invoice_id', $update_data) && $update_data['invoice_id'] === null) {
            $nullable_fks['invoice_id'] = null;
            $idx = array_search('invoice_id', array_keys($update_data));
            unset($update_data['invoice_id']);
            $format = array_values(array_diff_key($format, array($idx => true)));
        }
        
        $result = true;
        if (!empty($update_data)) {
            $result = $wpdb->update(
                $table,
                $update_data,
                array('id' => $id),
                $format,
                array('%d')
            ) !== false;
        }
        
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        return $result;
    }

    /**
     * Delete a retainer period
     *
     * @param int $id Period ID
     * @return bool
     */
    public static function delete($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        return $wpdb->delete($table, array('id' => $id), array('%d')) !== false;
    }

    /**
     * Get retainer summary for a project
     * Calculates total hours used, remaining, rollover, etc.
     *
     * @param int $project_id Project ID
     * @return object Summary object
     */
    public static function get_project_summary($project_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        // Check if table exists first to avoid errors
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table}'");
        if (!$table_exists) {
            // Table doesn't exist - return basic summary from project data
            $project = FS_Project::get($project_id);
            if (!$project || !$project->is_retainer) {
                return null;
            }
            
            $summary = new stdClass();
            $summary->project_id = $project_id;
            $summary->retainer_hours = floatval($project->retainer_hours);
            $summary->retainer_amount = floatval($project->retainer_amount);
            $summary->total_periods = 0;
            $summary->total_hours_used = 0;
            $summary->total_amount = 0;
            $summary->current_period = null;
            $summary->current_hours_included = floatval($project->retainer_hours);
            $summary->current_hours_used = 0;
            $summary->current_hours_remaining = floatval($project->retainer_hours);
            $summary->current_percentage_used = 0;
            return $summary;
        }
        
        $project = FS_Project::get($project_id);
        if (!$project || !$project->is_retainer) {
            return null;
        }
        
        // Get current period
        $current_period = self::get_current_period($project_id);
        
        // Get total hours used across all periods
        $totals = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                SUM(hours_used) as total_hours_used,
                SUM(amount) as total_amount,
                COUNT(*) as total_periods
             FROM {$table} 
             WHERE project_id = %d",
            $project_id
        ));
        
        $summary = new stdClass();
        $summary->project_id = $project_id;
        $summary->retainer_hours = floatval($project->retainer_hours);
        $summary->retainer_amount = floatval($project->retainer_amount);
        $summary->total_periods = intval($totals->total_periods ?? 0);
        $summary->total_hours_used = floatval($totals->total_hours_used ?? 0);
        $summary->total_amount = floatval($totals->total_amount ?? 0);
        
        if ($current_period) {
            $summary->current_period = $current_period;
            $summary->current_hours_included = floatval($current_period->hours_included) + floatval($current_period->hours_rollover_in);
            $summary->current_hours_used = floatval($current_period->hours_used);
            $summary->current_hours_remaining = $summary->current_hours_included - $summary->current_hours_used;
            $summary->current_percentage_used = $summary->current_hours_included > 0 
                ? round(($summary->current_hours_used / $summary->current_hours_included) * 100, 1) 
                : 0;
        } else {
            $summary->current_period = null;
            $summary->current_hours_included = floatval($project->retainer_hours);
            $summary->current_hours_used = 0;
            $summary->current_hours_remaining = floatval($project->retainer_hours);
            $summary->current_percentage_used = 0;
        }
        
        return $summary;
    }

    /**
     * Generate a new period for a retainer project
     * Handles rollover from previous period
     *
     * @param int    $project_id   Project ID
     * @param string $period_start Start date (Y-m-d)
     * @param string $period_end   End date (Y-m-d)
     * @return int|false New period ID or false on failure
     */
    public static function generate_period($project_id, $period_start, $period_end) {
        $project = FS_Project::get($project_id);
        if (!$project || !$project->is_retainer) {
            return false;
        }
        
        // Get previous period to calculate rollover
        global $wpdb;
        $table = $wpdb->prefix . 'fs_retainer_periods';
        
        $previous_period = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} 
             WHERE project_id = %d 
             AND period_end < %s 
             ORDER BY period_end DESC 
             LIMIT 1",
            $project_id,
            $period_start
        ));
        
        $rollover_in = 0;
        if ($previous_period) {
            // Calculate unused hours from previous period
            $previous_remaining = floatval($previous_period->hours_included) 
                + floatval($previous_period->hours_rollover_in) 
                - floatval($previous_period->hours_used);
            
            if ($previous_remaining > 0) {
                $rollover_in = $previous_remaining;
                
                // Update previous period's rollover_out
                self::update($previous_period->id, array(
                    'hours_rollover_out' => $rollover_in
                ));
            }
        }
        
        return self::create(array(
            'project_id' => $project_id,
            'period_start' => $period_start,
            'period_end' => $period_end,
            'hours_included' => $project->retainer_hours,
            'hours_used' => 0,
            'hours_rollover_in' => $rollover_in,
            'amount' => $project->retainer_amount,
        ));
    }

    /**
     * Update hours used from time entries
     * Called when time entries are added/updated for a retainer project
     *
     * @param int $project_id Project ID
     * @return bool
     */
    public static function sync_hours_from_time_entries($project_id) {
        $current_period = self::get_current_period($project_id);
        
        if (!$current_period) {
            return false;
        }
        
        // Get sum of time entries for this period (with rounding applied)
        global $wpdb;
        $time_table = $wpdb->prefix . 'fs_time_entries';
        
        // Apply user's time rounding setting in SQL
        $rounding_minutes = intval(get_option('fs_time_rounding', 0));
        $minimum_minutes = intval(get_option('fs_minimum_billable', 0));
        $dur_expr = 'duration';
        if ($minimum_minutes > 0) {
            $min_sec = $minimum_minutes * 60;
            $dur_expr = "GREATEST(duration, {$min_sec})";
        }
        if ($rounding_minutes > 0) {
            $rnd_sec = $rounding_minutes * 60;
            $dur_expr = "CEIL({$dur_expr} / {$rnd_sec}) * {$rnd_sec}";
        }
        
        $hours_used = $wpdb->get_var($wpdb->prepare( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            "SELECT SUM({$dur_expr}) / 3600 
             FROM {$time_table} 
             WHERE project_id = %d 
             AND DATE(start_time) >= %s 
             AND DATE(start_time) <= %s 
             AND is_running = 0",
            $project_id,
            $current_period->period_start,
            $current_period->period_end
        ));
        
        return self::update($current_period->id, array(
            'hours_used' => floatval($hours_used)
        ));
    }
}
