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

class FS_Task {

    const STATUS_PENDING = 'pending';
    const STATUS_IN_PROGRESS = 'in_progress';
    const STATUS_NEEDS_INFO = 'needs_info';
    const STATUS_WITH_CLIENT = 'with_client';
    const STATUS_COMPLETED = 'completed';
    const STATUS_CANCELLED = 'cancelled';
    const STATUS_REQUESTED = 'requested';
    const STATUS_ARCHIVED = 'archived';

    const PRIORITY_LOW = 'low';
    const PRIORITY_MEDIUM = 'medium';
    const PRIORITY_HIGH = 'high';
    const PRIORITY_URGENT = 'urgent';

    const ASSIGNED_FREELANCER = 'freelancer';
    const ASSIGNED_CLIENT = 'client';

    /**
     * Get status labels
     */
    public static function get_statuses() {
        return array(
            self::STATUS_REQUESTED => __('Requested', 'freelance-studio'),
            self::STATUS_PENDING => __('Pending', 'freelance-studio'),
            self::STATUS_IN_PROGRESS => __('In Progress', 'freelance-studio'),
            self::STATUS_NEEDS_INFO => __('Needs Information', 'freelance-studio'),
            self::STATUS_WITH_CLIENT => __('With Client', 'freelance-studio'),
            self::STATUS_COMPLETED => __('Completed', 'freelance-studio'),
            self::STATUS_ARCHIVED => __('Archived', 'freelance-studio'),
            self::STATUS_CANCELLED => __('Cancelled', 'freelance-studio'),
        );
    }

    /**
     * Get priority labels
     */
    public static function get_priorities() {
        return array(
            self::PRIORITY_LOW => __('Low', 'freelance-studio'),
            self::PRIORITY_MEDIUM => __('Medium', 'freelance-studio'),
            self::PRIORITY_HIGH => __('High', 'freelance-studio'),
            self::PRIORITY_URGENT => __('Urgent', 'freelance-studio'),
        );
    }

    /**
     * Get recurring frequency options
     */
    public static function get_recurring_frequencies() {
        return array(
            'daily' => __('Daily', 'freelance-studio'),
            'weekly' => __('Weekly', 'freelance-studio'),
            'biweekly' => __('Every 2 Weeks', 'freelance-studio'),
            'monthly' => __('Monthly', 'freelance-studio'),
            'quarterly' => __('Quarterly', 'freelance-studio'),
        );
    }

    /**
     * Get assignment options
     */
    public static function get_assignees() {
        return array(
            self::ASSIGNED_FREELANCER => __('Me (Freelancer)', 'freelance-studio'),
            self::ASSIGNED_CLIENT => __('Client', 'freelance-studio'),
        );
    }

    /**
     * Get a single task by ID
     */
    public static function get($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT t.*, 
                    p.name as project_name, 
                    COALESCE(t.client_id, p.client_id) as resolved_client_id,
                    m.name as milestone_name,
                    c.company_name as client_company_name,
                    c.name as client_name
             FROM {$table} t
             LEFT JOIN {$wpdb->prefix}fs_projects p ON t.project_id = p.id
             LEFT JOIN {$wpdb->prefix}fs_milestones m ON t.milestone_id = m.id
             LEFT JOIN {$wpdb->prefix}fs_clients c ON COALESCE(t.client_id, p.client_id) = c.id
             WHERE t.id = %d",
            $id
        ));
    }

    /**
     * Get all tasks with filters
     */
    public static function get_all($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        $defaults = array(
            'project_id' => null,
            'client_id' => null,
            'milestone_id' => null,
            'status' => null,
            'priority' => null,
            'task_type' => null, // 'project', 'client', or null for all
            'orderby' => 'position',
            'order' => 'ASC',
            'limit' => null,
            'offset' => null,
            'exclude_archived_projects' => false,
            'exclude_orphaned' => false, // v3.8.1 - Exclude tasks from deleted projects
            'exclude_internal' => false, // Exclude internal client tasks
            'exclude_archived' => false, // Exclude archived tasks
            'exclude_recurring_children' => false, // Exclude child instances of recurring tasks (for dropdowns)
            'smart_recurring' => false, // Show only current active instance per recurring parent (for task lists)
        );
        
        $args = wp_parse_args($args, $defaults);
        $where = array('1=1');
        
        if ($args['project_id']) {
            $where[] = $wpdb->prepare('t.project_id = %d', $args['project_id']);
        }
        
        if ($args['client_id']) {
            // Get tasks directly assigned to client OR tasks from client's projects
            $where[] = $wpdb->prepare('(t.client_id = %d OR p.client_id = %d)', $args['client_id'], $args['client_id']);
        }
        
        if ($args['milestone_id']) {
            $where[] = $wpdb->prepare('t.milestone_id = %d', $args['milestone_id']);
        }
        
        if ($args['status']) {
            if (is_array($args['status'])) {
                $placeholders = implode(',', array_fill(0, count($args['status']), '%s'));
                $where[] = $wpdb->prepare("t.status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('t.status = %s', $args['status']);
            }
        }
        
        if ($args['priority']) {
            $where[] = $wpdb->prepare('t.priority = %s', $args['priority']);
        }
        
        // Filter by task type
        if ($args['task_type'] === 'project') {
            $where[] = 't.project_id IS NOT NULL';
        } elseif ($args['task_type'] === 'client') {
            $where[] = 't.project_id IS NULL AND t.client_id IS NOT NULL';
        }
        
        // Exclude tasks from completed/archived projects (but include generic tasks with no project)
        if ($args['exclude_archived_projects']) {
            $where[] = "(t.project_id IS NULL OR p.status NOT IN ('completed', 'archived'))";
        }
        
        // v3.8.1 - Exclude orphaned tasks (project_id is set but project was deleted)
        if ($args['exclude_orphaned']) {
            $where[] = "(t.project_id IS NULL OR p.id IS NOT NULL)";
        }
        
        // Exclude internal client tasks
        if ($args['exclude_internal']) {
            $where[] = "(c.is_internal IS NULL OR c.is_internal = 0)";
        }
        
        // Exclude archived tasks
        if ($args['exclude_archived']) {
            $where[] = "t.status != 'archived'";
        }
        
        // Exclude child instances of recurring tasks (show only parents)
        if ($args['exclude_recurring_children']) {
            $where[] = "t.parent_task_id IS NULL";
        }
        
        // Smart recurring: show only the current active child per recurring parent
        // - Non-recurring tasks: always shown (unless hide_until_due)
        // - Recurring parents (parent_task_id IS NULL, is_recurring=1): shown only when no active children exist
        // - Recurring children: only show the one with the nearest due_date that isn't completed
        // - Respects hide_until_due: if enabled, only show if due_date <= today (applies to all tasks)
        if ($args['smart_recurring']) {
            $today = current_time('Y-m-d');
            $where[] = "(
                (t.parent_task_id IS NULL AND t.is_recurring = 0)
                OR (t.parent_task_id IS NULL AND t.is_recurring = 1 AND NOT EXISTS (
                    SELECT 1 FROM {$table} child WHERE child.parent_task_id = t.id AND child.status NOT IN ('completed', 'cancelled')
                ))
                OR (t.parent_task_id IS NOT NULL AND t.status NOT IN ('completed', 'cancelled') AND t.id = (
                    SELECT sub.id FROM {$table} sub 
                    WHERE sub.parent_task_id = t.parent_task_id 
                      AND sub.status NOT IN ('completed', 'cancelled')
                    ORDER BY sub.due_date ASC, sub.id ASC 
                    LIMIT 1
                ))
            )";
            // hide_until_due applies to all tasks shown via smart_recurring
            $where[] = "(t.hide_until_due = 0 OR t.due_date IS NULL OR t.due_date <= '{$today}')";
        }
        
        $where_clause = implode(' AND ', $where);
        $orderby = in_array($args['orderby'], array('position', 'due_date', 'title', 'priority', 'status', 'created_at', 'client_name')) ? $args['orderby'] : 'position';
        $order = strtoupper($args['order']) === 'DESC' ? 'DESC' : 'ASC';
        
        // Map orderby to actual SQL column references
        $orderby_map = array(
            'client_name' => 'COALESCE(c.company_name, c.name)',
            'status' => "CASE t.status 
                WHEN 'requested' THEN 1 
                WHEN 'pending' THEN 2 
                WHEN 'in_progress' THEN 3 
                WHEN 'needs_info' THEN 4 
                WHEN 'with_client' THEN 5 
                WHEN 'completed' THEN 6 
                WHEN 'archived' THEN 7 
                WHEN 'cancelled' THEN 8 
                ELSE 9 END",
        );
        $orderby_sql = isset($orderby_map[$orderby]) ? $orderby_map[$orderby] : "t.{$orderby}";
        
        $limit_clause = $args['limit'] ? $wpdb->prepare('LIMIT %d', $args['limit']) : '';
        if ($limit_clause && $args['offset']) {
            $limit_clause = $wpdb->prepare('LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        return $wpdb->get_results(
            "SELECT t.*, 
                    p.name as project_name, 
                    p.status as project_status, 
                    m.name as milestone_name,
                    COALESCE(t.client_id, p.client_id) as resolved_client_id,
                    c.company_name as client_company_name,
                    c.name as client_name,
                    c.is_internal as client_is_internal
             FROM {$table} t
             LEFT JOIN {$wpdb->prefix}fs_projects p ON t.project_id = p.id
             LEFT JOIN {$wpdb->prefix}fs_milestones m ON t.milestone_id = m.id
             LEFT JOIN {$wpdb->prefix}fs_clients c ON COALESCE(t.client_id, p.client_id) = c.id
             WHERE {$where_clause}
             ORDER BY {$orderby_sql} {$order}
             {$limit_clause}"
        );
    }

    /**
     * Count tasks matching the same filters as get_all (for pagination).
     * Accepts the same $args as get_all, ignores limit/offset/orderby.
     */
    public static function count_all($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';

        $defaults = array(
            'project_id'               => null,
            'client_id'                => null,
            'milestone_id'             => null,
            'status'                   => null,
            'priority'                 => null,
            'task_type'                => null,
            'exclude_archived_projects'=> false,
            'exclude_orphaned'         => false,
            'exclude_internal'         => false,
            'exclude_archived'         => false,
            'exclude_recurring_children'=> false,
            'smart_recurring'          => false,
        );
        $args = wp_parse_args($args, $defaults);
        $where = array('1=1');

        if ($args['project_id']) {
            $where[] = $wpdb->prepare('t.project_id = %d', $args['project_id']);
        }
        if ($args['client_id']) {
            $where[] = $wpdb->prepare('(t.client_id = %d OR p.client_id = %d)', $args['client_id'], $args['client_id']);
        }
        if ($args['milestone_id']) {
            $where[] = $wpdb->prepare('t.milestone_id = %d', $args['milestone_id']);
        }
        if ($args['status']) {
            if (is_array($args['status'])) {
                $placeholders = implode(',', array_fill(0, count($args['status']), '%s'));
                $where[] = $wpdb->prepare("t.status IN ($placeholders)", $args['status']);
            } else {
                $where[] = $wpdb->prepare('t.status = %s', $args['status']);
            }
        }
        if ($args['priority']) {
            $where[] = $wpdb->prepare('t.priority = %s', $args['priority']);
        }
        if ($args['task_type'] === 'project') {
            $where[] = 't.project_id IS NOT NULL';
        } elseif ($args['task_type'] === 'client') {
            $where[] = 't.project_id IS NULL AND t.client_id IS NOT NULL';
        }
        if ($args['exclude_archived_projects']) {
            $where[] = "(t.project_id IS NULL OR p.status NOT IN ('completed', 'archived'))";
        }
        if ($args['exclude_orphaned']) {
            $where[] = "(t.project_id IS NULL OR p.id IS NOT NULL)";
        }
        if ($args['exclude_internal']) {
            $where[] = "(c.is_internal IS NULL OR c.is_internal = 0)";
        }
        if ($args['exclude_archived']) {
            $where[] = "t.status != 'archived'";
        }
        if ($args['exclude_recurring_children']) {
            $where[] = "t.parent_task_id IS NULL";
        }
        if ($args['smart_recurring']) {
            $today = current_time('Y-m-d');
            $where[] = "(
                (t.parent_task_id IS NULL AND t.is_recurring = 0)
                OR (t.parent_task_id IS NULL AND t.is_recurring = 1 AND NOT EXISTS (
                    SELECT 1 FROM {$table} child WHERE child.parent_task_id = t.id AND child.status NOT IN ('completed', 'cancelled')
                ))
                OR (t.parent_task_id IS NOT NULL AND t.status NOT IN ('completed', 'cancelled') AND t.id = (
                    SELECT sub.id FROM {$table} sub
                    WHERE sub.parent_task_id = t.parent_task_id
                      AND sub.status NOT IN ('completed', 'cancelled')
                    ORDER BY sub.due_date ASC, sub.id ASC
                    LIMIT 1
                ))
            )";
            $where[] = "(t.hide_until_due = 0 OR t.due_date IS NULL OR t.due_date <= '{$today}')";
        }

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

        return (int) $wpdb->get_var(
            "SELECT COUNT(*)
             FROM {$table} t
             LEFT JOIN {$wpdb->prefix}fs_projects p ON t.project_id = p.id
             LEFT JOIN {$wpdb->prefix}fs_clients c ON COALESCE(t.client_id, p.client_id) = c.id
             WHERE {$where_clause}"
        );
    }

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

    /**
     * Get tasks by milestone
     */
    public static function get_by_milestone($milestone_id) {
        return self::get_all(array('milestone_id' => $milestone_id));
    }

    /**
     * Get tasks by client (both direct client tasks and tasks from client's projects)
     */
    public static function get_by_client($client_id, $include_project_tasks = true) {
        if ($include_project_tasks) {
            return self::get_all(array('client_id' => $client_id));
        }
        
        // Only get tasks directly assigned to client (not through projects)
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT t.*, 
                    c.company_name as client_company_name,
                    c.name as client_name,
                    c.is_internal as client_is_internal
             FROM {$table} t
             LEFT JOIN {$wpdb->prefix}fs_clients c ON t.client_id = c.id
             WHERE t.client_id = %d AND t.project_id IS NULL
             ORDER BY t.position ASC",
            $client_id
        ));
    }

    /**
     * Get all client-visible tasks across all projects for a given client
     * Used by the client portal dashboard to show tasks from all projects
     *
     * @param int $client_id Client ID
     * @return array Client-visible tasks with project info
     */
    public static function get_client_visible_tasks($client_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT t.*, 
                    p.name as project_name,
                    p.status as project_status
             FROM {$table} t
             LEFT JOIN {$wpdb->prefix}fs_projects p ON t.project_id = p.id
             WHERE p.client_id = %d 
               AND t.is_client_visible = 1
               AND p.status NOT IN ('completed', 'archived', 'cancelled')
             ORDER BY 
                CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END ASC,
                CASE WHEN t.due_date IS NULL THEN 1 ELSE 0 END ASC,
                t.due_date ASC,
                t.position ASC",
            $client_id
        ));
    }

    /**
     * Get internal tasks (tasks assigned to internal client)
     * @param array $args Additional filter arguments
     * @return array Tasks assigned to internal client
     */
    public static function get_internal_tasks($args = array()) {
        $internal_client = FS_Client::get_internal_client();
        
        if (!$internal_client) {
            return array();
        }
        
        $defaults = array(
            'client_id' => $internal_client->id,
            'status' => array('pending', 'in_progress', 'needs_info'),
            'orderby' => 'due_date',
            'order' => 'ASC',
        );
        
        $args = wp_parse_args($args, $defaults);
        
        return self::get_all($args);
    }

    /**
     * Count tasks by client
     * @param int $client_id Client ID
     * @return object Task counts (total, pending, completed)
     */
    public static function count_by_client($client_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        return $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN t.status IN ('pending', 'in_progress', 'needs_info', 'with_client') THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN t.status = 'completed' THEN 1 ELSE 0 END) as completed
             FROM {$table} t
             LEFT JOIN {$wpdb->prefix}fs_projects p ON t.project_id = p.id
             WHERE t.client_id = %d OR p.client_id = %d",
            $client_id,
            $client_id
        ));
    }

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

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

    /**
     * Create a new task
     */
    public static function create($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        // Get next position for this project/milestone
        $position = 0;
        if (!empty($data['project_id'])) {
            $where = $wpdb->prepare("project_id = %d", $data['project_id']);
            if (!empty($data['milestone_id'])) {
                $where .= $wpdb->prepare(" AND milestone_id = %d", $data['milestone_id']);
            }
            $max_position = $wpdb->get_var("SELECT MAX(position) FROM {$table} WHERE {$where}");
            $position = $max_position !== null ? $max_position + 1 : 0;
        }
        
        $insert_data = array(
                'project_id' => intval($data['project_id']),
                'milestone_id' => !empty($data['milestone_id']) ? intval($data['milestone_id']) : null,
                'template_id' => !empty($data['template_id']) ? intval($data['template_id']) : null,
                'title' => sanitize_text_field($data['title']),
                'description' => isset($data['description']) ? sanitize_textarea_field($data['description']) : null,
                'status' => isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_PENDING,
                'priority' => isset($data['priority']) ? sanitize_text_field($data['priority']) : self::PRIORITY_MEDIUM,
                'due_date' => !empty($data['due_date']) ? $data['due_date'] : null,
                'estimated_hours' => isset($data['estimated_hours']) ? floatval($data['estimated_hours']) : null,
                'assigned_to' => isset($data['assigned_to']) ? sanitize_text_field($data['assigned_to']) : self::ASSIGNED_FREELANCER,
                'is_client_visible' => isset($data['is_client_visible']) ? intval($data['is_client_visible']) : 1,
                'position' => isset($data['position']) ? intval($data['position']) : $position,
                'is_recurring' => isset($data['is_recurring']) ? intval($data['is_recurring']) : 0,
                'recurring_frequency' => !empty($data['recurring_frequency']) ? sanitize_text_field($data['recurring_frequency']) : null,
                'recurring_end_date' => !empty($data['recurring_end_date']) ? sanitize_text_field($data['recurring_end_date']) : null,
                'recurring_days' => !empty($data['recurring_days']) ? sanitize_text_field($data['recurring_days']) : null,
                'parent_task_id' => !empty($data['parent_task_id']) ? intval($data['parent_task_id']) : null,
                'hide_until_due' => isset($data['hide_until_due']) ? intval($data['hide_until_due']) : 0,
                'is_non_billable' => isset($data['is_non_billable']) ? intval($data['is_non_billable']) : 0,
        );
        
        $result = $wpdb->insert(
            $table,
            $insert_data,
            array('%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%f', '%s', '%d', '%d', '%d', '%s', '%s', '%s', '%d', '%d', '%d')
        );
        
        if ($result) {
            $new_id = $wpdb->insert_id;
            // Fix NULL foreign keys that wpdb->insert converts to 0
            FS_DB_Helper::fix_null_fields($table, $new_id, array(
                'milestone_id' => $insert_data['milestone_id'],
                'template_id' => $insert_data['template_id'],
                'parent_task_id' => $insert_data['parent_task_id'],
            ));
            return $new_id;
        }
        
        return false;
    }

    /**
     * Create a generic task (without project requirement)
     * Used for Kanban board standalone tasks and client-level tasks
     */
    public static function create_generic($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        // Get next position for status-based ordering (for generic tasks)
        $status = isset($data['status']) ? sanitize_text_field($data['status']) : self::STATUS_PENDING;
        $max_position = $wpdb->get_var($wpdb->prepare(
            "SELECT MAX(position) FROM {$table} WHERE status = %s",
            $status
        ));
        $position = $max_position !== null ? $max_position + 1 : 0;
        
        // Build insert data - project_id can be null
        $insert_data = array(
            'title' => sanitize_text_field($data['title']),
            'description' => isset($data['description']) ? sanitize_textarea_field($data['description']) : null,
            'status' => $status,
            'priority' => isset($data['priority']) ? sanitize_text_field($data['priority']) : self::PRIORITY_MEDIUM,
            'due_date' => !empty($data['due_date']) ? $data['due_date'] : null,
            'estimated_hours' => isset($data['estimated_hours']) ? floatval($data['estimated_hours']) : null,
            'assigned_to' => isset($data['assigned_to']) ? sanitize_text_field($data['assigned_to']) : self::ASSIGNED_FREELANCER,
            'is_client_visible' => isset($data['is_client_visible']) ? intval($data['is_client_visible']) : 1,
            'position' => isset($data['position']) ? intval($data['position']) : $position,
            'is_recurring' => isset($data['is_recurring']) ? intval($data['is_recurring']) : 0,
            'recurring_frequency' => !empty($data['recurring_frequency']) ? sanitize_text_field($data['recurring_frequency']) : null,
            'recurring_end_date' => !empty($data['recurring_end_date']) ? sanitize_text_field($data['recurring_end_date']) : null,
            'recurring_days' => !empty($data['recurring_days']) ? sanitize_text_field($data['recurring_days']) : null,
            'parent_task_id' => !empty($data['parent_task_id']) ? intval($data['parent_task_id']) : null,
            'hide_until_due' => isset($data['hide_until_due']) ? intval($data['hide_until_due']) : 0,
            'is_non_billable' => isset($data['is_non_billable']) ? intval($data['is_non_billable']) : 0,
        );
        
        $formats = array('%s', '%s', '%s', '%s', '%s', '%f', '%s', '%d', '%d', '%d', '%s', '%s', '%s', '%d', '%d', '%d');
        
        // Add project_id if provided
        if (!empty($data['project_id'])) {
            $insert_data['project_id'] = intval($data['project_id']);
            $formats[] = '%d';
        }
        
        // Add client_id if provided (for client-level tasks)
        if (!empty($data['client_id'])) {
            $insert_data['client_id'] = intval($data['client_id']);
            $formats[] = '%d';
        }
        
        // Add milestone_id if provided
        if (!empty($data['milestone_id'])) {
            $insert_data['milestone_id'] = intval($data['milestone_id']);
            $formats[] = '%d';
        }
        
        $result = $wpdb->insert($table, $insert_data, $formats);
        
        if ($result) {
            $new_id = $wpdb->insert_id;
            FS_DB_Helper::fix_null_fields($table, $new_id, array(
                'parent_task_id' => $insert_data['parent_task_id'] ?? null,
            ));
            return $new_id;
        }
        
        return false;
    }

    /**
     * Update a task
     */
    public static function update($id, $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        $update_data = array();
        $format = array();
        
        if (isset($data['title'])) {
            $update_data['title'] = sanitize_text_field($data['title']);
            $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('milestone_id', $data)) {
            $update_data['milestone_id'] = $data['milestone_id'] ? intval($data['milestone_id']) : null;
            $format[] = '%d';
        }
        
        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';
            }
            // Clear completed_date when moving away from completed status
            elseif ($data['status'] !== self::STATUS_COMPLETED && !isset($data['completed_date'])) {
                $update_data['completed_date'] = null;
                $format[] = '%s';
            }
        }
        
        if (isset($data['priority'])) {
            $update_data['priority'] = sanitize_text_field($data['priority']);
            $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 (array_key_exists('estimated_hours', $data)) {
            $update_data['estimated_hours'] = $data['estimated_hours'] ? floatval($data['estimated_hours']) : null;
            $format[] = '%f';
        }
        
        if (array_key_exists('actual_hours', $data)) {
            $update_data['actual_hours'] = $data['actual_hours'] ? floatval($data['actual_hours']) : null;
            $format[] = '%f';
        }
        
        if (isset($data['assigned_to'])) {
            $update_data['assigned_to'] = sanitize_text_field($data['assigned_to']);
            $format[] = '%s';
        }
        
        if (isset($data['is_client_visible'])) {
            $update_data['is_client_visible'] = intval($data['is_client_visible']);
            $format[] = '%d';
        }
        
        if (isset($data['position'])) {
            $update_data['position'] = intval($data['position']);
            $format[] = '%d';
        }
        
        // Support updating client_id for client-level tasks
        if (array_key_exists('client_id', $data)) {
            $update_data['client_id'] = $data['client_id'] ? intval($data['client_id']) : null;
            $format[] = '%d';
        }
        
        // Support updating project_id
        if (array_key_exists('project_id', $data)) {
            $update_data['project_id'] = $data['project_id'] ? intval($data['project_id']) : null;
            $format[] = '%d';
        }
        
        // Recurring task fields
        if (isset($data['is_recurring'])) {
            $update_data['is_recurring'] = intval($data['is_recurring']);
            $format[] = '%d';
        }
        if (array_key_exists('recurring_frequency', $data)) {
            $update_data['recurring_frequency'] = $data['recurring_frequency'] ? sanitize_text_field($data['recurring_frequency']) : null;
            $format[] = '%s';
        }
        if (array_key_exists('recurring_end_date', $data)) {
            $update_data['recurring_end_date'] = !empty($data['recurring_end_date']) ? sanitize_text_field($data['recurring_end_date']) : null;
            $format[] = '%s';
        }
        if (array_key_exists('recurring_days', $data)) {
            $update_data['recurring_days'] = !empty($data['recurring_days']) ? sanitize_text_field($data['recurring_days']) : null;
            $format[] = '%s';
        }
        if (array_key_exists('parent_task_id', $data)) {
            $update_data['parent_task_id'] = $data['parent_task_id'] ? intval($data['parent_task_id']) : null;
            $format[] = '%d';
        }
        
        if (array_key_exists('hide_until_due', $data)) {
            $update_data['hide_until_due'] = intval($data['hide_until_due']);
            $format[] = '%d';
        }
        
        if (array_key_exists('previous_status', $data)) {
            $update_data['previous_status'] = $data['previous_status'] ? sanitize_text_field($data['previous_status']) : null;
            $format[] = '%s';
        }
        
        if (array_key_exists('is_non_billable', $data)) {
            $update_data['is_non_billable'] = intval($data['is_non_billable']);
            $format[] = '%d';
        }
        
        if (empty($update_data)) {
            return false;
        }
        
        // Extract nullable FK fields that wpdb would convert null to 0 with %d format
        $nullable_fk_fields = array('milestone_id', 'client_id', 'project_id', 'parent_task_id');
        $nullable_fks = array();
        foreach ($nullable_fk_fields as $fk_field) {
            if (array_key_exists($fk_field, $update_data) && $update_data[$fk_field] === null) {
                $nullable_fks[$fk_field] = null;
                // Remove from update_data and format arrays
                $idx = array_search($fk_field, array_keys($update_data));
                unset($update_data[$fk_field]);
                $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'));
        }
        
        // Fix NULL foreign keys
        if (!empty($nullable_fks)) {
            FS_DB_Helper::fix_null_fields($table, $id, $nullable_fks);
        }
        
        return $result;
    }

    /**
     * Delete a task
     */
    public static function delete($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        $task = self::get($id);
        if (!$task) {
            return $wpdb->delete($table, array('id' => $id), array('%d'));
        }
        
        // Determine the parent ID for the recurring series
        $parent_id = $task->parent_task_id ? $task->parent_task_id : $task->id;
        
        // If deleting a recurring parent, also delete all child instances
        if (!$task->parent_task_id && $task->is_recurring) {
            $wpdb->delete($table, array('parent_task_id' => $id), array('%d'));
        }
        
        // If deleting a child, check if we should clean up orphaned siblings
        // (Only clean up if explicitly deleting - not when cascading from parent delete above)
        if ($task->parent_task_id) {
            // Check if parent still exists
            $parent_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$table} WHERE id = %d",
                $task->parent_task_id
            ));
            
            // If parent doesn't exist, clean up all orphaned siblings
            if (!$parent_exists) {
                $wpdb->query($wpdb->prepare(
                    "DELETE FROM {$table} WHERE parent_task_id = %d",
                    $task->parent_task_id
                ));
                // The task itself will be caught by this, so we can return
                return true;
            }
        }
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Toggle task completion
     */
    public static function toggle_complete($id) {
        $task = self::get($id);
        
        if (!$task) {
            return false;
        }
        
        if ($task->status === self::STATUS_COMPLETED) {
            return self::update($id, array(
                'status' => self::STATUS_PENDING,
                'completed_date' => null,
            ));
        } else {
            return self::update($id, array(
                'status' => self::STATUS_COMPLETED,
                'completed_date' => current_time('Y-m-d'),
            ));
        }
    }

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

    /**
     * Count tasks
     */
    public static function count($args = array()) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        
        $where = array('1=1');
        
        if (!empty($args['project_id'])) {
            $where[] = $wpdb->prepare('project_id = %d', $args['project_id']);
        }
        
        if (!empty($args['milestone_id'])) {
            $where[] = $wpdb->prepare('milestone_id = %d', $args['milestone_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}");
    }

    /**
     * Create tasks from template milestones/tasks
     */
    public static function create_from_template($project_id, $template_tasks) {
        $created = array();
        
        foreach ($template_tasks as $task_data) {
            $task_data['project_id'] = $project_id;
            $task_id = self::create($task_data);
            if ($task_id) {
                $created[] = $task_id;
            }
        }
        
        return $created;
    }

    /**
     * Generate the next recurring task instance when a recurring task is completed.
     * Called automatically when a recurring task status changes to completed.
     *
     * @param object $task The completed task object
     * @return int|false New task ID or false
     */
    public static function generate_next_recurring($task) {
        if (!$task || !$task->is_recurring) {
            return false;
        }

        // Determine the parent: if this task is itself a child, the parent is its parent_task_id
        $parent_id = $task->parent_task_id ? $task->parent_task_id : $task->id;

        // Verify parent still exists (could have been deleted)
        if ($task->parent_task_id) {
            global $wpdb;
            $table = $wpdb->prefix . 'fs_tasks';
            $parent_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$table} WHERE id = %d",
                $parent_id
            ));
            if (!$parent_exists) {
                // Parent was deleted, stop this recurring series
                return false;
            }
        }

        // Calculate next due date
        $base_date = $task->due_date ? $task->due_date : current_time('Y-m-d');
        $next_due = self::calculate_next_recurring_date($base_date, $task->recurring_frequency, $task->recurring_days ?? null);

        if (!$next_due) {
            return false;
        }

        // Safety: next_due must always be strictly after the base date to prevent same-day loops
        if ($next_due <= $base_date) {
            return false;
        }

        // Check if we've passed the end date
        if ($task->recurring_end_date && $next_due > $task->recurring_end_date) {
            return false;
        }

        // Check if a pending/in_progress child already exists for this parent
        // (regardless of due date — only one active instance should exist at a time)
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$table} 
             WHERE parent_task_id = %d 
               AND status NOT IN ('completed', 'cancelled', 'archived')
             LIMIT 1",
            $parent_id
        ));

        if ($existing) {
            return false; // An active instance already exists
        }

        // Create the next occurrence
        $new_data = array(
            'title' => $task->title,
            'description' => $task->description,
            'priority' => $task->priority,
            'due_date' => $next_due,
            'estimated_hours' => $task->estimated_hours,
            'assigned_to' => $task->assigned_to,
            'is_client_visible' => $task->is_client_visible,
            'is_recurring' => 1,
            'recurring_frequency' => $task->recurring_frequency,
            'recurring_days' => $task->recurring_days ?? null,
            'recurring_end_date' => $task->recurring_end_date,
            'parent_task_id' => $parent_id,
            'hide_until_due' => $task->hide_until_due ?? 0,
            'is_non_billable' => $task->is_non_billable ?? 0,
            'status' => self::STATUS_PENDING,
        );

        if ($task->project_id) {
            $new_data['project_id'] = $task->project_id;
            return self::create($new_data);
        } elseif ($task->client_id) {
            $new_data['client_id'] = $task->client_id;
            return self::create_generic($new_data);
        } else {
            return self::create_generic($new_data);
        }
    }

    /**
     * Calculate the next recurring date for tasks
     *
     * @param string $from_date Starting date (Y-m-d)
     * @param string $frequency Frequency (daily, weekly, biweekly, monthly, quarterly)
     * @return string|null Next date in Y-m-d format
     */
    public static function calculate_next_recurring_date($from_date, $frequency, $recurring_days = null) {
        if (!$from_date || !$frequency) {
            return null;
        }

        // Parse recurring_days (stored as comma-separated string like "mon,wed,fri")
        $days_array = array();
        if ($recurring_days) {
            if (is_string($recurring_days)) {
                $days_array = array_map('trim', explode(',', $recurring_days));
            } elseif (is_array($recurring_days)) {
                $days_array = $recurring_days;
            }
            $days_array = array_filter($days_array);
        }

        $date = new DateTime($from_date);

        switch ($frequency) {
            case 'daily':
                $date->modify('+1 day');
                break;

            case 'weekly':
            case 'biweekly':
                if (!empty($days_array)) {
                    // Map day names to PHP day numbers (1=Mon...7=Sun)
                    $day_map = array(
                        'mon' => 1, 'tue' => 2, 'wed' => 3, 'thu' => 4,
                        'fri' => 5, 'sat' => 6, 'sun' => 7,
                    );
                    $target_days = array();
                    foreach ($days_array as $d) {
                        $d = strtolower($d);
                        if (isset($day_map[$d])) {
                            $target_days[] = $day_map[$d];
                        }
                    }
                    sort($target_days);

                    if (!empty($target_days)) {
                        $current_dow = (int) $date->format('N'); // 1=Mon...7=Sun
                        $found = false;

                        // First look for the next matching day in the current week (after today)
                        foreach ($target_days as $td) {
                            if ($td > $current_dow) {
                                $diff = $td - $current_dow;
                                $date->modify("+{$diff} days");
                                $found = true;
                                break;
                            }
                        }

                        if (!$found) {
                            // Wrap to the first matching day of next week (or +2 weeks for biweekly)
                            $weeks = ($frequency === 'biweekly') ? 2 : 1;
                            $first_target = $target_days[0];
                            $days_until = (7 * $weeks) - $current_dow + $first_target;
                            $date->modify("+{$days_until} days");
                        }
                        break;
                    }
                }
                // No specific days selected — fall through to simple interval
                $interval = ($frequency === 'biweekly') ? '+2 weeks' : '+1 week';
                $date->modify($interval);
                break;

            case 'monthly':
                $date->modify('+1 month');
                break;
            case 'quarterly':
                $date->modify('+3 months');
                break;
            default:
                return null;
        }

        return $date->format('Y-m-d');
    }

    /**
     * Generate recurring task entries via cron (safety net).
     * Creates next occurrence for any recurring task whose last instance
     * was completed and whose next due date has arrived.
     *
     * @return int Number of tasks created
     */
    public static function generate_recurring_entries() {
        global $wpdb;

        $table = $wpdb->prefix . 'fs_tasks';
        $today = current_time('Y-m-d');
        $created = 0;

        // Clean up orphaned recurring children (parent was deleted)
        $wpdb->query(
            "DELETE FROM {$table} 
             WHERE parent_task_id IS NOT NULL 
               AND parent_task_id NOT IN (SELECT id FROM (SELECT id FROM {$table}) AS parents)"
        );

        // Get all recurring "parent" tasks (is_recurring=1 AND parent_task_id IS NULL)
        // that have not ended
        $parents = $wpdb->get_results(
            "SELECT * FROM {$table} 
             WHERE is_recurring = 1 
               AND parent_task_id IS NULL 
               AND (recurring_end_date IS NULL OR recurring_end_date >= '{$today}')"
        );

        foreach ($parents as $parent) {
            // Find the latest child (or parent itself if no children)
            $latest = $wpdb->get_row($wpdb->prepare(
                "SELECT * FROM {$table} 
                 WHERE (parent_task_id = %d OR id = %d)
                   AND is_recurring = 1
                 ORDER BY COALESCE(due_date, created_at) DESC 
                 LIMIT 1",
                $parent->id, $parent->id
            ));

            if (!$latest) {
                continue;
            }

            // Only generate if the latest instance is completed
            if ($latest->status !== self::STATUS_COMPLETED) {
                continue;
            }

            // Check if there's already a pending/active child for this parent
            $pending_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} 
                 WHERE parent_task_id = %d 
                   AND status NOT IN ('completed', 'cancelled')",
                $parent->id
            ));

            if ($pending_exists > 0) {
                continue;
            }

            // Calculate next due date from latest instance
            $base_date = $latest->due_date ? $latest->due_date : current_time('Y-m-d');
            $next_due = self::calculate_next_recurring_date($base_date, $parent->recurring_frequency, $parent->recurring_days ?? null);

            if (!$next_due || ($parent->recurring_end_date && $next_due > $parent->recurring_end_date)) {
                continue;
            }

            // Only generate if next due date is today or past
            if ($next_due > $today) {
                continue;
            }

            $new_id = self::generate_next_recurring($latest);
            if ($new_id) {
                $created++;
            }
        }

        return $created;
    }

    /**
     * Stop a recurring task series
     *
     * @param int $id Task ID (parent or child)
     * @return bool
     */
    public static function stop_recurring($id) {
        global $wpdb;
        $table = $wpdb->prefix . 'fs_tasks';

        $task = self::get($id);
        if (!$task) {
            return false;
        }

        $parent_id = $task->parent_task_id ? $task->parent_task_id : $task->id;

        // Stop the parent
        $wpdb->update(
            $table,
            array('is_recurring' => 0, 'recurring_frequency' => null, 'recurring_end_date' => null),
            array('id' => $parent_id),
            array('%d', '%s', '%s'),
            array('%d')
        );

        // Stop all children
        $wpdb->update(
            $table,
            array('is_recurring' => 0, 'recurring_frequency' => null, 'recurring_end_date' => null),
            array('parent_task_id' => $parent_id),
            array('%d', '%s', '%s'),
            array('%d')
        );

        return true;
    }
}
