package models

import "fmt"

// Flat/Denormalized Views
//
// This file provides flattened data structures with fully qualified names
// for easier querying and analysis of database schemas without navigating
// nested hierarchies.

// FlatColumn represents a column with full database context in a single structure.
// It includes fully qualified names for easy identification and querying.
type FlatColumn struct {
	DatabaseName       string `json:"database_name" yaml:"database_name" xml:"database_name"`
	SchemaName         string `json:"schema_name" yaml:"schema_name" xml:"schema_name"`
	TableName          string `json:"table_name" yaml:"table_name" xml:"table_name"`
	ColumnName         string `json:"column_name" yaml:"column_name" xml:"column_name"`
	FullyQualifiedName string `json:"fully_qualified_name" yaml:"fully_qualified_name" xml:"fully_qualified_name"` // database.schema.table.column
	Type               string `json:"type" yaml:"type" xml:"type"`
	Length             int    `json:"length,omitempty" yaml:"length,omitempty" xml:"length,omitempty"`
	Precision          int    `json:"precision,omitempty" yaml:"precision,omitempty" xml:"precision,omitempty"`
	Scale              int    `json:"scale,omitempty" yaml:"scale,omitempty" xml:"scale,omitempty"`
	NotNull            bool   `json:"not_null" yaml:"not_null" xml:"not_null"`
	Default            any    `json:"default,omitempty" yaml:"default,omitempty" xml:"default,omitempty"`
	AutoIncrement      bool   `json:"auto_increment" yaml:"auto_increment" xml:"auto_increment"`
	IsPrimaryKey       bool   `json:"is_primary_key" yaml:"is_primary_key" xml:"is_primary_key"`
	Description        string `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Comment            string `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
}

// ToFlatColumns converts a Database to a slice of FlatColumns for denormalized access to all columns.
func (d *Database) ToFlatColumns() []*FlatColumn {
	flatColumns := make([]*FlatColumn, 0)

	for _, schema := range d.Schemas {
		for _, table := range schema.Tables {
			for _, column := range table.Columns {
				flatColumns = append(flatColumns, &FlatColumn{
					DatabaseName:       d.Name,
					SchemaName:         schema.Name,
					TableName:          table.Name,
					ColumnName:         column.Name,
					FullyQualifiedName: fmt.Sprintf("%s.%s.%s.%s", d.Name, schema.Name, table.Name, column.Name),
					Type:               column.Type,
					Length:             column.Length,
					Precision:          column.Precision,
					Scale:              column.Scale,
					NotNull:            column.NotNull,
					Default:            column.Default,
					AutoIncrement:      column.AutoIncrement,
					IsPrimaryKey:       column.IsPrimaryKey,
					Description:        column.Description,
					Comment:            column.Comment,
				})
			}
		}
	}

	return flatColumns
}

// FlatTable represents a table with full database context and aggregated counts.
type FlatTable struct {
	DatabaseName       string `json:"database_name" yaml:"database_name" xml:"database_name"`
	SchemaName         string `json:"schema_name" yaml:"schema_name" xml:"schema_name"`
	TableName          string `json:"table_name" yaml:"table_name" xml:"table_name"`
	FullyQualifiedName string `json:"fully_qualified_name" yaml:"fully_qualified_name" xml:"fully_qualified_name"` // database.schema.table
	Description        string `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Comment            string `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Tablespace         string `json:"tablespace,omitempty" yaml:"tablespace,omitempty" xml:"tablespace,omitempty"`
	ColumnCount        int    `json:"column_count" yaml:"column_count" xml:"column_count"`
	ConstraintCount    int    `json:"constraint_count" yaml:"constraint_count" xml:"constraint_count"`
	IndexCount         int    `json:"index_count" yaml:"index_count" xml:"index_count"`
}

// ToFlatTables converts a Database to a slice of FlatTables for denormalized access to all tables.
func (d *Database) ToFlatTables() []*FlatTable {
	flatTables := make([]*FlatTable, 0)

	for _, schema := range d.Schemas {
		for _, table := range schema.Tables {
			flatTables = append(flatTables, &FlatTable{
				DatabaseName:       d.Name,
				SchemaName:         schema.Name,
				TableName:          table.Name,
				FullyQualifiedName: fmt.Sprintf("%s.%s.%s", d.Name, schema.Name, table.Name),
				Description:        table.Description,
				Comment:            table.Comment,
				Tablespace:         table.Tablespace,
				ColumnCount:        len(table.Columns),
				ConstraintCount:    len(table.Constraints),
				IndexCount:         len(table.Indexes),
			})
		}
	}

	return flatTables
}

// FlatConstraint represents a constraint with full database context and resolved references.
type FlatConstraint struct {
	DatabaseName       string         `json:"database_name" yaml:"database_name" xml:"database_name"`
	SchemaName         string         `json:"schema_name" yaml:"schema_name" xml:"schema_name"`
	TableName          string         `json:"table_name" yaml:"table_name" xml:"table_name"`
	ConstraintName     string         `json:"constraint_name" yaml:"constraint_name" xml:"constraint_name"`
	FullyQualifiedName string         `json:"fully_qualified_name" yaml:"fully_qualified_name" xml:"fully_qualified_name"` // database.schema.table.constraint
	Type               ConstraintType `json:"type" yaml:"type" xml:"type"`
	Columns            []string       `json:"columns" yaml:"columns" xml:"columns"`
	Expression         string         `json:"expression,omitempty" yaml:"expression,omitempty" xml:"expression,omitempty"`
	ReferencedTable    string         `json:"referenced_table,omitempty" yaml:"referenced_table,omitempty" xml:"referenced_table,omitempty"`
	ReferencedSchema   string         `json:"referenced_schema,omitempty" yaml:"referenced_schema,omitempty" xml:"referenced_schema,omitempty"`
	ReferencedColumns  []string       `json:"referenced_columns,omitempty" yaml:"referenced_columns,omitempty" xml:"referenced_columns,omitempty"`
	ReferencedFQN      string         `json:"referenced_fqn,omitempty" yaml:"referenced_fqn,omitempty" xml:"referenced_fqn,omitempty"` // Fully qualified reference
	OnDelete           string         `json:"on_delete,omitempty" yaml:"on_delete,omitempty" xml:"on_delete,omitempty"`
	OnUpdate           string         `json:"on_update,omitempty" yaml:"on_update,omitempty" xml:"on_update,omitempty"`
}

// ToFlatConstraints converts a Database to a slice of FlatConstraints for denormalized access to all constraints.
func (d *Database) ToFlatConstraints() []*FlatConstraint {
	flatConstraints := make([]*FlatConstraint, 0)

	for _, schema := range d.Schemas {
		for _, table := range schema.Tables {
			for _, constraint := range table.Constraints {
				fc := &FlatConstraint{
					DatabaseName:       d.Name,
					SchemaName:         schema.Name,
					TableName:          table.Name,
					ConstraintName:     constraint.Name,
					FullyQualifiedName: fmt.Sprintf("%s.%s.%s.%s", d.Name, schema.Name, table.Name, constraint.Name),
					Type:               constraint.Type,
					Columns:            constraint.Columns,
					Expression:         constraint.Expression,
					ReferencedTable:    constraint.ReferencedTable,
					ReferencedSchema:   constraint.ReferencedSchema,
					ReferencedColumns:  constraint.ReferencedColumns,
					OnDelete:           constraint.OnDelete,
					OnUpdate:           constraint.OnUpdate,
				}

				// Build fully qualified reference name for foreign keys
				if constraint.Type == ForeignKeyConstraint && constraint.ReferencedTable != "" {
					fc.ReferencedFQN = fmt.Sprintf("%s.%s.%s", d.Name, constraint.ReferencedSchema, constraint.ReferencedTable)
				}

				flatConstraints = append(flatConstraints, fc)
			}
		}
	}

	return flatConstraints
}

// FlatRelationship represents a relationship with full database context and fully qualified table names.
type FlatRelationship struct {
	DatabaseName     string       `json:"database_name" yaml:"database_name" xml:"database_name"`
	RelationshipName string       `json:"relationship_name" yaml:"relationship_name" xml:"relationship_name"`
	Type             RelationType `json:"type" yaml:"type" xml:"type"`
	FromFQN          string       `json:"from_fqn" yaml:"from_fqn" xml:"from_fqn"` // database.schema.table
	ToFQN            string       `json:"to_fqn" yaml:"to_fqn" xml:"to_fqn"`       // database.schema.table
	FromTable        string       `json:"from_table" yaml:"from_table" xml:"from_table"`
	FromSchema       string       `json:"from_schema" yaml:"from_schema" xml:"from_schema"`
	ToTable          string       `json:"to_table" yaml:"to_table" xml:"to_table"`
	ToSchema         string       `json:"to_schema" yaml:"to_schema" xml:"to_schema"`
	ForeignKey       string       `json:"foreign_key" yaml:"foreign_key" xml:"foreign_key"`
	ThroughTableFQN  string       `json:"through_table_fqn,omitempty" yaml:"through_table_fqn,omitempty" xml:"through_table_fqn,omitempty"`
	Description      string       `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
}

// ToFlatRelationships converts a Database to a slice of FlatRelationships for denormalized access to all relationships.
func (d *Database) ToFlatRelationships() []*FlatRelationship {
	flatRelationships := make([]*FlatRelationship, 0)

	for _, schema := range d.Schemas {
		for _, table := range schema.Tables {
			for _, relationship := range table.Relationships {
				fr := &FlatRelationship{
					DatabaseName:     d.Name,
					RelationshipName: relationship.Name,
					Type:             relationship.Type,
					FromFQN:          fmt.Sprintf("%s.%s.%s", d.Name, relationship.FromSchema, relationship.FromTable),
					ToFQN:            fmt.Sprintf("%s.%s.%s", d.Name, relationship.ToSchema, relationship.ToTable),
					FromTable:        relationship.FromTable,
					FromSchema:       relationship.FromSchema,
					ToTable:          relationship.ToTable,
					ToSchema:         relationship.ToSchema,
					ForeignKey:       relationship.ForeignKey,
					Description:      relationship.Description,
				}

				// Add through table FQN for many-to-many relationships
				if relationship.ThroughTable != "" {
					fr.ThroughTableFQN = fmt.Sprintf("%s.%s.%s", d.Name, relationship.ThroughSchema, relationship.ThroughTable)
				}

				flatRelationships = append(flatRelationships, fr)
			}
		}
	}

	return flatRelationships
}