<?php

namespace Sikshya\Database\Repositories;

use Sikshya\Database\Repositories\Contracts\RepositoryInterface;
use Sikshya\Database\Tables\ProgressTable;
use Sikshya\Services\LearnerCurriculumHelper;

class ProgressRepository implements RepositoryInterface
{
    private string $table_name;

    public function __construct()
    {
        $this->table_name = ProgressTable::getTableName();
    }

    public function tableExists(): bool
    {
        global $wpdb;

        return $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $this->table_name)) === $this->table_name;
    }

    /**
     * Query progress rows by user/course filters.
     *
     * @return array<int, object>
     */
    public function listFiltered(int $user_id = 0, int $course_id = 0, int $limit = 200, int $offset = 0): array
    {
        if (!$this->tableExists()) {
            return [];
        }

        $limit = max(1, min(500, $limit));
        $offset = max(0, $offset);

        global $wpdb;
        $where = [];
        $args = [];
        if ($user_id > 0) {
            $where[] = 'user_id = %d';
            $args[] = $user_id;
        }
        if ($course_id > 0) {
            $where[] = 'course_id = %d';
            $args[] = $course_id;
        }
        $where_sql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        $sql = "SELECT * FROM {$this->table_name} {$where_sql} ORDER BY updated_at DESC LIMIT %d OFFSET %d";
        $args[] = $limit;
        $args[] = $offset;

        $rows = $wpdb->get_results($wpdb->prepare($sql, ...$args));

        return is_array($rows) ? $rows : [];
    }

    public function findAll(array $args = []): array
    {
        global $wpdb;

        $defaults = [
            'limit' => 10,
            'offset' => 0,
            'orderby' => 'updated_at',
            'order' => 'DESC',
        ];

        $args = wp_parse_args($args, $defaults);

        $sql = "SELECT * FROM {$this->table_name}";
        $sql .= " ORDER BY {$args['orderby']} {$args['order']}";
        $sql .= " LIMIT {$args['limit']} OFFSET {$args['offset']}";

        return $wpdb->get_results($sql);
    }

    public function findById(int $id): ?object
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE id = %d", $id);
        $result = $wpdb->get_row($sql);

        return $result ?: null;
    }

    public function create(array $data): int
    {
        global $wpdb;

        $defaults = [
            'user_id' => 0,
            'course_id' => 0,
            'lesson_id' => null,
            'quiz_id' => null,
            'status' => 'in_progress',
            'percentage' => 0.00,
            'time_spent' => 0,
            'completed_date' => null,
        ];

        $data = wp_parse_args($data, $defaults);

        $result = $wpdb->insert($this->table_name, [
            'user_id' => intval($data['user_id']),
            'course_id' => intval($data['course_id']),
            'lesson_id' => $data['lesson_id'] ? intval($data['lesson_id']) : null,
            'quiz_id' => $data['quiz_id'] ? intval($data['quiz_id']) : null,
            'status' => sanitize_text_field($data['status']),
            'percentage' => floatval($data['percentage']),
            'time_spent' => intval($data['time_spent']),
            'completed_date' => $data['completed_date'],
        ]);

        return $result ? $wpdb->insert_id : 0;
    }

    public function update(int $id, array $data): bool
    {
        global $wpdb;

        $update_data = [];

        if (isset($data['status'])) {
            $update_data['status'] = sanitize_text_field($data['status']);
        }

        if (isset($data['percentage'])) {
            $update_data['percentage'] = floatval($data['percentage']);
        }

        if (isset($data['time_spent'])) {
            $update_data['time_spent'] = intval($data['time_spent']);
        }

        if (isset($data['completed_date'])) {
            $update_data['completed_date'] = $data['completed_date'];
        }

        if (empty($update_data)) {
            return false;
        }

        $result = $wpdb->update($this->table_name, $update_data, ['id' => $id]);

        return $result !== false;
    }

    public function delete(int $id): bool
    {
        global $wpdb;

        $result = $wpdb->delete($this->table_name, ['id' => $id]);

        return $result !== false;
    }

    public function findByUser(int $user_id, array $args = []): array
    {
        global $wpdb;

        $defaults = [
            'limit' => 10,
            'offset' => 0,
            'orderby' => 'updated_at',
            'order' => 'DESC',
        ];

        $args = wp_parse_args($args, $defaults);

        $sql = $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE user_id = %d", $user_id);
        $sql .= " ORDER BY {$args['orderby']} {$args['order']}";
        $sql .= " LIMIT {$args['limit']} OFFSET {$args['offset']}";

        return $wpdb->get_results($sql);
    }

    public function findByCourse(int $course_id, array $args = []): array
    {
        global $wpdb;

        $defaults = [
            'limit' => 10,
            'offset' => 0,
            'orderby' => 'updated_at',
            'order' => 'DESC',
        ];

        $args = wp_parse_args($args, $defaults);

        $sql = $wpdb->prepare("SELECT * FROM {$this->table_name} WHERE course_id = %d", $course_id);
        $sql .= " ORDER BY {$args['orderby']} {$args['order']}";
        $sql .= " LIMIT {$args['limit']} OFFSET {$args['offset']}";

        return $wpdb->get_results($sql);
    }

    public function findByUserAndCourse(int $user_id, int $course_id): ?object
    {
        global $wpdb;

        $sql = $wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE user_id = %d AND course_id = %d",
            $user_id,
            $course_id
        );

        $result = $wpdb->get_row($sql);

        return $result ?: null;
    }

    public function getCourseProgress(int $user_id, int $course_id): array
    {
        global $wpdb;

        $sql = $wpdb->prepare(
            "SELECT * FROM {$this->table_name} 
             WHERE user_id = %d AND course_id = %d 
             ORDER BY updated_at DESC",
            $user_id,
            $course_id
        );

        return $wpdb->get_results($sql);
    }

    public function updateCourseProgress(int $user_id, int $course_id, array $progress_data): bool
    {
        $progress = $this->findByUserAndCourse($user_id, $course_id);

        if ($progress) {
            return $this->update($progress->id, $progress_data);
        } else {
            $progress_data['user_id'] = $user_id;
            $progress_data['course_id'] = $course_id;
            return $this->create($progress_data) > 0;
        }
    }

    public function initializeProgress(int $user_id, int $course_id): bool
    {
        $existing = $this->findByUserAndCourse($user_id, $course_id);

        if (!$existing) {
            $result = $this->create([
                'user_id' => $user_id,
                'course_id' => $course_id,
                'status' => 'not_started',
                'percentage' => 0.00,
            ]);

            return $result > 0;
        }

        return true;
    }

    public function deleteProgress(int $user_id, int $course_id): bool
    {
        global $wpdb;

        $result = $wpdb->delete($this->table_name, [
            'user_id' => $user_id,
            'course_id' => $course_id,
        ]);

        return $result !== false;
    }

    public function markLessonComplete(int $user_id, int $course_id, int $lesson_id): bool
    {
        if ($this->hasLessonCompletion($user_id, $course_id, $lesson_id)) {
            return true;
        }

        $progress_data = [
            'user_id' => $user_id,
            'course_id' => $course_id,
            'lesson_id' => $lesson_id,
            'status' => 'completed',
            'percentage' => 100.00,
            'completed_date' => current_time('mysql'),
        ];

        $ok = $this->create($progress_data) > 0;
        if ($ok) {
            /**
             * Fires when a learner completes a lesson (new progress row saved).
             *
             * @param int $user_id     User ID.
             * @param int $course_id   Course ID.
             * @param int $lesson_id   Lesson post ID.
             */
            do_action('sikshya_lesson_completed', $user_id, $course_id, $lesson_id);
        }

        return $ok;
    }

    /**
     * Mark an assignment as curriculum-complete for progress UI.
     *
     * Uses the same `lesson_id` progress column as lessons (distinct post IDs prevent collisions).
     *
     * @param int $user_id       User ID.
     * @param int $course_id     Course ID.
     * @param int $assignment_id Assignment post ID.
     */
    public function markAssignmentComplete(int $user_id, int $course_id, int $assignment_id): bool
    {
        if ($this->hasLessonCompletion($user_id, $course_id, $assignment_id)) {
            return true;
        }

        $progress_data = [
            'user_id' => $user_id,
            'course_id' => $course_id,
            'lesson_id' => $assignment_id,
            'quiz_id' => null,
            'status' => 'completed',
            'percentage' => 100.00,
            'completed_date' => current_time('mysql'),
        ];

        $ok = $this->create($progress_data) > 0;
        if ($ok) {
            /**
             * Fires when an assignment counts as completed in course progress (typically after submit).
             *
             * @param int $user_id       User ID.
             * @param int $course_id     Course ID.
             * @param int $assignment_id Assignment post ID.
             */
            do_action('sikshya_assignment_completed', $user_id, $course_id, $assignment_id);
        }

        return $ok;
    }

    /**
     * Whether the learner has a completed progress row for this lesson (lesson rows use quiz_id NULL).
     */
    public function hasLessonCompletion(int $user_id, int $course_id, int $lesson_id): bool
    {
        global $wpdb;

        $n = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table_name} 
                WHERE user_id = %d AND course_id = %d AND lesson_id = %d 
                AND quiz_id IS NULL AND status = %s",
                $user_id,
                $course_id,
                $lesson_id,
                'completed'
            )
        );

        return $n > 0;
    }

    /**
     * Whether the learner has a completed quiz attempt row for this quiz.
     */
    public function hasQuizCompletion(int $user_id, int $course_id, int $quiz_id): bool
    {
        global $wpdb;

        $n = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$this->table_name}
                WHERE user_id = %d AND course_id = %d AND quiz_id = %d AND status = %s",
                $user_id,
                $course_id,
                $quiz_id,
                'completed'
            )
        );

        return $n > 0;
    }

    /**
     * Count distinct curriculum lessons marked completed for a course.
     *
     * Restricts to lesson IDs from the course outline so assignment rows (also stored in `lesson_id`)
     * are not counted as lessons.
     */
    public function countCompletedLessons(int $user_id, int $course_id): int
    {
        $lesson_ids = LearnerCurriculumHelper::lessonIdsForCourse($course_id);

        return $this->countLessonColumnCompletionsAmongIds($user_id, $course_id, $lesson_ids);
    }

    /**
     * How many curriculum assignments in $assignment_ids are marked complete for this learner.
     *
     * @param array<int, int> $assignment_ids
     */
    public function countCompletedAssignmentsAmong(int $user_id, int $course_id, array $assignment_ids): int
    {
        return $this->countLessonColumnCompletionsAmongIds($user_id, $course_id, $assignment_ids);
    }

    /**
     * Completed progress rows where `lesson_id` is in $ids (lesson or assignment posts).
     *
     * @param array<int, int> $ids
     */
    private function countLessonColumnCompletionsAmongIds(int $user_id, int $course_id, array $ids): int
    {
        global $wpdb;

        $ids = array_values(array_unique(array_filter(array_map('absint', $ids))));
        if ($ids === []) {
            return 0;
        }

        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $sql = "SELECT COUNT(DISTINCT lesson_id) FROM {$this->table_name}
            WHERE user_id = %d AND course_id = %d AND lesson_id IS NOT NULL
            AND quiz_id IS NULL AND status = %s
            AND lesson_id IN ({$placeholders})";

        $args = array_merge([$user_id, $course_id, 'completed'], $ids);

        return (int) $wpdb->get_var($wpdb->prepare($sql, ...$args));
    }

    /**
     * How many curriculum quizzes in $quiz_ids are completed for this learner.
     *
     * @param array<int, int> $quiz_ids
     */
    public function countCompletedQuizzesAmong(int $user_id, int $course_id, array $quiz_ids): int
    {
        $n = 0;
        foreach ($quiz_ids as $qid) {
            $qid = (int) $qid;
            if ($qid <= 0) {
                continue;
            }
            if ($this->hasQuizCompletion($user_id, $course_id, $qid)) {
                $n++;
            }
        }

        return $n;
    }

    public function markQuizComplete(int $user_id, int $course_id, int $quiz_id, float $score): bool
    {
        $progress_data = [
            'user_id' => $user_id,
            'course_id' => $course_id,
            'quiz_id' => $quiz_id,
            'status' => 'completed',
            'percentage' => $score,
            'completed_date' => current_time('mysql'),
        ];

        $ok = $this->create($progress_data) > 0;
        if ($ok) {
            /**
             * Fires when a learner completes a curriculum quiz (progress row saved).
             *
             * @param int   $user_id   User ID.
             * @param int   $course_id Course ID.
             * @param int   $quiz_id   Quiz post ID.
             * @param float $score     Score percentage.
             */
            do_action('sikshya_quiz_completed', $user_id, $course_id, $quiz_id, $score);
        }

        return $ok;
    }

    public function getOverallProgress(int $user_id, int $course_id): float
    {
        global $wpdb;

        $sql = $wpdb->prepare(
            "SELECT AVG(percentage) as overall_progress 
             FROM {$this->table_name} 
             WHERE user_id = %d AND course_id = %d",
            $user_id,
            $course_id
        );

        $result = $wpdb->get_var($sql);

        return $result ? floatval($result) : 0.00;
    }

    public function countByUser(int $user_id): int
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT COUNT(*) FROM {$this->table_name} WHERE user_id = %d", $user_id);

        return (int) $wpdb->get_var($sql);
    }

    public function countByCourse(int $course_id): int
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT COUNT(*) FROM {$this->table_name} WHERE course_id = %d", $course_id);

        return (int) $wpdb->get_var($sql);
    }

    public function countByStatus(string $status): int
    {
        global $wpdb;

        $sql = $wpdb->prepare("SELECT COUNT(*) FROM {$this->table_name} WHERE status = %s", $status);

        return (int) $wpdb->get_var($sql);
    }

    public function sumTimeSpentForUser(int $user_id): int
    {
        global $wpdb;
        $sum = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM(time_spent), 0) FROM {$this->table_name} WHERE user_id = %d",
                $user_id
            )
        );
        return (int) $sum;
    }

    public function countDistinctCompletedLessonsForUser(int $user_id): int
    {
        global $wpdb;
        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(DISTINCT lesson_id) FROM {$this->table_name} WHERE user_id = %d AND lesson_id IS NOT NULL AND quiz_id IS NULL AND status = %s",
                $user_id,
                'completed'
            )
        );
    }

    /**
     * Progress rows for a user + lesson (any quiz/lesson rows).
     *
     * @return array<int, object>
     */
    public function findByUserAndLesson(int $user_id, int $lesson_id): array
    {
        global $wpdb;

        $sql = $wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE user_id = %d AND lesson_id = %d ORDER BY updated_at DESC",
            $user_id,
            $lesson_id
        );

        return $wpdb->get_results($sql) ?: [];
    }

    /**
     * Most recently updated lesson progress row for this user (optionally scoped to a course).
     *
     * Used by the "Continue where you left off" surface on the learner dashboard. Only
     * lesson rows are considered (quiz_id IS NULL) so quiz attempts don't outrank lesson activity.
     *
     * @return object|null Row with at least lesson_id, course_id, status, updated_at.
     */
    public function findLastTouchedLessonRow(int $user_id, int $course_id = 0): ?object
    {
        global $wpdb;

        if ($course_id > 0) {
            $sql = $wpdb->prepare(
                "SELECT * FROM {$this->table_name}
                 WHERE user_id = %d AND course_id = %d
                   AND lesson_id IS NOT NULL AND quiz_id IS NULL
                 ORDER BY updated_at DESC
                 LIMIT 1",
                $user_id,
                $course_id
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT * FROM {$this->table_name}
                 WHERE user_id = %d
                   AND lesson_id IS NOT NULL AND quiz_id IS NULL
                 ORDER BY updated_at DESC
                 LIMIT 1",
                $user_id
            );
        }

        $row = $wpdb->get_row($sql);
        return $row ?: null;
    }

    /**
     * Lesson IDs the learner has completed for a course (lesson rows only).
     *
     * @return array<int, int>
     */
    public function completedLessonIdsForCourse(int $user_id, int $course_id): array
    {
        global $wpdb;

        $ids = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT DISTINCT lesson_id FROM {$this->table_name}
                 WHERE user_id = %d AND course_id = %d
                   AND lesson_id IS NOT NULL AND quiz_id IS NULL AND status = %s",
                $user_id,
                $course_id,
                'completed'
            )
        );

        return array_values(array_map('intval', is_array($ids) ? $ids : []));
    }

    /**
     * Remove completed lesson rows (not quiz rows) for a lesson.
     */
    public function deleteLessonCompletionRows(int $user_id, int $course_id, int $lesson_id): bool
    {
        global $wpdb;

        $sql = $wpdb->prepare(
            "DELETE FROM {$this->table_name} WHERE user_id = %d AND course_id = %d AND lesson_id = %d AND quiz_id IS NULL",
            $user_id,
            $course_id,
            $lesson_id
        );

        return false !== $wpdb->query($sql);
    }
}
