package drawdb

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

	"git.warky.dev/wdevs/relspecgo/pkg/models"
	"git.warky.dev/wdevs/relspecgo/pkg/writers"
)

func TestWriter_WriteTable(t *testing.T) {
	table := models.InitTable("users", "public")
	table.Description = "User accounts table"

	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "bigint"
	idCol.IsPrimaryKey = true
	idCol.AutoIncrement = true
	idCol.NotNull = true
	idCol.Comment = "Primary key"
	table.Columns["id"] = idCol

	emailCol := models.InitColumn("email", "users", "public")
	emailCol.Type = "varchar(255)"
	emailCol.NotNull = true
	emailCol.Comment = "User email"
	table.Columns["email"] = emailCol

	nameCol := models.InitColumn("name", "users", "public")
	nameCol.Type = "varchar(100)"
	table.Columns["name"] = nameCol

	// Add index
	emailIdx := models.InitIndex("idx_users_email", "users", "public")
	emailIdx.Columns = []string{"email"}
	emailIdx.Unique = true
	emailIdx.Table = "users"
	emailIdx.Schema = "public"
	table.Indexes["idx_users_email"] = emailIdx

	tmpDir := t.TempDir()
	outputPath := filepath.Join(tmpDir, "test.json")

	opts := &writers.WriterOptions{
		OutputPath: outputPath,
	}

	writer := NewWriter(opts)
	err := writer.WriteTable(table)
	if err != nil {
		t.Fatalf("WriteTable() error = %v", err)
	}

	content, err := os.ReadFile(outputPath)
	if err != nil {
		t.Fatalf("Failed to read output file: %v", err)
	}

	var result DrawDBSchema
	if err := json.Unmarshal(content, &result); err != nil {
		t.Fatalf("Failed to unmarshal JSON: %v", err)
	}

	if len(result.Tables) != 1 {
		t.Fatalf("Expected 1 table, got %d", len(result.Tables))
	}

	drawTable := result.Tables[0]
	if drawTable.Name != "users" {
		t.Errorf("Expected table name 'users', got '%s'", drawTable.Name)
	}

	if drawTable.Schema != "public" {
		t.Errorf("Expected schema 'public', got '%s'", drawTable.Schema)
	}

	if drawTable.Comment != "User accounts table" {
		t.Errorf("Expected comment 'User accounts table', got '%s'", drawTable.Comment)
	}

	if len(drawTable.Fields) != 3 {
		t.Errorf("Expected 3 fields, got %d", len(drawTable.Fields))
	}

	// Verify id field
	var idField *DrawDBField
	for _, field := range drawTable.Fields {
		if field.Name == "id" {
			idField = field
			break
		}
	}

	if idField == nil {
		t.Fatal("Field 'id' not found")
	}

	if !idField.Primary {
		t.Error("Field 'id' should be primary")
	}

	if !idField.NotNull {
		t.Error("Field 'id' should be not null")
	}

	if !idField.Increment {
		t.Error("Field 'id' should be auto-increment")
	}

	if idField.Comment != "Primary key" {
		t.Errorf("Expected id comment 'Primary key', got '%s'", idField.Comment)
	}

	// Verify email field
	var emailField *DrawDBField
	for _, field := range drawTable.Fields {
		if field.Name == "email" {
			emailField = field
			break
		}
	}

	if emailField == nil {
		t.Fatal("Field 'email' not found")
	}

	if !emailField.NotNull {
		t.Error("Field 'email' should be not null")
	}

	if emailField.Comment != "User email" {
		t.Errorf("Expected email comment 'User email', got '%s'", emailField.Comment)
	}

	// Verify index
	if len(drawTable.Indexes) != 1 {
		t.Errorf("Expected 1 index, got %d", len(drawTable.Indexes))
	}

	idx := drawTable.Indexes[0]
	if idx.Name != "idx_users_email" {
		t.Errorf("Expected index name 'idx_users_email', got '%s'", idx.Name)
	}

	if !idx.Unique {
		t.Error("Email index should be unique")
	}
}

func TestWriter_WriteDatabase_WithRelationships(t *testing.T) {
	db := models.InitDatabase("test_db")
	schema := models.InitSchema("public")

	// Create users table
	usersTable := models.InitTable("users", "public")
	usersTable.Description = "User accounts"

	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "bigint"
	idCol.IsPrimaryKey = true
	idCol.AutoIncrement = true
	idCol.NotNull = true
	usersTable.Columns["id"] = idCol

	emailCol := models.InitColumn("email", "users", "public")
	emailCol.Type = "varchar(255)"
	emailCol.NotNull = true
	usersTable.Columns["email"] = emailCol

	// Create posts table
	postsTable := models.InitTable("posts", "public")
	postsTable.Description = "Blog posts"

	postIdCol := models.InitColumn("id", "posts", "public")
	postIdCol.Type = "bigint"
	postIdCol.IsPrimaryKey = true
	postIdCol.NotNull = true
	postsTable.Columns["id"] = postIdCol

	userIdCol := models.InitColumn("user_id", "posts", "public")
	userIdCol.Type = "bigint"
	userIdCol.NotNull = true
	postsTable.Columns["user_id"] = userIdCol

	titleCol := models.InitColumn("title", "posts", "public")
	titleCol.Type = "varchar(200)"
	titleCol.NotNull = true
	postsTable.Columns["title"] = titleCol

	publishedCol := models.InitColumn("published", "posts", "public")
	publishedCol.Type = "boolean"
	publishedCol.Default = "false"
	postsTable.Columns["published"] = publishedCol

	// Add foreign key constraint
	fk := models.InitConstraint("fk_posts_user", models.ForeignKeyConstraint)
	fk.Table = "posts"
	fk.Schema = "public"
	fk.Columns = []string{"user_id"}
	fk.ReferencedTable = "users"
	fk.ReferencedSchema = "public"
	fk.ReferencedColumns = []string{"id"}
	fk.OnDelete = "CASCADE"
	fk.OnUpdate = "CASCADE"
	postsTable.Constraints["fk_posts_user"] = fk

	schema.Tables = append(schema.Tables, usersTable, postsTable)
	db.Schemas = append(db.Schemas, schema)

	tmpDir := t.TempDir()
	outputPath := filepath.Join(tmpDir, "test.json")

	opts := &writers.WriterOptions{
		OutputPath: outputPath,
	}

	writer := NewWriter(opts)
	err := writer.WriteDatabase(db)
	if err != nil {
		t.Fatalf("WriteDatabase() error = %v", err)
	}

	content, err := os.ReadFile(outputPath)
	if err != nil {
		t.Fatalf("Failed to read output file: %v", err)
	}

	var result DrawDBSchema
	if err := json.Unmarshal(content, &result); err != nil {
		t.Fatalf("Failed to unmarshal JSON: %v", err)
	}

	if len(result.Tables) != 2 {
		t.Fatalf("Expected 2 tables, got %d", len(result.Tables))
	}

	// Verify tables
	var usersTableResult, postsTableResult *DrawDBTable
	for _, table := range result.Tables {
		if table.Name == "users" {
			usersTableResult = table
		} else if table.Name == "posts" {
			postsTableResult = table
		}
	}

	if usersTableResult == nil {
		t.Fatal("Users table not found")
	}

	if postsTableResult == nil {
		t.Fatal("Posts table not found")
	}

	// Verify relationship
	if len(result.Relationships) != 1 {
		t.Fatalf("Expected 1 relationship, got %d", len(result.Relationships))
	}

	rel := result.Relationships[0]
	if rel.Name != "fk_posts_user" {
		t.Errorf("Expected relationship name 'fk_posts_user', got '%s'", rel.Name)
	}

	// Verify relationship references correct tables
	if rel.StartTableID != postsTableResult.ID {
		t.Error("Relationship should start from posts table")
	}

	if rel.EndTableID != usersTableResult.ID {
		t.Error("Relationship should end at users table")
	}

	if rel.DeleteConstraint != "CASCADE" {
		t.Errorf("Expected DELETE constraint 'CASCADE', got '%s'", rel.DeleteConstraint)
	}

	if rel.UpdateConstraint != "CASCADE" {
		t.Errorf("Expected UPDATE constraint 'CASCADE', got '%s'", rel.UpdateConstraint)
	}

	// Verify published column default
	var publishedField *DrawDBField
	for _, field := range postsTableResult.Fields {
		if field.Name == "published" {
			publishedField = field
			break
		}
	}

	if publishedField == nil {
		t.Fatal("Field 'published' not found")
	}

	if publishedField.Default != "false" {
		t.Errorf("Expected published default 'false', got '%s'", publishedField.Default)
	}
}

func TestWriter_WriteSchema(t *testing.T) {
	schema := models.InitSchema("public")

	table := models.InitTable("users", "public")
	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "bigint"
	idCol.IsPrimaryKey = true
	table.Columns["id"] = idCol

	usernameCol := models.InitColumn("username", "users", "public")
	usernameCol.Type = "varchar(50)"
	usernameCol.NotNull = true
	table.Columns["username"] = usernameCol

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

	tmpDir := t.TempDir()
	outputPath := filepath.Join(tmpDir, "test.json")

	opts := &writers.WriterOptions{
		OutputPath: outputPath,
	}

	writer := NewWriter(opts)
	err := writer.WriteSchema(schema)
	if err != nil {
		t.Fatalf("WriteSchema() error = %v", err)
	}

	content, err := os.ReadFile(outputPath)
	if err != nil {
		t.Fatalf("Failed to read output file: %v", err)
	}

	var result DrawDBSchema
	if err := json.Unmarshal(content, &result); err != nil {
		t.Fatalf("Failed to unmarshal JSON: %v", err)
	}

	if len(result.Tables) != 1 {
		t.Fatalf("Expected 1 table, got %d", len(result.Tables))
	}

	drawTable := result.Tables[0]
	if drawTable.Name != "users" {
		t.Errorf("Expected table name 'users', got '%s'", drawTable.Name)
	}

	if len(drawTable.Fields) != 2 {
		t.Errorf("Expected 2 fields, got %d", len(drawTable.Fields))
	}
}

func TestWriter_WriteDatabase_MultipleConstraints(t *testing.T) {
	db := models.InitDatabase("test_db")
	schema := models.InitSchema("public")

	// Create users table
	usersTable := models.InitTable("users", "public")
	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "bigint"
	idCol.IsPrimaryKey = true
	usersTable.Columns["id"] = idCol

	// Create posts table
	postsTable := models.InitTable("posts", "public")
	postIdCol := models.InitColumn("id", "posts", "public")
	postIdCol.Type = "bigint"
	postIdCol.IsPrimaryKey = true
	postsTable.Columns["id"] = postIdCol

	userIdCol := models.InitColumn("user_id", "posts", "public")
	userIdCol.Type = "bigint"
	postsTable.Columns["user_id"] = userIdCol

	// Create comments table with two foreign keys
	commentsTable := models.InitTable("comments", "public")
	commentIdCol := models.InitColumn("id", "comments", "public")
	commentIdCol.Type = "bigint"
	commentIdCol.IsPrimaryKey = true
	commentsTable.Columns["id"] = commentIdCol

	commentPostIdCol := models.InitColumn("post_id", "comments", "public")
	commentPostIdCol.Type = "bigint"
	commentPostIdCol.NotNull = true
	commentsTable.Columns["post_id"] = commentPostIdCol

	commentUserIdCol := models.InitColumn("user_id", "comments", "public")
	commentUserIdCol.Type = "bigint"
	commentUserIdCol.NotNull = true
	commentsTable.Columns["user_id"] = commentUserIdCol

	// Add foreign key to posts
	fkPost := models.InitConstraint("fk_comments_post", models.ForeignKeyConstraint)
	fkPost.Table = "comments"
	fkPost.Schema = "public"
	fkPost.Columns = []string{"post_id"}
	fkPost.ReferencedTable = "posts"
	fkPost.ReferencedSchema = "public"
	fkPost.ReferencedColumns = []string{"id"}
	fkPost.OnDelete = "CASCADE"
	commentsTable.Constraints["fk_comments_post"] = fkPost

	// Add foreign key to users
	fkUser := models.InitConstraint("fk_comments_user", models.ForeignKeyConstraint)
	fkUser.Table = "comments"
	fkUser.Schema = "public"
	fkUser.Columns = []string{"user_id"}
	fkUser.ReferencedTable = "users"
	fkUser.ReferencedSchema = "public"
	fkUser.ReferencedColumns = []string{"id"}
	fkUser.OnDelete = "SET NULL"
	commentsTable.Constraints["fk_comments_user"] = fkUser

	schema.Tables = append(schema.Tables, usersTable, postsTable, commentsTable)
	db.Schemas = append(db.Schemas, schema)

	tmpDir := t.TempDir()
	outputPath := filepath.Join(tmpDir, "test.json")

	opts := &writers.WriterOptions{
		OutputPath: outputPath,
	}

	writer := NewWriter(opts)
	err := writer.WriteDatabase(db)
	if err != nil {
		t.Fatalf("WriteDatabase() error = %v", err)
	}

	content, err := os.ReadFile(outputPath)
	if err != nil {
		t.Fatalf("Failed to read output file: %v", err)
	}

	var result DrawDBSchema
	if err := json.Unmarshal(content, &result); err != nil {
		t.Fatalf("Failed to unmarshal JSON: %v", err)
	}

	if len(result.Tables) != 3 {
		t.Fatalf("Expected 3 tables, got %d", len(result.Tables))
	}

	// Verify 2 relationships from comments table
	if len(result.Relationships) != 2 {
		t.Fatalf("Expected 2 relationships, got %d", len(result.Relationships))
	}

	// Verify different delete constraints
	cascadeFound := false
	setNullFound := false

	for _, rel := range result.Relationships {
		if rel.DeleteConstraint == "CASCADE" {
			cascadeFound = true
		}
		if rel.DeleteConstraint == "SET NULL" {
			setNullFound = true
		}
	}

	if !cascadeFound {
		t.Error("Expected at least one CASCADE delete constraint")
	}

	if !setNullFound {
		t.Error("Expected at least one SET NULL delete constraint")
	}
}

func TestWriter_WriteTable_EmptyPath(t *testing.T) {
	table := models.InitTable("users", "public")
	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "bigint"
	table.Columns["id"] = idCol

	// When OutputPath is empty, it should print to stdout (not error)
	opts := &writers.WriterOptions{
		OutputPath: "",
	}

	writer := NewWriter(opts)
	err := writer.WriteTable(table)
	if err != nil {
		t.Fatalf("WriteTable() with empty path should not error, got: %v", err)
	}
}

func TestGetPrimaryKey(t *testing.T) {
	table := models.InitTable("users", "public")

	idCol := models.InitColumn("id", "users", "public")
	idCol.Type = "bigint"
	idCol.IsPrimaryKey = true
	table.Columns["id"] = idCol

	emailCol := models.InitColumn("email", "users", "public")
	emailCol.Type = "varchar"
	table.Columns["email"] = emailCol

	pk := table.GetPrimaryKey()
	if pk == nil {
		t.Fatal("Expected primary key, got nil")
	}

	if pk.Name != "id" {
		t.Errorf("Expected primary key name 'id', got '%s'", pk.Name)
	}
}

func TestGetForeignKeys(t *testing.T) {
	table := models.InitTable("posts", "public")

	idCol := models.InitColumn("id", "posts", "public")
	idCol.Type = "bigint"
	idCol.IsPrimaryKey = true
	table.Columns["id"] = idCol

	userIdCol := models.InitColumn("user_id", "posts", "public")
	userIdCol.Type = "bigint"
	table.Columns["user_id"] = userIdCol

	// Add foreign key constraint
	fk := models.InitConstraint("fk_posts_user", models.ForeignKeyConstraint)
	fk.Columns = []string{"user_id"}
	fk.ReferencedTable = "users"
	fk.ReferencedColumns = []string{"id"}
	table.Constraints["fk_posts_user"] = fk

	fks := table.GetForeignKeys()
	if len(fks) != 1 {
		t.Errorf("Expected 1 foreign key, got %d", len(fks))
	}

	if fks[0].Type != models.ForeignKeyConstraint {
		t.Error("Expected foreign key constraint type")
	}
}