package db

import (
	"context"
	"fmt"
	"strings"
	"testing"
)

func TestItemCreateDuplicatePartNumber(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	item := &Item{PartNumber: "DUP-001", ItemType: "part", Description: "first"}
	if err := repo.Create(ctx, item, nil); err != nil {
		t.Fatalf("Create: %v", err)
	}

	dup := &Item{PartNumber: "DUP-001", ItemType: "part", Description: "duplicate"}
	err := repo.Create(ctx, dup, nil)
	if err == nil {
		t.Fatal("expected error for duplicate part number, got nil")
	}
	if !strings.Contains(err.Error(), "23505") && !strings.Contains(err.Error(), "duplicate") {
		t.Errorf("expected duplicate key error, got: %v", err)
	}
}

func TestItemDelete(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	item := &Item{PartNumber: "HDEL-001", ItemType: "part", Description: "hard delete"}
	if err := repo.Create(ctx, item, nil); err != nil {
		t.Fatalf("Create: %v", err)
	}

	if err := repo.Delete(ctx, item.ID); err != nil {
		t.Fatalf("Delete: %v", err)
	}

	got, err := repo.GetByID(ctx, item.ID)
	if err != nil {
		t.Fatalf("GetByID after delete: %v", err)
	}
	if got != nil {
		t.Error("expected nil after hard delete")
	}
}

func TestItemListPagination(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	for i := 0; i < 5; i++ {
		item := &Item{
			PartNumber:  fmt.Sprintf("PAGE-%04d", i),
			ItemType:    "part",
			Description: fmt.Sprintf("page item %d", i),
		}
		if err := repo.Create(ctx, item, nil); err != nil {
			t.Fatalf("Create #%d: %v", i, err)
		}
	}

	// Fetch page of 2 with offset 2
	items, err := repo.List(ctx, ListOptions{Limit: 2, Offset: 2})
	if err != nil {
		t.Fatalf("List: %v", err)
	}
	if len(items) != 2 {
		t.Errorf("expected 2 items, got %d", len(items))
	}
}

func TestItemListSearch(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	repo.Create(ctx, &Item{PartNumber: "SRCH-001", ItemType: "part", Description: "alpha widget"}, nil)
	repo.Create(ctx, &Item{PartNumber: "SRCH-002", ItemType: "part", Description: "beta gadget"}, nil)
	repo.Create(ctx, &Item{PartNumber: "SRCH-003", ItemType: "part", Description: "alpha gizmo"}, nil)

	items, err := repo.List(ctx, ListOptions{Search: "alpha"})
	if err != nil {
		t.Fatalf("List: %v", err)
	}
	if len(items) != 2 {
		t.Errorf("expected 2 items matching 'alpha', got %d", len(items))
	}
}

func TestRevisionStatusUpdate(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	item := &Item{PartNumber: "STAT-001", ItemType: "part", Description: "status test"}
	if err := repo.Create(ctx, item, map[string]any{"v": 1}); err != nil {
		t.Fatalf("Create: %v", err)
	}

	status := "released"
	if err := repo.UpdateRevisionStatus(ctx, item.ID, 1, &status, nil); err != nil {
		t.Fatalf("UpdateRevisionStatus: %v", err)
	}

	rev, err := repo.GetRevision(ctx, item.ID, 1)
	if err != nil {
		t.Fatalf("GetRevision: %v", err)
	}
	if rev.Status != "released" {
		t.Errorf("status: got %q, want %q", rev.Status, "released")
	}
}

func TestRevisionLabelsUpdate(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	item := &Item{PartNumber: "LBL-001", ItemType: "part", Description: "label test"}
	if err := repo.Create(ctx, item, nil); err != nil {
		t.Fatalf("Create: %v", err)
	}

	labels := []string{"prototype", "urgent"}
	if err := repo.UpdateRevisionStatus(ctx, item.ID, 1, nil, labels); err != nil {
		t.Fatalf("UpdateRevisionStatus: %v", err)
	}

	rev, err := repo.GetRevision(ctx, item.ID, 1)
	if err != nil {
		t.Fatalf("GetRevision: %v", err)
	}
	if len(rev.Labels) != 2 {
		t.Errorf("labels count: got %d, want 2", len(rev.Labels))
	}
}

func TestRevisionCompare(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	item := &Item{PartNumber: "CMP-001", ItemType: "part", Description: "compare test"}
	if err := repo.Create(ctx, item, map[string]any{"color": "red", "weight": 10}); err != nil {
		t.Fatalf("Create: %v", err)
	}

	// Rev 2: change color, remove weight, add size
	repo.CreateRevision(ctx, &Revision{
		ItemID:     item.ID,
		Properties: map[string]any{"color": "blue", "size": "large"},
	})

	diff, err := repo.CompareRevisions(ctx, item.ID, 1, 2)
	if err != nil {
		t.Fatalf("CompareRevisions: %v", err)
	}

	if len(diff.Added) == 0 {
		t.Error("expected added fields (size)")
	}
	if len(diff.Removed) == 0 {
		t.Error("expected removed fields (weight)")
	}
	if len(diff.Changed) == 0 {
		t.Error("expected changed fields (color)")
	}
}

func TestRevisionRollback(t *testing.T) {
	database := mustConnectTestDB(t)
	repo := NewItemRepository(database)
	ctx := context.Background()

	item := &Item{PartNumber: "RBK-001", ItemType: "part", Description: "rollback test"}
	if err := repo.Create(ctx, item, map[string]any{"version": "original"}); err != nil {
		t.Fatalf("Create: %v", err)
	}

	// Rev 2: change property
	repo.CreateRevision(ctx, &Revision{
		ItemID:     item.ID,
		Properties: map[string]any{"version": "modified"},
	})

	// Rollback to rev 1 — should create rev 3
	comment := "rollback to rev 1"
	rev3, err := repo.CreateRevisionFromExisting(ctx, item.ID, 1, comment, nil)
	if err != nil {
		t.Fatalf("CreateRevisionFromExisting: %v", err)
	}
	if rev3.RevisionNumber != 3 {
		t.Errorf("revision number: got %d, want 3", rev3.RevisionNumber)
	}

	// Rev 3 should have rev 1's properties
	got, err := repo.GetRevision(ctx, item.ID, 3)
	if err != nil {
		t.Fatalf("GetRevision: %v", err)
	}
	if got.Properties["version"] != "original" {
		t.Errorf("rolled back version: got %v, want %q", got.Properties["version"], "original")
	}
}

func TestProjectItemAssociationsByCode(t *testing.T) {
	database := mustConnectTestDB(t)
	projRepo := NewProjectRepository(database)
	itemRepo := NewItemRepository(database)
	ctx := context.Background()

	proj := &Project{Code: "BYTAG", Name: "Tag Project"}
	projRepo.Create(ctx, proj)

	item := &Item{PartNumber: "TAG-001", ItemType: "part", Description: "taggable"}
	itemRepo.Create(ctx, item, nil)

	// Tag by code
	if err := projRepo.AddItemToProjectByCode(ctx, item.ID, "BYTAG"); err != nil {
		t.Fatalf("AddItemToProjectByCode: %v", err)
	}

	projects, err := projRepo.GetProjectsForItem(ctx, item.ID)
	if err != nil {
		t.Fatalf("GetProjectsForItem: %v", err)
	}
	if len(projects) != 1 {
		t.Fatalf("expected 1 project, got %d", len(projects))
	}
	if projects[0].Code != "BYTAG" {
		t.Errorf("project code: got %q, want %q", projects[0].Code, "BYTAG")
	}

	// Untag by code
	if err := projRepo.RemoveItemFromProjectByCode(ctx, item.ID, "BYTAG"); err != nil {
		t.Fatalf("RemoveItemFromProjectByCode: %v", err)
	}

	projects, _ = projRepo.GetProjectsForItem(ctx, item.ID)
	if len(projects) != 0 {
		t.Errorf("expected 0 projects after removal, got %d", len(projects))
	}
}

func TestListByProject(t *testing.T) {
	database := mustConnectTestDB(t)
	projRepo := NewProjectRepository(database)
	itemRepo := NewItemRepository(database)
	ctx := context.Background()

	proj := &Project{Code: "FILT", Name: "Filter Project"}
	projRepo.Create(ctx, proj)

	// Create 3 items, tag only 2
	for i := 0; i < 3; i++ {
		item := &Item{
			PartNumber:  fmt.Sprintf("FILT-%04d", i),
			ItemType:    "part",
			Description: fmt.Sprintf("filter item %d", i),
		}
		itemRepo.Create(ctx, item, nil)
		if i < 2 {
			projRepo.AddItemToProjectByCode(ctx, item.ID, "FILT")
		}
	}

	items, err := itemRepo.List(ctx, ListOptions{Project: "FILT"})
	if err != nil {
		t.Fatalf("List with project filter: %v", err)
	}
	if len(items) != 2 {
		t.Errorf("expected 2 items in project FILT, got %d", len(items))
	}
}