1//
  2// Copyright (c) 2019 Ted Unangst <tedu@tedunangst.com>
  3//
  4// Permission to use, copy, modify, and distribute this software for any
  5// purpose with or without fee is hereby granted, provided that the above
  6// copyright notice and this permission notice appear in all copies.
  7//
  8// THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
  9// WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
 10// MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
 11// ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
 12// WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
 13// ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
 14// OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
 15
 16package main
 17
 18import (
 19	"database/sql"
 20	"fmt"
 21	"log"
 22	"strconv"
 23	"strings"
 24	"time"
 25
 26	"humungus.tedunangst.com/r/webs/login"
 27)
 28
 29func butwhatabout(name string) (*WhatAbout, error) {
 30	row := stmtWhatAbout.QueryRow(name)
 31	var user WhatAbout
 32	var options string
 33	err := row.Scan(&user.ID, &user.Name, &user.Display, &user.About, &user.Key, &options)
 34	user.URL = fmt.Sprintf("https://%s/%s/%s", serverName, userSep, user.Name)
 35	user.SkinnyCSS = strings.Contains(options, " skinny ")
 36	return &user, err
 37}
 38
 39func gethonkers(userid int64) []*Honker {
 40	rows, err := stmtHonkers.Query(userid)
 41	if err != nil {
 42		log.Printf("error querying honkers: %s", err)
 43		return nil
 44	}
 45	defer rows.Close()
 46	var honkers []*Honker
 47	for rows.Next() {
 48		var f Honker
 49		var combos string
 50		err = rows.Scan(&f.ID, &f.UserID, &f.Name, &f.XID, &f.Flavor, &combos)
 51		f.Combos = strings.Split(strings.TrimSpace(combos), " ")
 52		if err != nil {
 53			log.Printf("error scanning honker: %s", err)
 54			return nil
 55		}
 56		honkers = append(honkers, &f)
 57	}
 58	return honkers
 59}
 60
 61func getdubs(userid int64) []*Honker {
 62	rows, err := stmtDubbers.Query(userid)
 63	if err != nil {
 64		log.Printf("error querying dubs: %s", err)
 65		return nil
 66	}
 67	defer rows.Close()
 68	var honkers []*Honker
 69	for rows.Next() {
 70		var f Honker
 71		err = rows.Scan(&f.ID, &f.UserID, &f.Name, &f.XID, &f.Flavor)
 72		if err != nil {
 73			log.Printf("error scanning honker: %s", err)
 74			return nil
 75		}
 76		honkers = append(honkers, &f)
 77	}
 78	return honkers
 79}
 80
 81func allusers() []login.UserInfo {
 82	var users []login.UserInfo
 83	rows, _ := opendatabase().Query("select userid, username from users")
 84	defer rows.Close()
 85	for rows.Next() {
 86		var u login.UserInfo
 87		rows.Scan(&u.UserID, &u.Username)
 88		users = append(users, u)
 89	}
 90	return users
 91}
 92
 93func scanxonk(row *sql.Row) *Honk {
 94	h := new(Honk)
 95	var dt, aud, onts string
 96
 97	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
 98		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
 99	if err != nil {
100		if err != sql.ErrNoRows {
101			log.Printf("error scanning xonk: %s", err)
102		}
103		return nil
104	}
105	h.Date, _ = time.Parse(dbtimeformat, dt)
106	h.Audience = strings.Split(aud, " ")
107	h.Public = !keepitquiet(h.Audience)
108	if len(onts) > 0 {
109		h.Onts = strings.Split(onts, " ")
110	}
111	return h
112}
113
114func getxonk(userid int64, xid string) *Honk {
115	row := stmtOneXonk.QueryRow(userid, xid)
116	return scanxonk(row)
117}
118
119func getbonk(userid int64, xid string) *Honk {
120	row := stmtOneBonk.QueryRow(userid, xid)
121	return scanxonk(row)
122}
123
124func getpublichonks() []*Honk {
125	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
126	rows, err := stmtPublicHonks.Query(dt)
127	return getsomehonks(rows, err)
128}
129func gethonksbyuser(name string, includeprivate bool) []*Honk {
130	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
131	whofore := 2
132	if includeprivate {
133		whofore = 3
134	}
135	rows, err := stmtUserHonks.Query(whofore, name, dt)
136	return getsomehonks(rows, err)
137}
138func gethonksforuser(userid int64) []*Honk {
139	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
140	rows, err := stmtHonksForUser.Query(userid, dt, userid, userid)
141	return getsomehonks(rows, err)
142}
143func gethonksforme(userid int64) []*Honk {
144	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
145	rows, err := stmtHonksForMe.Query(userid, dt, userid)
146	return getsomehonks(rows, err)
147}
148func gethonksbyhonker(userid int64, honker string) []*Honk {
149	rows, err := stmtHonksByHonker.Query(userid, honker, userid)
150	return getsomehonks(rows, err)
151}
152func gethonksbyxonker(userid int64, xonker string) []*Honk {
153	rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
154	return getsomehonks(rows, err)
155}
156func gethonksbycombo(userid int64, combo string) []*Honk {
157	combo = "% " + combo + " %"
158	rows, err := stmtHonksByCombo.Query(userid, combo, userid)
159	return getsomehonks(rows, err)
160}
161func gethonksbyconvoy(userid int64, convoy string) []*Honk {
162	rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
163	honks := getsomehonks(rows, err)
164	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
165		honks[i], honks[j] = honks[j], honks[i]
166	}
167	return honks
168}
169func gethonksbysearch(userid int64, q string) []*Honk {
170	q = "%" + q + "%"
171	rows, err := stmtHonksBySearch.Query(userid, q)
172	honks := getsomehonks(rows, err)
173	return honks
174}
175func gethonksbyontology(userid int64, name string) []*Honk {
176	rows, err := stmtHonksByOntology.Query(name, userid, userid)
177	honks := getsomehonks(rows, err)
178	return honks
179}
180
181func getsomehonks(rows *sql.Rows, err error) []*Honk {
182	if err != nil {
183		log.Printf("error querying honks: %s", err)
184		return nil
185	}
186	defer rows.Close()
187	var honks []*Honk
188	for rows.Next() {
189		var h Honk
190		var dt, aud, onts string
191		err = rows.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID,
192			&h.RID, &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
193		if err != nil {
194			log.Printf("error scanning honks: %s", err)
195			return nil
196		}
197		h.Date, _ = time.Parse(dbtimeformat, dt)
198		h.Audience = strings.Split(aud, " ")
199		h.Public = !keepitquiet(h.Audience)
200		if len(onts) > 0 {
201			h.Onts = strings.Split(onts, " ")
202		}
203		honks = append(honks, &h)
204	}
205	rows.Close()
206	donksforhonks(honks)
207	return honks
208}
209
210func donksforhonks(honks []*Honk) {
211	db := opendatabase()
212	var ids []string
213	hmap := make(map[int64]*Honk)
214	for _, h := range honks {
215		ids = append(ids, fmt.Sprintf("%d", h.ID))
216		hmap[h.ID] = h
217	}
218	q := fmt.Sprintf("select honkid, donks.fileid, xid, name, description, url, media, local from donks join files on donks.fileid = files.fileid where honkid in (%s)", strings.Join(ids, ","))
219	rows, err := db.Query(q)
220	if err != nil {
221		log.Printf("error querying donks: %s", err)
222		return
223	}
224	defer rows.Close()
225	for rows.Next() {
226		var hid int64
227		var d Donk
228		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
229		if err != nil {
230			log.Printf("error scanning donk: %s", err)
231			continue
232		}
233		h := hmap[hid]
234		h.Donks = append(h.Donks, &d)
235	}
236}
237
238func savehonk(h *Honk) error {
239	dt := h.Date.UTC().Format(dbtimeformat)
240	aud := strings.Join(h.Audience, " ")
241
242	res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
243		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
244		h.Oonker, h.Flags, strings.Join(h.Onts, " "))
245	if err != nil {
246		log.Printf("err saving honk: %s", err)
247		return err
248	}
249	h.ID, _ = res.LastInsertId()
250	for _, d := range h.Donks {
251		_, err = stmtSaveDonk.Exec(h.ID, d.FileID)
252		if err != nil {
253			log.Printf("err saving donk: %s", err)
254			return err
255		}
256	}
257	for _, o := range h.Onts {
258		_, err = stmtSaveOnts.Exec(strings.ToLower(o), h.ID)
259		if err != nil {
260			log.Printf("error saving ont: %s", err)
261			return err
262		}
263	}
264	return nil
265}
266
267func cleanupdb(arg string) {
268	db := opendatabase()
269	days, err := strconv.Atoi(arg)
270	if err != nil {
271		honker := arg
272		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
273		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
274		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
275	} else {
276		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
277		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and convoy not in (select convoy from honks where whofore = 2 or whofore = 3))", expdate)
278		doordie(db, "delete from honks where dt < ? and whofore = 0 and convoy not in (select convoy from honks where whofore = 2 or whofore = 3)", expdate)
279	}
280	doordie(db, "delete from files where fileid not in (select fileid from donks)")
281	for _, u := range allusers() {
282		doordie(db, "delete from zonkers where userid = ? and wherefore = 'zonvoy' and zonkerid < (select zonkerid from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 1 offset 200)", u.UserID, u.UserID)
283	}
284}
285
286var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
287var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
288var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
289var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
290var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
291var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
292var stmtThumbBiters, stmtZonkIt, stmtZonkDonks, stmtSaveZonker *sql.Stmt
293var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
294var stmtSelectOnts, stmtSaveOnts, stmtUpdateFlags, stmtClearFlags *sql.Stmt
295
296func preparetodie(db *sql.DB, s string) *sql.Stmt {
297	stmt, err := db.Prepare(s)
298	if err != nil {
299		log.Fatalf("error %s: %s", err, s)
300	}
301	return stmt
302}
303
304func prepareStatements(db *sql.DB) {
305	stmtHonkers = preparetodie(db, "select honkerid, userid, name, xid, flavor, combos from honkers where userid = ? and (flavor = 'sub' or flavor = 'peep' or flavor = 'unsub') order by name")
306	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
307	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
308	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
309	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
310	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
311
312	selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, convoy, whofore, flags, onts from honks join users on honks.userid = users.userid "
313	limit := " order by honks.honkid desc limit 250"
314	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
315	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
316	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
317	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
318	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
319	stmtHonksForUser = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and honker in (select xid from honkers where userid = ? and flavor = 'sub' and combos not like '% - %')"+butnotthose+limit)
320	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
321	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
322	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
323	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
324	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
325	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
326	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
327
328	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, onts) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
329	stmtSaveOnts = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
330	stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
331	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
332	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
333	stmtZonkIt = preparetodie(db, "delete from honks where honkid = ?")
334	stmtZonkDonks = preparetodie(db, "delete from donks where honkid = ?")
335	stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
336	stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
337	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
338	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
339	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
340	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
341	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
342	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
343	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
344	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
345	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
346	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
347	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
348	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
349	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
350	stmtRecentHonkers = preparetodie(db, "select distinct(honker) from honks where userid = ? and honker not in (select xid from honkers where userid = ? and flavor = 'sub') order by honkid desc limit 100")
351	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
352	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
353	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
354}