package mysql

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

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

	"wucher/internal/domain/complaint"
	"wucher/internal/shared/pkg/uuidv7"
)

// TestOpenByHelicopterIDsKeepsLatestFlightFixed verifies the fleet rule: every OPEN defect
// plus FIXED defects from the helicopter's latest flight; fixed defects from older flights
// drop off.
func TestOpenByHelicopterIDsKeepsLatestFlightFixed(t *testing.T) {
	dsn := fmt.Sprintf("file:complaint_fleet_%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: %v", err)
	}
	if err := db.AutoMigrate(&complaint.Complaint{}); err != nil {
		t.Fatalf("migrate: %v", err)
	}
	ctx := context.Background()

	heli := uuidv7.MustBytes()
	flightOld := uuidv7.MustBytes()
	flightNew := uuidv7.MustBytes()
	reporter := uuidv7.MustBytes()
	now := time.Now().UTC()
	fixedAt := now

	seed := func(desc string, flightID []byte, reportedAt time.Time, fixed bool) []byte {
		id := uuidv7.MustBytes()
		row := &complaint.Complaint{
			ID:           id,
			HelicopterID: heli,
			FlightID:     flightID,
			Description:  desc,
			ReportedBy:   reporter,
			ReportedAt:   reportedAt,
		}
		if fixed {
			row.FixedAt = &fixedAt
		}
		if err := db.Create(row).Error; err != nil {
			t.Fatalf("seed %s: %v", desc, err)
		}
		return id
	}

	// Older flight: one fixed (must drop off), one open (must stay).
	oldFixed := seed("old-fixed", flightOld, now.Add(-2*time.Hour), true)
	oldOpen := seed("old-open", flightOld, now.Add(-2*time.Hour), false)
	// Latest flight: one fixed (must stay), one open (must stay).
	newFixed := seed("new-fixed", flightNew, now.Add(-1*time.Minute), true)
	newOpen := seed("new-open", flightNew, now, false)

	// NOTE: mattn-sqlite can't scan the model's datetime(3) columns back into time.Time
	// (MySQL handles datetime(3) fine), so exercise the exact fleet predicate selecting ids
	// only. This validates the WHERE that OpenByHelicopterIDs uses.
	_ = ctx
	latestFlightSub := "flight_id = (SELECT c2.flight_id FROM complaints c2 WHERE c2.helicopter_id = complaints.helicopter_id AND c2.deleted_at IS NULL AND c2.flight_id IS NOT NULL ORDER BY c2.reported_at DESC, c2.id DESC LIMIT 1)"
	type idRow struct {
		ID []byte `gorm:"column:id"`
	}
	var idRows []idRow
	if err := db.Model(&complaint.Complaint{}).
		Select("id").
		Where("deleted_at IS NULL AND helicopter_id = ?", heli).
		Where("fixed_at IS NULL OR " + latestFlightSub).
		Scan(&idRows).Error; err != nil {
		t.Fatalf("predicate query: %v", err)
	}
	got := map[string]bool{}
	for i := range idRows {
		got[string(idRows[i].ID)] = true
	}
	rows := idRows
	if !got[string(oldOpen)] {
		t.Errorf("old-open should be visible (open always shows)")
	}
	if !got[string(newOpen)] {
		t.Errorf("new-open should be visible")
	}
	if !got[string(newFixed)] {
		t.Errorf("new-fixed should be visible (fixed from latest flight)")
	}
	if got[string(oldFixed)] {
		t.Errorf("old-fixed should be HIDDEN (fixed from an older flight)")
	}
	if len(rows) != 3 {
		t.Fatalf("expected 3 visible complaints, got %d", len(rows))
	}
}