;;; forgejo-db.el --- SQLite cache for Forgejo API data  -*- lexical-binding: t; -*-

;; Copyright (C) 2026  Free Software Foundation, Inc.

;; Author: Thanos Apollo <public@thanosapollo.org>
;; Keywords: extensions

;; This program is free software; you can redistribute it and/or modify
;; it under the terms of the GNU General Public License as published by
;; the Free Software Foundation, either version 3 of the License, or
;; (at your option) any later version.

;; This program is distributed in the hope that it will be useful,
;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
;; GNU General Public License for more details.

;; You should have received a copy of the GNU General Public License
;; along with this program.  If not, see <https://www.gnu.org/licenses/>.

;;; Commentary:

;; Local SQLite cache for Forgejo API responses.
;;
;; Stores issues, pull requests, timeline events, labels, and
;; milestones.  Tracks sync timestamps per (host, owner, repo,
;; endpoint) for incremental updates via the API's `since' parameter.

;;; Code:

(require 'cl-lib)
(require 'json)

(defvar forgejo-db)
(defvar forgejo-db-dir)

;;; Schema

(defconst forgejo-db--schema
  '("CREATE TABLE IF NOT EXISTS issues (
       id INTEGER NOT NULL,
       host TEXT NOT NULL,
       owner TEXT NOT NULL,
       repo TEXT NOT NULL,
       number INTEGER NOT NULL,
       title TEXT NOT NULL,
       state TEXT NOT NULL,
       body TEXT,
       previous_body TEXT,
       user TEXT,
       labels TEXT,
       milestone TEXT,
       assignees TEXT,
       comments_count INTEGER DEFAULT 0,
       created_at TEXT,
       updated_at TEXT,
       closed_at TEXT,
       is_pull INTEGER DEFAULT 0,
       read INTEGER DEFAULT 0,
       pin_order INTEGER DEFAULT 0,
       due_date TEXT,
       is_locked INTEGER DEFAULT 0,
       PRIMARY KEY (host, owner, repo, number))"
    "CREATE TABLE IF NOT EXISTS timeline_events (
       id INTEGER NOT NULL,
       host TEXT NOT NULL,
       owner TEXT NOT NULL,
       repo TEXT NOT NULL,
       issue_number INTEGER NOT NULL,
       type TEXT,
       body TEXT,
       user TEXT,
       created_at TEXT,
       data TEXT,
       PRIMARY KEY (host, owner, repo, issue_number, id))"
    "CREATE TABLE IF NOT EXISTS labels (
       id INTEGER NOT NULL,
       host TEXT NOT NULL,
       owner TEXT NOT NULL,
       repo TEXT NOT NULL,
       name TEXT NOT NULL,
       color TEXT,
       description TEXT,
       PRIMARY KEY (host, owner, repo, id))"
    "CREATE TABLE IF NOT EXISTS milestones (
       id INTEGER NOT NULL,
       host TEXT NOT NULL,
       owner TEXT NOT NULL,
       repo TEXT NOT NULL,
       title TEXT NOT NULL,
       state TEXT,
       open_issues INTEGER DEFAULT 0,
       closed_issues INTEGER DEFAULT 0,
       PRIMARY KEY (host, owner, repo, id))"
    "CREATE TABLE IF NOT EXISTS repos (
       host TEXT NOT NULL,
       owner TEXT NOT NULL,
       name TEXT NOT NULL,
       description TEXT,
       is_user_repo INTEGER DEFAULT 0,
       fork INTEGER DEFAULT 0,
       archived INTEGER DEFAULT 0,
       private INTEGER DEFAULT 0,
       has_issues INTEGER DEFAULT 1,
       has_pull_requests INTEGER DEFAULT 1,
       has_wiki INTEGER DEFAULT 0,
       has_projects INTEGER DEFAULT 0,
       has_releases INTEGER DEFAULT 0,
       has_actions INTEGER DEFAULT 0,
       default_branch TEXT,
       stars_count INTEGER DEFAULT 0,
       forks_count INTEGER DEFAULT 0,
       open_issues_count INTEGER DEFAULT 0,
       language TEXT,
       updated_at TEXT,
       PRIMARY KEY (host, owner, name))"
    "CREATE TABLE IF NOT EXISTS sync_state (
       host TEXT NOT NULL,
       owner TEXT NOT NULL,
       repo TEXT NOT NULL,
       endpoint TEXT NOT NULL,
       last_synced TEXT,
       etag TEXT,
       last_modified TEXT,
       PRIMARY KEY (host, owner, repo, endpoint))"
    )
  "SQL statements to initialize the database schema.")

;;; Connection management

(defun forgejo-db--ensure ()
  "Open or create the SQLite database, run schema if needed.
Detects stale schema and rebuilds the database if necessary."
  (unless (and forgejo-db (sqlitep forgejo-db))
    (let* ((dir (file-name-as-directory forgejo-db-dir))
           (db-file (expand-file-name "forgejo.db" dir)))
      (unless (file-directory-p dir)
        (make-directory dir t))
      (setq forgejo-db (sqlite-open db-file))
      ;; Detect stale schema and rebuild
      (when (forgejo-db--stale-schema-p)
        (sqlite-close forgejo-db)
        (delete-file db-file)
        (setq forgejo-db (sqlite-open db-file))
        (message "forgejo.el: Rebuilt database (schema changed)"))
      (dolist (stmt forgejo-db--schema)
        (sqlite-execute forgejo-db stmt))))
  forgejo-db)

(defun forgejo-db--stale-schema-p ()
  "Return non-nil if the DB schema is outdated.
Checks for columns added in the latest migration."
  (condition-case nil
      (let ((cols (mapcar #'cadr
                          (sqlite-select forgejo-db
                                         "PRAGMA table_info(repos)"))))
        (and cols (not (member "has_issues" cols))))
    (error nil)))

(defun forgejo-db--close ()
  "Close the database connection."
  (when (and forgejo-db (sqlitep forgejo-db))
    (sqlite-close forgejo-db)
    (setq forgejo-db nil)))

;;; Low-level wrappers

(defun forgejo-db--execute (sql &rest args)
  "Execute SQL with ARGS on the forgejo database."
  (apply #'sqlite-execute (forgejo-db--ensure) sql args))

(defun forgejo-db--select (sql &rest args)
  "Execute SQL query with ARGS, return result rows."
  (apply #'sqlite-select (forgejo-db--ensure) sql args))

(defmacro forgejo-db--with-transaction (&rest body)
  "Execute BODY inside a single SQLite transaction."
  (declare (indent 0))
  `(let ((db (forgejo-db--ensure)))
     (sqlite-execute db "BEGIN")
     (condition-case err
         (prog1 (progn ,@body)
           (sqlite-execute db "COMMIT"))
       (error
        (sqlite-execute db "ROLLBACK")
        (signal (car err) (cdr err))))))

;;; JSON helpers

(defun forgejo-db--nullable (value)
  "Convert VALUE to nil if it is :null or :false."
  (if (memq value '(:null :false)) nil value))

(defun forgejo-db--encode-json (value)
  "Encode VALUE as a JSON string for storage."
  (if value (json-encode value) "null"))

(defun forgejo-db--decode-json (text)
  "Decode JSON TEXT from storage.  Returns nil for null/empty."
  (when (and text (not (string= text "null")) (not (string-empty-p text)))
    (json-parse-string text :object-type 'alist :array-type 'list)))

;;; Issues

(defun forgejo-db--track-edit (db host owner repo number new-body)
  "If issue body changed, append old body to previous_body history.
Returns nil if no change or no existing row."
  (setq owner (downcase owner) repo (downcase repo))
  (when-let* ((new-body)
              (existing (car (sqlite-select
                              db
                              "SELECT body, previous_body FROM issues
                               WHERE host = ? AND owner = ? AND repo = ? AND number = ?"
                              (list host owner repo number))))
              (old-body (nth 0 existing))
              ((and old-body (not (string= old-body new-body)))))
    (let* ((history-json (nth 1 existing))
           (history (if history-json
                        (forgejo-db--decode-json history-json)
                      nil))
           (updated (append history (list old-body))))
      (sqlite-execute
       db
       "UPDATE issues SET previous_body = ? WHERE host = ? AND owner = ? AND repo = ? AND number = ?"
       (list (forgejo-db--encode-json updated) host owner repo number)))))

(defun forgejo-db-save-issues (host owner repo issues &optional is-pull)
  "Upsert ISSUES (list of API alists) for HOST/OWNER/REPO.
Tracks body edits in previous_body.
When IS-PULL is non-nil, mark all entries as pull requests regardless of
whether a `pull_request' field is present in the data."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--with-transaction
    (let ((db (forgejo-db--ensure)))
      (dolist (issue issues)
	(let-alist issue
          (let ((body (forgejo-db--nullable .body))
		(labels (forgejo-db--nullable .labels))
		(milestone (forgejo-db--nullable .milestone))
		(assignees (forgejo-db--nullable .assignees))
		(closed (forgejo-db--nullable .closed_at))
		(pr (or is-pull (forgejo-db--nullable .pull_request))))
            ;; Track edits before overwriting
            (when body
              (forgejo-db--track-edit db host owner repo .number body))
            (sqlite-execute
             db
             "INSERT INTO issues
              (id, host, owner, repo, number, title, state, body,
               user, labels, milestone, assignees, comments_count,
               created_at, updated_at, closed_at, is_pull, read,
               pin_order, due_date, is_locked)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?)
            ON CONFLICT(host, owner, repo, number) DO UPDATE SET
              title=excluded.title, state=excluded.state, body=excluded.body,
              user=excluded.user, labels=excluded.labels, milestone=excluded.milestone,
              assignees=excluded.assignees, comments_count=excluded.comments_count,
              updated_at=excluded.updated_at, closed_at=excluded.closed_at,
              is_pull=excluded.is_pull, read=0,
              pin_order=excluded.pin_order, due_date=excluded.due_date,
              is_locked=excluded.is_locked"
             (list .id host owner repo .number .title .state body
                   (alist-get 'login .user)
                   (forgejo-db--encode-json (when (listp labels) labels))
                   (when (listp milestone) (alist-get 'title milestone))
                   (forgejo-db--encode-json
                    (when (listp assignees)
                      (mapcar (lambda (a) (alist-get 'login a)) assignees)))
                   .comments
                   .created_at .updated_at closed
                   (if pr 1 0)
                   (or .pin_order 0)
                   (forgejo-db--nullable .due_date)
                   (if (eq .is_locked t) 1 0)))))))))

(defun forgejo-db--like (s)
  "Wrap S in SQL LIKE wildcards."
  (format "%%%s%%" s))

(defun forgejo-db--read-val (v)
  "Convert read filter V to integer."
  (if (equal v "yes") 1 0))

(defun forgejo-db--type-clause (v)
  "Return bare SQL clause for type filter V."
  (if (equal v "pr") "is_pull = 1" "is_pull = 0"))

(defconst forgejo-db--filter-specs
  '((:state     "state = ?"     identity)
    (:label     "labels LIKE ?" forgejo-db--like)
    (:labels    "labels LIKE ?" forgejo-db--like)
    (:milestone "milestone = ?" identity)
    (:author    "user LIKE ?"   forgejo-db--like)
    (:query     "title LIKE ?"  forgejo-db--like)
    (:read      "read = ?"      forgejo-db--read-val)
    (:type      nil             forgejo-db--type-clause)
    (:is-pull   "is_pull = 1"   nil)
    (:no-pulls  "is_pull = 0"   nil))
  "Filter specs: (KEY SQL-CLAUSE VALUE-TRANSFORM).
When SQL-CLAUSE is nil, TRANSFORM returns the clause itself (bare SQL).
When TRANSFORM is nil, the clause is bare SQL with no bound argument.")

(defun forgejo-db--build-filter-clauses (filters)
  "Build (CLAUSES . ARGS) from FILTERS plist.
CLAUSES is a list of SQL WHERE fragments, ARGS is a list of
bound parameter values."
  (cl-loop for (key clause transform) in forgejo-db--filter-specs
           for val = (plist-get filters key)
           when val
           if (null transform)
           collect clause into clauses
           else if (null clause)
           collect (funcall transform val) into clauses
           else
           collect clause into clauses
           and collect (funcall transform val) into args
           finally return (cons clauses args)))

(defun forgejo-db-get-issues (host owner repo &optional filters)
  "Query cached issues for HOST/OWNER/REPO with optional FILTERS.
FILTERS is a plist with keys:
  :state      \"open\", \"closed\", or nil for all
  :label      label name substring to match
  :labels     alias for :label
  :milestone  milestone title to match
  :author     author/poster login to match
  :query      search string for title
  :read       \"yes\" or \"no\" for read/unread status
  :type       \"pr\" or \"issue\"
  :is-pull    when non-nil, filter to pull requests only
  :no-pulls   when non-nil, exclude pull requests"
  (setq owner (downcase owner) repo (downcase repo))
  (let* ((filter-result (forgejo-db--build-filter-clauses filters))
         (clauses (append (list "host = ?" "owner = ?" "repo = ?")
                          (car filter-result)))
         (args (append (list host owner repo)
                       (cdr filter-result))))
    (forgejo-db--select
     (format "SELECT %s FROM issues WHERE %s ORDER BY pin_order DESC, updated_at DESC"
             forgejo-db--issue-columns
             (mapconcat #'identity clauses " AND "))
     args)))

;;; Timeline events

(defun forgejo-db-save-timeline (host owner repo number events)
  "Upsert timeline EVENTS for issue NUMBER in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (when (and events (listp events))
    (forgejo-db--with-transaction
      (let ((db (forgejo-db--ensure)))
	(dolist (event events)
          (let-alist event
            (sqlite-execute
             db
             "INSERT INTO timeline_events
              (id, host, owner, repo, issue_number, type, body,
               user, created_at, data)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(host, owner, repo, issue_number, id) DO UPDATE SET
              type=excluded.type, body=excluded.body,
              user=excluded.user, created_at=excluded.created_at,
              data=excluded.data"
             (list .id host owner repo number
                   (forgejo-db--nullable .type)
                   (forgejo-db--nullable .body)
                   (alist-get 'login (forgejo-db--nullable .user))
                   (forgejo-db--nullable .created_at)
                   (forgejo-db--encode-json event)))))))))

(defun forgejo-db-get-timeline (host owner repo number)
  "Get cached timeline events for issue NUMBER in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--select
   (format "SELECT %s FROM timeline_events
            WHERE host = ? AND owner = ? AND repo = ? AND issue_number = ?
            ORDER BY created_at ASC"
           forgejo-db--timeline-columns)
   (list host owner repo number)))

;;; Labels

(defun forgejo-db-save-labels (host owner repo labels)
  "Upsert LABELS for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (let ((db (forgejo-db--ensure)))
    (dolist (label labels)
      (let-alist label
        (sqlite-execute
         db
         "INSERT OR REPLACE INTO labels
            (id, host, owner, repo, name, color, description)
          VALUES (?, ?, ?, ?, ?, ?, ?)"
         (list .id host owner repo .name .color .description))))))

(defun forgejo-db-get-labels (host owner repo)
  "Get cached labels for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--select
   "SELECT * FROM labels WHERE host = ? AND owner = ? AND repo = ?
    ORDER BY name"
   (list host owner repo)))

(defun forgejo-db-get-label-id (host owner repo name)
  "Return the label ID for NAME in HOST/OWNER/REPO, or nil."
  (setq owner (downcase owner) repo (downcase repo))
  (caar (forgejo-db--select
         "SELECT id FROM labels WHERE host = ? AND owner = ? AND repo = ? AND name = ?"
         (list host owner repo name))))

;;; Milestones

(defun forgejo-db-save-milestones (host owner repo milestones)
  "Upsert MILESTONES for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (let ((db (forgejo-db--ensure)))
    (dolist (ms milestones)
      (let-alist ms
        (sqlite-execute
         db
         "INSERT OR REPLACE INTO milestones
            (id, host, owner, repo, title, state, open_issues, closed_issues)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
         (list .id host owner repo .title .state
               .open_issues .closed_issues))))))

(defun forgejo-db-get-milestones (host owner repo)
  "Get cached milestones for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--select
   "SELECT * FROM milestones WHERE host = ? AND owner = ? AND repo = ?
    ORDER BY title"
   (list host owner repo)))

(defun forgejo-db-get-milestone-id (host owner repo title)
  "Return the milestone ID for TITLE in HOST/OWNER/REPO, or nil."
  (setq owner (downcase owner) repo (downcase repo))
  (caar (forgejo-db--select
         "SELECT id FROM milestones WHERE host = ? AND owner = ? AND repo = ? AND title = ?"
         (list host owner repo title))))

;;; Repos

(defun forgejo-db-save-repo (host repo &optional is-user-repo)
  "Save REPO (API alist) for HOST.
When IS-USER-REPO is non-nil, mark it as a user repository."
  (let ((db (forgejo-db--ensure)))
    (let-alist repo
      (sqlite-execute
       db
       "INSERT INTO repos (host, owner, name, description, is_user_repo,
          fork, archived, private, has_issues, has_pull_requests,
          has_wiki, has_projects, has_releases, has_actions,
          default_branch, stars_count, forks_count, open_issues_count,
          language, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(host, owner, name) DO UPDATE SET
          description=excluded.description,
          is_user_repo=MAX(is_user_repo, excluded.is_user_repo),
          fork=excluded.fork, archived=excluded.archived,
          private=excluded.private, has_issues=excluded.has_issues,
          has_pull_requests=excluded.has_pull_requests,
          has_wiki=excluded.has_wiki, has_projects=excluded.has_projects,
          has_releases=excluded.has_releases, has_actions=excluded.has_actions,
          default_branch=excluded.default_branch,
          stars_count=excluded.stars_count, forks_count=excluded.forks_count,
          open_issues_count=excluded.open_issues_count,
          language=excluded.language, updated_at=excluded.updated_at"
       (list host
             (downcase (alist-get 'login .owner))
             (downcase .name)
             (forgejo-db--nullable .description)
             (if is-user-repo 1 0)
             (if (eq .fork t) 1 0)
             (if (eq .archived t) 1 0)
             (if (eq .private t) 1 0)
             (if (eq .has_issues t) 1 0)
             (if (eq .has_pull_requests t) 1 0)
             (if (eq .has_wiki t) 1 0)
             (if (eq .has_projects t) 1 0)
             (if (eq .has_releases t) 1 0)
             (if (eq .has_actions t) 1 0)
             (forgejo-db--nullable .default_branch)
             (or .stars_count 0)
             (or .forks_count 0)
             (or .open_issues_count 0)
             (forgejo-db--nullable .language)
             (forgejo-db--nullable .updated_at))))))

(defun forgejo-db-save-user-repos (host repos)
  "Save REPOS (list of API alists) as user repos for HOST."
  (dolist (repo repos)
    (forgejo-db-save-repo host repo t)))

(defun forgejo-db-get-repo (host owner name)
  "Return metadata alist for OWNER/NAME on HOST, or nil."
  (setq owner (downcase owner) name (downcase name))
  (when-let* ((row (car (forgejo-db--select
                         "SELECT has_issues, has_pull_requests, has_wiki,
                            archived, fork, default_branch
                          FROM repos WHERE host = ? AND owner = ? AND name = ?"
                         (list host owner name)))))
    `((has_issues . ,(= (nth 0 row) 1))
      (has_pull_requests . ,(= (nth 1 row) 1))
      (has_wiki . ,(= (nth 2 row) 1))
      (archived . ,(= (nth 3 row) 1))
      (fork . ,(= (nth 4 row) 1))
      (default_branch . ,(nth 5 row)))))

(defun forgejo-db-get-user-repos (host)
  "Get cached user repo names for HOST as list of \"owner/name\" strings."
  (mapcar (lambda (row) (format "%s/%s" (nth 0 row) (nth 1 row)))
          (forgejo-db--select
           "SELECT owner, name FROM repos WHERE host = ? AND is_user_repo = 1
            ORDER BY name"
           (list host))))

(defun forgejo-db-get-cached-repos (host)
  "Return all \"owner/repo\" strings that have cached issues for HOST."
  (mapcar (lambda (row) (format "%s/%s" (nth 0 row) (nth 1 row)))
          (forgejo-db--select
           "SELECT DISTINCT owner, repo FROM issues WHERE host = ?
            ORDER BY owner, repo"
           (list host))))

;;; Pin

(defun forgejo-db-set-pin-order (host owner repo number pin-order)
  "Set PIN-ORDER for issue NUMBER in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--execute
   "UPDATE issues SET pin_order = ?
    WHERE host = ? AND owner = ? AND repo = ? AND number = ?"
   (list pin-order host owner repo number)))

;;; Delete

(defun forgejo-db-delete-issue (host owner repo number)
  "Delete issue NUMBER and its timeline from the DB for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--with-transaction
    (forgejo-db--execute
     "DELETE FROM timeline_events
      WHERE host = ? AND owner = ? AND repo = ? AND issue_number = ?"
     (list host owner repo number))
    (forgejo-db--execute
     "DELETE FROM issues
      WHERE host = ? AND owner = ? AND repo = ? AND number = ?"
     (list host owner repo number))))

;;; Stale timeline detection

(defun forgejo-db-stale-timelines (host owner repo limit)
  "Return issue numbers in HOST/OWNER/REPO with stale cached timelines.
Only considers issues previously viewed (with a sync_state entry for
their timeline).  Results are ordered by most recently updated first,
capped at LIMIT."
  (setq owner (downcase owner) repo (downcase repo))
  (mapcar #'car
          (forgejo-db--select
           "SELECT i.number FROM issues i
            JOIN sync_state s
              ON s.host = i.host AND s.owner = i.owner AND s.repo = i.repo
              AND s.endpoint = 'timeline/' || i.number
            WHERE i.host = ? AND i.owner = ? AND i.repo = ?
              AND i.updated_at > s.last_synced
            ORDER BY i.updated_at DESC
            LIMIT ?"
           (list host owner repo limit))))

;;; Sync state tracking

(defun forgejo-db-get-sync-time (host owner repo endpoint)
  "Get the last sync timestamp for ENDPOINT in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (caar (forgejo-db--select
         "SELECT last_synced FROM sync_state
          WHERE host = ? AND owner = ? AND repo = ? AND endpoint = ?"
         (list host owner repo endpoint))))

(defun forgejo-db-set-sync-time (host owner repo endpoint time)
  "Set the last sync TIME for ENDPOINT in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--execute
   "INSERT INTO sync_state (host, owner, repo, endpoint, last_synced)
    VALUES (?, ?, ?, ?, ?)
    ON CONFLICT (host, owner, repo, endpoint)
    DO UPDATE SET last_synced = excluded.last_synced"
   (list host owner repo endpoint time)))

(defun forgejo-db-get-cache-headers (host owner repo endpoint)
  "Get cached ETag and Last-Modified for ENDPOINT in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (let ((row (car (forgejo-db--select
                   "SELECT etag, last_modified FROM sync_state
                    WHERE host = ? AND owner = ? AND repo = ? AND endpoint = ?"
                   (list host owner repo endpoint)))))
    (when row
      (list :etag (nth 0 row) :last-modified (nth 1 row)))))

(defun forgejo-db-set-cache-headers (host owner repo endpoint etag last-modified)
  "Update cached ETAG and LAST-MODIFIED for ENDPOINT in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--execute
   "INSERT INTO sync_state (host, owner, repo, endpoint, etag, last_modified)
    VALUES (?, ?, ?, ?, ?, ?)
    ON CONFLICT (host, owner, repo, endpoint)
    DO UPDATE SET etag = excluded.etag, last_modified = excluded.last_modified"
   (list host owner repo endpoint etag last-modified)))

;;; Row-to-alist conversion (explicit column queries)

(defconst forgejo-db--issue-columns
  "number, title, state, body, user, labels, milestone,
   assignees, comments_count, created_at, updated_at, closed_at, is_pull,
   previous_body, read, pin_order, due_date, is_locked"
  "Column list for issue queries (deterministic order).")

(defun forgejo-db--row-to-issue-alist (row)
  "Convert an issue ROW to an API-shaped alist.
ROW must come from a query using `forgejo-db--issue-columns':
  0=number 1=title 2=state 3=body 4=user 5=labels
  6=milestone 7=assignees 8=comments_count 9=created_at
  10=updated_at 11=closed_at 12=is_pull 13=previous_body 14=read
  15=pin_order 16=due_date 17=is_locked"
  (let ((labels (forgejo-db--decode-json (nth 5 row)))
        (assignees-raw (forgejo-db--decode-json (nth 7 row))))
    `((number . ,(nth 0 row))
      (title . ,(nth 1 row))
      (state . ,(nth 2 row))
      (body . ,(nth 3 row))
      (user . ((login . ,(nth 4 row))))
      (labels . ,labels)
      (milestone . ,(when (nth 6 row) `((title . ,(nth 6 row)))))
      (assignees . ,(mapcar (lambda (login) `((login . ,login)))
                            (if (listp assignees-raw) assignees-raw nil)))
      (comments . ,(nth 8 row))
      (created_at . ,(nth 9 row))
      (updated_at . ,(nth 10 row))
      (closed_at . ,(nth 11 row))
      (pull_request . ,(when (= (or (nth 12 row) 0) 1) t))
      (previous_body . ,(nth 13 row))
      (read . ,(or (nth 14 row) 0))
      (pin_order . ,(or (nth 15 row) 0))
      (due_date . ,(nth 16 row))
      (is_locked . ,(= (or (nth 17 row) 0) 1)))))

(defconst forgejo-db--timeline-columns
  "id, type, body, user, created_at, data"
  "Column list for timeline queries (deterministic order).")

(defun forgejo-db--row-to-timeline-alist (row)
  "Convert a timeline ROW to an alist.
ROW must come from a query using `forgejo-db--timeline-columns':
  0=id 1=type 2=body 3=user 4=created_at 5=data
The `data' JSON blob is merged into the result to provide event-specific
fields like label, assignee, old_title, new_title."
  (let* ((base `((id . ,(nth 0 row))
                 (type . ,(nth 1 row))
                 (body . ,(nth 2 row))
                 (user . ((login . ,(nth 3 row))))
                 (created_at . ,(nth 4 row))))
         (data-json (nth 5 row))
         (data (when data-json (forgejo-db--decode-json data-json))))
    (if data
        (append base data)
      base)))

(defun forgejo-db-get-issue (host owner repo number)
  "Get a single issue alist from the DB, or nil."
  (setq owner (downcase owner) repo (downcase repo))
  (when-let* ((rows (forgejo-db--select
                     (format "SELECT %s FROM issues
                              WHERE host = ? AND owner = ? AND repo = ? AND number = ?"
                             forgejo-db--issue-columns)
                     (list host owner repo number)))
              (row (car rows)))
    (forgejo-db--row-to-issue-alist row)))

(defun forgejo-db-mark-read (host owner repo number)
  "Mark issue/PR NUMBER as read for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (forgejo-db--execute
   "UPDATE issues SET read = 1
    WHERE host = ? AND owner = ? AND repo = ? AND number = ?"
   (list host owner repo number)))

(defun forgejo-db-close-missing (host owner repo numbers &optional is-pull)
  "Mark issues NOT in NUMBERS as closed for HOST/OWNER/REPO.
When IS-PULL is non-nil, only affect pull requests."
  (setq owner (downcase owner) repo (downcase repo))
  (when numbers
    (let ((placeholders (mapconcat (lambda (_) "?") numbers ","))
          (pull-filter (if is-pull "AND is_pull = 1" "AND is_pull = 0")))
      (forgejo-db--execute
       (format "UPDATE issues SET state = 'closed'
                WHERE host = ? AND owner = ? AND repo = ?
                AND state = 'open' %s
                AND number NOT IN (%s)"
               pull-filter placeholders)
       (append (list host owner repo) numbers)))))

(defun forgejo-db-get-authors (host owner repo)
  "Get distinct author logins for HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (mapcar #'car
          (forgejo-db--select
           "SELECT DISTINCT user FROM issues
            WHERE host = ? AND owner = ? AND repo = ? AND user IS NOT NULL
            ORDER BY user"
           (list host owner repo))))

;;; Issue/PR titles for completion

(defun forgejo-db-get-issue-titles (host owner repo)
  "Return alist of (NUMBER . TITLE) for all issues/PRs in HOST/OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (mapcar (lambda (row) (cons (nth 0 row) (nth 1 row)))
          (forgejo-db--select
           "SELECT number, title FROM issues
            WHERE host = ? AND owner = ? AND repo = ?
            ORDER BY number DESC"
           (list host owner repo))))

;;; Host lookup

(defun forgejo-db-get-hosts-for-repo (owner repo)
  "Return distinct host strings that have data for OWNER/REPO."
  (setq owner (downcase owner) repo (downcase repo))
  (mapcar #'car
          (forgejo-db--select
           "SELECT DISTINCT host FROM issues WHERE owner = ? AND repo = ?"
           (list owner repo))))

(provide 'forgejo-db)
;;; forgejo-db.el ends here
