Vibe coding全紀錄-51

Ａ：

要請幫忙一步一步詳細仔細的列出最終版的下列程式碼，謝謝

data/local/dao/*

​AccountDao.kt

​CategoryDao.kt

​ProjectDao.kt

​SnapshotDao.kt

​TransactionDao.kt

​RecurringTransactionDao.kt 

Ｑ：

好的，當然。

我們繼續進行專案的完整程式碼回顧。DAO (Data Access Object) 是我們 App 中數據庫操作的「執行者」，裡面定義了所有與資料庫溝通的 SQL 查詢和方法。清晰、高效的 DAO 是整個 App 效能與穩定性的基礎。

以下是 data/local/dao/ 套件底下所有 DAO 介面的最終版程式碼。

---

第廿三步：最終程式碼回顧 (DAO 篇)

檔案路徑: app/src/main/java/com/yourcompany/cashflow/data/local/dao/AccountDao.kt

<details>

<summary><strong>點此展開 AccountDao.kt 程式碼</strong></summary>

Kotlin

package com.yourcompany.cashflow.data.local.dao

import androidx.room.*
import com.yourcompany.cashflow.data.local.entity.Account
import kotlinx.coroutines.flow.Flow

@Dao
interface AccountDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(account: Account)

    @Update
    suspend fun update(account: Account)

    @Delete
    suspend fun delete(account: Account)

    @Query("SELECT * FROM accounts ORDER BY type, name ASC")
    fun getAllAccounts(): Flow<List<Account>>
}

</details>

---

檔案路徑: app/src/main/java/com/yourcompany/cashflow/data/local/dao/CategoryDao.kt

<details>

<summary><strong>點此展開 CategoryDao.kt 程式碼</strong></summary>

Kotlin

package com.yourcompany.cashflow.data.local.dao

import androidx.room.*
import com.yourcompany.cashflow.data.local.entity.MainCategory
import com.yourcompany.cashflow.data.local.entity.SubCategory
import com.yourcompany.cashflow.data.local.entity.TransactionType
import com.yourcompany.cashflow.data.model.MainCategoryWithSubCategories
import kotlinx.coroutines.flow.Flow

@Dao
interface CategoryDao {
    // --- Create ---
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertMainCategory(category: MainCategory)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertSubCategory(category: SubCategory)

    // --- Read ---
    @Transaction // Relational queries must be annotated with @Transaction
    @Query("SELECT * FROM main_categories ORDER BY name ASC")
    fun getMainCategoriesWithSubCategories(): Flow<List<MainCategoryWithSubCategories>>
    
    // Used in AddTransactionScreen to populate dropdowns
    @Query("SELECT * FROM main_categories WHERE type = :type ORDER BY name ASC")
    fun getAllMainCategoriesByType(type: TransactionType): Flow<List<MainCategory>>

    // --- Update ---
    @Update
    suspend fun updateMainCategory(category: MainCategory)

    @Update
    suspend fun updateSubCategory(category: SubCategory)

    // --- Delete ---
    @Delete
    suspend fun deleteMainCategory(category: MainCategory)

    @Delete
    suspend fun deleteSubCategory(category: SubCategory)
}

</details>

---

檔案路徑: app/src/main/java/com/yourcompany/cashflow/data/local/dao/ProjectDao.kt

<details>

<summary><strong>點此展開 ProjectDao.kt 程式碼</strong></summary>

Kotlin

package com.yourcompany.cashflow.data.local.dao

import androidx.room.*
import com.yourcompany.cashflow.data.local.entity.Project
import com.yourcompany.cashflow.data.local.entity.ProjectStatus
import kotlinx.coroutines.flow.Flow

@Dao
interface ProjectDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(project: Project)

    @Update
    suspend fun update(project: Project)

    @Delete
    suspend fun delete(project: Project)

    @Query("SELECT * FROM projects WHERE id = :id")
    fun getProjectById(id: Long): Flow<Project?>

    @Query("SELECT * FROM projects ORDER BY name ASC")
    fun getAllProjects(): Flow<List<Project>>

    @Query("SELECT * FROM projects WHERE status = :status ORDER BY name ASC")
    fun getProjectsByStatus(status: ProjectStatus): Flow<List<Project>>

    @Query("SELECT * FROM projects WHERE name = :name LIMIT 1")
    suspend fun getProjectByName(name: String): Project?
}

</details>

---

檔案路徑: app/src/main/java/com/yourcompany/cashflow/data/local/dao/SnapshotDao.kt

<details>

<summary><strong>點此展開 SnapshotDao.kt 程式碼</strong></summary>

Kotlin

package com.yourcompany.cashflow.data.local.dao

import androidx.room.*
import com.yourcompany.cashflow.data.local.entity.AccountSnapshot
import com.yourcompany.cashflow.data.model.AccountWithLatestValue
import com.yourcompany.cashflow.data.model.AssetAllocation
import com.yourcompany.cashflow.data.model.MonthlyTotal
import com.yourcompany.cashflow.data.model.NetWorth
import kotlinx.coroutines.flow.Flow
import java.time.LocalDate
import java.time.ZoneId

@Dao
interface SnapshotDao {

    @Query("SELECT * FROM account_snapshots WHERE accountId = :accountId AND strftime('%Y-%m', snapshotDate / 1000, 'unixepoch') = :yearMonth LIMIT 1")
    suspend fun findSnapshotForMonth(accountId: Long, yearMonth: String): AccountSnapshot?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(snapshot: AccountSnapshot)

    @Update
    suspend fun update(snapshot: AccountSnapshot)

    @Transaction
    suspend fun addOrUpdateSnapshot(accountId: Long, value: Double, date: LocalDate) {
        val yearMonth = date.toString().substring(0, 7) // Format "YYYY-MM"
        val existingSnapshot = findSnapshotForMonth(accountId, yearMonth)
        val timestamp = date.atStartOfDay(ZoneId.systemDefault()).toInstant().toEpochMilli()

        if (existingSnapshot != null) {
            update(existingSnapshot.copy(value = value, snapshotDate = timestamp))
        } else {
            insert(AccountSnapshot(accountId = accountId, value = value, snapshotDate = timestamp))
        }
    }

    @Query("""
        SELECT strftime('%Y-%m', s.snapshotDate / 1000, 'unixepoch') as yearMonth, SUM(s.value) as totalValue
        FROM account_snapshots s
        INNER JOIN accounts a ON s.accountId = a.id
        WHERE a.type IN ('BANK', 'INVESTMENT') AND strftime('%Y', s.snapshotDate / 1000, 'unixepoch') = :year
        GROUP BY yearMonth
        ORDER BY yearMonth ASC
    """)
    fun getYearlyAssetTrend(year: String): Flow<List<MonthlyTotal>>

    @Query("""
        SELECT a.assetClass, SUM(s.value) as totalValue
        FROM account_snapshots s
        INNER JOIN accounts a ON s.accountId = a.id
        WHERE s.snapshotDate IN (SELECT MAX(snapshotDate) FROM account_snapshots GROUP BY accountId)
        AND a.type IN ('BANK', 'INVESTMENT')
        GROUP BY a.assetClass
    """)
    fun getLatestAssetAllocation(): Flow<List<AssetAllocation>>

    @Query("""
        SELECT acc.*, s.value as latestValue
        FROM accounts acc
        LEFT JOIN account_snapshots s ON acc.id = s.accountId
        LEFT JOIN (
            SELECT accountId, MAX(snapshotDate) as max_date
            FROM account_snapshots
            GROUP BY accountId
        ) as s2 ON s.accountId = s2.accountId AND s.snapshotDate = s2.max_date
        ORDER BY acc.type, acc.name ASC
    """)
    fun getAccountsWithLatestValue(): Flow<List<AccountWithLatestValue>>

    @Query("""
        SELECT
            SUM(CASE WHEN a.type IN ('BANK', 'INVESTMENT') THEN s.value ELSE 0 END) as totalAssets,
            SUM(CASE WHEN a.type = 'LIABILITY' THEN s.value ELSE 0 END) as totalLiabilities
        FROM account_snapshots s
        INNER JOIN accounts a ON s.accountId = a.id
        WHERE s.snapshotDate IN (SELECT MAX(snapshotDate) FROM account_snapshots GROUP BY accountId)
    """)
    fun getLatestNetWorth(): Flow<NetWorth?>
}

</details>

---

檔案路徑: app/src/main/java/com/yourcompany/cashflow/data/local/dao/TransactionDao.kt

<details>

<summary><strong>點此展開 TransactionDao.kt 程式碼</strong></summary>

Kotlin

package com.yourcompany.cashflow.data.local.dao

import androidx.room.*
import com.yourcompany.cashflow.data.local.entity.Transaction
import com.yourcompany.cashflow.data.local.entity.TransactionType
import com.yourcompany.cashflow.data.model.CategorySum
import com.yourcompany.cashflow.data.model.SubCategorySum
import com.yourcompany.cashflow.data.model.TransactionWithDetails
import kotlinx.coroutines.flow.Flow

@Dao
interface TransactionDao {
    // --- CRUD ---
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(transaction: Transaction)

    @Update
    suspend fun update(transaction: Transaction)

    @Delete
    suspend fun delete(transaction: Transaction)

    // --- Basic Queries ---
    @Transaction
    @Query("SELECT * FROM transactions WHERE id = :id")
    fun getTransactionDetailsById(id: Long): Flow<TransactionWithDetails?>
    
    @Transaction
    @Query("SELECT * FROM transactions ORDER BY transactionDate DESC")
    fun getAllTransactionDetails(): Flow<List<TransactionWithDetails>>
    
    @Query("SELECT COUNT(id) FROM transactions WHERE subCategoryId = :subCategoryId AND amount = :amount AND transactionDate BETWEEN :startOfDay AND :endOfDay")
    suspend fun transactionExists(subCategoryId: Long, amount: Double, startOfDay: Long, endOfDay: Long): Int
    
    @Query("SELECT SUM(amount) FROM transactions WHERE type = 'EXPENSE' AND transactionDate BETWEEN :startDate AND :endDate")
    fun getExpensesBetween(startDate: Long, endDate: Long): Flow<Double?>

    // --- Project-related Queries ---
    @Query("SELECT SUM(amount) FROM transactions WHERE projectId = :projectId")
    fun getSpendingForProject(projectId: Long): Flow<Double?>
    
    @Transaction
    @Query("SELECT * FROM transactions WHERE projectId = :projectId ORDER BY transactionDate DESC")
    fun getTransactionsForProject(projectId: Long): Flow<List<TransactionWithDetails>>

    @Transaction
    @Query("SELECT * FROM transactions WHERE projectId IS NULL ORDER BY transactionDate DESC")
    fun getUnassignedTransactions(): Flow<List<TransactionWithDetails>>

    @Query("UPDATE transactions SET projectId = :projectId WHERE id IN (:transactionIds)")
    suspend fun linkTransactionsToProject(transactionIds: List<Long>, projectId: Long)

    // --- Statistics Queries ---
    @Query("""
        SELECT mc.name as categoryName, SUM(t.amount) as amount
        FROM transactions t
        INNER JOIN sub_categories sc ON t.subCategoryId = sc.id
        INNER JOIN main_categories mc ON sc.mainCategoryId = mc.id
        WHERE t.type = :type AND t.projectId IS NULL AND t.transactionDate BETWEEN :startDate AND :endDate
        GROUP BY mc.name
        UNION ALL
        SELECT '專案總支出' as categoryName, SUM(t.amount) as amount
        FROM transactions t
        WHERE t.type = :type AND t.projectId IS NOT NULL AND t.transactionDate BETWEEN :startDate AND :endDate
    """)
    fun getCategoryStatistics(type: TransactionType, startDate: Long, endDate: Long): Flow<List<CategorySum>>

    @Query("""
        SELECT p.name as categoryName, SUM(t.amount) as amount
        FROM transactions t
        INNER JOIN projects p ON t.projectId = p.id
        WHERE t.type = :type AND t.transactionDate BETWEEN :startDate AND :endDate
        GROUP BY p.name
        ORDER BY amount DESC
    """)
    fun getProjectStatistics(type: TransactionType, startDate: Long, endDate: Long): Flow<List<CategorySum>>

    @Query("""
        SELECT mc.name as categoryName, SUM(t.amount) as amount
        FROM transactions t
        INNER JOIN sub_categories sc ON t.subCategoryId = sc.id
        INNER JOIN main_categories mc ON sc.mainCategoryId = mc.id
        WHERE t.type = :type AND t.projectId = :projectId AND t.transactionDate BETWEEN :startDate AND :endDate
        GROUP BY mc.name
        ORDER BY amount DESC
    """)
    fun getCategorySumsForProject(projectId: Long, type: TransactionType, startDate: Long, endDate: Long): Flow<List<CategorySum>>

    @Query("""
        SELECT sc.name as subCategoryName, SUM(t.amount) as amount
        FROM transactions t
        INNER JOIN sub_categories sc ON t.subCategoryId = sc.id
        INNER JOIN main_categories mc ON sc.mainCategoryId = mc.id
        WHERE t.type = :type AND t.projectId = :projectId AND mc.name = :mainCategoryName AND t.transactionDate BETWEEN :startDate AND :endDate
        GROUP BY sc.name
        ORDER BY amount DESC
    """)
    fun getSubCategorySumsForProject(projectId: Long, mainCategoryName: String, type: TransactionType, startDate: Long, endDate: Long): Flow<List<SubCategorySum>>
}

</details>

---

檔案路徑: app/src/main/java/com/yourcompany/cashflow/data/local/dao/RecurringTransactionDao.kt

<details>

<summary><strong>點此展開 RecurringTransactionDao.kt 程式碼</strong></summary>

Kotlin

package com.yourcompany.cashflow.data.local.dao

import androidx.room.*
import com.yourcompany.cashflow.data.local.entity.RecurringTransaction
import kotlinx.coroutines.flow.Flow

@Dao
interface RecurringTransactionDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(recurringTransaction: RecurringTransaction)

    // Used for a potential management UI
    @Query("SELECT * FROM recurring_transactions")
    fun getAllRecurringTransactions(): Flow<List<RecurringTransaction>>
    
    // Used by the WorkManager for a one-shot fetch
    @Query("SELECT * FROM recurring_transactions")
    suspend fun getAllRecurringTransactionsOnce(): List<RecurringTransaction>

    @Delete
    suspend fun delete(recurringTransaction: RecurringTransaction)
}

</details>

---

以上就是我們專案資料庫中所有 DAO 的最終版本。它們是我們 App 業務邏輯與數據之間最重要、最直接的橋樑，包含了從簡單的增刪改查到複雜的多表連接、數據聚合查詢。