<commit_before>;; Copyright © 2015-2019 <NAME>
;; This software is released under the Apache License 2.0.
;; The license text is at http://www.apache.org/licenses/LICENSE-2.0

(ns territory-bro.gis-db
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.tools.logging :as log]
            [medley.core :refer [remove-vals]]
            [territory-bro.db :as db])
  (:import (java.util UUID)
           (org.postgresql.util PSQLException)))

(def ^:private query! (db/compile-queries "db/hugsql/gis.sql"))


;;; Regions

(defn- format-region [territory]
  (remove-vals nil? {:region/id (:id territory)
                     :region/name (:name territory)
                     :region/location (:location territory)}))

(defn get-congregation-boundaries [conn]
  (->> (query! conn :get-congregation-boundaries)
       (map format-region)
       (doall)))

(defn create-congregation-boundary! [conn location]
  (let [id (UUID/randomUUID)]
    (query! conn :create-congregation-boundary {:id id
                                                :location location})
    id))


(defn get-subregions [conn]
  (->> (query! conn :get-subregions)
       (map format-region)
       (doall)))

(defn create-subregion! [conn name location]
  (let [id (UUID/randomUUID)]
    (query! conn :create-subregion {:id id
                                    :name name
                                    :location location})
    id))


(defn get-card-minimap-viewports [conn]
  (->> (query! conn :get-card-minimap-viewports)
       (map format-region)
       (doall)))

(defn create-card-minimap-viewport! [conn location]
  (let [id (UUID/randomUUID)]
    (query! conn :create-card-minimap-viewport {:id id
                                                :location location})
    id))


;;; Territories

(defn- format-territory [territory]
  {:territory/id (:id territory)
   :territory/number (:number territory)
   :territory/addresses (:addresses territory)
   :territory/subregion (:subregion territory)
   :territory/meta (:meta territory)
   :territory/location (:location territory)})

(defn get-territories
  ([conn]
   (get-territories conn {}))
  ([conn search]
   (->> (query! conn :get-territories search)
        (map format-territory)
        (doall))))

(defn get-territory-by-id [conn id]
  (first (get-territories conn {:ids [id]})))

(defn create-territory! [conn territory]
  (let [id (UUID/randomUUID)]
    (query! conn :create-territory {:id id
                                    :number (:territory/number territory)
                                    :addresses (:territory/addresses territory)
                                    :subregion (:territory/subregion territory)
                                    :meta (:territory/meta territory)
                                    :location (:territory/location territory)})
    id))


;;; Changes

(defn- format-gis-change [change]
  change)

(defn get-changes
  ([conn]
   (get-changes conn {}))
  ([conn search]
   (->> (query! conn :get-gis-changes search)
        (map format-gis-change)
        (doall))))


;;; Database users

(defn user-exists? [conn username]
  (not (empty? (jdbc/query conn ["SELECT 1 FROM pg_roles WHERE rolname = ?" username]))))

(defn ensure-user-present! [conn {:keys [username password schema]}]
  (log/info "Creating GIS user:" username)
  (assert username)
  (assert password)
  (assert schema)
  (try
    (jdbc/execute! conn ["SAVEPOINT create_role"])
    (jdbc/execute! conn [(str "CREATE ROLE " username " WITH LOGIN")])
    (jdbc/execute! conn ["RELEASE SAVEPOINT create_role"])
    (catch PSQLException e
      ;; ignore error if role already exists
      (if (= db/psql-duplicate-object (.getSQLState e))
        (do<commit_msg>♻️ Schema for GIS changes<commit_after>;; Copyright © 2015-2019 <NAME>
;; This software is released under the Apache License 2.0.
;; The license text is at http://www.apache.org/licenses/LICENSE-2.0

(ns territory-bro.gis-db
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.tools.logging :as log]
            [medley.core :refer [remove-vals]]
            [schema.core :as s]
            [territory-bro.db :as db])
  (:import (java.util UUID Date)
           (org.postgresql.util PSQLException)))

(def ^:private query! (db/compile-queries "db/hugsql/gis.sql"))


;;; Regions

(defn- format-region [territory]
  (remove-vals nil? {:region/id (:id territory)
                     :region/name (:name territory)
                     :region/location (:location territory)}))

(defn get-congregation-boundaries [conn]
  (->> (query! conn :get-congregation-boundaries)
       (map format-region)
       (doall)))

(defn create-congregation-boundary! [conn location]
  (let [id (UUID/randomUUID)]
    (query! conn :create-congregation-boundary {:id id
                                                :location location})
    id))


(defn get-subregions [conn]
  (->> (query! conn :get-subregions)
       (map format-region)
       (doall)))

(defn create-subregion! [conn name location]
  (let [id (UUID/randomUUID)]
    (query! conn :create-subregion {:id id
                                    :name name
                                    :location location})
    id))


(defn get-card-minimap-viewports [conn]
  (->> (query! conn :get-card-minimap-viewports)
       (map format-region)
       (doall)))

(defn create-card-minimap-viewport! [conn location]
  (let [id (UUID/randomUUID)]
    (query! conn :create-card-minimap-viewport {:id id
                                                :location location})
    id))


;;; Territories

(defn- format-territory [territory]
  {:territory/id (:id territory)
   :territory/number (:number territory)
   :territory/addresses (:addresses territory)
   :territory/subregion (:subregion territory)
   :territory/meta (:meta territory)
   :territory/location (:location territory)})

(defn get-territories
  ([conn]
   (get-territories conn {}))
  ([conn search]
   (->> (query! conn :get-territories search)
        (map format-territory)
        (doall))))

(defn get-territory-by-id [conn id]
  (first (get-territories conn {:ids [id]})))

(defn create-territory! [conn territory]
  (let [id (UUID/randomUUID)]
    (query! conn :create-territory {:id id
                                    :number (:territory/number territory)
                                    :addresses (:territory/addresses territory)
                                    :subregion (:territory/subregion territory)
                                    :meta (:territory/meta territory)
                                    :location (:territory/location territory)})
    id))


;;; Changes

(s/defschema GisFeature
  {:id s/Str ; TODO: change to UUID
   :location s/Str
   (s/optional-key :name) s/Str
   (s/optional-key :number) s/Str
   (s/optional-key :subregion) s/Str
   (s/optional-key :addresses) s/Str
   (s/optional-key :meta) {s/Any s/Any}})

(s/defschema GisChange
  {:id s/Int
   :schema s/Str
   :table s/Str
   :op (s/enum "INSERT" "UPDATE" "DELETE") ; TODO: change to keywords
   :user s/Str
   :time Date ; TODO: change to Instant
   :old (s/maybe GisFeature)
   :new (s/maybe GisFeature)})

(defn- format-gis-change [change]
  (s/validate GisChange change))

(defn get-changes
  ([conn]
   (get-changes conn {}))
  ([conn search]
   (->> (query! conn :get-gis-changes search)
        (map format-gis-change)
        (doall))))


;;; Database users

(defn user-exists? [conn username]
  (not (empty? (jdbc/query conn ["SELECT 1 FROM pg_roles WHERE rolname = ?" username]))))

(defn ensure-user-present! [conn {:keys [username password schema]}]
  (log/info "Creating GIS user:" username)
  (assert username)
  (assert password)
  (assert schema)
  (try
    (jdbc/execute! conn ["SAVEPOINT create_role"])
    (jdbc/execute! conn [(str "CREATE ROLE " username " WITH LOGIN")])
    (jdbc/execute! conn ["RELEASE SAVEPOINT create_role"])
    (catch PSQLException e
      ;; ignore error if role already exists
      (if (= db/psql-duplicate-object (.getSQLState e))
        (do
{'fraction_non_alphanumeric': 0.1387750031059759, 'fraction_numerical': 0.0032302149335321157, 'mean_word_length': 2.3951919021509913, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 22, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5873360', 'n_tokens_mistral': 2679, 'n_tokens_neox': 2473, 'n_words': 654}