package dctx

import (
	"os"
	"path/filepath"
	"testing"

	"git.warky.dev/wdevs/relspecgo/pkg/models"
	"git.warky.dev/wdevs/relspecgo/pkg/readers"
	dctxreader "git.warky.dev/wdevs/relspecgo/pkg/readers/dctx"
	"git.warky.dev/wdevs/relspecgo/pkg/writers"
	"github.com/stretchr/testify/assert"
)

func TestRoundTrip_WriteAndRead(t *testing.T) {
	// 1. Create a sample schema with relationships
	schema := models.InitSchema("public")
	schema.Name = "TestDB"

	// Table 1: users
	usersTable := models.InitTable("users", "public")
	usersTable.Comment = "Stores user information"
	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "serial"
	idCol.IsPrimaryKey = true
	idCol.NotNull = true
	usersTable.Columns["id"] = idCol
	nameCol := models.InitColumn("name", "users", "public")
	nameCol.Type = "varchar"
	nameCol.Length = 100
	usersTable.Columns["name"] = nameCol
	pkIndex := models.InitIndex("users_pkey", "users", "public")
	pkIndex.Unique = true
	pkIndex.Columns = []string{"id"}
	usersTable.Indexes["users_pkey"] = pkIndex

	pkConstraint := models.InitConstraint("users_pkey", models.PrimaryKeyConstraint)
	pkConstraint.Table = "users"
	pkConstraint.Schema = "public"
	pkConstraint.Columns = []string{"id"}
	usersTable.Constraints["users_pkey"] = pkConstraint

	schema.Tables = append(schema.Tables, usersTable)

	// Table 2: posts
	postsTable := models.InitTable("posts", "public")
	postsTable.Comment = "Stores blog posts"
	postIDCol := models.InitColumn("id", "posts", "public")
	postIDCol.Type = "serial"
	postIDCol.IsPrimaryKey = true
	postIDCol.NotNull = true
	postsTable.Columns["id"] = postIDCol
	titleCol := models.InitColumn("title", "posts", "public")
	titleCol.Type = "varchar"
	titleCol.Length = 255
	postsTable.Columns["title"] = titleCol
	userIDCol := models.InitColumn("user_id", "posts", "public")
	userIDCol.Type = "integer"
	postsTable.Columns["user_id"] = userIDCol
	postsPKIndex := models.InitIndex("posts_pkey", "posts", "public")
	postsPKIndex.Unique = true
	postsPKIndex.Columns = []string{"id"}
	postsTable.Indexes["posts_pkey"] = postsPKIndex

	fkIndex := models.InitIndex("posts_user_id_idx", "posts", "public")
	fkIndex.Columns = []string{"user_id"}
	postsTable.Indexes["posts_user_id_idx"] = fkIndex

	postsPKConstraint := models.InitConstraint("posts_pkey", models.PrimaryKeyConstraint)
	postsPKConstraint.Table = "posts"
	postsPKConstraint.Schema = "public"
	postsPKConstraint.Columns = []string{"id"}
	postsTable.Constraints["posts_pkey"] = postsPKConstraint

	// Foreign key constraint
	fkConstraint := models.InitConstraint("fk_posts_users", models.ForeignKeyConstraint)
	fkConstraint.Table = "posts"
	fkConstraint.Schema = "public"
	fkConstraint.Columns = []string{"user_id"}
	fkConstraint.ReferencedTable = "users"
	fkConstraint.ReferencedSchema = "public"
	fkConstraint.ReferencedColumns = []string{"id"}
	fkConstraint.OnDelete = "CASCADE"
	fkConstraint.OnUpdate = "NO ACTION"
	postsTable.Constraints["fk_posts_users"] = fkConstraint

	schema.Tables = append(schema.Tables, postsTable)

	// Relation
	relation := models.InitRelationship("posts_to_users", models.OneToMany)
	relation.FromTable = "posts"
	relation.FromSchema = "public"
	relation.ToTable = "users"
	relation.ToSchema = "public"
	relation.ForeignKey = "fk_posts_users"
	schema.Relations = append(schema.Relations, relation)

	// 2. Write the schema to DCTX
	outputPath := filepath.Join(t.TempDir(), "roundtrip.dctx")
	writerOpts := &writers.WriterOptions{
		OutputPath: outputPath,
	}
	writer := NewWriter(writerOpts)

	err := writer.WriteSchema(schema)
	assert.NoError(t, err)

	// Verify file was created
	_, err = os.Stat(outputPath)
	assert.NoError(t, err, "Output file should exist")

	// 3. Read the schema back from DCTX
	readerOpts := &readers.ReaderOptions{
		FilePath: outputPath,
	}
	reader := dctxreader.NewReader(readerOpts)

	db, err := reader.ReadDatabase()
	assert.NoError(t, err)
	assert.NotNil(t, db)

	// 4. Verify the schema was read correctly
	assert.Len(t, db.Schemas, 1, "Should have one schema")
	readSchema := db.Schemas[0]

	// Verify tables
	assert.Len(t, readSchema.Tables, 2, "Should have two tables")

	// Find users and posts tables
	var readUsersTable, readPostsTable *models.Table
	for _, table := range readSchema.Tables {
		switch table.Name {
		case "users":
			readUsersTable = table
		case "posts":
			readPostsTable = table
		}
	}

	assert.NotNil(t, readUsersTable, "Users table should exist")
	assert.NotNil(t, readPostsTable, "Posts table should exist")

	// Verify columns
	assert.Len(t, readUsersTable.Columns, 2, "Users table should have 2 columns")
	assert.NotNil(t, readUsersTable.Columns["id"])
	assert.NotNil(t, readUsersTable.Columns["name"])

	assert.Len(t, readPostsTable.Columns, 3, "Posts table should have 3 columns")
	assert.NotNil(t, readPostsTable.Columns["id"])
	assert.NotNil(t, readPostsTable.Columns["title"])
	assert.NotNil(t, readPostsTable.Columns["user_id"])

	// Verify relationships were preserved
	// The DCTX reader stores relationships on the foreign table (posts)
	assert.NotEmpty(t, readPostsTable.Relationships, "Posts table should have relationships")

	// Debug: print all relationships
	t.Logf("Posts table has %d relationships:", len(readPostsTable.Relationships))
	for name, rel := range readPostsTable.Relationships {
		t.Logf("  - %s: from=%s to=%s fk=%s", name, rel.FromTable, rel.ToTable, rel.ForeignKey)
	}

	// Find the relationship - the reader creates it with FromTable as primary and ToTable as foreign
	var postsToUsersRel *models.Relationship
	for _, rel := range readPostsTable.Relationships {
		// The relationship should have posts as ToTable (foreign) and users as FromTable (primary)
		if rel.FromTable == "users" && rel.ToTable == "posts" {
			postsToUsersRel = rel
			break
		}
	}

	assert.NotNil(t, postsToUsersRel, "Should have relationship from users to posts")
	if postsToUsersRel != nil {
		assert.Equal(t, "users", postsToUsersRel.FromTable, "Relationship should come from users (primary) table")
		assert.Equal(t, "posts", postsToUsersRel.ToTable, "Relationship should point to posts (foreign) table")
		assert.NotEmpty(t, postsToUsersRel.ForeignKey, "Relationship should have a foreign key")
	}

	// Verify foreign key constraint
	fks := readPostsTable.GetForeignKeys()
	assert.NotEmpty(t, fks, "Posts table should have foreign keys")

	if len(fks) > 0 {
		fk := fks[0]
		assert.Equal(t, models.ForeignKeyConstraint, fk.Type)
		assert.Contains(t, fk.Columns, "user_id")
		assert.Equal(t, "users", fk.ReferencedTable)
		assert.Contains(t, fk.ReferencedColumns, "id")
		assert.Equal(t, "CASCADE", fk.OnDelete)
	}

	t.Logf("Round-trip test successful: wrote and read back %d tables with relationships", len(readSchema.Tables))
}