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