package persistence

import (
	"strings"
	"testing"

	"github.com/nexuscrm/backend/internal/domain/schema"
)

func TestBuildColumnDDL(t *testing.T) {
	r := &SchemaRepository{db: nil}

	tests := []struct {
		name          string
		col           schema.ColumnDefinition
		includeUnique bool
		wantContains  []string
		wantNotContain []string
	}{
		{
			name: "simple varchar column",
			col: schema.ColumnDefinition{
				Name: "name",
				Type: "string",
			},
			includeUnique: false,
			wantContains: []string{"`name`"},
		},
		{
			name: "not null column",
			col: schema.ColumnDefinition{
				Name:     "email",
				Type:     "VARCHAR(255)",
				Nullable: false,
			},
			includeUnique: false,
			wantContains: []string{"`email`", "NOT NULL"},
		},
		{
			name: "nullable column omits NOT NULL",
			col: schema.ColumnDefinition{
				Name:     "phone",
				Type:     "VARCHAR(50)",
				Nullable: true,
			},
			includeUnique:  false,
			wantContains:   []string{"`phone`", "VARCHAR(50)"},
			wantNotContain: []string{"NOT NULL"},
		},
		{
			name: "column with default",
			col: schema.ColumnDefinition{
				Name:    "status",
				Type:    "VARCHAR(255)",
				Default: "'active'",
			},
			includeUnique: false,
			wantContains: []string{"`status`", "DEFAULT 'active'"},
		},
		{
			name: "auto increment column",
			col: schema.ColumnDefinition{
				Name:          "seq",
				Type:          "INT",
				AutoIncrement: true,
			},
			includeUnique: false,
			wantContains: []string{"`seq`", "AUTO_INCREMENT"},
		},
		{
			name: "primary key column",
			col: schema.ColumnDefinition{
				Name:       "Id",
				Type:       "VARCHAR(36)",
				PrimaryKey: true,
			},
			includeUnique: false,
			wantContains: []string{"`Id`", "PRIMARY KEY"},
		},
		{
			name: "unique column with includeUnique=true",
			col: schema.ColumnDefinition{
				Name:   "email",
				Type:   "VARCHAR(255)",
				Unique: true,
			},
			includeUnique: true,
			wantContains: []string{"`email`", "UNIQUE"},
		},
		{
			name: "unique column with includeUnique=false",
			col: schema.ColumnDefinition{
				Name:   "email",
				Type:   "VARCHAR(255)",
				Unique: true,
			},
			includeUnique:  false,
			wantContains:   []string{"`email`"},
			wantNotContain: []string{"UNIQUE"},
		},
		{
			name: "column with explicit length overrides varchar type",
			col: schema.ColumnDefinition{
				Name:   "short_code",
				Type:   "VARCHAR(255)",
				Length: 10,
			},
			includeUnique: false,
			wantContains: []string{"`short_code`", "VARCHAR(10)"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := r.buildColumnDDL(tt.col, tt.includeUnique)

			for _, want := range tt.wantContains {
				if !strings.Contains(got, want) {
					t.Errorf("buildColumnDDL() = %q, want to contain %q", got, want)
				}
			}
			for _, dontWant := range tt.wantNotContain {
				if strings.Contains(got, dontWant) {
					t.Errorf("buildColumnDDL() = %q, should NOT contain %q", got, dontWant)
				}
			}
		})
	}
}

func TestBuildColumnDDL_FormulaField(t *testing.T) {
	t.Skip("Formula fields use TiDB Views instead of GENERATED columns")
}

func TestBuildForeignKeyDDL(t *testing.T) {
	r := &SchemaRepository{db: nil}

	tests := []struct {
		name     string
		fk       schema.ForeignKeyDefinition
		wantParts []string
	}{
		{
			name: "simple FK",
			fk: schema.ForeignKeyDefinition{
				Column:     "AccountId",
				References: "Account(Id)",
			},
			wantParts: []string{"FOREIGN KEY (`AccountId`)", "REFERENCES `Account`(Id)"},
		},
		{
			name: "FK with ON DELETE",
			fk: schema.ForeignKeyDefinition{
				Column:     "OwnerId",
				References: "User(Id)",
				OnDelete:   "CASCADE",
			},
			wantParts: []string{"FOREIGN KEY (`OwnerId`)", "REFERENCES `User`(Id)", "ON DELETE CASCADE"},
		},
		{
			name: "FK with ON DELETE and ON UPDATE",
			fk: schema.ForeignKeyDefinition{
				Column:     "ParentId",
				References: "Account(Id)",
				OnDelete:   "SET NULL",
				OnUpdate:   "CASCADE",
			},
			wantParts: []string{"ON DELETE SET NULL", "ON UPDATE CASCADE"},
		},
		{
			name: "FK references without parens",
			fk: schema.ForeignKeyDefinition{
				Column:     "TypeId",
				References: "Type",
			},
			wantParts: []string{"REFERENCES `Type`"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := r.buildForeignKeyDDL(tt.fk)
			for _, part := range tt.wantParts {
				if !strings.Contains(got, part) {
					t.Errorf("buildForeignKeyDDL() = %q, want to contain %q", got, part)
				}
			}
		})
	}
}

func TestBuildIndexDDL(t *testing.T) {
	r := &SchemaRepository{db: nil}

	tests := []struct {
		name      string
		tableName string
		idx       schema.IndexDefinition
		wantParts []string
	}{
		{
			name:      "regular index with provided name",
			tableName: "Account",
			idx: schema.IndexDefinition{
				Name:    "idx_account_name",
				Columns: []string{"Name"},
			},
			wantParts: []string{"KEY `idx_account_name`", "(`Name`)"},
		},
		{
			name:      "unique index",
			tableName: "Account",
			idx: schema.IndexDefinition{
				Name:    "uniq_email",
				Columns: []string{"Email"},
				Unique:  true,
			},
			wantParts: []string{"UNIQUE KEY `uniq_email`", "(`Email`)"},
		},
		{
			name:      "composite index",
			tableName: "Account",
			idx: schema.IndexDefinition{
				Name:    "idx_name_email",
				Columns: []string{"Name", "Email"},
			},
			wantParts: []string{"KEY `idx_name_email`", "(`Name`, `Email`)"},
		},
		{
			name:      "auto-generated name",
			tableName: "Contact",
			idx: schema.IndexDefinition{
				Columns: []string{"AccountId"},
			},
			wantParts: []string{"KEY `idx_Contact_AccountId`", "(`AccountId`)"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := r.buildIndexDDL(tt.tableName, tt.idx)
			for _, part := range tt.wantParts {
				if !strings.Contains(got, part) {
					t.Errorf("buildIndexDDL() = %q, want to contain %q", got, part)
				}
			}
		})
	}
}

func TestColumnNameRegex(t *testing.T) {
	tests := []struct {
		name     string
		input    string
		expected bool
	}{
		{"simple lowercase", "name", true},
		{"PascalCase", "AccountName", true},
		{"camelCase", "firstName", true},
		{"snake_case", "first_name", true},
		{"starts with underscore", "_system_field", true},
		{"all caps", "ID", true},
		{"with numbers", "field1", true},
		{"starts with number", "1field", false},
		{"contains space", "first name", false},
		{"contains hyphen", "first-name", false},
		{"contains dot", "table.column", false},
		{"empty string", "", false},
		{"special chars", "field@name", false},
		{"semicolon", "field;", false},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got := columnNameRegex.MatchString(tt.input)
			if got != tt.expected {
				t.Errorf("columnNameRegex.MatchString(%q) = %v, want %v", tt.input, got, tt.expected)
			}
		})
	}
}