// Package merge provides utilities for merging database schemas.
// It allows combining schemas from multiple sources while avoiding duplicates,
// supporting only additive operations (no deletion or modification of existing items).
package merge

import (
	"fmt"
	"strings"

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

// MergeResult represents the result of a merge operation
type MergeResult struct {
	SchemasAdded   int
	TablesAdded    int
	ColumnsAdded   int
	RelationsAdded int
	DomainsAdded   int
	EnumsAdded     int
	ViewsAdded     int
	SequencesAdded int
}

// MergeOptions contains options for merge operations
type MergeOptions struct {
	SkipDomains    bool
	SkipRelations  bool
	SkipEnums      bool
	SkipViews      bool
	SkipSequences  bool
	SkipTableNames map[string]bool // Tables to skip during merge (keyed by table name)
}

// MergeDatabases merges the source database into the target database.
// Only adds missing items; existing items are not modified.
func MergeDatabases(target, source *models.Database, opts *MergeOptions) *MergeResult {
	if opts == nil {
		opts = &MergeOptions{}
	}

	result := &MergeResult{}

	if target == nil || source == nil {
		return result
	}

	// Merge schemas and their contents
	result.merge(target, source, opts)

	return result
}

func (r *MergeResult) merge(target, source *models.Database, opts *MergeOptions) {
	// Create maps of existing schemas for quick lookup
	existingSchemas := make(map[string]*models.Schema)
	for _, schema := range target.Schemas {
		existingSchemas[schema.SQLName()] = schema
	}

	// Merge schemas
	for _, srcSchema := range source.Schemas {
		schemaName := srcSchema.SQLName()
		if tgtSchema, exists := existingSchemas[schemaName]; exists {
			// Schema exists, merge its contents
			r.mergeSchemaContents(tgtSchema, srcSchema, opts)
		} else {
			// Schema doesn't exist, add it
			newSchema := cloneSchema(srcSchema)
			target.Schemas = append(target.Schemas, newSchema)
			r.SchemasAdded++
		}
	}

	// Merge domains if not skipped
	if !opts.SkipDomains {
		r.mergeDomains(target, source)
	}
}

func (r *MergeResult) mergeSchemaContents(target, source *models.Schema, opts *MergeOptions) {
	// Merge tables
	r.mergeTables(target, source, opts)

	// Merge views if not skipped
	if !opts.SkipViews {
		r.mergeViews(target, source)
	}

	// Merge sequences if not skipped
	if !opts.SkipSequences {
		r.mergeSequences(target, source)
	}

	// Merge enums if not skipped
	if !opts.SkipEnums {
		r.mergeEnums(target, source)
	}

	// Merge relations if not skipped
	if !opts.SkipRelations {
		r.mergeRelations(target, source)
	}
}

func (r *MergeResult) mergeTables(schema *models.Schema, source *models.Schema, opts *MergeOptions) {
	// Create map of existing tables
	existingTables := make(map[string]*models.Table)
	for _, table := range schema.Tables {
		existingTables[table.SQLName()] = table
	}

	// Merge tables
	for _, srcTable := range source.Tables {
		tableName := srcTable.SQLName()

		// Skip if table is in the skip list (case-insensitive)
		if opts != nil && opts.SkipTableNames != nil && opts.SkipTableNames[strings.ToLower(tableName)] {
			continue
		}

		if tgtTable, exists := existingTables[tableName]; exists {
			// Table exists, merge its columns
			r.mergeColumns(tgtTable, srcTable)
		} else {
			// Table doesn't exist, add it
			newTable := cloneTable(srcTable)
			schema.Tables = append(schema.Tables, newTable)
			r.TablesAdded++
			// Count columns in the newly added table
			r.ColumnsAdded += len(newTable.Columns)
		}
	}
}

func (r *MergeResult) mergeColumns(table *models.Table, srcTable *models.Table) {
	// Create map of existing columns
	existingColumns := make(map[string]*models.Column)
	for colName := range table.Columns {
		existingColumns[colName] = table.Columns[colName]
	}

	// Merge columns
	for colName, srcCol := range srcTable.Columns {
		if _, exists := existingColumns[colName]; !exists {
			// Column doesn't exist, add it
			newCol := cloneColumn(srcCol)
			table.Columns[colName] = newCol
			r.ColumnsAdded++
		}
	}
}

func (r *MergeResult) mergeViews(schema *models.Schema, source *models.Schema) {
	// Create map of existing views
	existingViews := make(map[string]*models.View)
	for _, view := range schema.Views {
		existingViews[view.SQLName()] = view
	}

	// Merge views
	for _, srcView := range source.Views {
		viewName := srcView.SQLName()
		if _, exists := existingViews[viewName]; !exists {
			// View doesn't exist, add it
			newView := cloneView(srcView)
			schema.Views = append(schema.Views, newView)
			r.ViewsAdded++
		}
	}
}

func (r *MergeResult) mergeSequences(schema *models.Schema, source *models.Schema) {
	// Create map of existing sequences
	existingSequences := make(map[string]*models.Sequence)
	for _, seq := range schema.Sequences {
		existingSequences[seq.SQLName()] = seq
	}

	// Merge sequences
	for _, srcSeq := range source.Sequences {
		seqName := srcSeq.SQLName()
		if _, exists := existingSequences[seqName]; !exists {
			// Sequence doesn't exist, add it
			newSeq := cloneSequence(srcSeq)
			schema.Sequences = append(schema.Sequences, newSeq)
			r.SequencesAdded++
		}
	}
}

func (r *MergeResult) mergeEnums(schema *models.Schema, source *models.Schema) {
	// Create map of existing enums
	existingEnums := make(map[string]*models.Enum)
	for _, enum := range schema.Enums {
		existingEnums[enum.SQLName()] = enum
	}

	// Merge enums
	for _, srcEnum := range source.Enums {
		enumName := srcEnum.SQLName()
		if _, exists := existingEnums[enumName]; !exists {
			// Enum doesn't exist, add it
			newEnum := cloneEnum(srcEnum)
			schema.Enums = append(schema.Enums, newEnum)
			r.EnumsAdded++
		}
	}
}

func (r *MergeResult) mergeRelations(schema *models.Schema, source *models.Schema) {
	// Create map of existing relations
	existingRelations := make(map[string]*models.Relationship)
	for _, rel := range schema.Relations {
		existingRelations[rel.SQLName()] = rel
	}

	// Merge relations
	for _, srcRel := range source.Relations {
		if _, exists := existingRelations[srcRel.SQLName()]; !exists {
			// Relation doesn't exist, add it
			newRel := cloneRelation(srcRel)
			schema.Relations = append(schema.Relations, newRel)
			r.RelationsAdded++
		}
	}
}

func (r *MergeResult) mergeDomains(target *models.Database, source *models.Database) {
	// Create map of existing domains
	existingDomains := make(map[string]*models.Domain)
	for _, domain := range target.Domains {
		existingDomains[domain.SQLName()] = domain
	}

	// Merge domains
	for _, srcDomain := range source.Domains {
		domainName := srcDomain.SQLName()
		if _, exists := existingDomains[domainName]; !exists {
			// Domain doesn't exist, add it
			newDomain := cloneDomain(srcDomain)
			target.Domains = append(target.Domains, newDomain)
			r.DomainsAdded++
		}
	}
}

// Clone functions to create deep copies of models

func cloneSchema(schema *models.Schema) *models.Schema {
	if schema == nil {
		return nil
	}
	newSchema := &models.Schema{
		Name:        schema.Name,
		Description: schema.Description,
		Owner:       schema.Owner,
		Comment:     schema.Comment,
		Sequence:    schema.Sequence,
		UpdatedAt:   schema.UpdatedAt,
		Tables:      make([]*models.Table, 0),
		Views:       make([]*models.View, 0),
		Sequences:   make([]*models.Sequence, 0),
		Enums:       make([]*models.Enum, 0),
		Relations:   make([]*models.Relationship, 0),
	}

	if schema.Permissions != nil {
		newSchema.Permissions = make(map[string]string)
		for k, v := range schema.Permissions {
			newSchema.Permissions[k] = v
		}
	}

	if schema.Metadata != nil {
		newSchema.Metadata = make(map[string]interface{})
		for k, v := range schema.Metadata {
			newSchema.Metadata[k] = v
		}
	}

	if schema.Scripts != nil {
		newSchema.Scripts = make([]*models.Script, len(schema.Scripts))
		copy(newSchema.Scripts, schema.Scripts)
	}

	// Clone tables
	for _, table := range schema.Tables {
		newSchema.Tables = append(newSchema.Tables, cloneTable(table))
	}

	// Clone views
	for _, view := range schema.Views {
		newSchema.Views = append(newSchema.Views, cloneView(view))
	}

	// Clone sequences
	for _, seq := range schema.Sequences {
		newSchema.Sequences = append(newSchema.Sequences, cloneSequence(seq))
	}

	// Clone enums
	for _, enum := range schema.Enums {
		newSchema.Enums = append(newSchema.Enums, cloneEnum(enum))
	}

	// Clone relations
	for _, rel := range schema.Relations {
		newSchema.Relations = append(newSchema.Relations, cloneRelation(rel))
	}

	return newSchema
}

func cloneTable(table *models.Table) *models.Table {
	if table == nil {
		return nil
	}
	newTable := &models.Table{
		Name:        table.Name,
		Description: table.Description,
		Schema:      table.Schema,
		Comment:     table.Comment,
		Sequence:    table.Sequence,
		UpdatedAt:   table.UpdatedAt,
		Columns:     make(map[string]*models.Column),
		Constraints: make(map[string]*models.Constraint),
		Indexes:     make(map[string]*models.Index),
	}

	if table.Metadata != nil {
		newTable.Metadata = make(map[string]interface{})
		for k, v := range table.Metadata {
			newTable.Metadata[k] = v
		}
	}

	// Clone columns
	for colName, col := range table.Columns {
		newTable.Columns[colName] = cloneColumn(col)
	}

	// Clone constraints
	for constName, constraint := range table.Constraints {
		newTable.Constraints[constName] = cloneConstraint(constraint)
	}

	// Clone indexes
	for idxName, index := range table.Indexes {
		newTable.Indexes[idxName] = cloneIndex(index)
	}

	return newTable
}

func cloneColumn(col *models.Column) *models.Column {
	if col == nil {
		return nil
	}
	newCol := &models.Column{
		Name:          col.Name,
		Type:          col.Type,
		Description:   col.Description,
		Comment:       col.Comment,
		IsPrimaryKey:  col.IsPrimaryKey,
		NotNull:       col.NotNull,
		Default:       col.Default,
		Precision:     col.Precision,
		Scale:         col.Scale,
		Length:        col.Length,
		Sequence:      col.Sequence,
		AutoIncrement: col.AutoIncrement,
		Collation:     col.Collation,
	}

	return newCol
}

func cloneConstraint(constraint *models.Constraint) *models.Constraint {
	if constraint == nil {
		return nil
	}
	newConstraint := &models.Constraint{
		Type:              constraint.Type,
		Columns:           make([]string, len(constraint.Columns)),
		ReferencedTable:   constraint.ReferencedTable,
		ReferencedSchema:  constraint.ReferencedSchema,
		ReferencedColumns: make([]string, len(constraint.ReferencedColumns)),
		OnUpdate:          constraint.OnUpdate,
		OnDelete:          constraint.OnDelete,
		Expression:        constraint.Expression,
		Name:              constraint.Name,
		Deferrable:        constraint.Deferrable,
		InitiallyDeferred: constraint.InitiallyDeferred,
		Sequence:          constraint.Sequence,
	}
	copy(newConstraint.Columns, constraint.Columns)
	copy(newConstraint.ReferencedColumns, constraint.ReferencedColumns)
	return newConstraint
}

func cloneIndex(index *models.Index) *models.Index {
	if index == nil {
		return nil
	}
	newIndex := &models.Index{
		Name:        index.Name,
		Description: index.Description,
		Table:       index.Table,
		Schema:      index.Schema,
		Columns:     make([]string, len(index.Columns)),
		Unique:      index.Unique,
		Type:        index.Type,
		Where:       index.Where,
		Concurrent:  index.Concurrent,
		Include:     make([]string, len(index.Include)),
		Comment:     index.Comment,
		Sequence:    index.Sequence,
	}
	copy(newIndex.Columns, index.Columns)
	copy(newIndex.Include, index.Include)
	return newIndex
}

func cloneView(view *models.View) *models.View {
	if view == nil {
		return nil
	}
	newView := &models.View{
		Name:        view.Name,
		Description: view.Description,
		Schema:      view.Schema,
		Definition:  view.Definition,
		Comment:     view.Comment,
		Sequence:    view.Sequence,
		Columns:     make(map[string]*models.Column),
	}

	if view.Metadata != nil {
		newView.Metadata = make(map[string]interface{})
		for k, v := range view.Metadata {
			newView.Metadata[k] = v
		}
	}

	// Clone columns
	for colName, col := range view.Columns {
		newView.Columns[colName] = cloneColumn(col)
	}

	return newView
}

func cloneSequence(seq *models.Sequence) *models.Sequence {
	if seq == nil {
		return nil
	}
	newSeq := &models.Sequence{
		Name:          seq.Name,
		Description:   seq.Description,
		Schema:        seq.Schema,
		StartValue:    seq.StartValue,
		MinValue:      seq.MinValue,
		MaxValue:      seq.MaxValue,
		IncrementBy:   seq.IncrementBy,
		CacheSize:     seq.CacheSize,
		Cycle:         seq.Cycle,
		OwnedByTable:  seq.OwnedByTable,
		OwnedByColumn: seq.OwnedByColumn,
		Comment:       seq.Comment,
		Sequence:      seq.Sequence,
	}
	return newSeq
}

func cloneEnum(enum *models.Enum) *models.Enum {
	if enum == nil {
		return nil
	}
	newEnum := &models.Enum{
		Name:   enum.Name,
		Values: make([]string, len(enum.Values)),
		Schema: enum.Schema,
	}
	copy(newEnum.Values, enum.Values)
	return newEnum
}

func cloneRelation(rel *models.Relationship) *models.Relationship {
	if rel == nil {
		return nil
	}
	newRel := &models.Relationship{
		Name:          rel.Name,
		Type:          rel.Type,
		FromTable:     rel.FromTable,
		FromSchema:    rel.FromSchema,
		FromColumns:   make([]string, len(rel.FromColumns)),
		ToTable:       rel.ToTable,
		ToSchema:      rel.ToSchema,
		ToColumns:     make([]string, len(rel.ToColumns)),
		ForeignKey:    rel.ForeignKey,
		ThroughTable:  rel.ThroughTable,
		ThroughSchema: rel.ThroughSchema,
		Description:   rel.Description,
		Sequence:      rel.Sequence,
	}

	if rel.Properties != nil {
		newRel.Properties = make(map[string]string)
		for k, v := range rel.Properties {
			newRel.Properties[k] = v
		}
	}

	copy(newRel.FromColumns, rel.FromColumns)
	copy(newRel.ToColumns, rel.ToColumns)
	return newRel
}

func cloneDomain(domain *models.Domain) *models.Domain {
	if domain == nil {
		return nil
	}
	newDomain := &models.Domain{
		Name:        domain.Name,
		Description: domain.Description,
		Comment:     domain.Comment,
		Sequence:    domain.Sequence,
		Tables:      make([]*models.DomainTable, len(domain.Tables)),
	}

	if domain.Metadata != nil {
		newDomain.Metadata = make(map[string]interface{})
		for k, v := range domain.Metadata {
			newDomain.Metadata[k] = v
		}
	}

	copy(newDomain.Tables, domain.Tables)
	return newDomain
}

// GetMergeSummary returns a human-readable summary of the merge result
func GetMergeSummary(result *MergeResult) string {
	if result == nil {
		return "No merge result available"
	}

	lines := []string{
		"=== Merge Summary ===",
		fmt.Sprintf("Schemas added:     %d", result.SchemasAdded),
		fmt.Sprintf("Tables added:      %d", result.TablesAdded),
		fmt.Sprintf("Columns added:     %d", result.ColumnsAdded),
		fmt.Sprintf("Views added:       %d", result.ViewsAdded),
		fmt.Sprintf("Sequences added:   %d", result.SequencesAdded),
		fmt.Sprintf("Enums added:       %d", result.EnumsAdded),
		fmt.Sprintf("Relations added:   %d", result.RelationsAdded),
		fmt.Sprintf("Domains added:     %d", result.DomainsAdded),
	}

	totalAdded := result.SchemasAdded + result.TablesAdded + result.ColumnsAdded +
		result.ViewsAdded + result.SequencesAdded + result.EnumsAdded +
		result.RelationsAdded + result.DomainsAdded

	lines = append(lines, fmt.Sprintf("Total items added: %d", totalAdded))

	summary := ""
	for _, line := range lines {
		summary += line + "\n"
	}

	return summary
}