import Foundation
import GRDB

/// GRDB-backed `SessionMetadataStore` (RUNTIME §4). WAL-mode SQLite on disk;
/// an in-memory queue for tests. Named to avoid clashing with GRDB's own
/// `Database` type used inside the read/write closures.
public final class GRDBMetadataStore: SessionMetadataStore {
    private let writer: any DatabaseWriter

    /// Opens (and migrates) the store. `path == nil` → an in-memory queue (tests);
    /// a path → a WAL-mode `DatabasePool` under `Application Support/Nucleic`.
    public init(path: String?) throws {
        if let path {
            try FileManager.default.createDirectory(
                at: URL(fileURLWithPath: path).deletingLastPathComponent(),
                withIntermediateDirectories: true)
            var config = Configuration()
            config.prepareDatabase { try $0.execute(sql: "PRAGMA foreign_keys = ON") }
            writer = try DatabasePool(path: path, configuration: config)
        } else {
            writer = try DatabaseQueue()
        }
        try Self.migrator.migrate(writer)
    }

    // MARK: - Schema (RUNTIME §4; device table deferred to M4)

    private static var migrator: DatabaseMigrator {
        var migrator = DatabaseMigrator()
        migrator.registerMigration("v1") { db in
            try db.execute(sql: """
                CREATE TABLE project (
                  id TEXT PRIMARY KEY,
                  name TEXT NOT NULL,
                  root_path TEXT NOT NULL,
                  default_branch TEXT NOT NULL,
                  default_backend TEXT,
                  worktree_base TEXT,
                  setup_script TEXT,
                  setup_policy TEXT NOT NULL DEFAULT 'block',
                  created_at DATETIME NOT NULL
                );
                """)
            try db.execute(sql: """
                CREATE TABLE session (
                  id TEXT PRIMARY KEY,
                  project_id TEXT NOT NULL,
                  backend TEXT NOT NULL,
                  backend_session_id TEXT,
                  title TEXT NOT NULL,
                  status TEXT NOT NULL,
                  worktree_path TEXT,
                  branch TEXT,
                  base_sha TEXT,
                  model TEXT,
                  last_seq INTEGER NOT NULL DEFAULT 0,
                  transcript_path TEXT NOT NULL,
                  native_transcript_path TEXT,
                  diff_files INTEGER, diff_added INTEGER, diff_removed INTEGER,
                  ahead INTEGER, behind INTEGER,
                  created_at DATETIME NOT NULL,
                  updated_at DATETIME NOT NULL
                );
                """)
            try db.execute(sql: "CREATE INDEX idx_session_project ON session(project_id, updated_at);")
            try db.execute(sql: """
                CREATE TABLE approval (
                  id TEXT PRIMARY KEY,
                  session_id TEXT NOT NULL,
                  tool_call_id TEXT,
                  tool_name TEXT NOT NULL,
                  title TEXT NOT NULL,
                  risk TEXT NOT NULL,
                  input TEXT NOT NULL,
                  created_at DATETIME NOT NULL,
                  resolved_at DATETIME,
                  decision TEXT, decided_by TEXT
                );
                """)
            try db.execute(sql:
                "CREATE INDEX idx_approval_pending ON approval(session_id) WHERE resolved_at IS NULL;")
        }
        migrator.registerMigration("v2-effort") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN effort TEXT;")
        }
        migrator.registerMigration("v3-flags") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN auto INTEGER NOT NULL DEFAULT 0;")
            try db.execute(sql: "ALTER TABLE session ADD COLUMN favorite INTEGER NOT NULL DEFAULT 0;")
            try db.execute(sql: "ALTER TABLE session ADD COLUMN archived INTEGER NOT NULL DEFAULT 0;")
        }
        migrator.registerMigration("v4-summary") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN summary TEXT;")
        }
        migrator.registerMigration("v5-todos") { db in
            try db.execute(sql: """
                CREATE TABLE todo (
                  id TEXT PRIMARY KEY,
                  text TEXT NOT NULL,
                  project_id TEXT,
                  status TEXT NOT NULL DEFAULT 'open',
                  dispatched_session_id TEXT,
                  created_at DATETIME NOT NULL,
                  updated_at DATETIME NOT NULL
                );
                """)
            try db.execute(sql: "CREATE INDEX idx_todo_updated ON todo(updated_at);")
        }
        migrator.registerMigration("v6-todo-summary") { db in
            try db.execute(sql: "ALTER TABLE todo ADD COLUMN summary TEXT;")
        }
        migrator.registerMigration("v7-sandbox") { db in
            try db.execute(sql: "ALTER TABLE project ADD COLUMN sandbox_config TEXT;")
        }
        migrator.registerMigration("v8-disposition") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN last_turn_disposition TEXT;")
        }
        migrator.registerMigration("v9-todo-triage") { db in
            try db.execute(sql: "ALTER TABLE todo ADD COLUMN triage_level TEXT;")
            try db.execute(sql: "ALTER TABLE todo ADD COLUMN triage_rank INTEGER;")
            try db.execute(sql: "ALTER TABLE todo ADD COLUMN triage_reason TEXT;")
        }
        migrator.registerMigration("v10-autoship") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN auto_ship INTEGER NOT NULL DEFAULT 0;")
        }
        migrator.registerMigration("v11-unseen-completion") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN unseen_completion INTEGER NOT NULL DEFAULT 0;")
        }
        migrator.registerMigration("v12-last-event-autoship") { db in
            try db.execute(
                sql: "ALTER TABLE session ADD COLUMN last_event_was_autoship INTEGER NOT NULL DEFAULT 0;")
        }
        migrator.registerMigration("v13-autoship-failed") { db in
            try db.execute(
                sql: "ALTER TABLE session ADD COLUMN auto_ship_failed INTEGER NOT NULL DEFAULT 0;")
        }
        migrator.registerMigration("v14-parent-refs") { db in
            // Parent/root pointers for the lock domain (LOCKING §3.5). parent_ref = release
            // target; root_ref = lock domain (the tree's top-level branch); parent_session_id
            // links a nested child to its parent session.
            try db.execute(sql: "ALTER TABLE session ADD COLUMN parent_ref TEXT;")
            try db.execute(sql: "ALTER TABLE session ADD COLUMN root_ref TEXT;")
            try db.execute(sql: "ALTER TABLE session ADD COLUMN parent_session_id TEXT;")
        }
        migrator.registerMigration("v15-last-user-message") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN last_user_message_at DATETIME;")
        }
        migrator.registerMigration("v16-archived-at") { db in
            try db.execute(sql: "ALTER TABLE session ADD COLUMN archived_at DATETIME;")
        }
        migrator.registerMigration("v17-autoship-branch") { db in
            // Per-project autoship destination override; NULL → the project's default branch.
            try db.execute(sql: "ALTER TABLE project ADD COLUMN auto_ship_branch TEXT;")
            // Per-session autoship destination override; NULL → inherit the project's.
            try db.execute(sql: "ALTER TABLE session ADD COLUMN ship_branch TEXT;")
        }
        migrator.registerMigration("v18-project-archived") { db in
            // When the project was archived; NULL → active.
            try db.execute(sql: "ALTER TABLE project ADD COLUMN archived_at DATETIME;")
        }
        migrator.registerMigration("v19-autoship-conflict") { db in
            // Sticky "autoship hit a merge conflict but stayed armed" marker; 0 → no conflict.
            try db.execute(
                sql: "ALTER TABLE session ADD COLUMN auto_ship_conflict INTEGER NOT NULL DEFAULT 0;")
        }
        migrator.registerMigration("v20-nvrsion") { db in
            // Per-project nvrsion (Beta) config, JSON-encoded like sandbox_config; NULL → off
            // (NVRSION §8, §10). New fields are additive within the JSON, no further migration.
            try db.execute(sql: "ALTER TABLE project ADD COLUMN nvrsion_config TEXT;")
        }
        migrator.registerMigration("v21-activity-cache") { db in
            // Derived per-session rollup powering the home dashboard (message count + per-day
            // histogram), so it renders from a query instead of re-scanning every transcript on
            // the main thread. Pure cache — rebuildable from the transcript; `last_seq` is the
            // cursor it was computed at (recompute when the session's live lastSeq advances).
            // CASCADE so a deleted session drops its cache row.
            try db.execute(sql: """
                CREATE TABLE session_activity (
                  session_id TEXT PRIMARY KEY REFERENCES session(id) ON DELETE CASCADE,
                  last_seq INTEGER NOT NULL,
                  message_count INTEGER NOT NULL,
                  activity_json TEXT NOT NULL
                );
                """)
        }
        migrator.registerMigration("v22-activity-tokens") { db in
            // Extend the per-session rollup with token usage: a grand total and a per-day
            // histogram (same shape as activity_json), so the dashboard can show total tokens and
            // the activity grid can shade by tokens-per-day.
            try db.execute(sql: """
                ALTER TABLE session_activity ADD COLUMN token_count INTEGER NOT NULL DEFAULT 0;
                """)
            try db.execute(sql: """
                ALTER TABLE session_activity ADD COLUMN tokens_json TEXT NOT NULL DEFAULT '{}';
                """)
            // Existing rows carry no token data but still match their session's last_seq, so they'd
            // be served as "fresh" and never rescanned — leaving historical tokens permanently 0
            // for idle sessions. The rollup is a pure cache, so clear it: the next dashboard load
            // treats every session as stale and rebuilds it (off-main) with token data folded in.
            try db.execute(sql: "DELETE FROM session_activity;")
        }
        return migrator
    }

    // MARK: - Projects

    public func saveProject(_ project: Project) async throws {
        try await writer.write { db in
            try ProjectRow(project).upsert(db)
        }
    }

    public func loadProjects() async throws -> [Project] {
        try await writer.read { db in
            try ProjectRow.order(Column("name")).fetchAll(db).map { $0.toProject() }
        }
    }

    public func deleteProject(id: ProjectID) async throws {
        _ = try await writer.write { db in
            try ProjectRow.deleteOne(db, key: id.rawValue)
        }
    }

    // MARK: - Sessions

    public func saveSession(_ session: Session) async throws {
        try await writer.write { db in
            try SessionRow(session).upsert(db)
        }
    }

    public func loadSession(id: SessionID) async throws -> Session? {
        try await writer.read { db in
            try SessionRow.fetchOne(db, key: id.rawValue)?.toSession()
        }
    }

    public func loadSessions(projectID: ProjectID) async throws -> [Session] {
        try await writer.read { db in
            try SessionRow
                .filter(Column("project_id") == projectID.rawValue)
                .order(Column("updated_at").desc)
                .fetchAll(db).map { $0.toSession() }
        }
    }

    public func loadAllSessions() async throws -> [Session] {
        try await writer.read { db in
            try SessionRow.order(Column("updated_at").desc).fetchAll(db).map { $0.toSession() }
        }
    }

    public func deleteSession(id: SessionID) async throws {
        _ = try await writer.write { db in
            try SessionRow.deleteOne(db, key: id.rawValue)
        }
    }

    // MARK: - Activity cache (derived dashboard rollup)

    public func loadActivityCache() async throws -> [SessionID: ActivityCacheEntry] {
        try await writer.read { db in
            try ActivityRow.fetchAll(db).reduce(into: [:]) { acc, row in
                acc[SessionID(rawValue: row.session_id)] = row.toEntry()
            }
        }
    }

    public func saveActivityCache(_ sessionID: SessionID, _ entry: ActivityCacheEntry) async throws {
        let row = ActivityRow(sessionID: sessionID, entry: entry)
        try await writer.write { db in try row.upsert(db) }
    }

    // MARK: - Approvals

    public func saveApproval(_ request: ApprovalRequest) async throws {
        try await writer.write { db in
            try ApprovalRow(request).upsert(db)
        }
    }

    public func resolveApproval(_ resolved: ApprovalResolved) async throws {
        let decisionJSON = try Self.encodeDecision(resolved.decision)
        let id = resolved.id.rawValue
        let decidedBy = resolved.decidedBy
        let decidedAt = resolved.decidedAt
        try await writer.write { db in
            try db.execute(
                sql: """
                    UPDATE approval SET resolved_at = :at, decision = :decision, decided_by = :by
                    WHERE id = :id
                    """,
                arguments: ["at": decidedAt, "decision": decisionJSON, "by": decidedBy, "id": id])
        }
    }

    public func pendingApprovals(sessionID: SessionID) async throws -> [ApprovalRequest] {
        let sid = sessionID.rawValue
        return try await writer.read { db in
            try ApprovalRow
                .filter(Column("session_id") == sid && Column("resolved_at") == nil)
                .order(Column("created_at"))
                .fetchAll(db)
                .compactMap { try? $0.toRequest() }
        }
    }

    // MARK: - Todos

    public func saveTodo(_ todo: Todo) async throws {
        try await writer.write { db in
            try TodoRow(todo).upsert(db)
        }
    }

    public func loadTodos() async throws -> [Todo] {
        try await writer.read { db in
            try TodoRow.order(Column("updated_at").desc).fetchAll(db).map { $0.toTodo() }
        }
    }

    public func deleteTodo(id: TodoID) async throws {
        _ = try await writer.write { db in
            try TodoRow.deleteOne(db, key: id.rawValue)
        }
    }

    // MARK: - Decision JSON helpers

    static func encodeDecision(_ decision: Decision) throws -> String {
        let encoder = JSONEncoder()
        encoder.dateEncodingStrategy = .iso8601
        return String(decoding: try encoder.encode(decision), as: UTF8.self)
    }
}

// MARK: - Row records (1:1 with columns; converted to/from domain types)

private struct ProjectRow: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "project"
    var id: String
    var name: String
    var root_path: String
    var default_branch: String
    var auto_ship_branch: String?
    var default_backend: String?
    var worktree_base: String?
    var setup_script: String?
    var setup_policy: String
    var sandbox_config: String?
    var nvrsion_config: String?
    var created_at: Date
    var archived_at: Date?

    init(_ p: Project) {
        id = p.id.rawValue
        name = p.name
        root_path = p.rootPath
        default_branch = p.defaultBranch.value
        auto_ship_branch = p.autoShipBranch?.value
        default_backend = p.defaultBackend?.rawValue
        worktree_base = p.worktreeBase
        setup_script = p.setupScript
        setup_policy = p.setupPolicy.rawValue
        sandbox_config = p.sandbox.flatMap { sandbox in
            (try? JSONEncoder().encode(sandbox)).map { String(decoding: $0, as: UTF8.self) }
        }
        nvrsion_config = p.nvrsion.flatMap { nvrsion in
            (try? JSONEncoder().encode(nvrsion)).map { String(decoding: $0, as: UTF8.self) }
        }
        created_at = p.createdAt
        archived_at = p.archivedAt
    }

    func toProject() -> Project {
        Project(
            id: ProjectID(rawValue: id),
            name: name,
            rootPath: root_path,
            defaultBranch: GitRef(default_branch),
            autoShipBranch: auto_ship_branch.map { GitRef($0) },
            defaultBackend: default_backend.flatMap(BackendID.init(rawValue:)),
            worktreeBase: worktree_base,
            setupScript: setup_script,
            setupPolicy: SetupPolicy(rawValue: setup_policy) ?? .block,
            sandbox: sandbox_config.flatMap { json in
                try? JSONDecoder().decode(ProjectSandbox.self, from: Data(json.utf8))
            },
            nvrsion: nvrsion_config.flatMap { json in
                try? JSONDecoder().decode(ProjectNvrsion.self, from: Data(json.utf8))
            },
            createdAt: created_at,
            archivedAt: archived_at)
    }
}

private struct SessionRow: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "session"
    var id: String
    var project_id: String
    var backend: String
    var backend_session_id: String?
    var title: String
    var status: String
    var worktree_path: String?
    var branch: String?
    var base_sha: String?
    var parent_ref: String?
    var root_ref: String?
    var parent_session_id: String?
    var model: String?
    var effort: String?
    var last_seq: Int64
    var transcript_path: String
    var native_transcript_path: String?
    var diff_files: Int?
    var diff_added: Int?
    var diff_removed: Int?
    var ahead: Int?
    var behind: Int?
    var auto: Bool
    var auto_ship: Bool
    var ship_branch: String?
    var favorite: Bool
    var archived: Bool
    var archived_at: Date?
    var unseen_completion: Bool
    var last_event_was_autoship: Bool
    var auto_ship_failed: Bool
    var auto_ship_conflict: Bool
    var summary: String?
    var last_turn_disposition: String?
    var last_user_message_at: Date?
    var created_at: Date
    var updated_at: Date

    init(_ s: Session) {
        id = s.id.rawValue
        project_id = s.projectID.rawValue
        backend = s.backend.rawValue
        backend_session_id = s.backendSessionID
        title = s.title
        status = s.status.rawValue
        worktree_path = s.worktreePath
        branch = s.branch
        base_sha = s.baseSHA
        parent_ref = s.parentRef
        root_ref = s.rootRef
        parent_session_id = s.parentSessionID?.rawValue
        model = s.model
        effort = s.effort
        last_seq = Int64(s.lastSeq)
        transcript_path = s.transcriptPath
        native_transcript_path = s.nativeTranscriptPath
        diff_files = s.diffStat?.filesChanged
        diff_added = s.diffStat?.added
        diff_removed = s.diffStat?.removed
        ahead = s.ahead
        behind = s.behind
        auto = s.auto
        auto_ship = s.autoShip
        ship_branch = s.shipBranch
        favorite = s.favorite
        archived = s.archived
        archived_at = s.archivedAt
        unseen_completion = s.unseenCompletion
        last_event_was_autoship = s.lastEventWasAutoship
        auto_ship_failed = s.autoShipFailed
        auto_ship_conflict = s.autoShipConflict
        summary = s.summary
        last_turn_disposition = s.lastTurnDisposition?.rawValue
        last_user_message_at = s.lastUserMessageAt
        created_at = s.createdAt
        updated_at = s.updatedAt
    }

    func toSession() -> Session {
        let diffStat = diff_files.map {
            DiffStat(filesChanged: $0, added: diff_added ?? 0, removed: diff_removed ?? 0)
        }
        return Session(
            id: SessionID(rawValue: id),
            projectID: ProjectID(rawValue: project_id),
            backend: BackendID(rawValue: backend) ?? .claudeCode,
            backendSessionID: backend_session_id,
            title: title,
            status: SessionStatus(rawValue: status) ?? .idle,
            worktreePath: worktree_path,
            branch: branch,
            baseSHA: base_sha,
            parentRef: parent_ref,
            rootRef: root_ref,
            parentSessionID: parent_session_id.map { SessionID(rawValue: $0) },
            model: model,
            effort: effort,
            lastSeq: UInt64(max(0, last_seq)),
            transcriptPath: transcript_path,
            nativeTranscriptPath: native_transcript_path,
            diffStat: diffStat,
            ahead: ahead,
            behind: behind,
            auto: auto,
            autoShip: auto_ship,
            shipBranch: ship_branch,
            favorite: favorite,
            archived: archived,
            archivedAt: archived_at,
            unseenCompletion: unseen_completion,
            lastEventWasAutoship: last_event_was_autoship,
            autoShipFailed: auto_ship_failed,
            autoShipConflict: auto_ship_conflict,
            summary: summary,
            lastTurnDisposition: last_turn_disposition.flatMap(TurnDisposition.init(rawValue:)),
            lastUserMessageAt: last_user_message_at,
            createdAt: created_at,
            updatedAt: updated_at)
    }
}

private struct TodoRow: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "todo"
    var id: String
    var text: String
    var project_id: String?
    var status: String
    var dispatched_session_id: String?
    var summary: String?
    var triage_level: String?
    var triage_rank: Int?
    var triage_reason: String?
    var created_at: Date
    var updated_at: Date

    init(_ t: Todo) {
        id = t.id.rawValue
        text = t.text
        project_id = t.projectID?.rawValue
        status = t.status.rawValue
        dispatched_session_id = t.dispatchedSessionID?.rawValue
        summary = t.summary
        triage_level = t.triageLevel?.rawValue
        triage_rank = t.triageRank
        triage_reason = t.triageReason
        created_at = t.createdAt
        updated_at = t.updatedAt
    }

    func toTodo() -> Todo {
        Todo(
            id: TodoID(rawValue: id),
            text: text,
            projectID: project_id.map(ProjectID.init(rawValue:)),
            status: TodoStatus(rawValue: status) ?? .open,
            dispatchedSessionID: dispatched_session_id.map(SessionID.init(rawValue:)),
            summary: summary,
            triageLevel: triage_level.flatMap(TriageLevel.init(rawValue:)),
            triageRank: triage_rank,
            triageReason: triage_reason,
            createdAt: created_at,
            updatedAt: updated_at)
    }
}

private struct ActivityRow: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "session_activity"
    var session_id: String
    var last_seq: Int64
    var message_count: Int
    var activity_json: String  // JSON map of day-epoch-seconds (string) → message count
    var token_count: Int
    var tokens_json: String    // JSON map of day-epoch-seconds (string) → tokens used

    init(sessionID: SessionID, entry: ActivityCacheEntry) {
        session_id = sessionID.rawValue
        last_seq = Int64(entry.lastSeq)
        message_count = entry.messageCount
        activity_json = Self.encodeByDay(entry.activityByDay)
        token_count = entry.tokenCount
        tokens_json = Self.encodeByDay(entry.tokensByDay)
    }

    func toEntry() -> ActivityCacheEntry {
        ActivityCacheEntry(
            lastSeq: UInt64(max(0, last_seq)), messageCount: message_count,
            activityByDay: Self.decodeByDay(activity_json),
            tokenCount: token_count, tokensByDay: Self.decodeByDay(tokens_json))
    }

    /// A `[Date: Int]` day histogram as a JSON object keyed by integer epoch-seconds (as a
    /// string, since JSON object keys are strings). Shared by the message and token series.
    private static func encodeByDay(_ byDay: [Date: Int]) -> String {
        let map = Dictionary(uniqueKeysWithValues: byDay.map {
            (String(Int($0.key.timeIntervalSince1970)), $0.value)
        })
        return (try? JSONEncoder().encode(map))
            .map { String(decoding: $0, as: UTF8.self) } ?? "{}"
    }

    private static func decodeByDay(_ json: String) -> [Date: Int] {
        let map = (try? JSONDecoder().decode([String: Int].self, from: Data(json.utf8))) ?? [:]
        return Dictionary(uniqueKeysWithValues: map.compactMap { key, value -> (Date, Int)? in
            guard let secs = TimeInterval(key) else { return nil }
            return (Date(timeIntervalSince1970: secs), value)
        })
    }
}

private struct ApprovalRow: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "approval"
    var id: String
    var session_id: String
    var tool_call_id: String?
    var tool_name: String
    var title: String
    var risk: String
    var input: String  // canonical JSON
    var created_at: Date
    var resolved_at: Date?
    var decision: String?
    var decided_by: String?

    init(_ r: ApprovalRequest) {
        id = r.id.rawValue
        session_id = r.sessionID.rawValue
        tool_call_id = r.toolCallID
        tool_name = r.toolName
        title = r.title
        risk = r.risk.rawValue
        input = r.input.canonicalString()
        created_at = r.createdAt
        resolved_at = nil
        decision = nil
        decided_by = nil
    }

    func toRequest() throws -> ApprovalRequest {
        ApprovalRequest(
            id: ApprovalID(rawValue: id),
            sessionID: SessionID(rawValue: session_id),
            toolCallID: tool_call_id,
            toolName: tool_name,
            input: (try? JSONValue(parsing: input)) ?? .null,
            title: title,
            risk: Risk(rawValue: risk) ?? .unknown,
            createdAt: created_at)
    }
}
