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