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

class ReviewModel extends \Core\Model {
    protected $moduleName = 'Review';
    // Read-only aggregate queries across modules. No delete operations.
    protected $useSoftDeletes = false;

    // ISO week start (Monday) and end (Sunday)
    public static function getWeekRange($year, $week) {
        $dto = new \DateTime();
        $dto->setISODate($year, $week, 1); // Monday
        $start = $dto->format('Y-m-d');
        $dto->setISODate($year, $week, 7); // Sunday
        $end = $dto->format('Y-m-d');
        return [$start, $end];
    }

    // ─── TASKS ────────────────────────────────────────────
    public function getTaskStats($userId, $start, $end) {
        $total = $this->query(
            "SELECT COUNT(*) as c FROM todo_tasks WHERE user_id=? AND target_date BETWEEN ? AND ? AND deleted_at IS NULL",
            [$userId, $start, $end]
        )->fetch()->c ?? 0;
        $done = $this->query(
            "SELECT COUNT(*) as c FROM todo_tasks WHERE user_id=? AND target_date BETWEEN ? AND ? AND status='done' AND deleted_at IS NULL",
            [$userId, $start, $end]
        )->fetch()->c ?? 0;
        $pct = $total > 0 ? round(($done / $total) * 100) : 0;
        // Daily logs this week
        $logs = $this->query(
            "SELECT log_date, rating FROM todo_daily_logs WHERE user_id=? AND log_date BETWEEN ? AND ? AND deleted_at IS NULL ORDER BY log_date ASC",
            [$userId, $start, $end]
        )->fetchAll();
        $avgRating = count($logs) > 0 ? round(array_sum(array_column((array)$logs, 'rating')) / count($logs), 1) : 0;
        return compact('total', 'done', 'pct', 'avgRating', 'logs');
    }

    // ─── HEALTH ───────────────────────────────────────────
    public function getHealthStats($userId, $start, $end) {
        $weights = $this->query(
            "SELECT weight FROM health_weight_logs WHERE user_id=? AND logged_at BETWEEN ? AND ? AND deleted_at IS NULL ORDER BY logged_at ASC",
            [$userId, $start, $end]
        )->fetchAll(\PDO::FETCH_COLUMN);
        $avgWeight = count($weights) > 0 ? round(array_sum($weights) / count($weights), 1) : null;

        $fastings = (int)$this->query(
            "SELECT COUNT(*) as c FROM health_fasts WHERE user_id=? AND DATE(start_time) BETWEEN ? AND ? AND status='completed' AND deleted_at IS NULL",
            [$userId, $start, $end]
        )->fetch()->c;

        $activities = (int)$this->query(
            "SELECT COUNT(*) as c FROM health_activities WHERE user_id=? AND log_date BETWEEN ? AND ? AND deleted_at IS NULL",
            [$userId, $start, $end]
        )->fetch()->c;

        return compact('avgWeight', 'fastings', 'activities', 'weights');
    }

    // ─── FINANCE ──────────────────────────────────────────
    public function getFinanceStats($userId, $start, $end) {
        $income = (float)$this->query(
            "SELECT COALESCE(SUM(amount),0) as t FROM finance_incomes WHERE user_id=? AND date_received BETWEEN ? AND ? AND deleted_at IS NULL",
            [$userId, $start, $end]
        )->fetch()->t;
        $expense = (float)$this->query(
            "SELECT COALESCE(SUM(amount),0) as t FROM finance_expenses WHERE user_id=? AND expense_date BETWEEN ? AND ? AND deleted_at IS NULL",
            [$userId, $start, $end]
        )->fetch()->t;
        $net = $income - $expense;
        return compact('income', 'expense', 'net');
    }

    // ─── EDUCATION ────────────────────────────────────────
    public function getEducationStats($userId, $start, $end) {
        $skillsUpped = $this->query(
            "SELECT description FROM action_logs WHERE user_id=? AND module='Education' AND action='achieved' AND created_at BETWEEN ? AND ?",
            [$userId, $start . ' 00:00:00', $end . ' 23:59:59']
        )->fetchAll();

        $coursesCompleted = (int)$this->query(
            "SELECT COUNT(*) as c FROM action_logs WHERE user_id=? AND module='Education' AND action='updated' AND description LIKE '%إلى 100%%' AND created_at BETWEEN ? AND ?",
            [$userId, $start . ' 00:00:00', $end . ' 23:59:59']
        )->fetch()->c;

        return ['skills_leveled' => count($skillsUpped), 'skills_events' => $skillsUpped, 'courses_completed' => $coursesCompleted];
    }
}
