artifacts.id.nextval=SELECT NEXTVAL('ARTIFACTS_ID_SEQ')

artifacts.insert=INSERT INTO artifacts \
    (id, gid, creation, last_access, ttl, factory, data) \
    VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?, ?)

artifacts.update=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP, \
    data = ? WHERE id = ?

artifacts.touch=UPDATE artifacts SET last_access = CURRENT_TIMESTAMP WHERE id = ?

artifacts.outdated=SELECT id, factory, data, gid FROM artifacts WHERE ttl IS NOT NULL \
    AND DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) > ttl \
    AND id NOT IN (SELECT DISTINCT artifact_id FROM collection_items) \
    AND id NOT IN ($LOCKED_IDS$) \
    LIMIT 50

artifacts.select.gid=SELECT id, ttl, factory, data FROM artifacts WHERE gid = ?::uuid \
    AND (ttl IS NULL \
    OR  (DATEDIFF('MILLISECOND', last_access, CURRENT_TIMESTAMP) <= ttl)  \
    OR  id IN (SELECT artifact_id FROM collection_items))

artifacts.get.id=SELECT id FROM artifacts WHERE gid = ?

artifacts.replace=UPDATE artifacts SET \
    creation = CURRENT_TIMESTAMP, last_access = CURRENT_TIMESTAMP, \
    ttl = ?, factory = ?, data = ? \
    WHERE id = ?

artifacts.delete=DELETE FROM artifacts WHERE id = ?

# USERS

users.id.nextval=SELECT NEXTVAL('USERS_ID_SEQ')

users.insert=INSERT INTO users (id, gid, name, role) VALUES (?, ?, ?, ?)

users.select.id.by.gid=SELECT id FROM users WHERE gid = ?
users.select.gid=SELECT id, name, role FROM users WHERE gid = ?

users.delete.id=DELETE FROM users WHERE id = ?

users.delete.collections=DELETE FROM collections where owner_id = ?

users.select.all=SELECT id, gid, name, role FROM users

collection.check.artifact=SELECT id FROM collection_items \
    WHERE artifact_id = ? AND collection_id = ?

# COLLECTION ITEMS
collection.items.id.nextval=SELECT NEXTVAL('COLLECTION_ITEMS_ID_SEQ')

collection.items.insert=INSERT INTO collection_items \
    (id, collection_id, artifact_id, attribute, creation) \
    VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)

collection.item.get.attribute= \
    SELECT ci.attribute FROM collection_items ci \
        INNER JOIN collections c ON ci.collection_id = c.id \
        INNER JOIN artifacts a ON ci.artifact_id = a.id \
        WHERE c.gid = ? AND a.gid = ?

collection.item.set.attribute= \
    UPDATE collection_items SET attribute = ? WHERE id IN ( \
        SELECT ci.id FROM collection_items ci \
            INNER JOIN collections c ON ci.collection_id = c.id \
            INNER JOIN artifacts a ON ci.artifact_id = a.id \
            WHERE c.gid = ? AND a.gid = ?)

collection.item.id.cid.aid= \
    SELECT ci.id, ci.collection_id, ci.artifact_id FROM collection_items ci \
        INNER JOIN collections c ON ci.collection_id = c.id \
        INNER JOIN artifacts a ON ci.artifact_id = a.id \
        WHERE c.gid = ? AND a.gid = ?

collection.item.outdate.artifact= \
    UPDATE artifacts \
    SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
    WHERE id = ? AND \
    NOT EXISTS \
    (SELECT id FROM collection_items WHERE collection_id <> ? AND artifact_id = ?)

collection.item.delete=DELETE FROM collection_items WHERE id = ?

collection.items.list.gid= \
    SELECT a.gid, ci.attribute FROM collection_items ci \
    INNER JOIN artifacts a ON ci.artifact_id = a.id \
    WHERE ci.collection_id IN (SELECT id FROM collections WHERE gid = ?) \
    ORDER BY ci.creation

# COLLECTIONS

collections.outdated= \
    SELECT c.id, c.gid FROM collections c \
        INNER JOIN collection_items ci ON c.id = ci.collection_id \
        INNER JOIN artifacts        a  ON ci.artifact_id = a.id \
        WHERE c.ttl IS NOT NULL \
            AND DATEDIFF('MILLISECOND', c.last_access, CURRENT_TIMESTAMP) > c.ttl \
            AND a.id NOT IN ($LOCKED_IDS$)

collections.update.ttl=UPDATE collections SET ttl = ? WHERE gid = ?

collections.update.name=UPDATE collections SET name = ? WHERE gid = ?

collections.touch.trigger.function = \
   UPDATE collections SET last_access = current_timestamp \
   WHERE id IN \
        (SELECT c.id FROM collections c \
         INNER JOIN collection_items ci ON c.id = ci.collection_id  \
         INNER JOIN artifacts a         ON a.id = ci.artifact_id \
         WHERE a.id = ?)

collections.touch.by.gid =\
    UPDATE collections SET last_access = CURRENT_TIMESTAMP \
        WHERE gid = ?

collections.touch.by.id =\
    UPDATE collections SET last_access = CURRENT_TIMESTAMP \
        WHERE id = ?

collections.id.nextval=SELECT NEXTVAL('COLLECTIONS_ID_SEQ')

collections.id.by.gid=SELECT id FROM collections WHERE gid = ?

delete.collection.items=DELETE FROM collection_items WHERE collection_id = ?

delete.collection=DELETE FROM collections WHERE id = ?

delete.user.collection.items= \
    DELETE FROM collection_items WHERE collection_id IN \
    (SELECT id FROM collections WHERE owner_id = ?)

collections.insert= \
    INSERT INTO collections \
    (id, gid, name, owner_id, creation, last_access, ttl, attribute) \
    VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?, ?)

collection.creation.time=SELECT creation from collections WHERE id = ?

collections.artifacts.oldest=SELECT a.gid, ci.artifact_id  \
    FROM artifacts AS a, collection_items AS ci, collections AS c \
    WHERE ci.collection_id = c.id AND c.gid = ?::uuid AND ci.artifact_id = a.id \
    ORDER BY ci.creation

collections.select.user= \
    SELECT c.gid, c.name, c.creation, u.gid, c.ttl FROM \
    collections c LEFT OUTER JOIN users u ON c.owner_id = u.id \
    WHERE u.gid = ?

collections.select.all= \
    SELECT c.gid, c.name, c.creation, u.gid, c.ttl FROM \
    collections c LEFT OUTER JOIN users u ON c.owner_id = u.id

collections.select.by.gid= \
    SELECT id, name, owner_id, creation, last_access, attribute, ttl \
    FROM collections WHERE gid = ?

users.collections=SELECT collection_id, gid, name FROM collections WHERE owner_id = ?
users.collection.ids=SELECT collection_id FROM collections WHERE owner_id = ?

users.delete.all.collections=DELETE FROM collections WHERE owner_id = ?

artifacts.in.one.collection.only= \
    SELECT artifact_id FROM collection_items\
    WHERE collection_id = ? AND\
    artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?)

outdate.artifacts.collection=UPDATE artifacts \
    SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
    WHERE id IN \
    (SELECT artifact_id FROM collection_items \
        WHERE collection_id = ? AND \
        artifact_id NOT IN (SELECT DISTINCT artifact_id FROM collection_items WHERE collection_id <> ?))

outdate.artifacts.user=UPDATE artifacts \
    SET last_access = DATEADD('MILLISECOND', -2, CURRENT_TIMESTAMP), ttl = 1 \
    WHERE id IN \
    (SELECT artifact_id FROM collection_items WHERE \
        collection_id IN (SELECT id FROM collections WHERE owner_id = ?) \
        AND artifact_id NOT IN \
            (SELECT artifact_id FROM collection_items WHERE collection_id IN \
                (SELECT id FROM collections WHERE owner_id <> ?)))

collection.get.attribute= \
    SELECT c.attribute FROM collections c WHERE c.gid = ?

collection.set.attribute= \
    UPDATE collections SET attribute = ? WHERE gid = ?

all.artifacts = \
    SELECT u.gid AS u_gid, c.gid AS c_gid, c.name AS c_name, c.creation as c_creation, \
           a.gid AS a_gid, a.factory AS factory, a.creation AS a_creation, a.data AS data \
    FROM \
        users u INNER JOIN collections c       ON u.id = c.owner_id \
                INNER JOIN collection_items ci ON c.id = ci.collection_id \
                INNER JOIN artifacts a         ON a.id = ci.artifact_id \
        ORDER BY u_gid, c_gid