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 getxonk(userid int64, xid string) *Honk {
 94	row := stmtOneXonk.QueryRow(userid, xid)
 95	return scanhonk(row)
 96}
 97
 98func getbonk(userid int64, xid string) *Honk {
 99	row := stmtOneBonk.QueryRow(userid, xid)
100	return scanhonk(row)
101}
102
103func getpublichonks() []*Honk {
104	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
105	rows, err := stmtPublicHonks.Query(dt)
106	return getsomehonks(rows, err)
107}
108func gethonksbyuser(name string, includeprivate bool) []*Honk {
109	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
110	whofore := 2
111	if includeprivate {
112		whofore = 3
113	}
114	rows, err := stmtUserHonks.Query(whofore, name, dt)
115	return getsomehonks(rows, err)
116}
117func gethonksforuser(userid int64) []*Honk {
118	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
119	rows, err := stmtHonksForUser.Query(userid, dt, userid, userid)
120	return getsomehonks(rows, err)
121}
122func gethonksforme(userid int64) []*Honk {
123	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
124	rows, err := stmtHonksForMe.Query(userid, dt, userid)
125	return getsomehonks(rows, err)
126}
127func gethonksbyhonker(userid int64, honker string) []*Honk {
128	rows, err := stmtHonksByHonker.Query(userid, honker, userid)
129	return getsomehonks(rows, err)
130}
131func gethonksbyxonker(userid int64, xonker string) []*Honk {
132	rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
133	return getsomehonks(rows, err)
134}
135func gethonksbycombo(userid int64, combo string) []*Honk {
136	combo = "% " + combo + " %"
137	rows, err := stmtHonksByCombo.Query(userid, combo, userid)
138	return getsomehonks(rows, err)
139}
140func gethonksbyconvoy(userid int64, convoy string) []*Honk {
141	rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
142	honks := getsomehonks(rows, err)
143	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
144		honks[i], honks[j] = honks[j], honks[i]
145	}
146	return honks
147}
148func gethonksbysearch(userid int64, q string) []*Honk {
149	q = "%" + q + "%"
150	rows, err := stmtHonksBySearch.Query(userid, q)
151	honks := getsomehonks(rows, err)
152	return honks
153}
154func gethonksbyontology(userid int64, name string) []*Honk {
155	rows, err := stmtHonksByOntology.Query(name, userid, userid)
156	honks := getsomehonks(rows, err)
157	return honks
158}
159
160func getsomehonks(rows *sql.Rows, err error) []*Honk {
161	if err != nil {
162		log.Printf("error querying honks: %s", err)
163		return nil
164	}
165	defer rows.Close()
166	var honks []*Honk
167	for rows.Next() {
168		h := scanhonk(rows)
169		if h != nil {
170			honks = append(honks, h)
171		}
172	}
173	rows.Close()
174	donksforhonks(honks)
175	return honks
176}
177
178type RowLike interface {
179	Scan(dest ...interface{}) error
180}
181
182func scanhonk(row RowLike) *Honk {
183	h := new(Honk)
184	var dt, aud string
185	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
186		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Format, &h.Convoy, &h.Whofore, &h.Flags)
187	if err != nil {
188		if err != sql.ErrNoRows {
189			log.Printf("error scanning honk: %s", err)
190		}
191		return nil
192	}
193	h.Date, _ = time.Parse(dbtimeformat, dt)
194	h.Audience = strings.Split(aud, " ")
195	h.Public = !keepitquiet(h.Audience)
196	return h
197}
198
199func donksforhonks(honks []*Honk) {
200	db := opendatabase()
201	var ids []string
202	hmap := make(map[int64]*Honk)
203	for _, h := range honks {
204		ids = append(ids, fmt.Sprintf("%d", h.ID))
205		hmap[h.ID] = h
206	}
207	// grab donks
208	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, ","))
209	rows, err := db.Query(q)
210	if err != nil {
211		log.Printf("error querying donks: %s", err)
212		return
213	}
214	defer rows.Close()
215	for rows.Next() {
216		var hid int64
217		var d Donk
218		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
219		if err != nil {
220			log.Printf("error scanning donk: %s", err)
221			continue
222		}
223		h := hmap[hid]
224		h.Donks = append(h.Donks, &d)
225	}
226	rows.Close()
227
228	// grab onts
229	q = fmt.Sprintf("select honkid, ontology from onts where honkid in (%s)", strings.Join(ids, ","))
230	rows, err = db.Query(q)
231	if err != nil {
232		log.Printf("error querying onts: %s", err)
233		return
234	}
235	defer rows.Close()
236	for rows.Next() {
237		var hid int64
238		var o string
239		err = rows.Scan(&hid, &o)
240		if err != nil {
241			log.Printf("error scanning donk: %s", err)
242			continue
243		}
244		h := hmap[hid]
245		h.Onts = append(h.Onts, o)
246	}
247	rows.Close()
248}
249
250func savehonk(h *Honk) error {
251	dt := h.Date.UTC().Format(dbtimeformat)
252	aud := strings.Join(h.Audience, " ")
253
254	res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
255		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
256		h.Oonker, h.Flags)
257	if err != nil {
258		log.Printf("err saving honk: %s", err)
259		return err
260	}
261	h.ID, _ = res.LastInsertId()
262	err = saveextras(h)
263	return err
264}
265
266func saveextras(h *Honk) error {
267	for _, d := range h.Donks {
268		_, err := stmtSaveDonk.Exec(h.ID, d.FileID)
269		if err != nil {
270			log.Printf("err saving donk: %s", err)
271			return err
272		}
273	}
274	for _, o := range h.Onts {
275		_, err := stmtSaveOnt.Exec(strings.ToLower(o), h.ID)
276		if err != nil {
277			log.Printf("error saving ont: %s", err)
278			return err
279		}
280	}
281	return nil
282}
283
284func deleteextras(honkid int64) {
285	_, err := stmtDeleteDonks.Exec(honkid)
286	if err != nil {
287		log.Printf("error deleting: %s", err)
288	}
289	_, err = stmtDeleteOnts.Exec(honkid)
290	if err != nil {
291		log.Printf("error deleting: %s", err)
292	}
293}
294
295func deletehonk(honkid int64) {
296	deleteextras(honkid)
297	_, err := stmtDeleteHonk.Exec(honkid)
298	if err != nil {
299		log.Printf("error deleting: %s", err)
300	}
301}
302
303func updatehonk(h *Honk) {
304	old := getxonk(h.UserID, h.XID)
305	_, err := stmtSaveOld.Exec(old.ID, old.Precis, old.Noise)
306	if err != nil {
307		log.Printf("error saving old: %s", err)
308		return
309	}
310	deleteextras(h.ID)
311
312	dt := h.Date.UTC().Format(dbtimeformat)
313	stmtUpdateHonk.Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
314
315	saveextras(h)
316}
317
318func cleanupdb(arg string) {
319	db := opendatabase()
320	days, err := strconv.Atoi(arg)
321	if err != nil {
322		honker := arg
323		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
324		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
325		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
326	} else {
327		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
328		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)
329		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)
330	}
331	doordie(db, "delete from files where fileid not in (select fileid from donks)")
332	for _, u := range allusers() {
333		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)
334	}
335}
336
337var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
338var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
339var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
340var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
341var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
342var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
343var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
344var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
345var stmtSelectOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
346var stmtSaveOld, stmtUpdateHonk *sql.Stmt
347
348func preparetodie(db *sql.DB, s string) *sql.Stmt {
349	stmt, err := db.Prepare(s)
350	if err != nil {
351		log.Fatalf("error %s: %s", err, s)
352	}
353	return stmt
354}
355
356func prepareStatements(db *sql.DB) {
357	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")
358	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
359	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
360	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
361	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
362	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
363
364	selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, format, convoy, whofore, flags from honks join users on honks.userid = users.userid "
365	limit := " order by honks.honkid desc limit 250"
366	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
367	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
368	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
369	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
370	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
371	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)
372	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
373	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
374	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
375	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
376	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
377	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
378	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
379
380	stmtSaveOld = preparetodie(db, "insert into forsaken (honkid, precis, noise) values (?, ?, ?)")
381	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
382	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
383	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
384	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
385	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
386	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
387	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
388	stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
389	stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
390	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
391	stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
392	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
393	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
394	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
395	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
396	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
397	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
398	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
399	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
400	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
401	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
402	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
403	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
404	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
405	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")
406	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
407	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
408	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
409}