cosette/server/controller.js

import Database from 'better-sqlite3'
import { v4 } from 'uuid'
const db = new Database('./cosette.db')

function load () {
  db.pragma('journal_mode = WAL')
  db.exec('CREATE TABLE IF NOT EXISTS cosette (uuid TEXT PRIMARY KEY, name TEXT NOT NULL, description TEXT, tags TEXT, images TEXT, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP)')
  db.exec('CREATE TABLE IF NOT EXISTS chan (uuid TEXT PRIMARY KEY, cosetta_uuid REFERENCES cosette(uuid), message TEXT, updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP)')
}
// TODO: da gestire in qualche modo all'avvio
// load()

export function add (cosetta) {
  const q = db.prepare('INSERT INTO cosette (uuid, name, description, tags, images) VALUES(:uuid, :name, :description, :tags, :imgs)')
  cosetta.uuid = v4()
  cosetta.tags = JSON.stringify(cosetta.tags.split(',').map(t => t.toLowerCase().trim()))
  cosetta.imgs = JSON.stringify(cosetta.imgs)
  q.run(cosetta)
  return cosetta
}

export function get (uuid) {
  let q = db.prepare('SELECT * from cosette WHERE uuid = ?')
  const cosetta = q.get(uuid)
  cosetta.tags = cosetta.tags ? JSON.parse(cosetta.tags) : []
  cosetta.images = cosetta.images ? JSON.parse(cosetta.images) : []

  q = db.prepare('SELECT message, updatedAt FROM chan WHERE cosetta_uuid = ? ORDER BY updatedAt desc')
  const comments = q.all(uuid)

  return { cosetta, comments }
}

export function getAll (limit=0, offset=0, tags=[], search='') {
  const q = db.prepare('SELECT * FROM cosette')
  const ret = q.all()
  return ret.map(r => {
    r.tags = r.tags ? JSON.parse(r.tags) : []
    r.images = r.images ? JSON.parse(r.images) : []
    return r
  })
}


export function addComment (comment) {
  const q = db.prepare('INSERT INTO chan (uuid, cosetta_uuid, message) VALUES(:uuid, :cosetta_uuid, :message)')
  comment.uuid = v4()
  q.run(comment)
  return comment
}

export function getComments (cosetta_uuid) {
  const q = db.prepare('SELECT * from chan WHERE cosetta_uuid = ?')
  return q.all(cosetta_uuid)
}