package db

import (
	"database/sql"
	"testing"

	_ "github.com/duckdb/duckdb-go/v2"
)

// setupInvariantsTestDB creates an in-memory database with the full schema
func setupInvariantsTestDB(t *testing.T) *sql.DB {
	t.Helper()

	db, err := sql.Open("duckdb", ":memory:")
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	schema, err := ReadSchemaSQL()
	if err != nil {
		t.Fatalf("failed to read schema: %v", err)
	}

	_, err = db.Exec(schema)
	if err != nil {
		t.Fatalf("failed to create schema: %v", err)
	}

	return db
}

// insertDataset creates a test dataset and returns its ID
func insertDataset(t *testing.T, db *sql.DB, id, name string) {
	t.Helper()
	_, err := db.Exec(
		"INSERT INTO dataset (id, name, type, active) VALUES (?, ?, 'structured', true)",
		id, name,
	)
	if err != nil {
		t.Fatalf("failed to insert dataset: %v", err)
	}
}

// insertLocation creates a test location and returns its ID
func insertLocation(t *testing.T, db *sql.DB, id, datasetID, name string) {
	t.Helper()
	_, err := db.Exec(
		`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active) 
		 VALUES (?, ?, ?, -36.8485, 174.7633, 'Pacific/Auckland', true)`,
		id, datasetID, name,
	)
	if err != nil {
		t.Fatalf("failed to insert location: %v", err)
	}
}

// insertCluster creates a test cluster
func insertCluster(t *testing.T, db *sql.DB, id, datasetID, locationID, name string) {
	t.Helper()
	_, err := db.Exec(
		`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active) 
		 VALUES (?, ?, ?, ?, 48000, true)`,
		id, datasetID, locationID, name,
	)
	if err != nil {
		t.Fatalf("failed to insert cluster: %v", err)
	}
}

// insertFile creates a test file
func insertFile(t *testing.T, db *sql.DB, id, hash, locationID string) {
	t.Helper()
	_, err := db.Exec(
		`INSERT INTO file (id, file_name, xxh64_hash, location_id, timestamp_local, duration, sample_rate, active) 
		 VALUES (?, 'test.wav', ?, ?, CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		id, hash, locationID,
	)
	if err != nil {
		t.Fatalf("failed to insert file: %v", err)
	}
}

// ============================================================================
// Phase 1, Test 1: UniqueFileHash invariant
// Spec: validation.allium - UniqueFileHash
// "for f1 in Files: for f2 in Files: f1 != f2 implies f1.xxh64_hash != f2.xxh64_hash"
// ============================================================================

func TestInvariant_UniqueFileHash(t *testing.T) {
	db := setupInvariantsTestDB(t)
	defer db.Close()

	// Setup: create dataset → location → cluster → file
	insertDataset(t, db, "ds_test12345", "Test Dataset")
	insertLocation(t, db, "loc_test1234", "ds_test12345", "Test Location")
	insertCluster(t, db, "clustest1234", "ds_test12345", "loc_test1234", "Test Cluster")

	// Insert first file with a specific hash
	insertFile(t, db, "filetest1234567890123", "abcd1234efgh5678", "loc_test1234")

	// Test: Attempting to insert a second file with the same hash should fail
	t.Run("duplicate hash rejected", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO file (id, file_name, xxh64_hash, location_id, timestamp_local, duration, sample_rate, active) 
			 VALUES ('filetest_diffhash01', 'test2.wav', 'abcd1234efgh5678', 'loc_test1234', CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		)
		if err == nil {
			t.Error("expected error for duplicate xxh64_hash, got nil")
		}
	})

	// Test: Different hash should succeed
	t.Run("different hash accepted", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO file (id, file_name, xxh64_hash, location_id, timestamp_local, duration, sample_rate, active) 
			 VALUES ('filetest_diffhash02', 'test3.wav', '9876zyxw5432vuts', 'loc_test1234', CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		)
		if err != nil {
			t.Errorf("unexpected error for different hash: %v", err)
		}
	})

	// Test: Same hash with inactive file should still fail (constraint applies to all rows)
	t.Run("inactive file still blocks duplicate", func(t *testing.T) {
		// Mark first file as inactive
		_, err := db.Exec("UPDATE file SET active = false WHERE id = 'filetest1234567890123'")
		if err != nil {
			t.Fatalf("failed to deactivate file: %v", err)
		}

		// Attempt duplicate hash with new file
		_, err = db.Exec(
			`INSERT INTO file (id, file_name, xxh64_hash, location_id, timestamp_local, duration, sample_rate, active) 
			 VALUES ('filetest_inactblk01', 'test4.wav', 'abcd1234efgh5678', 'loc_test1234', CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		)
		if err == nil {
			t.Error("expected error for duplicate xxh64_hash even with inactive file, got nil")
		}
	})
}

// ============================================================================
// Phase 1, Test 2: LocationBelongsToDataset invariant
// Spec: validation.allium - LocationBelongsToDataset
// "for l in Locations: l.dataset exists and is valid"
// ============================================================================

func TestInvariant_LocationBelongsToDataset(t *testing.T) {
	db := setupInvariantsTestDB(t)
	defer db.Close()

	// Setup: create dataset
	insertDataset(t, db, "ds_valid123456", "Valid Dataset")

	t.Run("location with valid dataset accepted", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active) 
			 VALUES ('loc_valid12345', 'ds_valid123456', 'Valid Location', -36.8485, 174.7633, 'Pacific/Auckland', true)`,
		)
		if err != nil {
			t.Errorf("unexpected error: %v", err)
		}
	})

	t.Run("location with nonexistent dataset rejected", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active) 
			 VALUES ('loc_bad_ds_001', 'ds_nonexistent', 'Bad Location', -36.8485, 174.7633, 'Pacific/Auckland', true)`,
		)
		if err == nil {
			t.Error("expected error for nonexistent dataset_id, got nil")
		}
	})

	t.Run("location with deleted dataset rejected", func(t *testing.T) {
		// Create and then soft-delete a dataset
		insertDataset(t, db, "ds_del_temp_01", "To Be Deleted")
		_, err := db.Exec("UPDATE dataset SET active = false WHERE id = 'ds_del_temp_01'")
		if err != nil {
			t.Fatalf("failed to deactivate dataset: %v", err)
		}

		// Try to create location pointing to inactive dataset
		_, err = db.Exec(
			`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active) 
			 VALUES ('loc_inact_ds01', 'ds_del_temp_01', 'Inactive DS Location', -36.8485, 174.7633, 'Pacific/Auckland', true)`,
		)
		// Note: FK constraint may still allow this depending on implementation
		// This test documents the current behavior
		t.Logf("Insert location to inactive dataset: err=%v", err)
	})

	t.Run("duplicate location name in same dataset rejected", func(t *testing.T) {
		// Try to insert location with same name in same dataset
		_, err := db.Exec(
			`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active) 
			 VALUES ('loc_dup_name01', 'ds_valid123456', 'Valid Location', -40.9006, 174.8860, 'Pacific/Auckland', true)`,
		)
		if err == nil {
			t.Error("expected error for duplicate location name in same dataset, got nil")
		}
	})

	t.Run("same location name in different datasets accepted", func(t *testing.T) {
		// Create second dataset
		insertDataset(t, db, "ds_second_1234", "Second Dataset")

		// Same name as in first dataset should work
		_, err := db.Exec(
			`INSERT INTO location (id, dataset_id, name, latitude, longitude, timezone_id, active) 
			 VALUES ('loc_same_name2', 'ds_second_1234', 'Valid Location', -36.8485, 174.7633, 'Pacific/Auckland', true)`,
		)
		if err != nil {
			t.Errorf("unexpected error for same name in different dataset: %v", err)
		}
	})
}

// ============================================================================
// Phase 1, Test 3: ClusterBelongsToLocation invariant
// Spec: validation.allium - ClusterBelongsToLocation, LocationBelongsToDataset (cross-check)
// "for c in Clusters: c.location exists AND c.location.dataset = c.dataset"
// ============================================================================

func TestInvariant_ClusterBelongsToLocation(t *testing.T) {
	db := setupInvariantsTestDB(t)
	defer db.Close()

	// Setup: create two separate dataset hierarchies
	insertDataset(t, db, "ds_cluster_t01", "Cluster Test Dataset 1")
	insertDataset(t, db, "ds_cluster_t02", "Cluster Test Dataset 2")
	insertLocation(t, db, "loc_clust_t001", "ds_cluster_t01", "Location in DS1")
	insertLocation(t, db, "loc_clust_t002", "ds_cluster_t02", "Location in DS2")

	t.Run("cluster with valid location accepted", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active) 
			 VALUES ('cl_valid123456', 'ds_cluster_t01', 'loc_clust_t001', 'Valid Cluster', 48000, true)`,
		)
		if err != nil {
			t.Errorf("unexpected error: %v", err)
		}
	})

	t.Run("cluster with nonexistent location rejected", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active) 
			 VALUES ('cl_badloc12345', 'ds_cluster_t01', 'loc_nonexistent', 'Bad Location Cluster', 48000, true)`,
		)
		if err == nil {
			t.Error("expected error for nonexistent location_id, got nil")
		}
	})

	t.Run("cluster with mismatched dataset and location rejected", func(t *testing.T) {
		// Attempt: cluster.dataset_id = ds1, but cluster.location_id = location from ds2
		_, err := db.Exec(
			`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active) 
			 VALUES ('cl_mismatch001', 'ds_cluster_t01', 'loc_clust_t002', 'Mismatched Cluster', 48000, true)`,
		)
		// This tests the business logic invariant from the spec
		// The schema allows this via FKs, but the application should reject it
		// If the schema doesn't prevent this, the test documents the gap
		t.Logf("Mismatched dataset/location: err=%v", err)
	})

	t.Run("duplicate cluster name in same location rejected", func(t *testing.T) {
		// Try to insert cluster with same name in same location
		_, err := db.Exec(
			`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active) 
			 VALUES ('cl_dup_name_01', 'ds_cluster_t01', 'loc_clust_t001', 'Valid Cluster', 48000, true)`,
		)
		if err == nil {
			t.Error("expected error for duplicate cluster name in same location, got nil")
		}
	})

	t.Run("same cluster name in different locations accepted", func(t *testing.T) {
		// Same name but different location should work
		_, err := db.Exec(
			`INSERT INTO cluster (id, dataset_id, location_id, name, sample_rate, active) 
			 VALUES ('cl_same_nam_02', 'ds_cluster_t02', 'loc_clust_t002', 'Valid Cluster', 48000, true)`,
		)
		if err != nil {
			t.Errorf("unexpected error for same name in different location: %v", err)
		}
	})
}

// ============================================================================
// Cross-invariant: Hierarchical integrity
// Tests that the full hierarchy chain is enforced
// ============================================================================

func TestInvariant_HierarchicalIntegrity(t *testing.T) {
	db := setupInvariantsTestDB(t)
	defer db.Close()

	// Build complete hierarchy
	insertDataset(t, db, "ds_hier_test01", "Hierarchy Test")
	insertLocation(t, db, "loc_hier_test1", "ds_hier_test01", "Hier Location")
	insertCluster(t, db, "cl_hier_test01", "ds_hier_test01", "loc_hier_test1", "Hier Cluster")

	t.Run("file must have valid location", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO file (id, file_name, xxh64_hash, location_id, timestamp_local, duration, sample_rate, active) 
			 VALUES ('file_badloc001', 'test.wav', '1111111111111111', 'loc_nonexistent', CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		)
		if err == nil {
			t.Error("expected error for file with invalid location, got nil")
		}
	})

	t.Run("file with valid location but invalid cluster rejected", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO file (id, file_name, xxh64_hash, location_id, cluster_id, timestamp_local, duration, sample_rate, active) 
			 VALUES ('file_badcl_001', 'test.wav', '2222222222222222', 'loc_hier_test1', 'cl_nonexistent', CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		)
		if err == nil {
			t.Error("expected error for file with invalid cluster, got nil")
		}
	})

	t.Run("valid file through full hierarchy accepted", func(t *testing.T) {
		_, err := db.Exec(
			`INSERT INTO file (id, file_name, xxh64_hash, location_id, cluster_id, timestamp_local, duration, sample_rate, active) 
			 VALUES ('file_valid0001', 'test.wav', '3333333333333333', 'loc_hier_test1', 'cl_hier_test01', CURRENT_TIMESTAMP, 1.0, 48000, true)`,
		)
		if err != nil {
			t.Errorf("unexpected error: %v", err)
		}
	})
}