package bootstrap

import (
	"context"
	"database/sql"
	"regexp"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/stretchr/testify/require"
)

func TestBackfillContentCompatSystemFiles_NoAllowlistedPrefixes(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	conn := database.NewConnectionFromDB(db)

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLinksQuery())).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ContentDocumentId", "LinkedEntityId"}))

	require.NoError(t, BackfillContentCompatSystemFiles(context.Background(), conn))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBackfillContentCompatSystemFiles_ProjectsAllowlistedFiles(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	conn := database.NewConnectionFromDB(db)

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLinksQuery())).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ContentDocumentId", "LinkedEntityId"}).
			AddRow("cdl-1", "069DOC1", "0Q0QQQ000000001AAA").
			AddRow("cdl-2", "069DOC2", "003LEAD00000001AAA"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOC1").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOC1", "068VER1", "Archived Quote", "exports/Archived Quote.pdf", "pdf", 1280, "content-version/069DOC1/068VER1/Archived Quote.pdf", "s3://nexus-crm-prod/content-version/069DOC1/068VER1/Archived Quote.pdf"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("0Q0QQQ000000001AAA", "Quote", contentCompatSourceType, "069DOC1").
		WillReturnError(sql.ErrNoRows)

	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			generateContentCompatFileID("Quote", "0Q0QQQ000000001AAA", "069DOC1"),
			"0Q0QQQ000000001AAA",
			"Quote",
			"Archived Quote.pdf",
			"Archived Quote.pdf",
			"pdf",
			"application/pdf",
			int64(1280),
			"s3://nexus-crm-prod/content-version/069DOC1/068VER1/Archived Quote.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOC1/068VER1/Archived Quote.pdf",
			"Active",
			contentCompatSourceType,
			"069DOC1",
			"068VER1",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, BackfillContentCompatSystemFiles(context.Background(), conn))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestResolveContentCompatFileID_ReusesExistingCompatRow(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("801ORDER0000001AAA", "Order", contentCompatSourceType, "069DOCX").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow("existing-compat-row"))

	got, err := resolveContentCompatFileID(context.Background(), db, "Order", "801ORDER0000001AAA", "069DOCX")
	require.NoError(t, err)
	require.Equal(t, "existing-compat-row", got)
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestGenerateContentCompatFileID_IsDeterministic(t *testing.T) {
	got1 := generateContentCompatFileID("Quote", "0Q0QQQ000000001AAA", "069DOC1")
	got2 := generateContentCompatFileID("Quote", "0Q0QQQ000000001AAA", "069DOC1")
	require.Equal(t, got1, got2)
	require.Equal(t, "sysfilecompat_6bb84b967bbdf7b1acc32bd9de54be48", got1)
}

func TestBackfillContentCompatSystemFiles_SkipsWhenLatestVersionMissing(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	conn := database.NewConnectionFromDB(db)

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLinksQuery())).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ContentDocumentId", "LinkedEntityId"}).
			AddRow("cdl-1", "069MISSING", "0Q0QQQ000000001AAA"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069MISSING").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}))

	require.NoError(t, BackfillContentCompatSystemFiles(context.Background(), conn))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBackfillContentCompatSystemFiles_ReusesExistingCompatRowAcrossDuplicateLinks(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	conn := database.NewConnectionFromDB(db)

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLinksQuery())).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ContentDocumentId", "LinkedEntityId"}).
			AddRow("cdl-1", "069DOC1", "801ORDER0000001AAA").
			AddRow("cdl-2", "069DOC1", "801ORDER0000001AAA"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOC1").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOC1", "068VER1", "Archived Order", "exports/Archived Order.pdf", "pdf", 2048, "content-version/069DOC1/068VER1/Archived Order.pdf", "s3://nexus-crm-prod/content-version/069DOC1/068VER1/Archived Order.pdf"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("801ORDER0000001AAA", "Order", contentCompatSourceType, "069DOC1").
		WillReturnError(sql.ErrNoRows)

	compatID := generateContentCompatFileID("Order", "801ORDER0000001AAA", "069DOC1")
	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			compatID,
			"801ORDER0000001AAA",
			"Order",
			"Archived Order.pdf",
			"Archived Order.pdf",
			"pdf",
			"application/pdf",
			int64(2048),
			"s3://nexus-crm-prod/content-version/069DOC1/068VER1/Archived Order.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOC1/068VER1/Archived Order.pdf",
			"Active",
			contentCompatSourceType,
			"069DOC1",
			"068VER1",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOC1").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOC1", "068VER1", "Archived Order", "exports/Archived Order.pdf", "pdf", 2048, "content-version/069DOC1/068VER1/Archived Order.pdf", "s3://nexus-crm-prod/content-version/069DOC1/068VER1/Archived Order.pdf"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("801ORDER0000001AAA", "Order", contentCompatSourceType, "069DOC1").
		WillReturnRows(sqlmock.NewRows([]string{"Id"}).AddRow(compatID))

	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			compatID,
			"801ORDER0000001AAA",
			"Order",
			"Archived Order.pdf",
			"Archived Order.pdf",
			"pdf",
			"application/pdf",
			int64(2048),
			"s3://nexus-crm-prod/content-version/069DOC1/068VER1/Archived Order.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOC1/068VER1/Archived Order.pdf",
			"Active",
			contentCompatSourceType,
			"069DOC1",
			"068VER1",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, BackfillContentCompatSystemFiles(context.Background(), conn))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBackfillContentCompatSystemFiles_ProjectsMixedAllowlistedBatchAndSkipsUnsupportedParents(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	conn := database.NewConnectionFromDB(db)

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLinksQuery())).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ContentDocumentId", "LinkedEntityId"}).
			AddRow("cdl-quote", "069DOCQ", "0Q0QQQ000000001AAA").
			AddRow("cdl-order", "069DOCO", "801ORDER0000001AAA").
			AddRow("cdl-contract", "069DOCC", "800CONTRACT0001AAA").
			AddRow("cdl-skip", "069DOCX", "003LEAD00000001AAA"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOCQ").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOCQ", "068VERQ", "Archived Quote", "exports/Archived Quote.pdf", "pdf", 1280, "content-version/069DOCQ/068VERQ/Archived Quote.pdf", "s3://nexus-crm-prod/content-version/069DOCQ/068VERQ/Archived Quote.pdf"))
	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("0Q0QQQ000000001AAA", "Quote", contentCompatSourceType, "069DOCQ").
		WillReturnError(sql.ErrNoRows)
	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			generateContentCompatFileID("Quote", "0Q0QQQ000000001AAA", "069DOCQ"),
			"0Q0QQQ000000001AAA",
			"Quote",
			"Archived Quote.pdf",
			"Archived Quote.pdf",
			"pdf",
			"application/pdf",
			int64(1280),
			"s3://nexus-crm-prod/content-version/069DOCQ/068VERQ/Archived Quote.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOCQ/068VERQ/Archived Quote.pdf",
			"Active",
			contentCompatSourceType,
			"069DOCQ",
			"068VERQ",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOCO").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOCO", "068VERO", "Archived Order", "exports/Archived Order.pdf", "pdf", 2048, "content-version/069DOCO/068VERO/Archived Order.pdf", "s3://nexus-crm-prod/content-version/069DOCO/068VERO/Archived Order.pdf"))
	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("801ORDER0000001AAA", "Order", contentCompatSourceType, "069DOCO").
		WillReturnError(sql.ErrNoRows)
	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			generateContentCompatFileID("Order", "801ORDER0000001AAA", "069DOCO"),
			"801ORDER0000001AAA",
			"Order",
			"Archived Order.pdf",
			"Archived Order.pdf",
			"pdf",
			"application/pdf",
			int64(2048),
			"s3://nexus-crm-prod/content-version/069DOCO/068VERO/Archived Order.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOCO/068VERO/Archived Order.pdf",
			"Active",
			contentCompatSourceType,
			"069DOCO",
			"068VERO",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOCC").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOCC", "068VERC", "Archived Contract", "exports/Archived Contract.pdf", "pdf", 4096, "content-version/069DOCC/068VERC/Archived Contract.pdf", "s3://nexus-crm-prod/content-version/069DOCC/068VERC/Archived Contract.pdf"))
	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("800CONTRACT0001AAA", "Contract", contentCompatSourceType, "069DOCC").
		WillReturnError(sql.ErrNoRows)
	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			generateContentCompatFileID("Contract", "800CONTRACT0001AAA", "069DOCC"),
			"800CONTRACT0001AAA",
			"Contract",
			"Archived Contract.pdf",
			"Archived Contract.pdf",
			"pdf",
			"application/pdf",
			int64(4096),
			"s3://nexus-crm-prod/content-version/069DOCC/068VERC/Archived Contract.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOCC/068VERC/Archived Contract.pdf",
			"Active",
			contentCompatSourceType,
			"069DOCC",
			"068VERC",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, BackfillContentCompatSystemFiles(context.Background(), conn))
	require.NoError(t, mock.ExpectationsWereMet())
}

func TestBackfillContentCompatSystemFiles_ProjectsSeparateCompatRowsForSharedDocumentAcrossParents(t *testing.T) {
	db, mock, err := sqlmock.New()
	require.NoError(t, err)
	defer db.Close()

	conn := database.NewConnectionFromDB(db)

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLinksQuery())).
		WillReturnRows(sqlmock.NewRows([]string{"Id", "ContentDocumentId", "LinkedEntityId"}).
			AddRow("cdl-quote", "069DOCSHARED", "0Q0QQQ000000001AAA").
			AddRow("cdl-order", "069DOCSHARED", "801ORDER0000001AAA"))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOCSHARED").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOCSHARED", "068VERSHARED", "Shared Archive", "exports/Shared Archive.pdf", "pdf", 3072, "content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf", "s3://nexus-crm-prod/content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf"))
	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("0Q0QQQ000000001AAA", "Quote", contentCompatSourceType, "069DOCSHARED").
		WillReturnError(sql.ErrNoRows)
	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			generateContentCompatFileID("Quote", "0Q0QQQ000000001AAA", "069DOCSHARED"),
			"0Q0QQQ000000001AAA",
			"Quote",
			"Shared Archive.pdf",
			"Shared Archive.pdf",
			"pdf",
			"application/pdf",
			int64(3072),
			"s3://nexus-crm-prod/content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf",
			"Active",
			contentCompatSourceType,
			"069DOCSHARED",
			"068VERSHARED",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	mock.ExpectQuery(regexp.QuoteMeta(contentCompatLatestVersionQuery())).
		WithArgs("069DOCSHARED").
		WillReturnRows(sqlmock.NewRows([]string{"Id", "LatestPublishedVersionId", "Title", "PathOnClient", "FileExtension", "ContentSize", "S3Key", "S3Url"}).
			AddRow("069DOCSHARED", "068VERSHARED", "Shared Archive", "exports/Shared Archive.pdf", "pdf", 3072, "content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf", "s3://nexus-crm-prod/content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf"))
	mock.ExpectQuery(regexp.QuoteMeta(contentCompatExistingFileQuery())).
		WithArgs("801ORDER0000001AAA", "Order", contentCompatSourceType, "069DOCSHARED").
		WillReturnError(sql.ErrNoRows)
	mock.ExpectExec(regexp.QuoteMeta(contentCompatUpsertQuery())).
		WithArgs(
			generateContentCompatFileID("Order", "801ORDER0000001AAA", "069DOCSHARED"),
			"801ORDER0000001AAA",
			"Order",
			"Shared Archive.pdf",
			"Shared Archive.pdf",
			"pdf",
			"application/pdf",
			int64(3072),
			"s3://nexus-crm-prod/content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf",
			"s3",
			"nexus-crm-prod",
			"content-version/069DOCSHARED/068VERSHARED/Shared Archive.pdf",
			"Active",
			contentCompatSourceType,
			"069DOCSHARED",
			"068VERSHARED",
			contentCompatAttachmentKind,
			constants.SystemUserID,
		).
		WillReturnResult(sqlmock.NewResult(1, 1))

	require.NoError(t, BackfillContentCompatSystemFiles(context.Background(), conn))
	require.NoError(t, mock.ExpectationsWereMet())
}