package database

import (
	"context"
	"fmt"
	"os"
	"path/filepath"
	"testing"
	"time"

	"github.com/glebarez/sqlite"
	"github.com/stretchr/testify/assert"
	"github.com/tidbcloud/observability/o11ymetering/pkg/common/constants"
	"github.com/tidbcloud/observability/o11ymetering/pkg/common/models"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// setupTestDB creates an in-memory SQLite database for testing.
func setupTestDB(t *testing.T) *Database {
	db, err := gorm.Open(sqlite.Open("file::memory:"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	})
	assert.NoError(t, err)

	// Migrate models
	err = db.AutoMigrate(
		&models.StatisticRecord{},
		&models.StatisticTask{},
		&models.MonthlyStatisticReport{},
		&models.MonthlyProportionSum{},
		&models.O11yCost{},
	)
	assert.NoError(t, err)
	return &Database{base: db}
}

// createTempSQLiteFile creates a temporary SQLite file for CopyRecords tests.
func createTempSQLiteFile(t *testing.T, records []*models.StatisticRecord) string {
	tmpDir := t.TempDir()
	filePath := filepath.Join(tmpDir, "source.db")

	db, err := gorm.Open(sqlite.Open(filePath+"?_busy_timeout=5000&_txlock=immediate"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	})
	assert.NoError(t, err)

	err = db.AutoMigrate(&models.StatisticRecord{})
	assert.NoError(t, err)

	if len(records) > 0 {
		err = db.CreateInBatches(records, len(records)).Error
		assert.NoError(t, err)
	}

	sqlDB, _ := db.DB()
	sqlDB.Close() // Close the connection to allow other processes to open it

	return filePath
}

func TestGormRW_GetTask(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare a task
	task := &models.StatisticTask{
		StatisticClass: constants.MetricsCollection,
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	task.BuildID()
	db.base.Create(task)

	// Test: Task found
	foundTask, err := gormRW.GetTask(ctx, task.ID)
	assert.NoError(t, err)
	assert.NotNil(t, foundTask)
	assert.Equal(t, task.ID, foundTask.ID)
	assert.Equal(t, task.Region, foundTask.Region)

	// Test: Task not found
	notFoundTask, err := gormRW.GetTask(ctx, "non-existent-id")
	assert.Error(t, err) // GORM returns gorm.ErrRecordNotFound
	assert.Nil(t, notFoundTask)
}

func TestGormRW_SaveStatisticRecordsForTask(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	taskID := "test-task-123"
	// Create a task with pending state
	task := &models.StatisticTask{
		ID:             taskID,
		StatisticClass: constants.MetricsCollection,
		State:          constants.StatePending,
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	db.base.Create(task)

	// Prepare records
	record1 := &models.StatisticRecord{
		Period:        "2024-01-01 00:00:00",
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		StatisticName: constants.MetricsTimeseriesCount,
		Category:      constants.Metrics,
		Value:         10.0,
	}
	record1.BuildID() // Ensure ID is built
	record2 := &models.StatisticRecord{
		Period:        "2024-01-01 00:05:00",
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		StatisticName: constants.MetricsTimeseriesCount,
		Category:      constants.Metrics,
		Value:         20.0,
	}
	record2.BuildID() // Ensure ID is built
	records := []*models.StatisticRecord{record1, record2}

	// Test: Save records and update task state
	savedRecords, err := gormRW.SaveStatisticRecordsForTask(ctx, taskID, records)
	assert.NoError(t, err)
	assert.Len(t, savedRecords, 2)

	// Verify records are saved
	var count int64
	db.base.Model(&models.StatisticRecord{}).Count(&count)
	assert.Equal(t, int64(2), count)

	// Verify task state is updated
	var updatedTask models.StatisticTask
	db.base.First(&updatedTask, "id = ?", taskID)
	assert.Equal(t, constants.StateCalculated, updatedTask.State)

	// Test: Saving records with existing IDs (DoNothing on conflict)
	_, err = gormRW.SaveStatisticRecordsForTask(ctx, taskID, records) // Should not error due to DoNothing
	assert.NoError(t, err)
	db.base.Model(&models.StatisticRecord{}).Count(&count)
	assert.Equal(t, int64(2), count, "Records count should remain 2 due to DoNothing on conflict")

	// Test: Empty records slice
	taskID2 := "test-task-456"
	task2 := &models.StatisticTask{
		ID:             taskID2,
		StatisticClass: constants.MetricsCollection,
		State:          constants.StatePending,
		Region:         "us-east-1",
		Period:         "2024-01-02 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	db.base.Create(task2)
	_, err = gormRW.SaveStatisticRecordsForTask(ctx, taskID2, []*models.StatisticRecord{})
	assert.NoError(t, err)
	var updatedTask2 models.StatisticTask
	db.base.First(&updatedTask2, "id = ?", taskID2)
	assert.Equal(t, constants.StateCalculated, updatedTask2.State, "Task state should still update even with empty records")
}

func TestGormRW_CopyRecords(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare source records
	sourceRecords := []*models.StatisticRecord{
		{
			Period:        "2024-01-01 00:00:00",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.O11Y,
			StatisticName: constants.MetricsTimeseriesCount,
			Category:      constants.Metrics,
			Value:         10.0,
		},
		{
			Period:        "2024-01-01 00:05:00",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.O11Y,
			StatisticName: constants.MetricsTimeseriesCount,
			Category:      constants.Metrics,
			Value:         20.0,
		},
		{
			Period:        "2024-01-01 00:10:00",
			Region:        "eu-west-1", // Different region
			Env:           constants.Prod,
			MeteringGroup: constants.O11Y,
			StatisticName: constants.MetricsTimeseriesCount,
			Category:      constants.Metrics,
			Value:         30.0,
		},
	}
	for _, rec := range sourceRecords {
		rec.BuildID()
	}

	// Create a temporary source SQLite file
	sourceFilePath := createTempSQLiteFile(t, sourceRecords)
	defer os.Remove(sourceFilePath) // Clean up the temp file

	// Test: Copy records from a specific region
	err := gormRW.CopyRecords(ctx, sourceFilePath, "us-east-1")
	assert.NoError(t, err)

	// Verify only records from "us-east-1" are copied
	var targetRecords []*models.StatisticRecord
	db.base.Find(&targetRecords)
	assert.Len(t, targetRecords, 2, "Should copy only records from the specified region")
	for _, rec := range targetRecords {
		assert.Equal(t, "us-east-1", rec.Region)
	}

	// Test: Copying from a non-existent source file
	err = gormRW.CopyRecords(ctx, "/path/to/nonexistent.db", "us-east-1")
	assert.Error(t, err, "Should return an error for non-existent source file")

	// Test: Copying to a DB with existing identical records (should not duplicate due to DoNothing)
	err = gormRW.CopyRecords(ctx, sourceFilePath, "us-east-1")
	assert.NoError(t, err)
	db.base.Find(&targetRecords)
	assert.Len(t, targetRecords, 2, "Should not duplicate records on re-copy")
}

func TestGormRW_LatestMergedRecordPeriod(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Test: No records
	period, err := gormRW.LatestMergedRecordPeriod(ctx, "us-east-1")
	assert.NoError(t, err)
	assert.Empty(t, period)

	// Prepare records
	record1 := &models.StatisticRecord{
		Period:        "2024-01-01 00:00:00",
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		StatisticName: constants.MetricsTimeseriesCount,
	}
	record1.BuildID()
	record2 := &models.StatisticRecord{
		Period:        "2024-01-01 00:05:00",
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		StatisticName: constants.MetricsTimeseriesCount,
	}
	record2.BuildID()
	record3 := &models.StatisticRecord{
		Period:        "2024-01-01 00:05:00", // Same period as record2 but different ID
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		StatisticName: constants.LogsLokiEventsCount, // Different statistic name to make ID unique
	}
	record3.BuildID()
	record4 := &models.StatisticRecord{
		Period:        "2024-01-01 00:00:00",
		Region:        "eu-west-1", // Different region
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		StatisticName: constants.MetricsTimeseriesCount,
	}
	record4.BuildID()
	db.base.CreateInBatches([]*models.StatisticRecord{record1, record2, record3, record4}, 4)

	// Test: Latest period for us-east-1
	period, err = gormRW.LatestMergedRecordPeriod(ctx, "us-east-1")
	assert.NoError(t, err)
	assert.Equal(t, "2024-01-01 00:05:00", period)

	// Test: Latest period for eu-west-1
	period, err = gormRW.LatestMergedRecordPeriod(ctx, "eu-west-1")
	assert.NoError(t, err)
	assert.Equal(t, "2024-01-01 00:00:00", period)

	// Test: Non-existent region
	period, err = gormRW.LatestMergedRecordPeriod(ctx, "cn-north-1")
	assert.NoError(t, err)
	assert.Empty(t, period)
}

func TestGormRW_DeleteRecordsByPeriod(t *testing.T) {
	gormRW := GormRW{}
	ctx := context.Background()
	// This method is marked as panic("implement me"), so we'll test that it panics.
	assert.PanicsWithValue(t, "implement me", func() {
		gormRW.DeleteRecordsByPeriod(ctx, []string{"2024-01-01"})
	})
}

func TestGormRW_BatchCreateTask(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare tasks
	task1 := &models.StatisticTask{
		StatisticClass: constants.MetricsCollection,
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	task1.BuildID()
	task2 := &models.StatisticTask{
		StatisticClass: constants.LogsCollectionLoki,
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	task2.BuildID()
	tasks := []*models.StatisticTask{task1, task2}

	// Test: Batch create new tasks
	createdTasks, err := gormRW.BatchCreateTask(ctx, tasks)
	assert.NoError(t, err)
	assert.Len(t, createdTasks, 2)

	var count int64
	db.base.Model(&models.StatisticTask{}).Count(&count)
	assert.Equal(t, int64(2), count)

	// Test: Batch create with one existing task (should not duplicate due to DoNothing)
	task3 := &models.StatisticTask{
		StatisticClass: constants.MetricsCollection, // Same as task1
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	task3.BuildID() // Will generate the same ID as task1
	task4 := &models.StatisticTask{
		StatisticClass: constants.LogsStorageS3, // New task
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
	}
	task4.BuildID()
	tasksWithConflict := []*models.StatisticTask{task3, task4}

	createdTasks, err = gormRW.BatchCreateTask(ctx, tasksWithConflict)
	assert.NoError(t, err)
	assert.Len(t, createdTasks, 2) // Length of input slice

	db.base.Model(&models.StatisticTask{}).Count(&count)
	assert.Equal(t, int64(3), count, "Should add only the new task, not duplicate existing one")

	// Test: Empty slice
	createdTasks, err = gormRW.BatchCreateTask(ctx, []*models.StatisticTask{})
	assert.NoError(t, err)
	assert.Len(t, createdTasks, 0)
}

func TestGormRW_PickEarliestPendingTasks(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare tasks with different periods and states
	tasksToCreate := []*models.StatisticTask{
		{
			StatisticClass: constants.MetricsCollection, Region: "us-east-1", Period: "2024-01-01 00:15:00",
			Env: constants.Prod, MeteringGroup: constants.O11Y, State: constants.StatePending,
		},
		{
			StatisticClass: constants.MetricsCollection, Region: "us-east-1", Period: "2024-01-01 00:05:00",
			Env: constants.Prod, MeteringGroup: constants.O11Y, State: constants.StatePending,
		},
		{
			StatisticClass: constants.MetricsCollection, Region: "us-east-1", Period: "2024-01-01 00:10:00",
			Env: constants.Prod, MeteringGroup: constants.O11Y, State: constants.StateCalculated, // Not pending
		},
		{
			StatisticClass: constants.MetricsCollection, Region: "us-west-2", Period: "2024-01-01 00:00:00",
			Env: constants.Prod, MeteringGroup: constants.O11Y, State: constants.StatePending, // Different region
		},
		{
			StatisticClass: constants.LogsCollectionLoki, Region: "us-east-1", Period: "2024-01-01 00:05:00", // Same period as another pending, different class
			Env: constants.Prod, MeteringGroup: constants.O11Y, State: constants.StatePending,
		},
	}
	for _, task := range tasksToCreate {
		task.BuildID()
		db.base.Create(task)
	}

	// Test: Pick 2 earliest pending tasks for us-east-1
	pickedTasks, err := gormRW.PickEarliestPendingTasks(ctx, "us-east-1", 2)
	assert.NoError(t, err)
	assert.Len(t, pickedTasks, 2)
	assert.Equal(t, "2024-01-01 00:05:00", pickedTasks[0].Period)
	// The order for tasks with same period depends on ID, which is hash-based, so just check period and state
	assert.Contains(t, []string{string(constants.MetricsCollection), string(constants.LogsCollectionLoki)}, string(pickedTasks[0].StatisticClass))
	assert.Equal(t, "2024-01-01 00:05:00", pickedTasks[1].Period)
	assert.Contains(t, []string{string(constants.MetricsCollection), string(constants.LogsCollectionLoki)}, string(pickedTasks[1].StatisticClass))
	assert.Equal(t, constants.StatePending, pickedTasks[0].State)
	assert.Equal(t, constants.StatePending, pickedTasks[1].State)

	// Test: Pick all pending tasks for us-east-1 (limit > available)
	pickedTasksAll, err := gormRW.PickEarliestPendingTasks(ctx, "us-east-1", 10)
	assert.NoError(t, err)
	assert.Len(t, pickedTasksAll, 3) // 3 pending tasks in us-east-1

	// Test: No pending tasks for a region
	pickedTasksNone, err := gormRW.PickEarliestPendingTasks(ctx, "cn-north-1", 1)
	assert.NoError(t, err)
	assert.Len(t, pickedTasksNone, 0)
}

func TestGormRW_UpdateTaskState(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare a task
	task := &models.StatisticTask{
		StatisticClass: constants.MetricsCollection,
		Region:         "us-east-1",
		Period:         "2024-01-01 00:00:00",
		Env:            constants.Prod,
		MeteringGroup:  constants.O11Y,
		State:          constants.StatePending,
	}
	task.BuildID()
	db.base.Create(task)

	// Test: Update state successfully
	err := gormRW.UpdateTaskState(ctx, task.ID, constants.StatePending, constants.StateProcessing)
	assert.NoError(t, err)

	var updatedTask models.StatisticTask
	db.base.First(&updatedTask, "id = ?", task.ID)
	assert.Equal(t, constants.StateProcessing, updatedTask.State)

	// Test: Update a non-existent task (GORM returns no error if record not found for Update, just affects 0 rows)
	err = gormRW.UpdateTaskState(ctx, "non-existent-task", constants.StatePending, constants.StateProcessing)
	assert.NoError(t, err) // GORM doesn't return an error for not finding the record to update.
}

func TestGormRW_GetLastedTaskPeriod(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Test: No tasks
	period, err := gormRW.GetLastedTaskPeriod(ctx, "us-east-1")
	assert.NoError(t, err)
	assert.Empty(t, period)

	// Prepare tasks
	task1 := &models.StatisticTask{
		StatisticClass: constants.MetricsCollection, Region: "us-east-1", Period: "2024-01-01 00:00:00",
		Env: constants.Prod, MeteringGroup: constants.O11Y,
	}
	task1.BuildID()
	task2 := &models.StatisticTask{
		StatisticClass: constants.LogsCollectionLoki, Region: "us-east-1", Period: "2024-01-01 00:10:00",
		Env: constants.Prod, MeteringGroup: constants.O11Y,
	}
	task2.BuildID()
	task3 := &models.StatisticTask{
		StatisticClass: constants.MetricsCollection, Region: "eu-west-1", Period: "2024-01-01 00:05:00", // Different region
		Env: constants.Prod, MeteringGroup: constants.O11Y,
	}
	task3.BuildID()
	db.base.CreateInBatches([]*models.StatisticTask{task1, task2, task3}, 3)

	// Test: Latest period for us-east-1
	period, err = gormRW.GetLastedTaskPeriod(ctx, "us-east-1")
	assert.NoError(t, err)
	assert.Equal(t, "2024-01-01 00:10:00", period)

	// Test: Latest period for eu-west-1
	period, err = gormRW.GetLastedTaskPeriod(ctx, "eu-west-1")
	assert.NoError(t, err)
	assert.Equal(t, "2024-01-01 00:05:00", period)

	// Test: Non-existent region
	period, err = gormRW.GetLastedTaskPeriod(ctx, "ap-south-1")
	assert.NoError(t, err)
	assert.Empty(t, period)
}

func TestGormRW_DeleteTasksByPeriod(t *testing.T) {
	gormRW := GormRW{}
	ctx := context.Background()
	// This method is marked as panic("implement me"), so we'll test that it panics.
	assert.PanicsWithValue(t, "implement me", func() {
		gormRW.DeleteTasksByPeriod(ctx, []string{"2024-01-01"})
	})
}

func TestGormRW_IfReportExists(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare a report
	report := &models.MonthlyStatisticReport{
		Timestamp:     1678886400, // Example: March 2023
		Region:        "us-east-1",
		Vendor:        "AWS",
		MeteringGroup: constants.O11Y,
		Env:           constants.Prod,
	}
	report.BuildID()
	db.base.Create(report)

	// Test: Report exists
	exists, err := gormRW.IfReportExists(ctx, 1678886400, "us-east-1")
	assert.NoError(t, err)
	assert.True(t, exists)

	// Test: Report does not exist (different timestamp)
	exists, err = gormRW.IfReportExists(ctx, 1678972800, "us-east-1") // Next month
	assert.NoError(t, err)
	assert.False(t, exists)

	// Test: Report does not exist (different region)
	exists, err = gormRW.IfReportExists(ctx, 1678886400, "eu-west-1")
	assert.NoError(t, err)
	assert.False(t, exists)

	// Test: No reports in DB
	db.base.Exec("DELETE FROM monthly_statistic_reports")
	exists, err = gormRW.IfReportExists(ctx, 1678886400, "us-east-1")
	assert.NoError(t, err)
	assert.False(t, exists)
}

func TestGormRW_IfCostReady(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare a cost entry (note: O11yCost is not a GORM model in the provided code,
	// but it's used with Table("o11y_cost"). We need to explicitly create the table
	// for the test or define O11yCost as a GORM model if it's meant to be one.
	// For testing, let's just use raw SQL to insert into "o11y_cost" table).
	db.base.Exec(`CREATE TABLE IF NOT EXISTS o11y_cost (
		provider TEXT,
		region TEXT,
		cloud_service TEXT,
		inner_cloud_service TEXT,
		fixed_cost INTEGER,
		timestamp INTEGER,
		cost REAL
	)`)

	// Insert a test cost entry
	costEntry := &models.O11yCost{
		Provider:     "AWS",
		Region:       "us-east-1",
		CloudService: "EBS",
		Timestamp:    1678886400,
		Cost:         100.0,
	}
	db.base.Table("o11y_cost").Create(costEntry) // Use Table() to match the original code

	// Test: Cost exists
	ready, err := gormRW.IfCostReady(ctx, 1678886400, "us-east-1")
	assert.NoError(t, err)
	assert.True(t, ready)

	// Test: Cost does not exist (different timestamp)
	ready, err = gormRW.IfCostReady(ctx, 1678972800, "us-east-1") // Next month
	assert.NoError(t, err)
	assert.False(t, ready)

	// Test: Cost does not exist (different region)
	ready, err = gormRW.IfCostReady(ctx, 1678886400, "eu-west-1")
	assert.NoError(t, err)
	assert.False(t, ready)

	// Test: No costs in DB
	db.base.Exec("DELETE FROM o11y_cost")
	ready, err = gormRW.IfCostReady(ctx, 1678886400, "us-east-1")
	assert.NoError(t, err)
	assert.False(t, ready)
}

func TestGormRW_GetMonthlyCost(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	db.base.Exec(`CREATE TABLE IF NOT EXISTS o11y_cost (
		provider TEXT,
		region TEXT,
		cloud_service TEXT,
		inner_cloud_service TEXT,
		fixed_cost INTEGER,
		timestamp INTEGER,
		cost REAL
	)`)

	// Insert test cost entries
	costEntry1 := &models.O11yCost{
		Provider:     "AWS",
		Region:       "us-east-1",
		CloudService: string(constants.EBS),
		Timestamp:    1678886400,
		Cost:         150.75,
	}
	costEntry2 := &models.O11yCost{
		Provider:     "AWS",
		Region:       "us-east-1",
		CloudService: string(constants.ElasticCompute),
		Timestamp:    1678886400,
		Cost:         200.50,
	}
	costEntry3 := &models.O11yCost{
		Provider:     "AWS",
		Region:       "eu-west-1", // Different region
		CloudService: string(constants.EBS),
		Timestamp:    1678886400,
		Cost:         50.00,
	}
	db.base.Table("o11y_cost").Create([]*models.O11yCost{costEntry1, costEntry2, costEntry3})

	// Test: Get existing cost
	cost, err := gormRW.GetMonthlyCost(ctx, "us-east-1", 1678886400, string(constants.EBS))
	assert.NoError(t, err)
	assert.InEpsilon(t, 150.75, cost, 0.001)

	// Test: Get another existing cost
	cost, err = gormRW.GetMonthlyCost(ctx, "us-east-1", 1678886400, string(constants.ElasticCompute))
	assert.NoError(t, err)
	assert.InEpsilon(t, 200.50, cost, 0.001)

	// Test: Cost not found (different cloud service)
	cost, err = gormRW.GetMonthlyCost(ctx, "us-east-1", 1678886400, string(constants.CloudWatch))
	assert.NoError(t, err) // GORM returns 0.0, no error for not found
	assert.Equal(t, 0.0, cost)

	// Test: Cost not found (different region)
	cost, err = gormRW.GetMonthlyCost(ctx, "ap-northeast-1", 1678886400, string(constants.EBS))
	assert.NoError(t, err)
	assert.Equal(t, 0.0, cost)

	// Test: Cost not found (different timestamp)
	cost, err = gormRW.GetMonthlyCost(ctx, "us-east-1", 1678972800, string(constants.EBS))
	assert.NoError(t, err)
	assert.Equal(t, 0.0, cost)
}

func TestGormRW_InsertReport(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare reports
	report1 := &models.MonthlyStatisticReport{
		Timestamp:     1678886400,
		Vendor:        "AWS",
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		Category:      constants.Metrics,
		Cost:          100.0,
	}
	report1.BuildID()
	report2 := &models.MonthlyStatisticReport{
		Timestamp:     1678886400,
		Vendor:        "AWS",
		Region:        "us-east-1",
		Env:           constants.Prod,
		MeteringGroup: constants.O11Y,
		Category:      constants.Logs,
		Cost:          50.0,
	}
	report2.BuildID()
	reports := []*models.MonthlyStatisticReport{report1, report2}

	// Test: Insert new reports
	err := gormRW.InsertReport(ctx, reports)
	assert.NoError(t, err)

	var count int64
	db.base.Model(&models.MonthlyStatisticReport{}).Count(&count)
	assert.Equal(t, int64(2), count)

	// Test: Insert reports with existing IDs (should not duplicate due to DoNothing)
	err = gormRW.InsertReport(ctx, reports)
	assert.NoError(t, err)
	db.base.Model(&models.MonthlyStatisticReport{}).Count(&count)
	assert.Equal(t, int64(2), count, "Reports count should remain 2 due to DoNothing on conflict")

	// Test: Empty slice
	err = gormRW.InsertReport(ctx, []*models.MonthlyStatisticReport{})
	assert.NoError(t, err) // Should not error for empty slice
	db.base.Model(&models.MonthlyStatisticReport{}).Count(&count)
	assert.Equal(t, int64(2), count)
}

func TestGormRW_MonthlyStatisticSum(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare statistic records
	recordsToCreate := []*models.StatisticRecord{
		{
			Period: "2024-01-01 00:00:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, Value: 10.0,
		},
		{
			Period: "2024-01-01 00:05:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, Value: 20.0,
		},
		{
			Period: "2024-01-01 00:10:00", Region: "us-east-1", StatisticName: constants.LogsLokiEventsCount, // Different statistic name
			Category: constants.Logs, Value: 5.0,
		},
		{
			Period: "2024-01-01 00:00:00", Region: "eu-west-1", StatisticName: constants.MetricsTimeseriesCount, // Different region
			Category: constants.Metrics, Value: 15.0,
		},
		{
			Period: "2023-12-31 23:55:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount, // Before start period
			Category: constants.Metrics, Value: 50.0,
		},
		{
			Period: "2024-01-01 00:15:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount, // At end period (exclusive)
			Category: constants.Metrics, Value: 60.0,
		},
	}
	for _, rec := range recordsToCreate {
		rec.BuildID()
		db.base.Create(rec)
	}

	startPeriod := "2024-01-01 00:00:00"
	endPeriod := "2024-01-01 00:15:00"

	// Test: Sum for MetricsTimeseriesCount, Metrics category, us-east-1
	sum, err := gormRW.MonthlyStatisticSum(ctx, "us-east-1", startPeriod, endPeriod, constants.MetricsTimeseriesCount, constants.Metrics)
	assert.NoError(t, err)
	assert.InEpsilon(t, 30.0, sum, 0.001) // 10.0 + 20.0

	// Test: Sum for LogsLokiEventsCount, Logs category, us-east-1
	sum, err = gormRW.MonthlyStatisticSum(ctx, "us-east-1", startPeriod, endPeriod, constants.LogsLokiEventsCount, constants.Logs)
	assert.NoError(t, err)
	assert.InEpsilon(t, 5.0, sum, 0.001)

	// Test: Sum for different region
	sum, err = gormRW.MonthlyStatisticSum(ctx, "eu-west-1", startPeriod, endPeriod, constants.MetricsTimeseriesCount, constants.Metrics)
	assert.NoError(t, err)
	assert.InEpsilon(t, 15.0, sum, 0.001)

	// Test: No matching records
	sum, err = gormRW.MonthlyStatisticSum(ctx, "us-east-1", startPeriod, endPeriod, constants.DryRunStatisticName, constants.DryRunCategory)
	assert.NoError(t, err)
	assert.Equal(t, 0.0, sum)

	// Test: Periods outside range
	sum, err = gormRW.MonthlyStatisticSum(ctx, "us-east-1", "2024-02-01 00:00:00", "2024-02-02 00:00:00", constants.MetricsTimeseriesCount, constants.Metrics)
	assert.NoError(t, err)
	assert.Equal(t, 0.0, sum)
}

func TestGormRW_MonthlyTenantStatistic(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare statistic records
	recordsToCreate := []*models.StatisticRecord{
		{
			Period: "2024-01-01 00:00:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, SubCategory: "cpu_usage", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 10.0,
		},
		{
			Period: "2024-01-01 00:05:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, SubCategory: "cpu_usage", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 20.0,
		},
		{
			Period: "2024-01-01 00:00:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, SubCategory: "memory_usage", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 5.0, // Different sub-category
		},
		{
			Period: "2024-01-01 00:00:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, SubCategory: "cpu_usage", MeteringGroup: constants.DedicatedDataplane, // Different metering group
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 15.0,
		},
		{
			Period: "2024-01-01 00:00:00", Region: "us-east-1", StatisticName: constants.LogsLokiEventsCount, // Different statistic name
			Category: constants.Logs, SubCategory: "audit_logs", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 30.0,
		},
		{
			Period: "2024-01-01 00:00:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount,
			Category: constants.Metrics, SubCategory: "cpu_usage", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-B"}, Value: 40.0, // Different tenant
		},
		{
			Period: "2024-01-01 00:00:00", Region: "eu-west-1", StatisticName: constants.MetricsTimeseriesCount, // Different region
			Category: constants.Metrics, SubCategory: "cpu_usage", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 25.0,
		},
		{
			Period: "2023-12-31 23:55:00", Region: "us-east-1", StatisticName: constants.MetricsTimeseriesCount, // Outside period
			Category: constants.Metrics, SubCategory: "cpu_usage", MeteringGroup: constants.Serverless,
			Env: constants.Prod, ClusterMeta: models.ClusterMeta{Tenant: "tenant-A"}, Value: 100.0,
		},
	}
	for _, rec := range recordsToCreate {
		rec.BuildID()
		db.base.Create(rec)
	}

	startPeriod := "2024-01-01 00:00:00"
	endPeriod := "2024-01-01 00:10:00" // Records until 00:05:00 will be included

	// Test: Monthly tenant statistic for MetricsTimeseriesCount in us-east-1
	results, err := gormRW.MonthlyTenantStatistic(ctx, "us-east-1", startPeriod, endPeriod, constants.MetricsTimeseriesCount)
	assert.NoError(t, err)
	assert.Len(t, results, 4) // tenant-A (cpu_usage, serverless), tenant-A (memory_usage, serverless), tenant-A (cpu_usage, dedicated_dataplane), tenant-B (cpu_usage, serverless)

	// Convert results to a map for easier assertion
	resultMap := make(map[string]float64) // key: category-subcategory-meteringgroup-env-tenant
	for _, r := range results {
		key := fmt.Sprintf("%s-%s-%s-%s-%s", r.Category, r.SubCategory, r.MeteringGroup, r.Env, r.Tenant)
		resultMap[key] = r.SumValue
	}

	assert.InEpsilon(t, 30.0, resultMap["metrics-cpu_usage-serverless-prod-tenant-A"], 0.001)          // 10.0 + 20.0
	assert.InEpsilon(t, 5.0, resultMap["metrics-memory_usage-serverless-prod-tenant-A"], 0.001)        // 5.0
	assert.InEpsilon(t, 15.0, resultMap["metrics-cpu_usage-dedicated_dataplane-prod-tenant-A"], 0.001) // 15.0
	assert.InEpsilon(t, 40.0, resultMap["metrics-cpu_usage-serverless-prod-tenant-B"], 0.001)          // 40.0

	// Test: Monthly tenant statistic for LogsLokiEventsCount in us-east-1
	resultsLogs, err := gormRW.MonthlyTenantStatistic(ctx, "us-east-1", startPeriod, endPeriod, constants.LogsLokiEventsCount)
	assert.NoError(t, err)
	assert.Len(t, resultsLogs, 1)
	assert.InEpsilon(t, 30.0, resultsLogs[0].SumValue, 0.001)
	assert.Equal(t, constants.Logs, resultsLogs[0].Category)
	assert.Equal(t, "audit_logs", resultsLogs[0].SubCategory)
	assert.Equal(t, constants.Serverless, resultsLogs[0].MeteringGroup)
	assert.Equal(t, "tenant-A", resultsLogs[0].Tenant)

	// Test: No matching statistic name
	resultsNone, err := gormRW.MonthlyTenantStatistic(ctx, "us-east-1", startPeriod, endPeriod, constants.DryRunStatisticName)
	assert.NoError(t, err)
	assert.Len(t, resultsNone, 0)

	// Test: No matching region
	resultsRegion, err := gormRW.MonthlyTenantStatistic(ctx, "ap-northeast-1", startPeriod, endPeriod, constants.MetricsTimeseriesCount)
	assert.NoError(t, err)
	assert.Len(t, resultsRegion, 0)
}

func TestGormRW_CalculateMonthlyProportionSum(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	targetTimestamp := time.Date(2024, time.July, 1, 0, 0, 0, 0, time.UTC).Unix()
	targetMonth := "2024-07"

	// Seed MonthlyStatisticReport data
	reportsToCreate := []*models.MonthlyStatisticReport{
		// Tenant A, AWS, us-east-1
		{
			Timestamp:     int32(targetTimestamp),
			Vendor:        "AWS",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.DedicatedDataplane,
			Tenant:        "tenant-A",
			Cost:          100.0,
		},
		{
			Timestamp:     int32(targetTimestamp),
			Vendor:        "AWS",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.DedicatedRegionalControl,
			Tenant:        "tenant-A",
			Cost:          50.0,
		},
		{
			Timestamp:     int32(targetTimestamp),
			Vendor:        "AWS",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.Serverless,
			Tenant:        "tenant-A",
			Cost:          20.0,
		},
		// Tenant B, AWS, us-east-1 (different metering group for proportion denominator)
		{
			Timestamp:     int32(targetTimestamp),
			Vendor:        "AWS",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.Serverless,
			Tenant:        "tenant-B",
			Cost:          30.0,
		},
		// Tenant C, GCP, us-central-1
		{
			Timestamp:     int32(targetTimestamp),
			Vendor:        "GCP",
			Region:        "us-central-1",
			Env:           constants.Prod,
			MeteringGroup: constants.Serverless,
			Tenant:        "tenant-C",
			Cost:          60.0,
		},
		{
			Timestamp:     int32(targetTimestamp - 3600*24*30), // Previous month
			Vendor:        "AWS",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.DedicatedDataplane,
			Tenant:        "tenant-A",
			Cost:          999.0,
		},
	}
	for _, r := range reportsToCreate {
		r.BuildID()
	}
	db.base.CreateInBatches(reportsToCreate, len(reportsToCreate))

	// Test: Calculate monthly proportion sum
	proportions, err := gormRW.CalculateMonthlyProportionSum(ctx, targetTimestamp, targetMonth)
	assert.NoError(t, err)
	assert.NotEmpty(t, proportions)

	// Verify the number of generated proportion records (should not include dedicated_regional in the final output)
	// Expecting:
	// - Tenant A, AWS, DedicatedDataplane
	// - Tenant A, AWS, Serverless
	// - Tenant B, AWS, Serverless
	// - Tenant C, GCP, Serverless
	assert.Len(t, proportions, 4)

	// Find specific proportions to verify calculations
	var propTenantA_DedicatedDataplane, propTenantA_Serverless, propTenantB_Serverless, propTenantC_Serverless *models.MonthlyProportionSum

	for _, p := range proportions {
		if p.Tenant == "tenant-A" && p.MeteringGroup == string(constants.DedicatedDataplane) && p.Provider == "AWS" {
			propTenantA_DedicatedDataplane = &p
		}
		if p.Tenant == "tenant-A" && p.MeteringGroup == string(constants.Serverless) && p.Provider == "AWS" {
			propTenantA_Serverless = &p
		}
		if p.Tenant == "tenant-B" && p.MeteringGroup == string(constants.Serverless) && p.Provider == "AWS" {
			propTenantB_Serverless = &p
		}
		if p.Tenant == "tenant-C" && p.MeteringGroup == string(constants.Serverless) && p.Provider == "GCP" {
			propTenantC_Serverless = &p
		}
	}

	assert.NotNil(t, propTenantA_DedicatedDataplane)
	assert.InEpsilon(t, 0.75, propTenantA_DedicatedDataplane.Proportion, 0.001)
	assert.Equal(t, string(constants.DedicatedDataplane), propTenantA_DedicatedDataplane.MeteringGroup)
	assert.Equal(t, "AWS", propTenantA_DedicatedDataplane.Provider)
	assert.Equal(t, "tenant-A", propTenantA_DedicatedDataplane.Tenant)

	assert.NotNil(t, propTenantA_Serverless)
	assert.InEpsilon(t, 20.0/200.0, propTenantA_Serverless.Proportion, 0.001)
	assert.Equal(t, string(constants.Serverless), propTenantA_Serverless.MeteringGroup)
	assert.Equal(t, "AWS", propTenantA_Serverless.Provider)
	assert.Equal(t, "tenant-A", propTenantA_Serverless.Tenant)

	assert.NotNil(t, propTenantB_Serverless)
	assert.InEpsilon(t, 30.0/200.0, propTenantB_Serverless.Proportion, 0.001)
	assert.Equal(t, string(constants.Serverless), propTenantB_Serverless.MeteringGroup)
	assert.Equal(t, "AWS", propTenantB_Serverless.Provider)
	assert.Equal(t, "tenant-B", propTenantB_Serverless.Tenant)

	// Calculations verification for GCP (Tenant C)
	// GCP: serverless = 60
	// Total GCP cost (for proportion denominator): 60
	// Amplification factor for GCP (no dedicated_dataplane) = 1.0
	// Check Tenant C Serverless: Cost 60. Proportion = 60 / 60 = 1.0
	assert.NotNil(t, propTenantC_Serverless)
	assert.InEpsilon(t, 1.0, propTenantC_Serverless.Proportion, 0.001)
	assert.Equal(t, string(constants.Serverless), propTenantC_Serverless.MeteringGroup)
	assert.Equal(t, "GCP", propTenantC_Serverless.Provider)
	assert.Equal(t, "tenant-C", propTenantC_Serverless.Tenant)

	// Test case: No dedicated_dataplane for a provider.
	// Add a new provider "Azure" with only Serverless cost.
	azureReport := &models.MonthlyStatisticReport{
		Timestamp:     int32(targetTimestamp),
		Vendor:        "Azure",
		Region:        "eastus",
		Env:           constants.Prod,
		MeteringGroup: constants.Serverless,
		Tenant:        "tenant-D",
		Cost:          75.0,
	}
	azureReport.BuildID()
	db.base.Create(azureReport)

	proportionsAzure, err := gormRW.CalculateMonthlyProportionSum(ctx, targetTimestamp, targetMonth)
	assert.NoError(t, err)

	var propTenantD_Serverless *models.MonthlyProportionSum
	for _, p := range proportionsAzure {
		if p.Tenant == "tenant-D" && p.MeteringGroup == string(constants.Serverless) && p.Provider == "Azure" {
			propTenantD_Serverless = &p
		}
	}
	// Azure: serverless = 75
	// Total Azure cost (for proportion denominator): 75
	// Amplification factor for Azure (no dedicated_dataplane) = 1.0
	// Check Tenant D Serverless: Cost 75. Proportion = 75 / 75 = 1.0
	assert.NotNil(t, propTenantD_Serverless)
	assert.InEpsilon(t, 1.0, propTenantD_Serverless.Proportion, 0.001)
	assert.Equal(t, "Azure", propTenantD_Serverless.Provider)
	assert.Equal(t, "tenant-D", propTenantD_Serverless.Tenant)

	// Test case: No reports for the given timestamp
	emptyProportions, err := gormRW.CalculateMonthlyProportionSum(ctx, targetTimestamp+3600*24*30, "2024-08")
	assert.NoError(t, err)
	assert.Empty(t, emptyProportions, "Should return empty slice if no reports exist for the timestamp")

	// Verify that the table `monthly_proportion_sums` is populated
	var storedProportions []models.MonthlyProportionSum
	db.base.Find(&storedProportions)
	assert.Len(t, storedProportions, 9)

	// Clean up for next test run if using persistent DB, but in-memory is fine.
	db.base.Exec("DELETE FROM monthly_statistic_reports")
	db.base.Exec("DELETE FROM monthly_proportion_sums")
}

func TestInsertRecordsToMergeDB(t *testing.T) {
	db := setupTestDB(t)
	gormRW := GormRW{DB: db}
	ctx := context.Background()

	// Prepare records
	records := []*models.StatisticRecord{
		{
			Period:        "2024-01-01 00:00:00",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.O11Y,
			StatisticName: constants.MetricsTimeseriesCount,
			Category:      constants.Metrics,
			Value:         100.0,
		},
		{
			Period:        "2024-01-01 00:05:00",
			Region:        "us-east-1",
			Env:           constants.Prod,
			MeteringGroup: constants.O11Y,
			StatisticName: constants.MetricsTimeseriesCount,
			Category:      constants.Metrics,
			Value:         200.0,
		},
	}
	for _, r := range records {
		r.BuildID()
	}

	// Test: Successful insertion
	err := InsertRecordsToMergeDB(ctx, &gormRW, records)
	assert.NoError(t, err)

	// Verify records are in the database
	var count int64
	db.base.Model(&models.StatisticRecord{}).Count(&count)
	assert.Equal(t, int64(2), count)

	// Test: Duplicate records (should be ignored due to OnConflict)
	err = InsertRecordsToMergeDB(ctx, &gormRW, records)
	assert.NoError(t, err)
	db.base.Model(&models.StatisticRecord{}).Count(&count)
	assert.Equal(t, int64(2), count, "Count should remain 2 due to DoNothing on conflict")

	// Test: Empty records slice
	err = InsertRecordsToMergeDB(ctx, &gormRW, []*models.StatisticRecord{})
	assert.NoError(t, err)

	// Test: Invalid mergeRW type
	err = InsertRecordsToMergeDB(ctx, "not-a-gorm-rw", records)
	assert.Error(t, err)
	assert.Equal(t, "merge reader/writer is not a GormRW instance", err.Error())
}