@file:OptIn(kotlin.time.ExperimentalTime::class)

package computer.whatthefuck.arcology.domain

import computer.whatthefuck.arcology.flashcard.ReviewData
import kotlin.time.Instant

// Flashcard Types (org-fc format)
enum class FlashcardType {
    NORMAL,    // Front/Back flashcard
    DOUBLE,    // Bidirectional (F->B and B->F)
    CLOZE,     // Deletion cards with holes
    TEXT_INPUT, // Answer entered by user
    VOCAB      // Vocabulary cards
}

/**
 * Quiz context type for filtering which cards are shown during a quiz session.
 */
enum class QuizContextType {
    ALL,       // No filter - show all due cards
    TAG,       // Filter by tag
    BACKLINK   // Filter by backlinks to a specific node
}

/**
 * Quiz context for filtering cards during a quiz session.
 * @param type The type of filter to apply
 * @param value The value for the filter (tag name or node ID)
 */
data class QuizContext(
    val type: QuizContextType,
    val value: String
)

enum class ClozeType {
    DELETION,     // Show other holes, hide current
    ENUMERATION,  // Show holes before current
    CONTEXT,      // Show N holes around current
    SINGLE;       // Hide all other holes

    companion object {
        // Default context size for CONTEXT cloze type
        // TODO: Move to AppPreferences with UI setting
        const val DEFAULT_CONTEXT_SIZE = 1
    }
}

enum class ReviewRating(val value: Int, val label: String) {
    AGAIN(0, "Again"),
    HARD(1, "Hard"),
    GOOD(2, "Good"),
    EASY(3, "Easy");

    companion object {
        fun fromValue(value: Int): ReviewRating {
            return values().firstOrNull { it.value == value } ?: GOOD
        }
    }
}

// Flashcard Domain Models
data class Flashcard(
    val nodeId: String,
    val cardType: FlashcardType,
    val clozeType: ClozeType?,
    val createdAt: Instant,
    val isSuspended: Boolean = false,
    val reviewData: Map<String, ReviewData> = emptyMap()
)

data class FlashcardPosition(
    val nodeId: String,
    val positionName: String,
    val easeFactor: Double,
    val box: Int,
    val intervalDays: Double,
    val dueDate: Instant,
    val reviewCount: Int
)

data class FlashcardReview(
    val id: Long,
    val nodeId: String,
    val positionName: String,
    val rating: ReviewRating,
    val easeFactor: Double,
    val box: Int,
    val intervalDays: Double,
    val dueDate: Instant,
    val reviewDate: Instant,
    val durationMs: Long?
)

/**
 * Statistics for flashcard review system.
 * @param totalFlashcards Total number of flashcards
 * @param totalPositions Total number of flashcard positions (cards with review data)
 * @param dueToday Number of cards due for review today
 * @param totalReviews Total number of reviews recorded
 * @param averageEase Average ease factor across all positions
 * @param typeBreakdown Count of cards by type
 * @param suspendedCount Number of suspended cards
 * @param newCards Count of cards created in different time periods (day, week, month)
 * @param dueByTime Count of cards due in different time periods (now, day, week, month)
 * @param ratingDistribution Count of reviews by rating
 */
data class FlashcardStatistics(
    val totalFlashcards: Long,
    val totalPositions: Long,
    val dueToday: Long,
    val totalReviews: Long,
    val averageEase: Double?,
    val typeBreakdown: Map<FlashcardType, Long>,
    val suspendedCount: Long = 0,
    val newCards: Map<String, Long> = emptyMap(),
    val dueByTime: Map<String, Long> = emptyMap(),
    val ratingDistribution: Map<ReviewRating, Long> = emptyMap()
)

package computer.whatthefuck.arcology.domain

/**
 * Represents a single cloze hole within a flashcard.
 * @param id The unique identifier for this hole (0, 1, 2, ...)
 * @param text The text content to hide/reveal
 * @param hint Optional hint shown when hole is hidden (e.g., "[...city]")
 * @param startIndex Character offset in the raw content where this hole begins
 * @param endIndex Character offset in the raw content where this hole ends
 */
data class ClozeHole(
    val id: Int,
    val text: String,
    val hint: String?,
    val startIndex: Int,
    val endIndex: Int
)

/**
 * Parsed content of a cloze flashcard.
 * @param rawContent The original org-mode text content
 * @param holes List of all cloze holes found in the content
 * @param clozeType The subtype determining hole visibility behavior
 */
data class ClozeCardContent(
    val rawContent: String,
    val holes: List<ClozeHole>,
    val clozeType: ClozeType
)

/**
 * Visibility state for a cloze hole during review.
 */
enum class ClozeHoleVisibility {
    /** Show "[...]" or "[...hint]" placeholder */
    HIDDEN,
    /** Show full hole text normally */
    VISIBLE,
    /** Show hole text with highlight (on flip) */
    REVEALED
}

/**
 * Rendered cloze content with segments for display.
 * @param segments Ordered list of text and hole segments
 */
data class ClozeRenderedContent(
    val segments: List<ClozeSegment>
)

/**
 * A segment of rendered cloze content.
 */
sealed class ClozeSegment {
    /** Plain text between holes */
    data class Text(val content: String) : ClozeSegment()
    
    /** Hidden hole shown as placeholder */
    data class HiddenHole(val hole: ClozeHole, val displayText: String) : ClozeSegment()
    
    /** Revealed hole (on flip) with optional highlight */
    data class RevealedHole(val hole: ClozeHole, val isHighlighted: Boolean) : ClozeSegment()
    
    // TODO: Image support
    // Add ImageHole segment type for cloze holes containing images:
    // data class ImageHole(val hole: ClozeHole, val imagePath: String, val isHidden: Boolean) : ClozeSegment()
    // When parsing, detect OrgInlineElem.Link with image file targets
    // Render with AsyncImage composable instead of Text
}

package computer.whatthefuck.arcology.flashcard

import kotlin.time.Instant

/**
 * Represents the review state for a specific position within a flashcard.
 * Parsed from :REVIEW_DATA: drawers in org-mode files.
 */
data class ReviewData(
    val positionName: String,
    val easeFactor: Double? = null,
    val box: Int? = null,
    val intervalDays: Double? = null,
    val dueDate: Instant? = null,
    val reviewCount: Int? = null,
    val customColumns: Map<String, String> = emptyMap()
)

-- Flashcard support (org-fc format)
CREATE TABLE IF NOT EXISTS flashcards (
    node_id TEXT PRIMARY KEY NOT NULL,
    card_type TEXT NOT NULL,
    cloze_type TEXT,
    created_at INTEGER NOT NULL,
    suspended INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (node_id) REFERENCES nodes (id) ON DELETE CASCADE
);

-- Position-specific review data for multi-position cards (cloze, double)
CREATE TABLE IF NOT EXISTS flashcard_positions (
    node_id TEXT NOT NULL,
    position_name TEXT NOT NULL,
    ease_factor REAL NOT NULL DEFAULT 2.5,
    box INTEGER NOT NULL DEFAULT 0,
    interval_days REAL NOT NULL DEFAULT 0,
    due_date INTEGER NOT NULL DEFAULT 0,
    review_count INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (node_id, position_name),
    FOREIGN KEY (node_id) REFERENCES flashcards (node_id) ON DELETE CASCADE
);

-- Review history
CREATE TABLE IF NOT EXISTS flashcard_reviews (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    node_id TEXT NOT NULL,
    position_name TEXT NOT NULL,
    rating INTEGER NOT NULL,
    ease_factor REAL NOT NULL,
    box INTEGER NOT NULL,
    interval_days REAL NOT NULL,
    due_date INTEGER NOT NULL,
    review_date INTEGER NOT NULL,
    duration_ms INTEGER,
    FOREIGN KEY (node_id) REFERENCES flashcards (node_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_flashcard_positions_due ON flashcard_positions (due_date);
CREATE INDEX IF NOT EXISTS idx_flashcard_reviews_node ON flashcard_reviews (node_id);
CREATE INDEX IF NOT EXISTS idx_flashcard_reviews_position ON flashcard_reviews (node_id, position_name);

-- Flashcard operations
insertFlashcard:
INSERT OR REPLACE INTO flashcards (node_id, card_type, cloze_type, created_at, suspended)
VALUES (?, ?, ?, ?, ?);

selectFlashcardByNodeId:
SELECT * FROM flashcards WHERE node_id = ?;

selectAllFlashcards:
SELECT * FROM flashcards ORDER BY created_at DESC;

selectFlashcardsByType:
SELECT * FROM flashcards WHERE card_type = ? ORDER BY created_at DESC;

deleteFlashcardByNodeId:
DELETE FROM flashcards WHERE node_id = ?;

selectSuspendedFlashcards:
SELECT * FROM flashcards WHERE suspended = 1;

selectFlashcardNodeIdsByFile:
SELECT f.node_id FROM flashcards f
JOIN nodes n ON f.node_id = n.id
WHERE n.file = ?;

deleteFlashcardsByFile:
DELETE FROM flashcards WHERE node_id IN (SELECT id FROM nodes WHERE file = ?);

deleteFlashcardPositionsByFile:
DELETE FROM flashcard_positions WHERE node_id IN (SELECT id FROM nodes WHERE file = ?);

-- Flashcard position operations
insertFlashcardPosition:
INSERT OR REPLACE INTO flashcard_positions (node_id, position_name, ease_factor, box, interval_days, due_date, review_count)
VALUES (?, ?, ?, ?, ?, ?, ?);

selectFlashcardPositionsByNodeId:
SELECT * FROM flashcard_positions WHERE node_id = ? ORDER BY position_name;

selectFlashcardPosition:
SELECT * FROM flashcard_positions WHERE node_id = ? AND position_name = ?;

selectDueFlashcardPositions:
SELECT fp.* FROM flashcard_positions fp
JOIN flashcards f ON fp.node_id = f.node_id
WHERE fp.due_date <= ? AND f.suspended = 0 ORDER BY fp.due_date ASC LIMIT ?;

updateFlashcardPosition:
UPDATE flashcard_positions
SET ease_factor = ?, box = ?, interval_days = ?, due_date = ?, review_count = review_count + 1
WHERE node_id = ? AND position_name = ?;

deleteFlashcardPosition:
DELETE FROM flashcard_positions WHERE node_id = ? AND position_name = ?;

deleteFlashcardPositionsByNodeId:
DELETE FROM flashcard_positions WHERE node_id = ?;

-- Flashcard review operations
insertFlashcardReview:
INSERT INTO flashcard_reviews (node_id, position_name, rating, ease_factor, box, interval_days, due_date, review_date, duration_ms)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);

selectFlashcardReviewsByNodeId:
SELECT * FROM flashcard_reviews WHERE node_id = ? ORDER BY review_date DESC LIMIT ?;

selectFlashcardReviewsByNodeIdAndPosition:
SELECT * FROM flashcard_reviews WHERE node_id = ? AND position_name = ? ORDER BY review_date DESC;

selectFlashcardReviewById:
SELECT * FROM flashcard_reviews WHERE id = ?;

selectFlashcardReviewsBetweenDates:
SELECT * FROM flashcard_reviews WHERE review_date >= ? AND review_date <= ? ORDER BY review_date DESC;

-- Idempotency check for org-fc-reviews.tsv import. A row is considered
-- already-imported if a review exists with the same (node_id, position_name,
-- review_date, rating) natural key. review_date is stored as epoch seconds
-- (INTEGER), matching how FlashcardReview.reviewDate is persisted elsewhere.
selectReviewExistsByNaturalKey:
SELECT EXISTS(
    SELECT 1 FROM flashcard_reviews
    WHERE node_id = ? AND position_name = ? AND review_date = ? AND rating = ?
) AS exists_;

deleteFlashcardReviewsByNodeId:
DELETE FROM flashcard_reviews WHERE node_id = ?;

-- Flashcard statistics
selectFlashcardCount:
SELECT COUNT(*) FROM flashcards;

selectFlashcardCountByType:
SELECT card_type, COUNT(*) AS count FROM flashcards GROUP BY card_type;

selectFlashcardPositionCount:
SELECT COUNT(*) FROM flashcard_positions;

selectDueFlashcardCount:
SELECT COUNT(*) FROM flashcard_positions fp
JOIN flashcards f ON fp.node_id = f.node_id
WHERE fp.due_date <= ? AND f.suspended = 0;

selectFlashcardReviewCount:
SELECT COUNT(*) FROM flashcard_reviews;

selectAverageEaseFactor:
SELECT AVG(ease_factor) AS avgEase FROM flashcard_positions;

-- Additional statistics queries
selectSuspendedFlashcardCount:
SELECT COUNT(*) FROM flashcards WHERE suspended = 1;

selectNewFlashcardCount:
SELECT COUNT(*) FROM flashcards WHERE created_at >= ?;

selectDueFlashcardCountByTime:
SELECT COUNT(*) FROM flashcard_positions fp
JOIN flashcards f ON fp.node_id = f.node_id
WHERE fp.due_date <= ? AND f.suspended = 0;

selectReviewCountByRating:
SELECT rating, COUNT(*) AS count FROM flashcard_reviews GROUP BY rating;

-- Cross-domain queries (roam ↔ quiz)
selectFlashcardNodesByTag:
SELECT DISTINCT f.node_id
FROM flashcards f
JOIN tags t ON f.node_id = t.node_id
WHERE t.tag = ? AND f.suspended = 0;

selectFlashcardNodesByBacklink:
SELECT DISTINCT f.node_id
FROM flashcards f
JOIN links l ON f.node_id = l.from_node
WHERE l.to_node = ? AND f.suspended = 0;

selectFlashcardNodes:
SELECT node_id FROM flashcards WHERE suspended = 0;

selectNodesWithBacklinksFromFlashcards:
SELECT DISTINCT l.to_node
FROM flashcards f
JOIN links l ON f.node_id = l.from_node
WHERE f.suspended = 0;

selectTagsForFlashcardNodes:
SELECT DISTINCT t.tag
FROM tags t
JOIN flashcards f ON t.node_id = f.node_id
WHERE f.suspended = 0
ORDER BY t.tag;

selectTagsForFlashcardNodesWithCount:
SELECT t.tag, COUNT(*) AS count
FROM tags t
JOIN flashcards f ON t.node_id = f.node_id
WHERE f.suspended = 0
GROUP BY t.tag
ORDER BY count DESC;

<<db-quiz-schema>>

<<db-quiz-queries>>

@file:OptIn(kotlin.time.ExperimentalTime::class)
package computer.whatthefuck.arcology.database

import computer.whatthefuck.arcology.db.ArcologyDatabase
import computer.whatthefuck.arcology.domain.*
import kotlinx.datetime.LocalDate
import kotlin.time.Clock
import kotlin.time.Instant
import kotlinx.coroutines.*
import kotlinx.coroutines.ExperimentalCoroutinesApi

interface QuizRepository {
    // Transaction support
    suspend fun <T> transaction(block: suspend () -> T): T

    // Flashcard CRUD
    suspend fun getFlashcard(nodeId: String): Flashcard?
    suspend fun getAllFlashcards(): List<Flashcard>
    suspend fun getFlashcardsByType(cardType: FlashcardType): List<Flashcard>
    suspend fun insertFlashcard(flashcard: Flashcard)
    suspend fun deleteFlashcard(nodeId: String)
    suspend fun getSuspendedFlashcards(): List<Flashcard>

    // Flashcard reconciliation (per-file, used by QuizIndexerPlugin)
    suspend fun getFlashcardNodeIdsByFile(file: String): List<String>
    suspend fun deleteFlashcardsByFile(file: String)
    suspend fun deleteFlashcardPositionsByFile(file: String)

    // Flashcard position operations
    suspend fun getFlashcardPositions(nodeId: String): List<FlashcardPosition>
    suspend fun getFlashcardPosition(nodeId: String, positionName: String): FlashcardPosition?
    suspend fun insertFlashcardPosition(position: FlashcardPosition)
    suspend fun updateFlashcardPosition(position: FlashcardPosition)
    suspend fun deleteFlashcardPosition(nodeId: String, positionName: String)
    suspend fun deleteFlashcardPositionsByNodeId(nodeId: String)

    // Due position queries
    suspend fun getDueFlashcardPositions(maxCount: Long = 50): List<FlashcardPosition>
    suspend fun countDueFlashcardPositions(): Long

    // Review operations
    suspend fun insertFlashcardReview(review: FlashcardReview)
    suspend fun getFlashcardReviews(nodeId: String, limit: Long = 100): List<FlashcardReview>
    suspend fun getFlashcardReviews(nodeId: String, positionName: String): List<FlashcardReview>
    suspend fun getFlashcardReviewById(id: Long): FlashcardReview?
    suspend fun getFlashcardReviewsBetweenDates(startDate: Instant, endDate: Instant): List<FlashcardReview>
    suspend fun deleteFlashcardReviewsByNodeId(nodeId: String)

    /**
     * Idempotency check for org-fc-reviews.tsv import. Returns true if a
     * review row with the given natural key already exists in the database.
     * Used by QuizIndexerPlugin.onIndexingComplete to skip rows that are
     * already imported (e.g. rows the Android app wrote via ReviewService
     * before the next index pass).
     *
     * @param nodeId The flashcard node ID (TSV column 3)
     * @param positionName The position name (TSV column 4)
     * @param reviewDateEpochSeconds The review timestamp in epoch seconds (TSV column 1)
     * @param ratingValue The rating integer value (TSV column 8 mapped via ReviewRating.fromValue)
     */
    suspend fun reviewExistsByNaturalKey(
        nodeId: String,
        positionName: String,
        reviewDateEpochSeconds: Long,
        ratingValue: Int
    ): Boolean

    // Cross-domain filtering (quiz context)
    suspend fun getFlashcardNodesByTag(tag: String): List<String>
    suspend fun getFlashcardNodesByBacklinkTo(targetNodeId: String): List<String>
    suspend fun getAllNodesWithFlashcards(): List<String>
    suspend fun getTagsForFlashcardNodes(): List<String>
    suspend fun getTagsForFlashcardNodesWithCount(): List<Pair<String, Long>>
    suspend fun getNodesWithBacklinksFromFlashcards(): List<String>

    // Statistics
    suspend fun getFlashcardStatistics(): FlashcardStatistics

    // Required by FlashcardService to check for suspended tags
    suspend fun getTagsByNode(nodeId: String): List<String>
}

class QuizRepositoryImpl(
    private val database: ArcologyDatabase
) : QuizRepository {

    @OptIn(ExperimentalCoroutinesApi::class)
    private val dbDispatcher = Dispatchers.IO.limitedParallelism(1)

    // Flashcard operations
    override suspend fun getFlashcard(nodeId: String): Flashcard? {
        return         database.quizQueries.selectFlashcardByNodeId(nodeId)
            .executeAsOneOrNull()?.toFlashcardDomain()
    }

    override suspend fun getAllFlashcards(): List<Flashcard> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectAllFlashcards().executeAsList().map { it.toFlashcardDomain() }
        }
    }

    override suspend fun getFlashcardsByType(cardType: FlashcardType): List<Flashcard> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectFlashcardsByType(cardType.name)
                .executeAsList().map { it.toFlashcardDomain() }
        }
    }

    override suspend fun insertFlashcard(flashcard: Flashcard) {
        database.quizQueries.insertFlashcard(
            flashcard.nodeId,
            flashcard.cardType.name,
            flashcard.clozeType?.name,
            flashcard.createdAt.epochSeconds,
            if (flashcard.isSuspended) 1 else 0
        )
    }

    override suspend fun deleteFlashcard(nodeId: String) {
        database.quizQueries.deleteFlashcardByNodeId(nodeId)
    }

    override suspend fun getFlashcardNodeIdsByFile(file: String): List<String> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectFlashcardNodeIdsByFile(file).executeAsList()
        }
    }

    override suspend fun deleteFlashcardsByFile(file: String) {
        database.quizQueries.deleteFlashcardsByFile(file)
    }

    override suspend fun deleteFlashcardPositionsByFile(file: String) {
        database.quizQueries.deleteFlashcardPositionsByFile(file)
    }

    override suspend fun getSuspendedFlashcards(): List<Flashcard> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectSuspendedFlashcards().executeAsList().map { it.toFlashcardDomain() }
        }
    }

    // Flashcard position operations
    override suspend fun getFlashcardPositions(nodeId: String): List<FlashcardPosition> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectFlashcardPositionsByNodeId(nodeId)
                .executeAsList().map { it.toFlashcardPositionDomain() }
        }
    }

    override suspend fun getFlashcardPosition(nodeId: String, positionName: String): FlashcardPosition? {
        return database.quizQueries.selectFlashcardPosition(nodeId, positionName)
            .executeAsOneOrNull()?.toFlashcardPositionDomain()
    }

    override suspend fun insertFlashcardPosition(position: FlashcardPosition) {
        database.quizQueries.insertFlashcardPosition(
            position.nodeId,
            position.positionName,
            position.easeFactor,
            position.box.toLong(),
            position.intervalDays,
            position.dueDate.epochSeconds,
            position.reviewCount.toLong()
        )
    }

    override suspend fun updateFlashcardPosition(position: FlashcardPosition) {
        database.quizQueries.updateFlashcardPosition(
            position.easeFactor,
            position.box.toLong(),
            position.intervalDays,
            position.dueDate.epochSeconds,
            position.nodeId,
            position.positionName
        )
    }

    override suspend fun deleteFlashcardPosition(nodeId: String, positionName: String) {
        database.quizQueries.deleteFlashcardPosition(nodeId, positionName)
    }

    override suspend fun deleteFlashcardPositionsByNodeId(nodeId: String) {
        database.quizQueries.deleteFlashcardPositionsByNodeId(nodeId)
    }

    override suspend fun getDueFlashcardPositions(maxCount: Long): List<FlashcardPosition> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectDueFlashcardPositions(
                Clock.System.now().epochSeconds,
                maxCount
            ).executeAsList().map { it.toFlashcardPositionDomain() }
        }
    }

    override suspend fun countDueFlashcardPositions(): Long {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectDueFlashcardCount(
                Clock.System.now().epochSeconds
            ).executeAsOne()
        }
    }

    // Flashcard review operations
    override suspend fun insertFlashcardReview(review: FlashcardReview) {
        database.quizQueries.insertFlashcardReview(
            review.nodeId,
            review.positionName,
            review.rating.value.toLong(),
            review.easeFactor,
            review.box.toLong(),
            review.intervalDays,
            review.dueDate.epochSeconds,
            review.reviewDate.epochSeconds,
            review.durationMs
        )
    }

    override suspend fun getFlashcardReviews(nodeId: String, limit: Long): List<FlashcardReview> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectFlashcardReviewsByNodeId(nodeId, limit)
                .executeAsList().map { it.toFlashcardReviewDomain() }
        }
    }

    override suspend fun getFlashcardReviews(nodeId: String, positionName: String): List<FlashcardReview> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectFlashcardReviewsByNodeIdAndPosition(nodeId, positionName)
                .executeAsList().map { it.toFlashcardReviewDomain() }
        }
    }

    override suspend fun getFlashcardReviewById(id: Long): FlashcardReview? {
        return database.quizQueries.selectFlashcardReviewById(id)
            .executeAsOneOrNull()?.toFlashcardReviewDomain()
    }

    override suspend fun getFlashcardReviewsBetweenDates(startDate: Instant, endDate: Instant): List<FlashcardReview> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectFlashcardReviewsBetweenDates(
                startDate.epochSeconds,
                endDate.epochSeconds
            ).executeAsList().map { it.toFlashcardReviewDomain() }
        }
    }

    override suspend fun deleteFlashcardReviewsByNodeId(nodeId: String) {
        database.quizQueries.deleteFlashcardReviewsByNodeId(nodeId)
    }

    override suspend fun reviewExistsByNaturalKey(
        nodeId: String,
        positionName: String,
        reviewDateEpochSeconds: Long,
        ratingValue: Int
    ): Boolean {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectReviewExistsByNaturalKey(
                nodeId,
                positionName,
                reviewDateEpochSeconds,
                ratingValue.toLong()
            ).executeAsOne()
        }
    }

    // Cross-domain filtering (quiz context)
    override suspend fun getFlashcardNodesByTag(tag: String): List<String> {
        return database.quizQueries.selectFlashcardNodesByTag(tag).executeAsList()
    }

    override suspend fun getFlashcardNodesByBacklinkTo(targetNodeId: String): List<String> {
        return database.quizQueries.selectFlashcardNodesByBacklink(targetNodeId).executeAsList()
    }

    override suspend fun getAllNodesWithFlashcards(): List<String> {
        return database.quizQueries.selectFlashcardNodes().executeAsList()
    }

    override suspend fun getTagsForFlashcardNodes(): List<String> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectTagsForFlashcardNodes().executeAsList()
        }
    }

    override suspend fun getTagsForFlashcardNodesWithCount(): List<Pair<String, Long>> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectTagsForFlashcardNodesWithCount { tag, count ->
                tag to count
            }.executeAsList()
        }
    }

    override suspend fun getNodesWithBacklinksFromFlashcards(): List<String> {
        return withContext(Dispatchers.IO) {
            database.quizQueries.selectNodesWithBacklinksFromFlashcards { toNode ->
                toNode ?: ""
            }.executeAsList()
        }
    }

    // Required by FlashcardService for suspended-tag check
    override suspend fun getTagsByNode(nodeId: String): List<String> {
        return database.arcologyDatabaseQueries.selectTagsByNode(nodeId).executeAsList()
    }

    // Flashcard statistics
    override suspend fun getFlashcardStatistics(): FlashcardStatistics {
        return withContext(Dispatchers.IO) {
            val now = Clock.System.now()
            val oneDayAgo = Instant.fromEpochSeconds(now.epochSeconds - 86400)
            val oneWeekAgo = Instant.fromEpochSeconds(now.epochSeconds - 604800)
            val oneMonthAgo = Instant.fromEpochSeconds(now.epochSeconds - 2592000)

            val totalFlashcards = database.quizQueries.selectFlashcardCount().executeAsOne()
            val totalPositions = database.quizQueries.selectFlashcardPositionCount().executeAsOne()
            val dueToday = database.quizQueries.selectDueFlashcardCount(now.epochSeconds).executeAsOne()
            val totalReviews = database.quizQueries.selectFlashcardReviewCount().executeAsOne()
            val avgEase = database.quizQueries.selectAverageEaseFactor().executeAsOneOrNull()?.avgEase
            val suspendedCount = database.quizQueries.selectSuspendedFlashcardCount().executeAsOne()

            val typeBreakdown = database.quizQueries.selectFlashcardCountByType().executeAsList()
                .associate { FlashcardType.valueOf(it.card_type) to it.count }

            val newCards = mapOf(
                "day" to database.quizQueries.selectNewFlashcardCount(oneDayAgo.epochSeconds).executeAsOne(),
                "week" to database.quizQueries.selectNewFlashcardCount(oneWeekAgo.epochSeconds).executeAsOne(),
                "month" to database.quizQueries.selectNewFlashcardCount(oneMonthAgo.epochSeconds).executeAsOne()
            )

            val dueInOneDay = Instant.fromEpochSeconds(now.epochSeconds + 86400)
            val dueInOneWeek = Instant.fromEpochSeconds(now.epochSeconds + 604800)
            val dueInOneMonth = Instant.fromEpochSeconds(now.epochSeconds + 2592000)

            val dueByTime = mapOf(
                "now" to dueToday,
                "day" to database.quizQueries.selectDueFlashcardCountByTime(dueInOneDay.epochSeconds).executeAsOne(),
                "week" to database.quizQueries.selectDueFlashcardCountByTime(dueInOneWeek.epochSeconds).executeAsOne(),
                "month" to database.quizQueries.selectDueFlashcardCountByTime(dueInOneMonth.epochSeconds).executeAsOne()
            )

            val ratingDistribution = database.quizQueries.selectReviewCountByRating().executeAsList()
                .associate { ReviewRating.fromValue(it.rating.toInt()) to it.count }

            FlashcardStatistics(
                totalFlashcards = totalFlashcards,
                totalPositions = totalPositions,
                dueToday = dueToday,
                totalReviews = totalReviews,
                averageEase = avgEase,
                typeBreakdown = typeBreakdown,
                suspendedCount = suspendedCount,
                newCards = newCards,
                dueByTime = dueByTime,
                ratingDistribution = ratingDistribution
            )
        }
    }

    override suspend fun <T> transaction(block: suspend () -> T): T {
        return withContext(dbDispatcher) {
            database.transactionWithResult {
                runBlocking { block() }
            }
        }
    }

}

// Flashcard domain conversions
private fun computer.whatthefuck.arcology.db.Flashcards.toFlashcardDomain(): Flashcard {
    return Flashcard(
        nodeId = node_id,
        cardType = FlashcardType.valueOf(card_type),
        clozeType = cloze_type?.let { ClozeType.valueOf(it) },
        createdAt = Instant.fromEpochSeconds(created_at),
        isSuspended = suspended == 1L
    )
}

private fun computer.whatthefuck.arcology.db.Flashcard_positions.toFlashcardPositionDomain(): FlashcardPosition {
    return FlashcardPosition(
        nodeId = node_id,
        positionName = position_name,
        easeFactor = ease_factor,
        box = box.toInt(),
        intervalDays = interval_days,
        dueDate = Instant.fromEpochSeconds(due_date),
        reviewCount = review_count.toInt()
    )
}

private fun computer.whatthefuck.arcology.db.Flashcard_reviews.toFlashcardReviewDomain(): FlashcardReview {
    return FlashcardReview(
        id = id,
        nodeId = node_id,
        positionName = position_name,
        rating = ReviewRating.fromValue(rating.toInt()),
        easeFactor = ease_factor,
        box = box.toInt(),
        intervalDays = interval_days,
        dueDate = Instant.fromEpochSeconds(due_date),
        reviewDate = Instant.fromEpochSeconds(review_date),
        durationMs = duration_ms?.toLong()
    )
}

<<quiz-repo-preamble>>

<<quiz-repo-interface>>

<<quiz-repo-impl-header>>

<<quiz-repo-flashcards>>

<<quiz-repo-transaction>>

<<quiz-repo-impl-footer>>

<<quiz-repo-conversions>>