package files

import (
	"database/sql"
	"fmt"
	"os"
	"strings"
	"testing"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func int64Pointer(value int64) *int64 {
	return &value
}

func openFileUploadTiDB(t *testing.T) (*sql.DB, func()) {
	t.Helper()
	host := envOrDefault("TIDB_HOST", "127.0.0.1")
	port := envOrDefault("TIDB_PORT", "4001")
	user := envOrDefault("TIDB_USER", "root")
	password := os.Getenv("TIDB_PASSWORD")
	adminDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/?parseTime=true&loc=UTC", user, password, host, port)
	adminDB, err := sql.Open("mysql", adminDSN)
	if err != nil {
		requireOrSkipTiDB(t, "open admin TiDB", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		requireOrSkipTiDB(t, "ping TiDB", err)
	}
	dbName := fmt.Sprintf("nexuscrm_files_m1_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "`"); err != nil {
		_ = adminDB.Close()
		requireOrSkipTiDB(t, "create TiDB database", err)
	}
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true&loc=UTC", user, password, host, port, dbName)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatal(err)
	}
	if err := db.Ping(); err != nil {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatal(err)
	}
	return db, func() {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
	}
}

func requireOrSkipTiDB(t *testing.T, operation string, err error) {
	t.Helper()
	if os.Getenv("NEXUSCRM_TIDB_REQUIRED") == "1" {
		t.Fatalf("%s: %v", operation, err)
	}
	t.Skipf("%s: %v", operation, err)
}

func envOrDefault(name, fallback string) string {
	if value := strings.TrimSpace(os.Getenv(name)); value != "" {
		return value
	}
	return fallback
}

func createFileUploadOperationTable(t *testing.T, db *sql.DB) {
	t.Helper()
	if _, err := db.Exec(`CREATE TABLE _System_FileUploadOperation (
		Id VARCHAR(255) NOT NULL PRIMARY KEY,
		IdempotencyKey VARCHAR(255) NOT NULL,
		RequestHash VARCHAR(255) NOT NULL,
		State VARCHAR(64) NOT NULL DEFAULT 'PREPARED',
		OwnerToken VARCHAR(64) NOT NULL,
		ObjectKey VARCHAR(1024) NULL,
		StorageProvider VARCHAR(64) NULL,
		StorageBucket VARCHAR(255) NULL,
		StorageAuthorityDigest VARCHAR(255) NULL,
		SystemFileId VARCHAR(255) NULL,
		ParentId VARCHAR(255) NOT NULL,
		ParentObjectApiName VARCHAR(255) NOT NULL,
		ErrorCode VARCHAR(255) NULL,
		ErrorMessage TEXT NULL,
		RetryCount INT NOT NULL DEFAULT 0,
		RepairId VARCHAR(255) NULL,
		RepairReason VARCHAR(1024) NULL,
		RepairCount INT NOT NULL DEFAULT 0,
		LastRepairAt DATETIME(6) NULL,
		ExpiresAt DATETIME(6) NULL,
		CreatedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
		LastModifiedDate DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
		UNIQUE KEY uk__System_FileUploadOperation_idempotency (IdempotencyKey),
		KEY idx__System_FileUploadOperation_expires (ExpiresAt),
		KEY idx__System_FileUploadOperation_lifecycle (State, ExpiresAt, Id)
	)`); err != nil {
		t.Fatal(err)
	}
}