package persistence

import (
	"bytes"
	"log"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/stretchr/testify/require"
)

// physColumns names the six INFORMATION_SCHEMA columns that
// getPhysicalColumnSpec selects, in order.
var physColumns = []string{
	"COLUMN_TYPE", "IS_NULLABLE", "COLUMN_DEFAULT", "EXTRA", "COLUMN_COMMENT", "COLLATION_NAME",
}

// TestReconcileNullability_RelaxesNotNullToNull — PR #1850 Gap C:
// when spec says nullable=true but the physical column is NOT NULL,
// reconcileNullability must emit ALTER TABLE ... MODIFY ... NULL so the
// V2 audit writer can insert NULL, NULL for head-row sentinels.
//
// Reproduces the staging canary failure where _System_AuditLog.OldValue
// / NewValue were NOT NULL in the live DB but the spec treats them as
// nullable; INSERT ... NULL, NULL failed with Error 1048 (23000).
func TestReconcileNullability_RelaxesNotNullToNull(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	repo := NewSchemaRepository(db, nil)

	// Physical column exists, is NOT NULL, and has no other attributes.
	mock.ExpectQuery("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("_System_AuditLog", "OldValue").
		WillReturnRows(sqlmock.NewRows(physColumns).
			AddRow("longtext", "NO", nil, "", "", nil))

	// Expect the relax DDL. Type is preserved verbatim from the physical read.
	mock.ExpectExec("ALTER TABLE `_System_AuditLog` MODIFY COLUMN `OldValue` longtext NULL").
		WillReturnResult(sqlmock.NewResult(0, 0))

	col := schema.ColumnDefinition{
		Name:     "OldValue",
		Type:     "LONGTEXT",
		Nullable: true,
	}
	require.NoError(t, repo.reconcileNullability("_System_AuditLog", col))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcileNullability_WarnsOnSpecNotNullVsPhysicalNullable — safety
// rail: when the spec says NOT NULL but the physical column is nullable,
// we MUST NOT auto-tighten (existing NULL rows would fail the new
// constraint). We only log a WARN.
func TestReconcileNullability_WarnsOnSpecNotNullVsPhysicalNullable(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	repo := NewSchemaRepository(db, nil)

	mock.ExpectQuery("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("SomeTable", "SomeCol").
		WillReturnRows(sqlmock.NewRows(physColumns).
			AddRow("varchar(255)", "YES", nil, "", "", "utf8mb4_general_ci"))

	// No ALTER expected — tightening is unsafe.

	// Capture log output to assert the WARN was emitted.
	var buf bytes.Buffer
	prevOut := log.Writer()
	log.SetOutput(&buf)
	defer log.SetOutput(prevOut)

	col := schema.ColumnDefinition{
		Name:     "SomeCol",
		Type:     "VARCHAR(255)",
		Nullable: false,
	}
	require.NoError(t, repo.reconcileNullability("SomeTable", col))
	require.NoError(t, mock.ExpectationsWereMet())
	require.Contains(t, buf.String(), "Nullability drift")
	require.Contains(t, buf.String(), "Auto-tightening is unsafe")
}

// TestReconcileNullability_NoOpWhenAligned — both sides agree, no DDL.
func TestReconcileNullability_NoOpWhenAligned(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	repo := NewSchemaRepository(db, nil)

	mock.ExpectQuery("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("T", "C").
		WillReturnRows(sqlmock.NewRows(physColumns).
			AddRow("varchar(64)", "YES", nil, "", "", "utf8mb4_general_ci"))

	col := schema.ColumnDefinition{Name: "C", Type: "VARCHAR(64)", Nullable: true}
	require.NoError(t, repo.reconcileNullability("T", col))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcileNullability_PreservesDefault — issue #1918: MODIFY COLUMN
// replaces the entire column spec, so the relax path must restate DEFAULT
// or silently lose it.
func TestReconcileNullability_PreservesDefault(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	repo := NewSchemaRepository(db, nil)

	mock.ExpectQuery("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("T", "Status").
		WillReturnRows(sqlmock.NewRows(physColumns).
			AddRow("varchar(32)", "NO", "pending", "", "", "utf8mb4_general_ci"))

	// ALTER must carry DEFAULT 'pending' through the relax.
	mock.ExpectExec("ALTER TABLE `T` MODIFY COLUMN `Status` varchar\\(32\\) COLLATE utf8mb4_general_ci NULL DEFAULT 'pending'").
		WillReturnResult(sqlmock.NewResult(0, 0))

	col := schema.ColumnDefinition{Name: "Status", Type: "VARCHAR(32)", Nullable: true}
	require.NoError(t, repo.reconcileNullability("T", col))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcileNullability_PreservesCollate — physical-sourced: the spec
// carries no Collate field, so the only source of truth for collation is
// the physical column. Without preservation, MODIFY drops collation back
// to the table default, silently changing sort/comparison semantics.
func TestReconcileNullability_PreservesCollate(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	repo := NewSchemaRepository(db, nil)

	mock.ExpectQuery("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("T", "Code").
		WillReturnRows(sqlmock.NewRows(physColumns).
			AddRow("varchar(64)", "NO", nil, "", "", "utf8mb4_bin"))

	mock.ExpectExec("ALTER TABLE `T` MODIFY COLUMN `Code` varchar\\(64\\) COLLATE utf8mb4_bin NULL").
		WillReturnResult(sqlmock.NewResult(0, 0))

	col := schema.ColumnDefinition{Name: "Code", Type: "VARCHAR(64)", Nullable: true}
	require.NoError(t, repo.reconcileNullability("T", col))
	require.NoError(t, mock.ExpectationsWereMet())
}

// TestReconcileNullability_PreservesComment — physical-sourced COMMENT
// must survive the relax; MODIFY without COMMENT would clear it.
func TestReconcileNullability_PreservesComment(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)
	defer db.Close()

	repo := NewSchemaRepository(db, nil)

	mock.ExpectQuery("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLLATION_NAME\\s+FROM INFORMATION_SCHEMA\\.COLUMNS").
		WithArgs("T", "Note").
		WillReturnRows(sqlmock.NewRows(physColumns).
			AddRow("varchar(255)", "NO", nil, "", "abc", "utf8mb4_general_ci"))

	mock.ExpectExec("ALTER TABLE `T` MODIFY COLUMN `Note` varchar\\(255\\) COLLATE utf8mb4_general_ci NULL COMMENT 'abc'").
		WillReturnResult(sqlmock.NewResult(0, 0))

	col := schema.ColumnDefinition{Name: "Note", Type: "VARCHAR(255)", Nullable: true}
	require.NoError(t, repo.reconcileNullability("T", col))
	require.NoError(t, mock.ExpectationsWereMet())
}