Kotlin Legacy→DQL Migration Guide

I need help migrating a Ditto Kotlin Android application from the legacy query builder APIs to modern DQL (Ditto Query Language). This migration involves converting method chaining patterns to SQL-like DQL syntax.

CRITICAL RULES:
1. All query builder method chains (.collection().find()) must be replaced with ditto.store.execute() using DQL
2. Use parameterized queries with :paramName syntax - NEVER string interpolation
3. Counter operations must use PN_INCREMENT BY in APPLY clause - do NOT initialize counter fields
4. Sync subscriptions must use ditto.sync.registerSubscription() instead of .find().subscribe()
5. observeLocal must be replaced with registerObserver + DittoDiffer

---

CORE MIGRATION AREAS:

1. QUERY SYNTAX MIGRATION

BEFORE (Legacy Query Builder):
```
ditto.store.collection("cars")
    .find("color == $0", "red")
    .exec()
```

AFTER (DQL):
```
ditto.store.execute(
    "SELECT * FROM cars WHERE color = :color",
    mapOf("color" to "red")
)
```

2. INSERT OPERATIONS

BEFORE (Legacy Query Builder):
```
ditto.store.collection("cars")
    .upsert(mapOf("_id" to id, "color" to "blue"))
```

AFTER (DQL):
```
ditto.store.execute(
    "INSERT INTO cars DOCUMENTS (:car)",
    mapOf("car" to carData)
)
```

3. UPDATE OPERATIONS

BEFORE (Legacy Query Builder):
```
ditto.store.collection("cars")
    .findById(id)
    .update { doc -> doc["color"].set("green") }
```

AFTER (DQL):
```
ditto.store.execute(
    "UPDATE cars SET color = :color WHERE _id = :id",
    mapOf("color" to "green", "id" to id)
)
```

4. DELETE OPERATIONS

BEFORE (Legacy Query Builder):
```
ditto.store.collection("cars").findById(id).remove()
```

AFTER (DQL):
```
ditto.store.execute(
    "DELETE FROM cars WHERE _id = :id",
    mapOf("id" to id)
)
```

5. EVICTION OPERATIONS

BEFORE (Legacy Query Builder):
```
ditto.store.collection("cars").findById(id).evict()
```

AFTER (DQL):
```
ditto.store.execute(
    "EVICT FROM cars WHERE _id = :id",
    mapOf("id" to id)
)
```

6. COUNTER OPERATIONS (PN_COUNTER)

BEFORE (Legacy Query Builder):
```
ditto.store.collection("cars")
    .findById(id)
    .update { doc ->
        doc["numUpdates"].counter?.increment(amount = 1.0)
    }
```

AFTER (DQL with PN_INCREMENT):
```
ditto.store.execute(
    "UPDATE cars APPLY numUpdates PN_INCREMENT BY :increment WHERE _id = :id",
    mapOf("increment" to 1, "id" to id)
)
```

IMPORTANT: Do NOT initialize counter fields in documents:
```
// WRONG - Creates a register, not a counter
mapOf("counter" to 0)

// CORRECT - Omit counter field, it's created on first PN_INCREMENT
mapOf("_id" to id, "color" to "blue")
```

7. DOCUMENT FIELD ACCESS MIGRATION

BEFORE (Legacy Query Builder):
```
val document: DittoDocument = collection.findById(res.id).exec()
val color = document.value["color"] as String
```

AFTER (DQL):
```
val result: DittoQueryResult = ditto.store.execute(dqlString)
val item: DittoQueryResultItem = result.items.first()
val color = item.value["color"] as String
```

8. LIVE QUERY MIGRATION (observeLocal → registerObserver)

BEFORE (Legacy observeLocal):
```
liveQuery = collection.find("_id.locationId == '${Constants.locationId}'")
    .observeLocal { docs, event ->
        when (event) {
            is DittoLiveQueryEvent.Update -> {
                adapter.delete(event.deletions)
                adapter.insert(event.insertions)
                adapter.update(event.updates)
            }
            is DittoLiveQueryEvent.Initial -> {
                adapter.setInitialCars(docs)
            }
        }
    }
```

AFTER (DQL with registerObserver + DittoDiffer):
```
import live.ditto.DittoDiffer

class MainFragment : Fragment() {
    private var differ = DittoDiffer()
    private var previousDocumentIds: MutableList<String> = mutableListOf()

    private fun startLiveQuery() {
        val observer = ditto.store.registerObserver(
            query = "SELECT * FROM cars WHERE _id.locationId = :locationId",
            parameters = mapOf("locationId" to Constants.locationId)
        ) { queryResult ->
            val diff = differ.diff(queryResult.items)

            // Extract current document IDs and dematerialize items
            val currentDocumentIds = queryResult.items.map { item ->
                val id = item.value["_id"] as? String ?: "unknown"
                item.dematerialize() // IMPORTANT: Free memory
                id
            }

            // Handle deletions using stored IDs from previous emission
            for (index in diff.deletions) {
                val deletedId = previousDocumentIds[index]
                // Handle deletion
            }

            // Handle insertions using current IDs
            for (index in diff.insertions) {
                val insertedId = currentDocumentIds[index]
                // Handle insertion
            }

            // Handle updates using current IDs
            for (index in diff.updates) {
                val updatedId = currentDocumentIds[index]
                // Handle update
            }

            previousDocumentIds = currentDocumentIds.toMutableList()
        }
    }
}
```

9. SYNC SUBSCRIPTIONS MIGRATION

BEFORE (Legacy Query Builder):
```
val subscription = ditto.store.collection("cars")
    .find("color == $0", "red")
    .subscribe()
```

AFTER (DQL):
```
val subscription = ditto.sync.registerSubscription(
    "SELECT * FROM cars WHERE color = :color",
    mapOf("color" to "red")
)
```

---

COMMON PITFALLS TO AVOID:

1. DQL Syntax Errors
   - Use :paramName for parameters, not $0 or string interpolation

2. Missing Parameter Binding
   - NEVER use string interpolation in queries
   - Always use parameterized queries with mapOf()

3. Counter Type Errors
   - Do NOT initialize counter fields with DittoCounter() or numbers
   - Use PN_INCREMENT BY in APPLY clause
   - Pass negative values for decrements

4. Memory Leaks in Observers
   - Always call item.dematerialize() after extracting data
   - Store only IDs, not full query items

5. Attachment Handling
   - Use ATTACHMENT annotation: "(image ATTACHMENT)"
   - Create attachments with ditto.store.newAttachment()

---

MIGRATION CHECKLIST:

Search for these legacy patterns and replace:
- [ ] .collection(" → ditto.store.execute("SELECT * FROM 
- [ ] .find( → Convert to DQL WHERE clause with parameters
- [ ] .findById( → Convert to DQL WHERE _id = :id
- [ ] .upsert( → Convert to DQL INSERT INTO 
- [ ] .update( → Convert to DQL UPDATE SET
- [ ] .remove( → Convert to DQL DELETE FROM
- [ ] .evict( → Convert to DQL EVICT FROM
- [ ] .counter?.increment( → Convert to PN_INCREMENT BY in APPLY clause
- [ ] DittoCounter() → Remove initialization, use PN_INCREMENT
- [ ] .observeLocal( → Convert to registerObserver + DittoDiffer
- [ ] .subscribe() → Convert to ditto.sync.registerSubscription()
- [ ] DittoDocument → DittoQueryResultItem
- [ ] DittoSubscription → DittoSyncSubscription

---

Please help me convert all legacy query builder patterns in my codebase to DQL syntax. Focus on:
1. Maintaining the same functionality
2. Using proper parameterized queries
3. Handling counter operations correctly with PN_INCREMENT
4. Implementing proper memory management in observers
5. Converting all sync subscriptions to DQL

Start by identifying all uses of .collection() in my codebase and systematically converting each one to the appropriate DQL pattern.