package dialectsfcrm

// Ledgered runtime database/sql exception (B6) — the sole runtime dialect file
// permitted to import database/sql; see seals/runtime_database_sql_seal_test.go. Auth
// stays neutral downstream via Files.ResolveAuthorizedRecordAttachmentSource.
import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/nexuscrm/shared/pkg/contract"
)

const (
	contentDistributionSourceObject = "ContentDistribution"
	contentDistributionSourceField  = "IdBody"
)

type contentDistributionFileLookup struct {
	db *sql.DB
}

func NewContentDistributionFileLookup(db *sql.DB) contract.RecordAttachmentSourceLookup {
	if db == nil {
		return nil
	}
	return &contentDistributionFileLookup{db: db}
}

func (r *contentDistributionFileLookup) LookupRecordAttachmentSource(ctx context.Context, req contract.RecordAttachmentSourceRequest) (*contract.RecordAttachmentSourceLookupResult, bool, error) {
	if r == nil || r.db == nil {
		return nil, false, nil
	}
	if !strings.EqualFold(strings.TrimSpace(req.SourceObject), contentDistributionSourceObject) ||
		!strings.EqualFold(strings.TrimSpace(req.SourceField), contentDistributionSourceField) {
		return nil, false, nil
	}
	parentID := strings.TrimSpace(req.ParentID)
	distIDBody := strings.TrimSpace(req.SourceValue)
	if parentID == "" || distIDBody == "" {
		return nil, true, nil
	}
	fileID, err := r.lookupSystemFileIDByDistribution(ctx, parentID, distIDBody)
	if err != nil {
		return nil, true, err
	}
	if fileID == "" {
		return nil, false, nil
	}
	return &contract.RecordAttachmentSourceLookupResult{FileID: fileID}, true, nil
}

func (r *contentDistributionFileLookup) lookupSystemFileIDByDistribution(ctx context.Context, parentID, distIDBody string) (string, error) {
	const q = "SELECT sf.`Id` FROM `_System_File` sf " +
		"JOIN `ContentDistribution` cd " +
		"  ON cd.`ContentVersionId` = sf.`SourceContentVersionId` " +
		" AND cd.`IsDeleted` = 0 " +
		"WHERE SUBSTRING(cd.`Id`, 4, 12) = ? " +
		"  AND sf.`ParentId` = ? " +
		"  AND (sf.`Status` IS NULL OR sf.`Status` = 'Active') " +
		"ORDER BY sf.`CreatedDate` DESC " +
		"LIMIT 1"
	var fileID string
	err := r.db.QueryRowContext(ctx, q, distIDBody, parentID).Scan(&fileID)
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", fmt.Errorf("query content distribution file: %w", err)
	}
	return strings.TrimSpace(fileID), nil
}