//go:build integration

package persistence

import (
	"context"
	"fmt"
	"testing"
	"time"

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

func TestUserHierarchy_ManagerLogic(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping integration test in short mode")
	}

	// Setup DB
	schemaRepo := setupTestDB(t)
	db := schemaRepo.db
	repo := NewRecordRepository(db)
	ctx := context.Background()

	// 1. Manually create _System_User table if not fully bootstrapped in test env
	// (Note: setupTestDB usually creates strict minimal tables. We need to ensure ManagerID exists)
	// For integration test safety, we will alter the table to ensuring columns exist,
	// or create it if missing, mirroring system_tables.json

	// Create User Table with ManagerID
	// Create User Table with ManagerID
	_, err := db.ExecContext(ctx, fmt.Sprintf(`
		CREATE TABLE IF NOT EXISTS %s (
			%s VARCHAR(255) PRIMARY KEY,
			username VARCHAR(255) UNIQUE,
			email VARCHAR(255) UNIQUE,
			password VARCHAR(255),
			%s VARCHAR(255),
			%s VARCHAR(255),
			profile_id VARCHAR(255),
			is_active VARCHAR(255),
			%s VARCHAR(255),
			%s VARCHAR(255)
		) %s`,
		constants.TableUser,
		constants.ColID,
		constants.FieldUser_ManagerID,
		constants.FieldUser_Title,
		constants.FieldCreatedDate,
		constants.FieldLastModifiedDate,
		constants.DefaultTableOptions))
	require.NoError(t, err)

	// Ensure columns exist (in case table existed but columns didn't)
	// This "Add Column If Not Exists" logic is verbose in MySQL, simpler to just Try Add and ignore error or DROP/CREATE
	// For this isolated test, let's DROP and CREATE to be clean.
	_, _ = db.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=0")
	_, _ = db.ExecContext(ctx, "DROP TABLE IF EXISTS "+constants.TableUser)
	_, _ = db.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=1")
	_, err = db.ExecContext(ctx, fmt.Sprintf(`
		CREATE TABLE %s (
			%s VARCHAR(36) PRIMARY KEY,
			Username VARCHAR(255),
			Email VARCHAR(255),
			Password VARCHAR(255),
			FirstName VARCHAR(255),
			LastName VARCHAR(255),
			%s VARCHAR(255),
			%s VARCHAR(255),
			ProfileId VARCHAR(255),
			IsActive VARCHAR(255),
			IsDeleted TINYINT DEFAULT 0
		) %s`,
		constants.TableUser,
		constants.ColID,
		constants.FieldUser_ManagerID,
		constants.FieldUser_Title,
		constants.DefaultTableOptions))
	require.NoError(t, err)
	defer db.ExecContext(ctx, "DROP TABLE "+constants.TableUser)

	// Register Metadata
	userMeta := &models.ObjectMetadata{APIName: constants.TableUser}
	_ = schemaRepo.SaveObjectMetadata(ctx, userMeta, nil)

	// 2. Insert Manager User
	managerID := "005Manager" + fmt.Sprintf("%d", time.Now().UnixNano())
	manager := models.SObject{
		constants.FieldID:            managerID,
		"Username":                   "manager_" + fmt.Sprintf("%d", time.Now().UnixNano()) + "@test.com",
		"Email":                      "manager_email_" + fmt.Sprintf("%d", time.Now().UnixNano()) + "@test.com",
		"FirstName":                  "Big",
		"LastName":                   "Boss",
		constants.FieldUser_Title: "CEO",
		"ProfileId":                  "00eStandard",
		"IsActive":                   true,
	}
	err = repo.Insert(ctx, constants.TableUser, manager)
	require.NoError(t, err)

	// 3. Insert Subordinate User with ManagerID
	subordinateID := "005Subordinate" + fmt.Sprintf("%d", time.Now().UnixNano())
	subordinate := models.SObject{
		constants.FieldID:                subordinateID,
		"Username":                       "sub_" + fmt.Sprintf("%d", time.Now().UnixNano()) + "@test.com",
		"Email":                          "sub_email_" + fmt.Sprintf("%d", time.Now().UnixNano()) + "@test.com",
		"FirstName":                      "Little",
		"LastName":                       "Worker",
		constants.FieldUser_ManagerID: managerID,
		constants.FieldUser_Title:     "Associate",
		"ProfileId":                      "00eStandard",
		"IsActive":                       true,
	}
	err = repo.Insert(ctx, constants.TableUser, subordinate)
	require.NoError(t, err)

	// 4. Verify Retrieval (Direct)
	retrievedSub, err := repo.FindOne(ctx, constants.TableUser, subordinateID)
	require.NoError(t, err)
	assert.Equal(t, managerID, retrievedSub[constants.FieldUser_ManagerID])

	// 5. Verify Update (Change Manager)
	newManagerID := "005NewBoss" + fmt.Sprintf("%d", time.Now().UnixNano())
	newManager := models.SObject{
		constants.FieldID:            newManagerID,
		"Username":                   "newboss_" + fmt.Sprintf("%d", time.Now().UnixNano()) + "@test.com",
		"Email":                      "newboss_" + fmt.Sprintf("%d", time.Now().UnixNano()) + "@test.com",
		"FirstName":                  "New",
		"LastName":                   "Boss",
		constants.FieldUser_Title: "Director",
		"ProfileId":                  "00eStandard",
		"IsActive":                   true,
	}
	err = repo.Insert(ctx, constants.TableUser, newManager)
	require.NoError(t, err)

	updatePayload := models.SObject{
		constants.FieldUser_ManagerID: newManagerID,
	}
	err = repo.Update(ctx, constants.TableUser, subordinateID, updatePayload)
	require.NoError(t, err)

	retrievedSub2, err := repo.FindOne(ctx, constants.TableUser, subordinateID)
	require.NoError(t, err)
	assert.Equal(t, newManagerID, retrievedSub2[constants.FieldUser_ManagerID])
}