package lasebuche

import (
	"database/sql"
	"fmt"
	"reflect"
	"strings"
	"time"
)

type SqliteDialect struct{}

func NewSqliteDialect() *SqliteDialect {
	return &SqliteDialect{}
}

func convertType(f dbfield) string {
	switch f.stype {
	case "string", "*string":
		return "text"
	case "bool", "int", "*int", "time.Time", "*time.Time":
		return "numeric"
	case "[]byte":
		return "blob"
	}
	return "text"
}

func defaultValue(f dbfield) string {
	if f.dbname == "_date_created" {
		return "current_timestamp"
	}
	switch f.stype {
	case "string":
		return "''"
	case "bool", "int", "time.Time":
		return "0"
	}
	return "''"
}

func (d *SqliteDialect) TableExists(db *sql.DB, tableName string) (bool, error) {
	sql := "select name from sqlite_schema where type='table' and name=$1"
	rows, err := db.Query(sql, tableName)
	if err != nil {
		return false, err
	}
	if rows.Next() {
		rows.Close()
		return true, nil
	}
	return false, nil
}

func (d *SqliteDialect) ColumnSpec(f dbfield) (string, error) {
	if f.dbname == "id" {
		return "id text not null primary key", nil
	}
	if strings.HasPrefix(f.stype, "*") || strings.HasPrefix(f.stype, "[]") {
		return fmt.Sprintf("%s %s", f.dbname, convertType(f)), nil
	}
	return fmt.Sprintf("%s %s not null default %s", f.dbname, convertType(f), defaultValue(f)), nil
}

// ScanResult scans a single row into the provided fields slice.
// For SQLite, this handles time.Time fields which are returned as strings.
func (d *SqliteDialect) ScanResult(row interface{ Scan(dest ...any) error }, fields []any, fieldTypes []dbfield) error {
	// SQLite returns time.Time as strings, so we need special handling
	// Check if any field is a time type
	hasTimeField := false
	for _, f := range fieldTypes {
		if f.stype == "time.Time" || f.stype == "*time.Time" {
			hasTimeField = true
			break
		}
	}
	
	if !hasTimeField {
		// No time fields, use standard scan
		return row.Scan(fields...)
	}
	
	// We have time fields - need custom handling
	// Create temp destinations for all fields
	tempFields := make([]any, len(fields))
	fieldInfos := make([]struct {
		index    int
		dest     any
		isTime   bool
		isTimePtr bool
	}, len(fields))
	
	for i, f := range fieldTypes {
		isTime := f.stype == "time.Time"
		isTimePtr := f.stype == "*time.Time"
		
		fieldInfos[i] = struct {
			index    int
			dest     any
			isTime   bool
			isTimePtr bool
		}{
			index:    i,
			dest:     fields[i],
			isTime:   isTime,
			isTimePtr: isTimePtr,
		}
		
		if isTime || isTimePtr {
			var s sql.NullString
			tempFields[i] = &s
		} else {
			tempFields[i] = fields[i]
		}
	}
	
	if err := row.Scan(tempFields...); err != nil {
		return err
	}
	
	// Copy time values back to original destinations
	for _, info := range fieldInfos {
		if info.isTime || info.isTimePtr {
			s := tempFields[info.index].(*sql.NullString)
			if s.Valid {
				// Try to parse the string as time
				t, err := tryParseTime(s.String)
				if err != nil {
					return fmt.Errorf("failed to parse time field: %w", err)
				}
				
				// Set the value in the original destination
				destVal := reflect.ValueOf(info.dest).Elem()
				if info.isTime {
					// dest is *time.Time, set the value
					destVal.Set(reflect.ValueOf(t))
				} else if info.isTimePtr {
					// dest is **time.Time, allocate and set
					ptr := reflect.New(destVal.Type().Elem())
					ptr.Elem().Set(reflect.ValueOf(t))
					destVal.Set(ptr)
				}
			}
		}
	}
	
	return nil
}

// ScanRows scans multiple rows using the dialect-specific logic
func (d *SqliteDialect) ScanRows(rows *sql.Rows, fieldDest func() []any, fieldTypes []dbfield) ([]any, error) {
	// Check if any field is a time type
	hasTimeField := false
	for _, f := range fieldTypes {
		if f.stype == "time.Time" || f.stype == "*time.Time" {
			hasTimeField = true
			break
		}
	}
	
	if !hasTimeField {
		// No time fields, use standard scan
		var results []any
		for rows.Next() {
			fields := fieldDest()
			if err := rows.Scan(fields...); err != nil {
				return nil, err
			}
			results = append(results, fields...)
		}
		if err := rows.Err(); err != nil {
			return nil, err
		}
		return results, nil
	}
	
	// We have time fields - need custom handling
	var results []any
	for rows.Next() {
		fields := fieldDest()
		
		// Create temp destinations for all fields
		tempFields := make([]any, len(fields))
		fieldInfos := make([]struct {
			index    int
			dest     any
			isTime   bool
			isTimePtr bool
		}, len(fields))
		
		for i, f := range fieldTypes {
			isTime := f.stype == "time.Time"
			isTimePtr := f.stype == "*time.Time"
			
			fieldInfos[i] = struct {
				index    int
				dest     any
				isTime   bool
				isTimePtr bool
			}{
				index:    i,
				dest:     fields[i],
				isTime:   isTime,
				isTimePtr: isTimePtr,
			}
			
			if isTime || isTimePtr {
				var s sql.NullString
				tempFields[i] = &s
			} else {
				tempFields[i] = fields[i]
			}
		}
		
		if err := rows.Scan(tempFields...); err != nil {
			return nil, err
		}
		
		// Copy time values back to original destinations
		for _, info := range fieldInfos {
			if info.isTime || info.isTimePtr {
				s := tempFields[info.index].(*sql.NullString)
				if s.Valid {
					t, err := tryParseTime(s.String)
					if err != nil {
						return nil, fmt.Errorf("failed to parse time field: %w", err)
					}
					
					// Set the value in the original destination
					destVal := reflect.ValueOf(info.dest).Elem()
					if info.isTime {
						// dest is *time.Time, set the value
						destVal.Set(reflect.ValueOf(t))
					} else if info.isTimePtr {
						// dest is **time.Time, allocate and set
						ptr := reflect.New(destVal.Type().Elem())
						ptr.Elem().Set(reflect.ValueOf(t))
						destVal.Set(ptr)
					}
				}
			}
		}
		
		results = append(results, fields...)
	}
	
	if err := rows.Err(); err != nil {
		return nil, err
	}
	
	return results, nil
}

// tryParseTime attempts to parse a time string in various common formats
// SQLite driver returns time as "2006-01-02 15:04:05.999999999 -0700 MST m=+0.000000000"
// We need to strip the monotonic clock part (m=...) before parsing
func tryParseTime(s string) (time.Time, error) {
	// Remove monotonic clock part if present
	// Format: "2006-01-02 15:04:05.999999999 -0700 MST m=+0.000000000"
	if idx := strings.Index(s, " m="); idx != -1 {
		s = s[:idx]
	}
	
	// Try common formats
	formats := []string{
		time.RFC3339,
		time.RFC3339Nano,
		"2006-01-02T15:04:05Z07:00",
		"2006-01-02 15:04:05.999999999-07:00",
		"2006-01-02 15:04:05",
		"2006-01-02T15:04:05",
		"2006-01-02 15:04:05+00:00",
		// Go's default time.String() format (without monotonic clock)
		"2006-01-02 15:04:05.999999999 -0700 MST",
		// SQLite without timezone
		"2006-01-02 15:04:05.999999",
	}
	
	var err error
	var t time.Time
	for _, format := range formats {
		t, err = time.Parse(format, s)
		if err == nil {
			return t, nil
		}
	}
	
	return time.Time{}, fmt.Errorf("unable to parse time: %s", s)
}