package database

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"time"

	"hitkeep/internal/api"
)

func (s *Store) augmentImportedSiteStats(ctx context.Context, params api.AnalyticsParams, truncUnit string, stats *api.SiteStats) error {
	if stats == nil {
		return nil
	}
	if truncUnit == "hour" {
		return s.addImportedSiteStatsExclusion(ctx, params, stats, "daily_grain", "Imported aggregate data is stored at daily grain and is excluded from hourly/realtime views.")
	}
	if len(params.Filters) > 0 {
		return s.addImportedSiteStatsExclusion(ctx, params, stats, "aggregate_filter", "The active filters require relationships aggregate imports cannot prove.")
	}

	var (
		pageviews sql.NullInt64
		visits    sql.NullInt64
		bounces   sql.NullInt64
		duration  sql.NullInt64
	)
	err := s.db.QueryRowContext(ctx, `
		SELECT
			SUM(pageviews),
			SUM(visits),
			SUM(bounces),
			SUM(visit_duration)
		FROM imported_traffic_daily
		WHERE site_id = ? AND date >= CAST(? AS DATE) AND date <= CAST(? AS DATE)
	`, params.SiteID, params.Start, params.End).Scan(&pageviews, &visits, &bounces, &duration)
	if err != nil {
		return fmt.Errorf("query imported traffic totals: %w", err)
	}

	importedPageviews := int(sqlNullInt64Value(pageviews))
	importedVisits := int(sqlNullInt64Value(visits))
	importedBounces := int(sqlNullInt64Value(bounces))
	importedDuration := float64(sqlNullInt64Value(duration))
	nativePageviews := stats.TotalPageviews
	nativeSessions := stats.UniqueSessions
	nativeBounces := int((stats.BounceRate / 100) * float64(nativeSessions))
	nativeDuration := stats.AvgSessionDuration * float64(nativeSessions)

	stats.TotalPageviews += importedPageviews
	stats.UniqueSessions += importedVisits
	totalSessions := nativeSessions + importedVisits
	if totalSessions > 0 {
		stats.BounceRate = (float64(nativeBounces+importedBounces) / float64(totalSessions)) * 100
		stats.AvgSessionDuration = (nativeDuration + importedDuration) / float64(totalSessions)
		stats.PagesPerSession = float64(nativePageviews+importedPageviews) / float64(totalSessions)
	}

	if err := s.mergeImportedChartData(ctx, params, truncUnit, stats); err != nil {
		return err
	}
	if err := s.mergeImportedTopLists(ctx, params, stats); err != nil {
		return err
	}
	return nil
}

func canIncludeImportedSiteAggregates(params api.AnalyticsParams, truncUnit string) bool {
	return truncUnit != "hour" && len(params.Filters) == 0
}

func (s *Store) queryImportedEventGoalConversions(ctx context.Context, params api.AnalyticsParams, eventName string) (int, error) {
	var conversions sql.NullInt64
	if err := s.db.QueryRowContext(ctx, `
		SELECT SUM(visitors)
		FROM imported_event_daily
		WHERE site_id = ? AND date >= CAST(? AS DATE) AND date <= CAST(? AS DATE) AND event_name = ?
	`, params.SiteID, params.Start, params.End, eventName).Scan(&conversions); err != nil {
		return 0, fmt.Errorf("query imported event goal conversions: %w", err)
	}
	return int(sqlNullInt64Value(conversions)), nil
}

func (s *Store) queryImportedEventGoalSeries(ctx context.Context, params api.AnalyticsParams, truncUnit string, eventNames []string) (map[time.Time]int, error) {
	result := map[time.Time]int{}
	if len(eventNames) == 0 {
		return result, nil
	}
	args := make([]any, 0, 3+len(eventNames))
	args = append(args, params.SiteID, params.Start, params.End)
	for _, name := range eventNames {
		args = append(args, name)
	}
	//nolint:gosec // truncUnit is chosen from the analytics bucket allowlist and placeholders are generated by count.
	query := fmt.Sprintf(`
		SELECT date_trunc('%s', date)::TIMESTAMPTZ AS bucket, SUM(visitors)
		FROM imported_event_daily
		WHERE site_id = ? AND date >= CAST(? AS DATE) AND date <= CAST(? AS DATE)
			AND event_name IN (%s)
		GROUP BY bucket
	`, truncUnit, buildPlaceholders(len(eventNames)))
	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("query imported event goal series: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var (
			bucket time.Time
			count  int
		)
		if err := rows.Scan(&bucket, &count); err != nil {
			return nil, fmt.Errorf("scan imported event goal series: %w", err)
		}
		result[truncToUnit(bucket, truncUnit)] += count
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("read imported event goal series: %w", err)
	}
	return result, nil
}

func (s *Store) addImportedSiteStatsExclusion(ctx context.Context, params api.AnalyticsParams, stats *api.SiteStats, reason, detail string) error {
	hasImported, err := s.hasImportedTraffic(ctx, params)
	if err != nil {
		return err
	}
	if hasImported {
		stats.ImportedExcluded = append(stats.ImportedExcluded, api.ImportExclusionReason{Reason: reason, Detail: detail})
	}
	return nil
}

func (s *Store) hasImportedTraffic(ctx context.Context, params api.AnalyticsParams) (bool, error) {
	var exists bool
	if err := s.db.QueryRowContext(ctx, `
		SELECT EXISTS(
			SELECT 1
			FROM imported_traffic_daily
			WHERE site_id = ? AND date >= CAST(? AS DATE) AND date <= CAST(? AS DATE)
			LIMIT 1
		)
	`, params.SiteID, params.Start, params.End).Scan(&exists); err != nil {
		return false, fmt.Errorf("query imported traffic coverage: %w", err)
	}
	return exists, nil
}

func sqlNullInt64Value(value sql.NullInt64) int64 {
	if !value.Valid {
		return 0
	}
	return value.Int64
}

func (s *Store) mergeImportedChartData(ctx context.Context, params api.AnalyticsParams, truncUnit string, stats *api.SiteStats) error {
	//nolint:gosec // truncUnit is chosen from the analytics bucket allowlist before this helper is called.
	query := fmt.Sprintf(`
		SELECT date_trunc('%s', date)::TIMESTAMPTZ AS bucket, SUM(pageviews), SUM(visitors)
		FROM imported_traffic_daily
		WHERE site_id = ? AND date >= CAST(? AS DATE) AND date <= CAST(? AS DATE)
		GROUP BY bucket
	`, truncUnit)
	rows, err := s.db.QueryContext(ctx, query, params.SiteID, params.Start, params.End)
	if err != nil {
		return fmt.Errorf("query imported chart data: %w", err)
	}
	defer rows.Close()

	byBucket := make(map[time.Time]int, len(stats.ChartData))
	for idx, point := range stats.ChartData {
		byBucket[truncToUnit(point.Time, truncUnit)] = idx
	}
	for rows.Next() {
		var (
			bucket    time.Time
			pageviews int
			visitors  int
		)
		if err := rows.Scan(&bucket, &pageviews, &visitors); err != nil {
			return fmt.Errorf("scan imported chart data: %w", err)
		}
		normalized := truncToUnit(bucket, truncUnit)
		if idx, ok := byBucket[normalized]; ok {
			stats.ChartData[idx].Pageviews += pageviews
			stats.ChartData[idx].Visitors += visitors
		}
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("read imported chart rows: %w", err)
	}
	return nil
}

func (s *Store) mergeImportedTopLists(ctx context.Context, params api.AnalyticsParams, stats *api.SiteStats) error {
	rows, err := s.db.QueryContext(ctx, `
		SELECT dimension, name, SUM(pageviews) AS val
		FROM imported_dimension_daily
		WHERE site_id = ? AND date >= CAST(? AS DATE) AND date <= CAST(? AS DATE)
			AND dimension IN (
				'page', 'entry_page', 'exit_page', 'source', 'device', 'country', 'browser', 'operating_system',
				'city', 'provider', 'asn', 'language', 'utm_campaign', 'utm_content', 'utm_medium', 'utm_source', 'utm_term'
			)
		GROUP BY dimension, name
		ORDER BY dimension, val DESC, name ASC
	`, params.SiteID, params.Start, params.End)
	if err != nil {
		return fmt.Errorf("query imported top lists: %w", err)
	}
	defer rows.Close()

	imported := map[string][]api.MetricStat{}
	for rows.Next() {
		var dim string
		var metric api.MetricStat
		if err := rows.Scan(&dim, &metric.Name, &metric.Value); err != nil {
			return fmt.Errorf("scan imported top list: %w", err)
		}
		imported[dim] = append(imported[dim], metric)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("read imported top lists: %w", err)
	}

	stats.TopPages = mergeMetricList(stats.TopPages, imported["page"], 10)
	stats.TopLandingPages = mergeMetricList(stats.TopLandingPages, imported["entry_page"], 10)
	stats.TopExitPages = mergeMetricList(stats.TopExitPages, imported["exit_page"], 10)
	stats.TopReferrers = mergeMetricList(stats.TopReferrers, imported["source"], 10)
	stats.TopDevices = mergeMetricList(stats.TopDevices, imported["device"], 10)
	stats.TopCountries = mergeMetricList(stats.TopCountries, imported["country"], 10)
	stats.TopCities = mergeMetricList(stats.TopCities, imported["city"], 10)
	stats.TopProviders = mergeMetricList(stats.TopProviders, imported["provider"], 10)
	stats.TopASNs = mergeMetricList(stats.TopASNs, imported["asn"], 10)
	stats.TopBrowsers = mergeMetricList(stats.TopBrowsers, imported["browser"], 10)
	stats.TopLanguages = mergeMetricList(stats.TopLanguages, imported["language"], 10)
	stats.TopUTMCampaigns = mergeMetricList(stats.TopUTMCampaigns, imported["utm_campaign"], 10)
	stats.TopUTMContents = mergeMetricList(stats.TopUTMContents, imported["utm_content"], 10)
	stats.TopUTMMediums = mergeMetricList(stats.TopUTMMediums, imported["utm_medium"], 10)
	stats.TopUTMSources = mergeMetricList(stats.TopUTMSources, imported["utm_source"], 10)
	stats.TopUTMTerms = mergeMetricList(stats.TopUTMTerms, imported["utm_term"], 10)
	return nil
}

func mergeMetricList(native []api.MetricStat, imported []api.MetricStat, limit int) []api.MetricStat {
	if len(imported) == 0 {
		return native
	}
	values := make(map[string]int, len(native)+len(imported))
	for _, metric := range native {
		values[metric.Name] += metric.Value
	}
	for _, metric := range imported {
		values[metric.Name] += metric.Value
	}
	merged := make([]api.MetricStat, 0, len(values))
	for name, value := range values {
		merged = append(merged, api.MetricStat{Name: name, Value: value})
	}
	sort.Slice(merged, func(i, j int) bool {
		if merged[i].Value == merged[j].Value {
			return merged[i].Name < merged[j].Name
		}
		return merged[i].Value > merged[j].Value
	})
	if limit > 0 && len(merged) > limit {
		return merged[:limit]
	}
	return merged
}
