package tasks

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"atlas9.dev/c/core"
	"atlas9.dev/c/core/dbi"
	"atlas9.dev/c/demo/lib"
)

const sqliteTimeFormat = "2006-01-02 15:04:05"

// SqliteProducer enqueues tasks of type T into a single SQLite table.
type SqliteProducer[T any] struct {
	DB    dbi.DBI
	Table string
}

func (p *SqliteProducer[T]) Create(ctx context.Context, payload T) error {

	data, err := json.Marshal(payload)
	if err != nil {
		return fmt.Errorf("marshaling task payload: %w", err)
	}
	taskID := core.NewID("task").String()
	origin := lib.GetRequestID(ctx)
	_, err = p.DB.Exec(ctx,
		"INSERT INTO "+p.Table+" (task_id, payload, status, origin) VALUES ($1, $2, 'Pending', $3)",
		taskID, string(data), origin)
	if err != nil {
		return fmt.Errorf("inserting task: %w", err)
	}
	return nil
}

// SqliteConsumer claims and manages tasks in a named table.
type SqliteConsumer struct {
	db    dbi.DBI
	table string
}

func NewSqliteConsumer(db dbi.DBI, table string) *SqliteConsumer {
	return &SqliteConsumer{db: db, table: table}
}

func (s *SqliteConsumer) Claim(ctx context.Context, leaseDuration time.Duration, limit int) ([]Task, error) {

	var results []Task

	// Reclaim expired leases
	_, err := s.db.Exec(ctx, `
		UPDATE `+s.table+` SET
			status = 'Pending', lease_until = NULL
		WHERE status = 'Processing' AND lease_until < datetime('now')
	`)
	if err != nil {
		return nil, fmt.Errorf("reclaiming expired leases: %w", err)
	}

	leaseUntil := time.Now().Add(leaseDuration).UTC().Format(sqliteTimeFormat)

	for range limit {
		// Claim one pending task
		res, err := s.db.Exec(ctx, `
			UPDATE `+s.table+` SET
				status = 'Processing',
				lease_until = $1,
				attempts = attempts + 1
			WHERE task_id = (
				SELECT task_id FROM `+s.table+`
				WHERE status = 'Pending'
				  AND run_after <= datetime('now')
				ORDER BY created_at, task_id
				LIMIT 1
			)
		`, leaseUntil)
		if err != nil {
			return nil, fmt.Errorf("claiming task: %w", err)
		}

		n, err := res.RowsAffected()
		if err != nil {
			return nil, fmt.Errorf("checking rows affected: %w", err)
		}
		if n == 0 {
			break
		}

		// Fetch the claimed task
		var task Task
		var payloadStr string
		var statusStr string
		var leaseStr sql.NullString
		var runAfterStr, createdStr string

		row := s.db.QueryRow(ctx, `
			SELECT task_id, payload, status, origin, attempts, run_after, lease_until, created_at
			FROM `+s.table+`
			WHERE status = 'Processing' AND lease_until = $1
			ORDER BY created_at, task_id LIMIT 1
		`, leaseUntil)

		err = row.Scan(
			&task.TaskID, &payloadStr, &statusStr, &task.Origin,
			&task.Attempts, &runAfterStr, &leaseStr, &createdStr,
		)
		if err != nil {
			return nil, fmt.Errorf("fetching claimed task: %w", err)
		}

		task.Payload = []byte(payloadStr)
		task.Status, _ = ParseStatus(statusStr)
		task.RunAfter, _ = time.Parse(sqliteTimeFormat, runAfterStr)
		task.CreatedAt, _ = time.Parse(sqliteTimeFormat, createdStr)
		if leaseStr.Valid {
			t, _ := time.Parse(sqliteTimeFormat, leaseStr.String)
			task.LeaseUntil = &t
		}

		results = append(results, task)
	}

	return results, nil
}

func (s *SqliteConsumer) Complete(ctx context.Context, taskID string) error {

	_, err := s.db.Exec(ctx, `
		UPDATE `+s.table+`
		SET status = 'Completed', lease_until = NULL
		WHERE task_id = $1
	`, taskID)
	if err != nil {
		return fmt.Errorf("completing task: %w", err)
	}
	return nil
}

func (s *SqliteConsumer) Retry(ctx context.Context, taskID string, runAfter time.Time) error {

	_, err := s.db.Exec(ctx, `
		UPDATE `+s.table+`
		SET status = 'Pending', lease_until = NULL, run_after = $1
		WHERE task_id = $2
	`, runAfter.UTC().Format(sqliteTimeFormat), taskID)
	if err != nil {
		return fmt.Errorf("retrying task: %w", err)
	}
	return nil
}

func (s *SqliteConsumer) Fail(ctx context.Context, taskID string) error {

	_, err := s.db.Exec(ctx, `
		UPDATE `+s.table+`
		SET status = 'Failed', lease_until = NULL
		WHERE task_id = $1
	`, taskID)
	if err != nil {
		return fmt.Errorf("failing task: %w", err)
	}
	return nil
}