<?php
namespace App\Modules\Calendar\Models;

class CalendarModel extends \Core\Model {
    protected $table = 'calendar_events';
    protected $moduleName = 'Calendar';
    protected $useSoftDeletes = false;

    // ─────────────────────────────────────────────────────────────
    //  DYNAMIC CATEGORIES
    // ─────────────────────────────────────────────────────────────
    public function getCategories($userId) {
        $this->ensureDefaultCategories($userId);
        return $this->query("SELECT * FROM calendar_categories WHERE user_id = ? ORDER BY id ASC", [$userId])->fetchAll();
    }

    private function ensureDefaultCategories($userId) {
        $count = $this->query("SELECT COUNT(*) as c FROM calendar_categories WHERE user_id = ?", [$userId])->fetch()->c;
        if ($count == 0) {
            $defaults = [
                ['name' => 'اجتماعات', 'bg' => '#e0e7ff', 'text' => '#4338ca', 'border' => '#818cf8', 'sys' => 'meetings'],
                ['name' => 'شخصي', 'bg' => '#fce7f3', 'text' => '#be185d', 'border' => '#f472b6', 'sys' => 'personal'],
                ['name' => 'صحة', 'bg' => '#dcfce7', 'text' => '#15803d', 'border' => '#4ade80', 'sys' => 'health'],
                ['name' => 'مالية', 'bg' => '#fef9c3', 'text' => '#a16207', 'border' => '#facc15', 'sys' => 'finance'],
                ['name' => 'أخرى', 'bg' => '#f3f4f6', 'text' => '#374151', 'border' => '#9ca3af', 'sys' => 'other'],
            ];
            foreach ($defaults as $d) {
                $this->query("INSERT INTO calendar_categories (user_id, name, bg_color, text_color, border_color, system_key) VALUES (?, ?, ?, ?, ?, ?)",
                    [$userId, $d['name'], $d['bg'], $d['text'], $d['border'], $d['sys']]
                );
            }
        }
    }

    public function addCategory($userId, $data) {
        $sql = "INSERT INTO calendar_categories (user_id, name, bg_color, text_color, border_color) VALUES (?, ?, ?, ?, ?)";
        $this->query($sql, [
            $userId, 
            $data['name'], 
            $data['bg_color'] ?? '#e0e7ff', 
            $data['text_color'] ?? '#4338ca', 
            $data['border_color'] ?? '#818cf8'
        ]);
        return $this->db->lastInsertId();
    }

    public function deleteCategory($userId, $id) {
        return $this->query("DELETE FROM calendar_categories WHERE id = ? AND user_id = ?", [$id, $userId]);
    }

    // ─────────────────────────────────────────────────────────────
    //  ARCHIVE GATE — runs at-most once per calendar day
    // ─────────────────────────────────────────────────────────────
    public function archivePastEvents($userId) {
        // Auto-archiving disabled — it was causing past events to disappear from the calendar.
        // Events remain visible regardless of their date.
        return;
    }

    // ─────────────────────────────────────────────────────────────
    //  RECURRING EXPANDER
    // ─────────────────────────────────────────────────────────────
    private function expandRecurringEvents($events, $startDate, $endDate) {
        $expanded  = [];
        $startLimit = new \DateTime($startDate);
        $endLimit   = new \DateTime($endDate);

        foreach ($events as $ev) {
            if ($ev->recurrence_type === 'none' || empty($ev->recurrence_type)) {
                $evDate = new \DateTime($ev->event_date);
                if ($evDate >= $startLimit && $evDate <= $endLimit) {
                    $expanded[] = (object)(array)$ev;
                }
            } else {
                $currentDate = new \DateTime($ev->event_date);
                $recEndLimit = clone $endLimit;
                if (!empty($ev->recurrence_end_date)) {
                    $recEnd = new \DateTime($ev->recurrence_end_date);
                    if ($recEnd < $endLimit) $recEndLimit = $recEnd;
                }
                while ($currentDate <= $recEndLimit) {
                    if ($currentDate >= $startLimit) {
                        $newEv = (object)(array)$ev; // safe clone alternative
                        $newEv->event_date = $currentDate->format('Y-m-d');
                        $expanded[] = $newEv;
                    }
                    switch ($ev->recurrence_type) {
                        case 'daily':   $currentDate->modify('+1 day');   break;
                        case 'weekly':  $currentDate->modify('+1 week');  break;
                        case 'monthly': $currentDate->modify('+1 month'); break;
                        case 'yearly':  $currentDate->modify('+1 year');  break;
                        default: break 2;
                    }
                    if ($ev->recurrence_type === 'none') break;
                }
            }
        }

        usort($expanded, function($a, $b) {
            if ($a->event_date === $b->event_date) {
                return strcmp($a->start_time ?? '00:00:00', $b->start_time ?? '00:00:00');
            }
            return strcmp($a->event_date, $b->event_date);
        });

        return $expanded;
    }

    // ─────────────────────────────────────────────────────────────
    //  FETCH EVENTS
    // ─────────────────────────────────────────────────────────────
    public function getEventsByMonth($userId, $year, $month) {
        $startDate = sprintf("%04d-%02d-01", $year, $month);
        $endDate   = date("Y-m-t", strtotime($startDate));
        $events = $this->query(
            "SELECT * FROM {$this->table} WHERE user_id = ? AND event_date <= ? AND (recurrence_end_date >= ? OR recurrence_end_date IS NULL) AND deleted_at IS NULL",
            [$userId, $endDate, $startDate]
        )->fetchAll();
        return $this->expandRecurringEvents($events, $startDate, $endDate);
    }

    public function getEventsInDateRange($userId, $startDate, $endDate) {
        $events = $this->query(
            "SELECT * FROM {$this->table} WHERE user_id = ? AND event_date <= ? AND (recurrence_end_date >= ? OR recurrence_end_date IS NULL) AND deleted_at IS NULL",
            [$userId, $endDate, $startDate]
        )->fetchAll();
        return $this->expandRecurringEvents($events, $startDate, $endDate);
    }

    public function getEventsByDate($userId, $date) {
        $events = $this->query(
            "SELECT * FROM {$this->table} WHERE user_id = ? AND event_date <= ? AND (recurrence_end_date >= ? OR recurrence_end_date IS NULL) AND deleted_at IS NULL",
            [$userId, $date, $date]
        )->fetchAll();
        return $this->expandRecurringEvents($events, $date, $date);
    }

    public function getEventsByYear($userId, $year) {
        $startDate = "$year-01-01";
        $endDate   = "$year-12-31";
        $events = $this->query(
            "SELECT * FROM {$this->table} WHERE user_id = ? AND event_date <= ? AND (recurrence_end_date >= ? OR recurrence_end_date IS NULL) AND deleted_at IS NULL",
            [$userId, $endDate, $startDate]
        )->fetchAll();
        return $this->expandRecurringEvents($events, $startDate, $endDate);
    }

    public function getEventById($id, $userId) {
        return $this->query(
            "SELECT * FROM {$this->table} WHERE id = ? AND user_id = ? AND deleted_at IS NULL LIMIT 1",
            [$id, $userId]
        )->fetch();
    }

    public function getUpcomingEvents($userId, $limit = 3) {
        $today = date('Y-m-d');
        $sql = "SELECT * FROM {$this->table}
                WHERE user_id = ? AND event_date >= ? AND status = 'active' AND deleted_at IS NULL
                ORDER BY event_date ASC, start_time ASC
                LIMIT " . (int)$limit;
        return $this->query($sql, [$userId, $today])->fetchAll();
    }

    // ─────────────────────────────────────────────────────────────
    //  WRITE EVENTS
    // ─────────────────────────────────────────────────────────────
    public function addEvent($userId, $data) {
        $sql = "INSERT INTO {$this->table} (user_id, title, description, category, event_type, block_category, event_date, start_time, end_time, color_hex, status, recurrence_type, recurrence_end_date)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', ?, ?)";
        $this->query($sql, [
            $userId,
            $data['title'],
            $data['description'] ?? null,
            $data['category'] ?? 'other',
            $data['event_type'] ?? 'event',
            $data['block_category'] ?? null,
            $data['event_date'],
            $data['start_time'] ?? null,
            $data['end_time'] ?? null,
            $data['color_hex'] ?? '#0d6efd',
            $data['recurrence_type'] ?? 'none',
            $data['recurrence_end_date'] ?? null
        ]);

        $eventId = $this->db->lastInsertId();

        if (!empty($data['create_task'])) {
            $sqlTask = "INSERT INTO todo_tasks (user_id, title, description, category, priority, estimated_time, energy_level, target_date, due_date, status, event_id)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?)";
            $this->query($sqlTask, [
                $userId,
                "حدث: " . $data['title'],
                $data['description'] ?? null,
                'general',
                'medium',
                0,
                'medium',
                $data['event_date'],
                $data['event_date'],
                $eventId
            ]);
        }

        $this->logAction($userId, 'created', "إضافة حدث: {$data['title']} بتاريخ {$data['event_date']}");
        return $eventId;
    }

    public function updateEvent($id, $userId, $data, $scope = 'all') {
        // scope: 'all' = update the parent record, 'single' = not supported without exceptions table
        $sql = "UPDATE {$this->table} SET title=?, description=?, category=?, event_type=?, block_category=?, event_date=?, start_time=?, end_time=?, color_hex=?, recurrence_type=?, recurrence_end_date=?
                WHERE id=? AND user_id=? AND deleted_at IS NULL";
        $res = $this->query($sql, [
            $data['title'],
            $data['description'] ?? null,
            $data['category'] ?? 'other',
            $data['event_type'] ?? 'event',
            $data['block_category'] ?? null,
            $data['event_date'],
            $data['start_time'] ?? null,
            $data['end_time'] ?? null,
            $data['color_hex'] ?? '#0d6efd',
            $data['recurrence_type'] ?? 'none',
            $data['recurrence_end_date'] ?? null,
            $id,
            $userId
        ]);
        $this->logAction($userId, 'updated', "تحديث حدث: {$data['title']}");
        return $res;
    }

    public function rescheduleEvent($id, $userId, $newDate) {
        $sql = "UPDATE {$this->table} SET event_date=? WHERE id=? AND user_id=? AND deleted_at IS NULL";
        return $this->query($sql, [$newDate, $id, $userId]);
    }

    public function delete($id, $userId = 1) {
        $sql = "UPDATE {$this->table} SET deleted_at = CURRENT_TIMESTAMP WHERE id=? AND user_id=? AND deleted_at IS NULL";
        return $this->query($sql, [$id, $userId]);
    }

    // ─────────────────────────────────────────────────────────────
    //  TIME BLOCKS
    // ─────────────────────────────────────────────────────────────
    public function getTimeBlocks($userId, $date) {
        return $this->query(
            "SELECT * FROM calendar_time_blocks WHERE user_id = ? AND block_date = ? AND deleted_at IS NULL ORDER BY start_time ASC",
            [$userId, $date]
        )->fetchAll();
    }

    public function getTimeBlocksInRange($userId, $startDate, $endDate) {
        return $this->query(
            "SELECT * FROM calendar_time_blocks WHERE user_id = ? AND block_date >= ? AND block_date <= ? AND deleted_at IS NULL ORDER BY block_date ASC, start_time ASC",
            [$userId, $startDate, $endDate]
        )->fetchAll();
    }

    public function addTimeBlock($userId, $data) {
        $sql = "INSERT INTO calendar_time_blocks (user_id, title, block_date, start_time, end_time, category, color_hex, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $this->query($sql, [
            $userId,
            $data['title'],
            $data['block_date'],
            $data['start_time'],
            $data['end_time'],
            $data['category'] ?? 'focus',
            $data['color_hex'] ?? '#6366f1',
            $data['notes'] ?? null
        ]);
        return $this->db->lastInsertId();
    }

    public function deleteTimeBlock($id, $userId) {
        return $this->query(
            "UPDATE calendar_time_blocks SET deleted_at = CURRENT_TIMESTAMP WHERE id = ? AND user_id = ? AND deleted_at IS NULL",
            [$id, $userId]
        );
    }

    // ─────────────────────────────────────────────────────────────
    //  MISSED EVENTS — past events that were never rescheduled
    // ─────────────────────────────────────────────────────────────
    public function getMissedEvents($userId, $limit = 5) {
        $today = date('Y-m-d');
        $sql = "SELECT * FROM {$this->table}
                WHERE user_id = ? AND event_date < ? AND status = 'active'
                AND recurrence_type = 'none' AND deleted_at IS NULL
                ORDER BY event_date DESC LIMIT " . (int)$limit;
        return $this->query($sql, [$userId, $today])->fetchAll();
    }

    // ─────────────────────────────────────────────────────────────
    //  WEEKLY PATTERN — count events per day-of-week over N weeks
    // ─────────────────────────────────────────────────────────────
    public function getWeeklyPattern($userId, $weeks = 4) {
        $startDate = date('Y-m-d', strtotime("-{$weeks} weeks"));
        $today     = date('Y-m-d');
        $sql = "SELECT DAYOFWEEK(event_date) as dow, COUNT(*) as total
                FROM {$this->table}
                WHERE user_id = ? AND event_date >= ? AND event_date <= ? AND deleted_at IS NULL
                GROUP BY dow ORDER BY dow";
        $rows    = $this->query($sql, [$userId, $startDate, $today])->fetchAll();
        $pattern = array_fill(1, 7, 0); // 1=Sun, 7=Sat
        foreach ($rows as $r) {
            $pattern[(int)$r->dow] = (int)$r->total;
        }
        return $pattern; // [ 1=>n, 2=>n, ..., 7=>n ]
    }

    // ─────────────────────────────────────────────────────────────
    //  DAY SUMMARY — aggregate for mini tooltip
    // ─────────────────────────────────────────────────────────────
    public function getDaySummary($userId, $date) {
        $db = \Core\Database::getInstance();

        // Events
        $events = $this->query(
            "SELECT COUNT(*) as c FROM {$this->table} WHERE user_id = ? AND event_date = ? AND status = 'active' AND deleted_at IS NULL",
            [$userId, $date]
        )->fetch();

        // Tasks
        $tasks = $db->query(
            "SELECT COUNT(*) as total, SUM(CASE WHEN status='done' THEN 1 ELSE 0 END) as done FROM todo_tasks WHERE user_id = ? AND target_date = ? AND deleted_at IS NULL",
            [$userId, $date]
        )->fetch();

        // Habits
        $habits = $db->query(
            "SELECT COUNT(*) as logged FROM habit_logs WHERE user_id = ? AND log_date = ? AND deleted_at IS NULL",
            [$userId, $date]
        )->fetch();

        // Finance (expense amount)
        $finance = $db->query(
            "SELECT COALESCE(SUM(amount), 0) as total FROM finance_expenses WHERE user_id = ? AND expense_date = ? AND deleted_at IS NULL",
            [$userId, $date]
        )->fetch();

        // Time Blocks
        $blocks = $db->query(
            "SELECT COUNT(*) as c FROM calendar_time_blocks WHERE user_id = ? AND block_date = ? AND deleted_at IS NULL",
            [$userId, $date]
        )->fetch();

        return [
            'events'      => (int)($events->c ?? 0),
            'tasks_total' => (int)($tasks->total ?? 0),
            'tasks_done'  => (int)($tasks->done ?? 0),
            'habits'      => (int)($habits->logged ?? 0),
            'finance'     => (float)($finance->total ?? 0),
            'blocks'      => (int)($blocks->c ?? 0),
        ];
    }

    // ─────────────────────────────────────────────────────────────
    //  ENERGY SCHEDULE — best time suggestions from Journal
    // ─────────────────────────────────────────────────────────────
    public function getEnergySchedule($userId, $days = 14) {
        $db = \Core\Database::getInstance();
        $start = date('Y-m-d', strtotime("-{$days} days"));
        $rows = $db->query(
            "SELECT date, energy_level FROM daily_journal WHERE user_id = ? AND date >= ? ORDER BY date DESC",
            [$userId, $start]
        )->fetchAll();

        if (empty($rows)) return null;

        $sum   = 0;
        $count = 0;
        foreach ($rows as $r) {
            $sum += (int)$r->energy_level;
            $count++;
        }
        $avg = $count > 0 ? round($sum / $count, 1) : 3;

        // High energy (4-5): morning focus blocks (07:00–11:00)
        // Medium energy (3): afternoon tasks (13:00–16:00)
        // Low energy (1-2): evening review (19:00–21:00)
        if ($avg >= 4) {
            $suggestion = ['window' => '07:00 – 11:00', 'type' => 'تركيز عالي', 'icon' => '🔆', 'color' => '#f59e0b'];
        } elseif ($avg >= 3) {
            $suggestion = ['window' => '13:00 – 16:00', 'type' => 'عمل معتدل', 'icon' => '☀️', 'color' => '#3b82f6'];
        } else {
            $suggestion = ['window' => '19:00 – 21:00', 'type' => 'مراجعة وراحة', 'icon' => '🌙', 'color' => '#8b5cf6'];
        }

        return array_merge($suggestion, ['avg_energy' => $avg]);
    }

    // ─────────────────────────────────────────────────────────────
    //  GOAL MILESTONES — Monthly Plan Integration
    // ─────────────────────────────────────────────────────────────
    public function getGoalMilestones($userId, $year, $month) {
        $db = \Core\Database::getInstance();
        $monthKey = sprintf("%04d-%02d", $year, $month);
        return $db->query(
            "SELECT g.id, g.title, g.is_primary, g.status
             FROM monthly_goals g
             JOIN monthly_plans p ON p.id = g.plan_id
             WHERE p.user_id = ? AND p.month_key = ? AND g.status != 'completed'",
            [$userId, $monthKey]
        )->fetchAll();
    }

    // ─────────────────────────────────────────────────────────────
    //  TIME USAGE ANALYTICS
    // ─────────────────────────────────────────────────────────────
    public function getTimeUsageAnalytics($userId, $startDate, $endDate) {
        // Approximate analytics by counting events per block_category or category
        $sql = "SELECT category, COUNT(*) as c FROM {$this->table} 
                WHERE user_id = ? AND event_date >= ? AND event_date <= ? AND status='active' AND deleted_at IS NULL GROUP BY category";
        $rows = $this->query($sql, [$userId, $startDate, $endDate])->fetchAll();
        $usage = [];
        foreach ($rows as $r) {
            $usage[$r->category] = $r->c;
        }
        return $usage;
    }

    // ─────────────────────────────────────────────────────────────
    //  CONFLICT DETECTION
    // ─────────────────────────────────────────────────────────────
    public function detectConflict($userId, $date, $startTime, $endTime, $ignoreId = null) {
        if (!$startTime || !$endTime) return false;
        $sql = "SELECT id, title FROM {$this->table} 
                WHERE user_id = ? AND event_date = ? AND status = 'active' AND deleted_at IS NULL
                AND start_time < ? AND end_time > ?";
        $params = [$userId, $date, $endTime, $startTime];
        if ($ignoreId) {
            $sql .= " AND id != ?";
            $params[] = $ignoreId;
        }
        return $this->query($sql, $params)->fetch();
    }

}
