package models

import "strings"

type DatabaseType string

const (
	PostgresqlDatabaseType DatabaseType = "pgsql"
	MSSQLDatabaseType      DatabaseType = "mssql"
	SqlLiteDatabaseType    DatabaseType = "sqlite"
)

// Database represents the complete database schema
type Database struct {
	Name            string       `json:"name" yaml:"name"`
	Description     string       `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Schemas         []*Schema    `json:"schemas" yaml:"schemas" xml:"schemas"`
	Comment         string       `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	DatabaseType    DatabaseType `json:"database_type,omitempty" yaml:"database_type,omitempty" xml:"database_type,omitempty"`
	DatabaseVersion string       `json:"database_version,omitempty" yaml:"database_version,omitempty" xml:"database_version,omitempty"`
	SourceFormat    string       `json:"source_format,omitempty" yaml:"source_format,omitempty" xml:"source_format,omitempty"` // Source Format of the database.
}

// SQLNamer returns the database name in lowercase
func (d *Database) SQLName() string {
	return strings.ToLower(d.Name)
}

type Schema struct {
	Name        string            `json:"name" yaml:"name" xml:"name"`
	Description string            `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Tables      []*Table          `json:"tables" yaml:"tables" xml:"-"`
	Views       []*View           `json:"views,omitempty" yaml:"views,omitempty" xml:"-"`
	Sequences   []*Sequence       `json:"sequences,omitempty" yaml:"sequences,omitempty" xml:"-"`
	Owner       string            `json:"owner" yaml:"owner" xml:"owner"`
	Permissions map[string]string `json:"permissions,omitempty" yaml:"permissions,omitempty" xml:"-"`
	Comment     string            `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Metadata    map[string]any    `json:"metadata,omitempty" yaml:"metadata,omitempty" xml:"-"`
	Scripts     []*Script         `json:"scripts,omitempty" yaml:"scripts,omitempty" xml:"scripts,omitempty"`
	Sequence    uint              `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
	RefDatabase *Database         `json:"-" yaml:"-" xml:"-"` // Excluded to prevent circular references
	Relations   []*Relationship   `json:"relations,omitempty" yaml:"relations,omitempty" xml:"-"`
}

// SQLName returns the schema name in lowercase
func (d *Schema) SQLName() string {
	return strings.ToLower(d.Name)
}

type Table struct {
	Name          string                   `json:"name" yaml:"name" xml:"name"`
	Description   string                   `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Schema        string                   `json:"schema" yaml:"schema" xml:"schema"`
	Columns       map[string]*Column       `json:"columns" yaml:"columns" xml:"-"`
	Constraints   map[string]*Constraint   `json:"constraints" yaml:"constraints" xml:"-"`
	Indexes       map[string]*Index        `json:"indexes,omitempty" yaml:"indexes,omitempty" xml:"-"`
	Relationships map[string]*Relationship `json:"relationships,omitempty" yaml:"relationships,omitempty" xml:"-"`
	Comment       string                   `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Tablespace    string                   `json:"tablespace,omitempty" yaml:"tablespace,omitempty" xml:"tablespace,omitempty"`
	Metadata      map[string]any           `json:"metadata,omitempty" yaml:"metadata,omitempty" xml:"-"`
	Sequence      uint                     `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
	RefSchema     *Schema                  `json:"-" yaml:"-" xml:"-"` // Excluded to prevent circular references
}

// SQLName returns the table name in lowercase
func (d *Table) SQLName() string {
	return strings.ToLower(d.Name)
}

func (m Table) GetPrimaryKey() *Column {
	for _, column := range m.Columns {
		if column.IsPrimaryKey {
			return column
		}
	}
	return nil
}

func (m Table) GetForeignKeys() []*Constraint {
	keys := make([]*Constraint, 0)

	for _, c := range m.Constraints {
		if c.Type == ForeignKeyConstraint {
			keys = append(keys, c)
		}
	}
	return keys
}

// View represents a database view (read-only)
type View struct {
	Name        string             `json:"name" yaml:"name" xml:"name"`
	Description string             `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Schema      string             `json:"schema" yaml:"schema" xml:"schema"`
	Definition  string             `json:"definition" yaml:"definition" xml:"definition"` // SQL definition
	Columns     map[string]*Column `json:"columns" yaml:"columns" xml:"-"`
	Comment     string             `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Metadata    map[string]any     `json:"metadata,omitempty" yaml:"metadata,omitempty" xml:"-"`
	Sequence    uint               `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
	RefSchema   *Schema            `json:"-" yaml:"-" xml:"-"` // Excluded to prevent circular references
}

// SQLName returns the view name in lowercase
func (d *View) SQLName() string {
	return strings.ToLower(d.Name)
}

// Sequence represents a database sequence (auto-increment generator)
type Sequence struct {
	Name          string  `json:"name" yaml:"name" xml:"name"`
	Description   string  `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Schema        string  `json:"schema" yaml:"schema" xml:"schema"`
	StartValue    int64   `json:"start_value" yaml:"start_value" xml:"start_value"`
	MinValue      int64   `json:"min_value,omitempty" yaml:"min_value,omitempty" xml:"min_value,omitempty"`
	MaxValue      int64   `json:"max_value,omitempty" yaml:"max_value,omitempty" xml:"max_value,omitempty"`
	IncrementBy   int64   `json:"increment_by" yaml:"increment_by" xml:"increment_by"`
	CacheSize     int64   `json:"cache_size,omitempty" yaml:"cache_size,omitempty" xml:"cache_size,omitempty"`
	Cycle         bool    `json:"cycle" yaml:"cycle" xml:"cycle"`
	OwnedByTable  string  `json:"owned_by_table,omitempty" yaml:"owned_by_table,omitempty" xml:"owned_by_table,omitempty"`
	OwnedByColumn string  `json:"owned_by_column,omitempty" yaml:"owned_by_column,omitempty" xml:"owned_by_column,omitempty"`
	Comment       string  `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Sequence      uint    `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
	RefSchema     *Schema `json:"-" yaml:"-" xml:"-"` // Excluded to prevent circular references
}

// SQLName returns the sequence name in lowercase
func (d *Sequence) SQLName() string {
	return strings.ToLower(d.Name)
}

// Column represents a table column
type Column struct {
	Name          string `json:"name" yaml:"name" xml:"name"`
	Description   string `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Table         string `json:"table" yaml:"table" xml:"table"`
	Schema        string `json:"schema" yaml:"schema" xml:"schema"`
	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"`
	Comment       string `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Collation     string `json:"collation,omitempty" yaml:"collation,omitempty" xml:"collation,omitempty"`
	Sequence      uint   `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
}

// SQLName returns the table name in lowercase
func (d *Column) SQLName() string {
	return strings.ToLower(d.Name)
}

type Index struct {
	Name        string   `json:"name" yaml:"name" xml:"name"`
	Description string   `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Table       string   `json:"table,omitempty" yaml:"table,omitempty" xml:"table,omitempty"`
	Schema      string   `json:"schema,omitempty" yaml:"schema,omitempty" xml:"schema,omitempty"`
	Columns     []string `json:"columns" yaml:"columns" xml:"columns"`
	Unique      bool     `json:"unique" yaml:"unique" xml:"unique"`
	Type        string   `json:"type" yaml:"type" xml:"type"`                                  // btree, hash, gin, gist, etc.
	Where       string   `json:"where,omitempty" yaml:"where,omitempty" xml:"where,omitempty"` // partial index condition
	Concurrent  bool     `json:"concurrent,omitempty" yaml:"concurrent,omitempty" xml:"concurrent,omitempty"`
	Include     []string `json:"include,omitempty" yaml:"include,omitempty" xml:"include,omitempty"` // INCLUDE columns
	Comment     string   `json:"comment,omitempty" yaml:"comment,omitempty" xml:"comment,omitempty"`
	Sequence    uint     `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
}

// SQLName returns the Indexin lowercase
func (d *Index) SQLName() string {
	return strings.ToLower(d.Name)
}

type RelationType string

const (
	OneToOne   RelationType = "one_to_one"
	OneToMany  RelationType = "one_to_many"
	ManyToMany RelationType = "many_to_many"
)

type Relationship struct {
	Name          string            `json:"name" yaml:"name" xml:"name"`
	Type          RelationType      `json:"type" yaml:"type" xml:"type"`
	FromTable     string            `json:"from_table" yaml:"from_table" xml:"from_table"`
	FromSchema    string            `json:"from_schema" yaml:"from_schema" xml:"from_schema"`
	FromColumns   []string          `json:"from_columns" yaml:"from_columns" xml:"from_columns"`
	ToTable       string            `json:"to_table" yaml:"to_table" xml:"to_table"`
	ToSchema      string            `json:"to_schema" yaml:"to_schema" xml:"to_schema"`
	ToColumns     []string          `json:"to_columns" yaml:"to_columns" xml:"to_columns"`
	ForeignKey    string            `json:"foreign_key" yaml:"foreign_key" xml:"foreign_key"`
	Properties    map[string]string `json:"properties" yaml:"properties" xml:"-"`
	ThroughTable  string            `json:"through_table,omitempty" yaml:"through_table,omitempty" xml:"through_table,omitempty"` // For many-to-many
	ThroughSchema string            `json:"through_schema,omitempty" yaml:"through_schema,omitempty" xml:"through_schema,omitempty"`
	Description   string            `json:"description,omitempty" yaml:"description,omitempty" xml:"description,omitempty"`
	Sequence      uint              `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
}

// SQLName returns the Relationship lowercase
func (d *Relationship) SQLName() string {
	return strings.ToLower(d.Name)
}

type Constraint struct {
	Name              string         `json:"name" yaml:"name" xml:"name"`
	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"`
	Schema            string         `json:"schema,omitempty" yaml:"schema,omitempty" xml:"schema,omitempty"`
	Table             string         `json:"table,omitempty" yaml:"table,omitempty" xml:"table,omitempty"`
	ReferencedTable   string         `json:"referenced_table" yaml:"referenced_table" xml:"referenced_table"`
	ReferencedSchema  string         `json:"referenced_schema" yaml:"referenced_schema" xml:"referenced_schema"`
	ReferencedColumns []string       `json:"referenced_columns" yaml:"referenced_columns" xml:"referenced_columns"`
	OnDelete          string         `json:"on_delete" yaml:"on_delete" xml:"on_delete"` // CASCADE, SET NULL, RESTRICT, etc.
	OnUpdate          string         `json:"on_update" yaml:"on_update" xml:"on_update"`
	Deferrable        bool           `json:"deferrable,omitempty" yaml:"deferrable,omitempty" xml:"deferrable,omitempty"`
	InitiallyDeferred bool           `json:"initially_deferred,omitempty" yaml:"initially_deferred,omitempty" xml:"initially_deferred,omitempty"`
	Sequence          uint           `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
}

func (d *Constraint) SQLName() string {
	return strings.ToLower(d.Name)
}

type ConstraintType string

const (
	PrimaryKeyConstraint ConstraintType = "primary_key"
	ForeignKeyConstraint ConstraintType = "foreign_key"
	UniqueConstraint     ConstraintType = "unique"
	CheckConstraint      ConstraintType = "check"
	NotNullConstraint    ConstraintType = "not_null"
)

type Script struct {
	Name        string   `json:"name" yaml:"name" xml:"name"`
	Description string   `json:"description" yaml:"description" xml:"description"`
	SQL         string   `json:"sql" yaml:"sql" xml:"sql"`
	Rollback    string   `json:"rollback,omitempty" yaml:"rollback,omitempty" xml:"rollback,omitempty"`
	RunAfter    []string `json:"run_after,omitempty" yaml:"run_after,omitempty" xml:"run_after,omitempty"`
	Schema      string   `json:"schema,omitempty" yaml:"schema,omitempty" xml:"schema,omitempty"`
	Version     string   `json:"version,omitempty" yaml:"version,omitempty" xml:"version,omitempty"`
	Priority    int      `json:"priority,omitempty" yaml:"priority,omitempty" xml:"priority,omitempty"`
	Sequence    uint     `json:"sequence,omitempty" yaml:"sequence,omitempty" xml:"sequence,omitempty"`
}

func (d *Script) SQLName() string {
	return strings.ToLower(d.Name)
}

// Initialize functions

// InitDatabase initializes a new Database with empty slices
func InitDatabase(name string) *Database {
	return &Database{
		Name:    name,
		Schemas: make([]*Schema, 0),
	}
}

// InitSchema initializes a new Schema with empty slices and maps
func InitSchema(name string) *Schema {
	return &Schema{
		Name:        name,
		Tables:      make([]*Table, 0),
		Views:       make([]*View, 0),
		Sequences:   make([]*Sequence, 0),
		Permissions: make(map[string]string),
		Metadata:    make(map[string]any),
		Scripts:     make([]*Script, 0),
	}
}

// InitTable initializes a new Table with empty maps
func InitTable(name, schema string) *Table {
	return &Table{
		Name:          name,
		Schema:        schema,
		Columns:       make(map[string]*Column),
		Constraints:   make(map[string]*Constraint),
		Indexes:       make(map[string]*Index),
		Relationships: make(map[string]*Relationship),
		Metadata:      make(map[string]any),
	}
}

// InitColumn initializes a new Column
func InitColumn(name, table, schema string) *Column {
	return &Column{
		Name:   name,
		Table:  table,
		Schema: schema,
	}
}

// InitIndex initializes a new Index with empty slices
func InitIndex(name, table, schema string) *Index {
	return &Index{
		Name:    name,
		Table:   table,
		Schema:  schema,
		Columns: make([]string, 0),
		Include: make([]string, 0),
	}
}

// InitRelation initializes a new Relationship with empty slices
func InitRelation(name, schema string) *Relationship {
	return &Relationship{
		Name:        name,
		FromSchema:  schema,
		ToSchema:    schema,
		Properties:  make(map[string]string),
		FromColumns: make([]string, 0),
		ToColumns:   make([]string, 0),
	}
}

// InitRelationship initializes a new Relationship with empty maps
func InitRelationship(name string, relType RelationType) *Relationship {
	return &Relationship{
		Name:       name,
		Type:       relType,
		Properties: make(map[string]string),
	}
}

// InitConstraint initializes a new Constraint with empty slices
func InitConstraint(name string, constraintType ConstraintType) *Constraint {
	return &Constraint{
		Name:              name,
		Type:              constraintType,
		Columns:           make([]string, 0),
		ReferencedColumns: make([]string, 0),
	}
}

// InitScript initializes a new Script with empty slices
func InitScript(name string) *Script {
	return &Script{
		Name:     name,
		RunAfter: make([]string, 0),
	}
}

// InitView initializes a new View with empty maps
func InitView(name, schema string) *View {
	return &View{
		Name:     name,
		Schema:   schema,
		Columns:  make(map[string]*Column),
		Metadata: make(map[string]any),
	}
}

// InitSequence initializes a new Sequence with default values
func InitSequence(name, schema string) *Sequence {
	return &Sequence{
		Name:        name,
		Schema:      schema,
		IncrementBy: 1,
		StartValue:  1,
	}
}