namespace AutomationDAL.Query.EventMap;

public static class EventMapQB
{
    public const string INSERT = @"
        INSERT INTO AUTO.TAUTOMATIONEVENTMAP
               (EVENTTYPE, TENANTID, CUSTOMERID, STAGE, PRECEDINGSTAGE, TARGETID, ACKCALLBACKURL, ISACTIVE, CREATEDON)
        VALUES (@EventType, @TenantId, @CustomerId, @Stage, @PrecedingStage, @TargetId, @AckCallbackUrl, 1, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string UPDATE = @"
        UPDATE AUTO.TAUTOMATIONEVENTMAP
        SET    EVENTTYPE      = @EventType,
               CUSTOMERID     = @CustomerId,
               STAGE          = @Stage,
               PRECEDINGSTAGE = @PrecedingStage,
               TARGETID       = @TargetId,
               ACKCALLBACKURL = @AckCallbackUrl,
               ISACTIVE       = @IsActive
        WHERE  MAPID    = @MapId
        AND    TENANTID = @TenantId";

    // Covering index required: IX_TAUTOMATIONEVENTMAP_LOOKUP (EVENTTYPE, TENANTID, ISACTIVE)
    // Most-specific-first: a CustomerId-scoped mapping wins over a tenant-wide one for the same event.
    public const string RESOLVE = @"
        SELECT TOP (1) MAPID, EVENTTYPE, TENANTID, CUSTOMERID, STAGE, PRECEDINGSTAGE, TARGETID, ACKCALLBACKURL, ISACTIVE, CREATEDON
        FROM   AUTO.TAUTOMATIONEVENTMAP
        WHERE  EVENTTYPE = @EventType
        AND    TENANTID  = @TenantId
        AND    ISACTIVE  = 1
        AND    (CUSTOMERID = @CustomerId OR CUSTOMERID IS NULL)
        ORDER BY CASE WHEN CUSTOMERID IS NULL THEN 1 ELSE 0 END";

    public const string GET_BY_TENANT = @"
        SELECT MAPID, EVENTTYPE, TENANTID, CUSTOMERID, STAGE, PRECEDINGSTAGE, TARGETID, ACKCALLBACKURL, ISACTIVE, CREATEDON
        FROM   AUTO.TAUTOMATIONEVENTMAP
        WHERE  TENANTID = @TenantId
        ORDER BY EVENTTYPE";
}