//go:build integration

package persistence

import (
	"encoding/json"
	"fmt"
	"testing"

	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

// TestJSONUpdateBehavior isolates the behavior of RecordRepository.Update with JSON fields
func TestJSONUpdateBehavior(t *testing.T) {
	// 1. Setup DB
	db, err := database.GetInstance()
	require.NoError(t, err)
	defer db.Close()

	// repo := NewRecordRepository(db.DB())
	tableName := "_System_EmailTemplate"
	id := "json_repro_1"

	// DROP TABLE TO FORCE RE-BOOTSTRAP WITH NEW SCHEMA
	_, err = db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s", tableName))
	require.NoError(t, err, "Drop table failed")
	t.Log("Table Dropped Successfully")

	createTableQuery := `CREATE TABLE ` + tableName + ` (
		Id VARCHAR(255) PRIMARY KEY,
		name VARCHAR(255) NOT NULL,
		subject VARCHAR(255),
		html_body TEXT,
		text_body TEXT,
		CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
		LastModifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP
	);`
	_, err = db.Exec(createTableQuery)
	require.NoError(t, err, "Create table failed")

	// Cleanup
	db.Exec(fmt.Sprintf("DELETE FROM %s WHERE Id = ?", tableName), id)

	// 2. Insert Initial Record (Raw to ensure valid state)
	initialMsg := `[{"role":"user","content":"Init"}]`
	_, err = db.Exec(fmt.Sprintf("INSERT INTO %s (Id, name, subject, html_body) VALUES (?, ?, ?, ?)", tableName),
		id, "test_template", "Test Subject", initialMsg)
	require.NoError(t, err, "Insert failed")

	// 3. Test Manual Update WITH BACKTICKS on JSON column
	// This tests if TiDB driver handles `messages` = ? correctly for JSON type

	newMsgArr := []map[string]string{
		{"role": "user", "content": "UpdatedBacktick"},
	}
	newBytes, _ := json.Marshal(newMsgArr)
	newStr := string(newBytes)

	q := fmt.Sprintf("UPDATE %s SET `html_body` = ? WHERE Id = ?", tableName)
	_, err = db.Exec(q, newStr, id)
	require.NoError(t, err, "Manual Update with Backticks failed")

	// 4. Verify Persistence
	var fetchedMsg string
	row := db.QueryRow(fmt.Sprintf("SELECT html_body FROM %s WHERE Id = ?", tableName), id)
	err = row.Scan(&fetchedMsg)
	require.NoError(t, err, "Scan failed")

	assert.JSONEq(t, newStr, fetchedMsg, "Fetched JSON validation failed - likely NULL or Empty")

	// Cleanup
	db.Exec(fmt.Sprintf("DELETE FROM %s WHERE Id = ?", tableName), id)
}