package bootstrap

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"fmt"
	"log"
	"mime"
	"net/url"
	"path"
	"strings"

	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/nexuscrm/backend/internal/infrastructure/storage"
	"github.com/nexuscrm/shared/pkg/constants"
)

const (
	contentCompatSourceType       = "content_compat"
	contentCompatAttachmentKind   = "record"
	contentCompatObjectPrefixSize = 3
)

var contentCompatAllowlistedParents = []string{"Quote", "Order", "Contract"}
var contentCompatAllowlistedParentPrefixes = map[string]string{
	"0Q0": "Quote",
	"800": "Contract",
	"801": "Order",
}

type contentCompatLinkRow struct {
	ID              string
	ContentDocument string
	LinkedEntityID  string
}

type contentCompatVersionRow struct {
	DocumentID      string
	LatestVersionID string
	Title           sql.NullString
	PathOnClient    sql.NullString
	FileExtension   sql.NullString
	ContentSize     sql.NullInt64
	S3Key           sql.NullString
	S3URL           sql.NullString
}

func contentCompatLinksQuery() string {
	return `
		SELECT ` + "`Id`, `ContentDocumentId`, `LinkedEntityId`" + `
		FROM ` + "`ContentDocumentLink`" + `
		WHERE ` + "`IsDeleted` = 0" + `
		ORDER BY ` + "`Id`"
}

func contentCompatLatestVersionQuery() string {
	return `
		SELECT
			d.` + "`Id`" + `,
			d.` + "`LatestPublishedVersionId`" + `,
			v.` + "`Title`" + `,
			v.` + "`PathOnClient`" + `,
			v.` + "`FileExtension`" + `,
			CAST(v.` + "`ContentSize`" + ` AS SIGNED) AS ` + "`ContentSize`" + `,
			v.` + "`S3Key`" + `,
			v.` + "`S3Url`" + `
		FROM ` + "`ContentDocument`" + ` d
		JOIN ` + "`ContentVersion`" + ` v ON v.` + "`Id`" + ` = d.` + "`LatestPublishedVersionId`" + `
		WHERE d.` + "`Id`" + ` = ? AND v.` + "`IsLatest`" + ` = 1 AND v.` + "`ContentDocumentId`" + ` = d.` + "`Id`" + `
		LIMIT 1`
}

func contentCompatUpsertQuery() string {
	return "INSERT INTO `_System_File` (" +
		"`Id`, `ParentId`, `ParentObjectApiName`, `Name`, `OriginalFileName`, `Extension`, `MimeType`, `SizeBytes`, " +
		"`StoragePath`, `StorageProvider`, `StorageBucket`, `StorageKey`, `Status`, `SourceType`, " +
		"`SourceContentDocumentId`, `SourceContentVersionId`, `AttachmentKind`, `CreatedById`" +
		") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " +
		"ON DUPLICATE KEY UPDATE " +
		"`ParentId` = VALUES(`ParentId`), " +
		"`ParentObjectApiName` = VALUES(`ParentObjectApiName`), " +
		"`Name` = VALUES(`Name`), " +
		"`OriginalFileName` = VALUES(`OriginalFileName`), " +
		"`Extension` = VALUES(`Extension`), " +
		"`MimeType` = VALUES(`MimeType`), " +
		"`SizeBytes` = VALUES(`SizeBytes`), " +
		"`StoragePath` = VALUES(`StoragePath`), " +
		"`StorageProvider` = VALUES(`StorageProvider`), " +
		"`StorageBucket` = VALUES(`StorageBucket`), " +
		"`StorageKey` = VALUES(`StorageKey`), " +
		"`Status` = VALUES(`Status`), " +
		"`SourceType` = VALUES(`SourceType`), " +
		"`SourceContentDocumentId` = VALUES(`SourceContentDocumentId`), " +
		"`SourceContentVersionId` = VALUES(`SourceContentVersionId`), " +
		"`AttachmentKind` = VALUES(`AttachmentKind`), " +
		"`DeletedById` = NULL, " +
		"`DeletedDate` = NULL"
}

func contentCompatExistingFileQuery() string {
	return "SELECT `Id` FROM `_System_File` " +
		"WHERE `ParentId` = ? AND `ParentObjectApiName` = ? AND `SourceType` = ? AND `SourceContentDocumentId` = ? " +
		"ORDER BY `CreatedDate` DESC, `Id` DESC LIMIT 1"
}

// BackfillContentCompatSystemFiles projects legacy ContentDocument /
// ContentVersion / ContentDocumentLink data into `_System_File` rows that
// the new file service can serve via `SourceType=content_compat`.
//
// The helper is idempotent:
//   - allowlisted parents are resolved from standard Salesforce key prefixes
//   - each projected row reuses an existing compat row when present, otherwise
//     it uses a deterministic compat row ID derived from parent/document identity
//   - a second run reuses the same upsert path and preserves the same row
//
// It is intentionally narrow: only allowlisted parent objects are projected,
// and only the latest version for each linked document is backfilled.
func BackfillContentCompatSystemFiles(ctx context.Context, db *database.TiDBConnection) error {
	if db == nil || db.DB() == nil {
		return fmt.Errorf("content compat backfill requires a database connection")
	}

	prefixes, err := loadContentCompatParentPrefixes(ctx, db.DB())
	if err != nil {
		return err
	}
	if len(prefixes) == 0 {
		log.Println("   ✅ BackfillContentCompatSystemFiles: no allowlisted parent prefixes found")
		return nil
	}

	links, err := listContentCompatLinks(ctx, db.DB())
	if err != nil {
		return err
	}
	if len(links) == 0 {
		log.Println("   ✅ BackfillContentCompatSystemFiles: no legacy content links found")
		return nil
	}

	projected := 0
	skippedParents := 0
	skippedVersions := 0
	for _, link := range links {
		parentObjectAPIName, ok := resolveContentCompatParentObject(link.LinkedEntityID, prefixes)
		if !ok {
			skippedParents++
			continue
		}

		versionRow, err := loadLatestContentVersion(ctx, db.DB(), link.ContentDocument)
		if err != nil {
			return fmt.Errorf("load latest content version for document %q: %w", link.ContentDocument, err)
		}
		if versionRow == nil {
			skippedVersions++
			continue
		}

		if err := upsertContentCompatFile(ctx, db.DB(), parentObjectAPIName, link.LinkedEntityID, versionRow); err != nil {
			return fmt.Errorf("project content document %q to parent %q: %w", link.ContentDocument, link.LinkedEntityID, err)
		}
		projected++
	}

	log.Printf("   ✅ BackfillContentCompatSystemFiles: projected=%d skipped-parent=%d skipped-version=%d inspected=%d",
		projected, skippedParents, skippedVersions, len(links))
	return nil
}

func loadContentCompatParentPrefixes(ctx context.Context, db *sql.DB) (map[string]string, error) {
	_ = ctx
	_ = db

	if len(contentCompatAllowlistedParentPrefixes) == 0 {
		return map[string]string{}, nil
	}

	prefixes := make(map[string]string, len(contentCompatAllowlistedParentPrefixes))
	for prefix, apiName := range contentCompatAllowlistedParentPrefixes {
		prefixes[prefix] = apiName
	}
	return prefixes, nil
}

func listContentCompatLinks(ctx context.Context, db *sql.DB) ([]contentCompatLinkRow, error) {
	rows, err := db.QueryContext(ctx, contentCompatLinksQuery())
	if err != nil {
		return nil, fmt.Errorf("list content document links: %w", err)
	}
	defer rows.Close()

	var links []contentCompatLinkRow
	for rows.Next() {
		var row contentCompatLinkRow
		if err := rows.Scan(&row.ID, &row.ContentDocument, &row.LinkedEntityID); err != nil {
			return nil, fmt.Errorf("scan content document link: %w", err)
		}
		links = append(links, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate content document links: %w", err)
	}
	return links, nil
}

func resolveContentCompatParentObject(linkedEntityID string, prefixes map[string]string) (string, bool) {
	linkedEntityID = strings.TrimSpace(linkedEntityID)
	if len(linkedEntityID) < contentCompatObjectPrefixSize {
		return "", false
	}

	prefix := linkedEntityID[:contentCompatObjectPrefixSize]
	apiName, ok := prefixes[prefix]
	if !ok {
		return "", false
	}
	return apiName, true
}

func loadLatestContentVersion(ctx context.Context, db *sql.DB, contentDocumentID string) (*contentCompatVersionRow, error) {
	row := db.QueryRowContext(ctx, contentCompatLatestVersionQuery(), contentDocumentID)

	var result contentCompatVersionRow
	if err := row.Scan(
		&result.DocumentID,
		&result.LatestVersionID,
		&result.Title,
		&result.PathOnClient,
		&result.FileExtension,
		&result.ContentSize,
		&result.S3Key,
		&result.S3URL,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, fmt.Errorf("query latest content version: %w", err)
	}
	return &result, nil
}

func upsertContentCompatFile(ctx context.Context, db *sql.DB, parentObjectAPIName, parentID string, versionRow *contentCompatVersionRow) error {
	if versionRow == nil {
		return nil
	}

	fileName := deriveCompatFileName(versionRow.Title, versionRow.PathOnClient, versionRow.FileExtension)
	extension := deriveCompatExtension(versionRow.FileExtension, fileName)
	mimeType := mime.TypeByExtension("." + extension)
	if strings.TrimSpace(mimeType) == "" {
		mimeType = "application/octet-stream"
	}
	if extension == "" {
		mimeType = strings.TrimSpace(mimeType)
	}

	targetID, err := resolveContentCompatFileID(ctx, db, parentObjectAPIName, parentID, versionRow.DocumentID)
	if err != nil {
		return fmt.Errorf("resolve compat file id: %w", err)
	}

	values := []interface{}{
		targetID,
		parentID,
		parentObjectAPIName,
		fileName,
		fileName,
		extension,
		mimeType,
		contentSizeOrZero(versionRow.ContentSize),
		contentCompatStoragePath(versionRow),
		contentCompatStorageProvider(versionRow),
		contentCompatStorageBucket(versionRow),
		contentCompatStorageKey(versionRow),
		"Active",
		contentCompatSourceType,
		versionRow.DocumentID,
		versionRow.LatestVersionID,
		contentCompatAttachmentKind,
		constants.SystemUserID,
	}

	_, err = db.ExecContext(ctx, contentCompatUpsertQuery(), values...)
	if err != nil {
		return fmt.Errorf("upsert compat file: %w", err)
	}
	return nil
}

func contentCompatStorageProvider(versionRow *contentCompatVersionRow) string {
	if versionRow != nil && versionRow.S3Key.Valid && strings.TrimSpace(versionRow.S3Key.String) != "" {
		return storage.ProviderS3
	}
	return storage.ProviderFilesystem
}

func contentCompatStorageKey(versionRow *contentCompatVersionRow) string {
	if versionRow == nil || !versionRow.S3Key.Valid {
		return ""
	}
	return strings.TrimSpace(versionRow.S3Key.String)
}

func contentCompatStorageBucket(versionRow *contentCompatVersionRow) string {
	if versionRow == nil || !versionRow.S3URL.Valid {
		return ""
	}
	parsed, err := url.Parse(strings.TrimSpace(versionRow.S3URL.String))
	if err != nil || !strings.EqualFold(parsed.Scheme, "s3") {
		return ""
	}
	return strings.TrimSpace(parsed.Host)
}

func contentCompatStoragePath(versionRow *contentCompatVersionRow) string {
	if versionRow != nil && versionRow.S3URL.Valid && strings.TrimSpace(versionRow.S3URL.String) != "" {
		return strings.TrimSpace(versionRow.S3URL.String)
	}
	return legacyContentVersionDownloadURL(versionRow.LatestVersionID)
}

func resolveContentCompatFileID(ctx context.Context, db *sql.DB, parentObjectAPIName, parentID, documentID string) (string, error) {
	var existingID string
	err := db.QueryRowContext(
		ctx,
		contentCompatExistingFileQuery(),
		parentID,
		parentObjectAPIName,
		contentCompatSourceType,
		documentID,
	).Scan(&existingID)
	switch {
	case err == nil:
		return existingID, nil
	case err == sql.ErrNoRows:
		return generateContentCompatFileID(parentObjectAPIName, parentID, documentID), nil
	default:
		return "", err
	}
}

func generateContentCompatFileID(parentObjectAPIName, parentID, documentID string) string {
	sum := sha256.Sum256([]byte(strings.Join([]string{
		strings.TrimSpace(parentObjectAPIName),
		strings.TrimSpace(parentID),
		strings.TrimSpace(documentID),
	}, "|")))
	return "sysfilecompat_" + hex.EncodeToString(sum[:16])
}

func legacyContentVersionDownloadURL(versionID string) string {
	return fmt.Sprintf("/services/data/v63.0/sobjects/ContentVersion/%s/VersionData", strings.TrimSpace(versionID))
}

func deriveCompatFileName(title, pathOnClient, fileExtension sql.NullString) string {
	candidates := []string{}
	if pathOnClient.Valid {
		candidates = append(candidates, pathOnClient.String)
	}
	if title.Valid {
		candidates = append(candidates, title.String)
	}
	for _, candidate := range candidates {
		name := normalizeCompatFileName(candidate)
		if name != "" {
			return appendCompatExtensionIfMissing(name, fileExtension)
		}
	}
	if fileExtension.Valid && strings.TrimSpace(fileExtension.String) != "" {
		return "legacy-file." + strings.TrimPrefix(strings.TrimSpace(fileExtension.String), ".")
	}
	return "legacy-file"
}

func appendCompatExtensionIfMissing(fileName string, fileExtension sql.NullString) string {
	ext := ""
	if fileExtension.Valid {
		ext = strings.TrimPrefix(strings.TrimSpace(fileExtension.String), ".")
	}
	if ext == "" {
		return fileName
	}
	if strings.EqualFold(strings.TrimPrefix(path.Ext(fileName), "."), ext) {
		return fileName
	}
	return fileName + "." + ext
}

func deriveCompatExtension(fileExtension sql.NullString, fileName string) string {
	if fileExtension.Valid && strings.TrimSpace(fileExtension.String) != "" {
		return strings.TrimPrefix(strings.TrimSpace(fileExtension.String), ".")
	}
	ext := strings.TrimPrefix(path.Ext(normalizeCompatFileName(fileName)), ".")
	return strings.TrimSpace(ext)
}

func normalizeCompatFileName(value string) string {
	value = strings.TrimSpace(value)
	if value == "" {
		return ""
	}
	value = strings.ReplaceAll(value, "\\", "/")
	value = path.Base(value)
	if value == "." || value == "/" || value == "" {
		return ""
	}
	return value
}

func contentSizeOrZero(size sql.NullInt64) int64 {
	if !size.Valid {
		return 0
	}
	return size.Int64
}