package persistence

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

	_ "github.com/go-sql-driver/mysql"
	"github.com/nexuscrm/db"
	"github.com/nexuscrm/shared/pkg/models"
)

func openSessionRepositoryTiDB(t *testing.T) (*sql.DB, func()) {
	t.Helper()

	host := os.Getenv("TIDB_HOST")
	if host == "" {
		host = "127.0.0.1"
	}
	port := os.Getenv("TIDB_PORT")
	if port == "" {
		port = "4001"
	}
	user := os.Getenv("TIDB_USER")
	if user == "" {
		user = "root"
	}
	password := os.Getenv("TIDB_PASSWORD")

	adminDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/?parseTime=true&loc=Local", user, password, host, port)
	adminDB, err := sql.Open("mysql", adminDSN)
	if err != nil {
		sessionRepositoryTiDBUnavailable(t, "open admin TiDB: %v", err)
	}
	if err := adminDB.Ping(); err != nil {
		_ = adminDB.Close()
		sessionRepositoryTiDBUnavailable(t, "TiDB not reachable at %s:%s: %v", host, port, err)
	}

	dbName := fmt.Sprintf("nexuscrm_session_repo_%d", time.Now().UnixNano())
	if _, err := adminDB.Exec("CREATE DATABASE `" + dbName + "`"); err != nil {
		_ = adminDB.Close()
		sessionRepositoryTiDBUnavailable(t, "create temp database: %v", 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.Fatalf("open test TiDB connection: %v", err)
	}
	if err := db.Ping(); err != nil {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
		t.Fatalf("ping test TiDB connection: %v", err)
	}

	cleanup := func() {
		_ = db.Close()
		_, _ = adminDB.Exec("DROP DATABASE IF EXISTS `" + dbName + "`")
		_ = adminDB.Close()
	}
	return db, cleanup
}

func sessionRepositoryTiDBUnavailable(t *testing.T, format string, args ...interface{}) {
	t.Helper()
	if os.Getenv("NEXUSCRM_TIDB_REQUIRED") == "1" {
		t.Fatalf("required TiDB integration environment unavailable: "+format, args...)
	}
	t.Skipf("skipping integration test: "+format, args...)
}

func createSessionRepositoryTestTables(t *testing.T, db *sql.DB) {
	t.Helper()

	stmts := []string{
		`CREATE TABLE User (
			Id VARCHAR(64) PRIMARY KEY,
			Name VARCHAR(255) NULL,
			ProfileId VARCHAR(64) NULL,
			UserRoleId VARCHAR(64) NULL
		)`,
		`CREATE TABLE _System_Session (
			Id VARCHAR(64) PRIMARY KEY,
			UserId VARCHAR(64) NOT NULL,
			ConnectedAppId VARCHAR(64) NULL,
			ControlProofHash CHAR(64) NULL,
			IsRevoked BOOL NOT NULL DEFAULT 0,
			CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			LastModifiedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
		)`,
	}

	for _, stmt := range stmts {
		if _, err := db.Exec(stmt); err != nil {
			t.Fatalf("create test table: %v", err)
		}
	}
}

func seedSessionRepositoryUser(t *testing.T, ctx context.Context, db *sql.DB, id string, name string, profileID string, roleID string) {
	t.Helper()
	if _, err := db.ExecContext(ctx, `INSERT INTO User (Id, Name, ProfileId, UserRoleId) VALUES (?, ?, ?, ?)`, id, name, nullableStringForTest(profileID), nullableStringForTest(roleID)); err != nil {
		t.Fatalf("seed User: %v", err)
	}
}

func seedSessionRepositorySession(t *testing.T, ctx context.Context, db *sql.DB, id string, userID string, appID string) {
	t.Helper()
	if _, err := db.ExecContext(ctx, `INSERT INTO _System_Session (Id, UserId, ConnectedAppId, IsRevoked) VALUES (?, ?, ?, 0)`, id, userID, nullableStringForTest(appID)); err != nil {
		t.Fatalf("seed _System_Session: %v", err)
	}
}

func nullableStringForTest(v string) interface{} {
	if v == "" {
		return nil
	}
	return v
}

func TestSessionRepositoryPreservesLegacyRowsAndStoresLongBearersOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()

	if _, err := db.Exec(`
		CREATE TABLE _System_Session (
			Id VARCHAR(255) PRIMARY KEY,
			UserId VARCHAR(255) NOT NULL,
			Token VARCHAR(750) NOT NULL,
			ExpiresAt DATETIME NOT NULL,
			IpAddress VARCHAR(255) NOT NULL,
			UserAgent VARCHAR(255) NOT NULL,
			IsRevoked BOOL NOT NULL DEFAULT 0,
			AuthEpoch DECIMAL(20,0) NOT NULL DEFAULT 1,
			ControlProofHash CHAR(64) NULL,
			LastActivity DATETIME NOT NULL,
			Scope TEXT NULL,
			ConnectedAppId VARCHAR(255) NULL,
			GrantType VARCHAR(50) NULL,
			CreatedDate DATETIME NOT NULL,
			LastModifiedDate DATETIME NOT NULL,
			UNIQUE INDEX uniq_session_token (Token)
		)
	`); err != nil {
		t.Fatalf("create legacy session table: %v", err)
	}

	now := time.Now().Truncate(time.Second)
	legacyToken := "legacy-bearer"
	if _, err := db.Exec(`
		INSERT INTO _System_Session
			(Id, UserId, Token, ExpiresAt, IpAddress, UserAgent, IsRevoked, AuthEpoch,
			 LastActivity, CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, ?, ?, 0, 1, ?, ?, ?)
	`, "legacy-session", "user-1", legacyToken, now.Add(time.Hour), "127.0.0.1", "integration", now, now, now); err != nil {
		t.Fatalf("insert legacy session: %v", err)
	}
	if _, err := db.Exec("ALTER TABLE _System_Session ADD COLUMN CurrentBearerToken VARBINARY(3000) NULL AFTER Token"); err != nil {
		t.Fatalf("add current bearer column: %v", err)
	}

	repo := NewSessionRepository(db)
	legacy, err := repo.GetSession(context.Background(), "legacy-session")
	if err != nil || legacy == nil || legacy.Token != legacyToken {
		t.Fatalf("legacy GetSession = %#v, %v; want raw bearer", legacy, err)
	}

	longToken := strings.Repeat("t", 2048)
	if err := repo.InsertSession(context.Background(), &models.SystemSession{
		ID:           "long-session",
		UserID:       "user-1",
		Token:        longToken,
		ExpiresAt:    now.Add(time.Hour),
		IPAddress:    "127.0.0.1",
		UserAgent:    "integration",
		LastActivity: now,
	}); err != nil {
		t.Fatalf("insert long session: %v", err)
	}

	var storedVerifier string
	var materialBytes int
	if err := db.QueryRow(
		"SELECT Token, OCTET_LENGTH(CurrentBearerToken) FROM _System_Session WHERE Id = ?",
		"long-session",
	).Scan(&storedVerifier, &materialBytes); err != nil {
		t.Fatalf("read long session storage: %v", err)
	}
	if storedVerifier != sessionTokenVerifier(longToken) || materialBytes != len(longToken) {
		t.Fatalf("long session storage = verifier %q bytes %d; want verifier and %d bytes",
			storedVerifier, materialBytes, len(longToken))
	}
	current, err := repo.GetSession(context.Background(), "long-session")
	if err != nil || current == nil || current.Token != longToken {
		t.Fatalf("long GetSession = %#v, %v; want raw bearer", current, err)
	}

	replacement := strings.Repeat("r", 2500)
	renewed, err := repo.RenewSession(
		context.Background(), "long-session", longToken, replacement, now.Add(2*time.Hour), now,
	)
	if err != nil || !renewed {
		t.Fatalf("RenewSession = %v, %v; want true, nil", renewed, err)
	}
	current, err = repo.GetSession(context.Background(), "long-session")
	if err != nil || current == nil || current.Token != replacement {
		t.Fatalf("renewed GetSession = %#v, %v; want replacement bearer", current, err)
	}
}

func TestSessionRepositoryPreservesLongLegacyBearerOnCanonicalVarbinarySchemaOnTiDB(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()

	if _, err := db.Exec(`
		CREATE TABLE _System_Session (
			Id VARCHAR(255) PRIMARY KEY,
			UserId VARCHAR(255) NOT NULL,
			Token VARBINARY(3000) NOT NULL,
			ExpiresAt DATETIME NOT NULL,
			IpAddress VARCHAR(255) NOT NULL,
			UserAgent VARCHAR(255) NOT NULL,
			IsRevoked BOOL NOT NULL DEFAULT 0,
			AuthEpoch DECIMAL(20,0) NOT NULL DEFAULT 1,
			ControlProofHash CHAR(64) NULL,
			LastActivity DATETIME NOT NULL,
			Scope TEXT NULL,
			ConnectedAppId VARCHAR(255) NULL,
			GrantType VARCHAR(50) NULL,
			CreatedDate DATETIME NOT NULL,
			LastModifiedDate DATETIME NOT NULL,
			UNIQUE INDEX uniq_session_token (Token)
		)
	`); err != nil {
		t.Fatalf("create canonical session table: %v", err)
	}

	now := time.Now().Truncate(time.Second)
	legacyToken := strings.Repeat("l", 2048)
	if _, err := db.Exec(`
		INSERT INTO _System_Session
			(Id, UserId, Token, ExpiresAt, IpAddress, UserAgent, IsRevoked, AuthEpoch,
			 LastActivity, CreatedDate, LastModifiedDate)
		VALUES (?, ?, ?, ?, ?, ?, 0, 1, ?, ?, ?)
	`, "long-legacy-session", "user-1", legacyToken, now.Add(time.Hour), "127.0.0.1", "integration", now, now, now); err != nil {
		t.Fatalf("insert long legacy session: %v", err)
	}
	if _, err := db.Exec("ALTER TABLE _System_Session ADD COLUMN CurrentBearerToken VARBINARY(3000) NULL AFTER Token"); err != nil {
		t.Fatalf("add current bearer column: %v", err)
	}

	repo := NewSessionRepository(db)
	current, err := repo.GetSession(context.Background(), "long-legacy-session")
	if err != nil || current == nil || current.Token != legacyToken {
		t.Fatalf("legacy GetSession = %#v, %v; want preserved long raw bearer", current, err)
	}

	var storedBytes int
	var currentBearer sql.NullString
	if err := db.QueryRow(
		"SELECT OCTET_LENGTH(Token), CurrentBearerToken FROM _System_Session WHERE Id = ?",
		"long-legacy-session",
	).Scan(&storedBytes, &currentBearer); err != nil {
		t.Fatalf("read preserved legacy storage: %v", err)
	}
	if storedBytes != len(legacyToken) || currentBearer.Valid {
		t.Fatalf("legacy storage = %d bytes, companion valid %v; want %d bytes and NULL companion",
			storedBytes, currentBearer.Valid, len(legacyToken))
	}
}

func TestSessionRepositoryRevokeAllSessionsForUser_UsesInjectedTransactionAndRollsBack(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createSessionRepositoryTestTables(t, db)

	ctx := context.Background()
	seedSessionRepositoryUser(t, ctx, db, "user-001", "before", "prof-001", "role-001")
	seedSessionRepositorySession(t, ctx, db, "sess-001", "user-001", "")

	repo := NewSessionRepository(db)
	txm := NewTransactionManager(database.NewConnectionFromDB(db))
	forcedRollback := errors.New("force rollback after revoke")

	err := txm.WithTransaction(ctx, func(txCtx context.Context) error {
		txRaw := txm.ExtractTx(txCtx)
		tx, ok := txRaw.(*sql.Tx)
		if !ok {
			return fmt.Errorf("ExtractTx type = %T; want *sql.Tx", txRaw)
		}

		if _, err := tx.ExecContext(txCtx, `UPDATE User SET Name = ? WHERE Id = ?`, "after", "user-001"); err != nil {
			return err
		}
		if err := repo.RevokeAllSessionsForUser(txCtx, "user-001"); err != nil {
			return err
		}

		var revoked bool
		if err := tx.QueryRowContext(txCtx, `SELECT IsRevoked FROM _System_Session WHERE Id = ?`, "sess-001").Scan(&revoked); err != nil {
			return err
		}
		if !revoked {
			return errors.New("session not revoked inside tx")
		}

		return forcedRollback
	})
	if !errors.Is(err, forcedRollback) {
		t.Fatalf("WithTransaction error = %v; want forced rollback", err)
	}

	var name string
	if err := db.QueryRowContext(ctx, `SELECT Name FROM User WHERE Id = ?`, "user-001").Scan(&name); err != nil {
		t.Fatalf("select rolled-back user: %v", err)
	}
	if name != "before" {
		t.Fatalf("User.Name after rollback = %q; want %q", name, "before")
	}

	var revoked bool
	if err := db.QueryRowContext(ctx, `SELECT IsRevoked FROM _System_Session WHERE Id = ?`, "sess-001").Scan(&revoked); err != nil {
		t.Fatalf("select rolled-back session: %v", err)
	}
	if revoked {
		t.Fatal("session remained revoked after rollback; want false")
	}
}

func TestSessionRepositoryRevokeAllSessionsByProfileID_UsesInjectedTransactionAndRollsBack(t *testing.T) {
	db, cleanup := openSessionRepositoryTiDB(t)
	defer cleanup()
	createSessionRepositoryTestTables(t, db)

	ctx := context.Background()
	seedSessionRepositoryUser(t, ctx, db, "user-001", "before", "prof-001", "role-001")
	seedSessionRepositorySession(t, ctx, db, "sess-001", "user-001", "")

	repo := NewSessionRepository(db)
	txm := NewTransactionManager(database.NewConnectionFromDB(db))
	forcedRollback := errors.New("force rollback after profile revoke")

	err := txm.WithTransaction(ctx, func(txCtx context.Context) error {
		txRaw := txm.ExtractTx(txCtx)
		tx, ok := txRaw.(*sql.Tx)
		if !ok {
			return fmt.Errorf("ExtractTx type = %T; want *sql.Tx", txRaw)
		}

		if _, err := tx.ExecContext(txCtx, `UPDATE User SET Name = ? WHERE Id = ?`, "after", "user-001"); err != nil {
			return err
		}
		rows, err := repo.RevokeAllSessionsByProfileID(txCtx, "prof-001")
		if err != nil {
			return err
		}
		if rows != 1 {
			return fmt.Errorf("rows affected = %d; want 1", rows)
		}

		var revoked bool
		if err := tx.QueryRowContext(txCtx, `SELECT IsRevoked FROM _System_Session WHERE Id = ?`, "sess-001").Scan(&revoked); err != nil {
			return err
		}
		if !revoked {
			return errors.New("session not revoked inside tx")
		}

		return forcedRollback
	})
	if !errors.Is(err, forcedRollback) {
		t.Fatalf("WithTransaction error = %v; want forced rollback", err)
	}

	var revoked bool
	if err := db.QueryRowContext(ctx, `SELECT IsRevoked FROM _System_Session WHERE Id = ?`, "sess-001").Scan(&revoked); err != nil {
		t.Fatalf("select rolled-back session: %v", err)
	}
	if revoked {
		t.Fatal("session remained revoked after rollback; want false")
	}
}