package drawdb

import (
	"path/filepath"
	"testing"

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

func TestReader_ReadDatabase_Simple(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "simple.json"),
	}

	reader := NewReader(opts)
	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase() error = %v", err)
	}

	if db == nil {
		t.Fatal("ReadDatabase() returned nil database")
	}

	if len(db.Schemas) == 0 {
		t.Fatal("Expected at least one schema")
	}

	// Find public schema
	var publicSchema *models.Schema
	for _, schema := range db.Schemas {
		if schema.Name == "public" {
			publicSchema = schema
			break
		}
	}

	if publicSchema == nil {
		t.Fatal("Public schema not found")
	}

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

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

	if table.Description != "Users table" {
		t.Errorf("Expected table description 'Users table', got '%s'", table.Description)
	}

	if len(table.Columns) != 3 {
		t.Errorf("Expected 3 columns, got %d", len(table.Columns))
	}

	// Verify id column
	idCol, exists := table.Columns["id"]
	if !exists {
		t.Fatal("Column 'id' not found")
	}
	if !idCol.IsPrimaryKey {
		t.Error("Column 'id' should be primary key")
	}
	if !idCol.AutoIncrement {
		t.Error("Column 'id' should be auto-increment")
	}
	if !idCol.NotNull {
		t.Error("Column 'id' should be not null")
	}
	if idCol.Type != "bigint" {
		t.Errorf("Expected id type 'bigint', got '%s'", idCol.Type)
	}
	if idCol.Comment != "Primary key" {
		t.Errorf("Expected id comment 'Primary key', got '%s'", idCol.Comment)
	}

	// Verify email column
	emailCol, exists := table.Columns["email"]
	if !exists {
		t.Fatal("Column 'email' not found")
	}
	if !emailCol.NotNull {
		t.Error("Column 'email' should be not null")
	}
	// DrawDB stores types without length, so just check for varchar
	if emailCol.Type != "varchar" && emailCol.Type != "varchar(255)" {
		t.Errorf("Expected email type 'varchar' or 'varchar(255)', got '%s'", emailCol.Type)
	}
	if emailCol.Comment != "User email" {
		t.Errorf("Expected email comment 'User email', got '%s'", emailCol.Comment)
	}

	// Verify name column (nullable)
	nameCol, exists := table.Columns["name"]
	if !exists {
		t.Fatal("Column 'name' not found")
	}
	if nameCol.NotNull {
		t.Error("Column 'name' should be nullable")
	}

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

	emailIdx, exists := table.Indexes["idx_users_email"]
	if !exists {
		t.Fatal("Index 'idx_users_email' not found")
	}
	if !emailIdx.Unique {
		t.Error("Email index should be unique")
	}
	if len(emailIdx.Columns) != 1 || emailIdx.Columns[0] != "email" {
		t.Error("Email index should have 'email' column")
	}
}

func TestReader_ReadDatabase_Complex(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "complex.json"),
	}

	reader := NewReader(opts)
	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase() error = %v", err)
	}

	if db == nil {
		t.Fatal("ReadDatabase() returned nil database")
	}

	// Check for database name in notes
	if db.Name == "" {
		t.Error("Expected database name to be extracted from notes")
	}

	if len(db.Schemas) == 0 {
		t.Fatal("Expected at least one schema")
	}

	// Find public schema
	var publicSchema *models.Schema
	for _, schema := range db.Schemas {
		if schema.Name == "public" {
			publicSchema = schema
			break
		}
	}

	if publicSchema == nil {
		t.Fatal("Public schema not found")
	}

	// Verify 3 tables: users, posts, comments
	if len(publicSchema.Tables) != 3 {
		t.Fatalf("Expected 3 tables, got %d", len(publicSchema.Tables))
	}

	// Find tables
	var usersTable, postsTable, commentsTable *models.Table
	for _, table := range publicSchema.Tables {
		switch table.Name {
		case "users":
			usersTable = table
		case "posts":
			postsTable = table
		case "comments":
			commentsTable = table
		}
	}

	if usersTable == nil {
		t.Fatal("Users table not found")
	}
	if postsTable == nil {
		t.Fatal("Posts table not found")
	}
	if commentsTable == nil {
		t.Fatal("Comments table not found")
	}

	// Verify users table
	if usersTable.Description != "User accounts" {
		t.Errorf("Expected users description 'User accounts', got '%s'", usersTable.Description)
	}
	if len(usersTable.Columns) != 4 {
		t.Errorf("Expected 4 columns in users table, got %d", len(usersTable.Columns))
	}

	// Verify posts table
	if postsTable.Description != "Blog posts" {
		t.Errorf("Expected posts description 'Blog posts', got '%s'", postsTable.Description)
	}
	if len(postsTable.Columns) != 5 {
		t.Errorf("Expected 5 columns in posts table, got %d", len(postsTable.Columns))
	}

	// Verify default value
	publishedCol, exists := postsTable.Columns["published"]
	if !exists {
		t.Fatal("Column 'published' not found")
	}
	if publishedCol.Default != "false" {
		t.Errorf("Expected published default 'false', got '%v'", publishedCol.Default)
	}

	// Verify foreign key constraints
	if len(postsTable.Constraints) != 1 {
		t.Errorf("Expected 1 constraint on posts table, got %d", len(postsTable.Constraints))
	}

	// Check posts -> users foreign key
	fkPostsUser, exists := postsTable.Constraints["fk_posts_user"]
	if !exists {
		t.Fatal("Foreign key 'fk_posts_user' not found on posts table")
	}
	if fkPostsUser.Type != models.ForeignKeyConstraint {
		t.Error("Expected foreign key constraint type")
	}
	if fkPostsUser.ReferencedTable != "users" {
		t.Errorf("Expected referenced table 'users', got '%s'", fkPostsUser.ReferencedTable)
	}
	if fkPostsUser.OnDelete != "CASCADE" {
		t.Errorf("Expected ON DELETE CASCADE, got '%s'", fkPostsUser.OnDelete)
	}
	if fkPostsUser.OnUpdate != "CASCADE" {
		t.Errorf("Expected ON UPDATE CASCADE, got '%s'", fkPostsUser.OnUpdate)
	}
	if len(fkPostsUser.Columns) != 1 || fkPostsUser.Columns[0] != "user_id" {
		t.Error("Expected FK column 'user_id'")
	}
	if len(fkPostsUser.ReferencedColumns) != 1 || fkPostsUser.ReferencedColumns[0] != "id" {
		t.Error("Expected FK referenced column 'id'")
	}

	// Verify comments table has 2 foreign keys
	if len(commentsTable.Constraints) != 2 {
		t.Errorf("Expected 2 constraints on comments table, got %d", len(commentsTable.Constraints))
	}

	// Check comments -> posts foreign key
	fkCommentsPost, exists := commentsTable.Constraints["fk_comments_post"]
	if !exists {
		t.Fatal("Foreign key 'fk_comments_post' not found")
	}
	if fkCommentsPost.ReferencedTable != "posts" {
		t.Errorf("Expected referenced table 'posts', got '%s'", fkCommentsPost.ReferencedTable)
	}
	if fkCommentsPost.OnDelete != "CASCADE" {
		t.Errorf("Expected ON DELETE CASCADE, got '%s'", fkCommentsPost.OnDelete)
	}
	if len(fkCommentsPost.Columns) != 1 || fkCommentsPost.Columns[0] != "post_id" {
		t.Error("Expected FK column 'post_id'")
	}

	// Check comments -> users foreign key with SET NULL
	fkCommentsUser, exists := commentsTable.Constraints["fk_comments_user"]
	if !exists {
		t.Fatal("Foreign key 'fk_comments_user' not found")
	}
	if fkCommentsUser.ReferencedTable != "users" {
		t.Errorf("Expected referenced table 'users', got '%s'", fkCommentsUser.ReferencedTable)
	}
	if fkCommentsUser.OnDelete != "SET NULL" {
		t.Errorf("Expected ON DELETE SET NULL, got '%s'", fkCommentsUser.OnDelete)
	}
	if len(fkCommentsUser.Columns) != 1 || fkCommentsUser.Columns[0] != "user_id" {
		t.Error("Expected FK column 'user_id'")
	}

	// Verify indexes
	if len(postsTable.Indexes) != 1 {
		t.Errorf("Expected 1 index on posts table, got %d", len(postsTable.Indexes))
	}

	postsIdx, exists := postsTable.Indexes["idx_posts_user_id"]
	if !exists {
		t.Fatal("Index 'idx_posts_user_id' not found")
	}
	if postsIdx.Unique {
		t.Error("Posts user_id index should not be unique")
	}
}

func TestReader_ReadSchema(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "simple.json"),
	}

	reader := NewReader(opts)
	schema, err := reader.ReadSchema()
	if err != nil {
		t.Fatalf("ReadSchema() error = %v", err)
	}

	if schema == nil {
		t.Fatal("ReadSchema() returned nil schema")
	}

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

func TestReader_ReadTable(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "simple.json"),
	}

	reader := NewReader(opts)
	table, err := reader.ReadTable()
	if err != nil {
		t.Fatalf("ReadTable() error = %v", err)
	}

	if table == nil {
		t.Fatal("ReadTable() returned nil table")
	}

	if table.Name != "users" {
		t.Errorf("Expected table name 'users', got '%s'", table.Name)
	}

	if len(table.Columns) != 3 {
		t.Errorf("Expected 3 columns, got %d", len(table.Columns))
	}
}

func TestReader_ReadDatabase_InvalidPath(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: "/nonexistent/file.json",
	}

	reader := NewReader(opts)
	_, err := reader.ReadDatabase()
	if err == nil {
		t.Error("Expected error for invalid file path")
	}
}

func TestReader_ReadDatabase_EmptyPath(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: "",
	}

	reader := NewReader(opts)
	_, err := reader.ReadDatabase()
	if err == nil {
		t.Error("Expected error for empty file path")
	}
}

func TestReader_ReadDatabase_InvalidJSON(t *testing.T) {
	// Create a temporary invalid JSON file
	tmpFile := filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "invalid.json")

	opts := &readers.ReaderOptions{
		FilePath: tmpFile,
	}

	reader := NewReader(opts)
	_, err := reader.ReadDatabase()
	// Should fail since the file doesn't exist or has invalid JSON
	if err == nil {
		t.Error("Expected error for invalid JSON")
	}
}

func TestReader_ReadTable_NoTables(t *testing.T) {
	// This would require a fixture with no tables, which should return an error
	// We'll skip for now as it requires additional test fixtures
}

func TestReader_ReadDatabase_WithMetadata(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "simple.json"),
		Metadata: map[string]interface{}{
			"name": "custom_db_name",
		},
	}

	reader := NewReader(opts)
	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase() error = %v", err)
	}

	if db.Name != "custom_db_name" {
		t.Errorf("Expected database name 'custom_db_name', got '%s'", db.Name)
	}
}

func TestGetPrimaryKey(t *testing.T) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "simple.json"),
	}

	reader := NewReader(opts)
	table, err := reader.ReadTable()
	if err != nil {
		t.Fatalf("ReadTable() error = %v", err)
	}

	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) {
	opts := &readers.ReaderOptions{
		FilePath: filepath.Join("..", "..", "..", "tests", "assets", "drawdb", "complex.json"),
	}

	reader := NewReader(opts)
	db, err := reader.ReadDatabase()
	if err != nil {
		t.Fatalf("ReadDatabase() error = %v", err)
	}

	// Find posts table
	var postsTable *models.Table
	for _, schema := range db.Schemas {
		for _, table := range schema.Tables {
			if table.Name == "posts" {
				postsTable = table
				break
			}
		}
	}

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

	fks := postsTable.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")
	}
}