package database

import (
	"errors"
	"fmt"
	"reflect"
	"strings"
	"time"
)

var (
	// ErrNotFound is returned when a record doesn't exist
	ErrNotFound = errors.New("record not found")

	// ErrDuplicate is returned when a unique constraint is violated
	ErrDuplicate = errors.New("duplicate record")
)

// Collection is a generic repository for type-safe CRUD operations.
type Collection[E any] struct {
	db     *Database
	table  string
	mirror *mirror
}

// ManageOption configures a Collection during creation.
type ManageOption[E any] func(*Collection[E])

// WithIndex creates a non-unique index on the specified columns.
// Panics on failure — this is a declarative startup-time operation.
func WithIndex[E any](columns ...string) ManageOption[E] {
	return func(c *Collection[E]) {
		indexName := fmt.Sprintf("idx_%s_%s", strings.ToLower(c.table), strings.ToLower(strings.Join(columns, "_")))
		query := fmt.Sprintf(`CREATE INDEX IF NOT EXISTS "%s" ON "%s"(%s)`, indexName, c.table, strings.Join(columns, ", "))
		if _, err := c.db.Exec(query); err != nil {
			panic(fmt.Sprintf("database: create index %s: %v", indexName, err))
		}
	}
}

// WithUniqueIndex creates a unique index on the specified column(s).
// Pass multiple columns for a composite unique constraint (e.g., WithUniqueIndex("UserID", "TileID")).
// Panics on failure — this is a declarative startup-time operation.
func WithUniqueIndex[E any](columns ...string) ManageOption[E] {
	return func(c *Collection[E]) {
		indexName := fmt.Sprintf("idx_%s_%s_unique", strings.ToLower(c.table), strings.ToLower(strings.Join(columns, "_")))
		query := fmt.Sprintf(`CREATE UNIQUE INDEX IF NOT EXISTS "%s" ON "%s"(%s)`, indexName, c.table, strings.Join(columns, ", "))
		if _, err := c.db.Exec(query); err != nil {
			panic(fmt.Sprintf("database: create unique index %s: %v", indexName, err))
		}
	}
}

// Manage creates a new Collection for the given entity type.
// The table name is derived from the struct name.
// Tables are automatically created if they don't exist, and missing columns are added.
func Manage[E any](db *Database, model *E, opts ...ManageOption[E]) *Collection[E] {
	m := reflectType[E]()

	c := &Collection[E]{
		db:     db,
		table:  m.Name(),
		mirror: m,
	}

	// Auto-create table and migrate schema.
	// Panics on failure — schema is declarative and runs at startup.
	if !c.db.TableExists(c.table) {
		if err := c.db.CreateTable(c.table, c.mirror.Columns()); err != nil {
			panic(fmt.Sprintf("database: create table %s: %v", c.table, err))
		}
	} else {
		// Table exists, check for missing columns
		existing := make(map[string]bool)
		for _, name := range c.db.GetColumns(c.table) {
			existing[name] = true
		}

		for _, col := range c.mirror.Columns() {
			if !existing[col.Name] {
				if err := c.db.AddColumn(c.table, col); err != nil {
					panic(fmt.Sprintf("database: add column %s.%s: %v", c.table, col.Name, err))
				}
			}
		}
	}

	// Apply options (including index creation)
	for _, opt := range opts {
		opt(c)
	}

	return c
}

// Get retrieves an entity by ID
func (c *Collection[E]) Get(id string) (*E, error) {
	return c.First("WHERE ID = ?", id)
}

// First returns the first entity matching the query
func (c *Collection[E]) First(where string, args ...any) (*E, error) {
	results, err := c.Search(where+" LIMIT 1", args...)
	if err != nil {
		return nil, err
	}
	if len(results) == 0 {
		return nil, ErrNotFound
	}
	return results[0], nil
}

// Search returns all entities matching the query
func (c *Collection[E]) Search(where string, args ...any) ([]*E, error) {
	cols := c.mirror.Columns()
	names := make([]string, len(cols))
	for i, col := range cols {
		names[i] = col.Name
	}

	query := fmt.Sprintf(`SELECT %s FROM "%s" %s`, strings.Join(names, ", "), c.table, where)

	rows, err := c.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	results := make([]*E, 0)
	for rows.Next() {
		entity := new(E)
		if err := c.mirror.Scan(rows, entity); err != nil {
			return nil, err
		}
		results = append(results, entity)
	}

	return results, rows.Err()
}

// All returns all entities in the collection
func (c *Collection[E]) All() ([]*E, error) {
	return c.Search("")
}

// Insert creates a new record and returns the generated ID
func (c *Collection[E]) Insert(entity *E) (string, error) {
	v := reflect.ValueOf(entity).Elem()

	// Set ID if empty
	idField := c.mirror.Field(v, "ID")
	if idField.IsValid() && idField.String() == "" {
		idField.SetString(c.db.GenerateID())
	}

	// Set timestamps
	now := time.Now()
	if createdAt := c.mirror.Field(v, "CreatedAt"); createdAt.IsValid() && createdAt.Type() == reflect.TypeOf(time.Time{}) && createdAt.Interface().(time.Time).IsZero() {
		createdAt.Set(reflect.ValueOf(now))
	}
	if updatedAt := c.mirror.Field(v, "UpdatedAt"); updatedAt.IsValid() {
		updatedAt.Set(reflect.ValueOf(now))
	}

	// Build INSERT query
	cols := c.mirror.Columns()
	names := make([]string, len(cols))
	placeholders := make([]string, len(cols))
	for i, col := range cols {
		names[i] = col.Name
		placeholders[i] = "?"
	}
	values := c.mirror.Values(v)

	query := fmt.Sprintf(
		`INSERT INTO "%s" (%s) VALUES (%s)`,
		c.table,
		strings.Join(names, ", "),
		strings.Join(placeholders, ", "),
	)

	_, err := c.db.Exec(query, values...)
	if err != nil {
		if strings.Contains(err.Error(), "UNIQUE constraint") {
			return "", ErrDuplicate
		}
		return "", err
	}

	if idField.IsValid() {
		return idField.String(), nil
	}
	return "", nil
}

// Update saves changes to an existing record
func (c *Collection[E]) Update(entity *E) error {
	v := reflect.ValueOf(entity).Elem()

	// Update timestamp
	if updatedAt := c.mirror.Field(v, "UpdatedAt"); updatedAt.IsValid() {
		updatedAt.Set(reflect.ValueOf(time.Now()))
	}

	// Build UPDATE query (skip ID and CreatedAt)
	var sets []string
	var values []any
	for _, col := range c.mirror.Columns() {
		if col.Name == "ID" || col.Name == "CreatedAt" {
			continue
		}
		f := c.mirror.Field(v, col.Name)
		if !f.IsValid() {
			continue
		}
		sets = append(sets, col.Name+" = ?")
		values = append(values, f.Interface())
	}

	// Add ID for WHERE clause
	idField := c.mirror.Field(v, "ID")
	if !idField.IsValid() {
		return fmt.Errorf("entity %T has no ID field", entity)
	}
	values = append(values, idField.Interface())

	query := fmt.Sprintf(`UPDATE "%s" SET %s WHERE ID = ?`, c.table, strings.Join(sets, ", "))

	result, err := c.db.Exec(query, values...)
	if err != nil {
		return err
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrNotFound
	}

	return nil
}

// Delete removes a record
func (c *Collection[E]) Delete(entity *E) error {
	v := reflect.ValueOf(entity).Elem()
	idField := c.mirror.Field(v, "ID")
	if !idField.IsValid() {
		return fmt.Errorf("entity %T has no ID field", entity)
	}
	id := idField.Interface()

	result, err := c.db.Exec(fmt.Sprintf(`DELETE FROM "%s" WHERE ID = ?`, c.table), id)
	if err != nil {
		return err
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrNotFound
	}

	return nil
}

// DeleteByID removes a record by ID without requiring a full entity.
// Returns ErrNotFound if no record with the given ID exists.
func (c *Collection[E]) DeleteByID(id string) error {
	result, err := c.db.Exec(fmt.Sprintf(`DELETE FROM "%s" WHERE ID = ?`, c.table), id)
	if err != nil {
		return err
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return ErrNotFound
	}

	return nil
}

// Count returns the number of records matching the query.
func (c *Collection[E]) Count(where string, args ...any) (int, error) {
	query := fmt.Sprintf(`SELECT COUNT(*) FROM "%s" %s`, c.table, where)

	var count int
	if err := c.db.QueryRow(query, args...).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

// DB returns the underlying database connection
func (c *Collection[E]) DB() *Database {
	return c.db
}

// Table returns the table name
func (c *Collection[E]) Table() string {
	return c.table
}