package excel

import (
	"fmt"
	"strconv"
	"sync"

	"github.com/xuri/excelize/v2"
)

type Table struct {
	Name    string
	Columns []string
	Rows    []map[string]any
}

type Engine struct {
	mu       sync.Mutex
	filePath string
	Tables   map[string]*Table
}

func (e *Engine) Lock()   { e.mu.Lock() }
func (e *Engine) Unlock() { e.mu.Unlock() }

func Open(filePath string) (*Engine, error) {
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return nil, fmt.Errorf("open xlsx: %w", err)
	}
	defer f.Close()

	e := &Engine{
		filePath: filePath,
		Tables:   make(map[string]*Table),
	}

	sheets := f.GetSheetList()
	for _, sheet := range sheets {
		rows, err := f.GetRows(sheet)
		if err != nil {
			return nil, fmt.Errorf("read sheet %q: %w", sheet, err)
		}
		if len(rows) < 2 {
			continue
		}

		t := &Table{
			Name:    sheet,
			Columns: rows[0],
			Rows:    make([]map[string]any, 0, len(rows)-1),
		}

		for _, row := range rows[1:] {
			rec := make(map[string]any, len(t.Columns))
			for i, col := range t.Columns {
				if i < len(row) {
					rec[col] = inferValue(row[i])
				} else {
					rec[col] = nil
				}
			}
			t.Rows = append(t.Rows, rec)
		}

		e.Tables[sheet] = t
	}

	if len(e.Tables) == 0 {
		return nil, fmt.Errorf("no tables found in %s", filePath)
	}

	return e, nil
}

func (e *Engine) ListTables() []string {
	names := make([]string, 0, len(e.Tables))
	for n := range e.Tables {
		names = append(names, n)
	}
	return names
}

func (e *Engine) GetTable(name string) *Table {
	return e.Tables[name]
}

func (e *Engine) IDColumn(table string) string {
	t, ok := e.Tables[table]
	if !ok || len(t.Columns) == 0 {
		return ""
	}
	return t.Columns[0]
}

func (e *Engine) CreateSheet(name string) error {
	if _, exists := e.Tables[name]; exists {
		return fmt.Errorf("sheet %q already exists", name)
	}
	e.Tables[name] = &Table{
		Name:    name,
		Columns: []string{"ID"},
		Rows:    []map[string]any{},
	}
	return nil
}

func (e *Engine) DropSheet(name string) error {
	if _, exists := e.Tables[name]; !exists {
		return fmt.Errorf("sheet %q not found", name)
	}
	delete(e.Tables, name)
	return nil
}

func (e *Engine) CreateTable(sheetName string, columns []string, rows []map[string]any) error {
	t, exists := e.Tables[sheetName]
	if !exists {
		return fmt.Errorf("sheet %q not found", sheetName)
	}
	t.Columns = columns
	t.Rows = rows
	return nil
}

func (e *Engine) DropTable(name string) error {
	t, exists := e.Tables[name]
	if !exists {
		return fmt.Errorf("table %q not found", name)
	}
	t.Columns = nil
	t.Rows = nil
	return nil
}

func (e *Engine) Flush() error {
	f, err := excelize.OpenFile(e.filePath)
	if err != nil {
		return fmt.Errorf("open for flush: %w", err)
	}
	defer f.Close()

	// Merge: import any sheets from the file that our in-memory state doesn't know about.
	// This preserves external changes (e.g. edits made in Excel) across Flush calls.
	for _, sheet := range f.GetSheetList() {
		if _, exists := e.Tables[sheet]; exists {
			continue
		}
		rows, err := f.GetRows(sheet)
		if err != nil || len(rows) < 2 {
			continue
		}
		t := &Table{
			Name:    sheet,
			Columns: rows[0],
			Rows:    make([]map[string]any, 0, len(rows)-1),
		}
		for _, row := range rows[1:] {
			rec := make(map[string]any, len(t.Columns))
			for i, col := range t.Columns {
				if i < len(row) {
					rec[col] = inferValue(row[i])
				} else {
					rec[col] = nil
				}
			}
			t.Rows = append(t.Rows, rec)
		}
		e.Tables[sheet] = t
	}

	existing := f.GetSheetList()
	keep := make(map[string]bool)
	for name := range e.Tables {
		keep[name] = true
	}

	for _, s := range existing {
		if !keep[s] {
			f.DeleteSheet(s)
		}
	}

	for _, t := range e.Tables {
		if !sheetExists(f, t.Name) {
			f.NewSheet(t.Name)
		}
		rows, err := f.GetRows(t.Name)
		if err != nil {
			rows = nil
		}
		for i := range rows {
			for j := range rows[i] {
				cell, _ := excelize.CoordinatesToCellName(j+1, i+1)
				_ = f.SetCellValue(t.Name, cell, "")
			}
		}

		for j, col := range t.Columns {
			cell, _ := excelize.CoordinatesToCellName(j+1, 1)
			_ = f.SetCellStr(t.Name, cell, col)
		}

		for i, row := range t.Rows {
			for j, col := range t.Columns {
				val, ok := row[col]
				if !ok || val == nil {
					continue
				}
				cell, _ := excelize.CoordinatesToCellName(j+1, i+2)
				switch v := val.(type) {
				case string:
					_ = f.SetCellStr(t.Name, cell, v)
				case float64:
					_ = f.SetCellFloat(t.Name, cell, v, 2, 64)
				case int:
					_ = f.SetCellInt(t.Name, cell, int64(v))
				default:
					_ = f.SetCellStr(t.Name, cell, fmt.Sprintf("%v", v))
				}
			}
		}
	}

	return f.Save()
}

func sheetExists(f *excelize.File, name string) bool {
	for _, s := range f.GetSheetList() {
		if s == name {
			return true
		}
	}
	return false
}

func inferValue(s string) any {
	if i, err := strconv.Atoi(s); err == nil {
		return i
	}
	if f, err := strconv.ParseFloat(s, 64); err == nil {
		return f
	}
	return s
}