package services

import (
	"context"
	"errors"
	"os"
	"testing"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"

	"mgit.msbls.de/m/paliad/internal/db"
)

// TestProjectService_ProceedingTypeCategoryGuard exercises the Phase 3
// Slice 5 (t-paliad-186) "fristenrechner-category only" invariant on
// paliad.projects.proceeding_type_id from three angles:
//
//  1. Migration smoke: post-mig 087, no project points at a
//     non-fristenrechner-category proceeding_types row.
//
//  2. ProjectService.Create returns ErrInvalidProceedingTypeCategory
//     when handed a non-fristenrechner-category id. The server-side
//     service guard fires BEFORE the DB write hits the trigger from
//     mig 088.
//
//  3. The mig 088 trigger rejects a raw INSERT that bypasses the Go
//     service layer (defence-in-depth). A non-fristenrechner-category
//     id INSERT via plain SQL must raise EXCEPTION.
//
//  4. Passing a fristenrechner-category id (upc.inf.cfi) succeeds.
//
// Phase 3 Slice 9 follow-up B (t-paliad-200, mig 093) retired the
// 'litigation' category from the rule corpus; the negative-case lookup
// is now any non-fristenrechner-category row (the _archived_litigation
// pt mig 093 introduces is the canonical one and exists on every
// post-093 deploy).
//
// Skipped when TEST_DATABASE_URL is unset, mirroring audit_service_test.go.
func TestProjectService_ProceedingTypeCategoryGuard(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()

	// -----------------------------------------------------------------
	// 1. Migration smoke — no project points at a litigation-category code.
	// -----------------------------------------------------------------
	var leaked int
	if err := pool.GetContext(ctx, &leaked, `
		SELECT count(*)
		  FROM paliad.projects p
		  JOIN paliad.proceeding_types pt ON pt.id = p.proceeding_type_id
		 WHERE pt.category <> 'fristenrechner'`); err != nil {
		t.Fatalf("count leaked refs: %v", err)
	}
	if leaked != 0 {
		t.Errorf("%d projects still reference non-fristenrechner proceeding_types — mig 087 incomplete", leaked)
	}

	// -----------------------------------------------------------------
	// 2 + 4. ProjectService.Create guard — typed error on non-
	//        fristenrechner id, success on fristenrechner id.
	//
	// Pre-mig-093 this looked up category='litigation' AND code='INF';
	// mig 093 retired the litigation category so the negative case now
	// pulls any non-fristenrechner row (the _archived_litigation pt is
	// the canonical post-093 row, but the query is broad in case other
	// non-fristenrechner buckets are introduced).
	// -----------------------------------------------------------------
	var nonFristenrechnerID int
	if err := pool.GetContext(ctx, &nonFristenrechnerID,
		`SELECT id FROM paliad.proceeding_types
		  WHERE category <> 'fristenrechner'
		  ORDER BY id
		  LIMIT 1`); err != nil {
		t.Fatalf("look up non-fristenrechner id: %v", err)
	}
	var fristenrechnerID int
	if err := pool.GetContext(ctx, &fristenrechnerID,
		`SELECT id FROM paliad.proceeding_types
		  WHERE category = 'fristenrechner' AND code = $1 AND is_active = true`,
		CodeUPCInfringement); err != nil {
		t.Fatalf("look up %s id: %v", CodeUPCInfringement, err)
	}

	users := NewUserService(pool)
	svc := NewProjectService(pool, users)

	// Seed a user so Create has a creator with a paliad.users row.
	userID := uuid.New()
	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.projects WHERE created_by = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users    WHERE id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users      WHERE id = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, 'slice5-guard-test@hlc.com')`,
		userID); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users (id, email, display_name, office, role, lang)
		 VALUES ($1, 'slice5-guard-test@hlc.com', 'Slice5 Guard', 'munich', 'associate', 'de')`,
		userID); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}

	// 2. Non-fristenrechner-category id → ErrInvalidProceedingTypeCategory.
	_, err = svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeProject,
		Title:            "Slice 5 — non-fristenrechner-id reject",
		ProceedingTypeID: &nonFristenrechnerID,
	})
	if err == nil {
		t.Error("Create with non-fristenrechner-category proceeding_type_id should fail, but succeeded")
	} else if !errors.Is(err, ErrInvalidProceedingTypeCategory) {
		t.Errorf("expected ErrInvalidProceedingTypeCategory, got %v", err)
	}

	// 4. Fristenrechner-category id → success.
	created, err := svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeProject,
		Title:            "Slice 5 — fristenrechner-id accept",
		ProceedingTypeID: &fristenrechnerID,
	})
	if err != nil {
		t.Fatalf("Create with fristenrechner-category proceeding_type_id: %v", err)
	}
	if created.ProceedingTypeID == nil || *created.ProceedingTypeID != fristenrechnerID {
		t.Errorf("created project proceeding_type_id = %v, want %d", created.ProceedingTypeID, fristenrechnerID)
	}

	// -----------------------------------------------------------------
	// 3. mig 088 trigger — raw INSERT bypassing Go service must raise.
	// -----------------------------------------------------------------
	rawID := uuid.New()
	defer pool.ExecContext(ctx, `DELETE FROM paliad.projects WHERE id = $1`, rawID)

	_, err = pool.ExecContext(ctx,
		`INSERT INTO paliad.projects
		    (id, type, parent_id, path, title, status, created_by,
		     proceeding_type_id, metadata, created_at, updated_at)
		 VALUES ($1, 'project', NULL, $1::text, 'Slice 5 — trigger bypass', 'active', $2,
		         $3, '{}'::jsonb, now(), now())`,
		rawID, userID, nonFristenrechnerID)
	if err == nil {
		t.Error("raw INSERT with non-fristenrechner-category proceeding_type_id should have raised; got nil")
	}
}

// TestProjectService_ProceedingTypeKindGuard exercises the mig 153
// (t-paliad-325 / m/paliad#147) "kind='proceeding' only" invariant on
// paliad.projects.proceeding_type_id from three angles:
//
//  1. ProjectService.Create returns ErrInvalidProceedingTypeKind when
//     handed an id pointing at a kind='phase' / 'side_action' / 'meta'
//     row (the Go service guard fires before the DB trigger).
//
//  2. ProjectService.Create returns ErrInvalidProceedingTypeKind when
//     handed an id pointing at a row with is_active=false (mig 153 §4
//     deactivated all non-primary rows so this is the same set of IDs;
//     the test still independently asserts the is_active branch by
//     re-activating a phase row inside the test and confirming the kind
//     check still fires).
//
//  3. The mig 153 backstop trigger rejects a raw INSERT that bypasses
//     the Go service layer (defence-in-depth). Bypasses mig 088's
//     category trigger by also picking a fristenrechner-category row.
//
//  4. Passing a kind='proceeding' active id (upc.inf.cfi) still
//     succeeds — proves the new guard doesn't break the happy path.
//
// Skipped when TEST_DATABASE_URL is unset, mirroring the rest of this
// file.
func TestProjectService_ProceedingTypeKindGuard(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()

	// A row that is fristenrechner-category but kind != 'proceeding'.
	// Picks the first phase row by id (deterministic). Falls back to any
	// non-proceeding kind if no phase rows are present (post-data-drift
	// hardening).
	var phaseID int
	if err := pool.GetContext(ctx, &phaseID,
		`SELECT id FROM paliad.proceeding_types
		  WHERE category = 'fristenrechner' AND kind <> 'proceeding'
		  ORDER BY (kind = 'phase') DESC, id
		  LIMIT 1`); err != nil {
		t.Fatalf("look up non-proceeding kind id: %v", err)
	}

	// A primary id for the happy-path case + raw-INSERT control.
	var proceedingID int
	if err := pool.GetContext(ctx, &proceedingID,
		`SELECT id FROM paliad.proceeding_types
		  WHERE category = 'fristenrechner' AND kind = 'proceeding'
		    AND is_active = true AND code = $1`,
		CodeUPCInfringement); err != nil {
		t.Fatalf("look up %s id: %v", CodeUPCInfringement, err)
	}

	users := NewUserService(pool)
	svc := NewProjectService(pool, users)

	userID := uuid.New()
	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.projects WHERE created_by = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users    WHERE id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users      WHERE id = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, 'mig153-guard-test@hlc.com')`,
		userID); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users (id, email, display_name, office, role, lang)
		 VALUES ($1, 'mig153-guard-test@hlc.com', 'Mig153 Guard', 'munich', 'associate', 'de')`,
		userID); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}

	// 1. Non-proceeding kind id → ErrInvalidProceedingTypeKind from the
	//    service guard. (The row is also is_active=false post-mig-153,
	//    but the kind check fires first.)
	_, err = svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeProject,
		Title:            "Mig 153 — non-proceeding-kind reject",
		ProceedingTypeID: &phaseID,
	})
	if err == nil {
		t.Error("Create with kind!=proceeding proceeding_type_id should fail, but succeeded")
	} else if !errors.Is(err, ErrInvalidProceedingTypeKind) {
		t.Errorf("expected ErrInvalidProceedingTypeKind, got %v", err)
	}

	// 2. Re-activate the phase row in a savepoint so the kind check
	//    still fires (proves the kind branch isn't shadowed by the
	//    is_active branch).
	if _, err := pool.ExecContext(ctx,
		`UPDATE paliad.proceeding_types SET is_active = true WHERE id = $1`, phaseID); err != nil {
		t.Fatalf("re-activate phase row: %v", err)
	}
	t.Cleanup(func() {
		pool.ExecContext(ctx,
			`UPDATE paliad.proceeding_types SET is_active = false WHERE id = $1`, phaseID)
	})

	_, err = svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeProject,
		Title:            "Mig 153 — active phase row still rejects on kind",
		ProceedingTypeID: &phaseID,
	})
	if err == nil {
		t.Error("Create with active kind=phase row should still fail on kind check; got nil")
	} else if !errors.Is(err, ErrInvalidProceedingTypeKind) {
		t.Errorf("expected ErrInvalidProceedingTypeKind, got %v", err)
	}

	// 3. mig 153 trigger — raw INSERT bypassing Go service must raise.
	//    We use the active phase row (still re-activated from step 2)
	//    so we don't trip mig 088's category check first. Both triggers
	//    are independent; mig 153's must fire on a category=fristenrechner
	//    kind!=proceeding row.
	rawID := uuid.New()
	defer pool.ExecContext(ctx, `DELETE FROM paliad.projects WHERE id = $1`, rawID)
	_, err = pool.ExecContext(ctx,
		`INSERT INTO paliad.projects
		    (id, type, parent_id, path, title, status, created_by,
		     proceeding_type_id, metadata, created_at, updated_at)
		 VALUES ($1, 'project', NULL, $1::text, 'Mig 153 — trigger bypass', 'active', $2,
		         $3, '{}'::jsonb, now(), now())`,
		rawID, userID, phaseID)
	if err == nil {
		t.Error("raw INSERT with kind!=proceeding proceeding_type_id should have raised; got nil")
	}

	// 4. Happy path: kind='proceeding' active id → success.
	created, err := svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeProject,
		Title:            "Mig 153 — primary proceeding accept",
		ProceedingTypeID: &proceedingID,
	})
	if err != nil {
		t.Fatalf("Create with kind=proceeding proceeding_type_id: %v", err)
	}
	if created.ProceedingTypeID == nil || *created.ProceedingTypeID != proceedingID {
		t.Errorf("created proceeding_type_id = %v, want %d", created.ProceedingTypeID, proceedingID)
	}
}

// TestProjectService_InstanceLevel_Roundtrip covers the Phase 3 Slice 8
// (t-paliad-189) instance_level data path: Create + Update both accept
// the four allowed shapes (first / appeal / cassation / NULL) and reject
// anything else with ErrInvalidInput. The DB CHECK from mig 080
// (Slice 1) is the defence-in-depth backstop; the service-layer
// validation provides a clearer error to the handler.
//
// Skipped when TEST_DATABASE_URL is unset.
func TestProjectService_InstanceLevel_Roundtrip(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()
	users := NewUserService(pool)
	svc := NewProjectService(pool, users)

	userID := uuid.New()
	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.projects WHERE created_by = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users    WHERE id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users      WHERE id = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, 'slice8-instance-test@hlc.com')`,
		userID); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users (id, email, display_name, office, role, lang)
		 VALUES ($1, 'slice8-instance-test@hlc.com', 'Slice8 Test', 'munich', 'associate', 'de')`,
		userID); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}

	// Create with instance_level='first'.
	first := "first"
	created, err := svc.Create(ctx, userID, CreateProjectInput{
		Type:          ProjectTypeProject,
		Title:         "Slice 8 — instance_level first",
		InstanceLevel: &first,
	})
	if err != nil {
		t.Fatalf("Create with instance_level=first: %v", err)
	}
	if created.InstanceLevel == nil || *created.InstanceLevel != "first" {
		t.Errorf("created InstanceLevel = %v, want 'first'", created.InstanceLevel)
	}

	// Update to 'appeal'.
	appeal := "appeal"
	updated, err := svc.Update(ctx, userID, created.ID, UpdateProjectInput{InstanceLevel: &appeal})
	if err != nil {
		t.Fatalf("Update to appeal: %v", err)
	}
	if updated.InstanceLevel == nil || *updated.InstanceLevel != "appeal" {
		t.Errorf("updated InstanceLevel = %v, want 'appeal'", updated.InstanceLevel)
	}

	// Update to '' (clear).
	clear := ""
	cleared, err := svc.Update(ctx, userID, created.ID, UpdateProjectInput{InstanceLevel: &clear})
	if err != nil {
		t.Fatalf("Update clear: %v", err)
	}
	if cleared.InstanceLevel != nil {
		t.Errorf("cleared InstanceLevel = %v, want nil", cleared.InstanceLevel)
	}

	// Invalid value → ErrInvalidInput.
	bogus := "supreme"
	_, err = svc.Update(ctx, userID, created.ID, UpdateProjectInput{InstanceLevel: &bogus})
	if err == nil {
		t.Error("instance_level=supreme should fail; got nil")
	} else if !errors.Is(err, ErrInvalidInput) {
		t.Errorf("want ErrInvalidInput, got %v", err)
	}
}

// TestProjectService_CaseProceedingTypePicker covers the t-paliad-232
// data path for the new project-form Verfahrenstyp picker:
//
//  1. Creating a `case`-typed project with a fristenrechner-category
//     proceeding_type_id round-trips the column.
//  2. The same code path rejects a non-fristenrechner-category id with
//     ErrInvalidProceedingTypeCategory (mirror of the guard test above,
//     this time exercised through a 'case' shape).
//
// Skipped when TEST_DATABASE_URL is unset.
func TestProjectService_CaseProceedingTypePicker(t *testing.T) {
	url := os.Getenv("TEST_DATABASE_URL")
	if url == "" {
		t.Skip("TEST_DATABASE_URL not set — skipping live DB test")
	}
	if err := db.ApplyMigrations(url); err != nil {
		t.Fatalf("apply migrations: %v", err)
	}
	pool, err := sqlx.Connect("postgres", url)
	if err != nil {
		t.Fatalf("connect: %v", err)
	}
	defer pool.Close()

	ctx := context.Background()

	var fristenrechnerID int
	if err := pool.GetContext(ctx, &fristenrechnerID,
		`SELECT id FROM paliad.proceeding_types
		  WHERE category = 'fristenrechner' AND code = $1 AND is_active = true`,
		CodeUPCInfringement); err != nil {
		t.Fatalf("look up %s id: %v", CodeUPCInfringement, err)
	}
	var nonFristenrechnerID int
	if err := pool.GetContext(ctx, &nonFristenrechnerID,
		`SELECT id FROM paliad.proceeding_types
		  WHERE category <> 'fristenrechner'
		  ORDER BY id
		  LIMIT 1`); err != nil {
		t.Fatalf("look up non-fristenrechner id: %v", err)
	}

	users := NewUserService(pool)
	svc := NewProjectService(pool, users)

	userID := uuid.New()
	cleanup := func() {
		pool.ExecContext(ctx, `DELETE FROM paliad.projects WHERE created_by = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM paliad.users    WHERE id = $1`, userID)
		pool.ExecContext(ctx, `DELETE FROM auth.users      WHERE id = $1`, userID)
	}
	cleanup()
	defer cleanup()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO auth.users (id, email) VALUES ($1, 't-paliad-232-test@hlc.com')`,
		userID); err != nil {
		t.Fatalf("seed auth.users: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.users (id, email, display_name, office, role, lang)
		 VALUES ($1, 't-paliad-232-test@hlc.com', 'Picker Test', 'munich', 'associate', 'de')`,
		userID); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}

	// 1. Case-typed create with a fristenrechner id succeeds.
	created, err := svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeCase,
		Title:            "t-paliad-232 — case with proceeding_type_id",
		ProceedingTypeID: &fristenrechnerID,
	})
	if err != nil {
		t.Fatalf("Create case with fristenrechner id: %v", err)
	}
	if created.ProceedingTypeID == nil || *created.ProceedingTypeID != fristenrechnerID {
		t.Errorf("created proceeding_type_id = %v, want %d", created.ProceedingTypeID, fristenrechnerID)
	}

	// 2. Case-typed create with a non-fristenrechner id is rejected.
	_, err = svc.Create(ctx, userID, CreateProjectInput{
		Type:             ProjectTypeCase,
		Title:            "t-paliad-232 — case with non-fristenrechner id",
		ProceedingTypeID: &nonFristenrechnerID,
	})
	if err == nil {
		t.Error("Create case with non-fristenrechner proceeding_type_id should fail, but succeeded")
	} else if !errors.Is(err, ErrInvalidProceedingTypeCategory) {
		t.Errorf("expected ErrInvalidProceedingTypeCategory, got %v", err)
	}
}
