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, onts 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, &onts)
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	if len(onts) > 0 {
197		h.Onts = strings.Split(onts, " ")
198	}
199	return h
200}
201
202func donksforhonks(honks []*Honk) {
203	db := opendatabase()
204	var ids []string
205	hmap := make(map[int64]*Honk)
206	for _, h := range honks {
207		ids = append(ids, fmt.Sprintf("%d", h.ID))
208		hmap[h.ID] = h
209	}
210	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, ","))
211	rows, err := db.Query(q)
212	if err != nil {
213		log.Printf("error querying donks: %s", err)
214		return
215	}
216	defer rows.Close()
217	for rows.Next() {
218		var hid int64
219		var d Donk
220		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
221		if err != nil {
222			log.Printf("error scanning donk: %s", err)
223			continue
224		}
225		h := hmap[hid]
226		h.Donks = append(h.Donks, &d)
227	}
228}
229
230func savehonk(h *Honk) error {
231	dt := h.Date.UTC().Format(dbtimeformat)
232	aud := strings.Join(h.Audience, " ")
233
234	res, err := stmtSaveHonk.Exec(h.UserID, h.What, h.Honker, h.XID, h.RID, dt, h.URL,
235		aud, h.Noise, h.Convoy, h.Whofore, h.Format, h.Precis,
236		h.Oonker, h.Flags, strings.Join(h.Onts, " "))
237	if err != nil {
238		log.Printf("err saving honk: %s", err)
239		return err
240	}
241	h.ID, _ = res.LastInsertId()
242	err = saveextras(h)
243	return err
244}
245
246func saveextras(h *Honk) error {
247	for _, d := range h.Donks {
248		_, err := stmtSaveDonk.Exec(h.ID, d.FileID)
249		if err != nil {
250			log.Printf("err saving donk: %s", err)
251			return err
252		}
253	}
254	for _, o := range h.Onts {
255		_, err := stmtSaveOnt.Exec(strings.ToLower(o), h.ID)
256		if err != nil {
257			log.Printf("error saving ont: %s", err)
258			return err
259		}
260	}
261	return nil
262}
263
264func deleteextras(honkid int64) {
265	_, err := stmtDeleteDonks.Exec(honkid)
266	if err != nil {
267		log.Printf("error deleting: %s", err)
268	}
269	_, err = stmtDeleteOnts.Exec(honkid)
270	if err != nil {
271		log.Printf("error deleting: %s", err)
272	}
273}
274
275func deletehonk(honkid int64) {
276	deleteextras(honkid)
277	_, err := stmtDeleteHonk.Exec(honkid)
278	if err != nil {
279		log.Printf("error deleting: %s", err)
280	}
281}
282
283func updatehonk(h *Honk) {
284	old := getxonk(h.UserID, h.XID)
285	_, err := stmtSaveOld.Exec(old.ID, old.Precis, old.Noise)
286	if err != nil {
287		log.Printf("error saving old: %s", err)
288		return
289	}
290	deleteextras(h.ID)
291
292	dt := h.Date.UTC().Format(dbtimeformat)
293	stmtUpdateHonk.Exec(h.Precis, h.Noise, h.Format, dt, h.ID)
294
295	saveextras(h)
296}
297
298func cleanupdb(arg string) {
299	db := opendatabase()
300	days, err := strconv.Atoi(arg)
301	if err != nil {
302		honker := arg
303		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
304		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
305		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
306	} else {
307		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
308		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)
309		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)
310	}
311	doordie(db, "delete from files where fileid not in (select fileid from donks)")
312	for _, u := range allusers() {
313		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)
314	}
315}
316
317var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
318var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
319var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
320var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
321var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
322var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
323var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
324var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
325var stmtSelectOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
326var stmtSaveOld, stmtUpdateHonk *sql.Stmt
327
328func preparetodie(db *sql.DB, s string) *sql.Stmt {
329	stmt, err := db.Prepare(s)
330	if err != nil {
331		log.Fatalf("error %s: %s", err, s)
332	}
333	return stmt
334}
335
336func prepareStatements(db *sql.DB) {
337	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")
338	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
339	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
340	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
341	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
342	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
343
344	selecthonks := "select honks.honkid, honks.userid, username, what, honker, oonker, honks.xid, rid, dt, url, audience, noise, precis, format, convoy, whofore, flags, onts from honks join users on honks.userid = users.userid "
345	limit := " order by honks.honkid desc limit 250"
346	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
347	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
348	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
349	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
350	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
351	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)
352	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
353	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
354	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
355	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
356	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
357	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
358	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
359
360	stmtSaveOld = preparetodie(db, "insert into forsaken (honkid, precis, noise) values (?, ?, ?)")
361	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, onts) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
362	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
363	stmtUpdateHonk = preparetodie(db, "update honks set precis = ?, noise = ?, format = ?, dt = ? where honkid = ?")
364	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
365	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
366	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
367	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
368	stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
369	stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
370	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
371	stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
372	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
373	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
374	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
375	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
376	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
377	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
378	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
379	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
380	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
381	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
382	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
383	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
384	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
385	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")
386	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
387	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
388	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
389}