import GRDB

// File-based database
let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
let dbQueue = try DatabaseQueue(path: "\(dbPath)/db.sqlite")

// In-memory database (tests)
let dbQueue = try DatabaseQueue()

// For apps with heavy concurrent access
let dbPool = try DatabasePool(path: dbPath)

struct Track: Codable {
    var id: String
    var title: String
    var artist: String
    var duration: TimeInterval
}

// Fetch
let tracks = try dbQueue.read { db in
    try Track.fetchAll(db, sql: "SELECT * FROM tracks")
}

// Insert
try dbQueue.write { db in
    try track.insert(db)  // Codable conformance provides insert
}

struct TrackInfo: FetchableRecord {
    var title: String
    var artist: String
    var albumTitle: String

    init(row: Row) {
        title = row["title"]
        artist = row["artist"]
        albumTitle = row["album_title"]
    }
}

let results = try dbQueue.read { db in
    try TrackInfo.fetchAll(db, sql: """
        SELECT tracks.title, tracks.artist, albums.title as album_title
        FROM tracks
        JOIN albums ON tracks.albumId = albums.id
        """)
}

struct Track: Codable, PersistableRecord {
    var id: String
    var title: String

    // Customize table name
    static let databaseTableName = "tracks"
}

try dbQueue.write { db in
    var track = Track(id: "1", title: "Song")
    try track.insert(db)

    track.title = "Updated"
    try track.update(db)

    try track.delete(db)
}

// Fetch all rows
let rows = try dbQueue.read { db in
    try Row.fetchAll(db, sql: "SELECT * FROM tracks WHERE genre = ?", arguments: ["Rock"])
}

// Fetch single value
let count = try dbQueue.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM tracks")
}

// Fetch into Codable
let tracks = try dbQueue.read { db in
    try Track.fetchAll(db, sql: "SELECT * FROM tracks ORDER BY title")
}

try dbQueue.write { db in
    try db.execute(sql: """
        INSERT INTO tracks (id, title, artist, duration)
        VALUES (?, ?, ?, ?)
        """, arguments: ["1", "Song", "Artist", 240])
}

try dbQueue.write { db in
    // Automatic transaction - all or nothing
    for track in tracks {
        try track.insert(db)
    }
    // Commits automatically on success, rolls back on error
}

let request = Track
    .filter(Column("genre") == "Rock")
    .filter(Column("duration") > 180)

let tracks = try dbQueue.read { db in
    try request.fetchAll(db)
}

let request = Track
    .order(Column("title").asc)
    .limit(10)

struct TrackWithAlbum: FetchableRecord {
    var trackTitle: String
    var albumTitle: String
}

let request = Track
    .joining(required: Track.belongsTo(Album.self))
    .select(Column("title").forKey("trackTitle"), Column("album_title").forKey("albumTitle"))

let results = try dbQueue.read { db in
    try TrackWithAlbum.fetchAll(db, request)
}

let sql = """
    SELECT
        tracks.title as track_title,
        albums.title as album_title,
        artists.name as artist_name,
        COUNT(plays.id) as play_count
    FROM tracks
    JOIN albums ON tracks.albumId = albums.id
    JOIN artists ON albums.artistId = artists.id
    LEFT JOIN plays ON plays.trackId = tracks.id
    WHERE artists.genre = ?
    GROUP BY tracks.id
    HAVING play_count > 10
    ORDER BY play_count DESC
    LIMIT 50
    """

struct TrackStats: FetchableRecord {
    var trackTitle: String
    var albumTitle: String
    var artistName: String
    var playCount: Int

    init(row: Row) {
        trackTitle = row["track_title"]
        albumTitle = row["album_title"]
        artistName = row["artist_name"]
        playCount = row["play_count"]
    }
}

let stats = try dbQueue.read { db in
    try TrackStats.fetchAll(db, sql: sql, arguments: ["Rock"])
}

import GRDB
import Combine

let observation = ValueObservation.tracking { db in
    try Track.fetchAll(db)
}

// Start observing with Combine
let cancellable = observation.publisher(in: dbQueue)
    .sink(
        receiveCompletion: { _ in },
        receiveValue: { tracks in
            print("Tracks updated: \(tracks.count)")
        }
    )

import GRDB
import GRDBQuery  // https://github.com/groue/GRDBQuery

@Query(Tracks())
var tracks: [Track]

struct Tracks: Queryable {
    static var defaultValue: [Track] { [] }

    func publisher(in dbQueue: DatabaseQueue) -> AnyPublisher<[Track], Error> {
        ValueObservation
            .tracking { db in try Track.fetchAll(db) }
            .publisher(in: dbQueue)
            .eraseToAnyPublisher()
    }
}

func observeGenre(_ genre: String) -> ValueObservation<[Track]> {
    ValueObservation.tracking { db in
        try Track
            .filter(Column("genre") == genre)
            .fetchAll(db)
    }
}

let cancellable = observeGenre("Rock")
    .publisher(in: dbQueue)
    .sink { tracks in
        print("Rock tracks: \(tracks.count)")
    }

var migrator = DatabaseMigrator()

// Migration 1: Create tables
migrator.registerMigration("v1") { db in
    try db.create(table: "tracks") { t in
        t.column("id", .text).primaryKey()
        t.column("title", .text).notNull()
        t.column("artist", .text).notNull()
        t.column("duration", .real).notNull()
    }
}

// Migration 2: Add column
migrator.registerMigration("v2_add_genre") { db in
    try db.alter(table: "tracks") { t in
        t.add(column: "genre", .text)
    }
}

// Migration 3: Add index
migrator.registerMigration("v3_add_indexes") { db in
    try db.create(index: "idx_genre", on: "tracks", columns: ["genre"])
}

// Run migrations
try migrator.migrate(dbQueue)

migrator.registerMigration("v4_normalize_artists") { db in
    // 1. Create new table
    try db.create(table: "artists") { t in
        t.column("id", .text).primaryKey()
        t.column("name", .text).notNull()
    }

    // 2. Extract unique artists
    try db.execute(sql: """
        INSERT INTO artists (id, name)
        SELECT DISTINCT
            lower(replace(artist, ' ', '_')) as id,
            artist as name
        FROM tracks
        """)

    // 3. Add foreign key to tracks
    try db.alter(table: "tracks") { t in
        t.add(column: "artistId", .text)
            .references("artists", onDelete: .cascade)
    }

    // 4. Populate foreign keys
    try db.execute(sql: """
        UPDATE tracks
        SET artistId = (
            SELECT id FROM artists
            WHERE artists.name = tracks.artist
        )
        """)
}

try dbQueue.write { db in
    for batch in tracks.chunked(into: 500) {
        for track in batch {
            try track.insert(db)
        }
    }
}

try dbQueue.write { db in
    let statement = try db.makeStatement(sql: """
        INSERT INTO tracks (id, title, artist, duration)
        VALUES (?, ?, ?, ?)
        """)

    for track in tracks {
        try statement.execute(arguments: [track.id, track.title, track.artist, track.duration])
    }
}

try db.create(index: "idx_tracks_artist", on: "tracks", columns: ["artist"])
try db.create(index: "idx_tracks_genre_duration", on: "tracks", columns: ["genre", "duration"])

// Unique index
try db.create(index: "idx_tracks_unique_title", on: "tracks", columns: ["title"], unique: true)

// Analyze query performance
let explanation = try dbQueue.read { db in
    try String.fetchOne(db, sql: "EXPLAIN QUERY PLAN SELECT * FROM tracks WHERE artist = ?", arguments: ["Artist"])
}
print(explanation)

import SQLiteData
import GRDB

@Dependency(\.database) var database  // SQLiteData Database

// Access underlying GRDB DatabaseQueue
try await database.database.write { db in
    // Full GRDB power here
    try db.execute(sql: "CREATE INDEX idx_genre ON tracks(genre)")
}

// Read single value
let count = try db.fetchOne(Int.self, sql: "SELECT COUNT(*) FROM tracks")

// Read all rows
let rows = try Row.fetchAll(db, sql: "SELECT * FROM tracks WHERE genre = ?", arguments: ["Rock"])

// Write
try db.execute(sql: "INSERT INTO tracks VALUES (?, ?, ?)", arguments: [id, title, artist])

// Transaction
try dbQueue.write { db in
    // All or nothing
}

// Observe changes
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.publisher(in: dbQueue)

var database = try DatabaseQueue(path: dbPath)

// Enable tracing to see SQL execution
database.trace { print($0) }

// Run the slow query
try database.read { db in
    let results = try Track.fetchAll(db)  // Watch output for execution time
}

// Use EXPLAIN QUERY PLAN to understand execution:
try database.read { db in
    let plan = try String(fetching: db, sql: "EXPLAIN QUERY PLAN SELECT ...")
    print(plan)
    // Look for SCAN (slow, full table) vs SEARCH (fast, indexed)
}

// Add index on frequently queried column
try database.write { db in
    try db.execute(sql: "CREATE INDEX idx_plays_track_id ON plays(track_id)")
}

// Re-evaluates query on ANY write to database
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.start(in: database, onError: { }, onChange: { tracks in
    // Called for every change — CPU spike!
})

// Coalesce rapid updates (recommended)
ValueObservation.tracking { db in
    try Track.fetchAll(db)
}.removeDuplicates()  // Skip duplicate results
 .debounce(for: 0.5, scheduler: DispatchQueue.main)  // Batch updates
 .start(in: database, ...)

var migrator = DatabaseMigrator()

migrator.registerMigration("v1_initial") { db in
    try db.execute(sql: "CREATE TABLE tracks (...)")
}

migrator.registerMigration("v2_add_plays") { db in
    try db.execute(sql: "CREATE TABLE plays (...)")
}

// GRDB guarantees:
// - Each migration runs exactly ONCE
// - In order (v1, then v2)
// - Safe to call migrate() multiple times
try migrator.migrate(dbQueue)

for track in 50000Tracks {
    try dbQueue.write { db in try track.insert(db) }  // 50k transactions!
}

let tracks = try dbQueue.read { db in try Track.fetchAll(db) }  // Blocks UI

// Slow query without index
try Track.filter(Column("genre") == "Rock").fetchAll(db)

for track in tracks {
    let album = try Album.fetchOne(db, key: track.albumId)  // N queries!
}