package nstore

import (
	"database/sql"
	"fmt"
	"iter"
	"log"
	"reflect"
)

const (
	maxRecursionDepth = 3
)

func (d *Database) rowToValue(rows *sql.Rows, rType reflect.Type) (int64, reflect.Value, error) {
	return d.rowToValueDepth(rows, rType, 0)
}

func (d *Database) rowToValueDepth(rows *sql.Rows, rType reflect.Type, depth int) (int64, reflect.Value, error) {
	ID := int64(0)
	rValue := reflect.New(rType).Elem()

	scanValues := make([]any, 0)
	scanValues = append(scanValues, &ID)

	for rField, rFieldValue := range structFieldValues(rValue) {
		if fieldTypeToDatabaseType(rField.Type) == "" {
			continue
		}
		scanValues = append(scanValues, rFieldValue.Addr().Interface())
	}

	log.Println(scanValues)

	if err := rows.Scan(scanValues...); err != nil {
		return 0, reflect.Value{}, err
	}

	d.recordIDs[rValue.Addr().Interface()] = ID

	// Populate relation fields recursively
	if err := d.populateRelations(ID, rValue, depth); err != nil {
		return 0, reflect.Value{}, err
	}

	return ID, rValue, nil
}

func (d *Database) populateRelations(srcID int64, rValue reflect.Value, depth int) error {
	if depth >= maxRecursionDepth {
		d.logDebug(fmt.Sprintf("populateRelations: depth=%d >= maxRecursionDepth, skipping", depth))
		return nil
	}

	rType := rValue.Type()

	d.debugValues("populateRelations", map[string]any{"depth": depth, "srcID": srcID})

	for rField, rFieldValue := range rValue.Fields() {
		if !rField.IsExported() {
			continue
		}

		checkType := rField.Type
		if checkType.Kind() == reflect.Pointer {
			checkType = rField.Type.Elem()
		}

		switch checkType.Kind() {
		case reflect.Struct:
			if err := d.populateSingleStructRelation(srcID, rType, rField, rFieldValue, depth); err != nil {
				return err
			}
		case reflect.Slice:
			elemKind := checkType.Elem().Kind()
			if elemKind == reflect.Struct || elemKind == reflect.Pointer && checkType.Elem().Elem().Kind() == reflect.Struct {
				// Slice of structs
				if err := d.populateSliceStructRelation(srcID, rType, rField, rFieldValue, depth); err != nil {
					return err
				}
			} else {
				// Slice of primitive values
				if err := d.populatePrimitiveSliceRelation(srcID, rType, rField, rFieldValue); err != nil {
					return err
				}
			}
		}
	}
	return nil
}

func (d *Database) populateSingleStructRelation(srcID int64, rType reflect.Type, field reflect.StructField, fieldValue reflect.Value, depth int) error {
	seq, err := d.fetchFieldRelationStructs(rType, srcID, field, depth)
	if err != nil {
		return err
	}

	for _, val := range seq {
		// val is reflect.Value of struct (non-pointer)
		// fieldValue may be struct or pointer to struct
		if field.Type.Kind() == reflect.Pointer {
			// Create a new pointer to struct
			ptr := reflect.New(field.Type.Elem())
			ptr.Elem().Set(val)
			fieldValue.Set(ptr)
		} else {
			fieldValue.Set(val)
		}
		break
	}

	return nil
}

func (d *Database) populateSliceStructRelation(srcID int64, rType reflect.Type, field reflect.StructField, fieldValue reflect.Value, depth int) error {
	seq, err := d.fetchFieldRelationStructs(rType, srcID, field, depth)
	if err != nil {
		return err
	}
	var slice reflect.Value
	if field.Type.Kind() == reflect.Slice {
		slice = reflect.MakeSlice(field.Type, 0, 0)
	} else {
		// Array - fixed length, we need to know length ahead of time.
		// Since we don't know, we'll just fill up to capacity.
		// For simplicity, treat as slice and later convert? Not possible.
		// We'll skip arrays for now.
		return nil
	}
	elemType := field.Type.Elem()
	isPointer := elemType.Kind() == reflect.Pointer
	for _, val := range seq {
		// val is reflect.Value of struct (non-pointer)
		var elem reflect.Value
		if isPointer {
			ptr := reflect.New(elemType.Elem())
			ptr.Elem().Set(val)
			elem = ptr
		} else {
			elem = val
		}
		slice = reflect.Append(slice, elem)
	}
	fieldValue.Set(slice)
	return nil
}

func (d *Database) populatePrimitiveSliceRelation(srcID int64, rType reflect.Type, field reflect.StructField, fieldValue reflect.Value) error {
	seq, err := d.fetchFieldRelationValues(rType, srcID, field)
	if err != nil {
		return err
	}
	var slice reflect.Value
	if field.Type.Kind() == reflect.Slice {
		slice = reflect.MakeSlice(field.Type, 0, 0)
	} else {
		// Array - skip for now
		return nil
	}
	elemType := field.Type.Elem()
	for val := range seq {
		// Convert val to elemType using reflection
		converted := reflect.ValueOf(val).Convert(elemType)
		slice = reflect.Append(slice, converted)
	}
	fieldValue.Set(slice)
	return nil
}

func (d *Database) fetchFieldRelationValues(rType reflect.Type, srcID int64, rField reflect.StructField) (iter.Seq[any], error) {
	rFieldType := rField.Type
	if rFieldType.Kind() == reflect.Pointer {
		rFieldType = rFieldType.Elem()
	}
	if rFieldType.Kind() != reflect.Array && rFieldType.Kind() != reflect.Slice {
		return nil, errInvalidRelationType
	}

	tableName := structFieldTableName(rType, rField)

	query := fmt.Sprintf(`SELECT value FROM "%s" WHERE src_id = ?`, tableName)
	d.debugQuery(query, srcID)

	var err error
	return func(yield func(any) bool) {
		var stmt *sql.Stmt
		stmt, err = d.conn.Prepare(query)
		if err != nil {
			return
		}
		defer stmt.Close()

		var rows *sql.Rows
		rows, err = stmt.Query(srcID)
		if err != nil {
			return
		}
		defer rows.Close()

		for rows.Next() {
			var value any
			err = rows.Scan(&value)
			if err != nil {
				return
			}
			if !yield(value) {
				return
			}
		}
	}, err
}

func (d *Database) fetchFieldRelationStructs(rType reflect.Type, srcID int64, rField reflect.StructField, depth int) (iter.Seq2[int64, reflect.Value], error) {
	rFieldType := rField.Type
	isSlice := false

	d.debugValues("fetchFieldRelationStructs", map[string]any{"depth": depth, "srcID": srcID, "field": rField.Name})

	if rFieldType.Kind() == reflect.Slice || rFieldType.Kind() == reflect.Array {
		isSlice = true
		for rFieldType.Kind() == reflect.Array || rFieldType.Kind() == reflect.Slice || rFieldType.Kind() == reflect.Pointer {
			rFieldType = rFieldType.Elem()
		}
	} else if rFieldType.Kind() == reflect.Pointer {
		for rFieldType.Kind() == reflect.Pointer {
			rFieldType = rFieldType.Elem()
		}
	}
	if rFieldType.Kind() != reflect.Struct {
		return nil, errInvalidStruct
	}

	relTableName := structFieldTableName(rType, rField)
	dstTableName := structTypeTableName(rFieldType)
	d.debugValues("fetchFieldRelationStructs", map[string]any{"relTableName": relTableName, "dstTableName": dstTableName})

	query := fmt.Sprintf(`SELECT dst.* FROM "%s" AS rel INNER JOIN "%s" AS dst ON rel.value = dst._id WHERE rel.src_id = ?`, relTableName, dstTableName)
	d.debugQuery(query, srcID)

	var err error
	return func(yield func(int64, reflect.Value) bool) {
		var stmt *sql.Stmt
		stmt, err = d.conn.Prepare(query)
		if err != nil {
			d.logDebugf("fetchFieldRelationStructs: prepare error: %v", err)
			return
		}
		defer stmt.Close()

		var rows *sql.Rows
		rows, err = stmt.Query(srcID)
		if err != nil {
			d.logDebugf("fetchFieldRelationStructs: query error: %v", err)
			return
		}
		defer rows.Close()

		rowCount := 0
		for rows.Next() {
			rowCount++
			d.logDebugf("fetchFieldRelationStructs: row found for src_id=%d", srcID)
			dstID, rValue, err := d.rowToValueDepth(rows, rFieldType, depth+1)
			if err != nil {
				d.logDebugf("fetchFieldRelationStructs: rowToValue error: %v", err)
				return
			}
			if !yield(dstID, rValue) {
				return
			}
			if !isSlice {
				// For single struct relation, only yield first result
				break
			}
		}
		if rowCount == 0 {
			d.logDebugf("fetchFieldRelationStructs: no rows for src_id=%d", srcID)
		}
	}, err
}