package mysql

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

	sqlite3 "github.com/mattn/go-sqlite3"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"

	dutyroster "wucher/internal/domain/duty_roster"
	"wucher/internal/service"
	"wucher/internal/shared/pkg/uuidv7"
)

func openDutyRosterRepoTestDB(t *testing.T) *gorm.DB {
	t.Helper()
	dsn := fmt.Sprintf("file:duty_roster_repo_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)
	}

	createRoster := `
CREATE TABLE duty_rosters (
	id BLOB PRIMARY KEY,
	flight_id BLOB NULL,
	base_id BLOB NOT NULL,
	duty_date DATETIME NOT NULL,
	shift_start DATETIME NULL,
	shift_end DATETIME NULL,
	helicopter_id BLOB NULL,
	created_at DATETIME NULL,
	created_by BLOB NULL,
	updated_at DATETIME NULL,
	updated_by BLOB NULL,
	deleted_at DATETIME NULL,
	deleted_by BLOB NULL
);`
	if err := db.Exec(createRoster).Error; err != nil {
		t.Fatalf("create duty_rosters: %v", err)
	}
	if err := db.Exec("CREATE UNIQUE INDEX idx_roster_base_date ON duty_rosters(base_id, duty_date)").Error; err != nil {
		t.Fatalf("create idx_roster_base_date: %v", err)
	}

	createCrew := `
CREATE TABLE duty_roster_crews (
	id BLOB PRIMARY KEY,
	roster_id BLOB NOT NULL,
	takeover_ac_id BLOB NULL,
	user_id BLOB NULL,
	role_code TEXT NOT NULL,
	crew_type TEXT NOT NULL,
	name_label TEXT NULL,
	mobile_phone TEXT NULL,
	email TEXT NULL,
	confirm_at DATETIME NULL,
	date_start DATE NULL,
	date_end DATE NULL,
	shift_start DATETIME NULL,
	shift_end DATETIME NULL,
	flight_instructor INTEGER NOT NULL DEFAULT 0,
	line_checker INTEGER NOT NULL DEFAULT 0,
	supervisor INTEGER NOT NULL DEFAULT 0,
	examiner INTEGER NOT NULL DEFAULT 0,
	co_pilot INTEGER NOT NULL DEFAULT 0,
	created_at DATETIME NULL,
	created_by BLOB NULL,
	updated_at DATETIME NULL,
	updated_by BLOB NULL,
	deleted_at DATETIME NULL,
	deleted_by BLOB NULL
);`
	if err := db.Exec(createCrew).Error; err != nil {
		t.Fatalf("create duty_roster_crews: %v", err)
	}
	registerSQLiteTimeFormatCompat(t, db)

	return db
}

func registerSQLiteTimeFormatCompat(t *testing.T, db *gorm.DB) {
	t.Helper()
	sqlDB, err := db.DB()
	if err != nil {
		t.Fatalf("db.DB: %v", err)
	}
	conn, err := sqlDB.Conn(context.Background())
	if err != nil {
		t.Fatalf("sqlDB.Conn: %v", err)
	}
	defer conn.Close()

	if err := conn.Raw(func(driverConn any) error {
		raw, ok := driverConn.(*sqlite3.SQLiteConn)
		if !ok {
			return fmt.Errorf("unexpected sqlite conn type %T", driverConn)
		}
		return raw.RegisterFunc("TIME_FORMAT", func(v any, pattern string) string {
			if pattern != "%H:%i:%s" {
				return ""
			}
			var rawVal string
			switch x := v.(type) {
			case time.Time:
				return x.UTC().Format("15:04:05")
			case []byte:
				rawVal = string(x)
			case string:
				rawVal = x
			default:
				rawVal = fmt.Sprint(x)
			}
			rawVal = strings.TrimSpace(rawVal)
			if rawVal == "" {
				return ""
			}
			layouts := []string{
				"2006-01-02 15:04:05.999999999-07:00",
				"2006-01-02 15:04:05.999999999",
				"2006-01-02 15:04:05",
				time.RFC3339,
				"15:04:05",
			}
			for _, layout := range layouts {
				if tm, err := time.Parse(layout, rawVal); err == nil {
					return tm.UTC().Format("15:04:05")
				}
			}
			if len(rawVal) >= 8 {
				return rawVal[len(rawVal)-8:]
			}
			return ""
		}, true)
	}); err != nil {
		t.Fatalf("register TIME_FORMAT: %v", err)
	}
}

func dutyRosterTestCategoryIDs() (regularID, hemsID []byte) {
	return []byte("catregular000001"), []byte("cathems000000001")
}

func TestDutyRosterRepositoryUpdate_ReplaceSameUserAppendDifferentUser(t *testing.T) {
	db := openDutyRosterRepoTestDB(t)
	repo := NewDutyRosterRepository(db)
	svc := service.NewDutyRosterService(repo)
	ctx := context.Background()
	baseID := uuidv7.MustBytes()
	actor := uuidv7.MustBytes()
	userMain := uuidv7.MustBytes()
	userAdditional := uuidv7.MustBytes()
	date := time.Date(2026, 3, 20, 0, 0, 0, 0, time.UTC)

	row := &dutyroster.DutyRoster{
		BaseID:    baseID,
		DutyDate:  date,
		CreatedBy: actor,
		UpdatedBy: actor,
	}
	if err := svc.Create(ctx, row, nil); err != nil {
		t.Fatalf("seed create roster: %v", err)
	}
	if err := db.Create(&dutyroster.DutyRosterCrew{
		RosterID:  row.ID,
		UserID:    userMain,
		RoleCode:  "pilot",
		CrewType:  "main",
		CreatedBy: actor,
		UpdatedBy: actor,
	}).Error; err != nil {
		t.Fatalf("seed old crew: %v", err)
	}

	updateRow := &dutyroster.DutyRoster{
		ID:        row.ID,
		BaseID:    baseID,
		DutyDate:  date,
		UpdatedBy: actor,
	}
	updateCrews := []dutyroster.DutyRosterCrew{
		{
			UserID:   userMain,
			RoleCode: "pilot",
			CrewType: "main",
		},
		{
			UserID:   userAdditional,
			RoleCode: "pilot",
			CrewType: "additional",
		},
	}
	if err := svc.Update(ctx, updateRow, updateCrews); err != nil {
		t.Fatalf("update roster: %v", err)
	}

	var activeMain []dutyroster.DutyRosterCrew
	if err := db.Where("roster_id = ? AND user_id = ? AND role_code = 'pilot' AND crew_type = 'main' AND deleted_at IS NULL", row.ID, userMain).
		Find(&activeMain).Error; err != nil {
		t.Fatalf("query active main: %v", err)
	}
	if len(activeMain) != 1 {
		t.Fatalf("expected 1 active main crew, got %d", len(activeMain))
	}

	var historicalMain int64
	if err := db.Model(&dutyroster.DutyRosterCrew{}).
		Where("roster_id = ? AND user_id = ? AND role_code = 'pilot' AND crew_type = 'main'", row.ID, userMain).
		Count(&historicalMain).Error; err != nil {
		t.Fatalf("count main history: %v", err)
	}
	if historicalMain != 2 {
		t.Fatalf("expected 2 historical main rows (old deleted + new active), got %d", historicalMain)
	}

	var activeAdditional int64
	if err := db.Model(&dutyroster.DutyRosterCrew{}).
		Where("roster_id = ? AND user_id = ? AND role_code = 'pilot' AND crew_type = 'additional' AND deleted_at IS NULL", row.ID, userAdditional).
		Count(&activeAdditional).Error; err != nil {
		t.Fatalf("count active additional: %v", err)
	}
	if activeAdditional != 1 {
		t.Fatalf("expected appended additional crew, got %d", activeAdditional)
	}
}

func TestDutyRosterRepositoryUpdate_RangeShrinkRemovesOutOfRangeAssignments(t *testing.T) {
	db := openDutyRosterRepoTestDB(t)
	repo := NewDutyRosterRepository(db)
	svc := service.NewDutyRosterService(repo)
	ctx := context.Background()
	baseID := uuidv7.MustBytes()
	actor := uuidv7.MustBytes()
	userID := uuidv7.MustBytes()
	d20 := time.Date(2026, 3, 20, 0, 0, 0, 0, time.UTC)
	d21 := time.Date(2026, 3, 21, 0, 0, 0, 0, time.UTC)
	d22 := time.Date(2026, 3, 22, 0, 0, 0, 0, time.UTC)

	row20 := &dutyroster.DutyRoster{BaseID: baseID, DutyDate: d20, CreatedBy: actor, UpdatedBy: actor}
	row21 := &dutyroster.DutyRoster{BaseID: baseID, DutyDate: d21, CreatedBy: actor, UpdatedBy: actor}
	row22 := &dutyroster.DutyRoster{BaseID: baseID, DutyDate: d22, CreatedBy: actor, UpdatedBy: actor}
	if err := svc.Create(ctx, row20, nil); err != nil {
		t.Fatalf("create roster d20: %v", err)
	}
	if err := svc.Create(ctx, row21, nil); err != nil {
		t.Fatalf("create roster d21: %v", err)
	}
	if err := svc.Create(ctx, row22, nil); err != nil {
		t.Fatalf("create roster d22: %v", err)
	}

	startOld := d20
	endOld := d22
	seedCrew := func(rosterID []byte) {
		if err := db.Create(&dutyroster.DutyRosterCrew{
			RosterID:  rosterID,
			UserID:    userID,
			RoleCode:  "pilot",
			CrewType:  "main",
			DateStart: &startOld,
			DateEnd:   &endOld,
			CreatedBy: actor,
			UpdatedBy: actor,
		}).Error; err != nil {
			t.Fatalf("seed crew: %v", err)
		}
	}
	seedCrew(row20.ID)
	seedCrew(row21.ID)
	seedCrew(row22.ID)

	startNew := d20
	endNew := d21
	updateRow := &dutyroster.DutyRoster{
		ID:        row20.ID,
		BaseID:    baseID,
		DutyDate:  d20,
		UpdatedBy: actor,
	}
	updateCrews := []dutyroster.DutyRosterCrew{
		{
			UserID:    userID,
			RoleCode:  "pilot",
			CrewType:  "main",
			DateStart: &startNew,
			DateEnd:   &endNew,
		},
	}
	if err := svc.Update(ctx, updateRow, updateCrews); err != nil {
		t.Fatalf("update range shrink: %v", err)
	}

	var active22 int64
	if err := db.Model(&dutyroster.DutyRosterCrew{}).
		Where("roster_id = ? AND user_id = ? AND role_code = 'pilot' AND crew_type = 'main' AND deleted_at IS NULL", row22.ID, userID).
		Count(&active22).Error; err != nil {
		t.Fatalf("count active d22: %v", err)
	}
	if active22 != 1 {
		t.Fatalf("expected active assignment to remain on 2026-03-22 after shrink, got %d", active22)
	}

	var active21 int64
	if err := db.Model(&dutyroster.DutyRosterCrew{}).
		Where("roster_id = ? AND user_id = ? AND role_code = 'pilot' AND crew_type = 'main' AND deleted_at IS NULL", row21.ID, userID).
		Count(&active21).Error; err != nil {
		t.Fatalf("count active d21: %v", err)
	}
	if active21 != 1 {
		t.Fatalf("expected active assignment remains on 2026-03-21, got %d", active21)
	}
}

func openDutyRosterRepoCoverageDB(t *testing.T) *gorm.DB {
	t.Helper()
	dsn := fmt.Sprintf("file:duty_roster_repo_cov_%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)
	}

	stmts := []string{
		`CREATE TABLE duty_rosters (
			id BLOB PRIMARY KEY,
			base_id BLOB NOT NULL,
			duty_date DATETIME NOT NULL,
			shift_start DATETIME NULL,
			shift_end DATETIME NULL,
			helicopter_id BLOB NULL,
			created_at DATETIME NULL,
			created_by BLOB NULL,
			updated_at DATETIME NULL,
			updated_by BLOB NULL,
			deleted_at DATETIME NULL,
			deleted_by BLOB NULL
		);`,
		`CREATE UNIQUE INDEX idx_roster_base_date ON duty_rosters(base_id, duty_date);`,
		`CREATE TABLE duty_roster_crews (
			id BLOB PRIMARY KEY,
			roster_id BLOB NOT NULL,
			takeover_ac_id BLOB NULL,
			user_id BLOB NULL,
			role_code TEXT NOT NULL,
			crew_type TEXT NOT NULL,
			name_label TEXT NULL,
			mobile_phone TEXT NULL,
			email TEXT NULL,
			confirm_at DATETIME NULL,
			date_start DATE NULL,
			date_end DATE NULL,
			shift_start DATETIME NULL,
			shift_end DATETIME NULL,
			flight_instructor INTEGER NOT NULL DEFAULT 0,
			line_checker INTEGER NOT NULL DEFAULT 0,
			supervisor INTEGER NOT NULL DEFAULT 0,
			examiner INTEGER NOT NULL DEFAULT 0,
			co_pilot INTEGER NOT NULL DEFAULT 0,
			created_at DATETIME NULL,
			created_by BLOB NULL,
			updated_at DATETIME NULL,
			updated_by BLOB NULL,
			deleted_at DATETIME NULL,
			deleted_by BLOB NULL
		);`,
		"CREATE TABLE base_categories (\n\t\t\tid BLOB PRIMARY KEY,\n\t\t\t`key` TEXT UNIQUE,\n\t\t\tname TEXT\n\t\t);",
		`CREATE TABLE bases (
			id BLOB PRIMARY KEY,
			base_category_id BLOB NOT NULL,
			base TEXT,
			base_abbreviation TEXT,
			sortkey INTEGER NULL,
			is_active BOOLEAN DEFAULT 1,
			default_shift_start TEXT NULL,
			default_shift_end TEXT NULL,
			default_shift_time TEXT
		);`,
		`CREATE TABLE helicopters (
			id BLOB PRIMARY KEY,
			identifier TEXT,
			designation TEXT
		);`,
		`CREATE TABLE roles (
			id BLOB PRIMARY KEY,
			name TEXT
		);`,
		`CREATE TABLE users (
			id BLOB PRIMARY KEY,
			first_name TEXT,
			last_name TEXT,
			mobile_phone TEXT,
			email TEXT,
			role_id BLOB NULL
		);`,
	}
	for _, stmt := range stmts {
		if err := db.Exec(stmt).Error; err != nil {
			t.Fatalf("create stmt failed: %v\nsql=%s", err, stmt)
		}
	}
	regularCategoryID, hemsCategoryID := dutyRosterTestCategoryIDs()
	asBlob := func(b []byte) string { return fmt.Sprintf("X'%x'", b) }
	if err := db.Exec(
		fmt.Sprintf(
			"INSERT INTO base_categories(id, `key`, name) VALUES(%s, 'regular', 'Regular'),(%s, 'hems', 'HEMS')",
			asBlob(regularCategoryID),
			asBlob(hemsCategoryID),
		),
	).Error; err != nil {
		t.Fatalf("seed base_categories: %v", err)
	}
	registerSQLiteTimeFormatCompat(t, db)
	return db
}

func TestDutyRosterRepository_ReadListAndDeleteFlows(t *testing.T) {
	db := openDutyRosterRepoCoverageDB(t)
	repo := NewDutyRosterRepository(db)
	ctx := context.Background()

	hemsBaseID := []byte("hemsbase00000001")
	baseID := []byte("basebase00000001")
	regularCategoryID, hemsCategoryID := dutyRosterTestCategoryIDs()
	heliID := []byte("heliheli00000001")
	roleID := []byte("rolerole00000001")
	userID := []byte("useruser00000001")
	rosterID := []byte("rosterro00000001")
	crewID := []byte("crewcrew00000001")
	now := time.Now().UTC()
	dutyDate := time.Date(2026, 3, 20, 0, 0, 0, 0, time.UTC)

	mustExec := func(sql string, args ...any) {
		if err := db.Exec(sql, args...).Error; err != nil {
			t.Fatalf("exec failed: %v, sql=%s", err, sql)
		}
	}
	asBlob := func(b []byte) string { return fmt.Sprintf("X'%x'", b) }
	mustExec(fmt.Sprintf(`INSERT INTO bases(id, base_category_id, base, base_abbreviation, sortkey, is_active, default_shift_time) VALUES(%s, %s, ?, ?, 1, 1, ?)`, asBlob(hemsBaseID), asBlob(hemsCategoryID)),
		"HEMS A", "HA", "06:00-21:00")
	mustExec(fmt.Sprintf(`INSERT INTO bases(id, base_category_id, base, base_abbreviation, sortkey, is_active, default_shift_time) VALUES(%s, %s, ?, ?, 1, 1, ?)`, asBlob(baseID), asBlob(regularCategoryID)),
		"BASE A", "BA", "07:00-19:00")
	mustExec(fmt.Sprintf(`INSERT INTO helicopters(id, identifier, designation) VALUES(%s, ?, ?)`, asBlob(heliID)), "H145", "H145")
	mustExec(fmt.Sprintf(`INSERT INTO roles(id, name) VALUES(%s, ?)`, asBlob(roleID)), "pilot")
	mustExec(fmt.Sprintf(`INSERT INTO users(id, first_name, last_name, mobile_phone, email, role_id) VALUES(%s, ?, ?, ?, ?, %s)`, asBlob(userID), asBlob(roleID)),
		"Pilot", "One", "+62000", "pilot@example.com")
	mustExec(fmt.Sprintf(`INSERT INTO duty_rosters(id, base_id, duty_date, shift_start, shift_end, helicopter_id, created_at, updated_at) VALUES(%s, %s, ?, ?, ?, %s, ?, ?)`,
		asBlob(rosterID), asBlob(hemsBaseID), asBlob(heliID)),
		dutyDate, "2000-01-01 06:00:00", "2000-01-01 21:00:00", now, now)
	mustExec(fmt.Sprintf(`INSERT INTO duty_roster_crews(id, roster_id, user_id, role_code, crew_type, name_label, mobile_phone, email, date_start, date_end, shift_start, shift_end, created_at, updated_at)
			VALUES(%s, %s, %s, 'pilot', 'main', '', '', '', ?, ?, ?, ?, ?, ?)`, asBlob(crewID), asBlob(rosterID), asBlob(userID)),
		dutyDate, dutyDate, "2000-01-01 06:00:00", "2000-01-01 21:00:00", now, now)

	if _, err := repo.GetRosterByID(ctx, rosterID); err != nil {
		t.Fatalf("GetRosterByID: %v", err)
	}
	if _, err := repo.LockRosterByID(ctx, rosterID); err != nil {
		t.Fatalf("LockRosterByID: %v", err)
	}
	if _, err := repo.ListActiveCrewsByRosterID(ctx, rosterID); err != nil {
		t.Fatalf("ListActiveCrewsByRosterID: %v", err)
	}

	if _, err := repo.FindRosterIDsByBaseDate(ctx, hemsBaseID, dutyDate); err != nil {
		t.Fatalf("FindRosterIDsByBaseDate: %v", err)
	}
	rows, err := repo.MatchCrewByFilter(ctx, [][]byte{rosterID}, dutyroster.DutyRosterCrew{
		RoleCode: "pilot",
		CrewType: "main",
		UserID:   userID,
	})
	if err != nil {
		t.Fatalf("MatchCrewByFilter: %v", err)
	}
	if len(rows) == 0 {
		t.Fatalf("expected matched rows")
	}
	if err := repo.SoftDeleteCrewRows(ctx, rows, uuidv7.MustBytes()); err != nil {
		t.Fatalf("SoftDeleteCrewRows: %v", err)
	}

	if _, err := repo.GetBaseDefaultShift(ctx, hemsBaseID, "hems"); err != nil {
		t.Fatalf("GetBaseDefaultShift hems: %v", err)
	}
	if _, err := repo.GetBaseDefaultShift(ctx, baseID, "base"); err != nil {
		t.Fatalf("GetBaseDefaultShift base: %v", err)
	}

	if _, err := repo.GetHeaderByID(ctx, rosterID); err != nil {
		t.Fatalf("GetHeaderByID: %v", err)
	}
	if _, err := repo.GetHeaderByIDByBaseType(ctx, rosterID, "base"); err != nil {
		t.Fatalf("GetHeaderByIDByBaseType base: %v", err)
	}
	if _, err := repo.GetCrewsByRosterIDs(ctx, [][]byte{rosterID}); err != nil {
		t.Fatalf("GetCrewsByRosterIDs: %v", err)
	}
	if _, err := repo.ListHeadersByRange(ctx, hemsBaseID, dutyDate.AddDate(0, 0, -1), dutyDate.AddDate(0, 0, 1)); err != nil {
		t.Fatalf("ListHeadersByRange: %v", err)
	}
	if _, err := repo.ListHeadersByRangeByBaseType(ctx, baseID, dutyDate.AddDate(0, 0, -1), dutyDate.AddDate(0, 0, 1), "base"); err != nil {
		t.Fatalf("ListHeadersByRangeByBaseType: %v", err)
	}
	if _, err := repo.ListHEMSBases(ctx, nil); err != nil {
		t.Fatalf("ListHEMSBases: %v", err)
	}
	if _, err := repo.ListBasesByType(ctx, nil, "base"); err != nil {
		t.Fatalf("ListBasesByType: %v", err)
	}

	if err := repo.SoftDeleteCrewsByRosterID(ctx, rosterID, uuidv7.MustBytes()); err != nil {
		t.Fatalf("SoftDeleteCrewsByRosterID: %v", err)
	}
	if err := repo.SoftDeleteHeader(ctx, rosterID, uuidv7.MustBytes()); err != nil {
		t.Fatalf("SoftDeleteHeader: %v", err)
	}
}

func TestDutyRosterRepository_HelperCoverage(t *testing.T) {
	if key := rosterBaseCategoryKey("base"); key != "regular" {
		t.Fatalf("unexpected base category key")
	}
	if key := rosterBaseCategoryKey("hems"); key != "hems" {
		t.Fatalf("unexpected hems category key")
	}
	if got := dateOnlyUTC(time.Date(2026, 3, 20, 9, 10, 11, 0, time.FixedZone("X", 7*3600))); got.Hour() != 0 {
		t.Fatalf("expected date only UTC, got %v", got)
	}
	if err := softDeleteCrewByIDs(nil, nil, time.Now().UTC(), nil); err != nil {
		t.Fatalf("softDeleteCrewByIDs empty should be nil, got %v", err)
	}
}

func TestDutyRosterRepository_HelperCoverageExtra(t *testing.T) {
	db := openDutyRosterRepoCoverageDB(t)
	repo := NewDutyRosterRepository(db)
	ctx := context.Background()

	if _, err := repo.GetCrewsByRosterIDs(ctx, nil); err != nil {
		t.Fatalf("GetCrewsByRosterIDs empty should not error: %v", err)
	}
	if _, err := repo.ListBasesByType(ctx, nil, "unknown"); err != nil {
		t.Fatalf("ListBasesByType unknown should not error: %v", err)
	}
	if _, err := repo.FindRosterIDByBaseDate(ctx, []byte("base-does-not-exist"), time.Date(2026, 3, 1, 0, 0, 0, 0, time.UTC)); err != nil {
		t.Fatalf("FindRosterIDByBaseDate empty should not error: %v", err)
	}
	if _, err := repo.MatchCrewByFilter(ctx, nil, dutyroster.DutyRosterCrew{RoleCode: "pilot"}); err != nil {
		t.Fatalf("MatchCrewByFilter empty rosterIDs should not error: %v", err)
	}

	if err := softDeleteCrewByFilter(db, nil, dutyroster.DutyRosterCrew{RoleCode: "pilot"}, time.Now().UTC(), uuidv7.MustBytes()); err != nil {
		t.Fatalf("softDeleteCrewByFilter empty rosterIDs should not error: %v", err)
	}

	rosterID := []byte("roster-softdel-01")
	userID := []byte("user-softdel-001")
	now := time.Now().UTC()
	asBlob := func(b []byte) string { return fmt.Sprintf("X'%x'", b) }
	if err := db.Exec(fmt.Sprintf(`INSERT INTO duty_rosters(id, base_id, duty_date, shift_start, shift_end, created_at, updated_at) VALUES(%s, %s, ?, ?, ?, ?, ?)`,
		asBlob(rosterID), asBlob([]byte("hems-base-softdel"))),
		time.Date(2026, 3, 21, 0, 0, 0, 0, time.UTC), "2000-01-01 06:00:00", "2000-01-01 21:00:00", now, now).Error; err != nil {
		t.Fatalf("seed roster for softDeleteCrewByFilter: %v", err)
	}
	if err := db.Exec(fmt.Sprintf(`INSERT INTO duty_roster_crews(id, roster_id, user_id, role_code, crew_type, date_start, date_end, shift_start, shift_end, created_at, updated_at)
		VALUES(%s, %s, %s, 'pilot', 'main', ?, ?, ?, ?, ?, ?)`,
		asBlob([]byte("crew-softdel-001")), asBlob(rosterID), asBlob(userID)),
		time.Date(2026, 3, 21, 0, 0, 0, 0, time.UTC),
		time.Date(2026, 3, 21, 0, 0, 0, 0, time.UTC),
		"2000-01-01 08:00:00", "2000-01-01 22:00:00",
		now, now).Error; err != nil {
		t.Fatalf("seed crew for softDeleteCrewByFilter: %v", err)
	}
	filterDate := time.Date(2026, 3, 21, 0, 0, 0, 0, time.UTC)
	err := softDeleteCrewByFilter(db, [][]byte{rosterID}, dutyroster.DutyRosterCrew{
		RoleCode:  "pilot",
		CrewType:  "main",
		UserID:    userID,
		DateStart: &filterDate,
		DateEnd:   &filterDate,
	}, now, uuidv7.MustBytes())
	if err == nil {
		t.Fatalf("expected sqlite dialect error for UPDATE alias query")
	}
}

func TestDutyRosterRepository_NotFoundAndRowsAffectedBranches(t *testing.T) {
	db := openDutyRosterRepoCoverageDB(t)
	repo := NewDutyRosterRepository(db)
	ctx := context.Background()

	missingID := []byte("missing-roster-01")
	baseID := []byte("base-not-found-1")
	dutyDate := time.Date(2026, 3, 20, 0, 0, 0, 0, time.UTC)

	if _, err := repo.GetRosterByID(ctx, missingID); err == nil {
		t.Fatalf("expected GetRosterByID not found error")
	}
	if _, err := repo.LockRosterByID(ctx, missingID); err == nil {
		t.Fatalf("expected LockRosterByID not found error")
	}

	if err := repo.UpdateHeader(ctx, &dutyroster.DutyRoster{
		ID:        missingID,
		BaseID:    baseID,
		DutyDate:  dutyDate,
		UpdatedBy: uuidv7.MustBytes(),
	}); err == nil {
		t.Fatalf("expected UpdateHeader not found error")
	}

	if err := repo.SoftDeleteHeader(ctx, missingID, uuidv7.MustBytes()); err == nil {
		t.Fatalf("expected SoftDeleteHeader not found error")
	}
	if _, err := repo.GetBaseDefaultShift(ctx, baseID, "base"); err == nil {
		t.Fatalf("expected GetBaseDefaultShift not found error")
	}

	// with non-empty baseID filter branch on list bases
	if rows, err := repo.ListHEMSBases(ctx, baseID); err != nil || len(rows) != 0 {
		t.Fatalf("expected empty ListHEMSBases filtered result, got len=%d err=%v", len(rows), err)
	}
	if rows, err := repo.ListBasesByType(ctx, baseID, "base"); err != nil || len(rows) != 0 {
		t.Fatalf("expected empty ListBasesByType filtered result, got len=%d err=%v", len(rows), err)
	}

	if err := db.Exec("DROP TABLE duty_rosters").Error; err != nil {
		t.Fatalf("drop duty_rosters: %v", err)
	}
	if _, err := repo.FindRosterIDByBaseDate(ctx, baseID, dutyDate); err == nil {
		t.Fatalf("expected query error after dropping roster table")
	}
}

func TestDutyRosterRepository_LowLevelHelperBranches(t *testing.T) {
	db := openDutyRosterRepoCoverageDB(t)
	now := time.Now().UTC()
	asBlob := func(b []byte) string { return fmt.Sprintf("X'%x'", b) }

	baseID := []byte("hems-base-helper01")
	_, hemsCategoryID := dutyRosterTestCategoryIDs()
	rosterID := []byte("roster-helper-001")
	userID := []byte("user-helper-0001")
	crewIDUser := []byte("crew-helper-user1")
	crewIDGuest := []byte("crew-helper-guest")
	dutyDate := time.Date(2026, 3, 22, 0, 0, 0, 0, time.UTC)

	if err := db.Exec(fmt.Sprintf(`INSERT INTO bases(id, base_category_id, base, base_abbreviation, sortkey, is_active, default_shift_time) VALUES(%s, %s, ?, ?, 1, 1, ?)`, asBlob(baseID), asBlob(hemsCategoryID)),
		"HEMS H", "HH", "06:00-21:00").Error; err != nil {
		t.Fatalf("seed base: %v", err)
	}
	if err := db.Exec(fmt.Sprintf(`INSERT INTO duty_rosters(id, base_id, duty_date, shift_start, shift_end, created_at, updated_at) VALUES(%s, %s, ?, ?, ?, ?, ?)`,
		asBlob(rosterID), asBlob(baseID)),
		dutyDate, "2000-01-01 06:00:00", "2000-01-01 21:00:00", now, now).Error; err != nil {
		t.Fatalf("seed roster: %v", err)
	}
	if err := db.Exec(fmt.Sprintf(`INSERT INTO duty_roster_crews(id, roster_id, user_id, role_code, crew_type, name_label, mobile_phone, email, date_start, date_end, shift_start, shift_end, created_at, updated_at)
		VALUES(%s, %s, %s, 'pilot', 'main', '', '', '', ?, ?, ?, ?, ?, ?)`,
		asBlob(crewIDUser), asBlob(rosterID), asBlob(userID)),
		dutyDate, dutyDate, "2000-01-01 08:00:00", "2000-01-01 22:00:00", now, now).Error; err != nil {
		t.Fatalf("seed user crew: %v", err)
	}
	if err := db.Exec(fmt.Sprintf(`INSERT INTO duty_roster_crews(id, roster_id, user_id, role_code, crew_type, name_label, mobile_phone, email, shift_start, shift_end, created_at, updated_at)
		VALUES(%s, %s, NULL, 'other_person', 'main', 'Guest', '+62000', 'g@example.com', ?, ?, ?, ?)`,
		asBlob(crewIDGuest), asBlob(rosterID)),
		"2000-01-01 09:00:00", "2000-01-01 18:00:00", now, now).Error; err != nil {
		t.Fatalf("seed guest crew: %v", err)
	}

	// softReplaceCrewByUser branches
	if err := softReplaceCrewByUser(db, nil, "pilot", "main", userID, uuidv7.MustBytes()); err != nil {
		t.Fatalf("softReplaceCrewByUser empty roster should be nil: %v", err)
	}
	if err := softReplaceCrewByUser(db, rosterID, "pilot", "main", userID, uuidv7.MustBytes()); err != nil {
		t.Fatalf("softReplaceCrewByUser update branch failed: %v", err)
	}

	// crewExists branches (user/date nil and set)
	existsGuest, err := crewExists(db, dutyroster.DutyRosterCrew{
		RosterID:    rosterID,
		RoleCode:    "other_person",
		CrewType:    "main",
		NameLabel:   "Guest",
		MobilePhone: "+62000",
		Email:       "g@example.com",
		ShiftStart:  "2000-01-01 09:00:00",
		ShiftEnd:    "2000-01-01 18:00:00",
	})
	if err != nil || !existsGuest {
		t.Fatalf("expected guest crew exists, got exists=%v err=%v", existsGuest, err)
	}

	start := dutyDate
	end := dutyDate
	existsUser, err := crewExists(db, dutyroster.DutyRosterCrew{
		RosterID:  rosterID,
		UserID:    userID,
		RoleCode:  "pilot",
		CrewType:  "main",
		DateStart: &start,
		DateEnd:   &end,
	})
	if err != nil {
		t.Fatalf("crewExists user branch err: %v", err)
	}
	if existsUser {
		t.Fatalf("expected false after softReplaceCrewByUser (row should be deleted)")
	}

	// softDeleteCrewForDate branches
	if err := softDeleteCrewForDate(db, []byte("base-not-exist"), dutyDate, dutyroster.DutyRosterCrew{
		RoleCode: "pilot", CrewType: "main",
	}, now, uuidv7.MustBytes()); err != nil {
		t.Fatalf("softDeleteCrewForDate no roster branch should be nil: %v", err)
	}
	if err := softDeleteCrewForDate(db, baseID, dutyDate, dutyroster.DutyRosterCrew{
		RoleCode:    "other_person",
		CrewType:    "main",
		NameLabel:   "Guest",
		MobilePhone: "+62000",
		Email:       "g@example.com",
	}, now, uuidv7.MustBytes()); err != nil {
		t.Fatalf("softDeleteCrewForDate guest branch failed: %v", err)
	}

	// matchCrewByFilter name_label/date/shift filter branch
	if err := db.Exec(fmt.Sprintf(`INSERT INTO duty_roster_crews(id, roster_id, user_id, role_code, crew_type, name_label, mobile_phone, email, shift_start, shift_end, created_at, updated_at)
		VALUES(%s, %s, NULL, 'other_person', 'main', 'Guest', '+62000', 'g@example.com', ?, ?, ?, ?)`,
		asBlob([]byte("crew-helper-guest2")), asBlob(rosterID)),
		"2000-01-01 09:00:00", "2000-01-01 18:00:00", now, now).Error; err != nil {
		t.Fatalf("seed guest2 crew: %v", err)
	}
	rows, err := matchCrewByFilter(db, [][]byte{rosterID}, dutyroster.DutyRosterCrew{
		RoleCode:    "other_person",
		CrewType:    "main",
		NameLabel:   "Guest",
		MobilePhone: "+62000",
		Email:       "g@example.com",
	})
	if err != nil || len(rows) == 0 {
		t.Fatalf("expected matchCrewByFilter by name_label branch, got len=%d err=%v", len(rows), err)
	}
}