package db

import (
	"context"
	"database/sql"
	"path/filepath"
	"testing"
	"time"

	"github.com/bluesky-social/indigo/atproto/syntax"
)

func TestWorkflowSchedulesTrackIndexedRepositoriesIncludingEmptyManifests(t *testing.T) {
	ctx := context.Background()
	d := newTestDB(t)
	repo := Repo{
		Knot:    "knot.test",
		Owner:   syntax.DID("did:plc:owner"),
		Rkey:    syntax.RecordKey("repo"),
		RepoDid: syntax.DID("did:plc:repo"),
	}
	if err := d.AddRepo(repo); err != nil {
		t.Fatalf("AddRepo: %v", err)
	}

	unindexed, err := d.UnindexedScheduleRepos(ctx)
	if err != nil {
		t.Fatalf("UnindexedScheduleRepos: %v", err)
	}
	if len(unindexed) != 1 {
		t.Fatalf("unindexed repositories = %d, want 1", len(unindexed))
	}

	schedules := []WorkflowSchedule{
		{RepoDid: repo.RepoDid.String(), Workflow: "build.yml", Expression: "0 9 * * *", Timezone: "UTC"},
		{RepoDid: repo.RepoDid.String(), Workflow: "build.yml", Expression: "0 9 * * *", Timezone: "UTC"},
		{RepoDid: repo.RepoDid.String(), Workflow: "build.yml", Expression: "0 9 * * *", Timezone: "America/New_York"},
		{RepoDid: repo.RepoDid.String(), Workflow: "nightly.yml", Expression: "0 3 * * *"},
	}
	if err := d.ReplaceWorkflowSchedules(ctx, ScheduledRepo{
		RepoDid: repo.RepoDid.String(),
		Branch:  "main",
		SHA:     "sha-1",
	}, schedules, time.Now()); err != nil {
		t.Fatalf("ReplaceWorkflowSchedules: %v", err)
	}
	persisted, err := d.WorkflowSchedules(ctx)
	if err != nil {
		t.Fatalf("WorkflowSchedules: %v", err)
	}
	if len(persisted) != 3 {
		t.Fatalf("persisted schedules = %d, want 3 deduplicated definitions", len(persisted))
	}
	if persisted[0].Timezone != "America/New_York" || persisted[1].Timezone != "UTC" || persisted[2].Timezone != "UTC" {
		t.Fatalf("persisted timezones = %q, %q, %q", persisted[0].Timezone, persisted[1].Timezone, persisted[2].Timezone)
	}
	for i, schedule := range persisted {
		if schedule.Branch != "main" || schedule.SHA != "sha-1" {
			t.Fatalf("persisted snapshot at index %d = branch %q, sha %q; want main/sha-1", i, schedule.Branch, schedule.SHA)
		}
	}

	if err := d.ReplaceWorkflowSchedules(ctx, ScheduledRepo{
		RepoDid: repo.RepoDid.String(),
		Branch:  "main",
		SHA:     "sha-2",
	}, nil, time.Now()); err != nil {
		t.Fatalf("ReplaceWorkflowSchedules(empty): %v", err)
	}
	persisted, err = d.WorkflowSchedules(ctx)
	if err != nil {
		t.Fatalf("WorkflowSchedules after empty refresh: %v", err)
	}
	if len(persisted) != 0 {
		t.Fatalf("persisted schedules after empty refresh = %d, want 0", len(persisted))
	}
	var branch, sha string
	if err := d.QueryRow(
		`select branch, sha from scheduled_repos where repo_did = ?`,
		repo.RepoDid.String(),
	).Scan(&branch, &sha); err != nil {
		t.Fatalf("query snapshot after empty refresh: %v", err)
	}
	if branch != "main" || sha != "sha-2" {
		t.Fatalf("snapshot after empty refresh = branch %q, sha %q; want main/sha-2", branch, sha)
	}
	unindexed, err = d.UnindexedScheduleRepos(ctx)
	if err != nil {
		t.Fatalf("UnindexedScheduleRepos after empty refresh: %v", err)
	}
	if len(unindexed) != 0 {
		t.Fatalf("empty but indexed repository returned for backfill: %#v", unindexed)
	}

	if err := d.RemoveWorkflowSchedules(ctx, repo.RepoDid.String()); err != nil {
		t.Fatalf("RemoveWorkflowSchedules: %v", err)
	}
	unindexed, err = d.UnindexedScheduleRepos(ctx)
	if err != nil {
		t.Fatalf("UnindexedScheduleRepos after removal: %v", err)
	}
	if len(unindexed) != 1 {
		t.Fatalf("unindexed repositories after removal = %d, want 1", len(unindexed))
	}
}

func TestWorkflowSchedulesMigrationDefaultsTimezoneToUTC(t *testing.T) {
	ctx := context.Background()
	path := filepath.Join(t.TempDir(), "spindle.db")
	legacy, err := sql.Open("sqlite3", path)
	if err != nil {
		t.Fatalf("open legacy database: %v", err)
	}
	if _, err := legacy.Exec(`
		create table scheduled_repos (
			repo_did text primary key,
			refreshed_at integer not null
		);
		create table workflow_schedules (
			repo_did text not null,
			workflow text not null,
			expression text not null,
			primary key (repo_did, workflow, expression)
		);
		insert into scheduled_repos values ('did:plc:repo', 0);
		insert into workflow_schedules values ('did:plc:repo', 'build.yml', '0 9 * * *');
	`); err != nil {
		legacy.Close()
		t.Fatalf("seed legacy database: %v", err)
	}
	if err := legacy.Close(); err != nil {
		t.Fatalf("close legacy database: %v", err)
	}

	d, err := Make(ctx, path)
	if err != nil {
		t.Fatalf("migrate database: %v", err)
	}
	defer d.Close()

	repo := Repo{
		Knot:    "knot.test",
		Owner:   syntax.DID("did:plc:owner"),
		Rkey:    syntax.RecordKey("repo"),
		RepoDid: syntax.DID("did:plc:repo"),
	}
	if err := d.AddRepo(repo); err != nil {
		t.Fatalf("AddRepo: %v", err)
	}

	var timezone string
	if err := d.QueryRow(
		`select timezone from workflow_schedules where repo_did = ?`,
		repo.RepoDid.String(),
	).Scan(&timezone); err != nil {
		t.Fatalf("query migrated timezone: %v", err)
	}
	if timezone != "UTC" {
		t.Fatalf("migrated timezone = %q, want UTC", timezone)
	}

	var branch, sha string
	if err := d.QueryRow(
		`select branch, sha from scheduled_repos where repo_did = ?`,
		repo.RepoDid.String(),
	).Scan(&branch, &sha); err != nil {
		t.Fatalf("query migrated revision: %v", err)
	}
	if branch != "" || sha != "" {
		t.Fatalf("migrated revision = branch %q, sha %q, want empty legacy snapshot", branch, sha)
	}

	schedules, err := d.WorkflowSchedules(ctx)
	if err != nil {
		t.Fatalf("WorkflowSchedules: %v", err)
	}
	if len(schedules) != 0 {
		t.Fatalf("migrated incomplete schedules = %#v, want none", schedules)
	}

	unindexed, err := d.UnindexedScheduleRepos(ctx)
	if err != nil {
		t.Fatalf("UnindexedScheduleRepos: %v", err)
	}
	if len(unindexed) != 1 || unindexed[0].RepoDid != repo.RepoDid {
		t.Fatalf("migrated incomplete repos = %#v, want %s", unindexed, repo.RepoDid)
	}
}