<?php
/**
 * Milestone Model
 *
 * @package Freelance_Studio
 */

class FS_Milestone {

    const STATUS_PENDING = 'pending';
    const STATUS_IN_PROGRESS = 'in_progress';
    const STATUS_COMPLETED = 'completed';
    const STATUS_CANCELLED = 'cancelled';

    /**
     * Get status labels
     */
    public static function get_statuses() {
        return array(
            self::STATUS_PENDING => __('Pending', 'freelance-studio'),
            self::STATUS_IN_PROGRESS => __('In Progress', 'freelance-studio'),
            self::STATUS_COMPLETED => __('Completed', 'freelance-studio'),
            self::STATUS_CANCELLED => __('Cancelled', 'freelance-studio'),
        );
    }

    /**
     * Get a single milestone by ID
     */
    public static function get($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT m.*, p.name as project_name, p.client_id
             FROM {$table} m
             LEFT JOIN {$wpdb->prefix}fs_projects p ON m.project_id = p.id
             WHERE m.id = %d",
            $id
        ));
    }

    /**
     * Get all milestones with filters
     */
    public static function get_all($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        $defaults = array(
            'project_id' => null,
            'status' => null,
            'orderby' => 'position',
            'order' => 'ASC',
        );
        
        $args = wp_parse_args($args, $defaults);
        $where = array('1=1');
        
        if ($args['project_id']) {
            $where[] = $wpdb->prepare('m.project_id = %d', $args['project_id']);
        }
        
        if ($args['status']) {
            if (is_array($args['status'])) {
                $placeholders = implode(',', array_fill(0, count($args['status']), '%s'));
                $where[] = $wpdb->prepare("m.status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('m.status = %s', $args['status']);
            }
        }
        
        $where_clause = implode(' AND ', $where);
        $orderby = in_array($args['orderby'], array('position', 'due_date', 'name', 'created_at')) ? $args['orderby'] : 'position';
        $order = strtoupper($args['order']) === 'DESC' ? 'DESC' : 'ASC';
        
        return $wpdb->get_results(
            "SELECT m.*, p.name as project_name
             FROM {$table} m
             LEFT JOIN {$wpdb->prefix}fs_projects p ON m.project_id = p.id
             WHERE {$where_clause}
             ORDER BY m.{$orderby} {$order}"
        );
    }

    /**
     * Get milestones by project
     */
    public static function get_by_project($project_id) {
        return self::get_all(array('project_id' => $project_id));
    }

    /**
     * Get upcoming milestones (due within X days)
     */
    public static function get_upcoming($days = 7) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        $future_date = gmdate('Y-m-d', strtotime("+{$days} days"));
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT m.*, p.name as project_name, c.name as client_name, c.company_name
             FROM {$table} m
             LEFT JOIN {$wpdb->prefix}fs_projects p ON m.project_id = p.id
             LEFT JOIN {$wpdb->prefix}fs_clients c ON p.client_id = c.id
             WHERE m.status IN ('pending', 'in_progress')
               AND m.due_date IS NOT NULL
               AND m.due_date <= %s
             ORDER BY m.due_date ASC",
            $future_date
        ));
    }

    /**
     * Get overdue milestones
     */
    public static function get_overdue() {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        $today = current_time('Y-m-d');
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT m.*, p.name as project_name, c.name as client_name, c.company_name
             FROM {$table} m
             LEFT JOIN {$wpdb->prefix}fs_projects p ON m.project_id = p.id
             LEFT JOIN {$wpdb->prefix}fs_clients c ON p.client_id = c.id
             WHERE m.status IN ('pending', 'in_progress')
               AND m.due_date IS NOT NULL
               AND m.due_date < %s
             ORDER BY m.due_date ASC",
            $today
        ));
    }

    /**
     * Create a new milestone
     */
    public static function create($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        // Get next position for this project
        $position = 0;
        if (!empty($data['project_id'])) {
            $max_position = $wpdb->get_var($wpdb->prepare(
                "SELECT MAX(position) FROM {$table} WHERE project_id = %d",
                $data['project_id']
            ));
            $position = $max_position !== null ? $max_position + 1 : 0;
        }
        
        $result = $wpdb->insert(
            $table,
            array(
                'project_id' => intval($data['project_id']),
                'name' => sanitize_text_field($data['name']),
                'description' => isset($data['description']) ? sanitize_textarea_field($data['description']) : null,
                'due_date' => !empty($data['due_date']) ? $data['due_date'] : null,
                'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_PENDING,
                'invoice_on_completion' => isset($data['invoice_on_completion']) ? intval($data['invoice_on_completion']) : 0,
                'invoice_amount' => isset($data['invoice_amount']) ? floatval($data['invoice_amount']) : null,
                'invoice_percentage' => isset($data['invoice_percentage']) ? floatval($data['invoice_percentage']) : null,
                'position' => isset($data['position']) ? intval($data['position']) : $position,
            ),
            array('%d', '%s', '%s', '%s', '%s', '%d', '%f', '%f', '%d')
        );
        
        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Update a milestone
     */
    public static function update($id, $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        $update_data = array();
        $format = array();
        
        if (isset($data['name'])) {
            $update_data['name'] = sanitize_text_field($data['name']);
            $format[] = '%s';
        }
        
        if (array_key_exists('description', $data)) {
            $update_data['description'] = $data['description'] ? sanitize_textarea_field($data['description']) : null;
            $format[] = '%s';
        }
        
        if (array_key_exists('due_date', $data)) {
            $update_data['due_date'] = !empty($data['due_date']) ? $data['due_date'] : null;
            $format[] = '%s';
        }
        
        if (array_key_exists('completed_date', $data)) {
            $update_data['completed_date'] = !empty($data['completed_date']) ? $data['completed_date'] : null;
            $format[] = '%s';
        }
        
        if (isset($data['status'])) {
            $update_data['status'] = sanitize_text_field($data['status']);
            $format[] = '%s';
            
            // Auto-set completed_date when marking as completed
            if ($data['status'] === self::STATUS_COMPLETED && !isset($data['completed_date'])) {
                $update_data['completed_date'] = current_time('Y-m-d');
                $format[] = '%s';
            }
        }
        
        if (isset($data['invoice_on_completion'])) {
            $update_data['invoice_on_completion'] = intval($data['invoice_on_completion']);
            $format[] = '%d';
        }
        
        if (array_key_exists('invoice_amount', $data)) {
            $update_data['invoice_amount'] = $data['invoice_amount'] ? floatval($data['invoice_amount']) : null;
            $format[] = '%f';
        }
        
        if (array_key_exists('invoice_percentage', $data)) {
            $update_data['invoice_percentage'] = $data['invoice_percentage'] ? floatval($data['invoice_percentage']) : null;
            $format[] = '%f';
        }
        
        if (array_key_exists('invoice_id', $data)) {
            $update_data['invoice_id'] = $data['invoice_id'] ? intval($data['invoice_id']) : null;
            $format[] = '%d';
        }
        
        if (isset($data['position'])) {
            $update_data['position'] = intval($data['position']);
            $format[] = '%d';
        }
        
        if (empty($update_data)) {
            return false;
        }
        
        // 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 = false;
        if (!empty($update_data)) {
            $result = $wpdb->update($table, $update_data, array('id' => $id), $format, array('%d'));
        }
        
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        return $result;
    }

    /**
     * Delete a milestone
     */
    public static function delete($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        // Also delete associated tasks
        $wpdb->delete($wpdb->prefix . 'fs_tasks', array('milestone_id' => $id), array('%d'));
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Mark milestone as complete and optionally generate invoice
     */
    public static function complete($id) {
        $milestone = self::get($id);
        
        if (!$milestone) {
            return false;
        }
        
        // Update milestone status
        $updated = self::update($id, array(
            'status' => self::STATUS_COMPLETED,
            'completed_date' => current_time('Y-m-d'),
        ));
        
        if (!$updated) {
            return false;
        }
        
        // Generate invoice if configured
        if ($milestone->invoice_on_completion && !$milestone->invoice_id) {
            $invoice_id = self::generate_invoice($milestone);
            if ($invoice_id) {
                self::update($id, array('invoice_id' => $invoice_id));
                return $invoice_id;
            }
        }
        
        return true;
    }

    /**
     * Generate invoice for completed milestone
     */
    private static function generate_invoice($milestone) {
        $project = FS_Project::get($milestone->project_id);
        if (!$project) {
            return false;
        }
        
        // Calculate invoice amount
        $amount = 0;
        if ($milestone->invoice_amount) {
            $amount = $milestone->invoice_amount;
        } elseif ($milestone->invoice_percentage) {
            // Use budget if set, otherwise fall back to project total (from services)
            $base_amount = floatval($project->budget);
            if ($base_amount <= 0) {
                $base_amount = floatval($project->total_amount ?? 0);
            }
            if ($base_amount > 0) {
                $amount = $base_amount * ($milestone->invoice_percentage / 100);
            }
        }
        
        if ($amount <= 0) {
            return false;
        }
        
        $items = array(
            array(
                /* translators: 1. date/time */
                'description' => sprintf(__('Milestone: %s', 'freelance-studio'), $milestone->name),
                'quantity' => 1,
                'rate' => $amount,
                'amount' => $amount,
            ),
        );
        
        $payment_terms = get_option('fs_default_payment_terms', 30);
        $due_date = gmdate('Y-m-d', strtotime("+{$payment_terms} days"));
        
        $invoice_data = array(
            'client_id' => $project->client_id,
            'project_id' => $project->id,
            'invoice_number' => FS_Invoice::generate_invoice_number(),
            'invoice_date' => current_time('Y-m-d'),
            'due_date' => $due_date,
            'status' => FS_Invoice::STATUS_DRAFT,
            'items' => $items,
            'subtotal' => $amount,
            'tax_rate' => 0,
            /* translators: 1. date/time */
            'notes' => sprintf(__('Invoice for milestone: %s', 'freelance-studio'), $milestone->name),
        );
        
        return FS_Invoice::create($invoice_data);
    }

    /**
     * Reorder milestones
     */
    public static function reorder($milestone_ids) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        foreach ($milestone_ids as $position => $id) {
            $wpdb->update(
                $table,
                array('position' => $position),
                array('id' => $id),
                array('%d'),
                array('%d')
            );
        }
        
        return true;
    }

    /**
     * Count milestones
     */
    public static function count($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_milestones';
        
        $where = array('1=1');
        
        if (!empty($args['project_id'])) {
            $where[] = $wpdb->prepare('project_id = %d', $args['project_id']);
        }
        
        if (!empty($args['status'])) {
            $where[] = $wpdb->prepare('status = %s', $args['status']);
        }
        
        $where_clause = implode(' AND ', $where);
        
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table} WHERE {$where_clause}");
    }

    /**
     * Get progress percentage for a project
     */
    public static function get_project_progress($project_id) {
        $total = self::count(array('project_id' => $project_id));
        if ($total === 0) {
            return 0;
        }
        
        $completed = self::count(array('project_id' => $project_id, 'status' => self::STATUS_COMPLETED));
        
        return round(($completed / $total) * 100);
    }
}
