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