1package postgres
  2
  3import (
  4	"fmt"
  5	"sort"
  6	"sync"
  7	"time"
  8
  9	"github.com/picosh/pico/pkg/db"
 10)
 11
 12// mobileUserAgentExpr is a SQL expression to detect mobile user agents.
 13const mobileUserAgentExpr = `user_agent ILIKE '%mobile%' OR user_agent ILIKE '%android%' OR user_agent ILIKE '%iphone%' OR user_agent ILIKE '%ipad%' OR user_agent ILIKE '%ipod%' OR user_agent ILIKE '%blackberry%' OR user_agent ILIKE '%windows phone%'`
 14
 15func (me *PsqlDB) InsertVisit(visit *db.AnalyticsVisits) error {
 16	_, err := me.Db.Exec(
 17		`INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status, content_type) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);`,
 18		visit.UserID,
 19		newNullString(visit.ProjectID),
 20		newNullString(visit.PostID),
 21		newNullString(visit.Namespace),
 22		visit.Host,
 23		visit.Path,
 24		visit.IpAddress,
 25		visit.UserAgent,
 26		visit.Referer,
 27		visit.Status,
 28		visit.ContentType,
 29	)
 30	return err
 31}
 32
 33func visitFilterBy(opts *db.SummaryOpts) (string, string) {
 34	where := ""
 35	val := ""
 36	if opts.Host != "" {
 37		where = "host"
 38		val = opts.Host
 39	} else if opts.Path != "" {
 40		where = "path"
 41		val = opts.Path
 42	}
 43
 44	return where, val
 45}
 46
 47func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
 48	var intervals, currentIntervals []*db.VisitInterval
 49	var sumErr, rawErr error
 50
 51	var wg sync.WaitGroup
 52	wg.Add(2)
 53
 54	go func() {
 55		defer wg.Done()
 56		intervals, sumErr = me.visitUniqueFromSummary(opts)
 57	}()
 58	go func() {
 59		defer wg.Done()
 60		currentIntervals, rawErr = me.visitUniqueFromRaw(opts)
 61	}()
 62
 63	wg.Wait()
 64
 65	if sumErr != nil {
 66		return nil, fmt.Errorf("query summary visits: %w", sumErr)
 67	}
 68	if rawErr != nil {
 69		return nil, fmt.Errorf("query raw visits: %w", rawErr)
 70	}
 71
 72	// Merge: current month data may overlap with summary data, combine counts
 73	return mergeVisitIntervals(intervals, currentIntervals), nil
 74}
 75
 76// visitUniqueFromSummary reads unique visitor counts from analytics_monthly_visits for historical data.
 77func (me *PsqlDB) visitUniqueFromSummary(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
 78	now := time.Now()
 79	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
 80	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
 81
 82	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
 83	if !opts.Origin.Before(previousMonthStart) {
 84		return nil, nil
 85	}
 86
 87	where := ""
 88	args := []interface{}{opts.UserID, opts.Origin, currentMonthStart}
 89	argIdx := 4
 90	if opts.Host != "" {
 91		where = "AND host = $" + fmt.Sprintf("%d", argIdx)
 92		args = append(args, opts.Host)
 93	}
 94
 95	query := fmt.Sprintf(`
 96		SELECT
 97			date_trunc('%s', visit_date)::timestamptz as interval_start,
 98			sum(unique_visits) as unique_visitors,
 99			sum(mobile_visits) as mobile_visits,
100			sum(desktop_visits) as desktop_visits
101		FROM analytics_monthly_visits
102		WHERE user_id = $1 AND visit_date >= $2 AND visit_date < $3 %s
103		GROUP BY interval_start
104		ORDER BY interval_start`, opts.Interval, where)
105
106	rows, err := me.Db.Queryx(query, args...)
107	if err != nil {
108		return nil, err
109	}
110	defer func() { _ = rows.Close() }()
111
112	var intervals []*db.VisitInterval
113	for rows.Next() {
114		interval := &db.VisitInterval{}
115		if err := rows.Scan(&interval.Interval, &interval.Visitors, &interval.MobileVisitors, &interval.DesktopVisitors); err != nil {
116			return nil, err
117		}
118		intervals = append(intervals, interval)
119	}
120	return intervals, rows.Err()
121}
122
123// visitUniqueFromRaw reads unique visitor counts from analytics_visits for the previous and current months.
124// This covers the gap between the last aggregated month and the current month.
125func (me *PsqlDB) visitUniqueFromRaw(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
126	now := time.Now()
127	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
128	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
129
130	where, with := visitFilterBy(opts)
131
132	// Determine the effective start: max(origin, previousMonthStart)
133	effectiveStart := previousMonthStart
134	if opts.Origin.After(previousMonthStart) {
135		effectiveStart = opts.Origin
136	}
137
138	uniqueVisitors := fmt.Sprintf(`
139		SELECT
140			date_trunc('%s', created_at)::timestamptz as interval_start,
141			count(DISTINCT CASE WHEN %s THEN ip_address END) as mobile_visitors,
142			count(DISTINCT CASE WHEN NOT %s THEN ip_address END) as desktop_visitors,
143			count(DISTINCT ip_address) as unique_visitors
144		FROM analytics_visits
145		WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND status <> 404
146		GROUP BY interval_start
147		ORDER BY interval_start`,
148		opts.Interval, mobileUserAgentExpr, mobileUserAgentExpr, where)
149
150	rows, err := me.Db.Queryx(uniqueVisitors, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
151	if err != nil {
152		return nil, err
153	}
154	defer func() { _ = rows.Close() }()
155
156	var intervals []*db.VisitInterval
157	for rows.Next() {
158		interval := &db.VisitInterval{}
159		if err := rows.Scan(&interval.Interval, &interval.MobileVisitors, &interval.DesktopVisitors, &interval.Visitors); err != nil {
160			return nil, err
161		}
162		intervals = append(intervals, interval)
163	}
164	return intervals, rows.Err()
165}
166
167// mergeVisitIntervals combines historical (summary table) and current (raw) intervals.
168// Summary data is preferred when both sources have the same interval to avoid double-counting.
169func mergeVisitIntervals(historical, current []*db.VisitInterval) []*db.VisitInterval {
170	if len(historical) == 0 {
171		return current
172	}
173	if len(current) == 0 {
174		return historical
175	}
176
177	// Build a map by interval timestamp for merging.
178	// Summary data takes precedence over raw data for the same interval
179	// (e.g. when a month is aggregated but raw data still exists in a local dump).
180	intervalMap := make(map[int64]*db.VisitInterval)
181	for _, ci := range current {
182		ts := ci.Interval.Unix()
183		intervalMap[ts] = ci
184	}
185	for _, hi := range historical {
186		ts := hi.Interval.Unix()
187		intervalMap[ts] = hi // summary overwrites raw if both exist
188	}
189
190	// Sort by interval
191	result := make([]*db.VisitInterval, 0, len(intervalMap))
192	for _, iv := range intervalMap {
193		result = append(result, iv)
194	}
195	sort.Slice(result, func(i, j int) bool {
196		return result[i].Interval.Before(*result[j].Interval)
197	})
198	return result
199}
200
201// mergeTopUrls combines historical and current top URLs, summing counts for overlapping URLs,
202// then returns the top 10 by total count.
203func mergeTopUrls(historical, current []*db.VisitUrl) []*db.VisitUrl {
204	if len(historical) == 0 {
205		return current
206	}
207	if len(current) == 0 {
208		return historical
209	}
210
211	// Build a map by URL for merging
212	urlMap := make(map[string]*db.VisitUrl)
213	for _, hu := range historical {
214		urlMap[hu.Url] = hu
215	}
216
217	for _, cu := range current {
218		if existing, ok := urlMap[cu.Url]; ok {
219			existing.Count += cu.Count
220		} else {
221			urlMap[cu.Url] = cu
222		}
223	}
224
225	// Sort by count descending and take top 10
226	result := make([]*db.VisitUrl, 0, len(urlMap))
227	for _, u := range urlMap {
228		result = append(result, u)
229	}
230	sort.Slice(result, func(i, j int) bool {
231		return result[i].Count > result[j].Count
232	})
233	if len(result) > 10 {
234		result = result[:10]
235	}
236	return result
237}
238
239// mergeTopReferers combines historical and current top referers, summing counts for overlapping referers,
240// then returns the top 10 by total count.
241func mergeTopReferers(historical, current []*db.VisitUrl) []*db.VisitUrl {
242	return mergeTopUrls(historical, current) // Same logic as mergeTopUrls
243}
244
245// mergeHosts combines historical and current hosts, summing counts for overlapping hosts,
246// then returns sorted by total count descending.
247func mergeHosts(historical, current []*db.VisitUrl) []*db.VisitUrl {
248	if len(historical) == 0 {
249		return current
250	}
251	if len(current) == 0 {
252		return historical
253	}
254
255	// Build a map by host for merging
256	hostMap := make(map[string]*db.VisitUrl)
257	for _, hu := range historical {
258		hostMap[hu.Url] = hu
259	}
260
261	for _, cu := range current {
262		if existing, ok := hostMap[cu.Url]; ok {
263			existing.Count += cu.Count
264		} else {
265			hostMap[cu.Url] = cu
266		}
267	}
268
269	// Sort by count descending
270	result := make([]*db.VisitUrl, 0, len(hostMap))
271	for _, h := range hostMap {
272		result = append(result, h)
273	}
274	sort.Slice(result, func(i, j int) bool {
275		return result[i].Count > result[j].Count
276	})
277	return result
278}
279
280func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
281	var historical, current []*db.VisitUrl
282	var histErr, rawErr error
283
284	var wg sync.WaitGroup
285	wg.Add(2)
286
287	go func() {
288		defer wg.Done()
289		historical, histErr = me.visitRefererFromSummary(opts)
290	}()
291	go func() {
292		defer wg.Done()
293		current, rawErr = me.visitRefererFromRaw(opts)
294	}()
295
296	wg.Wait()
297
298	if histErr != nil {
299		return nil, fmt.Errorf("query summary referers: %w", histErr)
300	}
301	if rawErr != nil {
302		return nil, fmt.Errorf("query raw referers: %w", rawErr)
303	}
304
305	return mergeTopReferers(historical, current), nil
306}
307
308// visitRefererFromSummary reads top referers from analytics_monthly_top_referers for historical data.
309func (me *PsqlDB) visitRefererFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
310	now := time.Now()
311	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
312	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
313
314	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
315	if !opts.Origin.Before(previousMonthStart) {
316		return nil, nil
317	}
318
319	// Clamp origin to month boundary for summary table lookup
320	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
321
322	where := ""
323	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
324	if opts.Host != "" {
325		where = "AND host = $4"
326		args = append(args, opts.Host)
327	}
328
329	query := fmt.Sprintf(`
330		SELECT referer, sum(unique_visits) as total_visits
331		FROM analytics_monthly_top_referers
332		WHERE user_id = $1 AND month >= $2 AND month < $3 %s
333		GROUP BY referer
334		ORDER BY total_visits DESC
335		LIMIT 10`, where)
336
337	rows, err := me.Db.Queryx(query, args...)
338	if err != nil {
339		return nil, err
340	}
341	defer func() { _ = rows.Close() }()
342
343	var results []*db.VisitUrl
344	for rows.Next() {
345		result := &db.VisitUrl{}
346		if err := rows.Scan(&result.Url, &result.Count); err != nil {
347			return nil, err
348		}
349		results = append(results, result)
350	}
351	return results, rows.Err()
352}
353
354// visitRefererFromRaw reads top referers from analytics_visits for the previous and current months.
355func (me *PsqlDB) visitRefererFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
356	now := time.Now()
357	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
358	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
359
360	where, with := visitFilterBy(opts)
361
362	// Determine the effective start: max(origin, previousMonthStart)
363	effectiveStart := previousMonthStart
364	if opts.Origin.After(previousMonthStart) {
365		effectiveStart = opts.Origin
366	}
367
368	topUrls := fmt.Sprintf(`
369		SELECT
370			referer,
371			count(DISTINCT ip_address) as referer_count
372		FROM analytics_visits
373		WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND referer <> '' AND status <> 404
374		GROUP BY referer
375		ORDER BY referer_count DESC
376		LIMIT 10`, where)
377
378	rows, err := me.Db.Queryx(topUrls, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
379	if err != nil {
380		return nil, err
381	}
382	defer func() { _ = rows.Close() }()
383
384	var results []*db.VisitUrl
385	for rows.Next() {
386		result := &db.VisitUrl{}
387		if err := rows.Scan(&result.Url, &result.Count); err != nil {
388			return nil, err
389		}
390		results = append(results, result)
391	}
392	return results, rows.Err()
393}
394
395func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
396	var historical, current []*db.VisitUrl
397	var histErr, rawErr error
398
399	var wg sync.WaitGroup
400	wg.Add(2)
401
402	go func() {
403		defer wg.Done()
404		historical, histErr = me.visitUrlFromSummary(opts)
405	}()
406	go func() {
407		defer wg.Done()
408		current, rawErr = me.visitUrlFromRaw(opts)
409	}()
410
411	wg.Wait()
412
413	if histErr != nil {
414		return nil, fmt.Errorf("query summary urls: %w", histErr)
415	}
416	if rawErr != nil {
417		return nil, fmt.Errorf("query raw urls: %w", rawErr)
418	}
419
420	return mergeTopUrls(historical, current), nil
421}
422
423// visitUrlFromSummary reads top URLs from analytics_monthly_top_urls for historical data.
424func (me *PsqlDB) visitUrlFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
425	now := time.Now()
426	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
427	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
428
429	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
430	if !opts.Origin.Before(previousMonthStart) {
431		return nil, nil
432	}
433
434	// Clamp origin to month boundary for summary table lookup
435	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
436
437	where := ""
438	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
439	if opts.Host != "" {
440		where = "AND host = $4"
441		args = append(args, opts.Host)
442	}
443
444	query := fmt.Sprintf(`
445		SELECT path, sum(unique_visits) as total_visits
446		FROM analytics_monthly_top_urls
447		WHERE user_id = $1 AND month >= $2 AND month < $3 AND status_code <> 404 %s
448		GROUP BY path
449		ORDER BY total_visits DESC
450		LIMIT 10`, where)
451
452	rows, err := me.Db.Queryx(query, args...)
453	if err != nil {
454		return nil, err
455	}
456	defer func() { _ = rows.Close() }()
457
458	var results []*db.VisitUrl
459	for rows.Next() {
460		result := &db.VisitUrl{}
461		if err := rows.Scan(&result.Url, &result.Count); err != nil {
462			return nil, err
463		}
464		results = append(results, result)
465	}
466	return results, rows.Err()
467}
468
469// visitUrlFromRaw reads top URLs from analytics_visits for the previous and current months.
470func (me *PsqlDB) visitUrlFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
471	now := time.Now()
472	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
473	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
474
475	where, with := visitFilterBy(opts)
476
477	// Determine the effective start: max(origin, previousMonthStart)
478	effectiveStart := previousMonthStart
479	if opts.Origin.After(previousMonthStart) {
480		effectiveStart = opts.Origin
481	}
482
483	topUrls := fmt.Sprintf(`
484		SELECT
485			path,
486			count(DISTINCT ip_address) as path_count
487		FROM analytics_visits
488		WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND path <> '' AND status <> 404
489		GROUP BY path
490		ORDER BY path_count DESC
491		LIMIT 10`, where)
492
493	rows, err := me.Db.Queryx(topUrls, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
494	if err != nil {
495		return nil, err
496	}
497	defer func() { _ = rows.Close() }()
498
499	var results []*db.VisitUrl
500	for rows.Next() {
501		result := &db.VisitUrl{}
502		if err := rows.Scan(&result.Url, &result.Count); err != nil {
503			return nil, err
504		}
505		results = append(results, result)
506	}
507	return results, rows.Err()
508}
509
510func (me *PsqlDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
511	limit := opts.Limit
512	if limit == 0 {
513		limit = 10
514	}
515
516	var historical, current []*db.VisitUrl
517	var histErr, rawErr error
518
519	var wg sync.WaitGroup
520	wg.Add(2)
521
522	go func() {
523		defer wg.Done()
524		historical, histErr = me.visitUrlNotFoundFromSummary(opts, limit)
525	}()
526	go func() {
527		defer wg.Done()
528		current, rawErr = me.visitUrlNotFoundFromRaw(opts, limit)
529	}()
530
531	wg.Wait()
532
533	if histErr != nil {
534		return nil, fmt.Errorf("query summary 404 urls: %w", histErr)
535	}
536	if rawErr != nil {
537		return nil, fmt.Errorf("query raw 404 urls: %w", rawErr)
538	}
539
540	return mergeTopUrls(historical, current), nil
541}
542
543// visitUrlNotFoundFromSummary reads top 404 URLs from analytics_monthly_top_urls for historical data.
544func (me *PsqlDB) visitUrlNotFoundFromSummary(opts *db.SummaryOpts, limit int) ([]*db.VisitUrl, error) {
545	now := time.Now()
546	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
547	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
548
549	// If origin is in the previous month or later, raw data covers it — no summary to fetch.
550	if !opts.Origin.Before(previousMonthStart) {
551		return nil, nil
552	}
553
554	// Clamp origin to month boundary for summary table lookup
555	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
556
557	where := ""
558	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
559	argIdx := 4
560	if opts.Host != "" {
561		where = "AND host = $" + fmt.Sprintf("%d", argIdx)
562		args = append(args, opts.Host)
563	}
564
565	query := fmt.Sprintf(`
566		SELECT path, sum(unique_visits) as total_visits
567		FROM analytics_monthly_top_urls
568		WHERE user_id = $1 AND month >= $2 AND month < $3 AND status_code = 404 %s
569		GROUP BY path
570		ORDER BY total_visits DESC
571		LIMIT %d`, where, limit)
572
573	rows, err := me.Db.Queryx(query, args...)
574	if err != nil {
575		return nil, err
576	}
577	defer func() { _ = rows.Close() }()
578
579	var results []*db.VisitUrl
580	for rows.Next() {
581		result := &db.VisitUrl{}
582		if err := rows.Scan(&result.Url, &result.Count); err != nil {
583			return nil, err
584		}
585		results = append(results, result)
586	}
587	return results, rows.Err()
588}
589
590// visitUrlNotFoundFromRaw reads top 404 URLs from analytics_visits for the previous and current months.
591func (me *PsqlDB) visitUrlNotFoundFromRaw(opts *db.SummaryOpts, limit int) ([]*db.VisitUrl, error) {
592	now := time.Now()
593	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
594	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
595
596	where, with := visitFilterBy(opts)
597
598	// Determine the effective start: max(origin, previousMonthStart)
599	effectiveStart := previousMonthStart
600	if opts.Origin.After(previousMonthStart) {
601		effectiveStart = opts.Origin
602	}
603
604	topUrls := fmt.Sprintf(`
605		SELECT
606			path,
607			count(DISTINCT ip_address) as path_count
608		FROM analytics_visits
609		WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND path <> '' AND status = 404
610		GROUP BY path
611		ORDER BY path_count DESC
612		LIMIT %d`, where, limit)
613
614	rows, err := me.Db.Queryx(topUrls, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
615	if err != nil {
616		return nil, err
617	}
618	defer func() { _ = rows.Close() }()
619
620	var results []*db.VisitUrl
621	for rows.Next() {
622		result := &db.VisitUrl{}
623		if err := rows.Scan(&result.Url, &result.Count); err != nil {
624			return nil, err
625		}
626		results = append(results, result)
627	}
628	return results, rows.Err()
629}
630
631func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
632	var historical, current []*db.VisitUrl
633	var histErr, rawErr error
634
635	var wg sync.WaitGroup
636	wg.Add(2)
637
638	go func() {
639		defer wg.Done()
640		historical, histErr = me.visitHostFromSummary(opts)
641	}()
642	go func() {
643		defer wg.Done()
644		current, rawErr = me.visitHostFromRaw(opts)
645	}()
646
647	wg.Wait()
648
649	if histErr != nil {
650		return nil, fmt.Errorf("query summary hosts: %w", histErr)
651	}
652	if rawErr != nil {
653		return nil, fmt.Errorf("query raw hosts: %w", rawErr)
654	}
655
656	return mergeHosts(historical, current), nil
657}
658
659// visitHostFromSummary reads host data from analytics_user_sites for historical data.
660func (me *PsqlDB) visitHostFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
661	rows, err := me.Db.Queryx(`
662		SELECT host, total_visits
663		FROM analytics_user_sites
664		WHERE user_id = $1 AND host <> ''
665		ORDER BY total_visits DESC`, opts.UserID)
666	if err != nil {
667		return nil, err
668	}
669	defer func() { _ = rows.Close() }()
670
671	var results []*db.VisitUrl
672	for rows.Next() {
673		result := &db.VisitUrl{}
674		if err := rows.Scan(&result.Url, &result.Count); err != nil {
675			return nil, err
676		}
677		results = append(results, result)
678	}
679	return results, rows.Err()
680}
681
682// visitHostFromRaw reads hosts from analytics_visits for the current month that aren't in summary.
683func (me *PsqlDB) visitHostFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
684	now := time.Now()
685	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
686
687	rows, err := me.Db.Queryx(`
688		SELECT host, count(DISTINCT ip_address) as host_count
689		FROM analytics_visits
690		WHERE created_at >= $1 AND user_id = $2 AND host <> ''
691		GROUP BY host
692		ORDER BY host_count DESC`, currentMonthStart, opts.UserID)
693	if err != nil {
694		return nil, err
695	}
696	defer func() { _ = rows.Close() }()
697
698	var results []*db.VisitUrl
699	for rows.Next() {
700		result := &db.VisitUrl{}
701		if err := rows.Scan(&result.Url, &result.Count); err != nil {
702			return nil, err
703		}
704		results = append(results, result)
705	}
706	return results, rows.Err()
707}
708
709func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
710	var (
711		visitors    []*db.VisitInterval
712		urls        []*db.VisitUrl
713		refs        []*db.VisitUrl
714		notFound    []*db.VisitUrl
715		visitorsErr error
716		urlsErr     error
717		refsErr     error
718		nfErr       error
719	)
720
721	var wg sync.WaitGroup
722	wg.Add(4)
723
724	go func() {
725		defer wg.Done()
726		visitors, visitorsErr = me.visitUnique(opts)
727	}()
728	go func() {
729		defer wg.Done()
730		urls, urlsErr = me.visitUrl(opts)
731	}()
732	go func() {
733		defer wg.Done()
734		refs, refsErr = me.visitReferer(opts)
735	}()
736	go func() {
737		defer wg.Done()
738		notFound, nfErr = me.VisitUrlNotFound(opts)
739	}()
740
741	wg.Wait()
742
743	// Return the first error encountered
744	for _, err := range []error{visitorsErr, urlsErr, refsErr, nfErr} {
745		if err != nil {
746			return nil, err
747		}
748	}
749
750	return &db.SummaryVisits{
751		Intervals:    visitors,
752		TopUrls:      urls,
753		TopReferers:  refs,
754		NotFoundUrls: notFound,
755	}, nil
756}
757
758func (me *PsqlDB) FindVisitSiteList(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
759	return me.visitHost(opts)
760}