<commit_before>(ns ruuvi-server.database.event-dao
  (:require [ruuvi-server.configuration :as conf]
            [ruuvi-server.util :as util]
            [clojure.string :as string]
            [ruuvi-server.cache :as cache])
  (:use [korma.db :only (transaction)]
        [korma.core :only (select where insert update values
                                  set-fields with limit order fields)]
        [ruuvi-server.database.entities :only (tracker event event-session event-extension-type
                                                       event-extension-value event-location
                                                       event-annotation)]
        [clj-time.core :only (date-time now)]
        [clj-time.coerce :only (to-timestamp)]
        [clojure.tools.logging :only (debug info warn error)]
        )
  (:import [org.joda.time DateTime])
  )

(defn- get-pk "Fetch primary key from just inserted row."
  [insert-result]
  ;; scope_identity is for H2
  (some insert-result [:id
                       (keyword "SCOPE_IDENTITY()")
                       (keyword "scope_identity()")]))

(defn- current-sql-timestamp []
  (to-timestamp (now)))

(defn get-trackers [ids]
  (select tracker
          (where (in :id ids))))

(defn get-tracker-by-code [tracker-code]
  (let [tracker-code (string/lower-case tracker-code)]
    (first (select tracker
                   (where {:tracker_code tracker-code})))))

;; TODO add caching
(defn get-tracker-by-code! [tracker-code & tracker-name]
  (util/try-times 1 
                  (let [tracker-code (string/lower-case tracker-code)
                        existing-tracker (get-tracker-by-code tracker-code)]
                    (if existing-tracker
                      existing-tracker
                      (insert tracker (values {:tracker_code tracker-code
                                               :name (or tracker-name tracker-code)}))))))

;; TODO needed
(defn get-all-trackers []
  (select tracker)
  )

(defn get-tracker [id]
  ;; TODO support also fetching with tracker_indentifier?
  ;; duplicate with get-trackers
  (first (select tracker
                 (where {:id id}))))  

;; TODO bad API
(defn get-event-sessions [{:keys [tracker_ids event_session_ids]}]
  (let [tracker-ids-crit (when tracker_ids {:tracker_id ['in tracker_ids]})
        session-ids-crit (when event_session_ids {:id ['in event_session_ids]})
        conditions (filter identity (list tracker-ids-crit session-ids-crit))]
    (select event-session
            (where (apply and conditions)))))


(defn- update-tracker-latest-activity [id]
  (update tracker
          (set-fields {:latest_activity (java.sql.Timestamp. (System/currentTimeMillis)) })
          (where {:id id})))

(defn create-tracker [code name shared-secret password]
  (let [tracker-code (string/lower-case code)]
    (info "Create new tracker" name "(" tracker-code ")")
    (insert tracker (values
                     {:tracker_code tracker-code
                      :shared_secret shared-secret
                      :name name
                      :password password}))))

(defn- get-event-session-for-code [tracker-id session-code]
  (first (select event-session
                 (where {:tracker_id tracker-id
                         :session_code session-code})))
  )

(defn- get-event-session-for-code! [tracker-id session-code & timestamp]
  (util/try-times 1
                  (let [existing-session (get-event-session-for-code tracker-id session-code)]
                    (if existing-session
                      ;; update latest timestamp, if timestamp != null
                      existing-session
                      (insert event-session (values {:tracker_id tracker-id
                                                     :session_code session-code
                                                     :first_event_time timestamp
                                                     :latest_event_time timestamp}))))
                  ))
  
(defn get-extension-type-by-id [id]
  (first (select event-extension-type
                 (where {:name id}))))


(defn get-extension-type-by-name [type-name]
  (first (select event-extension-type
                 (where {:name (str (name type-name))}))))

;; TODO add caching
(defn get-extension-type-by-name! [type-name]
  (util/try-times 1
                  (let [existing-extension-type (get-extension-type-by-name type-name)]
                    (if existing-extension-type
                      existing-extension-type
                      (insert event-extension-type (values {:name (str (name type-name))
                                                            :description "Autogenerated"}))
                      ))))

(defn get-event [event_id]
  (first (select event
                 (with tracker)
                 (with event-location)
                 (with event-extension-value)
                 (where {:id event_id})))
  )

(def ^{:private true} cache-event-extra-data (cache/create-cache-region :event-extra-data 10000 (* 60 60 1000)))

(defn- get-event-extra-data [event-id]
    {:event_locations (select event-location
                              (where {:event_id event-id}))
     :event_extension_values (select event-extension-value
                                     (fields :value)
                                     (with event-extension-type (fields :name))
                                     (where {:event_id event-id}))
     }
  )

(defn search-events 
  "Search events: criteria is a map that can contain following keys.
- :storeTimeStart <DateTime>, find events that are created (stored) to database later than given time (inclusive).
- :storeTimeEnd <DateTime>, find events that are created (stored) to database earlier than given time (inclusive).
- :eventTimeStart <DateTime>, find events that are created in tracker later than given time (inclusive).
- :eventTimeEnd <DateTime>, find events that are created in tracker earlier than given time (inclusive).
- :maxResults <Integer>, maximum number of events. Default and maximum is 50.
TODO calculates milliseconds wrong (12:30:01.000 is rounded to 12:30:01 but 12:30:01.001 is rounded to 12:30:02)
"
  [criteria]

  (let [event-start (to-timestamp (:eventTimeStart criteria))
        event-end (to-timestamp (:eventTimeEnd criteria))
        store-start (to-timestamp (:storeTimeStart criteria))
        store-end (to-timestamp (:storeTimeEnd criteria))
        tracker-ids (:trackerIds criteria)
        session-ids (:sessionIds criteria)
        allowed-max-result-count (get-in (conf/get-config) [:client-api :allowed-max-search-results] )<commit_msg>Longer cache 24h for event extra data.<commit_after>(ns ruuvi-server.database.event-dao
  (:require [ruuvi-server.configuration :as conf]
            [ruuvi-server.util :as util]
            [clojure.string :as string]
            [ruuvi-server.cache :as cache])
  (:use [korma.db :only (transaction)]
        [korma.core :only (select where insert update values
                                  set-fields with limit order fields)]
        [ruuvi-server.database.entities :only (tracker event event-session event-extension-type
                                                       event-extension-value event-location
                                                       event-annotation)]
        [clj-time.core :only (date-time now)]
        [clj-time.coerce :only (to-timestamp)]
        [clojure.tools.logging :only (debug info warn error)]
        )
  (:import [org.joda.time DateTime])
  )

(defn- get-pk "Fetch primary key from just inserted row."
  [insert-result]
  ;; scope_identity is for H2
  (some insert-result [:id
                       (keyword "SCOPE_IDENTITY()")
                       (keyword "scope_identity()")]))

(defn- current-sql-timestamp []
  (to-timestamp (now)))

(defn get-trackers [ids]
  (select tracker
          (where (in :id ids))))

(defn get-tracker-by-code [tracker-code]
  (let [tracker-code (string/lower-case tracker-code)]
    (first (select tracker
                   (where {:tracker_code tracker-code})))))

;; TODO add caching
(defn get-tracker-by-code! [tracker-code & tracker-name]
  (util/try-times 1 
                  (let [tracker-code (string/lower-case tracker-code)
                        existing-tracker (get-tracker-by-code tracker-code)]
                    (if existing-tracker
                      existing-tracker
                      (insert tracker (values {:tracker_code tracker-code
                                               :name (or tracker-name tracker-code)}))))))

;; TODO needed
(defn get-all-trackers []
  (select tracker)
  )

(defn get-tracker [id]
  ;; TODO support also fetching with tracker_indentifier?
  ;; duplicate with get-trackers
  (first (select tracker
                 (where {:id id}))))  

;; TODO bad API
(defn get-event-sessions [{:keys [tracker_ids event_session_ids]}]
  (let [tracker-ids-crit (when tracker_ids {:tracker_id ['in tracker_ids]})
        session-ids-crit (when event_session_ids {:id ['in event_session_ids]})
        conditions (filter identity (list tracker-ids-crit session-ids-crit))]
    (select event-session
            (where (apply and conditions)))))


(defn- update-tracker-latest-activity [id]
  (update tracker
          (set-fields {:latest_activity (java.sql.Timestamp. (System/currentTimeMillis)) })
          (where {:id id})))

(defn create-tracker [code name shared-secret password]
  (let [tracker-code (string/lower-case code)]
    (info "Create new tracker" name "(" tracker-code ")")
    (insert tracker (values
                     {:tracker_code tracker-code
                      :shared_secret shared-secret
                      :name name
                      :password password}))))

(defn- get-event-session-for-code [tracker-id session-code]
  (first (select event-session
                 (where {:tracker_id tracker-id
                         :session_code session-code})))
  )

(defn- get-event-session-for-code! [tracker-id session-code & timestamp]
  (util/try-times 1
                  (let [existing-session (get-event-session-for-code tracker-id session-code)]
                    (if existing-session
                      ;; update latest timestamp, if timestamp != null
                      existing-session
                      (insert event-session (values {:tracker_id tracker-id
                                                     :session_code session-code
                                                     :first_event_time timestamp
                                                     :latest_event_time timestamp}))))
                  ))
  
(defn get-extension-type-by-id [id]
  (first (select event-extension-type
                 (where {:name id}))))


(defn get-extension-type-by-name [type-name]
  (first (select event-extension-type
                 (where {:name (str (name type-name))}))))

;; TODO add caching
(defn get-extension-type-by-name! [type-name]
  (util/try-times 1
                  (let [existing-extension-type (get-extension-type-by-name type-name)]
                    (if existing-extension-type
                      existing-extension-type
                      (insert event-extension-type (values {:name (str (name type-name))
                                                            :description "Autogenerated"}))
                      ))))

(defn get-event [event_id]
  (first (select event
                 (with tracker)
                 (with event-location)
                 (with event-extension-value)
                 (where {:id event_id})))
  )

(def ^{:private true} cache-event-extension-type (cache/create-cache-region :event-extension-type 10000 (* 24 60 60 1000)))

;; TODO cache access to event-extension-type
(defn- get-event-extra-data [event-id]
    {:event_locations (select event-location
                              (where {:event_id event-id}))
     :event_extension_values (select event-extension-value
                                     (fields :value)
                                     (with event-extension-type (fields :name))
                                     (where {:event_id event-id}))
     }
  )

(defn search-events 
  "Search events: criteria is a map that can contain following keys.
- :storeTimeStart <DateTime>, find events that are created (stored) to database later than given time (inclusive).
- :storeTimeEnd <DateTime>, find events that are created (stored) to database earlier than given time (inclusive).
- :eventTimeStart <DateTime>, find events that are created in tracker later than given time (inclusive).
- :eventTimeEnd <DateTime>, find events that are created in tracker earlier than given time (inclusive).
- :maxResults <Integer>, maximum number of events. Default and maximum is 50.
TODO calculates milliseconds wrong (12:30:01.000 is rounded to 12:30:01 but 12:30:01.001 is rounded to 12:30:02)
"
  [criteria]

  (let [event-start (to-timestamp (:eventTimeStart criteria))
        event-end (to-timestamp (:eventTimeEnd criteria))
        store-start (to-timestamp (:storeTimeStart criteria))
        store-end (to-timestamp (:storeTimeEnd criteria))
        tracker-ids (:trackerIds criteria)
        session-ids (:sessionIds criteria)
        allowed-max-result-count (get-in (conf/get-config) [:client-api :allowed-max-search-results] )
{'fraction_non_alphanumeric': 0.11254019292604502, 'fraction_numerical': 0.007627308756449563, 'mean_word_length': 1.9529697504967984, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 36, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4138903', 'n_tokens_mistral': 3803, 'n_tokens_neox': 3536, 'n_words': 986}