package repository

import (
	"context"
	"go-server/internal/models"
	"time"

	"gorm.io/gorm"
)

type TodoRepository struct {
	db *gorm.DB
}

func NewTodoRepository(db *gorm.DB) *TodoRepository {
	return &TodoRepository{db: db}
}

// CreateTodo creates a new todo for a user
func (r *TodoRepository) CreateTodo(ctx context.Context, todo *models.Todo) error {
	return r.db.WithContext(ctx).Create(todo).Error
}

// GetTodosByUserID gets all active todos for a user
func (r *TodoRepository) GetTodosByUserID(ctx context.Context, userID models.ULID) ([]models.Todo, error) {
	var todos []models.Todo
	err := r.db.WithContext(ctx).
		Where("user_id = ? AND is_active = ?", userID, true).
		Order("created_at ASC").
		Find(&todos).Error
	return todos, err
}

// GetTodoByID gets a todo by ID and user ID (for security)
func (r *TodoRepository) GetTodoByID(ctx context.Context, todoID, userID models.ULID) (*models.Todo, error) {
	var todo models.Todo
	err := r.db.WithContext(ctx).
		Where("id = ? AND user_id = ?", todoID, userID).
		First(&todo).Error
	if err != nil {
		return nil, err
	}
	return &todo, nil
}

// UpdateTodo updates a todo
func (r *TodoRepository) UpdateTodo(ctx context.Context, todo *models.Todo) error {
	return r.db.WithContext(ctx).Save(todo).Error
}

// DeleteTodo soft deletes a todo (sets is_active to false)
func (r *TodoRepository) DeleteTodo(ctx context.Context, todoID, userID models.ULID) error {
	return r.db.WithContext(ctx).
		Model(&models.Todo{}).
		Where("id = ? AND user_id = ?", todoID, userID).
		Update("is_active", false).Error
}

// CompleteTodo creates a completion record for a todo
func (r *TodoRepository) CompleteTodo(ctx context.Context, completion *models.TodoCompletion) error {
	return r.db.WithContext(ctx).Create(completion).Error
}

// GetTodayCompletions gets all completions for today for a user
func (r *TodoRepository) GetTodayCompletions(ctx context.Context, userID models.ULID) ([]models.TodoCompletion, error) {
	now := time.Now()
	startOfDay := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
	endOfDay := startOfDay.Add(24 * time.Hour)

	var completions []models.TodoCompletion
	err := r.db.WithContext(ctx).
		Preload("Todo").
		Where("user_id = ? AND completed_at >= ? AND completed_at < ?", userID, startOfDay, endOfDay).
		Order("completed_at DESC").
		Find(&completions).Error
	return completions, err
}

// GetCompletionsByDateRange gets completions for a date range
func (r *TodoRepository) GetCompletionsByDateRange(ctx context.Context, userID models.ULID, startDate, endDate time.Time) ([]models.TodoCompletion, error) {
	var completions []models.TodoCompletion
	err := r.db.WithContext(ctx).
		Preload("Todo").
		Where("user_id = ? AND completed_at >= ? AND completed_at < ?", userID, startDate, endDate).
		Order("completed_at DESC").
		Find(&completions).Error
	return completions, err
}

// GetTodoWithStats gets todos with completion statistics using raw SQL for better performance
func (r *TodoRepository) GetTodosWithStats(ctx context.Context, userID models.ULID) ([]models.TodoWithStats, error) {
	var results []models.TodoWithStats

	query := `
		WITH todo_stats AS (
			SELECT 
				t.id,
				t.user_id,
				t.title,
				t.description,
				t.color,
				t.is_active,
				t.created_at,
				t.updated_at,
				COUNT(tc.id) as total_completions,
				MAX(tc.completed_at) as last_completed_at,
				CASE 
					WHEN MAX(tc.completed_at) >= CURRENT_DATE 
					THEN true 
					ELSE false 
				END as completed_today
			FROM todos t
			LEFT JOIN todo_completions tc ON t.id = tc.todo_id
			WHERE t.user_id = ? AND t.is_active = true
			GROUP BY t.id, t.user_id, t.title, t.description, t.color, t.is_active, t.created_at, t.updated_at
		),
		streak_calc AS (
			SELECT 
				ts.*,
				COALESCE(
					(SELECT COUNT(*)
					 FROM generate_series(
						CURRENT_DATE - INTERVAL '365 days',
						CURRENT_DATE,
						INTERVAL '1 day'
					 ) AS date_series(date)
					 WHERE EXISTS (
						SELECT 1 FROM todo_completions tc2 
						WHERE tc2.todo_id = ts.id 
						AND DATE(tc2.completed_at) = date_series.date
					 )
					 AND date_series.date <= CURRENT_DATE
					 AND NOT EXISTS (
						SELECT 1 FROM generate_series(
							date_series.date + INTERVAL '1 day',
							CURRENT_DATE,
							INTERVAL '1 day'
						) AS gap_check(gap_date)
						WHERE NOT EXISTS (
							SELECT 1 FROM todo_completions tc3
							WHERE tc3.todo_id = ts.id
							AND DATE(tc3.completed_at) = gap_check.gap_date
						)
					 )
					), 0
				) as current_streak
			FROM todo_stats ts
		)
		SELECT 
			sc.*,
			COALESCE(
				(SELECT MAX(streak_length)
				 FROM (
					SELECT COUNT(*) as streak_length
					FROM (
						SELECT 
							DATE(tc.completed_at) as completion_date,
							ROW_NUMBER() OVER (ORDER BY DATE(tc.completed_at)) as rn,
							DATE(tc.completed_at) - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY DATE(tc.completed_at)) as streak_group
						FROM todo_completions tc
						WHERE tc.todo_id = sc.id
					) grouped
					GROUP BY streak_group
				 ) streaks), 0
			) as longest_streak
		FROM streak_calc sc
		ORDER BY sc.created_at ASC
	`

	rows, err := r.db.WithContext(ctx).Raw(query, userID).Rows()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var result models.TodoWithStats
		var lastCompletedAt *time.Time

		err := rows.Scan(
			&result.ID,
			&result.UserID,
			&result.Title,
			&result.Description,
			&result.Color,
			&result.IsActive,
			&result.CreatedAt,
			&result.UpdatedAt,
			&result.TotalCompletions,
			&lastCompletedAt,
			&result.CompletedToday,
			&result.CurrentStreak,
			&result.LongestStreak,
		)
		if err != nil {
			return nil, err
		}

		result.LastCompletedAt = lastCompletedAt
		results = append(results, result)
	}

	return results, nil
}

// CheckTodoCompletedToday checks if a todo was completed today
func (r *TodoRepository) CheckTodoCompletedToday(ctx context.Context, todoID, userID models.ULID) (bool, error) {
	now := time.Now()
	startOfDay := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
	endOfDay := startOfDay.Add(24 * time.Hour)

	var count int64
	err := r.db.WithContext(ctx).
		Model(&models.TodoCompletion{}).
		Where("todo_id = ? AND user_id = ? AND completed_at >= ? AND completed_at < ?",
			todoID, userID, startOfDay, endOfDay).
		Count(&count).Error

	return count > 0, err
}

// GetActivityLog gets activity log with pagination
func (r *TodoRepository) GetActivityLog(ctx context.Context, userID models.ULID, limit, offset int) ([]models.TodoCompletion, error) {
	var completions []models.TodoCompletion
	err := r.db.WithContext(ctx).
		Preload("Todo").
		Where("user_id = ?", userID).
		Order("completed_at DESC").
		Limit(limit).
		Offset(offset).
		Find(&completions).Error
	return completions, err
}

// GetActivityLogByDate gets activity log for a specific date
func (r *TodoRepository) GetActivityLogByDate(ctx context.Context, userID models.ULID, date time.Time) ([]models.TodoCompletion, error) {
	startOfDay := time.Date(date.Year(), date.Month(), date.Day(), 0, 0, 0, 0, date.Location())
	endOfDay := startOfDay.Add(24 * time.Hour)

	var completions []models.TodoCompletion
	err := r.db.WithContext(ctx).
		Preload("Todo").
		Where("user_id = ? AND completed_at >= ? AND completed_at < ?", userID, startOfDay, endOfDay).
		Order("completed_at DESC").
		Find(&completions).Error
	return completions, err
}
