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	h := new(Honk)
 95	var dt, aud, onts string
 96	row := stmtOneXonk.QueryRow(userid, xid)
 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 getbonk(userid int64, xid string) *Honk {
115	h := new(Honk)
116	var dt, aud, onts string
117	row := stmtOneBonk.QueryRow(userid, xid)
118	err := row.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID, &h.RID,
119		&dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
120	if err != nil {
121		if err != sql.ErrNoRows {
122			log.Printf("error scanning xonk: %s", err)
123		}
124		return nil
125	}
126	h.Date, _ = time.Parse(dbtimeformat, dt)
127	h.Audience = strings.Split(aud, " ")
128	h.Public = !keepitquiet(h.Audience)
129	if len(onts) > 0 {
130		h.Onts = strings.Split(onts, " ")
131	}
132	return h
133}
134
135func getpublichonks() []*Honk {
136	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
137	rows, err := stmtPublicHonks.Query(dt)
138	return getsomehonks(rows, err)
139}
140func gethonksbyuser(name string, includeprivate bool) []*Honk {
141	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
142	whofore := 2
143	if includeprivate {
144		whofore = 3
145	}
146	rows, err := stmtUserHonks.Query(whofore, name, dt)
147	return getsomehonks(rows, err)
148}
149func gethonksforuser(userid int64) []*Honk {
150	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
151	rows, err := stmtHonksForUser.Query(userid, dt, userid, userid)
152	return getsomehonks(rows, err)
153}
154func gethonksforme(userid int64) []*Honk {
155	dt := time.Now().UTC().Add(-7 * 24 * time.Hour).Format(dbtimeformat)
156	rows, err := stmtHonksForMe.Query(userid, dt, userid)
157	return getsomehonks(rows, err)
158}
159func gethonksbyhonker(userid int64, honker string) []*Honk {
160	rows, err := stmtHonksByHonker.Query(userid, honker, userid)
161	return getsomehonks(rows, err)
162}
163func gethonksbyxonker(userid int64, xonker string) []*Honk {
164	rows, err := stmtHonksByXonker.Query(userid, xonker, xonker, userid)
165	return getsomehonks(rows, err)
166}
167func gethonksbycombo(userid int64, combo string) []*Honk {
168	combo = "% " + combo + " %"
169	rows, err := stmtHonksByCombo.Query(userid, combo, userid)
170	return getsomehonks(rows, err)
171}
172func gethonksbyconvoy(userid int64, convoy string) []*Honk {
173	rows, err := stmtHonksByConvoy.Query(userid, userid, convoy)
174	honks := getsomehonks(rows, err)
175	for i, j := 0, len(honks)-1; i < j; i, j = i+1, j-1 {
176		honks[i], honks[j] = honks[j], honks[i]
177	}
178	return honks
179}
180func gethonksbysearch(userid int64, q string) []*Honk {
181	q = "%" + q + "%"
182	rows, err := stmtHonksBySearch.Query(userid, q)
183	honks := getsomehonks(rows, err)
184	return honks
185}
186func gethonksbyontology(userid int64, name string) []*Honk {
187	rows, err := stmtHonksByOntology.Query(name, userid, userid)
188	honks := getsomehonks(rows, err)
189	return honks
190}
191
192func getsomehonks(rows *sql.Rows, err error) []*Honk {
193	if err != nil {
194		log.Printf("error querying honks: %s", err)
195		return nil
196	}
197	defer rows.Close()
198	var honks []*Honk
199	for rows.Next() {
200		var h Honk
201		var dt, aud, onts string
202		err = rows.Scan(&h.ID, &h.UserID, &h.Username, &h.What, &h.Honker, &h.Oonker, &h.XID,
203			&h.RID, &dt, &h.URL, &aud, &h.Noise, &h.Precis, &h.Convoy, &h.Whofore, &h.Flags, &onts)
204		if err != nil {
205			log.Printf("error scanning honks: %s", err)
206			return nil
207		}
208		h.Date, _ = time.Parse(dbtimeformat, dt)
209		h.Audience = strings.Split(aud, " ")
210		h.Public = !keepitquiet(h.Audience)
211		if len(onts) > 0 {
212			h.Onts = strings.Split(onts, " ")
213		}
214		honks = append(honks, &h)
215	}
216	rows.Close()
217	donksforhonks(honks)
218	return honks
219}
220
221func donksforhonks(honks []*Honk) {
222	db := opendatabase()
223	var ids []string
224	hmap := make(map[int64]*Honk)
225	for _, h := range honks {
226		ids = append(ids, fmt.Sprintf("%d", h.ID))
227		hmap[h.ID] = h
228	}
229	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, ","))
230	rows, err := db.Query(q)
231	if err != nil {
232		log.Printf("error querying donks: %s", err)
233		return
234	}
235	defer rows.Close()
236	for rows.Next() {
237		var hid int64
238		var d Donk
239		err = rows.Scan(&hid, &d.FileID, &d.XID, &d.Name, &d.Desc, &d.URL, &d.Media, &d.Local)
240		if err != nil {
241			log.Printf("error scanning donk: %s", err)
242			continue
243		}
244		h := hmap[hid]
245		h.Donks = append(h.Donks, &d)
246	}
247}
248
249func cleanupdb(arg string) {
250	db := opendatabase()
251	days, err := strconv.Atoi(arg)
252	if err != nil {
253		honker := arg
254		expdate := time.Now().UTC().Add(-3 * 24 * time.Hour).Format(dbtimeformat)
255		doordie(db, "delete from donks where honkid in (select honkid from honks where dt < ? and whofore = 0 and honker = ?)", expdate, honker)
256		doordie(db, "delete from honks where dt < ? and whofore = 0 and honker = ?", expdate, honker)
257	} else {
258		expdate := time.Now().UTC().Add(-time.Duration(days) * 24 * time.Hour).Format(dbtimeformat)
259		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)
260		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)
261	}
262	doordie(db, "delete from files where fileid not in (select fileid from donks)")
263	for _, u := range allusers() {
264		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)
265	}
266}
267
268var stmtHonkers, stmtDubbers, stmtSaveHonker, stmtUpdateFlavor, stmtUpdateCombos *sql.Stmt
269var stmtOneXonk, stmtPublicHonks, stmtUserHonks, stmtHonksByCombo, stmtHonksByConvoy *sql.Stmt
270var stmtHonksByOntology, stmtHonksForUser, stmtHonksForMe, stmtSaveDub, stmtHonksByXonker *sql.Stmt
271var stmtHonksBySearch, stmtHonksByHonker, stmtSaveHonk, stmtFileData, stmtWhatAbout *sql.Stmt
272var stmtOneBonk, stmtFindZonk, stmtFindXonk, stmtSaveDonk, stmtFindFile, stmtSaveFile *sql.Stmt
273var stmtAddDoover, stmtGetDoovers, stmtLoadDoover, stmtZapDoover, stmtOneHonker *sql.Stmt
274var stmtThumbBiters, stmtZonkIt, stmtZonkDonks, stmtSaveZonker *sql.Stmt
275var stmtGetZonkers, stmtRecentHonkers, stmtGetXonker, stmtSaveXonker, stmtDeleteXonker *sql.Stmt
276var stmtSelectOnts, stmtSaveOnts, stmtUpdateFlags, stmtClearFlags *sql.Stmt
277
278func preparetodie(db *sql.DB, s string) *sql.Stmt {
279	stmt, err := db.Prepare(s)
280	if err != nil {
281		log.Fatalf("error %s: %s", err, s)
282	}
283	return stmt
284}
285
286func prepareStatements(db *sql.DB) {
287	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")
288	stmtSaveHonker = preparetodie(db, "insert into honkers (userid, name, xid, flavor, combos) values (?, ?, ?, ?, ?)")
289	stmtUpdateFlavor = preparetodie(db, "update honkers set flavor = ? where userid = ? and xid = ? and flavor = ?")
290	stmtUpdateCombos = preparetodie(db, "update honkers set combos = ? where honkerid = ? and userid = ?")
291	stmtOneHonker = preparetodie(db, "select xid from honkers where name = ? and userid = ?")
292	stmtDubbers = preparetodie(db, "select honkerid, userid, name, xid, flavor from honkers where userid = ? and flavor = 'dub'")
293
294	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 "
295	limit := " order by honks.honkid desc limit 250"
296	butnotthose := " and convoy not in (select name from zonkers where userid = ? and wherefore = 'zonvoy' order by zonkerid desc limit 100)"
297	stmtOneXonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ?")
298	stmtOneBonk = preparetodie(db, selecthonks+"where honks.userid = ? and xid = ? and what = 'bonk' and whofore = 2")
299	stmtPublicHonks = preparetodie(db, selecthonks+"where whofore = 2 and dt > ?"+limit)
300	stmtUserHonks = preparetodie(db, selecthonks+"where (whofore = 2 or whofore = ?) and username = ? and dt > ?"+limit)
301	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)
302	stmtHonksForMe = preparetodie(db, selecthonks+"where honks.userid = ? and dt > ? and whofore = 1"+butnotthose+limit)
303	stmtHonksByHonker = preparetodie(db, selecthonks+"join honkers on (honkers.xid = honks.honker or honkers.xid = honks.oonker) where honks.userid = ? and honkers.name = ?"+butnotthose+limit)
304	stmtHonksByXonker = preparetodie(db, selecthonks+" where honks.userid = ? and (honker = ? or oonker = ?)"+butnotthose+limit)
305	stmtHonksByCombo = preparetodie(db, selecthonks+"join honkers on honkers.xid = honks.honker where honks.userid = ? and honkers.combos like ?"+butnotthose+limit)
306	stmtHonksBySearch = preparetodie(db, selecthonks+"where honks.userid = ? and noise like ?"+limit)
307	stmtHonksByConvoy = preparetodie(db, selecthonks+"where (honks.userid = ? or (? = -1 and whofore = 2)) and convoy = ?"+limit)
308	stmtHonksByOntology = preparetodie(db, selecthonks+"join onts on honks.honkid = onts.honkid where onts.ontology = ? and (honks.userid = ? or (? = -1 and honks.whofore = 2))"+limit)
309
310	stmtSaveHonk = preparetodie(db, "insert into honks (userid, what, honker, xid, rid, dt, url, audience, noise, convoy, whofore, format, precis, oonker, flags, onts) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
311	stmtSaveOnts = preparetodie(db, "insert into onts (ontology, honkid) values (?, ?)")
312	stmtFileData = preparetodie(db, "select media, content from files where xid = ?")
313	stmtFindXonk = preparetodie(db, "select honkid from honks where userid = ? and xid = ?")
314	stmtSaveDonk = preparetodie(db, "insert into donks (honkid, fileid) values (?, ?)")
315	stmtZonkIt = preparetodie(db, "delete from honks where honkid = ?")
316	stmtZonkDonks = preparetodie(db, "delete from donks where honkid = ?")
317	stmtFindFile = preparetodie(db, "select fileid from files where url = ? and local = 1")
318	stmtSaveFile = preparetodie(db, "insert into files (xid, name, description, url, media, local, content) values (?, ?, ?, ?, ?, ?, ?)")
319	stmtWhatAbout = preparetodie(db, "select userid, username, displayname, about, pubkey, options from users where username = ?")
320	stmtSaveDub = preparetodie(db, "insert into honkers (userid, name, xid, flavor) values (?, ?, ?, ?)")
321	stmtAddDoover = preparetodie(db, "insert into doovers (dt, tries, username, rcpt, msg) values (?, ?, ?, ?, ?)")
322	stmtGetDoovers = preparetodie(db, "select dooverid, dt from doovers")
323	stmtLoadDoover = preparetodie(db, "select tries, username, rcpt, msg from doovers where dooverid = ?")
324	stmtZapDoover = preparetodie(db, "delete from doovers where dooverid = ?")
325	stmtThumbBiters = preparetodie(db, "select userid, name, wherefore from zonkers where (wherefore = 'zonker' or wherefore = 'zomain' or wherefore = 'zord' or wherefore = 'zilence')")
326	stmtFindZonk = preparetodie(db, "select zonkerid from zonkers where userid = ? and name = ? and wherefore = 'zonk'")
327	stmtGetZonkers = preparetodie(db, "select zonkerid, name, wherefore from zonkers where userid = ? and wherefore <> 'zonk'")
328	stmtSaveZonker = preparetodie(db, "insert into zonkers (userid, name, wherefore) values (?, ?, ?)")
329	stmtGetXonker = preparetodie(db, "select info from xonkers where name = ? and flavor = ?")
330	stmtSaveXonker = preparetodie(db, "insert into xonkers (name, info, flavor) values (?, ?, ?)")
331	stmtDeleteXonker = preparetodie(db, "delete from xonkers where name = ? and flavor = ?")
332	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")
333	stmtUpdateFlags = preparetodie(db, "update honks set flags = flags | ? where honkid = ?")
334	stmtClearFlags = preparetodie(db, "update honks set flags = flags & ~ ? where honkid = ?")
335	stmtSelectOnts = preparetodie(db, "select distinct(ontology) from onts join honks on onts.honkid = honks.honkid where (honks.userid = ? or honks.whofore = 2)")
336}