           :db (hikari-cp.core/make-datasource {:adapter "mysql",
                                                :username "root",
                                                :password "isokala",
-                                               :database-name "salava_extra1",
+                                               :database-name "salavareal",
                                                :server-name "localhost"})})
 
 
-(def most-messages
-  "SELECT bc.id, bc.name, bc.image_file, bc.description, ic.name AS issuer_content_name, ic.url AS issuer_content_url, MAX(b.ctime) AS ctime, badge_content_id  FROM social_event AS se
-  JOIN badge AS b ON se.object = b.badge_content_id
-                    JOIN badge_content AS bc ON b.badge_content_id = bc.id
-                    JOIN issuer_content AS ic ON b.issuer_content_id = ic.id
-                    LEFT JOIN user AS u ON b.user_id = u.id
-                    WHERE b.status = 'accepted' AND b.deleted = 0 AND b.revoked = 0 AND (b.expires_on IS NULL OR b.expires_on > UNIX_TIMESTAMP()) 
-                    
-  GROUP BY bc.id, bc.name, bc.image_file, bc.description, ic.name, ic.url, b.badge_content_id")
- 
-(def lo
-  "select distinct bc.id, count(se.id) as messagecount, count(b.id) as ownercount,  bc.name, bc.image_file, bc.description, ic.name AS issuer_content_name, ic.url AS issuer_content_url, MAX(b.ctime) AS ctime from badge_content as bc 
-  left join badge as b on bc.id = b.badge_content_id AND  b.status = 'accepted' AND b.deleted = 0 AND b.revoked = 0
-  left join social_event as se on bc.id = se.object and se.verb = 'message'
-  JOIN issuer_content AS ic ON b.issuer_content_id = ic.id 
-  group by bc.id;")
-
-(defn get-badge-adverts [ctx country tags badge-name issuer-name order id user-id show-followed-only recipient-name]
-  (let [;where-params (badge-adverts-where-params country badge-name issuer-name id recipient-name)
-        ;user-id (or user-id "NULL") ;set null if nil
-       ; where   (str (apply str (keys where-params))  (if show-followed-only  " AND scba.user_id IS NOT NULL ")) ;add IS NOT NULL when user want only followed adverts
-        params [] ;(cons user-id (vec (vals where-params)))  ;add user-id to params 
+
+
+
+(def hakujenhaku
+  "select distinct  bc.name, ic.name AS issuer_content_name, count(distinct b.id) as owners, MAX(b.ctime) AS ctime
+FROM badge_content as bc 
+INNER JOIN badge as b on bc.id = b.badge_content_id AND  b.status = 'accepted' AND b.deleted = 0 AND b.revoked = 0 AND (b.expires_on IS NULL OR b.expires_on > UNIX_TIMESTAMP())
+INNER JOIN issuer_content AS ic ON b.issuer_content_id = ic.id 
+group by bc.id
+ORDER BY owners desc")
+
+(defn get-badge-adverts [ctx country tags badge-name issuer-name order id recipient-name]
+  (let [where-params (badge-adverts-where-params country badge-name issuer-name recipient-name)
+        where  (apply str (keys where-params))
+        params  (vec (vals where-params))  ;add user-id to params 
         ;tags (vec (vals tags))
         order (cond
                 ;(= order "mtime") "ORDER BY ba.mtime DESC"
                 (= order "name") "ORDER BY bc.name"
                 (= order "issuer_content_name") "ORDER BY ic.name"
+                (= order "owners") "ORDER BY owners DESC"
                 :else "ORDER BY ctime DESC") 
-        query (str "SELECT bc.id, bc.name, bc.image_file, bc.description, ic.name AS issuer_content_name, ic.url AS issuer_content_url, MAX(b.ctime) AS ctime, badge_content_id  FROM badge AS b
-                    JOIN badge_content AS bc ON b.badge_content_id = bc.id
-                    JOIN issuer_content AS ic ON b.issuer_content_id = ic.id
-                    LEFT JOIN user AS u ON b.user_id = u.id
-                    WHERE b.status = 'accepted' AND b.deleted = 0 AND b.revoked = 0 AND (b.expires_on IS NULL OR b.expires_on > UNIX_TIMESTAMP()) "  
-
-                  ; where
+        query (str
+               "select bc.id, bc.name, ic.name AS issuer_content_name, count(distinct b.id) as owners, MAX(b.ctime) AS ctime
+FROM badge_content as bc 
+INNER JOIN badge as b on bc.id = b.badge_content_id AND  b.status = 'accepted' AND b.deleted = 0 AND b.revoked = 0 AND (b.expires_on IS NULL OR b.expires_on > UNIX_TIMESTAMP())
+INNER JOIN issuer_content AS ic ON b.issuer_content_id = ic.id
+INNER JOIN user as u on b.user_id =  u.id
+WHERE bc.id IN
+	(SELECT DISTINCT badge_content_id FROM badge WHERE visibility != 'private' AND  status = 'accepted' AND deleted = 0 AND revoked = 0 AND (expires_on IS NULL OR expires_on > UNIX_TIMESTAMP())) 
+ "
+
+                 
+
+                  where
                    
-                   "GROUP BY bc.id, bc.name, bc.image_file, bc.description, ic.name, ic.url, b.badge_content_id "
+                   "GROUP BY bc.id "
                    order
                    )
         search (jdbc/with-db-connection
                  [conn (:connection (get-db ctx))]
                  (jdbc/query conn (into [query] params)))]
+    (dump where)
     search
     #_(if (not-empty tags)
       (filter-tags search tags)
       search)))
 
-(dump )
-(count (get-badge-adverts ctx nil nil nil nil nil nil nil nil nil))
+
+(time  (count (get-badge-adverts ctx nil nil nil nil "owners" nil nil) ))
+
 (:name (first (get-badge-adverts ctx nil nil nil nil "mtime" nil nil nil nil)))
 
 (defn public-badges-by-user

(defn meta-tags [ctx badge-content-id]
  (let [badge-content (select-common-badge-content {:id badge-content-id} (into {:result-set-fn first} (get-db ctx)))]
    (rename-keys badge-content {:image_file :image :name :title})))

        assochelper (fn [user recipients] (assoc user  :recipients (get recipientsmap (:badge_content_id user))))]
    (map assochelper badgesearch recipients)))

(dump )
(time (count (public-badges ctx nil nil nil nil)))
(defn user-country
  "Return user's country id"
  [ctx user-id]
