package mysql

import (
	"context"
	"fmt"
	"strings"
	"testing"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"

	afterflightinspection "wucher/internal/domain/after_flight_inspection"
	filemanager "wucher/internal/domain/file_manager"
	"wucher/internal/domain/helicopter"
	"wucher/internal/shared/pkg/uuidv7"
)

func intPtr(v int) *int {
	return &v
}

func openHelicopterTestDB(t *testing.T) *gorm.DB {
	t.Helper()
	dsn := fmt.Sprintf("file:helicopter_test_%d?mode=memory&cache=shared", time.Now().UnixNano())
	db, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{
		NowFunc: func() time.Time { return time.Now().UTC() },
	})
	if err != nil {
		t.Fatalf("open sqlite: %v", err)
	}
	if err := db.AutoMigrate(&filemanager.Folder{}, &filemanager.File{}, &filemanager.Attachment{}, &helicopter.Helicopter{}); err != nil {
		t.Fatalf("auto migrate: %v", err)
	}
	return db
}

func TestNewHelicopterRepository(t *testing.T) {
	db := openHelicopterTestDB(t)
	repo := NewHelicopterRepository(db)
	if repo == nil || repo.db == nil {
		t.Fatalf("expected repository initialized")
	}
	if repo.hasUsersTable {
		t.Fatalf("expected users table cache to be false in base helicopter test db")
	}
}

func TestHelicopterRepositoryCachesUsersTablePresence(t *testing.T) {
	db := openHelicopterTestDB(t)
	if err := db.Exec(`CREATE TABLE users (
		id BLOB PRIMARY KEY,
		first_name TEXT,
		last_name TEXT,
		username TEXT,
		email TEXT
	)`).Error; err != nil {
		t.Fatalf("create users table: %v", err)
	}

	repo := NewHelicopterRepository(db)
	if !repo.hasUsersTable {
		t.Fatalf("expected users table cache to be true")
	}

	creatorID := uuidv7.MustBytes()
	updaterID := uuidv7.MustBytes()
	if err := db.Table("users").Create([]map[string]any{
		{
			"id":         creatorID,
			"first_name": "Ada",
			"last_name":  "Lovelace",
			"username":   "ada",
			"email":      "ada@example.com",
		},
		{
			"id":         updaterID,
			"first_name": "Grace",
			"last_name":  "Hopper",
			"username":   "grace",
			"email":      "grace@example.com",
		},
	}).Error; err != nil {
		t.Fatalf("seed users: %v", err)
	}

	row := &helicopter.Helicopter{
		Designation: "H145",
		Identifier:  "PK-AUDIT",
		Type:        "Twin",
		CreatedBy:   creatorID,
		UpdatedBy:   updaterID,
	}
	if err := repo.Create(context.Background(), row); err != nil {
		t.Fatalf("seed helicopter: %v", err)
	}

	got, err := repo.GetByID(context.Background(), row.ID)
	if err != nil {
		t.Fatalf("get helicopter: %v", err)
	}
	if got == nil {
		t.Fatalf("expected helicopter row")
	}
	if got.CreatedByName != "Ada Lovelace" {
		t.Fatalf("unexpected created_by_name: %q", got.CreatedByName)
	}
	if got.UpdatedByName != "Grace Hopper" {
		t.Fatalf("unexpected updated_by_name: %q", got.UpdatedByName)
	}
}

func TestHelicopterRepositoryCreate(t *testing.T) {
	db := openHelicopterTestDB(t)
	repo := NewHelicopterRepository(db)
	row := &helicopter.Helicopter{Designation: "H145", Identifier: "PK-ABC", Type: "Twin"}

	if err := repo.Create(context.Background(), row); err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(row.ID) == 0 {
		t.Fatalf("expected id set")
	}
	inactive := &helicopter.Helicopter{Designation: "Inactive", Identifier: "PK-INACTIVE", Type: "Single", IsActive: false}
	if err := repo.Create(context.Background(), inactive); err != nil {
		t.Fatalf("create inactive: %v", err)
	}
	gotInactive, err := repo.GetByID(context.Background(), inactive.ID)
	if err != nil {
		t.Fatalf("get inactive: %v", err)
	}
	if gotInactive == nil || gotInactive.IsActive {
		t.Fatalf("expected inactive helicopter persisted as false")
	}

	sqlDB, _ := db.DB()
	_ = sqlDB.Close()
	if err := repo.Create(context.Background(), &helicopter.Helicopter{Designation: "AfterClose", Identifier: "PK-CLOSE", Type: "Twin"}); err == nil {
		t.Fatalf("expected error on closed DB")
	}
}

func TestHelicopterRepositoryUpdate(t *testing.T) {
	db := openHelicopterTestDB(t)
	repo := NewHelicopterRepository(db)
	row := &helicopter.Helicopter{Designation: "Old", Identifier: "PK-OLD", Type: "Single"}
	if err := repo.Create(context.Background(), row); err != nil {
		t.Fatalf("seed create: %v", err)
	}

	row.Designation = "New"
	row.Type = "Twin"
	if err := repo.Update(context.Background(), row); err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	loaded, err := repo.GetByID(context.Background(), row.ID)
	if err != nil || loaded == nil || loaded.Designation != "New" || loaded.Type != "Twin" {
		t.Fatalf("expected updated row")
	}

	sqlDB, _ := db.DB()
	_ = sqlDB.Close()
	if err := repo.Update(context.Background(), row); err == nil {
		t.Fatalf("expected error on closed DB")
	}
}

func TestHelicopterRepositoryUpdate_ReplacesFotoAttachmentID(t *testing.T) {
	db := openHelicopterTestDB(t)
	repo := NewHelicopterRepository(db)
	now := time.Now().UTC()

	folder := &filemanager.Folder{
		ID:             uuidv7.MustBytes(),
		Name:           "photos",
		NameNormalized: "photos",
		Depth:          0,
		PathCache:      "/photos",
		NameSlot:       "live",
		CreatedAt:      now,
		UpdatedAt:      now,
	}
	if err := db.Create(folder).Error; err != nil {
		t.Fatalf("create folder: %v", err)
	}

	makeFile := func(name, key string) *filemanager.File {
		return &filemanager.File{
			ID:             uuidv7.MustBytes(),
			FolderID:       folder.ID,
			Name:           name,
			NameNormalized: strings.ToLower(name),
			Extension:      "webp",
			SizeBytes:      1234,
			MimeType:       "image/webp",
			Bucket:         "bucket",
			ObjectKey:      key,
			Status:         filemanager.FileStatusReady,
			NameSlot:       "live",
			CreatedAt:      now,
			UpdatedAt:      now,
		}
	}
	oldFile := makeFile("old.webp", "objects/old.webp")
	newFile := makeFile("new.webp", "objects/new.webp")
	if err := db.Create(oldFile).Error; err != nil {
		t.Fatalf("create old file: %v", err)
	}
	if err := db.Create(newFile).Error; err != nil {
		t.Fatalf("create new file: %v", err)
	}

	oldAttachment := &filemanager.Attachment{
		ID:        uuidv7.MustBytes(),
		FileID:    oldFile.ID,
		RefType:   "helicopter_photo",
		RefID:     "helicopter-1",
		IsPrimary: true,
		CreatedAt: now,
		UpdatedAt: now,
	}
	newAttachment := &filemanager.Attachment{
		ID:        uuidv7.MustBytes(),
		FileID:    newFile.ID,
		RefType:   "helicopter_photo",
		RefID:     "helicopter-1",
		IsPrimary: true,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if err := db.Create(oldAttachment).Error; err != nil {
		t.Fatalf("create old attachment: %v", err)
	}
	if err := db.Create(newAttachment).Error; err != nil {
		t.Fatalf("create new attachment: %v", err)
	}

	row := &helicopter.Helicopter{
		ID:               uuidv7.MustBytes(),
		Designation:      "Old",
		Identifier:       "PK-OLD-ATT",
		Type:             "Single",
		FotoAttachmentID: oldAttachment.ID,
		FotoAttachment:   oldAttachment,
		CreatedAt:        now,
		UpdatedAt:        now,
	}
	if err := repo.Create(context.Background(), row); err != nil {
		t.Fatalf("seed create helicopter: %v", err)
	}

	row.FotoAttachmentID = newAttachment.ID
	row.FotoAttachment = oldAttachment
	if err := repo.Update(context.Background(), row); err != nil {
		t.Fatalf("update helicopter: %v", err)
	}

	loaded, err := repo.GetByID(context.Background(), row.ID)
	if err != nil {
		t.Fatalf("get by id after update: %v", err)
	}
	if loaded == nil {
		t.Fatalf("expected updated row")
	}
	if string(loaded.FotoAttachmentID) != string(newAttachment.ID) {
		t.Fatalf("expected foto_attachment_id replaced")
	}
}

func TestHelicopterRepositoryDelete(t *testing.T) {
	db := openHelicopterTestDB(t)
	repo := NewHelicopterRepository(db)
	row := &helicopter.Helicopter{Designation: "DeleteMe", Identifier: "PK-DEL", Type: "Twin"}
	if err := repo.Create(context.Background(), row); err != nil {
		t.Fatalf("seed create: %v", err)
	}

	if err := repo.Delete(context.Background(), row.ID); err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	got, err := repo.GetByID(context.Background(), row.ID)
	if err != nil {
		t.Fatalf("get by id after delete: %v", err)
	}
	if got != nil {
		t.Fatalf("expected row deleted")
	}
}

func TestHelicopterRepositoryGetByID(t *testing.T) {
	t.Run("found", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		row := &helicopter.Helicopter{Designation: "Found", Identifier: "PK-FND", Type: "Twin"}
		if err := repo.Create(context.Background(), row); err != nil {
			t.Fatalf("seed create: %v", err)
		}

		got, err := repo.GetByID(context.Background(), row.ID)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if got == nil || got.Identifier != "PK-FND" {
			t.Fatalf("expected row found")
		}
	})

	t.Run("not found", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		got, err := repo.GetByID(context.Background(), uuidv7.MustBytes())
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if got != nil {
			t.Fatalf("expected nil for not found")
		}
	})

	t.Run("db error", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		sqlDB, _ := db.DB()
		_ = sqlDB.Close()

		if _, err := repo.GetByID(context.Background(), uuidv7.MustBytes()); err == nil {
			t.Fatalf("expected error on closed DB")
		}
	})
}

func TestHelicopterRepositoryList(t *testing.T) {
	t.Run("success without limit", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "H145", Identifier: "PK-A", Type: "Twin"})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "H125", Identifier: "PK-B", Type: "Single"})

		rows, total, err := repo.List(context.Background(), "", nil, "", 0, 0, nil)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if total != 2 || len(rows) != 2 {
			t.Fatalf("expected 2 rows, total=%d len=%d", total, len(rows))
		}
	})

	t.Run("success with filter sort and limit", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Main", Identifier: "PK-MAIN", Type: "Twin"})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Backup", Identifier: "PK-BACK", Type: "Single"})

		rows, total, err := repo.List(context.Background(), "MAIN", nil, "identifier DESC", 1, 0, nil)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if total != 1 || len(rows) != 1 || rows[0].Identifier != "PK-MAIN" {
			t.Fatalf("unexpected list result")
		}
	})

	t.Run("default order active sortkey first and inactive last", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Gamma", Identifier: "PK-G", Type: "Twin", IsActive: true})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Beta", Identifier: "PK-B0", Type: "Twin", SortKey: intPtr(0), IsActive: true})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Charlie", Identifier: "PK-C", Type: "Twin", SortKey: intPtr(2), IsActive: true})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Alpha", Identifier: "PK-A1", Type: "Twin", IsActive: true})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Alpha", Identifier: "PK-A2", Type: "Twin", SortKey: intPtr(1), IsActive: true})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Zulu", Identifier: "PK-Z", Type: "Twin", IsActive: false})
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Bravo", Identifier: "PK-B", Type: "Twin", IsActive: false, SortKey: intPtr(9)})

		rows, total, err := repo.List(context.Background(), "", nil, "", 0, 0, nil)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if total != 7 || len(rows) != 7 {
			t.Fatalf("unexpected total/len total=%d len=%d", total, len(rows))
		}

		gotOrder := []string{
			rows[0].Designation + "|" + rows[0].Identifier,
			rows[1].Designation + "|" + rows[1].Identifier,
			rows[2].Designation + "|" + rows[2].Identifier,
			rows[3].Designation + "|" + rows[3].Identifier,
			rows[4].Designation + "|" + rows[4].Identifier,
			rows[5].Designation + "|" + rows[5].Identifier,
			rows[6].Designation + "|" + rows[6].Identifier,
		}
		wantOrder := []string{
			"Beta|PK-B0",
			"Alpha|PK-A2",
			"Charlie|PK-C",
			"Alpha|PK-A1",
			"Gamma|PK-G",
			"Bravo|PK-B",
			"Zulu|PK-Z",
		}
		for i := range wantOrder {
			if gotOrder[i] != wantOrder[i] {
				t.Fatalf("unexpected default order: got=%v want=%v", gotOrder, wantOrder)
			}
		}
	})

	t.Run("status filter", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)

		// Minimal schema for the booked/available EXISTS subquery.
		if err := db.Exec(`CREATE TABLE reserve_acs (id BLOB PRIMARY KEY, helicopter_id BLOB, inspection_id BLOB, deleted_at DATETIME)`).Error; err != nil {
			t.Fatalf("create reserve_acs: %v", err)
		}
		if err := db.Exec(`CREATE TABLE takeover_acs (id BLOB PRIMARY KEY, reserve_ac_id BLOB, deleted_at DATETIME)`).Error; err != nil {
			t.Fatalf("create takeover_acs: %v", err)
		}
		if err := db.Exec(`CREATE TABLE flights (id BLOB PRIMARY KEY, reserve_ac_id BLOB, takeover_ac_id BLOB, deleted_at DATETIME)`).Error; err != nil {
			t.Fatalf("create flights: %v", err)
		}
		if err := db.AutoMigrate(&afterflightinspection.AfterFlightInspection{}); err != nil {
			t.Fatalf("auto migrate after flight inspection: %v", err)
		}

		aog := &helicopter.Helicopter{Designation: "AOG", Identifier: "PK-AOG", Type: "Twin", AirOnGround: true}
		booked := &helicopter.Helicopter{Designation: "Booked", Identifier: "PK-BKD", Type: "Twin"}
		closed := &helicopter.Helicopter{Designation: "Closed", Identifier: "PK-CLS", Type: "Twin"}
		available := &helicopter.Helicopter{Designation: "Avail", Identifier: "PK-AVL", Type: "Twin"}
		for _, h := range []*helicopter.Helicopter{aog, booked, closed, available} {
			if err := repo.Create(context.Background(), h); err != nil {
				t.Fatalf("create helicopter: %v", err)
			}
		}

		// Give "booked" an active (non-deleted) flight assignment.
		raID := uuidv7.MustBytes()
		takeoverID := uuidv7.MustBytes()
		if err := db.Table("reserve_acs").Create(map[string]any{"id": raID, "helicopter_id": booked.ID}).Error; err != nil {
			t.Fatalf("insert reserve_ac: %v", err)
		}
		if err := db.Table("takeover_acs").Create(map[string]any{"id": takeoverID, "reserve_ac_id": raID}).Error; err != nil {
			t.Fatalf("insert takeover_ac: %v", err)
		}
		if err := db.Table("flights").Create(map[string]any{"id": uuidv7.MustBytes(), "takeover_ac_id": takeoverID}).Error; err != nil {
			t.Fatalf("insert flight: %v", err)
		}
		closedRAID := uuidv7.MustBytes()
		closedTakeoverID := uuidv7.MustBytes()
		closedInspectionID := uuidv7.MustBytes()
		if err := db.Table("reserve_acs").Create(map[string]any{"id": closedRAID, "helicopter_id": closed.ID, "inspection_id": closedInspectionID}).Error; err != nil {
			t.Fatalf("insert closed reserve_ac: %v", err)
		}
		if err := db.Table("takeover_acs").Create(map[string]any{"id": closedTakeoverID, "reserve_ac_id": closedRAID}).Error; err != nil {
			t.Fatalf("insert closed takeover_ac: %v", err)
		}
		if err := db.Table("flights").Create(map[string]any{"id": uuidv7.MustBytes(), "takeover_ac_id": closedTakeoverID}).Error; err != nil {
			t.Fatalf("insert closed flight: %v", err)
		}
		if err := db.Create(&afterflightinspection.AfterFlightInspection{FlightInspectionID: closedInspectionID, CreatedAt: time.Now().UTC(), UpdatedAt: time.Now().UTC()}).Error; err != nil {
			t.Fatalf("insert after flight inspection: %v", err)
		}

		cases := []struct {
			name        string
			statuses    []string
			groundedIDs [][]byte
			want        []string
		}{
			{name: "aog only", statuses: []string{"aog"}, want: []string{"PK-AOG"}},
			{name: "booked only", statuses: []string{"booked"}, want: []string{"PK-BKD"}},
			{name: "available only", statuses: []string{"available"}, want: []string{"PK-AVL", "PK-CLS"}},
			{name: "available and booked", statuses: []string{"available", "booked"}, want: []string{"PK-AVL", "PK-BKD", "PK-CLS"}},
			{name: "mcf matches nothing", statuses: []string{"mcf"}, want: []string{}},
			{name: "no filter returns all", statuses: nil, want: []string{"PK-AOG", "PK-AVL", "PK-BKD", "PK-CLS"}},
			{name: "complaint-grounded excluded from available", statuses: []string{"available"}, groundedIDs: [][]byte{available.ID}, want: []string{"PK-CLS"}},
			{name: "complaint-grounded appears as aog", statuses: []string{"aog"}, groundedIDs: [][]byte{available.ID}, want: []string{"PK-AOG", "PK-AVL"}},
		}

		for _, tc := range cases {
			t.Run(tc.name, func(t *testing.T) {
				rows, total, err := repo.List(context.Background(), "", tc.statuses, "identifier ASC", 0, 0, tc.groundedIDs)
				if err != nil {
					t.Fatalf("list: %v", err)
				}
				if int(total) != len(tc.want) || len(rows) != len(tc.want) {
					t.Fatalf("count mismatch: total=%d len=%d want=%d", total, len(rows), len(tc.want))
				}
				got := make([]string, len(rows))
				for i := range rows {
					got[i] = rows[i].Identifier
				}
				for i := range tc.want {
					if got[i] != tc.want[i] {
						t.Fatalf("unexpected rows: got=%v want=%v", got, tc.want)
					}
				}
			})
		}
	})

	t.Run("count error", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		if err := db.Migrator().DropTable(&helicopter.Helicopter{}); err != nil {
			t.Fatalf("drop table: %v", err)
		}

		if _, _, err := repo.List(context.Background(), "", nil, "", 10, 0, nil); err == nil {
			t.Fatalf("expected count error")
		}
	})

	t.Run("find error", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		_ = repo.Create(context.Background(), &helicopter.Helicopter{Designation: "Main", Identifier: "PK-ERR", Type: "Twin"})

		if _, _, err := repo.List(context.Background(), "", nil, "designation ASC, )", 10, 0, nil); err == nil {
			t.Fatalf("expected find error from invalid sort")
		}
	})
}

func TestHelicopterRepositoryNextReportNumber(t *testing.T) {
	t.Run("success", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		row := &helicopter.Helicopter{
			Designation:    "H145",
			Identifier:     " PK-ABC ",
			Type:           "Twin",
			ReportSequence: 7,
		}
		if err := repo.Create(context.Background(), row); err != nil {
			t.Fatalf("seed create: %v", err)
		}

		reportNumber, sequence, err := repo.NextReportNumber(context.Background(), row.ID)
		if err != nil {
			t.Fatalf("unexpected error: %v", err)
		}
		if reportNumber != "PK-ABC-0008" || sequence != 8 {
			t.Fatalf("unexpected report number/sequence: %q %d", reportNumber, sequence)
		}
		loaded, err := repo.GetByID(context.Background(), row.ID)
		if err != nil || loaded == nil || loaded.ReportSequence != 8 {
			t.Fatalf("expected report_sequence updated")
		}
	})

	t.Run("identifier empty", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		row := &helicopter.Helicopter{
			Designation: "H145",
			Identifier:  "   ",
			Type:        "Twin",
		}
		if err := repo.Create(context.Background(), row); err != nil {
			t.Fatalf("seed create: %v", err)
		}

		if _, _, err := repo.NextReportNumber(context.Background(), row.ID); err == nil {
			t.Fatalf("expected identifier empty error")
		}
	})

	t.Run("not found", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		if _, _, err := repo.NextReportNumber(context.Background(), uuidv7.MustBytes()); err == nil {
			t.Fatalf("expected not found error")
		}
	})

	t.Run("db error", func(t *testing.T) {
		db := openHelicopterTestDB(t)
		repo := NewHelicopterRepository(db)
		sqlDB, _ := db.DB()
		_ = sqlDB.Close()

		if _, _, err := repo.NextReportNumber(context.Background(), uuidv7.MustBytes()); err == nil {
			t.Fatalf("expected error on closed DB")
		}
	})
}