package store

import (
	"database/sql"
	"time"

	"task-dashboard/internal/config"
	"task-dashboard/internal/models"
)

// CreateBucket inserts a new maintenance bucket and returns it.
func (s *Store) CreateBucket(name string, cycleDays, pickN int) (*models.MaintenanceBucket, error) {
	id := newTaskID()
	if _, err := s.db.Exec(`
		INSERT INTO maintenance_buckets (id, name, cycle_days, pick_n) VALUES (?, ?, ?, ?)
	`, id, name, cycleDays, pickN); err != nil {
		return nil, err
	}
	return &models.MaintenanceBucket{ID: id, Name: name, CycleDays: cycleDays, PickN: pickN}, nil
}

// GetBuckets returns every maintenance bucket, alphabetically by name.
func (s *Store) GetBuckets() ([]models.MaintenanceBucket, error) {
	rows, err := s.db.Query(`
		SELECT id, name, cycle_days, pick_n, last_cycle_at, created_at FROM maintenance_buckets ORDER BY name ASC
	`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var buckets []models.MaintenanceBucket
	for rows.Next() {
		var b models.MaintenanceBucket
		var lastCycleAt sql.NullTime
		if err := rows.Scan(&b.ID, &b.Name, &b.CycleDays, &b.PickN, &lastCycleAt, &b.CreatedAt); err != nil {
			return nil, err
		}
		if lastCycleAt.Valid {
			b.LastCycleAt = &lastCycleAt.Time
		}
		buckets = append(buckets, b)
	}
	return buckets, rows.Err()
}

// GetBucketByID returns a single bucket by id, or ErrNativeTaskNotFound.
func (s *Store) GetBucketByID(id string) (*models.MaintenanceBucket, error) {
	var b models.MaintenanceBucket
	var lastCycleAt sql.NullTime
	err := s.db.QueryRow(`
		SELECT id, name, cycle_days, pick_n, last_cycle_at, created_at FROM maintenance_buckets WHERE id = ?
	`, id).Scan(&b.ID, &b.Name, &b.CycleDays, &b.PickN, &lastCycleAt, &b.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, ErrNativeTaskNotFound
	}
	if err != nil {
		return nil, err
	}
	if lastCycleAt.Valid {
		b.LastCycleAt = &lastCycleAt.Time
	}
	return &b, nil
}

// AddBucketItem assigns an existing task to a bucket's pool: sets bucket_id
// and bucket_state = 'dormant', clearing any due date (dormant items are
// invisible to date-based views, per the design). Returns
// ErrNativeTaskNotFound if taskID doesn't match any row.
func (s *Store) AddBucketItem(bucketID, taskID string) error {
	result, err := s.db.Exec(`
		UPDATE native_tasks SET bucket_id = ?, bucket_state = 'dormant', due_date = NULL, updated_at = ? WHERE id = ?
	`, bucketID, config.Now(), taskID)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// RemoveBucketItem clears a task's bucket membership entirely. Returns
// ErrNativeTaskNotFound if taskID doesn't match any row.
func (s *Store) RemoveBucketItem(taskID string) error {
	result, err := s.db.Exec(`
		UPDATE native_tasks SET bucket_id = '', bucket_state = '', bucket_last_active_at = NULL, updated_at = ? WHERE id = ?
	`, config.Now(), taskID)
	if err != nil {
		return err
	}
	return checkRowsAffected(result)
}

// GetBucketItems returns every task in a bucket's pool (dormant and active
// both), active first, then by priority -- backs the bucket-management view.
func (s *Store) GetBucketItems(bucketID string) ([]models.Task, error) {
	rows, err := s.db.Query(`
		SELECT id, content, description, project_name, project_id, due_date, priority, completed, labels, created_at,
		       recurrence_freq, recurrence_interval, recurrence_weekdays, recurrence_series_id, next_occurrence_override, estimated_minutes,
		       chain_id, chain_position, chain_unlocked,
		       bucket_id, bucket_state, bucket_last_active_at
		FROM native_tasks
		WHERE bucket_id = ?
		ORDER BY (bucket_state = 'active') DESC, priority DESC
	`, bucketID)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()
	return scanNativeTasks(rows)
}

// DeleteBucket removes a bucket and returns every member task to being a
// plain, unbucketed task (clearing bucket_id/bucket_state/
// bucket_last_active_at) rather than deleting them. Returns
// ErrNativeTaskNotFound if id doesn't match any row.
func (s *Store) DeleteBucket(id string) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.Exec(`
		UPDATE native_tasks SET bucket_id = '', bucket_state = '', bucket_last_active_at = NULL, updated_at = ? WHERE bucket_id = ?
	`, config.Now(), id); err != nil {
		return err
	}
	result, err := tx.Exec(`DELETE FROM maintenance_buckets WHERE id = ?`, id)
	if err != nil {
		return err
	}
	if err := checkRowsAffected(result); err != nil {
		return err
	}
	return tx.Commit()
}

// selectBucketCycle activates the top pick_n dormant items in the bucket's
// pool, scored by staleness (never-activated items first, then oldest
// bucket_last_active_at) with task priority as a tiebreaker. Activated items
// get due_date = now + cycle_days and bucket_last_active_at = now. Always
// updates the bucket's last_cycle_at, even if nothing was activated (an
// empty pool shouldn't cause every subsequent tick to re-scan it). Returns
// the number of items activated.
func (s *Store) selectBucketCycle(bucketID string, now time.Time) (int, error) {
	bucket, err := s.GetBucketByID(bucketID)
	if err != nil {
		return 0, err
	}

	rows, err := s.db.Query(`
		SELECT id FROM native_tasks
		WHERE bucket_id = ? AND bucket_state = 'dormant'
		ORDER BY (bucket_last_active_at IS NULL) DESC, bucket_last_active_at ASC, priority DESC
		LIMIT ?
	`, bucketID, bucket.PickN)
	if err != nil {
		return 0, err
	}
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			_ = rows.Close()
			return 0, err
		}
		ids = append(ids, id)
	}
	if err := rows.Err(); err != nil {
		return 0, err
	}
	_ = rows.Close()

	dueDate := now.AddDate(0, 0, bucket.CycleDays)
	for _, id := range ids {
		if _, err := s.db.Exec(`
			UPDATE native_tasks SET bucket_state = 'active', due_date = ?, bucket_last_active_at = ?, updated_at = ? WHERE id = ?
		`, dueDate, now, now, id); err != nil {
			return 0, err
		}
	}

	if _, err := s.db.Exec(`UPDATE maintenance_buckets SET last_cycle_at = ? WHERE id = ?`, now, bucketID); err != nil {
		return 0, err
	}
	return len(ids), nil
}

// RunBucketCycles runs selectBucketCycle for every bucket whose cycle is
// due (last_cycle_at is unset, or at least cycle_days old). Mirrors
// AdvanceDueRecurringTasks's shape as the scheduler's entry point. Returns
// the total number of items activated across all due buckets.
func (s *Store) RunBucketCycles(now time.Time) (int, error) {
	buckets, err := s.GetBuckets()
	if err != nil {
		return 0, err
	}
	total := 0
	for _, b := range buckets {
		due := b.LastCycleAt == nil || !b.LastCycleAt.AddDate(0, 0, b.CycleDays).After(now)
		if !due {
			continue
		}
		n, err := s.selectBucketCycle(b.ID, now)
		if err != nil {
			return total, err
		}
		total += n
	}
	return total, nil
}

// DeferNativeTask returns an active bucket item to the pool without
// crediting it as done: bucket_last_active_at is left at its prior value
// (still relatively stale, likely to be reselected soon) -- unlike
// completing it, which stamps bucket_last_active_at = now via
// CompleteNativeTask. Immediately triggers a fresh selection for the
// task's bucket to backfill the freed slot. Returns ErrNativeTaskNotFound
// if id doesn't match any row, or if the task isn't an active bucket item.
func (s *Store) DeferNativeTask(id string) error {
	task, err := s.GetNativeTaskByID(id)
	if err != nil {
		return err
	}
	if task.BucketID == "" || task.BucketState != "active" {
		return ErrNativeTaskNotFound
	}

	now := config.Now()
	if _, err := s.db.Exec(`
		UPDATE native_tasks SET bucket_state = 'dormant', due_date = NULL, updated_at = ? WHERE id = ?
	`, now, id); err != nil {
		return err
	}

	_, err = s.selectBucketCycle(task.BucketID, now)
	return err
}