package dctx

import (
	"encoding/xml"
	"fmt"
	"os"
	"strings"

	"github.com/google/uuid"

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

// Writer implements the writers.Writer interface for DCTX format
type Writer struct {
	options      *writers.WriterOptions
	fieldGuidMap map[string]string // key: "table.column", value: guid
	keyGuidMap   map[string]string // key: "table.index", value: guid
	tableGuidMap map[string]string // key: "table", value: guid
}

// NewWriter creates a new DCTX writer with the given options
func NewWriter(options *writers.WriterOptions) *Writer {
	return &Writer{
		options:      options,
		fieldGuidMap: make(map[string]string),
		keyGuidMap:   make(map[string]string),
		tableGuidMap: make(map[string]string),
	}
}

// WriteDatabase is not implemented for DCTX
func (w *Writer) WriteDatabase(db *models.Database) error {
	return fmt.Errorf("writing a full database is not supported for DCTX, please write a single schema")
}

// WriteSchema writes a schema to the writer in DCTX format
func (w *Writer) WriteSchema(schema *models.Schema) error {
	dctx := models.DCTXDictionary{
		Name:    schema.Name,
		Version: "1",
		Tables:  make([]models.DCTXTable, len(schema.Tables)),
	}

	tableSlice := make([]*models.Table, 0, len(schema.Tables))

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

	// Pass 1: Create fields and populate fieldGuidMap
	for i, table := range tableSlice {
		dctx.Tables[i] = w.mapTableFields(table)
	}

	// Pass 2: Create keys and populate keyGuidMap
	for i, table := range tableSlice {
		dctx.Tables[i].Keys = w.mapTableKeys(table)
	}

	// Pass 3: Collect all relationships (from schema and tables)
	var allRelations []*models.Relationship

	// Add schema-level relations
	allRelations = append(allRelations, schema.Relations...)

	// Add table-level relationships
	for _, table := range tableSlice {
		for _, rel := range table.Relationships {
			// Check if this relationship is already in the list (avoid duplicates)
			isDuplicate := false
			for _, existing := range allRelations {
				if existing.Name == rel.Name &&
					existing.FromTable == rel.FromTable &&
					existing.ToTable == rel.ToTable {
					isDuplicate = true
					break
				}
			}
			if !isDuplicate {
				allRelations = append(allRelations, rel)
			}
		}
	}

	// Map all relations to DCTX format
	dctx.Relations = make([]models.DCTXRelation, len(allRelations))
	for i, rel := range allRelations {
		dctx.Relations[i] = w.mapRelation(rel, schema)
	}

	output, err := xml.MarshalIndent(dctx, "", "  ")
	if err != nil {
		return err
	}

	file, err := os.Create(w.options.OutputPath)
	if err != nil {
		return err
	}
	defer file.Close()

	if _, err := file.Write([]byte(xml.Header)); err != nil {
		return err
	}

	_, err = file.Write(output)
	return err
}

// WriteTable writes a single table to the writer in DCTX format
func (w *Writer) WriteTable(table *models.Table) error {
	dctxTable := w.mapTableFields(table)
	dctxTable.Keys = w.mapTableKeys(table)

	output, err := xml.MarshalIndent(dctxTable, "", "  ")
	if err != nil {
		return err
	}
	file, err := os.Create(w.options.OutputPath)
	if err != nil {
		return err
	}
	defer file.Close()

	_, err = file.Write(output)
	return err
}

func (w *Writer) mapTableFields(table *models.Table) models.DCTXTable {
	// Generate prefix (first 3 chars, or full name if shorter)
	prefix := table.Name
	if len(table.Name) > 3 {
		prefix = table.Name[:3]
	}

	// Use GUID from model if available, otherwise generate a new one
	tableGuid := table.GUID
	if tableGuid == "" {
		tableGuid = w.newGUID()
	}
	w.tableGuidMap[table.Name] = tableGuid

	dctxTable := models.DCTXTable{
		Guid:        tableGuid,
		Name:        table.Name,
		Prefix:      prefix,
		Description: table.Comment,
		Fields:      make([]models.DCTXField, len(table.Columns)),
		Options: []models.DCTXOption{
			{
				Property:      "SQL",
				PropertyType:  "1",
				PropertyValue: "1",
			},
		},
	}

	i := 0
	for _, column := range table.Columns {
		dctxTable.Fields[i] = w.mapField(column)
		i++
	}

	return dctxTable
}

func (w *Writer) mapTableKeys(table *models.Table) []models.DCTXKey {
	keys := make([]models.DCTXKey, len(table.Indexes))
	i := 0
	for _, index := range table.Indexes {
		keys[i] = w.mapKey(index, table)
		i++
	}
	return keys
}

func (w *Writer) mapField(column *models.Column) models.DCTXField {
	// Use GUID from model if available, otherwise generate a new one
	guid := column.GUID
	if guid == "" {
		guid = w.newGUID()
	}
	fieldKey := fmt.Sprintf("%s.%s", column.Table, column.Name)
	w.fieldGuidMap[fieldKey] = guid

	return models.DCTXField{
		Guid:     guid,
		Name:     column.Name,
		DataType: w.mapDataType(column.Type),
		Size:     column.Length,
	}
}

func (w *Writer) mapDataType(dataType string) string {
	switch dataType {
	case "integer", "int", "int4", "serial":
		return "LONG"
	case "bigint", "int8", "bigserial":
		return "DECIMAL"
	case "smallint", "int2":
		return "SHORT"
	case "boolean", "bool":
		return "BYTE"
	case "text", "varchar", "char":
		return "CSTRING"
	case "date":
		return "DATE"
	case "time":
		return "TIME"
	case "timestamp", "timestamptz":
		return "STRING"
	case "decimal", "numeric":
		return "DECIMAL"
	default:
		return "STRING"
	}
}

func (w *Writer) mapKey(index *models.Index, table *models.Table) models.DCTXKey {
	// Use GUID from model if available, otherwise generate a new one
	guid := index.GUID
	if guid == "" {
		guid = w.newGUID()
	}
	keyKey := fmt.Sprintf("%s.%s", table.Name, index.Name)
	w.keyGuidMap[keyKey] = guid

	key := models.DCTXKey{
		Guid:        guid,
		Name:        index.Name,
		Primary:     strings.HasSuffix(index.Name, "_pkey"),
		Unique:      index.Unique,
		Components:  make([]models.DCTXComponent, len(index.Columns)),
		Description: index.Comment,
	}

	for i, colName := range index.Columns {
		fieldKey := fmt.Sprintf("%s.%s", table.Name, colName)
		fieldID := w.fieldGuidMap[fieldKey]
		key.Components[i] = models.DCTXComponent{
			Guid:    w.newGUID(),
			FieldId: fieldID,
			Order:   i + 1,
			Ascend:  true,
		}
	}

	return key
}

func (w *Writer) mapRelation(rel *models.Relationship, schema *models.Schema) models.DCTXRelation {
	// Find the foreign key constraint from the 'from' table
	var fromTable *models.Table
	for _, t := range schema.Tables {
		if t.Name == rel.FromTable {
			fromTable = t
			break
		}
	}

	var constraint *models.Constraint
	if fromTable != nil {
		for _, c := range fromTable.Constraints {
			if c.Name == rel.ForeignKey {
				constraint = c
				break
			}
		}
	}

	var foreignKeyGUID string
	var fkColumns []string
	if constraint != nil {
		fkColumns = constraint.Columns
		// In DCTX, a relation is often linked by a foreign key which is an index.
		// We'll look for an index that matches the constraint columns.
		for _, index := range fromTable.Indexes {
			if strings.Join(index.Columns, ",") == strings.Join(constraint.Columns, ",") {
				keyKey := fmt.Sprintf("%s.%s", fromTable.Name, index.Name)
				foreignKeyGUID = w.keyGuidMap[keyKey]
				break
			}
		}
	}

	// Find the primary key of the 'to' table
	var toTable *models.Table
	for _, t := range schema.Tables {
		if t.Name == rel.ToTable {
			toTable = t
			break
		}
	}
	var primaryKeyGUID string
	var pkColumns []string

	// Use referenced columns from the constraint if available
	if constraint != nil && len(constraint.ReferencedColumns) > 0 {
		pkColumns = constraint.ReferencedColumns
	}

	if toTable != nil {
		// Find the matching primary key index
		for _, index := range toTable.Indexes {
			// If we have referenced columns, try to match them
			if len(pkColumns) > 0 {
				if strings.Join(index.Columns, ",") == strings.Join(pkColumns, ",") {
					keyKey := fmt.Sprintf("%s.%s", toTable.Name, index.Name)
					primaryKeyGUID = w.keyGuidMap[keyKey]
					break
				}
			} else if strings.HasSuffix(index.Name, "_pkey") {
				// Fall back to finding primary key by naming convention
				keyKey := fmt.Sprintf("%s.%s", toTable.Name, index.Name)
				primaryKeyGUID = w.keyGuidMap[keyKey]
				pkColumns = index.Columns
				break
			}
		}
	}

	// Create field mappings
	// NOTE: DCTX has backwards naming - ForeignMapping contains PRIMARY table fields,
	// and PrimaryMapping contains FOREIGN table fields
	var foreignMappings []models.DCTXFieldMapping // Will contain primary table fields
	var primaryMappings []models.DCTXFieldMapping // Will contain foreign table fields

	// Map foreign key columns (from foreign table) to PrimaryMapping
	for _, colName := range fkColumns {
		fieldKey := fmt.Sprintf("%s.%s", rel.FromTable, colName)
		if fieldGUID, exists := w.fieldGuidMap[fieldKey]; exists {
			primaryMappings = append(primaryMappings, models.DCTXFieldMapping{
				Guid:  w.newGUID(),
				Field: fieldGUID,
			})
		}
	}

	// Map primary key columns (from primary table) to ForeignMapping
	for _, colName := range pkColumns {
		fieldKey := fmt.Sprintf("%s.%s", rel.ToTable, colName)
		if fieldGUID, exists := w.fieldGuidMap[fieldKey]; exists {
			foreignMappings = append(foreignMappings, models.DCTXFieldMapping{
				Guid:  w.newGUID(),
				Field: fieldGUID,
			})
		}
	}

	// Get OnDelete and OnUpdate actions from the constraint
	onDelete := ""
	onUpdate := ""
	if constraint != nil {
		onDelete = w.mapReferentialAction(constraint.OnDelete)
		onUpdate = w.mapReferentialAction(constraint.OnUpdate)
	}

	return models.DCTXRelation{
		Guid:            rel.GUID,                      // Use GUID from relationship model
		PrimaryTable:    w.tableGuidMap[rel.ToTable],   // GUID of the 'to' table (e.g., users)
		ForeignTable:    w.tableGuidMap[rel.FromTable], // GUID of the 'from' table (e.g., posts)
		PrimaryKey:      primaryKeyGUID,
		ForeignKey:      foreignKeyGUID,
		Delete:          onDelete,
		Update:          onUpdate,
		ForeignMappings: foreignMappings,
		PrimaryMappings: primaryMappings,
	}
}

// mapReferentialAction maps SQL referential actions to DCTX format
func (w *Writer) mapReferentialAction(action string) string {
	switch strings.ToUpper(action) {
	case "RESTRICT":
		return "RESTRICT_SERVER"
	case "CASCADE":
		return "CASCADE_SERVER"
	case "SET NULL":
		return "SET_NULL_SERVER"
	case "SET DEFAULT":
		return "SET_DEFAULT_SERVER"
	case "NO ACTION":
		return "NO_ACTION_SERVER"
	default:
		return ""
	}
}

func (w *Writer) newGUID() string {
	return "{" + uuid.New().String() + "}"
}