package postgres

import (
	"context"
	"time"
)

// Dashboard são os contadores da página inicial do painel.
type Dashboard struct {
	UsersByStatus         map[string]int
	NotificationsEnabled  int
	PausedByAPIFailures   int
	TodayByStatus         map[string]int
	FailedLast7Days       int
	ExpiredLast7Days      int
	CustomWindows         int
}

func (s *Store) Dashboard(ctx context.Context, today string) (Dashboard, error) {
	d := Dashboard{UsersByStatus: map[string]int{}, TodayByStatus: map[string]int{}}

	if err := s.countBy(ctx, `SELECT status::text, count(*) FROM users GROUP BY 1`, d.UsersByStatus); err != nil {
		return d, err
	}
	if err := s.countBy(ctx, `SELECT status::text, count(*) FROM notifications WHERE reference_date = $1::text::date GROUP BY 1`,
		d.TodayByStatus, today); err != nil {
		return d, err
	}
	err := s.pool.QueryRow(ctx, `
		SELECT
			(SELECT count(*) FROM users WHERE status = 'active' AND notifications_enabled),
			(SELECT count(*) FROM users WHERE status = 'active' AND notifications_paused_reason = 'api_failures'),
			(SELECT count(*) FROM notifications WHERE status = 'failed' AND reference_date >= $1::text::date - 7),
			(SELECT count(*) FROM notifications WHERE status = 'expired' AND reference_date >= $1::text::date - 7),
			(SELECT count(*) FROM work_schedules ws JOIN users u ON u.id = ws.user_id
			  WHERE ws.query_window_start IS NOT NULL AND u.status = 'active')`, today).
		Scan(&d.NotificationsEnabled, &d.PausedByAPIFailures, &d.FailedLast7Days, &d.ExpiredLast7Days, &d.CustomWindows)
	return d, err
}

func (s *Store) countBy(ctx context.Context, sql string, out map[string]int, args ...any) error {
	rows, err := s.pool.Query(ctx, sql, args...)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var key string
		var n int
		if err := rows.Scan(&key, &n); err != nil {
			return err
		}
		out[key] = n
	}
	return rows.Err()
}

// ReminderSample é um lembrete de entrada2 recente, usado na pré-visualização
// da janela (9.13): se o horário do aviso cai antes do início da nova janela,
// ele teria saído atrasado ou expirado.
type ReminderSample struct {
	UserID       int64
	Timezone     string
	Day          string
	ScheduledAt  time.Time
	CustomWindow bool
}

func (s *Store) Entrada2Samples(ctx context.Context, sinceDay string) ([]ReminderSample, error) {
	rows, err := s.pool.Query(ctx, `
		SELECT n.user_id, u.timezone, to_char(n.reference_date, 'YYYY-MM-DD'), n.scheduled_at,
		       ws.query_window_start IS NOT NULL
		FROM notifications n
		JOIN users u ON u.id = n.user_id
		JOIN work_schedules ws ON ws.user_id = n.user_id
		WHERE n.type = 'entrada2' AND n.reference_date >= $1::text::date`, sinceDay)
	return collect(rows, err, func(row scanner) (ReminderSample, error) {
		var x ReminderSample
		err := row.Scan(&x.UserID, &x.Timezone, &x.Day, &x.ScheduledAt, &x.CustomWindow)
		return x, err
	})
}

func (s *Store) DeleteNotificationsBefore(ctx context.Context, day string) (int64, error) {
	tag, err := s.pool.Exec(ctx, `DELETE FROM notifications WHERE reference_date < $1::text::date`, day)
	return tag.RowsAffected(), err
}

func (s *Store) DeleteUserDaysBefore(ctx context.Context, day string) (int64, error) {
	tag, err := s.pool.Exec(ctx, `DELETE FROM user_days WHERE day < $1::text::date`, day)
	return tag.RowsAffected(), err
}