package services

// Live-DB integration test for parent-node lane aggregation
// (t-paliad-175 SmartTimeline Slice 4 §5). Skipped without TEST_DATABASE_URL.
//
// Builds a 3-level fixture (Patent → Case-A + Case-B → CCR-A) and walks
// the level policy at each viewpoint:
//
//   - Case-A view: full detail + CCR sub-project track (single project,
//     own actuals + projection, "self" lane + "counterclaim:<id>" lane).
//   - Patent view: lanes per child case; events from each case subtree;
//     deadlines + milestones surface, statuses done/open/overdue.
//   - Bubble-up: a counterclaim_created milestone (default-on bubble_up)
//     surfaces at Patent level under Case-A's lane.
//   - Custom milestone with bubble_up=true surfaces too; without, it's
//     filtered out.

import (
	"context"
	"os"
	"testing"
	"time"

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

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

func TestProjectionService_LevelAggregation_Live(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()
	userID := uuid.New()
	patentID := uuid.New()
	caseAID := uuid.New()
	caseBID := uuid.New()

	cleanup := func() {
		// CCR children (counterclaim_of points at one of the cases)
		// must go first so the FK doesn't block the case delete.
		pool.ExecContext(ctx, `DELETE FROM paliad.project_events WHERE project_id IN (
			SELECT id FROM paliad.projects WHERE counterclaim_of IN ($1, $2))`, caseAID, caseBID)
		pool.ExecContext(ctx, `DELETE FROM paliad.project_teams  WHERE project_id IN (
			SELECT id FROM paliad.projects WHERE counterclaim_of IN ($1, $2))`, caseAID, caseBID)
		pool.ExecContext(ctx, `DELETE FROM paliad.projects       WHERE counterclaim_of IN ($1, $2)`, caseAID, caseBID)

		pool.ExecContext(ctx, `DELETE FROM paliad.project_events WHERE project_id IN ($1, $2, $3)`, patentID, caseAID, caseBID)
		pool.ExecContext(ctx, `DELETE FROM paliad.deadlines      WHERE project_id IN ($1, $2, $3)`, patentID, caseAID, caseBID)
		pool.ExecContext(ctx, `DELETE FROM paliad.appointments   WHERE project_id IN ($1, $2, $3)`, patentID, caseAID, caseBID)
		pool.ExecContext(ctx, `DELETE FROM paliad.project_teams  WHERE project_id IN ($1, $2, $3)`, patentID, caseAID, caseBID)
		pool.ExecContext(ctx, `DELETE FROM paliad.projects       WHERE id IN ($1, $2, $3)`, patentID, caseAID, caseBID)
		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, 'level-agg-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, global_role, lang)
		 VALUES ($1, 'level-agg-test@hlc.com', 'Level Agg Test', 'munich', 'global_admin', 'de')`,
		userID); err != nil {
		t.Fatalf("seed paliad.users: %v", err)
	}
	// Patent hub.
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.projects (id, type, path, title, patent_number, status, created_by)
		 VALUES ($1, 'patent', $1::text, 'EP9999999 — Test Patent', 'EP9999999', 'active', $2)`,
		patentID, userID); err != nil {
		t.Fatalf("seed patent: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.project_teams (project_id, user_id, role, responsibility, inherited, added_by)
		 VALUES ($1, $2, 'lead', 'lead', false, $2)`,
		patentID, userID); err != nil {
		t.Fatalf("seed patent team: %v", err)
	}
	// Case-A under the patent.
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.projects (id, type, parent_id, path, title, status, created_by)
		 VALUES ($1, 'case', $2, $2::text || '.' || $1::text, 'Case A', 'active', $3)`,
		caseAID, patentID, userID); err != nil {
		t.Fatalf("seed case A: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.project_teams (project_id, user_id, role, responsibility, inherited, added_by)
		 VALUES ($1, $2, 'lead', 'lead', false, $2)`,
		caseAID, userID); err != nil {
		t.Fatalf("seed case A team: %v", err)
	}
	// Case-B under the patent.
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.projects (id, type, parent_id, path, title, status, created_by)
		 VALUES ($1, 'case', $2, $2::text || '.' || $1::text, 'Case B', 'active', $3)`,
		caseBID, patentID, userID); err != nil {
		t.Fatalf("seed case B: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.project_teams (project_id, user_id, role, responsibility, inherited, added_by)
		 VALUES ($1, $2, 'lead', 'lead', false, $2)`,
		caseBID, userID); err != nil {
		t.Fatalf("seed case B team: %v", err)
	}

	// Case-A: one open deadline + one done milestone (bubble_up=true via
	// counterclaim_created event_type) + one custom_milestone (bubble_up=false).
	now := time.Now().UTC()
	deadlineA := uuid.New()
	bubbledMilestoneA := uuid.New()
	regularMilestoneA := uuid.New()

	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.deadlines
		   (id, project_id, title, due_date, source, status, created_by)
		 VALUES ($1, $2, 'Case-A open deadline', $3::date, 'manual', 'pending', $4)`,
		deadlineA, caseAID, now.AddDate(0, 0, 14).Format("2006-01-02"), userID); err != nil {
		t.Fatalf("seed deadline A: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.project_events
		   (id, project_id, event_type, title, event_date, created_by, metadata,
		    created_at, updated_at, timeline_kind)
		 VALUES ($1, $2, 'counterclaim_created', 'Widerklage angelegt', $3, $4,
		         '{"bubble_up":true}'::jsonb, $5, $5, 'milestone')`,
		bubbledMilestoneA, caseAID, now.AddDate(0, 0, -7), userID, now); err != nil {
		t.Fatalf("seed bubbled milestone A: %v", err)
	}
	if _, err := pool.ExecContext(ctx,
		`INSERT INTO paliad.project_events
		   (id, project_id, event_type, title, event_date, created_by, metadata,
		    created_at, updated_at, timeline_kind)
		 VALUES ($1, $2, 'custom_milestone', 'Random Note (no bubble)', $3, $4,
		         '{}'::jsonb, $5, $5, 'custom_milestone')`,
		regularMilestoneA, caseAID, now.AddDate(0, 0, -3), userID, now); err != nil {
		t.Fatalf("seed regular milestone A: %v", err)
	}

	users := NewUserService(pool)
	projects := NewProjectService(pool, users)
	eventTypes := NewEventTypeService(pool, users)
	deadlines := NewDeadlineService(pool, projects, eventTypes)
	appointments := NewAppointmentService(pool, projects)
	rules := NewDeadlineRuleService(pool)
	holidays := NewHolidayService(pool)
	courts := NewCourtService(pool)
	fristen := NewFristenrechnerService(rules, holidays, courts)
	projection := NewProjectionService(pool, projects, deadlines, appointments, fristen, rules)

	t.Run("Case-level: lanes mirror tracks (self + CCR)", func(t *testing.T) {
		_, meta, err := projection.For(ctx, userID, caseAID, ProjectionOpts{})
		if err != nil {
			t.Fatalf("For caseA: %v", err)
		}
		// At least the "self" lane is present.
		var sawSelf bool
		for _, l := range meta.Lanes {
			if l.ID == "self" {
				sawSelf = true
				if l.Label != "Case A" {
					t.Errorf("self lane label = %q, want Case A", l.Label)
				}
			}
		}
		if !sawSelf {
			t.Errorf("Lanes = %v, want a 'self' entry", meta.Lanes)
		}
	})

	t.Run("Patent-level: lanes per child case + milestones bubble", func(t *testing.T) {
		rows, meta, err := projection.For(ctx, userID, patentID, ProjectionOpts{})
		if err != nil {
			t.Fatalf("For patent: %v", err)
		}

		// Lanes: one per child case.
		laneIDs := map[string]LaneInfo{}
		for _, l := range meta.Lanes {
			laneIDs[l.ID] = l
		}
		if _, ok := laneIDs[caseAID.String()]; !ok {
			t.Errorf("Lanes missing Case-A entry: %v", meta.Lanes)
		}
		if _, ok := laneIDs[caseBID.String()]; !ok {
			t.Errorf("Lanes missing Case-B entry: %v", meta.Lanes)
		}

		// Bubbled-up milestone (counterclaim_created) surfaces under
		// Case-A's lane.
		var sawBubbled, sawRegular, sawDeadline bool
		for _, r := range rows {
			if r.ProjectEventID != nil && *r.ProjectEventID == bubbledMilestoneA {
				sawBubbled = true
				if r.LaneID != caseAID.String() {
					t.Errorf("bubbled milestone LaneID = %q, want %s", r.LaneID, caseAID.String())
				}
				if !r.BubbleUp {
					t.Errorf("bubbled milestone BubbleUp should be true")
				}
			}
			if r.ProjectEventID != nil && *r.ProjectEventID == regularMilestoneA {
				sawRegular = true
			}
			if r.DeadlineID != nil && *r.DeadlineID == deadlineA {
				sawDeadline = true
				if r.LaneID != caseAID.String() {
					t.Errorf("deadline LaneID = %q, want %s", r.LaneID, caseAID.String())
				}
			}
		}
		if !sawBubbled {
			t.Errorf("bubbled milestone (counterclaim_created) should surface at Patent level")
		}
		// Patent policy = milestones + deadlines, statuses done/open/overdue.
		// The pending deadline (status=open) survives; the regular custom
		// milestone (off_script status, no bubble_up) is filtered out.
		if !sawDeadline {
			t.Errorf("Case-A's open deadline should surface at Patent level (kinds=deadline allowed)")
		}
		if sawRegular {
			t.Errorf("regular custom_milestone (no bubble_up, off_script status) should be filtered at Patent level")
		}
	})

	t.Run("Patent-level: direct_only collapses to single 'self' lane (m/paliad#33)", func(t *testing.T) {
		rows, meta, err := projection.For(ctx, userID, patentID, ProjectionOpts{DirectOnly: true})
		if err != nil {
			t.Fatalf("For patent direct_only: %v", err)
		}
		// Lanes should NOT include child cases — just one "self" entry
		// pointing at the patent itself.
		if len(meta.Lanes) != 1 || meta.Lanes[0].ID != "self" {
			t.Errorf("DirectOnly Lanes = %v, want a single 'self' lane", meta.Lanes)
		}
		if len(meta.Lanes) > 0 && meta.Lanes[0].ProjectID != patentID.String() {
			t.Errorf("self lane ProjectID = %q, want patent id", meta.Lanes[0].ProjectID)
		}
		// Case-A's deadline / milestones must NOT surface — they belong to
		// the case subtree and direct_only excludes them.
		for _, r := range rows {
			if r.DeadlineID != nil && *r.DeadlineID == deadlineA {
				t.Errorf("Case-A deadline should NOT surface at Patent level with direct_only=true (got %v)", r)
			}
			if r.ProjectEventID != nil && *r.ProjectEventID == bubbledMilestoneA {
				t.Errorf("Case-A bubbled milestone should NOT surface at Patent level with direct_only=true")
			}
		}
	})

	t.Run("Case-level: direct_only drops CCR sub-project lane", func(t *testing.T) {
		// Seed a CCR child of Case-A so the default (subtree) path
		// includes a "counterclaim:<id>" lane and direct_only excludes it.
		ccrID := uuid.New()
		ccrMilestoneID := uuid.New()
		if _, err := pool.ExecContext(ctx,
			`INSERT INTO paliad.projects (id, type, parent_id, counterclaim_of, path, title, status, created_by)
			 VALUES ($1, 'case', $2, $2, $2::text || '.' || $1::text, 'Case A — CCR', 'active', $3)`,
			ccrID, caseAID, userID); err != nil {
			t.Fatalf("seed CCR: %v", err)
		}
		if _, err := pool.ExecContext(ctx,
			`INSERT INTO paliad.project_teams (project_id, user_id, role, responsibility, inherited, added_by)
			 VALUES ($1, $2, 'lead', 'lead', false, $2)`,
			ccrID, userID); err != nil {
			t.Fatalf("seed CCR team: %v", err)
		}
		if _, err := pool.ExecContext(ctx,
			`INSERT INTO paliad.project_events
			   (id, project_id, event_type, title, event_date, created_by, metadata,
			    created_at, updated_at, timeline_kind)
			 VALUES ($1, $2, 'custom_milestone', 'CCR-side note', $3, $4,
			         '{}'::jsonb, $5, $5, 'custom_milestone')`,
			ccrMilestoneID, ccrID, now.AddDate(0, 0, -1), userID, now); err != nil {
			t.Fatalf("seed CCR milestone: %v", err)
		}
		defer func() {
			pool.ExecContext(ctx, `DELETE FROM paliad.project_events WHERE id = $1`, ccrMilestoneID)
			pool.ExecContext(ctx, `DELETE FROM paliad.project_teams  WHERE project_id = $1`, ccrID)
			pool.ExecContext(ctx, `DELETE FROM paliad.projects       WHERE id = $1`, ccrID)
		}()

		// Default (subtree) path: Case-A timeline carries both "self" +
		// "counterclaim:<ccrID>" lanes.
		_, defaultMeta, err := projection.For(ctx, userID, caseAID, ProjectionOpts{})
		if err != nil {
			t.Fatalf("For caseA default: %v", err)
		}
		var sawCCRLane bool
		for _, l := range defaultMeta.Lanes {
			if l.ID == "counterclaim:"+ccrID.String() {
				sawCCRLane = true
			}
		}
		if !sawCCRLane {
			t.Fatalf("default Case-A meta.Lanes should include the CCR child: %v", defaultMeta.Lanes)
		}

		// Direct-only path: only the "self" lane survives, CCR milestones
		// are excluded.
		rows, directMeta, err := projection.For(ctx, userID, caseAID, ProjectionOpts{DirectOnly: true})
		if err != nil {
			t.Fatalf("For caseA direct_only: %v", err)
		}
		if len(directMeta.Lanes) != 1 || directMeta.Lanes[0].ID != "self" {
			t.Errorf("direct_only Lanes = %v, want only 'self'", directMeta.Lanes)
		}
		for _, r := range rows {
			if r.ProjectEventID != nil && *r.ProjectEventID == ccrMilestoneID {
				t.Errorf("CCR milestone should NOT surface at Case-A with direct_only=true")
			}
		}
	})

	t.Run("Patent-level: bubble_up false → row dropped", func(t *testing.T) {
		// Re-write the regular milestone with bubble_up=true and confirm
		// it surfaces. Then revert.
		if _, err := pool.ExecContext(ctx,
			`UPDATE paliad.project_events
			    SET metadata = '{"bubble_up":true}'::jsonb
			  WHERE id = $1`, regularMilestoneA); err != nil {
			t.Fatalf("flip bubble_up: %v", err)
		}
		defer pool.ExecContext(ctx,
			`UPDATE paliad.project_events SET metadata = '{}'::jsonb WHERE id = $1`,
			regularMilestoneA)

		rows, _, err := projection.For(ctx, userID, patentID, ProjectionOpts{})
		if err != nil {
			t.Fatalf("For patent (after flip): %v", err)
		}
		var saw bool
		for _, r := range rows {
			if r.ProjectEventID != nil && *r.ProjectEventID == regularMilestoneA {
				saw = true
				if !r.BubbleUp {
					t.Errorf("flipped milestone BubbleUp should be true")
				}
			}
		}
		if !saw {
			t.Errorf("custom_milestone with bubble_up=true should surface at Patent level")
		}
	})
}
