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.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	for _, d := range h.Donks {
243		_, err = stmtSaveDonk.Exec(h.ID, d.FileID)
244		if err != nil {
245			log.Printf("err saving donk: %s", err)
246			return err
247		}
248	}
249	for _, o := range h.Onts {
250		_, err = stmtSaveOnt.Exec(strings.ToLower(o), h.ID)
251		if err != nil {
252			log.Printf("error saving ont: %s", err)
253			return err
254		}
255	}
256	return nil
257}
258
259func deletehonk(honkid int64) {
260	_, err := stmtDeleteDonks.Exec(honkid)
261	if err != nil {
262		log.Printf("error deleting: %s", err)
263	}
264	_, err = stmtDeleteOnts.Exec(honkid)
265	if err != nil {
266		log.Printf("error deleting: %s", err)
267	}
268	_, err = stmtDeleteHonk.Exec(honkid)
269	if err != nil {
270		log.Printf("error deleting: %s", err)
271	}
272}
273
274func cleanupdb(arg string) {
275	db := opendatabase()
276	days, err := strconv.Atoi(arg)
277	if err != nil {
278		honker := arg
279		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
280		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
281		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
282	} else {
283		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
284		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)
285		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)
286	}
287	doordie(db, "delete from files where fileid not in (select fileid from donks)")
288	for _, u := range allusers() {
289		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)
290	}
291}
292
293var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
294var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
295var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
296var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
297var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
298var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
299var stmtThumbBiters, stmtDeleteHonk, stmtDeleteDonks, stmtDeleteOnts, stmtSaveZonker *sql.Stmt
300var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
301var stmtSelectOnts, stmtSaveOnt, stmtUpdateFlags, stmtClearFlags *sql.Stmt
302
303func preparetodie(db *sql.DB, s string) *sql.Stmt {
304	stmt, err := db.Prepare(s)
305	if err != nil {
306		log.Fatalf("error %s: %s", err, s)
307	}
308	return stmt
309}
310
311func prepareStatements(db *sql.DB) {
312	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")
313	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
314	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
315	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
316	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
317	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
318
319	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 "
320	limit := " order by honks.honkid desc limit 250"
321	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
322	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
323	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
324	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
325	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
326	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)
327	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
328	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
329	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
330	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
331	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
332	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
333	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
334
335	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, onts) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
336	stmtDeleteHonk = preparetodie(db, "delete from honks where honkid = ?")
337	stmtSaveOnt = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
338	stmtDeleteOnts = preparetodie(db, "delete from onts where honkid = ?")
339	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
340	stmtDeleteDonks = preparetodie(db, "delete from donks where honkid = ?")
341	stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
342	stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
343	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
344	stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
345	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
346	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
347	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
348	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
349	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
350	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
351	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
352	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
353	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
354	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
355	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
356	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
357	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
358	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")
359	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
360	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
361	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
362}