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

import kotlin.time.Instant

/**
 * The kind of timestamp associated with a task heading.
 * - SCHEDULED: from a `SCHEDULED:` planning line
 * - DEADLINE: from a `DEADLINE:` planning line
 * - FLOATING: an active timestamp in the heading body (e.g., `<2026-07-31 Fri>`)
 */
enum class TaskTimestampKind {
    SCHEDULED,
    DEADLINE,
    FLOATING
}

/**
 * A single timestamp associated with a task heading.
 *
 * @param file The source org file path
 * @param position The byte offset of the heading in the file
 * @param kind SCHEDULED, DEADLINE, or FLOATING
 * @param date ISO date string (e.g., "2026-07-31") for date-range queries
 * @param timestamp Epoch seconds (UTC) for time-of-day ordering, or null if the
 *                  org-mode timestamp had no time component
 * @param repeater The raw repeater string (e.g., "+1d", "++1w", ".+1m") if the
 *                 org-mode timestamp carried a repeater, or null otherwise.
 *                 Used by the [[agenda/habits.org][HabitViewModel]] to size the
 *                 consistency-graph window per the repeater period.
 */
data class TaskTimestamp(
    val file: String,
    val position: Int,
    val kind: TaskTimestampKind,
    val date: String,
    val timestamp: Long?,
    val repeater: String? = null
)

/**
 * A heading extracted for the agenda system.
 *
 * Unlike [OrgNode], a TaskHeading may not have an `:ID:` property — org-agenda
 * works on all headings, not just nodes. The heading is identified by the
 * composite (file, position) key.
 *
 * @param file The source org file path
 * @param position The byte offset of the heading in the file
 * @param level The heading level (number of asterisks)
 * @param todo The TODO state (e.g., "TODO", "DONE"), or null if none
 * @param priority The priority cookie (e.g., "#A"), or null if none
 * @param title The heading text without markup or tags
 * @param outlinePath The breadcrumb path of ancestor heading titles
 * @param nodeId The `:ID:` property if the heading is also an org-roam node, or null
 * @param tags The heading's tags (including inherited filetags)
 * @param isHabit True if the heading has a `:STYLE: habit` property
 * @param timestamps All timestamps (SCHEDULED, DEADLINE, FLOATING) for this heading
 */
data class TaskHeading(
    val file: String,
    val position: Int,
    val level: Int,
    val todo: String?,
    val priority: String?,
    val title: String?,
    val outlinePath: List<String>,
    val nodeId: String?,
    val tags: List<String>,
    val isHabit: Boolean,
    val timestamps: List<TaskTimestamp> = emptyList()
)

/**
 * A single TODO state transition recorded in the heading's :LOGBOOK: drawer.
 *
 * @param id The autoincrement row id
 * @param file The source org file path
 * @param position The byte offset of the heading in the file
 * @param fromState The previous TODO state, or null if the heading had none
 * @param toState The new TODO state, or null if the state was cleared
 * @param timestamp When the transition was recorded
 */
data class TaskStateChange(
    val id: Long,
    val file: String,
    val position: Int,
    val fromState: String?,
    val toState: String?,
    val timestamp: Instant
)

/**
 * Per-file task state summary used by the Projects screen.
 *
 * @param file The source org file path
 * @param doneCount Number of task headings in a final state (DONE/CANCELLED/ARCHIVED)
 * @param openCount Number of task headings not in a final state (includes null todo)
 * @param totalCount Total task headings in the file
 * @param isStuck True when all tasks are in final state (openCount == 0, doneCount > 0)
 *                and the file has not been retired. Computed by the ViewModel with
 *                retirement info from [RoamRepository.getTagsByNode] on the level-0 node.
 * @param isRetired True when the file's level-0 node carries a =CLOSED= filetag.
 *                  Computed by the ViewModel.
 * @param tags File-level tags (filetags) from the level-0 node. Populated by the
 *             ViewModel via [RoamRepository.getTagsByNode].
 */
data class FileTaskSummary(
    val file: String,
    val doneCount: Long,
    val openCount: Long,
    val totalCount: Long,
    val isStuck: Boolean = false,
    val isRetired: Boolean = false,
    val tags: List<String> = emptyList()
)

<<agenda-models-preamble>>

<<agenda-models-timestamp-kind>>

<<agenda-models-task-timestamp>>

<<agenda-models-task-heading>>

<<agenda-models-task-state-change>>

<<agenda-models-file-task-summary>>

-- Agenda task support (org-agenda-style headings, timestamps, habits)
CREATE TABLE IF NOT EXISTS task_headings (
    file TEXT NOT NULL,
    position INTEGER NOT NULL,
    level INTEGER NOT NULL,
    todo TEXT,
    priority TEXT,
    title TEXT,
    outline_path TEXT,
    node_id TEXT,
    is_habit INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (file, position)
);

-- Normalized timestamps for task headings.
-- A heading may have multiple timestamps (SCHEDULED + DEADLINE + several FLOATING).
-- No PRIMARY KEY: a heading can have multiple floating timestamps of the same
-- kind on the same date. The indexer deletes by (file, position) before
-- re-inserting on each index run.
-- `repeater` stores the raw org-mode repeater string (e.g., "+1d", "++1w", ".+1m")
-- so the HabitViewModel can size the consistency-graph window without re-parsing
-- the source file.
CREATE TABLE IF NOT EXISTS task_timestamps (
    file TEXT NOT NULL,
    position INTEGER NOT NULL,
    kind TEXT NOT NULL,
    date TEXT NOT NULL,
    ts INTEGER,
    repeater TEXT,
    FOREIGN KEY (file, position) REFERENCES task_headings (file, position) ON DELETE CASCADE
);

-- Normalized task tags.
-- Each task heading may carry zero or more tags (heading-level tags plus
-- inherited filetags). Tags are stored one-per-row so the Tagged Tasks view
-- can filter and group on them without parsing a space-joined column. The
-- composite (file, position) key joins to task_headings and works for
-- non-node headings (those without an :ID: property), unlike the roam
-- `tags` table which is keyed by node_id.
CREATE TABLE IF NOT EXISTS task_tags (
    file TEXT NOT NULL,
    position INTEGER NOT NULL,
    tag TEXT NOT NULL,
    PRIMARY KEY (file, position, tag),
    FOREIGN KEY (file, position) REFERENCES task_headings (file, position) ON DELETE CASCADE
);

-- LOGBOOK state-change history for habit consistency sparklines.
CREATE TABLE IF NOT EXISTS task_state_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    file TEXT NOT NULL,
    position INTEGER NOT NULL,
    from_state TEXT,
    to_state TEXT,
    timestamp INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_task_headings_file ON task_headings (file);
CREATE INDEX IF NOT EXISTS idx_task_headings_todo ON task_headings (todo);
CREATE INDEX IF NOT EXISTS idx_task_headings_node ON task_headings (node_id);
CREATE INDEX IF NOT EXISTS idx_task_timestamps_date ON task_timestamps (date);
CREATE INDEX IF NOT EXISTS idx_task_timestamps_kind ON task_timestamps (kind);
CREATE INDEX IF NOT EXISTS idx_task_timestamps_file_pos ON task_timestamps (file, position);
CREATE INDEX IF NOT EXISTS idx_task_state_history_file_pos ON task_state_history (file, position);
CREATE INDEX IF NOT EXISTS idx_task_tags_tag ON task_tags (tag);
CREATE INDEX IF NOT EXISTS idx_task_tags_file_pos ON task_tags (file, position);

-- Task heading operations
insertTaskHeading:
INSERT OR REPLACE INTO task_headings (file, position, level, todo, priority, title, outline_path, node_id, is_habit)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);

deleteTaskHeadingsByFile:
DELETE FROM task_headings WHERE file = ?;

selectTaskHeadingsByFile:
SELECT * FROM task_headings WHERE file = ? ORDER BY position;

selectTaskHeading:
SELECT * FROM task_headings WHERE file = ? AND position = ?;

selectTaskHeadingsByTodoState:
SELECT * FROM task_headings WHERE todo = ? ORDER BY priority, position;

selectHabits:
SELECT * FROM task_headings WHERE is_habit = 1 ORDER BY file, position;

-- Files with task counts (for project view sorting by # of due tasks)
selectFilesWithTaskCounts:
SELECT file, COUNT(*) AS count FROM task_headings GROUP BY file ORDER BY count DESC;

-- All task headings, ordered for the Tagged view's "meta" untagged section.
-- The repository filters out completed/null-todo headings in Kotlin so this
-- query returns the table's Task_headings row type (matching toTaskHeadingDomain).
selectAllTaskHeadings:
SELECT * FROM task_headings ORDER BY priority, position;

-- Task headings with no tags. Used by the Tagged view's top "meta" section.
-- The repository filters out completed/null-todo headings in Kotlin.
selectUntaggedTaskHeadings:
SELECT th.*
FROM task_headings th
WHERE NOT EXISTS (
    SELECT 1 FROM task_tags tt WHERE tt.file = th.file AND tt.position = th.position
)
ORDER BY th.priority, th.position;

-- Task timestamp operations
insertTaskTimestamp:
INSERT INTO task_timestamps (file, position, kind, date, ts, repeater)
VALUES (?, ?, ?, ?, ?, ?);

deleteTaskTimestampsByFileAndPosition:
DELETE FROM task_timestamps WHERE file = ? AND position = ?;

deleteTaskTimestampsByFile:
DELETE FROM task_timestamps WHERE file = ?;

selectTimestampsByFileAndPosition:
SELECT * FROM task_timestamps WHERE file = ? AND position = ? ORDER BY date, ts;

-- Task tag operations.
-- The indexer deletes by (file, position) before re-inserting on each index
-- run, mirroring task_timestamps, so stale tags from a previous parse are
-- cleared before the new set is written.
insertTaskTag:
INSERT OR REPLACE INTO task_tags (file, position, tag)
VALUES (?, ?, ?);

deleteTaskTagsByFileAndPosition:
DELETE FROM task_tags WHERE file = ? AND position = ?;

deleteTaskTagsByFile:
DELETE FROM task_tags WHERE file = ?;

selectTaskTagsByFileAndPosition:
SELECT tag FROM task_tags WHERE file = ? AND position = ? ORDER BY tag;

-- Agenda day-view: timestamps in a date range, joined to task_headings.
-- Returns one row per timestamp; the repository dedupes by (file, position)
-- to produce TaskHeading objects with populated timestamps lists.
-- Habits are excluded — they live in the Habits tab.
selectTaskHeadingsForDateRange:
SELECT th.*, tt.kind AS ts_kind, tt.date AS ts_date, tt.ts AS ts_ts
FROM task_headings th
JOIN task_timestamps tt ON th.file = tt.file AND th.position = tt.position
WHERE tt.date >= ? AND tt.date <= ?
  AND th.is_habit = 0
ORDER BY tt.date, tt.ts;

-- Overdue tasks: DEADLINE or SCHEDULED with date < today AND todo NOT DONE/CANCELLED.
-- Uses a correlated subquery to find the earliest deadline/scheduled per heading.
-- Habits are excluded — they live in the Habits tab.
selectOverdueTaskHeadings:
SELECT DISTINCT th.*
FROM task_headings th
JOIN task_timestamps tt ON th.file = tt.file AND th.position = tt.position
WHERE tt.date < ?
  AND tt.kind IN ('DEADLINE', 'SCHEDULED')
  AND (th.todo IS NULL OR th.todo NOT IN ('DONE', 'CANCELLED', 'ARCHIVED'))
  AND th.is_habit = 0
ORDER BY tt.date;

-- Tasks by tag. Joins task_tags on the composite (file, position) key so
-- non-node headings (node_id NULL) are included — the agenda system works on
-- all headings, not just nodes. The repository filters out completed/null-todo
-- headings in Kotlin so this query returns the table's Task_headings row type.
selectTaskHeadingsByTag:
SELECT DISTINCT th.*
FROM task_headings th
JOIN task_tags tt ON th.file = tt.file AND th.position = tt.position
WHERE tt.tag = ?
ORDER BY th.priority, th.position;

-- Tag counts for the Tagged view. Each row is a tag and the number of
-- distinct open task headings carrying it. The WHERE clause excludes
-- completed tasks and tasks without a TODO keyword so counts match what is
-- displayed when drilling in. The return type (tag, count) is unaffected by
-- the todo filter.
selectTaskTagsWithCounts:
SELECT tt.tag, COUNT(*) AS count
FROM task_tags tt
JOIN task_headings th ON tt.file = th.file AND tt.position = th.position
WHERE th.todo IS NOT NULL
  AND th.todo NOT IN ('DONE', 'CANCELLED', 'ARCHIVED')
GROUP BY tt.tag
ORDER BY count DESC;

-- Task state history operations
insertTaskStateChange:
INSERT INTO task_state_history (file, position, from_state, to_state, timestamp)
VALUES (?, ?, ?, ?, ?);

deleteTaskStateChangesByFile:
DELETE FROM task_state_history WHERE file = ?;

selectTaskStateHistoryByFileAndPosition:
SELECT * FROM task_state_history
WHERE file = ? AND position = ?
ORDER BY timestamp DESC;

-- Habit consistency: state transitions for a heading in a date range.
selectHabitConsistency:
SELECT * FROM task_state_history
WHERE file = ? AND position = ? AND timestamp >= ? AND timestamp <= ?
ORDER BY timestamp ASC;

-- Project view: per-file task state summary (done/open/total counts).
-- Used by ProjectViewModel to compute isStuck (openCount == 0 && doneCount > 0).
selectFileTaskStateSummary:
SELECT file,
  SUM(CASE WHEN todo IN ('DONE','CANCELLED','ARCHIVED') THEN 1 ELSE 0 END) AS done_count,
  SUM(CASE WHEN todo IS NULL OR todo NOT IN ('DONE','CANCELLED','ARCHIVED') THEN 1 ELSE 0 END) AS open_count,
  COUNT(*) AS total_count
FROM task_headings GROUP BY file;

-- Project view: distinct tags on level-0 nodes that belong to files with tasks.
-- File-level tags (filetags) are stored on the level-0 node in the tags table.
selectFileTagsWithProjects:
SELECT DISTINCT t.tag
FROM tags t
JOIN nodes n ON t.node_id = n.id
WHERE n.level = 0
  AND EXISTS (SELECT 1 FROM task_headings th WHERE th.file = n.file)
ORDER BY t.tag;

-- Project view: tags for a specific file's level-0 node (for retirement detection).
-- The level-0 node's tags include inherited filetags; CLOSED among them means retired.
selectFileTagsByFile:
SELECT t.tag
FROM tags t
JOIN nodes n ON t.node_id = n.id
WHERE n.level = 0 AND n.file = ?
ORDER BY t.tag;

-- Habits due today: SCHEDULED on or before today and not completed.
-- When a habit is marked DONE, the repeater advances its SCHEDULED date,
-- so past-due habits that were completed today no longer match.
selectHabitsDueToday:
SELECT DISTINCT th.*
FROM task_headings th
JOIN task_timestamps tt ON th.file = tt.file AND th.position = tt.position
WHERE th.is_habit = 1
  AND tt.kind = 'SCHEDULED'
  AND tt.date <= ?
  AND (th.todo IS NULL OR th.todo NOT IN ('DONE', 'CANCELLED', 'ARCHIVED'));

-- Statistics
selectTaskHeadingCount:
SELECT COUNT(*) FROM task_headings;

selectOverdueTaskCount:
SELECT COUNT(DISTINCT th.file || ':' || th.position)
FROM task_headings th
JOIN task_timestamps tt ON th.file = tt.file AND th.position = tt.position
WHERE tt.date < ?
  AND tt.kind IN ('DEADLINE', 'SCHEDULED')
  AND (th.todo IS NULL OR th.todo NOT IN ('DONE', 'CANCELLED', 'ARCHIVED'));

<<db-agenda-schema>>

<<db-agenda-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 kotlinx.datetime.TimeZone
import kotlinx.datetime.toLocalDateTime
import kotlin.time.Clock
import kotlin.time.Instant
import kotlinx.coroutines.*
import kotlinx.coroutines.ExperimentalCoroutinesApi

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

    // Task heading CRUD
    suspend fun insertTaskHeading(heading: TaskHeading)
    suspend fun insertTaskTimestamp(timestamp: TaskTimestamp)
    suspend fun deleteTaskHeadingsByFile(filePath: String)
    suspend fun deleteTaskTimestampsByFile(filePath: String)
    suspend fun deleteTaskTagsByFile(filePath: String)
    suspend fun getTaskHeadingsByFile(filePath: String): List<TaskHeading>
    suspend fun getTaskHeading(file: String, position: Int): TaskHeading?
    suspend fun getTaskHeadingsByTodoState(state: String): List<TaskHeading>
    suspend fun getHabits(): List<TaskHeading>
    suspend fun getHabitsDueToday(): List<TaskHeading>
    suspend fun getAllTaskHeadings(): List<TaskHeading>
    suspend fun getUntaggedTaskHeadings(): List<TaskHeading>

    // Timestamp queries
    suspend fun getTimestamps(file: String, position: Int): List<TaskTimestamp>

    // Agenda queries
    suspend fun getTasksForDateRange(startDate: LocalDate, endDate: LocalDate): List<TaskHeading>
    suspend fun getOverdueTasks(): List<TaskHeading>
    suspend fun countOverdueTasks(): Long

    // Project / tag queries
    suspend fun getFilesWithTaskCounts(): List<Pair<String, Long>>
    suspend fun getTasksByTag(tag: String): List<TaskHeading>
    suspend fun getTaskTagsWithCounts(): List<Pair<String, Long>>

    // Project view queries
    suspend fun getFileTaskStateSummaries(): List<FileTaskSummary>
    suspend fun getProjectTags(): List<String>
    suspend fun getFileTags(file: String): List<String>

    // State history
    suspend fun insertTaskStateChange(change: TaskStateChange)
    suspend fun deleteTaskStateChangesByFile(filePath: String)
    suspend fun getTaskStateHistory(file: String, position: Int): List<TaskStateChange>
    suspend fun getHabitConsistency(file: String, position: Int, startEpoch: Long, endEpoch: Long): List<TaskStateChange>
}

class AgendaRepositoryImpl(
    private val database: ArcologyDatabase
) : AgendaRepository {

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

    private val completedTodoStates = setOf("DONE", "CANCELLED", "ARCHIVED")

    /** True when the heading has a TODO keyword and is not in a completed state. */
    private fun TaskHeading.isOpenTask(): Boolean =
        todo != null && todo !in completedTodoStates

    // Task heading operations
    override suspend fun insertTaskHeading(heading: TaskHeading) {
        database.agendaQueries.insertTaskHeading(
            heading.file,
            heading.position.toLong(),
            heading.level.toLong(),
            heading.todo,
            heading.priority,
            heading.title,
            heading.outlinePath.joinToString("/"),
            heading.nodeId,
            if (heading.isHabit) 1L else 0L
        )
        // Replace tags: delete then re-insert so stale tags from a previous
        // parse are cleared before the new set is written.
        database.agendaQueries.deleteTaskTagsByFileAndPosition(heading.file, heading.position.toLong())
        heading.tags.forEach { tag ->
            database.agendaQueries.insertTaskTag(heading.file, heading.position.toLong(), tag)
        }
        // Insert timestamps alongside the heading
        database.agendaQueries.deleteTaskTimestampsByFileAndPosition(heading.file, heading.position.toLong())
        heading.timestamps.forEach { ts ->
            database.agendaQueries.insertTaskTimestamp(
                ts.file,
                ts.position.toLong(),
                ts.kind.name,
                ts.date,
                ts.timestamp,
                ts.repeater
            )
        }
    }

    override suspend fun insertTaskTimestamp(timestamp: TaskTimestamp) {
        database.agendaQueries.insertTaskTimestamp(
            timestamp.file,
            timestamp.position.toLong(),
            timestamp.kind.name,
            timestamp.date,
            timestamp.timestamp,
            timestamp.repeater
        )
    }

    override suspend fun deleteTaskHeadingsByFile(filePath: String) {
        database.agendaQueries.deleteTaskHeadingsByFile(filePath)
    }

    override suspend fun deleteTaskTimestampsByFile(filePath: String) {
        database.agendaQueries.deleteTaskTimestampsByFile(filePath)
    }

    override suspend fun deleteTaskTagsByFile(filePath: String) {
        database.agendaQueries.deleteTaskTagsByFile(filePath)
    }

    override suspend fun getTaskHeadingsByFile(filePath: String): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectTaskHeadingsByFile(filePath)
                .executeAsList().map { it.toTaskHeadingDomain(database) }
        }
    }

    override suspend fun getTaskHeading(file: String, position: Int): TaskHeading? {
        val row = database.agendaQueries.selectTaskHeading(file, position.toLong())
            .executeAsOneOrNull() ?: return null
        return row.toTaskHeadingDomain(database)
    }

    override suspend fun getTaskHeadingsByTodoState(state: String): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectTaskHeadingsByTodoState(state)
                .executeAsList().map { it.toTaskHeadingDomain(database) }
        }
    }

    override suspend fun getHabits(): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectHabits()
                .executeAsList().map { it.toTaskHeadingDomain(database) }
        }
    }

    override suspend fun getHabitsDueToday(): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            val today = Clock.System.now().toLocalDateTime(TimeZone.UTC).date.toString()
            database.agendaQueries.selectHabitsDueToday(today)
                .executeAsList().map { it.toTaskHeadingDomain(database) }
        }
    }

    override suspend fun getAllTaskHeadings(): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectAllTaskHeadings()
                .executeAsList().map { it.toTaskHeadingDomain(database) }
                .filter { it.isOpenTask() }
        }
    }

    override suspend fun getUntaggedTaskHeadings(): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectUntaggedTaskHeadings()
                .executeAsList().map { it.toTaskHeadingDomain(database) }
                .filter { it.isOpenTask() }
        }
    }

    // Timestamp queries
    override suspend fun getTimestamps(file: String, position: Int): List<TaskTimestamp> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectTimestampsByFileAndPosition(file, position.toLong())
                .executeAsList().map { it.toTaskTimestampDomain() }
        }
    }

    // Agenda queries
    override suspend fun getTasksForDateRange(startDate: LocalDate, endDate: LocalDate): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            val rows = database.agendaQueries.selectTaskHeadingsForDateRange(
                startDate.toString(),
                endDate.toString()
            ).executeAsList()

            // Group rows by (file, position) and collect timestamps.
            // Each row is one timestamp; a heading with multiple timestamps appears multiple times.
            val grouped = rows.groupBy { it.file_ to it.position }
            grouped.values.map { groupRows ->
                val first = groupRows.first()
                val timestamps = groupRows.map { row ->
                    TaskTimestamp(
                        file = row.file_,
                        position = row.position.toInt(),
                        kind = TaskTimestampKind.valueOf(row.ts_kind),
                        date = row.ts_date,
                        timestamp = row.ts_ts
                    )
                }
                TaskHeading(
                    file = first.file_,
                    position = first.position.toInt(),
                    level = first.level.toInt(),
                    todo = first.todo,
                    priority = first.priority,
                    title = first.title,
                    outlinePath = first.outline_path?.split("/")?.filter { it.isNotEmpty() } ?: emptyList(),
                    nodeId = first.node_id,
                    tags = database.agendaQueries.selectTaskTagsByFileAndPosition(first.file_, first.position).executeAsList(),
                    isHabit = first.is_habit == 1L,
                    timestamps = timestamps
                )
            }
        }
    }

    override suspend fun getOverdueTasks(): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            val today = Clock.System.now().toLocalDateTime(TimeZone.UTC).date.toString()
            database.agendaQueries.selectOverdueTaskHeadings(today)
                .executeAsList().map { it.toTaskHeadingDomain(database) }
        }
    }

    override suspend fun countOverdueTasks(): Long {
        return withContext(Dispatchers.IO) {
            val today = Clock.System.now().toLocalDateTime(TimeZone.UTC).date.toString()
            database.agendaQueries.selectOverdueTaskCount(today).executeAsOne()
        }
    }

    // Project / tag queries
    override suspend fun getFilesWithTaskCounts(): List<Pair<String, Long>> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectFilesWithTaskCounts { file, count ->
                file to count
            }.executeAsList()
        }
    }

    override suspend fun getTasksByTag(tag: String): List<TaskHeading> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectTaskHeadingsByTag(tag)
                .executeAsList().map { it.toTaskHeadingDomain(database) }
                .filter { it.isOpenTask() }
        }
    }

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

    // Project view queries
    override suspend fun getFileTaskStateSummaries(): List<FileTaskSummary> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectFileTaskStateSummary { file, doneCount, openCount, totalCount ->
                FileTaskSummary(
                    file = file,
                    doneCount = doneCount ?: 0L,
                    openCount = openCount ?: 0L,
                    totalCount = totalCount
                )
            }.executeAsList()
        }
    }

    override suspend fun getProjectTags(): List<String> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectFileTagsWithProjects().executeAsList()
        }
    }

    override suspend fun getFileTags(file: String): List<String> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectFileTagsByFile(file).executeAsList()
        }
    }

    // State history
    override suspend fun insertTaskStateChange(change: TaskStateChange) {
        database.agendaQueries.insertTaskStateChange(
            change.file,
            change.position.toLong(),
            change.fromState,
            change.toState,
            change.timestamp.epochSeconds
        )
    }

    override suspend fun deleteTaskStateChangesByFile(filePath: String) {
        database.agendaQueries.deleteTaskStateChangesByFile(filePath)
    }

    override suspend fun getTaskStateHistory(file: String, position: Int): List<TaskStateChange> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectTaskStateHistoryByFileAndPosition(file, position.toLong())
                .executeAsList().map { it.toTaskStateChangeDomain() }
        }
    }

    override suspend fun getHabitConsistency(
        file: String,
        position: Int,
        startEpoch: Long,
        endEpoch: Long
    ): List<TaskStateChange> {
        return withContext(Dispatchers.IO) {
            database.agendaQueries.selectHabitConsistency(
                file, position.toLong(), startEpoch, endEpoch
            ).executeAsList().map { it.toTaskStateChangeDomain() }
        }
    }

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

}

// Task heading domain conversion.
// Fetches timestamps from task_timestamps and tags from task_tags in secondary
// queries.
private suspend fun computer.whatthefuck.arcology.db.Task_headings.toTaskHeadingDomain(
    database: ArcologyDatabase
): TaskHeading {
    val timestamps = database.agendaQueries.selectTimestampsByFileAndPosition(file_, position)
        .executeAsList().map { it.toTaskTimestampDomain() }
    return TaskHeading(
        file = file_,
        position = position.toInt(),
        level = level.toInt(),
        todo = todo,
        priority = priority,
        title = title,
        outlinePath = outline_path?.split("/")?.filter { it.isNotEmpty() } ?: emptyList(),
        nodeId = node_id,
        tags = database.agendaQueries.selectTaskTagsByFileAndPosition(file_, position).executeAsList(),
        isHabit = is_habit == 1L,
        timestamps = timestamps
    )
}

private fun computer.whatthefuck.arcology.db.Task_timestamps.toTaskTimestampDomain(): TaskTimestamp {
    return TaskTimestamp(
        file = file_,
        position = position.toInt(),
        kind = TaskTimestampKind.valueOf(kind),
        date = date,
        timestamp = ts,
        repeater = repeater
    )
}

private fun computer.whatthefuck.arcology.db.Task_state_history.toTaskStateChangeDomain(): TaskStateChange {
    return TaskStateChange(
        id = id,
        file = file_,
        position = position.toInt(),
        fromState = from_state,
        toState = to_state,
        timestamp = Instant.fromEpochSeconds(timestamp)
    )
}

<<agenda-repo-preamble>>

<<agenda-repo-interface>>

<<agenda-repo-impl-header>>

<<agenda-repo-tasks>>

<<agenda-repo-agenda>>

<<agenda-repo-projects>>

<<agenda-repo-history>>

<<agenda-repo-transaction>>

<<agenda-repo-impl-footer>>

<<agenda-repo-conversions>>

package computer.whatthefuck.arcology.domain

import kotlin.test.*
import kotlin.time.Instant

class TaskModelsTest {

    @Test
    fun `TaskTimestampKind enum has all three kinds`() {
        val values = TaskTimestampKind.values().map { it.name }
        assertTrue(values.contains("SCHEDULED"))
        assertTrue(values.contains("DEADLINE"))
        assertTrue(values.contains("FLOATING"))
        assertEquals(3, values.size)
    }

    @Test
    fun `TaskHeading defaults timestamps to empty list`() {
        val heading = TaskHeading(
            file = "/test.org",
            position = 10,
            level = 1,
            todo = "TODO",
            priority = null,
            title = "Test",
            outlinePath = emptyList(),
            nodeId = "test-id",
            tags = emptyList(),
            isHabit = false
        )
        assertTrue(heading.timestamps.isEmpty(), "Default timestamps should be empty")
    }

    @Test
    fun `TaskHeading equality with same fields`() {
        val heading1 = TaskHeading(
            file = "/test.org", position = 10, level = 1, todo = "TODO",
            priority = null, title = "Test", outlinePath = listOf("Parent", "Test"),
            nodeId = "test-id", tags = listOf("tag1"), isHabit = false,
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, "2026-07-31", null))
        )
        val heading2 = TaskHeading(
            file = "/test.org", position = 10, level = 1, todo = "TODO",
            priority = null, title = "Test", outlinePath = listOf("Parent", "Test"),
            nodeId = "test-id", tags = listOf("tag1"), isHabit = false,
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, "2026-07-31", null))
        )
        assertEquals(heading1, heading2)
    }

    @Test
    fun `TaskTimestamp equality`() {
        val ts1 = TaskTimestamp("/test.org", 10, TaskTimestampKind.FLOATING, "2026-07-31", 1234567890L)
        val ts2 = TaskTimestamp("/test.org", 10, TaskTimestampKind.FLOATING, "2026-07-31", 1234567890L)
        assertEquals(ts1, ts2)
    }

    @Test
    fun `TaskStateChange equality`() {
        val change1 = TaskStateChange(
            id = 1, file = "/test.org", position = 10,
            fromState = "TODO", toState = "DONE",
            timestamp = Instant.fromEpochSeconds(1640995200)
        )
        val change2 = TaskStateChange(
            id = 1, file = "/test.org", position = 10,
            fromState = "TODO", toState = "DONE",
            timestamp = Instant.fromEpochSeconds(1640995200)
        )
        assertEquals(change1, change2)
    }
}

package computer.whatthefuck.arcology.database

import computer.whatthefuck.arcology.domain.*
import kotlinx.coroutines.test.runTest
import kotlinx.datetime.LocalDate
import kotlin.test.*
import kotlin.time.Instant

class AgendaRepositoryTest {

    private fun createRepo(): AgendaRepository {
        val database = DatabaseFactory.createInMemoryDatabase()
        return AgendaRepositoryImpl(database)
    }

    private fun sampleTaskHeading(
        file: String = "/test.org",
        position: Int = 10,
        todo: String? = "TODO",
        title: String = "Test Task",
        nodeId: String? = "test-node-id",
        isHabit: Boolean = false,
        timestamps: List<TaskTimestamp> = emptyList()
    ): TaskHeading {
        return TaskHeading(
            file = file,
            position = position,
            level = 1,
            todo = todo,
            priority = null,
            title = title,
            outlinePath = listOf("Parent", title),
            nodeId = nodeId,
            tags = listOf("tag1", "tag2"),
            isHabit = isHabit,
            timestamps = timestamps
        )
    }

    @Test
    fun `insert and retrieve task heading by file`() = runTest {
        val repo = createRepo()
        val heading = sampleTaskHeading()

        repo.insertTaskHeading(heading)

        val retrieved = repo.getTaskHeadingsByFile("/test.org")
        assertEquals(1, retrieved.size)
        assertEquals("Test Task", retrieved.first().title)
        assertEquals("TODO", retrieved.first().todo)
        assertEquals("test-node-id", retrieved.first().nodeId)
        // Tags are stored in task_tags and round-trip back.
        assertEquals(listOf("tag1", "tag2"), retrieved.first().tags)
    }

    @Test
    fun `insert task heading with no tags round-trips empty list`() = runTest {
        val repo = createRepo()
        val heading = TaskHeading(
            file = "/test.org", position = 10, level = 1, todo = "TODO",
            priority = null, title = "No tags", outlinePath = emptyList(),
            nodeId = null, tags = emptyList(), isHabit = false
        )
        repo.insertTaskHeading(heading)

        val retrieved = repo.getTaskHeadingsByFile("/test.org")
        assertEquals(1, retrieved.size)
        assertTrue(retrieved.first().tags.isEmpty())
    }

    @Test
    fun `get all task headings returns all inserted`() = runTest {
        val repo = createRepo()
        repo.insertTaskHeading(sampleTaskHeading(position = 10, title = "A"))
        repo.insertTaskHeading(sampleTaskHeading(position = 20, title = "B"))
        repo.insertTaskHeading(sampleTaskHeading(file = "/other.org", position = 10, title = "C"))

        val all = repo.getAllTaskHeadings()
        assertEquals(3, all.size)
    }

    @Test
    fun `get untagged task headings returns only headings with no tags`() = runTest {
        val repo = createRepo()
        val tagged = sampleTaskHeading(position = 10, title = "tagged")
        val untagged = TaskHeading(
            file = "/test.org", position = 20, level = 1, todo = "TODO",
            priority = null, title = "untagged", outlinePath = emptyList(),
            nodeId = null, tags = emptyList(), isHabit = false
        )
        repo.insertTaskHeading(tagged)
        repo.insertTaskHeading(untagged)

        val untaggedOnly = repo.getUntaggedTaskHeadings()
        assertEquals(1, untaggedOnly.size)
        assertEquals("untagged", untaggedOnly.first().title)
    }

    @Test
    fun `get tasks by tag includes non-node headings`() = runTest {
        val repo = createRepo()
        // Non-node heading (nodeId null) with a tag
        val nonNode = TaskHeading(
            file = "/test.org", position = 10, level = 2, todo = "TODO",
            priority = null, title = "non-node task", outlinePath = emptyList(),
            nodeId = null, tags = listOf("urgent"), isHabit = false
        )
        // Node heading with the same tag
        val node = sampleTaskHeading(position = 20, title = "node task").copy(tags = listOf("urgent"))
        repo.insertTaskHeading(nonNode)
        repo.insertTaskHeading(node)

        val results = repo.getTasksByTag("urgent")
        assertEquals(2, results.size, "Non-node headings should be included via task_tags")
    }

    @Test
    fun `get task tags with counts reflects task_tags table`() = runTest {
        val repo = createRepo()
        repo.insertTaskHeading(sampleTaskHeading(position = 10).copy(tags = listOf("a", "b")))
        repo.insertTaskHeading(sampleTaskHeading(position = 20).copy(tags = listOf("a")))
        repo.insertTaskHeading(sampleTaskHeading(position = 30).copy(tags = listOf("a")))

        val counts = repo.getTaskTagsWithCounts().toMap()
        assertEquals(3L, counts["a"], "tag 'a' should have 3 task headings")
        assertEquals(1L, counts["b"], "tag 'b' should have 1 task heading")
    }

    @Test
    fun `tagged queries exclude completed tasks and tasks without a todo keyword`() = runTest {
        val repo = createRepo()
        // Open TODO task with a tag — should appear
        repo.insertTaskHeading(sampleTaskHeading(position = 10, todo = "TODO").copy(tags = listOf("work")))
        // DONE task with a tag — should be excluded
        repo.insertTaskHeading(sampleTaskHeading(position = 20, todo = "DONE").copy(tags = listOf("work")))
        // CANCELLED task with a tag — should be excluded
        repo.insertTaskHeading(sampleTaskHeading(position = 30, todo = "CANCELLED").copy(tags = listOf("work")))
        // No-todo heading with a tag — should be excluded
        repo.insertTaskHeading(sampleTaskHeading(position = 40, todo = null).copy(tags = listOf("work")))
        // Untagged open task — should appear in untagged + all
        repo.insertTaskHeading(sampleTaskHeading(position = 50, todo = "TODO").copy(tags = emptyList()))
        // Untagged DONE task — should be excluded
        repo.insertTaskHeading(sampleTaskHeading(position = 60, todo = "DONE").copy(tags = emptyList()))

        val byTag = repo.getTasksByTag("work")
        assertEquals(1, byTag.size, "Only the open TODO task should be returned by tag")
        assertEquals("TODO", byTag.first().todo)

        val counts = repo.getTaskTagsWithCounts().toMap()
        assertEquals(1L, counts["work"], "Count should reflect only open tasks")

        val untagged = repo.getUntaggedTaskHeadings()
        assertEquals(1, untagged.size, "Only the open untagged task should appear")
        assertEquals("TODO", untagged.first().todo)

        val all = repo.getAllTaskHeadings()
        assertEquals(2, all.size, "Only open tasks should appear in all-task-heading queries")
        assertTrue(all.all { it.todo == "TODO" })
    }

    @Test
    fun `delete task headings by file cascades to task_tags`() = runTest {
        val repo = createRepo()
        repo.insertTaskHeading(sampleTaskHeading(position = 10))
        repo.insertTaskHeading(sampleTaskHeading(position = 20))

        assertEquals(2, repo.getTaskHeadingsByFile("/test.org").size)

        repo.deleteTaskHeadingsByFile("/test.org")

        // Tags should be gone too: re-insert a heading at the same position and
        // confirm no stale tags leak through.
        repo.insertTaskHeading(sampleTaskHeading(position = 10).copy(tags = emptyList()))
        val retrieved = repo.getTaskHeadingsByFile("/test.org")
        assertEquals(1, retrieved.size)
        assertTrue(retrieved.first().tags.isEmpty(), "Stale tags should not survive delete + re-insert")
    }

    @Test
    fun `insert task heading with timestamps populates task_timestamps`() = runTest {
        val repo = createRepo()
        val timestamps = listOf(
            TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, "2026-07-31", null),
            TaskTimestamp("/test.org", 10, TaskTimestampKind.DEADLINE, "2026-08-15", null)
        )
        val heading = sampleTaskHeading(timestamps = timestamps)

        repo.insertTaskHeading(heading)

        val retrieved = repo.getTaskHeadingsByFile("/test.org")
        assertEquals(1, retrieved.size)
        assertEquals(2, retrieved.first().timestamps.size, "Should have two timestamps")
        val kinds = retrieved.first().timestamps.map { it.kind }
        assertTrue(kinds.contains(TaskTimestampKind.SCHEDULED))
        assertTrue(kinds.contains(TaskTimestampKind.DEADLINE))
    }

    @Test
    fun `delete task headings by file cascades to timestamps`() = runTest {
        val repo = createRepo()
        val timestamps = listOf(
            TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, "2026-07-31", null)
        )
        repo.insertTaskHeading(sampleTaskHeading(timestamps = timestamps))

        assertEquals(1, repo.getTaskHeadingsByFile("/test.org").size)

        repo.deleteTaskHeadingsByFile("/test.org")

        assertTrue(repo.getTaskHeadingsByFile("/test.org").isEmpty())
        assertTrue(repo.getTimestamps("/test.org", 10).isEmpty(), "Timestamps should be cascade-deleted")
    }

    @Test
    fun `get tasks for date range`() = runTest {
        val repo = createRepo()
        val heading1 = sampleTaskHeading(
            position = 10, title = "Task on 2026-07-31",
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, "2026-07-31", null))
        )
        val heading2 = sampleTaskHeading(
            position = 20, title = "Task on 2026-08-15",
            timestamps = listOf(TaskTimestamp("/test.org", 20, TaskTimestampKind.SCHEDULED, "2026-08-15", null))
        )
        repo.insertTaskHeading(heading1)
        repo.insertTaskHeading(heading2)

        val results = repo.getTasksForDateRange(
            LocalDate.parse("2026-07-01"),
            LocalDate.parse("2026-08-01")
        )

        assertEquals(1, results.size, "Should return only the task in the date range")
        assertEquals("Task on 2026-07-31", results.first().title)
        assertEquals(1, results.first().timestamps.size, "Should have timestamps populated")
    }

    @Test
    fun `get overdue tasks excludes done states`() = runTest {
        val repo = createRepo()
        val pastDate = LocalDate.parse("2020-01-01").toString()
        val todoHeading = sampleTaskHeading(
            position = 10, todo = "TODO",
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.DEADLINE, pastDate, null))
        )
        val doneHeading = sampleTaskHeading(
            position = 20, todo = "DONE",
            timestamps = listOf(TaskTimestamp("/test.org", 20, TaskTimestampKind.DEADLINE, pastDate, null))
        )
        repo.insertTaskHeading(todoHeading)
        repo.insertTaskHeading(doneHeading)

        val overdue = repo.getOverdueTasks()

        assertEquals(1, overdue.size, "Only the TODO task should be overdue")
        assertEquals("TODO", overdue.first().todo)
    }

    @Test
    fun `get habits returns only habit headings`() = runTest {
        val repo = createRepo()
        repo.insertTaskHeading(sampleTaskHeading(position = 10, isHabit = true, title = "Daily habit"))
        repo.insertTaskHeading(sampleTaskHeading(position = 20, isHabit = false, title = "Regular task"))

        val habits = repo.getHabits()

        assertEquals(1, habits.size)
        assertTrue(habits.first().isHabit)
        assertEquals("Daily habit", habits.first().title)
    }

    @Test
    fun `get habits due today returns pending habits scheduled on or before today`() = runTest {
        val repo = createRepo()
        val pastDate = LocalDate.parse("2020-01-01").toString()
        val futureDate = LocalDate.parse("2099-12-31").toString()

        val dueHabit = sampleTaskHeading(
            position = 10, isHabit = true, todo = "TODO", title = "Due habit",
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, pastDate, null))
        )
        val doneHabit = sampleTaskHeading(
            position = 20, isHabit = true, todo = "DONE", title = "Done habit",
            timestamps = listOf(TaskTimestamp("/test.org", 20, TaskTimestampKind.SCHEDULED, pastDate, null))
        )
        val futureHabit = sampleTaskHeading(
            position = 30, isHabit = true, todo = "TODO", title = "Future habit",
            timestamps = listOf(TaskTimestamp("/test.org", 30, TaskTimestampKind.SCHEDULED, futureDate, null))
        )
        val dueTask = sampleTaskHeading(
            position = 40, isHabit = false, todo = "TODO", title = "Due task",
            timestamps = listOf(TaskTimestamp("/test.org", 40, TaskTimestampKind.SCHEDULED, pastDate, null))
        )

        repo.insertTaskHeading(dueHabit)
        repo.insertTaskHeading(doneHabit)
        repo.insertTaskHeading(futureHabit)
        repo.insertTaskHeading(dueTask)

        val due = repo.getHabitsDueToday()

        assertEquals(1, due.size, "Only the pending habit scheduled today or earlier should be due")
        assertEquals("Due habit", due.first().title)
    }

    @Test
    fun `get tasks for date range excludes habits`() = runTest {
        val repo = createRepo()
        val date = "2026-07-31"
        val habit = sampleTaskHeading(
            position = 10, isHabit = true, title = "Daily habit",
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, date, null))
        )
        val task = sampleTaskHeading(
            position = 20, isHabit = false, title = "Regular task",
            timestamps = listOf(TaskTimestamp("/test.org", 20, TaskTimestampKind.SCHEDULED, date, null))
        )
        repo.insertTaskHeading(habit)
        repo.insertTaskHeading(task)

        val results = repo.getTasksForDateRange(LocalDate.parse(date), LocalDate.parse(date))

        assertEquals(1, results.size, "Habits should be excluded from the agenda day view")
        assertEquals("Regular task", results.first().title)
    }

    @Test
    fun `get overdue tasks excludes habits`() = runTest {
        val repo = createRepo()
        val pastDate = LocalDate.parse("2020-01-01").toString()
        val habit = sampleTaskHeading(
            position = 10, isHabit = true, todo = "TODO", title = "Overdue habit",
            timestamps = listOf(TaskTimestamp("/test.org", 10, TaskTimestampKind.SCHEDULED, pastDate, null))
        )
        val task = sampleTaskHeading(
            position = 20, isHabit = false, todo = "TODO", title = "Overdue task",
            timestamps = listOf(TaskTimestamp("/test.org", 20, TaskTimestampKind.SCHEDULED, pastDate, null))
        )
        repo.insertTaskHeading(habit)
        repo.insertTaskHeading(task)

        val overdue = repo.getOverdueTasks()

        assertEquals(1, overdue.size, "Habits should be excluded from overdue")
        assertEquals("Overdue task", overdue.first().title)
    }

    @Test
    fun `get tasks by todo state`() = runTest {
        val repo = createRepo()
        repo.insertTaskHeading(sampleTaskHeading(position = 10, todo = "TODO", title = "Active"))
        repo.insertTaskHeading(sampleTaskHeading(position = 20, todo = "DONE", title = "Completed"))
        repo.insertTaskHeading(sampleTaskHeading(position = 30, todo = "TODO", title = "Another active"))

        val todoTasks = repo.getTaskHeadingsByTodoState("TODO")

        assertEquals(2, todoTasks.size)
        val titles = todoTasks.map { it.title }.filterNotNull().sorted()
        assertEquals(listOf("Active", "Another active"), titles)
    }

    @Test
    fun `get files with task counts`() = runTest {
        val repo = createRepo()
        repo.insertTaskHeading(sampleTaskHeading(file = "/file1.org", position = 10))
        repo.insertTaskHeading(sampleTaskHeading(file = "/file1.org", position = 20))
        repo.insertTaskHeading(sampleTaskHeading(file = "/file2.org", position = 10))

        val counts = repo.getFilesWithTaskCounts()

        assertEquals(2, counts.size)
        val file1Count = counts.first { it.first == "/file1.org" }.second
        assertEquals(2L, file1Count)
        val file2Count = counts.first { it.first == "/file2.org" }.second
        assertEquals(1L, file2Count)
    }

    @Test
    fun `insert and retrieve task state change`() = runTest {
        val repo = createRepo()
        val change = TaskStateChange(
            id = 0, file = "/test.org", position = 10,
            fromState = "TODO", toState = "DONE",
            timestamp = Instant.fromEpochSeconds(1640995200)
        )

        repo.insertTaskStateChange(change)

        val history = repo.getTaskStateHistory("/test.org", 10)
        assertEquals(1, history.size)
        assertEquals("TODO", history.first().fromState)
        assertEquals("DONE", history.first().toState)
    }

    @Test
    fun `get habit consistency in date range`() = runTest {
        val repo = createRepo()
        val earlyChange = TaskStateChange(
            id = 0, file = "/test.org", position = 10,
            fromState = "TODO", toState = "DONE",
            timestamp = Instant.fromEpochSeconds(1640995200) // 2022-01-01
        )
        val lateChange = TaskStateChange(
            id = 0, file = "/test.org", position = 10,
            fromState = "TODO", toState = "DONE",
            timestamp = Instant.fromEpochSeconds(1700000000) // 2023-11
        )
        repo.insertTaskStateChange(earlyChange)
        repo.insertTaskStateChange(lateChange)

        val inRange = repo.getHabitConsistency(
            "/test.org", 10,
            startEpoch = 1700000000,
            endEpoch = 1800000000
        )

        assertEquals(1, inRange.size, "Should return only the change in the date range")
    }

    @Test
    fun `delete task state changes by file`() = runTest {
        val repo = createRepo()
        val change = TaskStateChange(
            id = 0, file = "/test.org", position = 10,
            fromState = "TODO", toState = "DONE",
            timestamp = Instant.fromEpochSeconds(1640995200)
        )
        repo.insertTaskStateChange(change)

        assertEquals(1, repo.getTaskStateHistory("/test.org", 10).size)

        repo.deleteTaskStateChangesByFile("/test.org")

        assertTrue(repo.getTaskStateHistory("/test.org", 10).isEmpty(), "State changes should be deleted")
    }
}